DATABASE

MANAGEMENT
SYSTEMS
Presented by
KVR ACHARI, Asst .Professor
CSE – AIML
MRECW.
SYLLABUS

1. CHAPTERS
2. INTRODUCTION
3. RELATIONAL MODEL
4. SQL QUERIES
5. TRANSACTION MANAGEMENT
6. STORAGE AND INDEXING
 UNIT 1: INTRODUCTION
•Purpose of Database Systems
•Introduction to Database Systems
•View of Data
Physical Level
Logical Level
View Level
•Database Languages
DDL (Data Definition Language)
DML (Data Manipulation Language)
DCL (Data Control Language)
TCL (Transaction Control Language)
•Relational Databases
•Database Design
•Database Architecture
•Data Mining and Information Retrieval
•Database Users and Administrators
•History of Database Systems
•Entity-Relationship (ER) Model
•Relationships and Relationship Sets
•Conceptual Design with ER Model
•Conceptual Design for Large Enterprises
What is Data?

 Raw facts and figures without context with out any meaning.
 Can be numbers, text, images, audio, etc.
 Classified as structured, semi-structured, and unstructured.
 Examples: Names, dates, temperatures, images.
 Stored in files, databases, or cloud systems.
 Needs security and integrity for effective use.
What is a Database?

 A structured collection of related data.

 Organizes and stores data for efficient access.
 Supports data management, retrieval, and updating and
deleting.
 Examples: Library catalog, banking system, student
records.
 Reduces data redundancy and inconsistency.
 Provides concurrent access to multiple users.
 Enhances security with authentication and authorization.
 Ensures data integrity through constraints.
 Can be relational, NoSQL, hierarchical, etc.
 Forms the foundation for modern applications
What is DBMS? (Database
Management System)

 Software for managing databases.

 It is a s/w or collection of programs used for managing data and simultaneously it supports
different types of users to create, manage, retrieve, update and store information.
 Allows users to store, retrieve, and manipulate data.
 Examples: MySQL, Oracle, SQL Server, MongoDB.
 Provides an interface between users and databases.
 Ensures data consistency, security, and recovery.
 Supports multi-user access and concurrent transactions.
 Uses Structured Query Language (SQL) for operations.
 Manages relationships between datasets.
 Facilitates backup and recovery mechanisms.
 Essential for enterprise applications and big data.
Applications of DBMS

DBMS applications range from banking to education, ensuring the effective management
of large amounts of data.
 PURPOSE OF DBMS

Evolution of DBMS
In earlier days they have used punched card technology to store the data.
Then files were used. The file systems were known as predecessor of database system.
Various access methods in file system were indexed, random and sequential access.
Evolution of DBMS
The evolution of DBMS can be divided into several phases:
1. Pre-DBMS Era (Before 1960s)
1. Data was stored in files with no standardization.
2. Issues: Data redundancy, inconsistency, and inefficient access.
2. First Generation (1960s - 1970s) – Hierarchical & Network DBMS
1. IBM IMS (1966): Tree-like hierarchical structure.
2. CODASYL Network Model (1969): Graph-based relationships.
3. Limitations: Complex relationships, difficult management.
3. Second Generation (1970s - 1980s) – Relational DBMS (RDBMS)
1. Edgar Codd (1970) introduced the Relational Model.
2. Used SQL for easy data retrieval (Oracle, MySQL, IBM System R).
3. Advantages: Data normalization, consistency, and independence.
[Link] Generation (1980s - 1990s) – Object-Oriented DBMS (OODBMS)
1. Integrated OOP concepts for handling complex data (ObjectStore, db4o).
2. Limited adoption due to compatibility issues.
5. Fourth Generation (1990s - 2000s) – NoSQL & Web Databases
3. Rise of the Internet & Big Data led to flexible NoSQL databases (MongoDB,
Cassandra).
4. Advantages: Scalability, semi-structured/unstructured data support.
6. Fifth Generation (2000s - Present) – Cloud & AI-driven DBMS
5. Growth of Cloud Databases (Amazon RDS, Google BigQuery).
6. AI-powered databases (Oracle Autonomous DB).
DBMS has evolved from file systems to cloud and AI-driven databases, balancing
scalability, performance, and flexibility for modern applications.
PURPOSE OF DATABASE SYSTEMS

Drawbacks in File System

There are so many drawbacks in using the file system.
• Data redundancy and inconsistency: Different file formats, duplication of
information in different files.
• Difficulty in accessing data: To carry out new task we need to write a new
program.
• Data Isolation − Different files and formats.
• Integrity problems.
• Atomicity of updates − Failures leave the database in an inconsistent state.
For example, the fund transfer from one account to another may be incomplete.
• Concurrent access by multiple users.
• Security problems.
Need of DBMS
DATA BASE ARCHITECTURE

A Database stores a lot of critical information to access data quickly and securely.
Hence it is important to select the correct architecture for efficient data
management.
Types of DBMS Architecture
There are several types of DBMS Architecture that we use according to the
usage requirements.

3 types of DBMS architecture are:

 Single-Tier Architecture

 Two-Tier Architecture

 Three-Tier Architecture
 DBMS
ARCHITECTUR
E
Tier 1 (Single-Tier Architecture):
The database and application are on the
same system.

Tier 2 (Two-Tier Architecture):

The application and database are
separate, directly connected via a
network.

Tier 3 (Three-Tier Architecture):

Divides into three parts: user interface,
logic and database, all working
together.
DATA ABSTRACTION (or) VIEW OF DATA

It is a 3-tier schema architecture.

It has 3 levels

 External level (view level)

 Conceptual level (logical level)
 Internal level (physical level)
1. External level (view level)

It is also called view level. The reason this level is called “view” is
because several users can view their desired data from this level.

the user doesn’t need to know the database schema details such as data
structure, table definition etc.

External level is the “top level” of the Three Level DBMS Architecture.
2. Conceptual level (logical level)

It is also called logical level. The whole design of the database such as
relationship among data, schema of data etc. are described in this
level.

Database constraints and security are also implemented in this level of

architecture. This level is maintained by DBA (database administrator).
[Link] level (physical level)

 This level is also known as physical level.

 This level describes how the data is actually stored in the storage
devices. This level is also responsible for allocating space to the data.
 This is the lowest level of the architecture.
DBMS languages

Database languages are used to read, update and store data in a

database. There are several such languages that can be used for
this purpose; one of them is SQL (Structured Query Language).
Here select command is under Data Query language.
Types of DBMS
languages:
1). Data Definition Language (DDL)
DDL is used for specifying the database schema. It is used for creating
tables, schema, indexes, constraints etc. in database.

•To create the database instance – CREATE

•To alter the structure of database – ALTER

•To drop database instances – DROP

•To delete tables in a database instance – TRUNCATE

•To rename database instances – RENAME

•To drop objects from database such as tables – DROP

•To Comment – Comment

All of these commands either defines or update the database schema that’s why
they come under Data Definition language.
2). Data Manipulation Language (DML) - DML is used for accessing and manipulating
data in a database.
Select - To read records from table(s)

insert - To insert record(s) into the table(s)

update - Update the data in table(s)

delete - Delete all the records from the table

3). Data Control language (DCL) - DCL is used for granting and revoking user access
on a database
GRANT - To grant access to user

REVOKE - To revoke access from user

4). Transaction Control Language(TCL)

The changes in the database that we made using DML commands are either performed or
rollbacked using TCL.

COMMIT - To persist the changes made by DML commands in database

DDL commands

DML commands
 Relational Model in DBMS
The Relational Model represents data and their relationships through a collection of
tables. Each table also known as a relation consists of rows and columns.

E.F. Codd introduced the Relational Model to organize data as relations or

tables.

After creating the conceptual design of a database using an ER diagram,

this design must be transformed into a relational model which can then be
implemented using relational database systems like Oracle SQL or MySQL.
What is the Relational Model?
 The relational model represents how data is stored in Relational
Databases. A relational database consists of a collection of tables each of
which is assigned a unique name. Consider a relation STUDENT with
attributes ROLL_NO, NAME, ADDRESS, PHONE, and AGE shown in
the table.
Key Terms
•Attribute: Attributes are the properties that define an entity. e.g. ROLL_NO, NAME,
ADDRESS.

•Relation Schema: A relation schema defines the structure of the relation and represents
the name of the relation with its attributes.

e.g. STUDENT (ROLL_NO, NAME, ADDRESS, PHONE, and AGE)

the relation schema for STUDENT. If a schema has more than 1 relation it is called
Relational Schema.

•Tuple: Each row in the relation is known as a tuple.

•Relation Instance: The set of tuples of a relation at a particular instance of time is called
a relation instance.

•Degree: The number of attributes in the relation is known as the degree of the relation. The
STUDENT relation defined above has degree 5.

•Cardinality: The number of tuples in a relation is known as cardinality. The STUDENT

relation defined above has cardinality 4.
•Column: The column represents the set of values for a particular attribute. The
column ROLL_NO is extracted from the relation STUDENT.

•NULL Values: The value which is not known or unavailable is called a NULL
value. It is represented by NULL. e.g. PHONE of STUDENT having ROLL_NO 4 is
NULL.

•Relation Key: These are basically the keys that are used to identify the rows
uniquely or also help in identifying tables. These are of the following types:

• Primary Key
• Candidate Key
• Super Key
• Foreign Key
• Alternate Key
• Composite Key
 DATA BASE DESIGN

Database Design can be defined as a set of procedures or collection

of tasks involving various steps taken to implement a database.
DATA BASE DESIGN LIFE CYCLE

1) Requirement analysis
2) Logical and physical design
3) Implementation and Testing

LIFE CYCLE OF DATABASE DESIGN

 •Requirement analysis: Understanding the needs of users and the purpose of the
database.

•Conceptual design: Developing an abstract model, usually represented by an ER

(Entity-Relationship) diagram.

•Logical design: Converting the ER model into a structured format, typically in relational
schema.

Different Types of Database Users

•Physical design: Defining how the data will be stored on hardware (e.g., indexing,
partitioning).
A Database User is defined as a person who interacts with data daily, updating, reading,
and modifying the given data. Database users can access and retrieve data from the
database through the Database Management System (DBMS) applications and interfaces.

Types of Database Users

Database users are categorized based on their interaction with the database. There are
seven types of database users in DBMS. But DBA and data base designer ,application
programmer and end users are very important in database.
1. Database Administrator (DBA)

A Database Administrator (DBA) is a person/team who defines the schema and also controls the
3 levels of the database. The DBA will then create a new account ID and password for the user
if he/she needs to access the database. DBA is also responsible for providing security to the
database and he allows only authorized users to access/modify the database. DBA is
responsible for problems such as security breaches and poor system response time.
•DBA also monitors the recovery and backup and provides technical support.

•The DBA has a DBA account in the DBMS which is called a system or superuser account.

•DBA repairs damage caused due to hardware and/or software failures.

•DBA is the one having privileges to perform DCL (Data Control Language) operations such as
GRANT and REVOKE, to allow/restrict a particular user from accessing the database.
Data mining and information retrieval
The Entity Relationship Model is a model for identifying entities (like student,
car or company) to be represented in the database and representation of
how those entities are related. The ER data model specifies enterprise
schema that represents the overall logical structure of a database
graphically.
Components of ER Diagram
ER Model consists of Entities, Attributes, and Relationships among Entities in a
Database System.
Important Questions:

1. What is a Database System? Explain its applications.

2. Describe the purpose of a Database System and its advantages over a File System.
3. Explain the different views of data in a Database Management System (DBMS).
4. Define Database Languages. Differentiate between DDL and DML with examples.
5. Explain Relational Databases with an example.
6. What is Database Architecture? Explain different types of database architectures.
7. Discuss the role of Database Users and Administrators in a DBMS.
8. Describe the History of Database Systems and their evolution.
9. What is an ER Diagram? Explain Entities, Attributes, and Relationships with an
example.
10. Explain the additional features of the ER Model like Generalization, Specialization,
and Aggregation.
Problem 1: Library Management System
Scenario:
A library has books, members, and staff.
•Each book has a unique Book ID, a Title, an Author, and a Category.
•Members can borrow multiple books, and each book can be borrowed by multiple members.
•Staff members manage books and assist users.

ER diagram Solution:
 Entities: Book, Member, Staff
 attributes:
book: book _id (PK), title, author, category
member: member_id(PK), name ,contact
staff: staff_id (PK), name, position
 relationships:
borrows : many to many
manages : one to many
Try to draw ER diagrams:

Problem 2: Hospital Management System

Scenario:
A hospital database maintains information about patients, doctors, and
treatments.
•Patients visit doctors for treatment.
•A doctor can treat multiple patients, but a patient is assigned to only one doctor.
•Each patient has a unique ID, name, and disease.
•Doctors have a unique ID, name, and specialization.
Problem 3: University Database
Scenario:
A university wants to maintain a database of students, courses, and professors. The
system should track:
•Students who enroll in courses.
•Each course is taught by a professor.
•Each student has a unique ID, name, and department.
•Professors have a unique ID, name, and department.
•Each course has a course ID, course name, and credits.