Database Management System

(DBMS)
Unit ⇒ 1
 1.1 Introduction
• Data, Information, Database and DBMS
 Data
• Data is the raw fact which is composed of
alphabets, digits, special symbols, etc.
• Example: Ram, 26, Mahendranagar, etc.
 Information
• The processed data that gives meaningful
result is called information.
• It is an organised collection of related data
which gives a complete sense.
• Example: Ram is 26 years old who lives in
Mahendranagar.
 Database
• It is a collection of interrelated data of objects
or entities in a structured/tabular form.
• Example: customer record, books library, etc.
 What is DBMS?
• DBMS is a software that defines, manipulates
and manages the database. It allows access,
update and retrieval of data as requested.
• Examples: MS Access, Oracle, MySQL, etc.
 Some Basic Terms in DBMS
i) Field: Property/attribute of table. E.g., name,
phone number.
ii) Record/tuple: Collection of interrelated fields.
E.g., 1, Ram.
iii) Table: Arrangement of rows and columns.
iv) Object: Used in database to store and
manipulate data.
v) Primary key: Primary key is a unique key/value in database which is
used to show the relationship between different tables or objects and
also helps in reducing data duplicacy. For example, registration
number, citizenship number, etc.

vi) Alternate key: Alternate key is not a primary key, but combining
one or more columns can make it unique. It can also be used instead of
primary key.

vii) Candidate key: Sometimes there is more than one attribute

processing the unique identification property. In this case all attribute
combination provide a unique record which is called candidate key.

viii) Foreign Key: A foreign key is a column or set of columns in a table

that refers to the primary key of another table. It establishes a
relationship between two tables and enforces referential integrity.
 Relational Table Example (Keys)
Reg. No Name contact Email

101 Rameshowr 9800000001 ram@[Link]

102 Shyam 9800000002 shyam@[Link]

Candidate key 1 Candidate key 2 Candidate key 3

Reg. No Phone Email

101 9800000001 ram@[Link]

102 9800000002 shyam@[Link]

Primary key alternate key1 alternate key2

Reg. No Phone Email

101 9800000001 ram@[Link]

102 9800000002 shyam@[Link]

 1.3 Advantages of DBMS
i. Reduce Redundancy→ IT stores everything in one place,
so you don’t have duplicate copies lying around.
ii. Easy Sharing → Lets people quickly access and work on
files together.
iii. Combines Data Sources → It pulls info from different
places into one view for better insights.
iv. Auto Backup & Restore → Regularly saves your data and
recovers it if anything gets lost.
v. Strong Security → Uses passwords and locks to keep
private info safe from hackers.
vi. Works Anywhere → Accessible on phones, computers, or
other devices—anytime, anywhere.
 Disadvantages of DBMS
i. Expensive→ Buying equipment and software is
expensive.
ii. Gets old fast → Tech changes quickly, so systems
become outdated.
iii. Training needed → Staff must keep learning new
skills.
iv. Hacking risks → Hackers can steal private data.
v. Hard to connect → Doesn’t work smoothly with older
systems.
vi. Can’t handle growth → Struggles when more users
or data arrive.
 DDL and DML Overview
i)DDL: (Data Defination Language)Defines structure
of table (CREATE, DROP, RENAME)
• Example DDL: CREATE TABLE Stu (S_id INT(4),
S_name CHAR(64));

ii)DML(Data Manipulation Language) Manipulation

of records (INSERT, DELETE, SELECT, UPDATE)
• Example DML: INSERT INTO Stu VALUES (1,
'Ram');
 Differences between DDL and DML
DML(Data
DDL(Data Defination
Feature Manipulation
Language)
Language)
It is a type of SQL
It is a type of SQL
command that helps to
command that helps to
Definition retrieve and manage
define database
data in relational
schemes.
databases.

Used during the design Used during daily

Execution or restructuring of a operations to handle
database schema. data within tables.

CREATE, DROP, ALTER INSERT, UPDATE,

Examples are some DDL DELETE are some DML
commands. commands.

Commands affect the Commands affect one

Effect entire database or the or more records in a
table. table.
SQL statements cannot SQL statements can be
Rollback
be rolled back. rolled back.
1.5 Database Model
➢There are different forms of database
management systems, they are:
1) Hierarchical Model:
➢It is one of the oldest types of database models in
which all records are arranged in a tree-like
structure. In this model, the records are in a
parent-child relationship basis in which children
have only one parent, but parents can have many
children. Also, the top-level record gets higher
priority than the child at the bottom level.
For example:
➢Advantages of Hierarchical Model:
I. The advantages of the hierarchical model are
mentioned below:
II. It is easiest model to implement.
III. It supports one-to-one and one-to-many
relationships.
IV. It is Highly secure model.
V. Searching is easy if the parent is known.
➢Disadvantages of Hierarchical Model
The disadvantages of the hierarchical model are
as follows:
I. It is old-fashioned and outdated.
II. It does not support many-to-one
relationships.
III. It increases data duplicacy (redundancy).
IV. Its dependency is only on the single parent
node.
V. If the parent node is disturbed, then the
whole hierarchical model may be disturbed.
Uses or Application Areas of Hierarchical Database Model
• IBM IMS (Information Management System): Widely used in
banking, insurance, and airline reservation systems (e.g.,
IBM’s SABRE for flight bookings).
• Healthcare: Early hospital record systems stored patient data
hierarchically (e.g., insurance claims linked to patient records).
• Operating Systems: File directories (e.g., Windows Registry,
UNIX/Linux file systems) use hierarchies to organize files and
folders.
• Network Management: Models network topologies (e.g.,
parent nodes as central servers, children as connected
devices).
2 )Network model: It replaces hierarchical
model due to some limitations. The structure of
database is more like graph rather than tree-like
structure. In this model, children can have
multiple parents.
For example:
➢Advantages of Network Model
The advantages of the network model are
mentioned below:
I. It supports many-to-one relationships.
II. In this model searching is faster.
III. It is Simple and easy to design.
IV. It reduces data duplicacy.
V. It is good for dynamic database.
➢ Disadvantages of network model
The disadvantages of network model are mentioned
below:
i) It is less secure.
ii) Difficult for complex relationship.
iii) Rigid Structure: Changing the database layout
requires reprogramming.
iv) Complex to Design and Maintain: Initial setup is
complicated and ongoing changes are difficult.
 Uses or Application Areas of Network
Database Model
• Industrial Systems:
– Manufacturing workflows (e.g., tracking parts shared
across multiple assemblies).
– Supply chain networks with interconnected suppliers
and distributors.
• Knowledge Graphs: Organizing semantic data
(e.g., early AI systems).
• Transportation Routing: Mapping
roads/junctions (e.g., flight paths with shared
hubs).
3) Relational database model: The database
model which stores and displays data in tabular
form (ie rows and columns) is known as
relational database model. For example, My
SQL, Oracle, Ms-Access etc.
➢Advantages of relational database model
The advantages of relational database model
are as follows:
I. Database processing is faster.
II. It has low chances of data duplicacy.
III. Data integrity can be easily implemented.
IV. It is Highly secured.
➢Disadvantages of Relational Database Model
I. It is More complex than other models.
II. It may be difficult to understand.
III. Role overload: Sometimes too many roles
may be created because of complex
relationships.
IV. Can be slow for big data → When the
database grows very large, operations may
take longer to complete.
 Uses or Application Areas of Relational Database Model

1. Enterprise Systems
Business Operations:
• ERP (SAP, Oracle): Manages finance, HR, supply chain.
• CRM (Salesforce): Tracks customer interactions and sales pipelines.
E-commerce: Product catalogs, order processing (e.g., Shopify, Amazon backend).
2. Financial Services
• Banking: Transaction records, account management (e.g., core banking systems).
• Stock Markets: Trade settlements, portfolio tracking.
3. Healthcare
• Electronic Health Records (EHR): Patient histories, prescriptions (e.g., Epic, Cerner).
• Medical Research: Clinical trial data storage and analysis.
4. Web & Mobile Applications
• User Data: Profiles, authentication (e.g., MySQL for social media platforms).
• Content Management: WordPress, Drupal for dynamic websites.
5. Government & Education
• Public Records: Tax databases, census data.
• Learning Management Systems (LMS): Student enrollments, grades (e.g., Moodle).
6. Logistics & Supply Chain
• Inventory Management: Real-time stock levels, supplier tracking (e.g., Walmart’s systems).
• Shipping: Route optimization, package tracking (FedEx, UPS).
❖ ER Model (Entity-Relationship Model) or ER-Diagram
➢Shows relationships between real-life entities
and their attributes.
• Components of ER Diagram
1. Entity
• Represents real-life objects(nodes) (e.g., student,
library, hospital).
• Symbol: Rectangular shape.
2. Attributes
• Characteristics of an entity (e.g., single-valued,
multi-valued, derived).
• Symbol: Oval shape. -> single valued
-> multi valued

-> derived valued

3)Relation
– Shows logical coordination between two or more
entities.
– Symbol: Diamond shape

4)Lines (Connectors)
– Link entities, attributes, and relations.
– Symbol: Single straight line
 DOB

age
Phone-no
➢Advantages of ER Model
• The advantages of ER model are mentioned
below:
i) Easy to represent and understand the flow
of data as it is in pictorial form.
ii) Helps in creating a link between several
entities.
iii) Easy to implement ER-diagram into DBMS.
iv) "Facilitates effective communication
between technical and non-technical
teams."
➢The disadvantages of ER-model are
mentioned below:
i)It may become complex with more no. of
relations.
ii) Users need to remember symbols.
iii) It cannot easily represent recursive
relationships.
iv) It lacks support for defining operations or
methods.
1.6 Centralized vs Distributed Database
❖Centralized Database
➢Definition: It is a simple type of database
which works on client-server basis. In this
type, the database is centralized and runs on a
single computer system.
A

DB Server B

» Fig: Centralized Database System

➢Advantages of centralized database
The advantages of centralized database are as
follows:

i) It is suitable for smaller organization.

ii) It has high security.
iii) Maintenance of database is easier.
iv) It Ensures strong data consistency.
➢Disadvantages of centralized database
The disadvantages of centralized database are
as follows:
i) It is dependent on central server. If the
server goes down, the whole system will
be disturbed.
ii) One breach exposes all data – like stealing
the whole cabinet, not just a folder.”
iii) Not suitable for large organization.
iv) Not suitable for huge data traffic.
❖Distributed database:
➢It is a complex type of database system which
is a collection of multiple logically interrelated
databases which may be in different
geographical locations. In other words, it is a
collection of different centralized databases in
different locations.
Fig: Distributed Database system
➢Advantages of distributed database

i) It can handle huge data traffic at the same

time.
ii) Backup and recovery is easier.
iii) It is suitable for large organization.
iv) Faster for global users → Stores data near
where it's used, so someone in Tokyo doesn’t
need to wait for data from New York.
Disadvantages of Distributed Database
I. It is highly expensive.
II. It is highly complex.
III. It may have several security issues.
IV. Maintenance is difficult.
 Differences Between Centralized and Distributed Database

Centralized Database Distributed Database

1) It depends upon a central server. 1) It has several servers.

2) Cannot handle huge traffic 2) Can handle huge traffic

simultaneously. simultaneously

3) Maintenance is easier. 3) Maintenance is difficult.

4) More secure. 4) Less secure.

5) Not suitable for large organizations. 5) Suitable for large organizations.

1.7 Concept of Normalization
➢Definition:
Normalization is a process in which a complex
database table is broken down into separate
tables. This makes the data model more
flexible and easier to maintain, reducing the
complexity of the database.
❖Types of Normalization
a) 1NF (First Normal Form)
➢Rules:
I. The data field must be atomic (single
record).
II. It Eliminates duplicate rows and columns in
the same table.
III. It Minimizes data redundancy in the
database table.
❖ 2NF (Second Normal Form)
➢2NF builds upon the First Normal Form (1NF) by
further addressing the removal of duplicate data.

➢ Rules to qualify as 2NF:

I. A table must already satisfy all 1NF requirements.
II. It enforces data dependency rules i.e All non-key
attributes must be functionally dependent on the
entire primary key (or key attributes), not just part of
it.
❖3NF (Third Normal Form)
➢Rules to satisfy 3NF:
I. The data must already be in Second Normal
Form (2NF).
II. It must eliminate transitive dependencies.
Here transitive dependency means, if A->B
and B->C, then A->C.
[Notes: 3NF requires removing such indirect dependencies by splitting
tables appropriately.]
1.8 Data Security
❖Definition:
Data security means protection of
hardware/software data essential for DBMS.
1) Hardware Security:
i. Regular maintenance: Check and fix hardware
parts often to avoid sudden breakdowns.
ii. Control temperature: Use cooling systems to
stop overheating, which can damage hardware.
iii. Keep dust out: Use filters and covers to prevent
dust from causing overheating or short circuits.
iv. Stable power: Use surge protectors and UPS to
guard against power surges that can break
hardware or lose data.
2) Software security:
i. Use genuine software:
Only use software that's official and paid for (or from trusted
sources). Why? Because fake or cracked software might have hidden
viruses or security holes that aren't fixed.
ii. Use strong anti-virus and update it regularly:
Install good anti-virus to catch and remove viruses, and keep it
updated so it knows about new threats.
iii. Use a strong firewall:
A firewall is like a security guard for your internet connection it
blocks hackers and bad programs from getting in or out without
permission.
iv. Use proper authentication techniques:
Add extra steps to log in, like your fingerprint, a code sent to your
phone (OTP), or a PIN. This makes it much harder for someone else to
get into your accounts.
v. Use a VPN if necessary:
When you're on public Wi-Fi (like at a cafe or airport), use a VPN to
scramble your internet activity so snoopers can't see what you’re
doing.
LAB WORKS
 Data Defination Language (DDL) COMMANDS

1) create create database statement

create table statement

2) drop drop database statement

drop table statement

3) alter alter table add-column statement.

alter table modify-column statement
alter table drop-column statement
Click here
Click here
[Link] a DDL command to create two new databases in
SQL using 'CREATE DATABASE' statement.
Query:
1) create database milan;
2) create database roshan;
➢ show databases;
Output:
 [Link] a DDL command to create two new table under the
existing database using 'CREATE TABLE' statement. [ Assign primary
key constraint to a unique attribute]
Query:
➢ use roshan;
1) create table science(roll int primary key, name varchar(25),
grade int);
2) create table management(roll int primary key, name
varchar(25), address varchar(25));
➢ show tables;
➢describe science;

➢describe management;
3) Demonstrate a DDL command to remove an existing
database using 'DROP DATABASE' statement.
Query:
1) drop database milan;
➢ show databases;
4) Demonstrate a DDL command to remove an
existing table using 'DROP TABLE' statement.
Query:
1. drop table science;
➢show tables;
5) (A)Demonstrate a DDL command to ALTER an existing table
using a 'alter table add-column' statement.
Query:
1) alter table management add age int, add gpa int, add
salary float;
➢ describe management;
5) (B)Demonstrate a DDL command to ALTER an existing table
using a 'alter table modify-column' statement.
Query:
1) alter table management modify salary int, modify gpa
float;
➢ describe management;
5) (C)Demonstrate a DDL command to ALTER an existing table
using a 'alter table drop-column' statement.
Query:
1) alter table management drop salary;
➢ describe management;
 Data Manipulationa Language (DML) COMMANDS

1. insert insert into table_name

2. select select-all column_name from table_name (use *)

select –a single column_name from table_name.
select -a column_name from table_name using
WHERE (clause)
select - any column_name from table_name ORDER
BY column_name ASC|DESC.

3. update update table_name set column_name WHERE clause

4. delete delete from table table_name WHERE column(condition)

6) Demonstrate a DML command to add/insert new records to an
existing table from SQL database using 'INSERT' statement.
Query:
(1)
insert into management values(1,'Akash','Kathmandu',
14,3.2),(2,'Bikash','bHaktapur',15,3.6),(3,'Prakash','Lalitpur',16,4.0
);
7) (A) Demonstrate a DML command to select all
records from each column from the existing table using
‘SELECT *’ .
Query:
1) select * from management;
7) (B) Demonstrate a DML command to select all
records from any single column from the existing table
using 'SELECT’.
Query
1) select roll from management;
7) (C) Demonstrate a DML command to select single
record from all column from the existing table using
'SELECT *’ and ‘WHERE’ clause.
Query
1) select * from management where roll=1;
7) (D) Demonstrate a DML command to select any
column from table_name ORDER BY column_name
‘ASC|DESC’.

Query
1) select * from management order by roll desc;
Query
2) select * from management order by roll asc;
8) Demonstrate a DML command to update data to the
existing records in a table using 'UPDATE' statement.
Query
3)update management set address= 'Bharatpur' where roll=2;
➢ select * from management;
9) Demonstrate a DML command to delete any records
from a table using 'DELETE' statement.
Query:
1) delete from management where gpa>3.6;
➢ select *from management;
THE END