Database Management

System (CS212)
Ms. Nida Khan
Assistant Professor
What is DBMS?
•Definition:
A Database Management System (DBMS) is software that allows users to
define, create, maintain, and control access to a database.

•Key Features:
•Efficient data storage
•Easy data retrieval
•Ensures data integrity and security
•Supports multiple users
Why DBMS?
Need for DBMS:
•Handles large volumes of data systematically.
•Reduces redundancy and inconsistency.
•Provides better data security.
•Allows concurrent access by multiple users.

Traditional vs. Modern Data Management:

File system limitations vs. DBMS advantages.
Components of DBMS
1. Database: Organized collection of data.
2. DBMS Software: Interfaces between the database and users.
3. Hardware: Servers and storage devices.
4. Users:
•End Users: Interact directly.
•Database Administrators (DBAs): Manage and maintain the database.
•Application Developers: Develop database-related applications.
Functions of DBMS
Data Definition:
•Defining schema and structure of the database.
Data Storage and Retrieval:
•Storing, retrieving, and updating data efficiently.
Transaction Management:
•Ensures ACID properties (Atomicity, Consistency, Isolation, Durability).
Security and Authorization:
•Restricts unauthorized access.
Backup and Recovery:
•Protects data against loss.
Types of DBMS

1. Hierarchical DBMS: Data is organized in a tree-like structure.

2. Network DBMS: Data is organized using a graph structure.
3. Relational DBMS (RDBMS): Data is stored in tables (most
widely used).
4. Object-Oriented DBMS: Integrates database functionality
with object programming.
Advantages of DBMS
1. Data Abstraction: Simplifies data access.
2. Reduces Data Redundancy: Eliminates duplicate data.
3. Data Integrity: Maintains accuracy and consistency.
4. Enhanced Security: Protects sensitive data.
5. Scalability: Easily adapts to growing data needs.
Disadvantages of DBMS

1. High Cost: Development and maintenance can be expensive.

2. Complexity: Requires technical expertise.
3. Performance: Slower for small applications.
4. Dependency: Tightly coupled with the organization’s infrastructure.
Real-Life Applications of DBMS
1. Banking: Managing accounts, transactions, and loans.
2. E-commerce: Inventory management, user data, and transactions.
3. Education: Student records and course management.
4. Healthcare: Patient records and appointment scheduling.
5. Social Media: User profiles and content management.
DBMS Architecture
• 1-Tier Architecture
• 2-Tier Architecture
• 3-Tier Architecture
1-Tier Architecture
• In 1-tier architecture, the database is directly accessible to the
user.
• The user interacts directly with the database for query
execution.
• This architecture is commonly used in development and testing
environments.

Single-layer connection
where the user directly
accesses the database.
• Suitable for single-user applications.
• Lacks security and scalability for large-scale use.
• Example: A developer directly interacting with a
database using SQL queries.
2-Tier Architecture
•In 2-tier architecture, the database system is divided into two
layers:
[Link]: The user interface where requests are initiated.
[Link]: The database server that processes queries and
returns results.
•The client communicates directly with the database server.

Arrows represent direct

communication between the client
and the server.
•Provides better security compared to 1-tier.
•Suitable for small-scale, departmental applications.
•Example: Desktop applications like MS Access.
3-Tier Architecture
• In 3-tier architecture, the system is divided into three layers:
[Link] Layer (Client): User interface for input and display.
[Link] Layer (Middleware): Processes business logic and
communicates between client and database.
[Link] Layer: Manages data storage, retrieval, and
transactions.
•Users do not interact directly with the database, enhancing security
and scalability

Arrows represent the flow of data and

requests.
• Highly secure, scalable, and suitable for enterprise-level
applications.
Example: Web applications (e.g., online banking systems).
Data Models in DBMS
• Data models in DBMS help to understand the design at
the conceptual, physical, and logical levels as it
provides a clear picture of the data making it easier for
developers to create a physical database.
• Data Models provide us with a transparent picture of
data which helps us in creating an actual database.
Types of Data Models in DBMS
• Hierarchical Model
• Network Model
• Entity-Relationship Model (ER Model)
• Relational Model
• Object-Oriented Data model
Hierarchical Model
• In this data model, the
data is organized in a
hierarchical tree-like
structure. This data model
can be easily visualized
because each record of
DBMS has one parent and
many children (possibly
0).
Network Model
•A network model is
nothing but
a generalization of the
hierarchical data
model as this data model
allows many to many
relationships therefore in
this model a record can
also have more than one
parent.
Entity-Relationship Model (ER
Model)
• An Entity-Relationship
model is a high-level
data model that
describes the
structure of the databa
se
in a pictorial form
which is known as ER-
diagram.
• In simple words, an ER
diagram is used to
represent logical
structure of the database
Relational Model
• This is the most widely
accepted data
model. In this model, the
database is represented
as a collection of
relations in the form of
rows and columns of a
two-dimensional
table. Each row is known
as a tuple while each
column represents
an attribute.
Object-Oriented Data model
• the object-oriented data
model is a combination of
object-oriented
programming and
relational data model.
• In this data model, the
data and their relationship
are represented in a single
structure which is known
as an object.
Database Languages and
Interface
Database Languages
DDL(Data Definition Language)
• Data Definition Language(DDL) is used for describing
structures or patterns and its relationship in a database.
• The commands only affect the database structure and
not the data.
The commands used in DDL are:

• Create: It is used to create a database or table.

• Alter: It is used to make a change in the structure of a
database.
• Drop: It is used to completely delete a table from the
database
• Rename: It is used to rename a table.
• Truncate: It is used to delete the entities inside the
table while holding the structure of the table.
• Comment: It is used to comment on the data
dictionary.
DML(Data Manipulation Language)
• DML is used to manipulate the data present in the table or
database. We can easily perform operations such as store,
modify, update, and delete on the database.
• The commands used in DML are:
• Select: It shows the record of the specific table. Also, it can be
used with a WHERE clause to get the particular record.
• Insert: It allows users to insert data into the database or tables.
• Update: It is used to update or modify the existing data in
database tables.
• Delete: It is used to delete records from the database tables.
Also, it can be used with a WHERE clause to delete a particular
row from the table.
• Merge: It allows the insert and update(UPSERT) operations.
DCL(Data Control Language)
• DCL works to deal with SQL commands that are used to
permit a user to access, modify and work on a
database. it is used to access stored data. It gives
access, revokes access, and changes the permission to
the owner of the database as per the requirement.
• The commands used in DCL are:
• Grant: It is used to give access to security privileges to
a specific database user.
• Revoke: It is used to revoke the access from the user
that is being granted by the grant command.
TCL(Transaction Control Language)
• It can be grouped into a logical transaction and is used
to run the changes made by the DML command in the
database.
• Commit: Transaction on the database is saved using
Commit.
• Rollback: The database gets restored to the original
since the last commit.
Interface
• An interface is a program that allows users to input
queries into a database without writing the code in the
query language.
• An interface can be used to manipulate the database for
adding, deleting, updating, or viewing the data.
Types of Interface-
• Form−based Interface
A form is displayed to each user by the form−based
interface. The user fills in the details and submits the
form to make a new entry into the database.
It can also be done when the user only fills in some
details and the system will help by retrieving the rest of
the details from the database.
• Example
Student entering his roll. no, branch in the form to get
the grade card.
Menu−based User Interface
• In this interface, the user was provided with a list of
options (called a menu) through which the user forms a
request.
• Example
In a shopping website, categories are selected from the
menu, brands are selected from the menu of brands, and
budget ranges are applied from the menu of budget
range.
GUI(Graphical User Interface)
• Users are provided a schema of diagrammatic form by
which query can be specified through manipulating the
diagram.
• Example
You liked a video on Instagram by tapping with your
finger, and the color changes to red. The visual graphic
gets changed due to user action.
Natural Language Interface
• A natural language interface contains its unique schema
more like the high−level conceptual schema. It also has a
directory of important words.
• It generates a query based on the interpretation of
important words in the input by the user and if the
interpretation is successful, then it displays the result to the
user.
• Example
A user googled the fastest car in India, and now the natural
language interface will look for the important words i. e.
fastest, car, India, and show the result accordingly.
Speech Input and Output
• The users query the interface with speech and get the
answer in speech. The input is detected using
predefined words and conversions are done into speech
to provide the output. Nowadays, it has become the
most common type of interface.
• Example
OK Google, Siri on Apple, and Alexa is used in the form of
speech.
Interface for DBA
• DBA staff are provided commands that can only be used
by them only to create an account, grant account
authorization, and change a schema, and storage
structure reorganization.
Databa
se
Structu
re
Data modeling using the entity
relationship model
ER (Entity Relationship) Diagram
• ER model stands for an Entity-Relationship model. It is a
high-level data model. This model is used to define the
data elements and relationship for a specified system.
• It develops a conceptual design for the database. It also
develops a very simple and easy to design view of data.
• In ER modeling, the database structure is portrayed as a
diagram called an entity-relationship diagram.
Component of ER Diagram
1. Entity:
• An entity may be any
object, class, person or
place. In the ER diagram,
an entity can be
represented as rectangles.
• Consider an organization
as an example- manager,
product, employee,
department etc. can be
taken as an entity.
Weak Entity
• An entity that depends on
another entity called a
weak entity. The weak
entity doesn't contain any
key attribute of its own.
The weak entity is
represented by a double
rectangle.
 Attribute
• The attribute is used to
describe the property of
an entity. Eclipse is used
to represent an attribute.
• For example, id, age,
contact number, name,
etc. can be attributes of a
student.
a. Key Attribute
• The key attribute is used
to represent the main
characteristics of an
entity. It represents a
primary key. The key
attribute is represented by
an ellipse with the text
underlined.
b. Composite Attribute
• An attribute that
composed of many other
attributes is known as a
composite attribute. The
composite attribute is
represented by an ellipse,
and those ellipses are
connected with an ellipse.
c. Multivalued Attribute
• An attribute can have
more than one value.
These attributes are
known as a multivalued
attribute. The double oval
is used to represent
multivalued attribute.
• For example, a student
can have more than one
phone number.
d. Derived Attribute
• An attribute that can be
derived from other
attribute is known as a
derived attribute. It can
be represented by a
dashed ellipse.
• For example, A person's
age changes over time
and can be derived from
another attribute like Date
of birth.
Relationship
• A relationship is used to
describe the relation
between entities.
Diamond or rhombus is
used to represent the
relationship.
Types of relationship
a. One-to-One Relationship
• When only one instance of
an entity is associated
with the relationship, then
it is known as one to one
relationship.
• For example, A female
can marry to one male,
and a male can marry to
one female.
b. One-to-many relationship
• When only one instance of
the entity on the left, and
more than one instance of
an entity on the right
associates with the
relationship then this is
known as a one-to-many
relationship.
• For example, Scientist
can invent many
inventions, but the
invention is done by the
c. Many-to-one relationship
• When more than one
instance of the entity on
the left, and only one
instance of an entity on
the right associates with
the relationship then it is
known as a many-to-one
relationship.
• For example, Student
enrolls for only one
course, but a course can
have many students.
d. Many-to-many relationship
• When more than one instance
of the entity on the left, and
more than one instance of an
entity on the right associates
with the relationship then it is
known as a many-to-many
relationship.
• For example, Employee can
assign by many projects and
project can have many
employees.
Notation of ER diagram
• Database can be
represented using the
notations. In ER diagram,
many notations are used
to express the cardinality.
These notations are as
follows:
Example based on ER Diagram
 List of scenarios where ER
Models are commonly used:
[Link] Management System [Link] Rental System
2.E-Commerce System [Link] Relationship Management
[Link] Database (CRM)
[Link] Management System [Link] Reservation System
[Link] System [Link] Management System
[Link] Management System [Link] Management System
[Link] Reservation System [Link] Delivery System
[Link] Media Platform [Link] Streaming Application
[Link] Examination System [Link] Management System
[Link] Payroll System [Link] Point of Sale System
[Link] Management System
Cardinality in DBMS (Mapping
Constraints)
• Cardinality means how the entities are arranged to each
other or what is the relationship structure between
entities in a relationship set.
• In a Database Management System, Cardinality
represents a number that denotes how many times an
entity is participating with another entity in a
relationship set.
• In a table, the number of rows or tuples represents the
Cardinality.
Cardinality Ratio
• Cardinality ratio is also called Cardinality Mapping,
which represents the mapping of one entity set to
another entity set in a relationship set.
• We generally take the example of a binary relationship
set where two entities are mapped to each other.
• There are four types of Cardinality Mapping in Database
Management Systems:
[Link] to one
[Link] to one
[Link] to many
[Link] to many
One to One
• One to one cardinality is
represented by
a 1:1 symbol. In this, there
is at most one relationship
from one entity to another
entity. There are a lot of
examples of one-to-one
cardinality in real life
databases.
• For example, one student
can have only one student
id.
 Many to One Cardinality:
• In many to one cardinality
mapping, from set 1, there can
be multiple sets that can make
relationships with a single
entity of set 2.
• It can be represented by M:1.
• For example, there are
multiple patients in a hospital
who are served by a single
doctor, so the relationship
between patients and doctors
can be represented by Many to
one Cardinality.
One to Many Cardinalities:
• In One-to-many cardinality
mapping, from set 1, there
can be a maximum single set
that can make relationships
with a single or more than one
entity of set 2.
• It can be represented by 1: M.
• For Example, in a hospital,
there can be various
compounders, so the
relationship between the
hospital and compounders
can be mapped through One-
to-many Cardinality.
Many to Many Cardinalities:
• n many, many cardinalities
mapping, there can be one or
more than one entity that can
associate with one or more
than one entity of set 2.
• It is represented by M:
N or N: M.
• For Example, in a college,
multiple students can work on
a single project, and a single
student can also work on
multiple projects.
Keys
• Keys play an important role in the relational
database.
• It is used to uniquely identify any record or row
of data from the table. It is also used to establish
and identify relationships between tables.
• For example, ID is used as a key in the Student table
because it is unique for each student. In the PERSON
table, passport_number, license_number are keys since
they are unique for each person.
Types of keys:
Primary key
• It is the first key used to
identify one and only one
instance of an entity
uniquely.
• An entity can contain
multiple keys.
• The key which is most
suitable from those lists
becomes a primary key.
Candidate key
• A candidate key is an
attribute or set of
attributes that can
uniquely identify a tuple.
• Except for the primary
key, the remaining
attributes are considered
a candidate key. The
candidate keys are as
strong as the primary key.
Super Key
• Super key is an attribute
set that can uniquely
identify a tuple. A super
key is a superset of a
candidate key.
• The super key would be
EMPLOYEE-ID
(EMPLOYEE_ID, EMPLOYEE-
NAME), etc.
 Foreign key
• Foreign keys are the column of
the table used to point to the
primary key of another table.
• We add the primary key of the
DEPARTMENT table,
Department_Id, as a new
attribute in the EMPLOYEE
table.
• In the EMPLOYEE table,
Department_Id is the foreign
key, and both the tables are
related.
 Alternate key
• There may be one or more
attributes or a combination of
attributes that uniquely identify
each tuple in a relation.
• These attributes or
combinations of the attributes
are called the candidate keys.
• One key is chosen as the
primary key from these
candidate keys, and the
remaining candidate key, if it
exists, is termed the alternate
key.
Composite key
• Whenever a primary key
consists of more than one
attribute, it is known as a
composite key. This key is
also known as
Concatenated Key.
 Artificial key
• The key created using
arbitrarily assigned data are
known as artificial keys.
• These keys are created
when a primary key is large
and complex and has no
relationship with many other
relations.
• The data values of the
artificial keys are usually
numbered in a serial order.
Generalization
• Generalization is like a bottom-up approach in which
two or more entities of lower level combine to form a
higher level entity if they have some attributes in
common.
• In generalization, an entity of a higher level can also
combine with the entities of the lower level to form a
further higher level entity.
• Generalization is more like subclass and superclass
system, but the only difference is the approach.
Generalization uses the bottom-up approach.
• In generalization, entities are combined to form a more
generalized entity, i.e., subclasses are combined to
• For example, Faculty and Student entities can be
generalized and create a higher level entity Person.
Specialization
• Specialization is a top-down approach, and it is opposite
to Generalization. In specialization, one higher level
entity can be broken down into two lower level entities.
• Specialization is used to identify the subset of an entity
set that shares some distinguishing characteristics.
• Normally, the superclass is defined first, the subclass
and its related attributes are defined next, and
relationship set are then added.
• For example: In an
Employee management
system, EMPLOYEE entity
can be specialized as
TESTER or DEVELOPER
based on what role they
play in the company.
Aggregation
• In aggregation, the
relation between two
entities is treated as a
single entity. In
aggregation, relationship
with its corresponding
entities is aggregated into
a higher level entity.
Reduction of ER diagram to Table
Extended Entity-Relationship
(EE-R) Model
• EER is a high-level data model that incorporates the
extensions to the original ER Model. Enhanced ERD are
high level models that represent the requirements and
complexities of complex database.
In addition to ER model concepts EE-R includes −
• Subclasses and Super classes.
• Specialization and Generalization.
• Category or union type.
• Aggregation.
These concepts are used to create EE-R diagrams.
Relationship Of Higher Degree
• The number of occurrences in one entity, which is
correlated with the times of events in another entity
defines the degree of relationship.
• There is a three degree of relationship:
[Link]-to-one (1:1)
[Link]-to-many (1: M)
[Link]-to-many (M: N)
 One-to-one
• According to the One-to-one relationship, anyone
occurrence in the entity is related to exclusively one
occurrence in other entity.
• However, One-to-one relationship is not commonly seen in
practice.
• For instance, if an employee is provided a vehicle by the
company, then the vehicle shall be only driven by the
respective employee.
• Hence, the vehicle of the company and employee is
sharing a one-to-one relationship.
One-to-Many
• One-to-many occurrence refers to one occurrence in
any entity that is related to many incidents in other
entity.
• For instance, Any employee works in any one
department. However, any department can have
various employees.
• Hence, the employee and department have a one-to-
many relationship.
Many-to-many
• Many-to-many degree of relationship refers to the various
occurrences in any entity which relates to many other
phenomena in another entity.
• Many-to-many degree of relationship is similar to a one-to-
one relationship. Many-to-many relationship hardly exists
in a system.
• For instance, an employee can do work on various
projects. Any project can have a large team of multiple
employees.
• Hence, project and employee maintain a many-to-many
relationship.