Unit-2 Database Management System

Data

Data refers to raw facts and figures that may or may not give any sense.

Examples

Ram
15
Pokhara
2083
Computer Science

Information

Information is processed or refined data that gives a complete meaning.

Example

Ram is a 15-year-old student from Pokhara studying Computer Science in 2083.

Difference Between Data and Information

Data	Information
Data is raw and unorganized facts.	Information is processed and meaningful data.
Data has no clear meaning.	Information has clear meaning.
Data is used as input.	Information is used for decision making.
Example: Ram, 15, Pokhara	Example: Ram is a 15-year-old student from Pokhara.

Table

A table is a collection of related data organized in rows and columns in a database. A table is used to store data. A table contains fields and records.

A field is a single piece of data in a table, represented as a column (e.g., Name or Age).

A record is a complete set of related fields, represented as a row containing all information about one entry.

Database

A database is a collection of related data stored in an organized form so that it can be easily accessed, managed, and updated. A database can be very large like Facebook’s database or small like a classroom attendance register.

A database is an organized collection of related data stored in tables consisting of rows and columns. A database contain single of multiple tables.

Examples of Database

Employee Payroll
Student Attendance Register
Telephone Directory
Library Records
Dictionary

Importance of Database

Huge storage of data: Stores a large amount of information efficiently.
Easy access to data: Data can be quickly retrieved and updated.
Maintains data security: Protects data from unauthorized users.
Maintains data integrity: Ensures data is accurate (correct and error-free) and consistent (same data is uniform and unchanged across the database).
Allows data sharing: Multiple users can access data at the same time.
Provides backup and recovery: Helps restore data if it is lost.
Reduces data redundancy: Avoids duplication of data

Applications of Database

Databases are used in many real-life systems where large amounts of information need to be stored, managed, and accessed efficiently.

1. Banking System : Banks use databases to store customer details, transactions, deposits, withdrawals, and loan records.

2. Education System: Schools use databases to manage student records, attendance, exam results, and fee details.

3. Railway Reservation System: Used for ticket booking, cancellation, seat availability, and passenger details.

4. Library Management System: Used to store book records, issue details, return dates, and fines.

5. Social Media Platforms: Stores posts, messages, photos, videos, and user profiles.

6. E-Commerce Websites: Manages products, orders, payments, and delivery details.

7. Healthcare System Stores patient records, medical history, and billing information.

8. Telecommunication System Manages customer data, call records, and billing information.

Database Management System (DBMS)

A DBMS is software used to create, store, manage, retrieve, update, and delete data in a database. It acts as a bridge between users and the database.

Examples of DBMS

MySQL – Open-source SQL database used for websites and applications.
Oracle Database – Powerful commercial database used in large organizations for secure and complex data management.
Microsoft SQL Server – Microsoft’s database system used in business and enterprise solutions.
PostgreSQL – Advanced open-source SQL database known for reliability and strong features.
MongoDB – NoSQL database that stores data in flexible document format for

Recent Trends in Database Technology

Cloud Database – Stores data online and can scale easily as needed.
Example: Amazon RDS, Google Cloud Firestore
NoSQL Database – Designed to handle large amounts of unstructured or semi-structured data.
Example: MongoDB, Cassandra
In-Memory Database – Stores data in RAM for very fast access and processing.
Example: Redis, SAP HANA
Distributed Database – Data is stored across multiple computers or locations but works as one system.
Example: Apache Cassandra, Google Spanner

Features of DBMS

Organized Data Storage: Stores large amounts of data in structured tables for easy management.
Fast Data Retrieval: Quickly searches and displays required information when needed.
Efficient Data Manipulation: Allows easy adding, updating, and deleting of data.
Strong Data Integrity: Ensures data is accurate, consistent, and valid at all times.
Duplicate Data Control: Reduces unnecessary repetition of data to save space and improve efficiency.
Data Security Protection: Protects data from unauthorized access and misuse.
Backup and Recovery System: Restores data safely in case of system failure or data loss.

Benefits of DBMS

Easy data storage and retrieval
Reduces data redundancy
Ensures data consistency
Supports multiple users
Provides data security
Supports backup and recovery

Challenges of DBMS

High setup cost
Security risks if not managed properly
Requires regular maintenance
Needs skilled users
May slow down with very large data

RDBMS

RDBMS is a database system where data is stored in tables and relationships among data are also stored in tables.

Examples

MySQL
Oracle
PostgreSQL
MS SQL Server

Uses of RDBMS

Websites
Banking systems
Schools
Business applications

Difference Between Database and DBMS

Database	DBMS
Organized collection of data	Software to manage data
Stores data in tables	Creates and manages data
Contains only data	Contains tools to manage data
Example: Student record file	Example: MySQL, Oracle

Data Types

Data types define the kind of data that can be stored in a database column.
Data types ensure that data is stored accurately, efficiently, and in the correct format within a database.
It also ensure data accuracy, integrity, consistency, and efficient storage in a database.

Examples

Age → INT
Name → VARCHAR
Birth Date → DATE
True/False values → BOOLEAN

1. Numeric Data Types

Used for storing numerical values and performing mathematical calculations.

Data Type	Description	Size (Bytes)	Example
INT	Stores whole numbers	4	RollNo INT
DECIMAL(p,s)	Stores exact decimal numbers. p is the total number of digits and s is the number of digits after the decimal point.	Depends on precision	Salary DECIMAL(10,2)
FLOAT	Stores approximate decimal numbers	4	Temperature FLOAT
SMALLINT	Stores smaller whole numbers	2	Marks SMALLINT
BIGINT	Stores very large whole numbers	8	Population BIGINT

2. Character (Text) Data Types

Used for storing letters, words, sentences and other text data.

Data Type	Description	Size (Bytes)	Example
CHAR(n)	Fixed-length text	n	Gender CHAR(1)
VARCHAR(n)	Variable-length text	Up to n	Name VARCHAR(50)
TEXT	Large amounts of text	Up to 65,535	Description TEXT

3. Date and Time Data Types

Used for storing dates and times.

Data Type	Description	Size (Bytes)	Example
DATE	Stores only the date	3	DOB DATE
TIME	Stores only the time	3	ClassTime TIME
DATETIME	Stores both date and time	8	EventDate DATETIME
TIMESTAMP	Records date and time automatically	4	LoginTime TIMESTAMP
YEAR	Stores a year value	1	AcademicYear YEAR

4. Boolean Data Type

Used for storing logical values such as true/false or yes/no.

Data Type	Description	Size (Bytes)	Example
BOOLEAN	Stores TRUE or FALSE	1	Passed BOOLEAN

Table (Relation), Field, records and Tuple

1. Table (relation)

A table is a collection of related data organized in rows and columns.

Represents a real-world entity (like students)
Contains multiple records and fields

Example Table

S.N.	FIRST_NAME	LAST_NAME	SUBJECT
1	SHINE	POUDEL	COMPUTER SCIENCE
2	SHIMMER	POUDEL	MATH

The whole structure above is called a Table

2. Field (Column / Attribute)

A field is a column in a table that stores a specific type of data.

Each field has a name
Stores similar data for all records

Example:

S.N.

FIRST_NAME

LAST_NAME

SUBJECT

3. Record (Row)

A record is a row in a table that contains complete information about one entity.

Combination of multiple fields
Represents one item/person

Example Records:

S.N.	FIRST_NAME	LAST_NAME	SUBJECT
1	SHINE	POUDEL	COMPUTER SCIENCE
2	SHIMMER	POUDEL	MATH

Each row = Record

4. Tuple

A tuple is another name for a record (row) in a relational database.

Used in formal DBMS terminology
No difference from record

Example Tuples:

(1, SHINE, POUDEL, COMPUTER SCIENCE)
(2, SHIMMER, POUDEL, MATH)

Keys in Database

A key is a field (or a combination of fields) used to identify, access, or relate records in a table.

Different Keys in DBMS

Primary Key
Foreign Key

Primary Key

PRIMARY KEY in DBMS is a rule which ensures that unique data is entered for a field and the field is not left blank (null).

Characteristics of Primary Key

The Primary Key can't be a duplicate meaning the same value can't appear more than once in the table.
A table cannot have more than one primary key.

Importance of Primary Key

It doesn’t allow the duplication of data in a field.
It doesn’t allow the field to be left blank (null).
It plays a vital role in establishing relationships.

Rules for Defining Primary Key

Two rows can't have the same primary key value.
The primary key field cannot be null.
The value in a primary key column can never be modified or updated if any foreign key refers to that primary key.

Example: Table Student

StudentID (Primary Key)	Name	Class	Age
101	Shimmer Poudel	10	15
102	Shine Poudel	10	16
103	Hari Thapa	9	14

Foreign Key

A foreign key is a column (or group of columns) in one database table that refers to the primary key of another table.

FOREIGN KEY is a column that creates a relationship between two tables.

StudentID is the Primary Key in the Student table.
StudentID in the Marks table is a Foreign Key because it refers to the Student table.

Features of Foreign Key

Creates a relationship between tables.
Refers Primary Key of another table.
Enforce relationships.
Ensure data integrity.
Consist in one or more columns.

Rules of Foreign Key

It must reference a Primary Key or Unique Key.
The referenced value must exist in the parent table.
Data types should be compatible.
A parent record cannot be deleted if related child records exist (unless specified otherwise).

Relationship

A relationship in DBMS is an association between two or more entities (or tables) that describes how they are connected to each other.

Example: A student enrolls in a course. Here, the entities Student and Course are related through the relationship Enrolls.

Types of Relationships in DBMS

1. One-to-One (1:1) Relationship

In this relationship, one record of an entity is associated with only one record of another entity, and vice versa.

Examples

One person → One passport
One Student → One Student ID Card

One ID card belongs to only one student.

2. One-to-Many (1:M) Relationship

In this relationship, one record of an entity can be associated with many records of another entity, but each record in the second entity is associated with only one record in the first entity.

Examples

One department → Many employees
One customer → Many orders
One Teacher → Many Students (A teacher can teach many students.)

3. Many-to-Many (M:N) Relationship

In this relationship, many records of one entity can be associated with many records of another entity.

Examples

Many students ↔ Many courses
Many doctors ↔ Many patients
Many Students ↔ Many Subjects (A student studies multiple subjects, and a subject is studied by multiple students.)

SQL (Structured Query Language)

SQL stands for Structured Query Language, pronounced as “S-Q-L” or sometimes as “See-Quel”.
Relational databases like MySQL Database, Oracle, MS SQL Server, Sybase, etc. use ANSI SQL.
SQL is the standard language for dealing with Relational Databases.

SQL can be used to:

Insert records
Search records
Update records
Delete records
Optimize and maintain databases

Features of SQL

Cross-Platform Support – Runs on Windows, Linux, and macOS.
User-Friendly – Easy to learn and use with SQL commands.
Multi-User Access – Allows multiple users to work simultaneously.
High Reliability – Ensures stable and dependable database operations.
Fast Performance – Processes data and executes queries efficiently.
Programming Language Support – Compatible with Java, Python, PHP, C++, and more.
Strong Security – Provides authentication and access control mechanisms.
Modern Application Support – Widely used in web, mobile, and enterprise applications.

DDL Statements

DDL is used to create, design, and manipulate the structure of a database.

It is used by the database designer and programmers to specify the content and the structure of the table.
It is the set of SQL commands used to create, modify and delete database structure.

1. CREATE Statement

CREATE statements are used to define the database structure schema.

Syntax

CREATE DATABASE DATABASE_NAME;

CREATE TABLE TABLE_NAME (COLUMN_NAME DATATYPE [,....]);

Example

To create a database:

CREATE DATABASE school;

To create a table:

CREATE TABLE Student (SN INT, Fullname VARCHAR(20));

2. DROP Statement

DROP commands remove tables and databases from RDBMS.

Syntax

DROP TABLE table_name;

DROP DATABASE DATABASE_NAME;

Example

DROP DATABASE SCHOOL; -- delete database school

DROP TABLE Student; --delete table name student

3. TRUNCATE TABLE

The TRUNCATE TABLE statement is used to delete the entire data inside the table but not the table itself.

Syntax

TRUNCATE TABLE table_name;

Example

TRUNCATE TABLE Student;

4. ALTER COMMAND

ALTER command is used to alter the structure of the database.

The ALTER TABLE statement is used to add, delete, or modify columns in an existing table.

a. ALTER TABLE - ADD Column

Syntax

ALTER TABLE table_name
ADD column_name datatype;

Example

ALTER TABLE student
ADD phone VARCHAR(15);

b. ALTER TABLE - DROP COLUMN

Syntax

ALTER TABLE table_name
DROP COLUMN column_name;

Example

ALTER TABLE student
DROP COLUMN phone;

c.. ALTER TABLE – MODIFY COLUMN

Used to change the datatype or size of an existing column.

Syntax


ALTER TABLE table_name
MODIFY COLUMN column_name new_datatype;

Example


ALTER TABLE Student
MODIFY COLUMN Name VARCHAR(50);

d. ALTER TABLE – RENAME COLUMN

Used to change the name of a column.

Syntax


ALTER TABLE table_name
RENAME COLUMN old_column_name TO new_column_name;

Example


ALTER TABLE Student
RENAME COLUMN Fullname TO StudentName;

e. ALTER TABLE – RENAME TABLE

Used to change the name of an existing table.

Syntax


ALTER TABLE old_table_name
RENAME TO new_table_name;

Example


ALTER TABLE Student
RENAME TO Students;
5. RENAME TABLE
The RENAME TABLE command is used to change the name of an existing table.
It is possible to change the name of a table whether it contains data or not using the simple RENAME TABLE command.
Syntax
RENAME TABLE table_name TO new_table_name;
Example
RENAME TABLE students TO grade12;

DML (Data Manipulation Language) Commands
DML (Data Manipulation Language) is used to manipulate the data stored in database tables. It allows users to insert, update, delete, and retrieve data from a database.
Main DML Commands
• INSERT
• UPDATE
• DELETE
• SELECT
1. INSERT Statement
The INSERT statement is used to add new records into a table.
Syntax
INSERT INTO table_name (column1, column2, column3, ...) VALUES (value1, value2, value3, ...);
Example
INSERT INTO Student (StudentID, Name, Class, Age) VALUES (101, 'Hari Thapa', 10, 15);
Alternative Syntax
If values are provided for all columns in the correct order, column names may be omitted.
INSERT INTO Student VALUES (101, 'Hari Thapa', 10, 15);
2. UPDATE Statement
The UPDATE statement is used to modify existing records in a table.
Syntax
UPDATE table_name SET column_name = value WHERE condition;
Example
UPDATE Student
SET Age = 16
WHERE StudentID = 101;
3. DELETE Statement
The DELETE statement is used to remove records from a table.
Syntax
DELETE FROM table_name WHERE condition;
Example
DELETE FROM Student WHERE StudentID = 101;
4. SELECT Statement
The SELECT statement is used to retrieve data from one or more tables.


 
  
  StudentID
  
  
  Name
  
  
  Class
  
  
  Age
  
 
 
  
  101
  
  
  Hari Thapa
  
  
  10
  
  
  15
  
 
 
  
  102
  
  
  Sita Sharma
  
  
  9
  
  
  14
  
 
 
  
  103
  
  
  Ram Gurung
  
  
  10
  
  
  16
  
 
Syntax (Specific Columns)
SELECT column1, column 2FROM table name;
Example
SELECT Name, Class FROM Student;
Output

 
  
  Name
  
  
  Class
  
 
 
  
  Hari Thapa
  
  
  10
  
 
 
  
  Sita Sharma
  
  
  9
  
 
 
  
  Ram Gurung
  
  
  10
  
 
This query displays only the Name and Class columns from the Student table.
Syntax (All Columns)
SELECT * FROM tablename;
Example
SELECT * FROM Student;
Output

 
  
  StudentID
  
  
  Name
  
  
  Class
  
  
  Age
  
 
 
  
  101
  
  
  Hari Thapa
  
  
  10
  
  
  15
  
 
 
  
  102
  
  
  Sita Sharma
  
  
  9
  
  
  14
  
 
 
  
  103
  
  
  Ram Gurung
  
  
  10
  
  
  16
  
 
The asterisk (*) symbol is used to display all columns and all records from the table.

StudentID	Name	Class	Age
101	Hari Thapa	10	15
102	Sita Sharma	9	14
103	Ram Gurung	10	16

Unit-2 Database Management System

Unit-2 Database Management System

Data

Information

Example

Difference Between Data and Information

Table

Database

Examples of Database

Importance of Database

Applications of Database

Database Management System (DBMS)

Examples of DBMS

Recent Trends in Database Technology

Features of DBMS

Benefits of DBMS

Challenges of DBMS

RDBMS

Examples

Uses of RDBMS

Difference Between Database and DBMS

Data Types

1. Numeric Data Types

2. Character (Text) Data Types

3. Date and Time Data Types

4. Boolean Data Type

Table (Relation), Field, records and Tuple

1. Table (relation)

2. Field (Column / Attribute)

3. Record (Row)

4. Tuple

Keys in Database

Primary Key

Characteristics of Primary Key

Importance of Primary Key

Rules for Defining Primary Key

Example: Table Student

Foreign Key

Features of Foreign Key

Rules of Foreign Key

Relationship

Types of Relationships in DBMS

1. One-to-One (1:1) Relationship

2. One-to-Many (1:M) Relationship

3. Many-to-Many (M:N) Relationship

SQL (Structured Query Language)

Features of SQL

DDL Statements

1. CREATE Statement

2. DROP Statement

3. TRUNCATE TABLE

4. ALTER COMMAND

a. ALTER TABLE - ADD Column

b. ALTER TABLE - DROP COLUMN

c.. ALTER TABLE – MODIFY COLUMN

Syntax

Example

d. ALTER TABLE – RENAME COLUMN

Syntax

Example

e. ALTER TABLE – RENAME TABLE

Syntax

Example

5. RENAME TABLE

The RENAME TABLE command is used to change the name of an existing table.It is possible to change the name of a table whether it contains data or not using the simple RENAME TABLE command.

Syntax

RENAME TABLE table_name TO new_table_name;

Example

RENAME TABLE students TO grade12;

DML (Data Manipulation Language) Commands

DML (Data Manipulation Language) is used to manipulate the data stored in database tables. It allows users to insert, update, delete, and retrieve data from a database.

Main DML Commands

• INSERT• UPDATE• DELETE• SELECT

1. INSERT Statement

The INSERT statement is used to add new records into a table.

Syntax

INSERT INTO table_name (column1, column2, column3, ...) VALUES (value1, value2, value3, ...);

Example

INSERT INTO Student (StudentID, Name, Class, Age) VALUES (101, 'Hari Thapa', 10, 15);

Alternative Syntax

The RENAME TABLE command is used to change the name of an existing table.
It is possible to change the name of a table whether it contains data or not using the simple RENAME TABLE command.

`RENAME TABLE table_name TO new_table_name;`

`RENAME TABLE students TO grade12;`

`DML (Data Manipulation Language) is used to manipulate the data stored in database tables. It allows users to insert, update, delete, and retrieve data from a database.`

`• INSERT • UPDATE • DELETE • SELECT`

`The INSERT statement is used to add new records into a table.`

`INSERT INTO table_name (column1, column2, column3, ...) VALUES (value1, value2, value3, ...);`

`INSERT INTO Student (StudentID, Name, Class, Age) VALUES (101, 'Hari Thapa', 10, 15);`

`If values are provided for all columns in the correct order, column names may be omitted. INSERT INTO Student VALUES (101, 'Hari Thapa', 10, 15);`

`The UPDATE statement is used to modify existing records in a table.`

`UPDATE table_name SET column_name = value WHERE condition;`

`UPDATE Student SET Age = 16 WHERE StudentID = 101;`

`The DELETE statement is used to remove records from a table.`

`DELETE FROM table_name WHERE condition;`

`DELETE FROM Student WHERE StudentID = 101;`

`The SELECT statement is used to retrieve data from one or more tables.`

StudentID

Name

Class

Age

101

Hari Thapa

10

15

102

Sita Sharma

9

14

103

Ram Gurung

10

16

Syntax (Specific Columns)

`SELECT column1, column 2FROM table name;`

`SELECT Name, Class FROM Student;`

`Name Class Hari Thapa 10 Sita Sharma 9 Ram Gurung 10 This query displays only the Name and Class columns from the Student table.`

`SELECT * FROM tablename;`

`SELECT * FROM Student;`

`StudentID Name Class Age 101 Hari Thapa 10 15 102 Sita Sharma 9 14 103 Ram Gurung 10 16 The asterisk (*) symbol is used to display all columns and all records from the table.`