Course : Database Management Systems

1.1
 What is a Database?
• Data: Facts, figures, statistics etc. having no particular meaning (e.g. 1,
ABC, 19 etc).

• Record: Collection of related data items, e.g. in the above example the
three data items had no meaning.

• But if we organize them in the following way, then they collectively

represent meaningful information.
Roll Name Age

1 ABC 19

2 DEF 22

3 XYZ 28

1.2
• The columns of this relation are called Fields, Attributes or Domains.
The rows are called Tuples or Records.

• Database: Collection of related relations

1.3
What is DBMS?

• A database-management system (DBMS) is a collection of

interrelated data and a set of programs to access those data.
• . The primary goal of a DBMS is to provide a way to store and retrieve
database information that is both convenient and efficient

1.4
Database Applications Examples

• Enterprise Information
• Sales: customers, products, purchases
• Accounting: payments, receipts, assets
• Human Resources: Information about employees, salaries, payroll
taxes.
• Manufacturing: management of production, inventory, orders, supply
chain.
• Banking and finance
• customer information, accounts, loans, and banking transactions.
• Credit card transactions
• Finance: sales and purchases of financial instruments (e.g., stocks
and bonds; storing real-time market data
• Universities: registration, grades

1.6
Purpose of Database Systems

In the early days, database applications were built directly on top of file systems, which
leads to:
• Data redundancy and inconsistency: data is stored in multiple file formats resulting
induplication of information in different files
• Difficulty in accessing data
• Need to write a new program to carry out each new task
• Data isolation
• Multiple files and formats
• Integrity problems
• Integrity constraints (e.g., account balance > 0) become “buried” in program code
rather than being stated explicitly
• Hard to add new constraints or change existing ones

1.7
Purpose of Database Systems (Cont.)
• Atomicity of updates
• Failures may leave database in an inconsistent state with partial
updates carried out
• Example: Transfer of funds from one account to another should either
complete or not happen at all
• Concurrent access by multiple users
• Concurrent access needed for performance
• Uncontrolled concurrent accesses can lead to inconsistencies
• Ex: Two people reading a balance (say 100) and updating it by
withdrawing money (say 50 each) at the same time
• Security problems
• Hard to provide user access to some, but not all, data

1.8
Advantages of DBMS
• Controlling of Redundancy: Data redundancy refers to the duplication
of data
• Improved Data Sharing : DBMS allows a user to share the data in any
number of application programs.
• Data Integrity : Integrity means that the data in the database is accurate
• Security : Having complete authority over the operational data
• Data Consistency : Eliminating data redundancy reduces the
opportunities for inconsistency
• Efficient Data Access : In a database system, the data is managed by the
DBMS and all access to the data is through the DBMS providing a key to
effective data processing
• Reduced Application Development and Maintenance Time : DBMS
supports many important functions that are common to many
applications, accessing data stored in the DBMS, which facilitates the
quick development of application.
• Recovery and Backup

1.10
Disadvantages of DBMS
• Cost-Implementing a DBMS system can be expensive due
to software and hardware expenses, maintenance, and
upgrades.
• Complexity-DBMS systems can be difficult to use as it
requires special training
• Scalability-The DBMS system may need help to handle
increasing database size
• Dependence on technology-An organization may become
too reliant on its DBMS, making it hard to switch to a
different system if necessary.

1.11
Disadvantages of DBMS(contd.)

• Performance issues- as a database grows in size and

complexity, it can slow down query speed and increase
downtime.
• Data integration- integrating data from different sources
can be complex and time-consuming. The data you want to
integrate has a different format, structure, and naming
conventions.
• Security- your data is not guaranteed to be safe even after
implementing these security measures like encryption,
firewalls, etc
• Data loss/corruption-It can result in the loss of important
information stored in the database for reasons, such as
hardware failure, software bugs, etc. Sometimes, these
losses are recoverable.

1.12
Difference between DBMS and RDBMS:
DBMS RDBMS
1. DBMS applications store 1. RDBMS applications
2. store data in a tabular
data as file.
form.
2. DBMS does not apply any 2. RDBMS defines the
security with regards to data integrity constraint
manipulation.
3. DBMS does not support 3. RDBMS supports
distributed database. distributed database.
4. DBMS is used for simpler 4. RDBMS is used for more
business applications complex applications.
5. Normalization is not present in 5. Normalization is present
DBMS. in RDBMS.
6. DBMS does not support client 6. RDBMS supports client
server architecture. server architecture.
7. Examples of DBMS are file 7. Examples of RDBMS are
systems, XML etc. MySQL, PostgreSQL,
MongoDB, MariaDB,
Microsoft SQL Server,
Oracle, Sybase etc.

1.13
Instances and Schemas

• Schema: It defines the structure of the database

• Logical Schema – the overall logical structure of the database
• Example: The database consists of information about a set of customers
and accounts in a bank and the relationship between them
• Physical schema – the overall physical structure of the database
• Instance – the actual content of the database at a particular point in
time

1.14
 Difference Between Schema and Instance
Schema Instance

The structure or design of the database A snapshot of the data stored in the
(tables, fields, relationships, constraints). database at a specific moment in time.

Dynamic; represents the actual data

Static; defines the organization of data.
stored.

Changes infrequently (when the database Changes frequently (as data is inserted,
structure is modified). updated, or deleted).

Defines the rules, constraints, and layout Reflects the current state of the data in the
for data storage. database.

Similar to declaring variables in a Similar to the values assigned to variables

program (defining their structure). at a particular moment.

Remains largely unchanged unless

Changes continuously as data is modified.
structural changes are made.

A table structure with columns like "ID", The actual records of data in those
"Name", "Age". columns (e.g., "1, John, 25").
1.15
View of Data
An architecture for a database system

1.17
Data Abstraction
Hide the complexity of data structures to represent data in the database
from users through several levels of data abstraction.
There are 3 levels of Data abstraction
• The physical or internal level:
This level describes how data is stored in the database. In other words,
this describes the physical storage structure of data in database. This
includes complex data structures.
• The logical or conceptual level:
This level describes what data is stored in the database and what
relationship exists between them. This includes relatively simple data
structures which are managed by DBA.
• The view or external level:
This level describes which part of database should be made available to
end user. This includes very simple data structures. This level play
important role in maintaining consistency in database

1.18
Three Schema Architecture in DBMS

1.19
Data Independence
• Data independence refers to the ability to modify a database
schema(Structure) at one level without affecting the schema at another
level.
• It provides abstraction between different levels of data storage, making
the system more flexible, easier to maintain, and less prone to
disruptions when changes occur.
• Importance
• Reduces Maintenance: Developers don't need to update applications
every time the database structure changes.
• Increases Flexibility: The database can be reorganized or optimized
internally without affecting user queries.
• Supports Long-Term Growth: As business needs evolve, the database
can be updated without breaking existing systems.

1.21
Types of Data Independence
• Physical Data Independence: Change in the internal level without
affecting the logical level.
• Logical Data Independence: Change in the logical level without
affecting the view level.

1.22
Physical Data Independence
:This allows changes to the physical storage of data (e.g., storage
devices, file organization) without impacting the conceptual schema or
applications.

Benefits:
• Backend optimizations without affecting users
• Reduces need for structural changes during storage upgrades
• Improves long-term maintainability

Example:
• Moving data files from the C: drive to the D: drive.
• Creating an index to speed up queries.
• Switching from HDD to SSD for better performance.
• Compressing data files to save space.

1.23
Logical Data Independence:

• This allows changes to the logical schema (e.g., adding or removing

attributes, changing data types) without affecting the external schema
(user views) or applications.
Benefits:
• Easier maintenance of application code.
• Allows smoother updates in growing systems.
• Helps support new business requirements without rewriting existing
queries.
Example:
•Adding a column like email in the employees table.
•Creating a new relationship between two tables.
•Merging two tables into a view for simplified access.

1.24
 Database Users
• A Database User interacts with a database for reading, updating, or managing
data.
• Users interact via DBMS applications or interfaces.

Types of Users
1. Database Administrator (DBA)
• Manages overall database architecture and security.
• Creates user accounts, controls access (GRANT/REVOKE).
• Responsible for backup, recovery, and performance tuning.
• Has superuser (DBA) privileges.

2. Naive / Parametric End Users

• No technical DBMS knowledge.
• Use predefined application forms.
• Examples: Bank clerks, railway ticket booking staff.

1.25
Database Users(contd.)
4. Sophisticated Users (System Analyst)
• Use SQL queries directly.
• Typically engineers or analysts.
• Do not build full applications but manipulate data as needed.
• Analyzes user requirements.
• Ensures system design meets end-user needs.
• Acts as a bridge between end users and developers.

[Link] Designers
• Define database schema, tables, constraints.
• Decide what data is stored and how it relates.
• Design before implementation.

1.26
Database Users(contd.)
6. Application Programmers
• Write backend code to access the database.
• Use High level languages.

7. Casual / Temporary Users

• Use the database occasionally.
• Need new information each time.
• Examples: Middle/higher-level managers.

1.27
When Not to Use a DBMS?
• There is some situation in which a DBMS may involve unnecessary
overhead costs that would not be incurred in traditional file
processing.
• The overhead cost of using a DBMS is due to the following:
High initial investment in hardware, software, and training.
The basic property that DBMS provide is defining and processing of data.
Overhead for providing security, concurrency control, recovery and integrity
functions.
• If the database designers and DBA don’t properly design the database
or if the database systems application is not implemented properly.
• You do not need a DBMS if your application deals with simple, flat
data structures where there is no need to create complex relationships
or querying capabilities.

1.28
When Not to Use a DBMS?(contd)

• Real-time, High-Throughput Data Processing: These can be

applications that process massive amounts of data in real-time. For
example, financial trading systems or social media platforms.
• Embedded Systems and IoT Devices: Resource-constrained devices
may not have enough physical resources to support a DBMS. Common
devices like smart appliances or wearables are examples.
• Temporary Data Storage: A DBMS may not be a optimal for
applications that update or delete data often. For example, session data in
a web application or temporary results of a data analysis job.

1.29
Types of Tier DBMS Architecture

• 1-tier architecture in DBMS has a single layer where the

database and user interface coexist.
• Two-tier DBMS architecture separates the system into two
layers, typically a client and a server.
• Three-tier architecture introduces a middle layer, such as an
application server, for additional processing.

1.30
1-tier architecture in DBMS

• Simplicity: Easy to set up since it only requires a single machine.

• Cost-Effective: No need for additional hardware, making it budget-
friendly.
• Easy Implementation: Quick deployment, making it ideal for small
projects or development environments.

1.31
Two-tier and three-tier architectures

1.32
Data Models
• A collection of tools for describing
• Data
• Data relationships
• Data semantics
• Data constraints
• Relational model
• Entity-Relationship data model (mainly for database design)
• Object-Based data models (Object-oriented Programming and relational
database model)- model combines features of the object-oriented
programming and relational data model.
• Semi-structured data model (XML)
• Other older models:
• Network model -consist of multiple parent segments and these
segments are grouped as levels
• Hierarchical model- the data is organized into a tree-like structure
where each record consists of one parent record and many children.
1.33
Relational Model

• All the data is stored in various tables.

• Example of tabular data in the relational model
Columns

Rows

1.34
A Sample Relational Database

1.35
Entity-Relationship Model (ER Model)

• The Entity-Relationship Model (ER Model) is a conceptual model for

designing a databases.
• It represents the logical structure of a database, including entities,
their attributes and relationships between them.
• Entity - An Entity represents a real-world object, concept or thing
about which data is stored in a database. Rectangles represent
entities in the ER Model.
• An entity may be an object with a physical existence (for example, a
particular person, car, house, or employee) or it may be an object
with a conceptual existence (for instance, a company, a job, or a
university course)
Real-World Objects: Person, Car, Employee etc.
. An entity type defines a collection (or set) of entities that have the
same attributes

1.36
• Entity Set: An entity refers to an individual object of an entity type,
and the collection of all entities of a particular type is called an entity
set.
• Relationships - A relationship in DBMS is used to describe the
association between entities. They are represented as diamond or
rhombus shapes in the ER diagram.

1.37
1.38
Attributes
Properties that define entities are called attributes. They are
represented by an ellipse shape.

There are four types of attributes:

• Key attribute
• Composite attribute
• Multivalued attribute
• Derived attribute

1.39
Attributes(contd.)

Key attribute:
• A key attribute can uniquely identify an entity from an entity set.
• Key attribute is represented by oval same as other attributes
however the text of key attribute is underlined

1.40
Attributes(contd.)

Composite Attributes-It can be divided into smaller subparts, which

represent more basic attributes with independent meanings

1.41
Attributes(contd.)

Multi-valued Attributes-If an attribute can take more than one value for
each entity instance, it is a multi-valued attribute. example for multi valued
attribute : telephone number of an employee

Derived Attribute
• An attribute which can be calculated or derived based on other attributes
is a derived attribute.
• Example for derived attribute : age of employee which can be calculated
from date of birth and current date.

1.42
Attributes(contd.)

1.43
1.44
Degree of a Relationship
1. Unary Relationship: When there is only ONE entity set participating
in a relation, the relationship is called a unary relationship. For
example, one person is married to only one person.

2. Binary Relationship: When there are TWO entities set participating in

a relationship, the relationship is called a binary relationship. For
example, a Student is enrolled in a Course.

1.45
Degree of a Relationship(contd.)

3. Ternary Relationship: When there are three entity sets participating in a

relationship, the relationship is called a ternary relationship.
4. N-ary Relationship: When there are n entities set participating in a
relationship, the relationship is called an n-ary relationship.

1.46
Cardinality of a Relationship
• Relationship cardinalities specify how many of each entity type is
allowed. Relationships can have four possible connectivities as given
below.
• One to one (1:1) relationship
• One to many (1:N) relationship
• Many to one (M:1) relationship
• Many to many (M:N) relationship
• The minimum and maximum values of this connectivity is called the
cardinality of the relationship

1.47
 Example for Cardinality – One-to-One (1:1)
• Employee is assigned with a parking space

1.48
Example for Cardinality – One-to-Many (1:N)
Organization has employees

1.49
Example for Cardinality – One-to-Many (M:1)
It is the reverse of the One to Many relationship. employee works in organization

1.50
Cardinality – Many-to-Many (M:N)
• Students enrolls for courses
• One student can enroll for many courses and one course can be
enrolled by many students. Hence it is a M:N relationship and
cardinality is Many-to-Many (M:N)

1.51
Total Participation of an Entity set

• A Total participation of an entity set represents that each entity in entity

set must have at least one relationship in a relationship set. For example:
In the below diagram each college must have at-least one associated
Student.

1.52
Steps to Create an Entity Relationship Diagram
• Identify the Entities: First, we need to define the principal objects of
your system. These could be tangible items, ideas, or phenomena that are
essential for the design of the database in analysis.
• Determine the Attributes of Each Entity: After identifying entities, that
define them by their attributes. It involves choosing which attributes are
needed for each of the entities and how they are connected.
• Establish Relationships Between Entities: Identify the relationship
between entities. For instance, if we already have two basic entities such
as “Employee” and “Department,” you might require a segmented
relationship, for instance, “Works_in,” to demonstrate that employees are
in departments.
• Define Cardinality: Cardinality defines how many occurrences of one
entity can be associated with another entity. The four common
cardinalities are one-to-one, one-to-many, many-to-one, and many-to-
many.
• Draw the ER Diagram: Entities should be depicted with rectangles,
attributes with ovals, and finally relationships with diamonds. Join these
components with straight like a straight line. Last but not least, identify
the cardinality for every relation of interest.

1.53
Generalization
• Generalization is a process in which the common attributes of more
than one entities form a new entity. This newly formed entity is
called generalized entity.
Note:
• Generalization uses bottom-up approach where two or more lower
level entities combine together to form a higher level new entity.
• The new generalized entity can further combine together with lower
level entity to create a further higher level generalized entity.

Generalization Example
• Lets say we have two entities Student and Teacher. Attributes of
Entity Student are: Name, Address & Grade Attributes of Entity
Teacher are: Name, Address & Salary
• We have created a new generalized entity Person and this entity has
the common attributes of both the entities.

1.54
The ER diagram after generalization:

1.55
Specialization
• Specialization is a process in which an entity is divided into sub-
entities.
• Reverse of generalization
• For example – Consider an entity employee which can be further
classified as sub- entities Technician, Engineer & Accountant because
these sub entities have some distinguish attributes.

1.56
Aggregration
• Aggregation is a process in which a single entity alone is not able to
make sense in a relationship so the relationship of two entities acts
as one entity

1.57
Aggregration(Contd.)

• In our example, the relationship “Works-On” between “Employee” &

“Project” acts as one entity that has a relationship “Manages” with
the entity “Manager”.

1.58
1.59
Features of ER model

• The extended E-R features are specialization, generalization, higher-

and lower-level entity sets, attribute inheritance, and aggregation.

1.60
Advantages and Disadvantages of ER Modeling

• ER Modeling is simple and easily understandable. It is represented in

business users language and it can be understood by non-technical
specialist.
• Intuitive and helps in Physical Database creation.
• Can be generalized and specialized based on needs.
• Can help in database design.
• Gives a higher level description of the system.
• Disadvantages
• Physical design derived from E-R Model may have some amount of
ambiguities or inconsistency.
• Sometime diagrams may lead to misinterpretations

1.61
Database Languages
• Database languages are used to interact with databases,
allowing users to define, manipulate, and control the data
stored within them.
• There are four main categories:
 Data Definition Language (DDL)
 Data Manipulation Language (DML),
 Data Control Language (DCL)
 Transaction Control Language (TCL).

1.62
1. Data Definition Language (DDL)

• Specification notation for defining the database schema

Example: create table instructor (
ID char(5),
name varchar(20),
dept_name varchar(20),
salary numeric(8,2))
• DDL compiler generates a set of table templates stored in a data
dictionary
• Data dictionary contains metadata (i.e., data about data)
• Database schema
• Integrity constraints
• Primary key (ID uniquely identifies instructors)
• Authorization
• Who can access what

1.63
[Link] Definition Language (DDL):

• DDL is used to define the structure of the database, including

creating, altering, or deleting database objects like tables, indexes,
and views.
Common DDL commands include:
• CREATE: Creates new database objects.
• ALTER: Modifies the structure of existing database objects.
• DROP: Deletes database objects.
• TRUNCATE: Removes all records from a table while keeping the
table structure.
• RENAME: Changes the name of a database object, such as a table,
column or index

1.64
2. Data Manipulation Language (DML)
• Language for accessing and updating the data organized by the
appropriate data model
• DML also known as query language
• There are basically two types of data-manipulation language
• Procedural DML -- require a user to specify what data are needed
and how to get those data.
• Declarative DML -- require a user to specify what data are
needed without specifying how to get those data.
• Declarative DMLs are usually easier to learn and use than are
procedural DMLs.
• Declarative DMLs are also referred to as non-procedural DMLs
• The portion of a DML that involves information retrieval is called a
query language.

1.65
[Link] Manipulation Language (DML):

• DML is used to manipulate the data within the database, including

inserting, retrieving, updating, and deleting data.

Common DML commands include:

• SELECT: Retrieves data from the database.
• INSERT: Adds new data into a table.
• UPDATE: Modifies existing data in a table.
• DELETE: Removes data from a table.

1.66
[Link] Control Language (DCL):

• DCL is used to control access to the database, managing user

permissions and privileges.
Common DCL commands include:
• GRANT: Grants users specific permissions to access database objects.
• REVOKE: Revokes previously granted permissions.

1.67
[Link] Control Language (TCL):

• TCL is used to manage the changes made to the database during a

transaction, ensuring data consistency and integrity.
Common TCL commands include:
• COMMIT: Saves all changes made during a transaction.
• ROLLBACK: Reverts all changes made during a transaction,
restoring the database to its previous state.
• SAVEPOINT: Sets a savepoint within a transaction, allowing for
partial rollbacks.

1.68
Database Design
The process of designing the general structure of the database:
• Logical Design – Deciding on the database schema. Database design
requires that we find a “good” collection of relation schemas.
• Business decision – What attributes should we record in the
database?
• Computer Science decision – What relation schemas should we
have and how should the attributes be distributed among the
various relation schemas?
• Physical Design – Deciding on the physical layout of the database

1.69
Database Architecture

1.70
Database Engine
• A database system is partitioned into modules that deal with each of
the responsibilities of the overall system.
• The functional components of a database system can be divided into
• The storage manager,
• The query processor component,
• The transaction management component.

1.71
Database Architecture
• Centralized databases
• One to a few cores, shared memory
• Client-server,
• One server machine executes work on behalf of multiple client
machines.
• Parallel databases
• Many core shared memory
• Shared disk
• Shared nothing
• Distributed databases
• Geographical distribution
• Schema/data heterogeneity

1.72
Storage Manager
• A program module that provides the interface between the low-level
data stored in the database and the application programs and queries
submitted to the system.
• The storage manager is responsible to the following tasks:
• Interaction with the OS file manager
• Efficient storing, retrieving and updating of data
• The storage manager components include:
• Authorization and integrity manager
• Transaction manager
• File manager
• Buffer manager

1.73
Storage Manager (Cont.)

• The storage manager implements several data structures as part of the

physical system implementation:
• Data files -- store the database itself
• Data dictionary -- stores metadata about the structure of the
database, in particular the schema of the database.
• Indices -- can provide fast access to data items. A database index
provides pointers to those data items that hold a particular value.

1.74
Query Processor

• The query processor components include:

• DDL interpreter -- interprets DDL statements and records the
definitions in the data dictionary.
• DML compiler -- translates DML statements in a query language
into an evaluation plan consisting of low-level instructions that the
query evaluation engine understands.
• The DML compiler performs query optimization; that is, it picks
the lowest cost evaluation plan from among the various
alternatives.
• Query evaluation engine -- executes low-level instructions
generated by the DML compiler.

1.75
Query Processing

1. Parsing and translation

2. Optimization
3. Evaluation

1.76
Transaction Management
• A transaction is a collection of operations that performs a single
logical function in a database application
• Transaction-management component ensures that the database
remains in a consistent (correct) state despite system failures (e.g.,
power failures and operating system crashes) and transaction failures.
• Concurrency-control manager controls the interaction among the
concurrent transactions, to ensure the consistency of the database.

1.77
Database Administrator
A person who has central control over the system is called a database
administrator (DBA). Functions of a DBA include:
• Schema definition
• Storage structure and access-method definition
• Schema and physical-organization modification
• Granting of authorization for data access
• Routine maintenance
• Periodically backing up the database
• Ensuring that enough free disk space is available for normal
operations, and upgrading disk space as required
• Monitoring jobs running on the database

1.78
History of Database Systems
• 1950s and early 1960s:
• Data processing using magnetic tapes for storage
• Tapes provided only sequential access
• Punched cards for input
• Late 1960s and 1970s:
• Hard disks allowed direct access to data
• Network and hierarchical data models in widespread use
• Ted Codd defines the relational data model
• Would win the ACM Turing Award for this work
• IBM Research begins System R prototype
• UC Berkeley (Michael Stonebraker) begins Ingres prototype
• Oracle releases first commercial relational database
• High-performance (for the era) transaction processing

1.79
History of Database Systems (Cont.)

• 1980s:
• Research relational prototypes evolve into commercial systems
• SQL becomes industrial standard
• Parallel and distributed database systems
• Wisconsin, IBM, Teradata
• Object-oriented database systems
• 1990s:
• Large decision support and data-mining applications
• Large multi-terabyte data warehouses
• Emergence of Web commerce

1.80
History of Database Systems (Cont.)
• 2000s
• Big data storage systems
• Google BigTable, Yahoo PNuts, Amazon,
• “NoSQL” systems.
• Big data analysis: beyond SQL
• Map reduce and friends
• 2010s
• SQL reloaded
• SQL front end to Map Reduce systems
• Massively parallel database systems
• Multi-core main-memory databases

1.81