SRI RAMAKRISHNA ENGINEERING COLLEGE

[Educational Service : SNR Sons Charitable Trust]

[Autonomous Institution, Reaccredited by NAAC with ‘A+’ Grade]
[Approved by AICTE and Permanently Affiliated to Anna University, Chennai]
[ISO 9001:2015 Certified and all Eligible Programmes Accredited by NBA]
VATTAMALAIPALAYAM, N.G.G.O. COLONY POST, COIMBATORE – 641 022.

Department of Information Technology

20IT206 DATABASE MANAGEMENT

SYSTEMS

Course Instructor: Dr.N.Suresh Kumar, ASP/IT

SYLLABUS
DBMS AND RELATIONAL MODEL 11

Database-System Applications, Purpose of Database Systems, View of Data,

Database Languages, Database Design, Database Engine, Database and
Application Architecture, Database Users and Administrators, History of
Database Systems. Structure of Relational Databases, Database Schema, Keys,
Schema Diagrams, Relational Query Languages, the Relational Algebra.
SYLLABUS
DATABASE DESIGN 11

Entity-Relationship Model – ER Diagrams – Functional Dependencies – Non-

Loss Decomposition Functional Dependencies – First Normal Form – Second
Normal Form – Third Normal Form – Dependency Preservation – Boyce/Codd
Normal Form – Multi-Valued Dependencies and Fourth Normal Form – Join
Dependencies and Fifth Normal Form
SYLLABUS
SQLAND NoSQL 12

SQL Query Language – Data types and Schema – SQL Data Definition – Basic
Structure of SQL Queries –Additional Basic Operations – Set Operations –
Null Values – Aggregate Functions – Nested Sub queries –Modification of the
Database – Join Expressions – Views – Transactions – Integrity Constraints –
Index Definitionin SQL – Authorization – Accessing SQL from a
Programming Language – Functions and Procedures – Triggers –Motivation
for NoSQLdatabase – Variety of NoSQLdatabases.
SYLLABUS
TRANSACTION MANAGEMENT 11

Transaction Concept – A Simple Transaction Mode – Storage Structure –

Transaction Atomicity and Durability –Transaction Isolation – Serializability –
Transaction Isolation and Atomicity – Transaction Isolation Levels –
Implementation of Isolation Levels – Transactions as SQL Statements – Lock-
Based Protocols – Deadlock Handling – Multiple Granularity – Insert
Operations – Delete Operations, and Predicate Reads – Timestamp-Based
Protocols – Validation-Based Protocols – Log Based Recovery.
TEXTBOOKS
1. A. Silberschatz, Henry F. Korth, and S. Sudharshan, “Database System Concepts”, 7 Edition,
Tata McGraw Hill, 2019.
2. Dan Sullivan, “NoSQLfor Mere Mortals”, Addison Wesley, 2015.

REFERENCES
1. Ramez Elmasri and Shamkant B. Navathe, “Fundamentals of Database Systems”, 7 Edition,
Pearson/Addision wesley, 2016.
2. Raghu Ramakrishnan, “Database Management Systems”, 4 Edition, McGraw Hill, 2021.
3. S. K. Singh, “Database Systems Concepts, Design and Applications”, 1 Edition, Pearson
Education, 2006.
4. C. J. Date, A. Kannan and S. Swamynathan, “An Introduction to Database Systems”, 8
Edition, Pearson
Education, 2006.

WEB REFERENCE
1. http://nptel.ac.in/courses/106106093/
 Cos and POs
COs POs

CO1 Outline the key concepts of database systems. PO1, PO2, PO3, PO12

Interpret the significant aspects of relational database, relational algebra and PO2, PO3, PO4, PO12
CO2
database design process.

CO3 Identify and use suitable normal form for designing relational database. PO2, PO3, PO4, PO12

CO4 Make use of SQL queries to manipulate a database for a given domain. PO2, PO3, PO4, PO12

CO5 Apply NoSQL queries to manipulate a database for a given domain. PO2, PO3, PO4, PO12

Examine the transaction management concepts, locking protocols deadlocks PO2, PO3, PO4, PO12
CO6
and log based recovery.
 Introduction to Database

25/08/2025 8
 What is Data ?

25/08/2025 9
 What is a Database?
Systematic collection of data
Collection of interrelated data
Can be stored in the form of tables
Supports storage and manipulation
of data
Make data management easy
Example
Collection Roll Nam Age Height Weigh
of Data no e t

25/08/2025 10
 Data can also be stored in?

Spreadsheet

Text File

25/08/2025 11
 Why Database?

25/08/2025 12
 Why Database?

25/08/2025 13
25/08/2025 14
 Why Database?

It is very quick and easy to find information.

It is easy to add new data and to edit or delete old data.

Data can be searched easily.

Data can be sorted easily.

25/08/2025 15
 Definitions
Data : Raw, Unprocessed facts
Eg: 25, Prem, Bangalore
Information: Processed data
Eg: The age of Prem is 25
Database: The collection of related data
Eg: Library management system
Meta – data : The database definition , complete description about the database.
The type of data which we are going to store, about the constraints etc.,.

25/08/2025 16
 Database Management
System

Database Management System: DBMS is software that handles the storage,

retrieval, and updating of data in a computer system.
Example: Oracle, MySQL

25/08/2025 17
 Popular DBMS Software

25/08/2025 18
 Database Management
System
Functionalities:
Define : Specifying the datatype, structures and constraints for the data to be
stored.
Construct: Process of storing the data on some storage medium. When the data
is stored database is constructed.
Manipulate: Querying the database to retrieve specific data, updating database
and generating reports.
Share: Allows multiple users and programs to access the database concurrently.
Many users can access or share the same database at the same time in an efficient
way.

25/08/2025 19
Properties of the database:

1. The database represents the aspect of the real world.

2. A database is logically coherent collection of data with some inherent

meaning.

3. A database is designed, built and populated with data for a specific purpose

25/08/2025 20
 Users/ Programmers

DBMS System Application Programs /

Queries

DB System Software to process queries or

Environme DBMS
programs
nt Software
Software to access stored data

Stored DB
definition Stored database
(Meta data)

25/08/2025 21
 Purpose of Database
Before Computers , we used paper to store
all information.
After arrival of computers, we started to store
information in form of files
Suppose we store information in Notepad…
we need following operations
For modifying file

File fopen
Processin fclose
g fread
Systems fwrite
25/08/2025 22
 Database Systems

25/08/2025 23
 Drawbacks in File Processing
Systems
Data Redundancy and Inconsistency
◦ Structure of files differs as it is created by different programmers and duplicate may also
exists.
◦ Eg:
◦ If students has double major (Engineering and Music),details of students will appear in
engineering file system and music too. This gives data redundancy
◦ Making a change in one file and not updating in another file will give inconsistent data
Difficulty in accessing data
◦ file-processing environments do not allow needed data to be retrieved in a convenient
and efficient manner
◦ To create useful applications for the user, often data from various files must be
combined. In file processing it was difficult to determine relationships between isolated
data in order to meet user requirements

25/08/2025 24
 Drawbacks in File Processing
Systems
Data Isolation
oData are scattered in various files, and files may be in different formats
Integrity Problems
odata values stored in the database must satisfy certain types of constraints
oThese constraints are enforced in the code while developing.
oWhen new constraints are added, it is difficult to update in the developed code
Atomicity Problems
oComputer is subject to failure and sudden crash makes the data updation problem.
o To have proper data updation, data should be centred in single place i.e.it should
be atomic

25/08/2025 25
 Drawbacks in File Processing
Systems
Concurrent Access Anomalies
◦ systems allow multiple users to update the data simultaneously.

◦ Since data resides at different places, it leads to erroneous data

Security Problems
◦ Not every user of the database system should be able to access all the data

◦ For example, in an university, payroll manager needs access to only financial

information, not in need of academic records but all are written as application
programs, imparting these security constraints is difficult in file processing system.
25/08/2025 26
Advantages in Database Management
Systems
Controlling redundancy:
In traditional file system each user group maintains its own files.
 Accounts dept( name , roll no, fees paid, fees due)
 Exam dept (name , roll no, course, grade)
This will lead to wastage of storage space and inconsistency.
Restricting unauthorized access:
When multiple users share large database, the type of access operation must be
controlled.
DBMS provide security and authorization.
DBA  creates account and specifies account restrictions.

25/08/2025 27
Advantages in Database Management
Systems
Providing backup and recovery:
The backup and recovery sub system of DBMS is responsible for recovery in
case of software / hardware failures.
Eg: if the computer crashes during the complex transaction the recovery
subsystem is responsible for ensuring the transaction resumes from where it is
interrupted or atleast restore to the state it was before transaction executing.
Providing Multiple user interface:
Multiple users  different levels of technical knowledge  so DBMS should
provide a variety of user interfaces

25/08/2025 28
 Why DBMS?
DBMS Flat file

Supports multi user Does not supports multi user

access access

Used by small and large business Used by small business

No redundancy and integrity Redundancy and integrity

Can work with complicated Cannot work with complicated
transactions transactions
Expensive cheap

25/08/2025 29
 Database – System
Applications

25/08/2025 30
 View of Data

25/08/2025 31
 View of Data
A database system is a collection of interrelated data and a set of programs that
allow users to access and modify these data. A major purpose of a database system
is to provide users with an abstract view of the data. That is, the system hides
certain details of how the data are stored and maintained.

query DBMS Software O/P

What you view now is called as a view. The DBMS software will hide many
things which the user does not require.
Abstraction is one of the main features of database systems. Hiding irrelevant
details from user and providing abstract view of data to users, helps in easy and
efficient user-database interaction.
25/08/2025 32
 View of Data
database system == collection of interrelated data and a set of programs

database system == collection of interrelated data and a set of

programs Data
Abstraction

Abstract system hides certain details of how the data are

View of stored and maintained
Data Instance
and
schema

25/08/2025 33
 Data Abstraction
Hiding irrelevant details from user and providing only the abstract view of data
to users

It helps in easy and efficient user-database interaction

Database systems are made-up of complex data structures

It hides the complex data structure from users through levels of abstraction in
order to simplify the database.

25/08/2025 34
 Three Levels of Abstraction
The three-schema architecture defines the view of data at three levels:

i. Physical level (internal level)

ii. Logical level (conceptual level)

iii. View level (external level)

25/08/2025 35
 Three Levels of Abstraction
Physical Level:

The physical or the internal level schema describes how the data is stored in
the hardware.

 It also describes how the data can be accessed.

The physical level shows the data abstraction at the lowest level and it
has complex data structures.

Only the database administrator operates at this level.

25/08/2025 36
 Three Levels of Abstraction
Logical Level:
It is a level above the physical level. The next level of abstraction
describe what data are stored in the database and what are the relationship
existed among those of data’s.
Here, the data is stored in the form of the entity set, entities, their data types,
the relationship among the entity sets, user operations performed to retrieve or
modify the data and certain constraints on the data.
Adding constraints to the view of data adds the security. As users are restricted
to access some particular parts of the database.
It is the developer and database administrator who operates at the logical or
the conceptual level.

25/08/2025 37
 Three Levels of Abstraction
View Level:
It is the highest level of data abstraction and exhibits only a part of the whole
database.
It exhibits the data in which the user is interested. The view level can describe
many views of the same data.
User see data and there may be many views of one data like chart and graph.
Here, the user retrieves the information using different application from the
database.
In this level user only interact with database

25/08/2025 38
25/08/2025 39
 Three Levels of Abstraction
Example : We have to create a database of a college. Now, what entity sets
would be involved? Student, Lecturer, Department, Course and so on…

Now, the entity sets Student, Lecturer, Department, Course will be stored in the
storage as the consecutive blocks of the memory location. This is the physical
or internal level and is hidden from the programmers but the database
administrator is it aware of it.

25/08/2025 40
 Three Levels of Abstraction
At the logical level, the programmers define the entity sets and relationship
among these entity sets using a programming language like SQL. So, the
programmers work at the logical level and even the database administrator also
operates at this level.

At the view level, the users have the set of applications which they use to
retrieve the data they are interested in.

25/08/2025 41
 Three Levels of Abstraction
At Physical Level
For Example
instructor, department, or student record
can be described as a block of consecutive
storage locations
At Logical Level
 type definition (type of data),
interrelationship of the record types is
defined as well
At View Level
several views of the database are defined

25/08/2025 42
 Three Levels of Abstraction
For Example

25/08/2025 43
25/08/2025 44
 Data Independence
Data independence defines the extent to which the data schema can be changed
at one level without modifying the data schema at the next level.

Data independence can be classified as:

i. Logical Data Independence

ii. Physical Data Independence

25/08/2025 45
 Data Independence
Logical data independence:

It describes the degree up to which the logical or conceptual schema

can be changed without modifying the external schema.

Well, the changes to data schema at the logical level are made either
to enlarge or reduce the database by adding or deleting more entities,
entity sets, or changing the constraints on data.

25/08/2025 46
 Data Independence
Physical Data Independence:

Physical data independence defines the extent up to which the data schema can
be changed at the physical or internal level without modifying the data schema at
logical and view level.

Well, the physical schema is changed if we add additional storage to the system
or we reorganize some files to enhance the retrieval speed of the records.

25/08/2025 47
 Instances and Schemas

Databases change over time as information is inserted and deleted.

The collection of information stored in the database at a particular moment is

called an instance of the database.

The overall design of the database is called the database schema.

25/08/2025 48
 Schemas
Design of a database is called the schema
Shows the design of the database, it doesn’t show the data present in those
tables
For example

25/08/2025 49
 Schema Architecture
• design of a database at physical level
Physical Schema • how the data stored in blocks of storage is described at this level.

• Design of database at logical level

• programmers and database administrators work at this level,
• data can be described as certain types of data records gets stored
Logical Schema
in data structures

• Design of database at view level

• This generally describes end user interaction with database
View Schema
systems.

25/08/2025 50
 Schema Architecture
Records of each student which each user

sees  view level of information

Columns, their data types, their mapping

and constraints  logical level of

information

Actual structure of table and data stored in

the server’s memory  physical level

25/08/2025 51
 Instance
Data stored in database at a particular moment of time is called
instance of database
For Example:
 Course_id :20
 Course_name: DBMS
 Department : CSE
Value of these variables at a moment of time is called the instance
of that database
For Example:
 Today , table has 100 records
 Tomorrow, Adding another 100 records, so instance of database
tomorrow will have 200 records

25/08/2025 52
 Database Languages

25/08/2025 53
 Database Languages
A DBMS has appropriate languages and interfaces to express database queries
and updates.

Database languages can be used to read, store and update the data in the
database.

25/08/2025 54
 Types of Database
Languages

25/08/2025 55
 Database Definition Language (DDL)
DDL stands for Data Definition Language. It is used to define database
structure or pattern.

It is used to create schema, tables, constraints, etc. in the database.

Using the DDL statements, you can create the skeleton of the database.

Data definition language is used to store the information of metadata like the
number of tables and schemas, their names, indexes, columns in each table,
constraints, etc.

25/08/2025 56
 Database Definition Language (DDL)
Here are some tasks that come under DDL:

Create: It is used to create objects in the database.

Alter: It is used to alter the structure of the database.

Drop: It is used to delete objects from the database.

Truncate: It is used to remove all records from a table.

Rename: It is used to rename an object.

25/08/2025 57
Database Manipulation Language
(DML)
DML stands for Data Manipulation Language. It is used for accessing and
manipulating data in a database. It handles user requests.

Select: It is used to retrieve data from a database.

Insert: It is used to insert data into a table.

Update: It is used to update existing data within a table.

Delete: It is used to delete all records from a table.

25/08/2025 58
Data Control Language (DCL)
DCL stands for Data Control Language. DCL is used for granting and revoking
user access on a database.

Here are some tasks that come under DCL:

Grant: It is used to give user access privileges to a database.

Revoke: It is used to take back permissions from the user.

25/08/2025 59
 Transaction Control
Language (TCL)
TCL is used to run the changes made by the DML statement. TCL can be
grouped into a logical transaction.

Here are some tasks that come under TCL:

Commit: It is used to save the transaction on the database.

Rollback: It is used to restore the database to original since the last Commit

25/08/2025 60
 RDBMS
RDBMS stands for relational database management system. A relational model
can be represented as a table of rows and columns.
A relational database has following major components:
1. Table
2. Record or Tuple
3. Field or Column name or Attribute
4. Domain
5. Instance
6. Schema
7. Keys

25/08/2025 61
 RDBMS - Table
A table is a collection of data represented in rows and columns. Each table has a
name in database. For example, the following table “STUDENT” stores the
information of students in database.

25/08/2025 62
 RDBMS - Row
Each row of a table is known as record. It is also known as tuple. For example,
the following row is a record that we have taken from the above table.

25/08/2025 63
 RDBMS - Column
In the table “STUDENT” has four fields (or attributes): Student_Id,
Student_Name, Student_Addr & Student_Age.

25/08/2025 64
 RDBMS -Domain
A domain is a set of permitted values for an attribute in table. For example, a
domain of month-of-year can accept January, February,…December as values, a
domain of dates can accept all possible valid dates etc. We specify domain of
attribute while creating a table. An attribute cannot accept values that are outside of
their domains.

 For example, In the above table “STUDENT”, the Student_Id field has integer
domain so that field cannot accept values that are not integers for example,
Student_Id cannot has values like, “First”, 10.11 etc.

25/08/2025 65
 RDBMS – Instance And
Schema
Design of a database is called the schema. Schema is of three types: Physical
schema, logical schema and view schema.

The data stored in database at a particular moment of time is called instance of

database.

25/08/2025 66
 RDBMS – Keys
Key plays an important role in relational database; it is used for identifying unique rows from table.
It also establishes relationship among tables.
Types of keys in DBMS
1. Primary Key-primary key is a field in a table which uniquely identifies each row/record
2. Foreign Key-A foreign key is the one that is used to link two tables together via
the primary key.
3. Super Key-to identify the tuples of the specified table
4. Composite Key-two or more attributes that together can uniquely identify a
tuple in a table
5. Candidate Key -subset of a super key set where the key which contains no redundant attribute
6. Surrogate key -A surrogate key is a key with virtual or no actual reason, and it is used for
representing the existence for data analysis.

25/08/2025 67
 Database Design

25/08/2025 68
 Database Design
Database Design is a collection of processes that facilitate the designing,
development, implementation and maintenance of data.

Properly designed database are easy to maintain, improves data consistency and
are cost effective in terms of disk storage space.

It helps produce database systems

a. That meet the requirements of the users

b. Have high performance.

25/08/2025 69
 Database Design

Two Types of Database Techniques:

1. ER Modeling

2. Normalization

25/08/2025 70
 Database Design – ER
Modeling
 Entity Relationship Model (ER Modeling) is a graphical approach to
database design.

 It is a high-level data model that defines data elements and their relationship
for a specified software system.

 An ER diagram is used to represent real-world objects.

25/08/2025 71
 ER Diagram
Design or blueprint of a database that can later be implemented as a database

Helps you to analyze data requirements systematically to produce a well-

designed database

Represents real-world entities and the relationship between them

Considered as a best practice to complete ER modeling before implementing

your database

25/08/2025 72
 ER Diagram
Steps to Create an ERD

Components of ER diagram
ER diagram depends on below three concepts:
◦ Entity
◦ Attributes
◦ Relationships

25/08/2025 73
 ER Diagram
Symbols in ER Diagrams:
Rectangles: This Entity Relationship
Diagram symbol represents entity types
Ellipses : Symbol represent attributes
Diamonds: This symbol represents
relationship types
Lines: It links attributes to entity types and
entity types with other relationship types
Primary key: attributes are underlined
Double Ellipses: Represent multi-valued
attributes
25/08/2025 74
 ER Diagram
 Entity sets are represented by a
rectangular box with the entity set name
in the header and the attributes listed
below it.
 Relationship sets are represented by a
diamond connecting a pair of related
entity sets. The name of the relationship
is placed inside the diamond.

25/08/2025 75
 Normalization
Another method for designing a relational database is to use a process
commonly known as normalization.

The goal is to generate a set of relation schemas that allows us to store

information without unnecessary redundancy.

It also allows us to retrieve information easily. The approach is to design

schemas that are in an appropriate normal form.

25/08/2025 76
 Database – Normal Forms
Normal Forms in SQL:
a. 1NF (First Normal Form)
b. 2NF (Second Normal Form)
c. 3NF (Third Normal Form)
d. BCNF (Boyce-Codd Normal
Form)
e. 4NF (Fourth Normal Form)
f. 5NF (Fifth Normal Form)
g. 6NF (Sixth Normal Form)

25/08/2025 77
 Database Engine
A database engine (or storage engine) is the underlying software component
that a database management system (DBMS) uses to create, update, read and
delete (CURD) data from a database.

The functional components of a database system can be broadly divided into

two components:
a. The storage manager

b. The query processor

25/08/2025 78
 Storage Manager
The storage manager is responsible for storing, retrieving, and updating data in
the database.

The storage manager components include:

Authorization and integrity manager

Transaction manager

File manager

Buffer manager

25/08/2025 79
 Storage Manager
Authorization and integrity manager: which tests for the satisfaction of
integrity constraints and checks the authority of users to access data.
Transaction manager: which ensures that the database remains in a consistent
(correct) state despite system failures, and that concurrent transaction executions
proceed without conflicting.
File manager: which manages the allocation of space on disks to rage and the
data structures used to represent information stored on disk.
Buffer manager: which is responsible for fetching data from disk storage into
main memory, and deciding what data to cache in main memory. The buffer
manager is a critical part of the database system, since it enables the database to
handle data sizes that are much larger than the size of main memory.

25/08/2025 80
 The Query Processor
As query is very much necessary to find out only the data user need from tons
of data of the database, query processor is very important to process these query
requests.
The query processor components include:
DDL interpreter: which interprets DDL statements and records the definitions
in the data dictionary.
DML compiler: which translates DML statements in a query language into an
evaluation plan consisting of low-level instructions that the query evaluation
engine understands.
Query evaluation engine: which executes low-level instructions generated by
the DML compiler.

25/08/2025 81
 Database System Architecture

25/08/2025 82
 Database System
Architecture
The DBMS design depends upon its architecture. The
basic client/server architecture is used to deal with a
large number of PCs, web servers, database servers and
other components that are connected with networks.
The client/server architecture consists of many PCs
and a workstation which are connected via the network.
DBMS architecture depends upon how users are
connected to the database to get their request done.

25/08/2025 83
 Database System
Architecture

25/08/2025 84
 1 – tier

Involves putting all of the required components for a software application or

technology on a single server or platform

25/08/2025 85
 1 – tier
 Database is directly available to the user. It means the user can directly sit on
the DBMS and uses it.
 Any changes done here will directly be done on the database itself. It doesn't
provide a handy tool for end users.
 The 1-Tier architecture is used for development of the local application, where
programmers can directly communicate with the database for the quick
response.
 These are centralized systems

25/08/2025 86
 2 – tier

25/08/2025 87
 2 – tier
 The two-tier is based on Client Server architecture.
 The direct communication takes place between client and
server. There is no intermediate between client and server.
 The user interfaces and application programs are run on the
client-side.
 The server side is responsible to provide the functionalities
like: query processing and transaction management.

25/08/2025 88
 3 – tier
 3-Tier architecture contains another layer between the client and server. In this
architecture, client can't directly communicate with the server.

 application on the client-end interacts with an application server which further

communicates with the database system.

 End user has no idea about the existence of the database beyond the application
server. The database also has no idea about any other user beyond the
application.

 The 3-Tier architecture is used in case of large web application.

25/08/2025 89
 3 – tier

25/08/2025 90
 Three levels of DBMS
Architecture
External Level
User 1 User 2 User 3 User n
Presentation Tier View View View ……. View
1 2 3 n

Can be viewed as
Application Tier Conceptual Level

Internal Level

Database Tier

Databas
e
25/08/2025 91
 Three levels of DBMS
Architecture
Internal Level or physical level:
 Physical Storage structure of Database
 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.
Conceptual Level or 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.
 Programmers and Database administrators work at this level

25/08/2025 92
 Three levels of DBMS
Architecture
External Level or view level:
Several users can view their desired data from this level which is internally
fetched from database with the help of conceptual and internal level mapping
User doesn’t need to know the database schema details such as data structure,
table definition etc.
User is only concerned about data which is what returned back to the view
level after it has been fetched from database

25/08/2025 93
25/08/2025 94
 DATABASE MANAGEMENT SYSTEMS
UNIT I : DBMS AND RELATIONAL MODEL 11

Database-System Applications, Purpose of Database Systems, View of Data,

Database Languages, Database Design, Database Engine, Database and
Application Architecture, Database Users and Administrators, History of
Database Systems. Structure of Relational Databases, Database Schema, Keys,
Schema Diagrams, Relational Query Languages, the Relational Algebra.

25/08/2025 95
Database Users and Administrators

25/08/2025 96
 Database users
There are four different types of database – system users, differentiated by the way
they expect to interact with the system.

 Different types of user interfaces have been designed for the different types of users.

i. Naive Users

ii. Application programmers

iii. Sophisticated users

iv. Specialized users

25/08/2025 97
 Database users
1. Naive Users :
Parametric End Users are the unsophisticated who don’t have any DBMS
knowledge but they frequently use the database applications in their daily life
to get the desired results.
This type of user does not need to be aware of the presence of the database
system. They interact with the database only with the help of the application
interface. They are just accessing the application whatever they are using.

25/08/2025 98
 Database users
EXAMPLES:

1. The person who is working in the bank will check and tell the current balance
after the transaction.

2. Reservation clerks for airlines, railways, hotels etc.,. they will check the
current availability and make reservation based upon our request.

3. Ticket booking users are naïve users.

25/08/2025 99
 Database users
2.Application Programmers:

Application programmers are computer professionals (back end

programmers) who write application programs. These programs could be written
in Programming languages such as Visual Basic, Developer, C, FORTRAN,
COBOL etc and reports with minimal programming effort. Application
programmers can choose from many tools to develop user interfaces.

25/08/2025 100
 Database users
3. Sophisticated users :

Sophisticated users can be engineers, scientists, business analyst, who are

familiar with the database. They can develop their own database applications
according to their requirement. They don’t write the program code but they
interact the database by writing SQL queries directly through the queryprocessor.

25/08/2025 101
 Database users
4. Specialized users :

Specialized users are sophisticated users who write specialized database

applications that do not fit into the traditional data-processing framework. They
are the developers who develop the complex programs to the requirement. These
applications store data with complex datatypes (for example: graphics data and
audio data) and environment-modeling systems.

25/08/2025 102
 Database Administrators

 Database Administrator (DBA) is a person/team who defines the schema and

also controls the 3 levels of database. The DBA will then create a new account id
and password for the user if he/she need to access the database.

DBA is also responsible for providing security to the database and he allows only
the authorized users to access/modify the database.

25/08/2025 103
 Database Administrators

DBA also monitors the recovery and back up and provide technical support.

The DBA has a DBA account in the DBMS which called a system or super user
account.

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

25/08/2025 104
 DBA - Functions
1. Schema Definition:
The DBA define the logical Schema of the database. A Schema refers to the
overall logical structure of the database.
According to this schema, database will be developed to store required data for
an organization.
2. Storage Structure and Access Method Definition:
The DBA decides how the data is to be represented in the stored database.
3. Assisting Application Programmers:
The DBA provides assistance to application programmers to develop application
programs.

25/08/2025 105
 DBA - Functions
4. Physical Organization Modification:

The DBA modifies the physical organization of the database to reflect the
changing needs of the organization or to improve performance.

5. Approving Data Access:

The DBA determines which user needs access to which part of the database.

According to this, various types of authorizations are granted to different users.

25/08/2025 106
 DBA - Functions
6. Monitoring Performance:
The DBA monitors performance of the system. The DBA ensures that better
performance is maintained by making changes in physical or logical schema if
required.
7. Backup and Recovery:
Database should not be lost or damaged.
The DBA ensures this periodically backing up the database on magnetic tapes or
remote servers.
In case of failure, such as virus attack database is recovered from this backup.

25/08/2025 107
 History of Database System

25/08/2025 108
 History of Database System
1950’s and early 1960’s •Magnetic tapes were developed for data storage.

Late 1960’s and 1970’s •Wide spread use of hard disks

1980’s • The relational model has reigned supreme among data models

Early 1990’s
• The SQL language was designed primarily for decision support
applications

1990’s •The explosive growth of the World Wide Web

2000’s
•The emerging of XML and the associated query language XQuery as a
new database technology

25/08/2025 109
Introduction to Relational Database
Model

25/08/2025 110
 Collection of Data forms
Database

25/08/2025 111
25/08/2025 112
 Data  Database  DBMS

Collected Managed
to form by Database
Management
Systems

25/08/2025 113
 Relational Database Management
System (RDBMS )
Software application used to create, manage and administer Relational Databases
Specially designed for creating and managing relational databases

25/08/2025 114
Next Question May be like?

25/08/2025 115
 Structure of Relational Database

25/08/2025 116
 Relational Database
Database is organized as group of related tables  Each table is a group of column and rows, where
Each table represents a database entity • column represents attribute of an entity
• rows represents records.

25/08/2025 117
 Relational Database

25/08/2025 118
 Relational Model
Model data in the form of relations or tables

conceptual
convert the
model of
conceptual
Database
model in the
using ER
relational model
diagram

25/08/2025 119
 Relational Model
Relational model represents how data is stored in relational databases
Represents the database as a collection of relations
In relational model, the data and relationships are represented by collection of inter-
related tables.

Relation Every row in the table

represents a collection
of related data values

25/08/2025 120
 Relational Model
Terminologies
collection of data
Table or
represented in rows and
Relation
columns

Record or Tuple Each row of a table

Field or Column
name or Each column in a Table
Attribute

25/08/2025 121
 Relational Model
Terminologies
For e.g.,
set of permitted values for domain of month-of-year can
Domain
an attribute in table accept January, February,…
December

Degree total number of attributes -

total number of rows

Cardinality -
present in the Table
e.g.;
Relation represents name of the STUDENT (ROLL_NO, NAME,
Schema relation with its attributes ADDRESS, PHONE, AGE) is
relation schema for STUDENT
value which is not known or
NULL Values unavailable is called NULL represented by blank space
value

25/08/2025 122
 Relational Model
Terminologies
specific instance of a
relation,
relation
i.e., containing a specific set -
instance
of rows at that moment of
time

they can uniquely identify

Keys -
the tuple

25/08/2025 123
 Relational Schema

25/08/2025 124
 Database Schema
Database Schema
◦ logical design of the database
Relation Schema
◦ consists of a list of attributes and
their corresponding domains

The schema for the relation ( table)

department:

25/08/2025 125
 Database Schema

25/08/2025 126
 Database Schema

25/08/2025 127
 Database Schema
 There are many more relations maintained in a real university database. In
addition to those relations we have listed already, instructor, department,
course, section, prereq and teaches.

25/08/2025 128
 Write relation
schema for this

25/08/2025 129
 DATABASE MANAGEMENT SYSTEMS
UNIT I : DBMS AND RELATIONAL MODEL 11

Database-System Applications, Purpose of Database Systems, View of Data,

Database Languages, Database Design, Database Engine, Database and
Application Architecture, Database Users and Administrators, History of
Database Systems. Structure of Relational Databases, Database Schema, Keys,
Schema Diagrams, Relational Query Languages, the Relational Algebra.

25/08/2025 130
 Keys

25/08/2025 131
 Keys
Keys play an important role in the STUDENT PERSON

relational database. ID Name

It is used to uniquely identify any record or row Name DOB

of data from the table. It is also used to establish
Address passport_number
and identify relationships between tables.

For example: In STUDENT table, ID is Course license_number

used as a key because it is unique for
each student. In PERSON table,
passport_number, license_number, SSN
SSN are keys since they are unique for
each person.
25/08/2025 132
 Types of Keys

KEYS

PRIMARY KEY CANDIDATE KEY SUPER KEY FOREIGN KEY

25/08/2025 133
 Primary Key
It is the first key which is used to uniquely identify rows. An relation can
contain multiple keys as we saw in PERSON table. The key which is most
suitable from those lists become a primary key.

In the EMPLOYEE table, ID can be primary key since it is unique for each
employee. In the EMPLOYEE table, we can even select License_number and
Passport_Number as primary key since they are also unique.

For each entity, selection of the primary key is based on requirement and
developers.
25/08/2025 134
 Primary Key

25/08/2025 135
 Candidate Key
If a relation schema has more than one key, each is called a candidate key.

One of the candidate keys is arbitrarily designated to be the primary key, and the
others are called secondary keys.

For example: In the EMPLOYEE table, Employee_id is best suited for the
primary key. Rest of the attributes like SSN, Passport_Number, and
License_number, etc. are considered as a candidate key.

25/08/2025 136
 Candidate Key

25/08/2025 137
 Super Key
We can define a super key as a set of those keys that identify a row or a tuple
uniquely.

 The word super denotes the superiority of a key. Thus, a super key is the
superset of a key known as a Candidate key

It means a candidate key is obtained from a super key only.

25/08/2025 138
 Super Key

25/08/2025 139
 Foreign Key
Foreign keys are the column of the table which is used to point to the primary
key of another table.
In a company, every employee works in a specific department, and employee
and department are two different entities.
 So we can't store the information of the department in the employee table.
That's why we link these two tables through the primary key of one table.
We add the primary key of the DEPARTMENT table, Department_ID as a new
attribute in the EMPLOYEE table.
Now in the EMPLOYEE table, Department_ID is the foreign key, and both the
tables are related.

25/08/2025 140
 Foreign Key

25/08/2025 141
 Schema Diagrams

25/08/2025 142
 Schema
The design of the database is called a schema. It is the structural view of the
database. It gives us an overall description of the database.
A database schema defines how the data is organised using the schema
diagram. A schema diagram is a diagram which contains entities and the
attributes that will define that schema.
A schema diagram only shows us the database design. It does not show the
actual data of the database.
Schema can be a single table or it can have more than one table which is
related. The schema represents the relationship between these tables.

25/08/2025 143
 Schema Diagram
A database schema, along with primary key and foreign key dependencies, can
be depicted by schema diagrams

Each relation appears as a box, with the relation name at the top, and the
attributes listed inside the box

Primary key attributes are shown underlined

Foreign key dependencies appear as arrows from the foreign key attributes of
the referencing relation to the primary key of the referenced relation.

25/08/2025 144
 Schema Diagram
Example: Three tables Employee, Department and Project tables are related.

25/08/2025 145
 Schema Diagram
Draw Schema diagram for the following relational schema:

1. user(user_id, name, email, gender, DOB, country_code)

2. products(p_id, m-id, name, price, status)

3. orders(o_id, user_id, status)

4. order_items(o_id, p_id, quantity)

5. merchants( m_id, m_name, country_code)

6. countries(country_code, name, continent_name)

25/08/2025 146
25/08/2025 147
 Query Language
A query language is a language in which a user requests information from the
database
a Language which is used to store and retrieve data from database
Eg: SQL
Two types:
Procedural Query language
Non-procedural query language

25/08/2025 148
 Query Language
Procedural Query language Non-procedural query language

User instructs the system to perform a User describes the desired information
sequence of operations on the database without giving a specific procedure for
to compute the desired result obtaining that information

Users tells what data to be retrieved Users tells what data to be retrieved
from database and how to retrieve it. from database but doesn’t tell how to
retrieve it.

25/08/2025 149
 Query Language
Relational Algebra
Conceptual procedural query language
used on relational model
Relational Calculus
Conceptual non-procedural query
language used on relational model
Relational algebra and calculus are the
theoretical concepts used on relational model
SQL is a practical implementation of
relational algebra and relational calculus

25/08/2025 150
Relational algebra Theore
t ical Im
plemen
and calculus tation
Relational
Model
t at ion
me n
I m ple
RDBMS ctica
l
Pra

Practical
Implementation SQL

25/08/2025 151
 Relational Algebra

25/08/2025 152
 Relational Algebra
Procedural query language that works on relational model
It tells what data to be retrieved and how to be retrieved
Takes relation as input and generate relation as output
It uses operators to perform queries
Relational algebra works on the whole table at once, so we do not have to use
loops etc to iterate over all the rows(tuples) of data one by one
Specify the table name from which we need the data, and in a single line of
command, relational algebra will traverse the entire given table to fetch data for
you

25/08/2025 153
 Relational Algebra

25/08/2025 154
 Relational Algebra

25/08/2025 155
 Relational Algebra -
Operations
Select
Project
Rename
Union
Intersect
Set Difference
Cartesian Product
Join

25/08/2025 156
Relational Algebra -
Operations

25/08/2025 157
 Select
Used to select the required tuples of data from a relation.
Denoted by sigma (σ)
During selection, we can specify certain conditions that the data must satisfy
Syntax :

25/08/2025 158
 Select Example

Query:
Details of the members who were born on
21/10/1997.

25/08/2025 159
Select Quiz :
Query: Details of loan in branch ‘Perryride’.
TABLE NAME : LOAN

25/08/2025 160
 Project
Used to select the required columns of data from a relation
Projection removes duplicate data
Denoted by П
Syntax:

A1, a2 etc are attribute names

25/08/2025 161
 Project Example
TABLE NAME: BORROW
Query:
Member IDs of members who have
borrowed books.

Book
Member ID Book ID
Name

1 1 OOPS
3 5 DBMS
4 3 DS
5 2 Java

25/08/2025 162
 Project
Query:
Member IDs of members and the Book
IDs of the books they have borrowed books.
Book
Member ID Book ID
Name

1 1 OOPS
3 5 AI
3 3 DBMS
4 2 DS
5 4 Java

25/08/2025 163
 Project
TABLE NAME : CUSTOMERS
Query:
Select the columns customer Name and
status from the table Customers

25/08/2025 164
 Rename
Rename operation allows renaming a certain output relation
Denoted by ρ Query:
Rename the Member relation as
LibraryMember.

25/08/2025 165
 Union
Used to fetch data from two relations(tables) or temporary relation(result of
another operation)
Both the relations must have the same set of attributes
Duplicate tuples should be automatically removed
Denoted by ∪ symbol
Syntax:

25/08/2025 166
 Union – Perform union on
student_name

25/08/2025 167
 Union - Perform union on
customer_name

25/08/2025 168
 Intersection
Defined by the symbol ∩
Suppose there are two tuples A and B. The set intersection operation contains all tuples that are
in both A & B
Syntax:

25/08/2025 169
 Intersection

25/08/2025 170
 Intersection

25/08/2025 171
 Set difference
We have two relations R1 and R2 and selects all those tuples(rows) that are present in relation
R1 but not present in relation R2
Denoted by – symbol
Both the relations must have the same set of attributes
Syntax:

25/08/2025 172
 Set difference

Provides the name of authors who have written books

but not articles

25/08/2025 173
 SET DIFFERENCE

write a query
to select
those
students who
have not
enrolled their
courses

25/08/2025 174
 SET DIFFERENCE

write a query to select

customers who have
loan but does not
maintain a deposit in
the bank

25/08/2025 175
 CARTESIAN PRODUCT

Operation used to merge columns from two relations

Combines information of two different relations into one
Denoted by X symbol
A X B will results all the attributes of A followed by each attribute of B
Each record of A will pairs with every record of B
It is also called cross product or cross join
Syntax: meaningful
operation when it is
followed by other
operations

25/08/2025 176
 CARTESIAN PRODUCT

AXB

25/08/2025 177
 CARTESIAN PRODUCT

RXS

Total rows in R X S = no of rows in R x no of rows in S

=3x3
=9

25/08/2025 178
 CARTESIAN PRODUCT
EMPLOYEE EMPLOYEE X DEPARTMENT

DEPARTMENT

25/08/2025 179
 CARTESIAN PRODUCT

Yields a relation, which shows all the books and

articles written by tutorialspoint.

25/08/2025 180
 CARTESIAN PRODUCT

25/08/2025 181
 Joins
Selectively pairs up tuples from two relations
Join operation is essentially a cartesian product followed by a selection
criterion.
Denoted by ⋈.
Combines related tuples from different relations, if and only if a given join
condition is satisfied
Syntax:

Relation1 ⋈condition Relation2

25/08/2025 182
 Joins - Example
EMPLOYEE

EMPLOYEE ⋈ SALARY

SALARY

25/08/2025 183
 JOINS - EXAMPLE

25/08/2025 184
Includes tuples
that satisfy the Joins - Types Include tuples that
matching satisfy the criteria,
criteria some or all tuples
Joins that do not match
the criteria
Inner Join Outer Join

Theta Join Left Outer

Equi Join Right Outer

Natural Join Full Outer

25/08/2025 185
 Inner Join – Theta Join
General case of JOIN operation
Denoted by symbol θ
Combines tuples from different relations provided they satisfy the theta
condition
Syntax :

25/08/2025 186
 INNER JOIN – THETA JOIN

<

25/08/2025 187
 INNER JOIN – EQUI JOIN
When theta join uses only equality comparison operator, it is said to be
equijoin
Special case of conditional join where only equality condition holds between
a pair of attributes
As values of two attributes will be equal in result of equijoin, only one
attribute will be appeared in result

25/08/2025 188
 INNER JOIN – EQUI JOIN

25/08/2025 189
 INNER JOIN –EQUI JOIN

Used a
= Equi Join
operator

25/08/2025 190
 Inner Join – Natural Join
Binary operator
Can only be performed if there is a common attribute (column) between the
relations.
Set of tuples of all combinations in r and s that are equal on their common
attribute names
Does not use any comparison operator. It does not concatenate the way a
cartesian product does
Name and type of the attribute must be same.
Syntax:

25/08/2025 191
 INNER JOIN – NATURAL JOIN

acts on those matching

attributes where the
values of attributes in
both the relations are
same

25/08/2025 192
 INNER JOIN – NATURAL JOIN

25/08/2025 193
 INNER Join - Natural JOin

25/08/2025 194
 Joins

25/08/2025 195
 What are the names of students who got
an A in any course?

25/08/2025 196
 What are the names of students who got
an A in any course?

25/08/2025 197
 Outer Join – Left JOIN
Select records from the first (left-most) table with matching right table
records
Join starting with the first (left-most) table.
Then, any matched records from the second table (right-most) will be
included
There is no matching tuple is found in right relation, then the attributes of
right relation in the join result are filled with null values
Syntax :

25/08/2025 198
 Left JOIN - eXAMPLE

25/08/2025 199
 Right join
Operation allows keeping all tuple in the right relation
 Join starting with the second (right-most) table and then any matching first
(left-most) table records
No matching tuple is found in the left relation, then the attributes of the left
relation in the join result are filled with null values
Syntax:

25/08/2025 200
 Right JOIN - eXAMPLE

25/08/2025 201
 Full join
All tuples from both relations are included in the result, irrespective of the
matching condition.
Syntax:

25/08/2025 202
 Full JOIN - eXAMPLE

25/08/2025 203
25/08/2025 204
25/08/2025 205
25/08/2025 206
25/08/2025 207
25/08/2025 208
25/08/2025 209
 Find movies made after 1997

25/08/2025 210
 Find movies made by Hanson after 199

25/08/2025 211
25/08/2025 212