DBMS - 24CS403 Module -1 Notes

Chapter - 01
Introduction to Databases
1.1 Introduction
1.2 An Example
1.3 Characteristics of the Database Approach
1.4 Actors on the Scene
1.5 Workers behind the Scene
1.6 Advantages of Using the DBMS Approach

1.1 Introduction
▪ A database is a collection of related data.
▪ Data - Raw facts and figures, unprocessed.
▪ Information - Processed data that is meaningful and useful.

Example: Cricket Match

• Data:
o Player A scored 45 runs
o Player B took 2 wickets
o Total overs: 20
• Information:
o “Player A contributed the highest score in the match, and Player B was the key bowler,
helping the team win in 20 overs.”
Database Management System (DBMS)
• Software to create, maintain, and use databases.
• Acts as interface between users and data
• Examples: MySQL, Oracle, PostgreSQL

Functions of a DBMS
• Define database structure (schema)
• Store and retrieve data
• Update data
• Share data among users
• Security and recovery

1
Database System

• Database + DBMS software

• Includes:
o Data
o Metadata
o Application programs

1.2 Advantages of DBMS over File Processing System

a) Self-describing Nature of a database system
b) Insulation between Programs and Data, and data abstraction
c) Support of Multiple views of the data
d) Sharing of data and multiuser transaction processing

a) Self-describing Nature of a database system

▪ A DBMS contains not only the database itself but also a complete description of the database
structure and constraints.
▪ This description is stored in the DBMS catalog, which includes:
o Structure of each table - table definitions, column types, keys, and constraints.
o Type and storage format of each data item
o Constraints on the data
▪ The information in the catalog is called metadata.
▪ Role of the catalog: -
o Used by DBMS software to access and manipulate data correctly
o Used by database users who need information about the database structure
▪ Difference from traditional file processing: In traditional systems, data definitions are embedded in
application programs (e.g., C++ structs or classes).

b) Insulation between Programs and Data, and data abstraction

Program-Data Independence
• Traditional File Processing:
o Data structure is embedded in application programs.
o Any change in file structure requires modifying all programs that access the file.
o Example: Adding Birth_date to a STUDENT record requires rewriting access programs.
o

2
 • DBMS Approach:
o Data structure is stored in the catalog, separate from access programs.
o Programs do not need to change when data structure changes.
o Only the catalog is updated; next time a program refers to it, the new structure is used.

This property is called program-data independence.

Program-Operation Independence (Object-Oriented/Relational Systems)

• Users can define operations (functions or methods) as part of the database.
• Interface (Signature):
o Includes operation name and data types of arguments.
• Implementation (Method):
o Can be changed without affecting the interface.
• Programs can invoke operations without knowing their implementation details.

This property is called program-operation independence.

Data abstraction is the characteristic that allows:

• Program-data independence
• Program-operation independence

c) Support of Multiple Views of the Data

▪ A view is a user-specific perspective of the database.

▪ View may be:
o A subset of the database
o Virtual data derived from the database but not physically stored
▪ DBMS provides facilities to define multiple views, allowing users to access only the relevant data.

d) Sharing of data and multiuser transaction processing

▪ A multiuser DBMS allows multiple users to access the database simultaneously, enabling data
sharing across many applications.

▪ The DBMS uses concurrency control to ensure correct updates when many users access the same data
(e.g., seat reservation systems – OLTP).

▪ A transaction is a sequence of database operations that must execute correctly as a unit.

3
 Key transaction properties:

➢ Isolation: Each transaction appears to run independently of others.

➢ Atomicity: All operations of a transaction are executed, or none are.

1.4 Actors on the Scene

Many people are involved in the design, use, and maintenance of a large database with hundreds or
thousands of users. People whose jobs involve the day-to-day use of a large database are called as actors on
the scene. The actors are :

a) Database Administrators
b) Database Designers
c) End Users
d) System Analysts and Application Programmers (Software Engineers)

a) Database Administrators (DBA)

▪ In a database environment, the primary resource is the database and the secondary resource is the
DBMS and related software.
▪ The Database Administrator (DBA) is responsible for authorizing access, coordinating and monitoring
database use, and acquiring required hardware and software.
▪ The DBA is accountable for security, performance, and reliability, and in large organizations is
supported by a DBA staff.
▪ Key idea: The DBA ensures the database system runs securely, efficiently, and reliably.

b) Database Designer

▪ Identify and model data: Decide what data to store and choose suitable structures before database
implementation.

▪ Gather user requirements: Communicate with all user groups to understand needs and design appropriate
database views.

▪ Key idea: Database designers ensure the database structure correctly supports all users and applications.

c) End Users & their types

End Users: People who access the database for queries, updates, and reports; the database exists mainly for
their use.

4
Categories of End Users:

▪ Casual End Users: Use the database occasionally; need different information each time; use query
interfaces.

▪ Naive (Parametric) End Users: Perform routine tasks using canned transactions (predefined
queries/updates); examples include bank customers, reservation systems, shipping clerks, and social
media users.

▪ Sophisticated End Users: Engineers, scientists, analysts who use DBMS tools to create complex
queries and applications.

▪ Standalone Users: Maintain personal databases using ready-made, menu- or GUI-based software
(e.g., personal finance software).

d) System Analysts and Application Programmers (Software Engineers)

System Analysts:

▪ Study and determine the requirements of end users, especially naive/parametric users

▪ Design specifications for canned transactions (standard queries and updates)

Programmers:

▪ Implement these specifications as programs

▪ Test, debug, document, and maintain the application code

Note - Analysts design what is needed; programmers build how it works using DBMS features.

1.5 Workers behind the Scene

These people build, maintain, and run the DBMS software and environment.

a)DBMS system designers and implementers -

DBMS system designers and implementers develop the DBMS software. A DBMS has many modules such
as catalog management, query processing, interface processing, data access and buffering, concurrency
control, recovery, and security.

b)Tool Developers:
They create software tools that help in database design, modeling, performance monitoring, interfaces,

5
prototyping, simulation, and test data generation. These tools are usually optional and often developed by
independent vendors.

c) Operators and Maintenance Personnel:

They are responsible for the running and maintenance of the DBMS hardware and software environment.

1.6 Advantages of DBMS

a) Controlling Redundancy
b) Restricting Unauthorized Access
c) Providing Persistent Storage for Program Objects
d) Providing Storage Structures and Search Techniques for Efficient Query Processing
e) Providing Backup and Recovery
f) Providing Multiple User Interfaces
g) Representing Complex Relationships among Data
h) Enforcing Integrity Constraints
i) Permitting Inferencing and Actions Using Rules and Triggers
j) Additional Implications of Using the Database

a) Controlling Redundancy
Problems of Data Redundancy
▪ The same data stored in multiple files causes duplicate updates (same change done many times).
▪ It wastes storage space.
▪ It may lead to data inconsistency if different files store different values for the same data.
Database Approach
▪ Each data item is stored only once using data normalization, which saves space and maintains
consistency.
▪ Sometimes controlled redundancy (denormalization) is used to improve query performance by keeping
related data together.

b) Restricting Unauthorized Access

▪ When many users share a database, not all users are allowed to access all data. Some data (like
salary or financial information) is confidential, and users may have different permissions such as
only retrieving data or retrieving and updating data.

6
 ▪ Users access the database through accounts and passwords, and the DBMS security and
authorization system controls what each user can do.

c) Providing Persistent Storage for Program Objects

▪ Databases can store program objects and data structures permanently. Normally, objects in
languages like C++ or Java disappear when the program ends unless they are saved in files.
▪ The stored objects are called persistent objects, because they remain even after the program stops
and can be retrieved later.
▪ Object-Oriented DBMS allows these objects to be stored directly in the database without manual
conversion.

d) Providing Storage Structures and Search Techniques for Efficient Query Processing
▪ DBMS must efficiently execute queries and updates since databases are stored on disk.
To speed up searching, DBMS uses indexes (based on tree or hash structures) and buffers/caches to
temporarily keep data in main memory.
▪ The query processing and optimization module selects the most efficient execution plan, while the
DBA decides which indexes to create.

e) Providing Backup and Recovery

▪ A DBMS provides backup and recovery facilities to handle hardware or software failures.
If a failure occurs during a transaction, the recovery subsystem restores the database to its previous
consistent state.
▪ It also performs disk backups to protect data from major failures like disk crashes.

f) Providing Multiple User Interfaces

▪ Because different users use a database, a DBMS provides various user interfaces such as mobile
apps, query languages, programming interfaces, forms, command codes, menus, and natural
language interfaces.
▪ These interfaces help users with different technical skills access the database easily.
▪ Forms-style interfaces and menu-driven interfaces are commonly known as Graphical User
Interfaces (GUI).

7
g) Representing Complex Relationships among Data
▪ A database contains different types of related data. For example, a student record may be related to
multiple grade reports, and a section may be related to one course and many grade reports.
▪ A DBMS must represent these relationships, allow new relationships to be defined, and retrieve or
update related data efficiently.

g) Enforcing Integrity Constraints

▪ A DBMS enforces integrity constraints to maintain correct and valid data.
o Data type constraints specify the type of data (e.g., Class must be an integer).
o Referential integrity ensures related records exist (e.g., each section must relate to a course).
o Key/uniqueness constraints ensure unique values (e.g., Course_number).
▪ These rules are defined during database design and are often called business rules.

h) Additional Implications of Using the Database Approach

▪ Enforcing Standards: DBA can define common data names, formats, and report structures,
improving coordination.
▪ Flexibility: Database structure can be modified or extended without affecting existing data or
applications.
▪ Up-to-Date Information: Updates by one user are immediately visible to others.
▪ Economies of Scale: Centralized data and resources reduce duplication and overall cost.
▪ Reduced Application Development Time: New applications can be developed quickly using the
existing database.

8
 Chapter 2
Database System Concepts and Architecture

2.1 Data Models, Schemas, and Instances

2.2 Three-Schema Architecture and Data Independence
2.3 Database Languages and Interfaces
2.4 The Database System Environment
2.5 Centralized and Client/Server Architectures for DBMSs
2.6 Classification of Database Management Systems
2.1 Data Models, Schemas, and Instances
A Data Model provides:
• Data types
• Relationships
• Constraints
• Basic operations (insert, delete, update, retrieve)
Dynamic Aspect (Behavior)- Modern models allow User-defined operations
Example: COMPUTE_GPA (Student)

Categories of Data Models - based on the concepts used to describe database structure.

a) High-Level (Conceptual) Data Models

b) Representational (Implementation) Data Models
c) Physical (Low-Level) Data Models

a) High-Level (Conceptual) Data Models

▪ Purpose - Describe data the way users understand it.
▪ Concepts Used – Entities, Attributes, Relationships
▪ Example - Entity–Relationship (ER) Model

b) Representational (Implementation) Data Models

Purpose - Bridge between conceptual and physical models.
Characteristics
• Easy for users to understand
• Can be directly implemented in DBMS
• Hide storage details
9
 Types
• Relational Model (most widely used)
• Network Model
• Hierarchical Model
c) Physical (Low-Level) Data Models
Purpose - Describe how data is actually stored on disk.
Concepts Include
o Record formats
o File organization
o Indexes

Comparison Table

Type Focus Used By Example

Conceptual What data Designers, Users ER Model

Representational Logical structure DBMS Relational Model

Physical How stored System programmers File structures

Schemas, Instances, and Database State

The database schema is the overall design or structure of the database.

It is defined during the database design phase and does not change frequently.

Schema Diagram: displays the structure of each record type but not the actual instances of records.

Database State (Instance) - The database state is the actual data stored at a particular moment in time.
Also called: Snapshot, Current instance, Occurrences

10
Schema vs State

Schema Database State

Structure of database Actual data

Defined once Changes frequently

Called Intension Called Extension

Stored in DBMS catalog (meta-data) Stored as records

2.2 Three-Schema Architecture and Data Independence

The Three-Schema Architecture

a)Internal Level (Physical Level)

Internal Schema
• Describes how data is stored

• Uses physical data model

• Includes:
o File structures
o Record formats
o Indexes
o Access paths
11
b) Conceptual Level (Logical Level)
Conceptual Schema
• Describes complete structure of database
• Hides physical details
• Includes:
o Entities
o Attributes
o Relationships
o Constraints
o Data types

c) External Level (View Level)

External Schema (User Views)
▪ The external level is the top level of the three-schema architecture.
▪ It contains multiple external schemas (user views). Each schema represents how a particular user
or user group sees the database.
▪ Every view shows only the required part of the database and hides the rest of the data.

Example:
• Faculty sees: StudentID, Name, Marks
• Accounts sees: StudentID, Name, Fee

Data Independence is the ability to change one level of the database without affecting the next higher level.
The TWO types are :
a) Logical Data Independence
b) Physical Data Independence

Logical Data Independence

Definition: Ability to change the conceptual schema without changing External schemas & application
programs

What Can Be Changed?

• Add/remove attributes
• Add/remove tables
• Modify constraints

12
 Example: If we modify the GRADE_REPORT structure, user views that don’t use those changed
fields should continue working.

Physical Data Independence

Definition: Ability to change the internal schema without changing:
• Conceptual schema
• External schemas

What Can Be Changed?

• File organization
• Indexes
• Access paths
• Storage methods

Example: Adding an index to retrieve SECTION records faster

→ Queries remain same
→ Only performance improves
✔ Easier to achieve

Comparison Table
Logical Data Independence Physical Data Independence

Change conceptual schema Change internal schema

Does not affect views Does not affect conceptual level

Harder to achieve Easier to achieve

Structural changes allowed Storage changes allowed

13
 2.3 Database Languages and Interfaces
After database design, different languages are used to define, manipulate, and control the database.
a) View Definition Language (VDL)
b) Data Definition Language (DDL)
c) Storage Definition Language (SDL)
d) Data Manipulation Language (DML)
o High-Level DML (Declarative / Set-Oriented)
o Low-Level DML (Procedural)

a) Data Definition Language (DDL)

Purpose: Used to define database structure (schema).

Used By: DBA and database designers

Functions:
o Create tables
o Define attributes
o Define constraints
o Modify schema
Example (SQL):
CREATE TABLE STUDENT (
RollNo INT PRIMARY KEY,
Name VARCHAR(20));

DDL compiler stores schema in DBMS Catalog (Meta-data).

b)Storage Definition Language (SDL)

Purpose: Defines internal schema (physical storage).
o File organization
o Indexes
o Access paths

In modern DBMS,
• there is no separate Storage Definition Language (SDL).
• Instead, the internal schema (physical storage details) is defined using DBMS functions, storage
parameters, and configuration settings.

14
 • These allow the DBA to control how data is stored, such as file organization, indexing, and mapping
data to disk storage.

c)View Definition Language (VDL)

Purpose: Defines user views (external schema).

Note - In relational DBMS → no separate VDL, instead SQL supports it.

d)Data Manipulation Language (DML)

Purpose: Used to manipulate data.
Operations: SELECT, INSERT, UPDATE, DELETE
🔹 Types of DML
i)High-Level DML (Declarative / Set-Oriented)
• Specifies what to retrieve
• Works on multiple records at once
• Example: SQL
• Called Set-at-a-time DML
• Example – SQL query
ii)Low-Level DML (Procedural)
• Specifies how to retrieve
• Processes one record at a time
• Needs loops
• Called Record-at-a-time DML

🔹 Host Language vs Data Sublanguage

• If DML is embedded in C/Java →
C/Java = Host Language
SQL = Data Sublanguage
• If used independently → called Query Language
Embedded SQL in C

#include <stdio.h>

EXEC SQL INCLUDE sqlca;

int main() {
int emp_id = 101;

15
 EXEC SQL UPDATE Employee
SET salary = salary + 1000
WHERE id = :emp_id;

printf("Salary updated\n");
return 0;
}

Summary Table

Language Purpose

DDL Define schema

SDL Define physical storage

VDL Define views

DML Manipulate data

2.3.2 DBMS Interfaces

DBMS provides different interfaces for different types of users.
a) Menu-Based Interface
• User selects options from menus
• No need to remember SQL syntax
• Used in web applications (drop-down menus)
• Example: Online shopping filters

b) Mobile App Interfaces

• Apps for banking, booking, insurance
• Login + limited options menu
• Easy access to personal data

c) Forms-Based Interface
• User fills forms to insert or search data
• Designed for naive users
• Used for routine transactions
• Example: Bank deposit form

16
 d) Graphical User Interface (GUI)
• Displays schema in diagram form
• User interacts visually
• Often uses menus + forms

e) Natural Language Interface

• Accepts queries in English
• Converts into SQL automatically
• Example: “Show all students in CS department”
f. Keyword-Based Search
• Similar to search engines
• User types keywords
• System matches and ranks results

g. Speech Input/Output
• Voice-based query system
• Used in banking & travel inquiry systems

h. Interfaces for Parametric Users

• For users performing repeated tasks
• Uses shortcut keys
• Example: Bank teller system

i. Interfaces for DBA

• Special privileged commands
• Used for schema change, authorization, storage tuning

2.4 The Database System Environment

2.4.1 DBMS Component Modules

DBMS has Two Main Parts. - The top part of the figure refers to the various users of the database environment
and their interfaces. The lower part shows the internal modules of the DBMS responsible for storage of data and
processing of transactions1.

Top Part – Users & Interfaces

Explains Who uses the database system and how they interact with it.
17
Different users are :
a) DBA (Database Administrator)
b) Casual Users
c) Application Programmers
d) Parametric Users

a)DBA (Database Administrator)

• DDL – used to defines conceptual & internal schema
• Privileged commands – to grants authorization (GRANT/REVOKE)
• DDL compiler - checks DDL statements for syntax and semantic errors, processes schema definitions,
and stores the database structure (meta-data) in the system catalog.
• Tunes performance (indexing, storage parameters)
• Manages backup & recovery

b)Casual Users
• Interactive query - is a query entered directly by a user through a query interface (like SQL prompt) to
retrieve or manipulate data.

18
 • Query Compiler - checks the syntax and correctness of a query and converts it into an internal
representation for execution.
• Query Optimizer - concerned with the rearrangement and possible reordering of operations, elimination
of redundancies, and use of efficient search algorithms during execution. Selects the most efficient one
based on cost and performance.

c)Application Programmers
• Pre-compiler - extracts embedded SQL statements from a host language program and sends them to the
DML compiler for processing. Used when SQL (DML) is embedded inside a host programming
language like C, Java, or Python.
• DML Compiler - processes Data Manipulation Language (DML) statements such as SELECT, INSERT,
UPDATE, and DELETE. It processes DML statements, checks for errors, and converts them into
executable instructions for the runtime database processor.
• Host Language Compiler - compiles the host language part of a program and links it with the compiled
DML code to produce an executable program.

d)Parametric Users

• Compiled Transaction (also called Canned Transaction) is a prewritten and precompiled database
program. Supply only input parameters
• Parametric Users are users who execute these predefined transactions by supplying input values.
• Example: Bank withdrawal (account no, amount)

Bottom Part – Internal DBMS Modules - These modules manage storage & processing.
a) The System Catalog (Data Dictionary) –
▪ Repository that stores meta-data about the database structure, constraints, and storage information.
▪ Stores - Table names, Attribute names and data types, Primary keys & foreign keys, Constraints &
Index information
▪ Used by - Query Compiler, Query Optimizer, DBA, Runtime processor

b) Runtime Database Processor - is the module that actually executes database operations.
▪ Executes - Privileged commands, Query execution plans (from optimizer) & Compiled / canned
transactions

19
 ▪ Works with –
o System Catalog → For schema information
o Stored Data Manager → To access data
o Buffer Manager → For memory management
o Concurrency & Recovery modules → For transaction control

c) Stored Data Manager - module that controls access to data stored on disk.
▪ Manages physical storage of database files
▪ Handles disk read/write operations
▪ Works with the Operating System for low-level I/O
▪ Retrieves and stores records as requested by the Runtime Processor

d) Stored Database - refers to the actual data stored physically on disk.

It contains:
▪ User data (tables, records)
▪ Index files
▪ Database files
▪ Possibly logs for recovery

Database System Utilities

DBMSs have database utilities that help the DBA manage the database system. Common utilities have the
following types of functions.

a)Loading. - is the process of importing existing data files into a database using a loading utility that
reformats the data according to the target database schema. Exampple – CSV file database

b)Backup - process of creating a copy of the database to restore data in case of failure. It can be full backup
or incremental backup.
▪ Why Backup is Needed? - Disk crash, System failure, Natural disasters, Accidental deletion
▪ Full backup – full database is copied
▪ Incremental backup - Only changes made after last backup are stored.

c)Database Storage Reorganization - a utility used to restructure database files to improve performance.
▪ Why is it needed? - Over time, files become fragmented & Access becomes slow
▪ What does it do? - Changes file organization - Sequential → Indexed – Heap → Clustered. Creates new
indexes

20
 d)Performance monitoring - utility that collects usage statistics and helps the DBA optimize database
performance.
▪ What does it monitor? - Query execution time, CPU usage, Disk I/O operations.
▪ The DBA uses this information to decide whether to reorganize files, Add or remove indexes

2.5 Centralized and Client/Server Architectures for DBMSs

In centralized DBMS architecture, all database processing, application execution, and user interface operations
are performed on a single central computer.

▪ All processing happens on one machine - DBMS functions & Application programs.
▪ Users access system through – Terminals
▪ Terminals only display output, but no processing power.
▪ Used in old systems – where database was stored on mainframe computers

Basic Client/Server Architectures

▪ Client/Server architecture is a network model where clients request services and specialized servers
provide those services.
▪ Client - PC, workstation, or mobile device; Provides user interface; Has local processing power
▪ Server - A specialized machine that provides specific services.

Examples:
File Server → Stores files
Printer Server → Manages printers
Web Server → Hosts websites

21
 E-mail Server → Manages emails
Database Server → Manages database

Two-Tier Client/Server Architectures for DBMS

In two-tier architecture, the client handles the user interface and application programs, while the server
handles SQL processing and database management.

Division of work – between Server & Client

How Communication Happens –
o Client program establishes connection to DBMS
o Sends SQL queries
o Server processes query
o Results sent back to client
Example for communication - ODBC (Open Database Connectivity), JDBC (For Java)

2-Tier : Because software is divided into:

• Tier 1 → Client
• Tier 2 → Server

Three-Tier and n-Tier Architectures for Web Applications

22
 Three-tier architecture, adds a middle layer between client and database server. The client handles
presentation, the middle tier handles business logic, and the database server manages data storage and
processing.

a)Presentation Layer (Client Tier)

• Web browser / Mobile app
• User interface
• Displays results
• Accepts user input

b)Application / Business Logic Layer (Middle Tier)

• Processes client requests
• Applies business rules
• Validates user credentials
• Sends queries to DB server
• Formats results (e.g., dynamic web pages)
• Improves security
• Reduces load on DB server

23
 Data Layer (Database Server Tier)
• Stores database
• Executes SQL queries
• Handles transactions
• Manages storage

Advantages
✔ Better security
✔ Better scalability
✔ Easy maintenance
✔ Load balancing possible

n-Tier Architecture
• Extension of three-tier
• Business logic divided further
• Used in ERP & CRM systems
• Middleware connects multiple databases

2.6 Classification of Database Management Systems

Several criteria can be used to classify DBMSs
a) Based on Data Model
• Relational DBMS (RDBMS):
o Based on the relational model.
o Most common commercial systems.
o Use SQL for queries.
• Object DBMS:
o Based on object-oriented concepts.
o Limited commercial use.
• Object-Relational DBMS:
o Combines relational and object-oriented features.
o Evolved from RDBMS incorporating object concepts.
• NoSQL / Big Data Systems:
o Use alternative models:
▪ Document-based
▪ Graph-based
▪ Column-based

24
 Key-value
▪
• Key-Value Stores:
o Specialized for storing data as key-value pairs.
• Hierarchical & Network DBMS:
o Legacy systems based on hierarchical/tree or network/graph models.
• XML DBMS:
o Use XML tree structure for data storage.
o Can be native XML DBMS or relational DBMS with XML support.

b) Based on Number of Users

• Single-user DBMS:
o Supports only one user at a time.
o Commonly used on PCs.
• Multiuser DBMS:
o Supports concurrent access by multiple users.
o Most commercial DBMSs fall in this category.

c) Based on Distribution of Database Sites

• Centralized DBMS:
o Data and DBMS reside at a single computer site.
o Can support multiple users concurrently.
o All operations are handled by that single site.
• Distributed DBMS (DDBMS):
o Database and DBMS software are spread over multiple sites connected via a network.
o Improves reliability and performance.
o Data replication is common so that a site failure does not make data unavailable.
o Big data systems are often massively distributed, spanning hundreds of sites.

d) Based on Cost
• Open Source / Free DBMS:
o Examples: MySQL, PostgreSQL.
o Supported by third-party vendors for additional services.
• Low-Cost / Personal Versions:
o Some RDBMS offer personal editions under $100.
o May include limited functionality suitable for individuals or small projects.
25
 • Commercial / Enterprise Systems:
o Large-scale DBMS sold in modular form with features like:
▪ Distribution and replication
▪ Parallel processing
▪ Mobile support
▪ Configurable parameters
o Sold via licenses:
▪ Site licenses: unlimited use at a location
▪ User-based licenses: limited to number of concurrent users or seats
o Standalone single-user versions (e.g., Microsoft Access) may be sold per copy or bundled with
hardware.

e) Based on Purpose
• General-Purpose DBMS:
o Designed to support a wide range of applications.
o Flexible and can be used for multiple domains without major changes.
• Special-Purpose DBMS:
o Designed for specific applications where performance is critical.
o Cannot be used for other applications without major modifications.
o Examples:
▪ Airline reservation systems
▪ Telephone directory systems
o Often implemented as OLTP (Online Transaction Processing) systems:
▪ Must handle large numbers of concurrent transactions efficiently.

Details of classification Based on Data Model

Relational Model
• Represents data as tables (relations).
• Each table can be stored as a separate file.
• Uses SQL for queries.
• Supports limited user views.
• Can incorporate object-oriented features → Object-Relational DBMS.

26
Object Model
• Database defined in terms of objects, their properties, and operations.
• Objects with same structure belong to classes, organized hierarchically or as acyclic graphs.
• Operations are implemented as methods.
• Object-relational systems extend RDBMS to include object concepts.

Big Data / NoSQL Models

• Key-Value: Associates unique keys with values for fast access.
• Document: Stores data as JSON-like documents (semi-structured).
• Graph: Stores objects as nodes and relationships as directed edges.
• Column-Based: Stores columns of rows clustered on disk pages for fast access; supports multiple
versions.

XML Model
• Uses tree structures; data represented as elements with tags.
• Combines database concepts with document representation.
• Used in native XML DBMSs or as extensions to relational DBMS.

Legacy Models
• Network Model (CODASYL/DBTG):
o Represents data as record types and 1:N relationships (sets).
o Requires record-at-a-time language, often embedded in host languages (e.g., COBOL).
• Hierarchical Model (IMS/DL1):
o Data represented as tree structures.
o No standard DML; DL/1 was widely used.
o Dominated the market from 1965–1985.

Tree diagram of all classification methods ::

DBMS
│
├─ 1. Based on Data Model
│ ├─ Relational (RDBMS) – SQL systems
│ ├─ Object
│ ├─ Object-Relational
│ ├─ NoSQL / Big Data

27
│ │ ├─ Document-based
│ │ ├─ Graph-based
│ │ ├─ Column-based
│ │ └─ Key-Value
│ ├─ Hierarchical
│ ├─ Network
│ └─ XML DBMS (Native XML or RDBMS with XML support)
│
├─ 2. Based on Number of Users
│ ├─ Single-user
│ └─ Multiuser
│
├─ 3. Based on Distribution of Sites
│ ├─ Centralized DBMS – data at single site
│ └─ Distributed DBMS (DDBMS)
│ ├─ Data replicated across sites
│ └─ Big Data: massively distributed
│
├─ 4. Based on Cost
│ ├─ Open Source / Free – MySQL, PostgreSQL
│ ├─ Low-Cost / Personal Editions – under $100
│ └─ Commercial / Enterprise
│ ├─ Modular features (distribution, replication, parallel processing)
│ ├─ Site license or user-based license
│ ├─ Standalone versions – per copy
│ └─ Extra features at additional cost (data warehousing, mining, etc.)
│
└─ 5. Based on Purpose
├─ General-Purpose DBMS – flexible for multiple applications
└─ Special-Purpose DBMS – optimized for specific applications
├─ Examples: airline reservations, telephone directories
└─ Usually OLTP systems (handle many concurrent transactions)
++++++++++++++++++++++++++++++++++++++++++

28
Chapter 3 Data Modeling Using the Entity Relationship (ER) Model
Topics ::
3.1 High-Level Conceptual Data Models for Database Design
3.2 A Sample Database Application
3.3 Entity Types, Entity Sets, Attributes, and Keys
3.4 Relationship Types, Relationship Sets, Roles, and Structural Constraints
3.5 Weak Entity Types
3.6 Refining the ER Design for the COMPANY Database
3.7 ER Diagrams, Naming Conventions, and Design Issues

Conceptual modeling is a crucial step in designing a successful database application. A database application
includes both the database and the programs that access and update it. Here, the focus is only on database
structure and constraints.

To do this, the Entity–Relationship (ER) model is used. The ER model helps in conceptual database design
by identifying entities, relationships, and constraints, and representing them using ER diagrams, which are
widely used in practice.

Conceptual model focuses on what data is needed, not how it is stored or processed.

3.1 High-Level Conceptual Data Models for Database Design

Database Design Process:
a)Requirements Collection & Analysis - Designers talk to users to find what data is needed and what
operations (queries, updates) will be performed.
Output: a clear, detailed list of data requirements + functional requirements.

b)Conceptual Design - Create a conceptual schema using a high-level model (like ER model).
It describes entities, relationships, and constraints, without worrying about storage or DBMS details. Easy
to understand and useful for discussion with non-technical users.

c) Logical Design (Data Model Mapping) - Convert the conceptual schema into a DBMS-specific model
(usually relational/SQL).Result: logical schema (tables, keys, constraints).

d)Physical Design - Decide how data is stored: files, indexes, access paths, performance tuning.
In parallel, application programs/transactions are implemented

29
 Application Program phases

i. Functional Analysis: Identifies what operations users perform on the database—queries, inserts,
updates, and deletes—and the order in which they occur.
ii. Application Program Design: - Focuses on designing programs, forms, menus, and interfaces that
allow users to interact with the database easily and safely.
iii. Transaction Implementation: Implements each operation as a database transaction, ensuring
atomicity, consistency, isolation, and durability (ACID) during execution.

Key idea: Conceptual design focuses on what data and rules, logical design on how data is represented, and
physical design on how data is stored efficiently.
30
3.2 A Sample Database Application

▪ The company is divided into departments, each with a unique name and number, a manager, start date,
and multiple locations.
▪ Each department controls projects, and every project has a unique name, number, and one location.
▪ Employees have personal details stored, work in one department, and may work on multiple projects
with recorded weekly hours.
▪ Each employee has a direct supervisor (another employee).
▪ Dependents of employees are stored for insurance, with personal and relationship details.

3.3 Entity Types, Entity Sets, Attributes, and Keys

Entity: A real-world object with independent existence; can be physical (person, car, employee) or conceptual
(company, job, course).
Attributes: Properties that describe an entity (e.g., name, age, address, salary); each entity has a value for every
attribute.
Attributes Types
a) Composite versus Simple (Atomic) Attributes
b) Single-Valued versus Multivalued Attributes
c) Stored versus Derived Attributes
d) NULL Values
e) Complex Attributes
a) Composite versus Simple (Atomic) Attributes :
Composite Attributes: Can be divided into smaller meaningful parts.
Example: Address → Street_address, City, State, Zip

Simple (Atomic) Attributes: Cannot be subdivided further.

Example: Age, Salary, Sex
b) Single-Valued versus Multivalued Attributes
Single-Valued Attributes: Have one value per entity.
Example: Age of a person

Multivalued Attributes: Can have multiple values for the same entity.
Example: College_degrees of a person
31
 Constraints: Multivalued attributes may have lower and upper bounds Example: Degrees may be limited to
1–2 values

Note - During Normalization, Composite & Multivalued attributes are handled.

▪ Multivalued attribute – values are moved to another table
▪ Composite attribute – Values are split into multiple columns in the same table

c) Stored versus Derived Attributes

Stored Attributes: Values are physically stored in the database.
Example: Birth_date of a person

Derived Attributes: Values are computed from other attributes or entities.

Example: Age derived from Birth_date and current date

d) NULL Values - used when an attribute value is

i. Not applicable
ii. Unknown

Not applicable - College_degrees for a person with no degree

Unknown Values: Used when the value exists but is not known. It may be missing or Uncertain.
Example: Home_phone not recorded for a person

e) Complex Attributes - is a combination of composite + multivalued attribute. It can be divided into sub-
attributes and can have multiple values.
Example :
Addresses - (12 MG Road, Mysore, KA), (45 Brigade Rd, Bangalore, KA) |

32
33
Entity Types, Entity Sets, Keys, and Value Sets
▪ Entity is a real-world object with independent existence.
▪ Entity type defines a collection (or set) of entities that have the same attributes
▪ An entity set defines a collection of similar entities.

Example – Employee, Department, Project

Key Attributes of an Entity Type

▪ Key attribute is an attribute whose values are unique for each entity
▪ Used to identify an entity uniquely
▪ Key attribute generally has only one attribute, at times it has more number of attributes
Examples: PERSON → Aadhar Number
▪ Composite Keys - combination of attributes
Example: Registration = (State, Number )Neither attribute alone is unique
▪ Composite keys must be minimal
▪ No extra attributes allowed

Value Sets (Domains) of Attributes.

▪ Each simple attribute has a value set (domain)
▪ A value set defines the allowed values for an attribute
▪ Ensures valid and meaningful data entry
Example:
▪ Age (EMPLOYEE) → integers from 16 to 70

34
 ▪ Name → alphabetic strings with spaces
▪ Gender → {Male, Female, Other}

3.4 Relationship Types, Relationship Sets, Roles, and Structural Constraints

Relationship Types –
▪ A relationship type (R) defines how two or more entity types are related
▪ It represents a set of associations among entities
▪ Like entity type & entity set:
➢ Relationship type → definition/structure
➢ Relationship set → actual relationship instances
Example :
➢ Relation WORKS_FOR exists between entities EMPLOYEE & DEPARTMENT
➢ Interpretation - WORKS_FOR associates one EMPLOYEE entity and one DEPARTMENT
entity.

Degree of a Relationship Type

▪ The degree of a relationship type = number of participating entity types
▪ Based on degree, relationships are classified as:
➢ Binary → degree 2
➢ Ternary → degree 3
➢ n-ary → degree n
Example:
▪ WORKS_FOR (EMPLOYEE–DEPARTMENT)→ Degree = 2 → Binary relationship

35
Role Names and Recursive Relationships
▪ When an entity type participates in a relationship, it plays a specific role.
▪ A role name explains how that entity participates in the relationship.
Example
In the WORKS_FOR relationship:
EMPLOYEE → role = worker
DEPARTMENT → role = employer
Here, role names are optional because the entities are different

Role names are essential when the same entity type participates more than once in a relationship. Such
relationships are called recursive (self-referencing) relationships.
Example: Relationship: SUPERVISION
Entity type: EMPLOYEE (appears twice)
Roles played:
Supervisor (Boss)
Supervisee (Subordinate)

36
Constraints on Binary Relationship Types :
a) Cardinality Ratios for Binary Relationships.
b) Participation Constraints and Existence Dependencies

Why need Cardinality Ratios ??

▪ They specify how many entities can participate in a relationship (1:1, 1:N, M:N).
▪ They help design correct tables and foreign keys during database implementation.
▪ They enforce business rules (e.g., one manager manages many employees, not many managers per
employee).

Cardinality ratio of a binary relationship tells us the maximum number of relationship instances an entity can
participate in.
Possible Cardinality Ratios (Binary) :
1 : 1 → one-to-one
1 : N → one-to-many
N : 1 → many-to-one
M : N → many-to-many
Example - WORKS_FOR binary relationship type between DEPARTMENT:EMPLOYEE is of cardinality ratio
1:N, meaning that each department can be related to (that is, employs) any number of employees (N),9 but an
employee can be related to (work for) at most one department (1).
1 : 1 → one-to-one
One entity is associated with at most one entity of the other type, and vice versa.
Example : MANAGES (1 : 1)
Relationship: MANAGES
Entities: EMPLOYEE — DEPARTMENT
Cardinality: 1 : 1

37
n : 1 → many-to-one is a relationship in which one entity instance can be related to many instances of another
entity, but each instance on the “many” side is related to only one instance on the “one” side.

Example : WORKS_FOR (N : 1)
Relationship: WORKS_FOR

1 : n → one-to-many An entity in A is associated with many entities in B and an entity in B is associated with at
most one entity in A.

38
m : n → many-to-many is a relationship in which one entity can be associated with many entities of another
type, and vice versa.

Example : WORKS_ON (M : N)
Relationship: WORKS_ON
Entities: EMPLOYEE — PROJECT
Cardinality: M : N

39
Participation Constraints and Existence Dependencies

Participation Constraints - defines the minimum number of relationship instances an entity must take part in.
Types of Participation :
a) Total Participation (Existence Dependency)
b) Partial Participation

Total Participation (Existence Dependency)

▪ Every entity must participate in the relationship
▪ Entity cannot exist independently

Example:
If every EMPLOYEE must work for a DEPARTMENT, then
EMPLOYEE has total participation in WORKS_FOR.

40
 Partial Participation
▪ Only some entities participate in the relationship
▪ Entity can exist without the relationship

Example:
Not every employee manages a department, so
EMPLOYEE has partial participation in MANAGES..

Relationship Attributes
Attributes of Relationship Types :
• Just like entities, relationship types can also have attributes.
• These attributes describe details of the relationship, not the entity itself.

Examples
WORKS_ON (EMPLOYEE–PROJECT)
• Attribute: Hours
• Meaning: Number of hours an employee works on a project per week

41
MANAGES (EMPLOYEE–DEPARTMENT)
• Attribute: Start_date
• Meaning: Date when an employee started managing a department

Relationship attributes can be stored as attribute in either of participating entities.

Cases ::
1 : 1 Relationship – In this case, Relationship attribute can be moved to either entity.
Example:
▪ MANAGES (1:1)
▪ Start_date can be stored in EMPLOYEE or DEPARTMENT

Tables BEFORE storing the relationship attribute

EMPLOYEE Table (before storing Reporting DEPARTMENT Table (before storing Reporting
Date) Date)

EmpID Name DeptID DeptID DeptName

E101 Suma D01 D01 HR

E102 Ravi D02 D02 IT

E103 Meera D03 D03 Finance

Tables AFTER storing the relationship attribute

Option 1: Store Relationship Attribute in EMPLOYEE
EMPLOYEE Table DEPARTMENT Table

EmpID Name DeptID ReportingDate DeptID DeptName

E101 Suma D01 2022-01-10 D01 HR

E102 Ravi D02 2023-03-05 D02 IT

E103 Meera D03 2023-07-15 D03 Finance

42
Option 2: Store Relationship Attribute in DEPARTMENT
EMPLOYEE Table DEPARTMENT Table

EmpID Name DeptID DeptID DeptName EmpID ReportingDate

E101 Suma D01 D01 HR E101 2022-01-10

E102 Ravi D02 D02 IT E102 2023-03-05

E103 Meera D03 D03 Finance E103 2023-07-15

1 : N Relationship – In this case, Relationship attribute can be moved only to the N-side entity
Example:
▪ WORKS_FOR (Department–Employee)
▪ Start_date → stored in EMPLOYEE
Scenario:
• EMPLOYEE works for a DEPARTMENT (1:N relationship)
• Each DEPARTMENT can have many EMPLOYEES
• Relationship attribute: ReportingDate (when employee joined the department)

Before storing relationship attribute

EMPLOYEE Table DEPARTMENT Table

EmpID Name DeptID

DeptID DeptName
E101 Suma D01
D01 HR
E102 Ravi D01
D02 IT
E103 Meera D02

E104 Arjun D02

After storing relationship attribute

EMPLOYEE Table (with ReportingDate) DEPARTMENT Table (no change needed)

EmpID Name DeptID ReportingDate

DeptID DeptName
E101 Suma D01 2022-01-10
D01 HR
E102 Ravi D01 2023-03-05
D02 IT
E103 Meera D02 2023-07-15

E104 Arjun D02 2023-08-01

43
M : N Relationship – Relationship attributes cannot be moved to any entity. They must remain with the
relationship
Example:
▪ WORKS_ON (M:N)
▪ Hours depends on employee–project combination
Scenario
• EMPLOYEE works on PROJECT
• One employee → many projects
• One project → many employees
• Relationship attribute: HoursWorked

Before storing relationship attribute

EMPLOYEE PROJECT

EmpID Name ProjID ProjName

E101 Suma P01 Payroll

E102 Ravi P02 Website

After storing relationship attribute (M:N case) in a separate Table

EMPLOYEE PROJECT WORKS_ON (Relationship Table)

EmpID Name ProjID ProjName EmpID ProjID HoursWorked

E101 Suma P01 Payroll E101 P01 20

E102 Ravi P02 Website E101 P02 10

E102 P01 15

3.5 Weak & Strong Entity Types

Strong Entity - Has its own key attribute & can be identified independently.
Example: EMPLOYEE (EmpID), DEPARTMENT (DeptID)

Weak Entity - does NOT have its own key & Cannot be identified without another entity
It is identified using both :
a. Owner (identifying) entity
b. Partial key of the weak entity
44
Example : DEPENDENT–EMPLOYEE
Entities :
EMPLOYEE (strong entity, key: EmpID)
DEPENDENT (weak entity)
Attributes of DEPENDENT:
Name (partial key), Birth_date, Sex, Relationship

Note - Two dependents may have the same details, but they are distinguished by which employee they belong to.
Example
EMPLOYEE (Strong Entity) DEPENDENT (Weak Entity)

EmpID Name Dept Name Birth_date Sex Relationship EmpID (FK)

E101 Suma HR Anu 2010-05-12 F Daughter E101

E102 Ravi IT Arun 2008-09-20 M Son E101

Anu 2012-11-30 F Daughter E102

Meera 2012-11-30 F Daughter E102

▪ EMPLOYEE is strong, identified by EmpID.

▪ DEPENDENT is weak, cannot exist without an EMPLOYEE.
▪ Partial key: Name + EmpID distinguishes dependents, since “Anu” appears under both Suma and
Ravi.
▪ Relationship: Employee Has Dependent.

ER Diagram Notations

45
46
Alternative ER notation for specifying structural constraints on relationships is “(min, max) cardinality
notation”

▪ This replaces traditional 1:1, 1:N, M:N cardinality ratios and single/double-line participation notation.
▪ How it works: For a relationship R between entity type E and R, we associate a pair of integers (min,
max) with the participation of E in R.

o min: minimum number of times an entity must participate in the relationship.

o max: maximum number of times an entity can participate.
o min=0 → partial participation, min>0 → total participation

Examples

================= ========================= =================== =========

47