DBMS

UNIT -1

Data Models
 What is a Data Model?
Data model is a collection of conceptual tools for describing data, data
relationships, data semantics and consistency constraints. That means a data
model provides a way to describe the design of a database.

Data models are widely used in database management systems to demonstrate how data
is related, stored, retrieved, and modified. We depict information using a set of symbols
and terminology so that members of an organisation may understand and comprehend it
before communicating.
Types of Data Models.

•Hierarchical Model
•Network Model
•Entity Relationship Model
•Relational Model
•Object-Oriented Model
Data model basic building blocks
Entity
An entity is anything about which data are to be collected and stored. An
entity represents a particular type of object in the real world. Like –
Student, employee etc.
Entity Set
Set of entities of the same type that share the same properties are called
as entity sets. Like – Students , Employees

Attribute
An attribute is a characteristic of an entity. Like – student_id, Stu_name

Constraints
A constraint is a restriction placed on the data. Constraints are important
because they help to ensure data integrity.
student_id is the primary key, ensuring no two students have the same ID.
 Relationships
A relationship describes an association among entities. Different types of
relationship are:

One to One (1:1) relationship:

One to Many (1:M) relationship:

Many to Many (M:N) relationship:

Dr. Jay Sarraf, School of Computer Engineering, KIIT Deemed to be University

 Hierarchical model
This is one of the oldest models in a data model which was developed by
IBM, in the 1950s.
The hierarchical model organizes data in a tree-like structure, where each record has a
single parent but can have multiple children.
• Parent-Child Relationship: Data is represented using parent-child relationships,
where a parent node can have multiple child nodes but each child has only one
parent.
• Traversal: To access data, traversal starts from the root and moves downwards
following the predefined paths.
Example: An organization structure where a company (parent) has multiple
departments (children), and each department has employees.

Dr. Jay Sarraf, School of Computer Engineering, KIIT Deemed to be University

Advantages of Hierarchical Model
• It is very simple and fast to traverse through a tree-like structure.
• Any change in the parent node is automatically reflected in the
child node so, the integrity of data is maintained.

Disadvantages of Hierarchical Model

• Complex relationships are not supported.

• As it does not support more than one parent of the child node so if
we have some complex relationship where a child node needs to
have two parent node then that can't be represented using this
model.
• If a parent node is deleted then the child node is automatically
deleted.

Dr. Jay Sarraf, School of Computer Engiring, KIIT Deemed to be University

 Network Model
Network Model
The network model is an extension of the hierarchical model, where data is represented
using a graph structure instead of a tree.
• Multiple Parent-Child Relationships: Unlike the hierarchical model, an entity can
have multiple parent nodes, allowing for complex relationships.
• Data Access: Traversal is done through pointers and links between different records,
making it more flexible than the hierarchical model.
It allows many-to-many (M:N) relationships, making it more flexible than the Hierarchical
Model.
Example: A university database, where a student can enroll in multiple courses, and each
course can have multiple students.

Dr. Jay Sarraf, School of Computer Engineering, KIIT Deemed to be University

 Advantages of Network Model
1. Supports Many-to-Many (M:N) Relationships
•Overcomes Hierarchical Model's limitation by allowing multiple parent-child relationships.

2. Faster Data Access

•Uses pointers to directly access records, reducing search time.
•More efficient for complex queries compared to hierarchical databases.

3. Reduces Data Redundancy

•Since multiple records can be linked directly, data does not need to be duplicated

4. Greater Flexibility in Data Representation

•Unlike the rigid hierarchical structure, the network model adapts better to real-world
relationships.

Disadvantages of Network Model

1. Complex Structure & Implementation
•Requires explicit linking using pointers, making it hard to design and maintain.
2. Difficult Database Modification
•Adding or deleting records requires updating multiple pointers, which can be complex.
3. No Standard Query Language (SQL)
•Unlike RDBMS, the network model lacks a universal query language.
4. Slower Insert/Delete Operations
•Due to pointer-based connections, inserting or deleting a record may require multiple updates
in different places.
Dr. Jay Sarraf, School of Computer Engineering, KIIT Deemed to be University
Entity-Relationship Model
The Entity-Relationship Model, known as the ER Model.
ER model in DBMS is the high-level data model. It stands for the Entity-
relationship model and is used to represent a logical view of the system from a
data perspective.
The ER diagram is a visual tool used to depict an ER Model. The ER diagram is
made up of three parts: Entity, Attribute & Relationships.
It shows how entities (objects) relate to each other in a system.

Dr. Jay Sarraf, School of Computer Engineering, KIIT Deemed to be University

• The entity relationship model consists of a collection of basic objects, called
entities, and relationships among these entities.

• Entity: An entity is a 'thing' or 'object' in the real world that is distinguishable from
other objects. For example, each person is an entity, and bank accounts are
entities.

• Attributes: Entities are described in a database by a set of attributes. For example,

the attributes of an account entity are account_no and balance.

• Relationship: A relationship is an association among several entities. For

example, a depositor relationship associates a customer with an account.
Advantages of Entity-Relationship Model
• Simple: Conceptually ER Model is very easy to build. If we know the relationship
between the attributes and the entities we can easily build the ER Diagram for
the model.

• Effective Communication Tool : This model is used widely by the

database designers for communicating their ideas.

• Easy Conversion to any Model : This model maps well to the relational model
and can be easily converted relational model by converting the ER model to the
table. This model can also be converted to any other model like network model,
hierarchical model etc.

Disadvantages of Entity-Relationship Model

• No industry standard for notation: There is no industry standard for
developing an ER model. So one developer might use notations which are not
understood by other developers.

• Hidden information: Some information might be lost or hidden in the ER

model. As it is a high-level view so there are chances that some details of
information might be hidden.

Dr. Jay Sarraf, School of Computer Engineering, KIIT Deemed to be University

Relational Model
The Relational Model is the most widely used database model, where data is
stored in tables (relations) with rows (tuples) and columns (attributes).
It follows ACID (Atomicity, Consistency, Isolation, Durability) properties.

Tables are the foundation of a relational paradigm.

Example: We have student & subject table in this example. Which will further
related with marks table.

Dr. Jay Sarraf, School of Computer Engineering, KIIT Deemed to be University

• Relational Model

• The relational model represents the database as a collection of relations (tables).

Each relation consists of a set of tuples (rows), where each tuple represents an
entity.

• Table (Relation): A table consists of columns and rows where each row
represents a record, and each column represents an attribute of the record.

• Primary Key: A primary key is an attribute (or a set of attributes) that uniquely
identifies each record in the table.

• Foreign Key: A foreign key is an attribute in one table that refers to the primary
key of another table, establishing a relationship between them.
Advantages of Relational Data Model
• Simple: This model is more simple as compared to the network and
hierarchical model.

• Scalable: This model can be easily scaled as we can add as many rows and
columns we want.

• Structural Independence: We can make changes in database structure

without changing the way to access the data.

• Easy Querying : to fetch the data is easy by the use of SQL language.

Disadvantages of Relational Data Model

High Storage Requirements - Requires significant memory and storage due to
indexes, constraints, and relationships.

Not Ideal for Unstructured Data - Cannot handle unstructured data like images,
videos, and documents efficiently.
Expensive Maintenance and Setup - Requires skilled database administrators
(DBAs) for management.
Object-Oriented Model
• The object-oriented model integrates object-oriented programming concepts
with databases, treating data as objects.

• Objects: Objects store both data and methods that operate on the data.

• Classes & Inheritance: Objects are instances of classes, and inheritance

allows classes to inherit properties from parent classes.

• Encapsulation: Data and behaviour are encapsulated within objects,

promoting modularity.
The Employee and Department are the two objects in the example above. Each
object’s data and the relationships are there in a single unit.

Here, the attributes of the employee, such as Name and Job_title, as well as
the methods that will be performed by that object, are all kept in a single
object.

The two objects are linked by a common attribute, i.e., Department_id, as well
as communication between them will be accomplished by this common id.
Object Oriented Data Model
Advantages
• Encapsulation
• Combines both data and behavior in objects, improving modularity and reusability.
• Inheritance
• Allows new objects to inherit properties and behaviors from existing objects, reducing
redundancy.
• Complex Data Representation
• Can handle complex data types like multimedia, CAD designs, and scientific data more
efficiently than relational databases.
• Supports Object-Oriented Languages
• Works well with OOP languages (e.g., Java, C++, Python), making it easier for developers to
integrate databases with application logic.

Disadvantages
• Complexity More difficult to design and manage compared to relational databases, requiring
expertise in both database and OOP principles.
• Lack of Standardization - Unlike relational databases with SQL, object-oriented databases lack a
universal standard, leading to compatibility issues.
• Slower for Simple Queries - If the application primarily uses simple queries, a relational database
may be more efficient due to indexing and structured data storage.
• Higher Storage Requirements- Storing objects, methods, and relationships can increase storage
overhead compared to normalized relational databases.
DataBase Languages
 Database languages

Database languages are specialized languages used to interact with a

database.
They allow users to perform different tasks such as defining, controlling,
and manipulating the data.

There are several types of database languages in DBMS, categorized into the
following four main types:
DDL (Data Definition Language)
DCL (Data Control Language)
DML (Data Manipulation Language)
TCL (Transaction Control Language)

Language Type Purpose Examples

CREATE, ALTER, DROP,
DDL Defines database schema
TRUNCATE
SELECT, INSERT, UPDATE,
DML Manipulates data
DELETE
DCL Controls access GRANT, REVOKE
COMMIT, ROLLBACK,
TCL Manages transactions
SAVEPOINT
DDL (Data Definition Language)

The DDL stands for Data Definition Language, Which is used to

define the database's internal structure and Pattern of the Database.
It is used to define and modify the structure of the database itself,
including the tables, views, indexes, and other schema-related
objects. It deals with the creation and modification of database
schema, but it doesn't deal with the data itself.

Following are the five DDL commands in SQL:

CREATE: Used to create database objects like tables, indexes, or
views.

ALTER: Used to modify the structure of an existing database object,

such as adding a new column to a table.

DROP: Used to delete database objects.

TRUNCATE: Used to remove all rows from a table, without affecting

the structure.

RENAME: Used to change the name of a database object.

 DML (Data Manipulation Language)
The DML (Data Manipulation Language) is used to manage and
manipulate data within a database. With DML, you can perform various
operations such as inserting, updating, selecting, and deleting data. These
operations allow you to work with the actual content in your database
tables.

Here are the key DML commands:

SELECT: Retrieves data from the table based on specific criteria.

INSERT: Adds new rows of data into an existing table.
UPDATE: Modifies existing data in a table.
DELETE: Removes data from a table.
DCL (Data Control Language)

DCL stands for Data Control Language. It is used to

control the access permissions of users to the
database. DCL commands help grant or revoke
privileges to users, determining who can perform
actions like reading or modifying data. DCL
commands are transactional, meaning they can be
rolled back if necessary.

The two main DCL commands are:

Grant: Gives user access to the database
Revoke: Removes access or permissions from the
user
TCL ( Transaction Control Language )

The TCL full form is Transaction Control

Language commands are used to manage and
control transactions in a database, grouping them
into logical units. These commands help ensure the
integrity of data and consistency during complex
operations. Here are the two main commands in this
category:

Commit: Saves all the changes made during the

current transaction to the database. These are very
useful in the banking sector.
Rollback: used to restore the database to its original
state from the last commit. This command also plays
an important role in Banking Sectors.
Database Users
Database Users

Types of Users in a Database System

1.Naive Users – Use predefined application interfaces
(e.g., bank tellers, web users) to interact with the
database without writing queries.
2.Application Programmers – Develop software
applications that interact with the database using
programming languages and SQL.
3.Sophisticated Users – Use query tools (e.g., SQL, BI
tools) to analyze and retrieve complex data for
reporting and decision-making.
4.Database Administrators (DBAs) – Manage the
database system, ensuring security, performance,
backup, and user access control.
 Database Administrator (DBA)
DBA has central control over both the database and the programs that access it.
The main functions of a DBA include:

1.Schema Definition
•Creates the original database schema using Data Definition Language (DDL)
statements.
2.Storage Structure & Access-Method Definition
•Specifies parameters related to the physical organization of data and index
structures.
3.Schema & Physical Organization Modification
•Adapts database schema and storage as per organizational needs.
•Modifies physical structures to improve performance.
4.Authorization Management
•Grants different levels of access to users based on roles.
•Stores authorization information in a system structure for security control.
5.Routine Maintenance
•Database Backup: Regularly backs up data to prevent loss due to disasters.
•Storage Management: Ensures sufficient free disk space and upgrades as
needed.
•Performance Monitoring: Tracks database performance and prevents resource-
heavy queries from slowing down the system.
 Comparison of database users
Application Database
Feature Naïve Users Sophisticated Users
Programmers Administrators (DBA)
Person responsible for
Unsophisticated users who Computer professionals Users who directly
managing and
Definition interact with predefined who develop database query and analyse data
maintaining the
applications. applications. without programming.
database system.
Expertise Level Low High Moderate to High Very High
Full control over
Interaction with Indirect (via Direct (via query
Indirect (via GUI/forms) database structure and
DB applications) languages)
access
DBMS Tools, DDL,
Programming languages SQL, Query Languages,
Main Tools Used Forms, Web/Mobile Apps Authorization
(SQL, Java, Python) Data Analysis Tools
Management

Students registering for Software Developers Business Analysts IT Admin managing

Example Users
classes online creating applications retrieving reports database access
Schema definition,
Data input via forms, Writing database-driven Querying, analyzing, and storage management,
Tasks Performed
viewing reports applications retrieving data security, and
performance monitoring
Limited (only user-friendly Intermediate (through Full control
Level of Access Direct (querying data)
UI) applications) (administrative rights)
Database System Architecture
 Query Processor Components

1.Compiler and Linker – Translates application programs into executable

code for database interaction.
2.DML Queries – Handled by the DML compiler and organizer to process
data manipulation commands like INSERT, UPDATE, DELETE.
3.DDL Interpreter – Processes Data Definition Language (DDL)
commands like CREATE, ALTER, DROP to define and modify database
structure.
4.Query Evaluation Engine – Executes queries efficiently, optimizing
retrieval and processing of results.
 Storage Manager Components
1.Buffer Manager – Optimizes data retrieval by
managing memory buffers for faster access.
2.File Manager – Handles physical storage,
organization, and retrieval of database files.
3.Authorization and Integrity Manager – Ensures
data security (user access control) and integrity
(maintaining correctness).
4.Transaction Manager – Manages transactions
to ensure ACID properties (Atomicity,
Consistency, Isolation, Durability) for reliability.
Disk Storage Components
1.Data – The primary raw information stored in tables,
representing records of entities like customers, orders,
or employees.
2.Indices – Special lookup structures that enhance
query performance by allowing faster data retrieval
without scanning entire tables.
3.Data Dictionary – A centralized repository containing
metadata about the database, including table
definitions, relationships, constraints, and user
permissions.
4.Statistical Data – Stores query execution statistics,
such as table sizes and access patterns, to help the
database optimizer improve query efficiency.