CS3492

Database Management
Systems
Jeppiaar Engineering College R-2021

Unit-1
Relational Databases
Purpose of Database System – Views of data – Data Models – Database System
Architecture – Introduction to relational databases – Relational Model – Keys – Relational
Algebra – SQL
fundamentals – Advanced SQL features – Embedded SQL– Dynamic SQL
 CS3492 - Database Management Systems
1
Jeppiaar Engineering College R-2021

1.Introduction to Database Systems

1.1 What is a Database?
A database is a structured collection of data that can be easily accessed, managed, and
updated. It provides a systematic way of storing and retrieving large amounts of
information.

Example:
A university database may store information about students, courses, faculty, and grades
in an organized manner.

1.2 What is a Database Management System (DBMS)?

A DBMS is software that interacts with users, applications, and the database itself to
define, manipulate, retrieve, and manage data efficiently.

Popular DBMS Software

Relational DBMS: MySQL, PostgreSQL, Oracle, SQL Server
NoSQL DBMS: MongoDB, Cassandra

1.3 Purpose of Database Systems

Databases are used to overcome the limitations of traditional file-processing systems
and provide a structured approach to handling data.

1.3.1 Problems with Traditional File-Based Systems

Before DBMS, data was stored in files, leading to various problems:

1. Data Redundancy and Inconsistency

Same data is stored in multiple files, leading to duplication and inconsistency.

Example: A student's contact details may be stored in both the admissions file and the
library file, but updating one does not automatically update the other.

2. Data Isolation

Data is scattered across multiple files, making access and retrieval complex.
Example: Retrieving all students enrolled in a particular course requires searching
multiple independent files.

3. Difficulty in Accessing Data

Users must write complex programs to retrieve even simple data.

Example: Fetching employee details based on department requires writing custom
search logic for file parsing.

4. Integrity Problems

Constraints (e.g., roll number must be unique) must be handled at the application level,
increasing complexity.

5. Security Issues
File-based systems do not provide user access control mechanisms, allowing
unauthorized users to access sensitive data.
 CS3492 - Database Management Systems
2
Jeppiaar Engineering College R-2021

1.3.2 How Database Systems Solve These Issues

A DBMS eliminates these problems by providing:

1. Data Abstraction

Users interact with data through structured queries without worrying about underlying
storage.

2. Efficient Data Access

Indexing and optimized query execution ensure fast data retrieval.

3. Minimized Data Redundancy

Data is stored in normalized form, reducing duplication.

4. Enhanced Security

Role-based access control ensures that only authorized users can access or modify
data.

5. Data Integrity and Consistency

Constraints like primary keys and foreign keys ensure data integrity.

6. Concurrent Access

Multiple users can access data simultaneously without inconsistencies.

7. Backup and Recovery

Automated backup mechanisms prevent data loss.

1.4 File System vs. Database System

Feature File-Based System Database System

Data Redundancy High Low

Data Consistency Low High

Security Weak Strong

Data Access Complex Easy (SQL queries)

Concurrent Users Not Supported Supported

Example Scenario
File System Approach:
A retail store maintains separate files for customers, orders, and payments. Retrieving
customer order history requires merging multiple files manually.
Database System Approach:
A relational database stores data in tables, allowing retrieval of order history with a
single SQL query.

1.5 Characteristics of Databases

A database system exhibits the following key characteristics:
 CS3492 - Database Management Systems
3
Jeppiaar Engineering College R-2021

1.5.1 Self-Describing Nature

Metadata (data about data) is stored within the database itself.

1.5.2 Data Independence

Changes in database structure do not require application modifications.

1.5.3 Multi-User Access

Multiple users can access and modify data concurrently.

1.5.4 Data Integrity and Security

Constraints ensure correctness, and access controls prevent unauthorized modifications.

1.5.5 Query Language Support

SQL provides a simple and powerful way to interact with the database.

1.5.6 Backup and Recovery

Mechanisms exist to restore data in case of failure.

1.6 Advantages of Using a DBMS

A DBMS provides numerous benefits over traditional file systems:

1. Better Data Management

Organized storage and efficient retrieval of large datasets.

2. Reduced Data Redundancy

Data is stored in a structured way, avoiding unnecessary duplication.

3. Improved Data Security

Access control mechanisms ensure only authorized users can view or modify data.

4. Concurrent Access Support

Multiple users can access and update data without conflicts.

5. Data Integrity and Accuracy

Constraints enforce correct and consistent data storage.

6. Automated Backup and Recovery

Data loss is minimized through periodic backups.

7. Scalability

Can handle large amounts of data and increasing users efficiently.

Example:

A banking system uses a DBMS to maintain customer accounts, transactions, and loan
details, ensuring data consistency and security while supporting thousands of concurrent
users.

1.7 Summary
A database is a structured collection of data that allows efficient storage, retrieval, and
management.
 CS3492 - Database Management Systems
4
Jeppiaar Engineering College R-2021

A DBMS is software that helps manage databases, providing security, consistency, and
multi-user support.
Traditional file-based systems suffer from data redundancy, inconsistency, security
issues, and difficulty in data retrieval.
A DBMS solves these problems by offering data abstraction, efficient access, security,
concurrency control, and integrity constraints.
The characteristics of a DBMS include self-describing nature, data independence,
multi-user access, data integrity, security, and backup & recovery.
The advantages of a DBMS over file systems include reduced redundancy, improved
security, concurrent access, integrity, scalability, and efficient data management.
 CS3492 - Database Management Systems
5
Jeppiaar Engineering College R-2021

Review Questions
Part-A

Sl.
Question KL PO(s) No.
1 What is a database? How is it different from a file-based purpose.
system?
3 List two limitations of a file-based system and explain
2 Define a Database Management System (DBMS) and its briefly.
KL1 PO1 KL1 PO1 KL2 PO2

4 Explain the concept of data redundancy with an example. KL2 PO1, PO2

Part-B

Sl.
Question KL PO(s) No.
1 Explain the limitations of a file-based system in 1.2
detail. How does a DBMS overcome these limitations?
Provide suitable examples. 2.Views of Data
2 Discuss the purpose of database systems. How does KL3 PO1,
a DBMS improve data management, security, and PO2,
efficiency? PO3

3 Compare and contrast the file system and DBMS in KL3 PO1, PO2
terms of data redundancy, consistency, access
control, and KL4 PO1,
concurrent access. PO2,
PO5

A database system is a collection of interrelated data and a set of programs to access

that data. In order to manage a large volume of data efficiently, different views of data
are maintained in a Database Management System (DBMS). These views help different
users interact with the database based on their specific needs and access levels.

2.1 Different Perspectives of Data in a DBMS

A database is used by different types of users who interact with it from various
perspectives. These perspectives define how data is viewed, accessed, and managed.
The key perspectives include:

2.1.1 Database Users' Perspective

There are several types of users who interact with a database system, each with a
unique perspective on data:
 CS3492 - Database Management Systems
6
Jeppiaar Engineering College R-2021

End Users: They interact with the database through applications (e.g., a bank customer
using an online banking system).
Database Administrators (DBA): They manage the database, including user access,
performance tuning, and security.
Application Programmers: They develop programs that interact with the database using
languages like SQL, Python, or Java.
System Analysts: They design and plan how data is stored and accessed to meet
business requirements.

Each of these users requires different levels of abstraction and access to data, which
leads to the concept of data abstraction.

2.2 Data Abstraction Levels

Data abstraction refers to hiding the complexity of database internals from users,
allowing them to interact with data at different levels. A DBMS provides three levels of
abstraction:

2.2.1 Physical Level (Internal Level)

The lowest level of abstraction.
Describes how data is physically stored on storage devices (e.g., hard disks, SSDs).
Involves data structures, indexing, and storage allocation techniques.
Example: A database table stored as a B+ tree index or linked list in memory.

2.2.2 Logical Level (Conceptual Level)

The middle level of abstraction.
Defines what data is stored and the relationships between different data elements.
Does not concern itself with how the data is actually stored.
Example: A student database contains attributes like Student_ID, Name, Course, and Age
without specifying storage details.

2.2.3 View Level (External Level)

The highest level of abstraction.
Defines how users see the data based on their specific needs.
Multiple views can be created for different users to restrict access to certain parts of the
database.
Example: A student may only see their own grades, whereas a faculty member can
access all students' grades.

The three-schema architecture is a conceptual framework that organizes these levels

of abstraction in a database system.

2.3 Instances and Schemas

2.3.1 Schema

A schema is the logical structure of a database that defines how data is organized. It
acts as a blueprint for the database.

Database Schema: Defines the structure of the database, including tables, relationships,
constraints, and data types.
Example:
 CS3492 - Database Management Systems
7
Jeppiaar Engineering College R-2021

Student(Student_ID, Name, Age, Course)

The schema remains fixed unless the database structure is modified.

2.3.2 Instance
An instance is a snapshot of the data stored in the database at a given moment.

Example: A table with actual student records at a given time:

Student_ID Name Age Course

101 John 21 CS3492
102 Alice 22 CS3492
103 Mike 20 CS3492

Instances change frequently as data is inserted, updated, or deleted.

2.4 Data Independence

Data independence refers to the ability to modify a database schema without
affecting the application programs that use the data. There are two types of data
independence:

2.4.1 Logical Data Independence

The ability to modify the logical schema without affecting the external schema (user
views) or application programs.
Example: Adding a new attribute (e.g., Email) to the Student table should not affect
existing applications that only retrieve Student_ID and Name.

2.4.2 Physical Data Independence

The ability to change the internal storage structure without affecting the logical schema
or external schema.
Example: Changing the storage format from sequential storage to B+ tree indexing
should not affect how users interact with the database.

Data independence ensures flexibility, scalability, and ease of database maintenance.

2.5 Summary
A DBMS provides different views of data to different users based on their needs.
Data abstraction is achieved through three levels: Physical, Logical, and View levels.
Instances represent the actual data stored at a given time, whereas schemas define the
database structure.
Data independence allows changes to the database schema without affecting application
programs.
There are two types of data independence: Logical (schema-level changes) and Physical
(storage-level changes).
 CS3492 - Database Management Systems
8
Jeppiaar Engineering College R-2021

Review Questions
Part-A

Sl. Question Knowledge Level (KL) Objectives (PO)

No. Program
1 What are the three levels of data abstraction in a 6 How does a DBMS provide different views of data
DBMS? to different users?

2 Differentiate between a schema and an instance in a 7 Define data independence in the context of database
database. management systems.
3 What is logical data independence? Why is it
important?
Part-B
4 Explain physical data independence with an KL1 PO1 KL2 PO1 KL2 PO1, PO2 KL2 PO1, PO2 KL2 PO1
example.
5 What is the purpose of the external level in data KL2 PO1, PO5 KL1 PO1
abstraction?

Sl. No. Program Objectives (PO)

Question Knowledge Level (KL)
1 Explain the three levels of data abstraction in a DBMS 1.3
with suitable examples. How do these levels contribute to
efficient database 3.Data Models
management?
2 Discuss the concept of data independence. 3.1 Introduction to Data Models
Differentiate between logical and physical KL3 PO1, PO2
data independence with real-world examples.
3 What are instances and schemas in a DBMS? How do
they affect database design and
management? Provide examples. KL4 PO1, PO2, PO5

KL3 PO1, PO2

A data model is a conceptual framework that defines how data is structured, stored,
and manipulated in a database. It provides a logical structure for organizing data and
establishes relationships between different data elements. Data models help in designing
databases and ensuring consistency in data storage and retrieval.
 CS3492 - Database Management Systems
9
Jeppiaar Engineering College R-2021

3.2 Types of Data Models

There are several types of data models, each with distinct characteristics and
applications. The four primary data models are:

3.2.1 Hierarchical Data Model

The Hierarchical Data Model organizes data in a tree-like structure where each record
has a single parent but can have multiple children. The relationships are one-to-many
(1:M), meaning that a parent can have multiple child records, but each child has only one
parent.

Characteristics of the Hierarchical Model

Data is organized in a tree-like structure.
Parent-child relationships enforce a strict hierarchy.
Data retrieval follows a predefined path from parent to child.
Efficient for queries that follow hierarchical relationships.

Example

Consider an employee management system where each department has multiple

employees.

Company
├── HR Department
│ ├── Employee 1
│ ├── Employee 2
├── IT Department
│ ├── Employee 3
│ ├── Employee 4

In this example:

The Company is the root.

The Departments are parents.
The Employees are children.

Limitations
Rigid structure: Difficult to modify relationships.
Data redundancy: Requires duplication of data.
Complex queries: Retrieving data outside the hierarchy is challenging.

3.2.2 Network Data Model

The Network Data Model extends the hierarchical model by allowing records to have
multiple parent and child relationships, forming a graph-like structure.

Characteristics of the Network Model

Uses pointers to define relationships.
Allows many-to-many (M:N) relationships.
More flexible than the hierarchical model.
Data is accessed using navigational methods.
 CS3492 - Database Management Systems
10
Jeppiaar Engineering College R-2021

Example

Consider a university database where a student can enroll in multiple courses, and
each course can have multiple students.

(Student) --- Enrolled_In --- (Course)

| |
(Professor) ---- Teaches ---- (Course)

Here,

A student can enroll in multiple courses.

A course can have multiple students.
A professor can teach multiple courses.

Limitations
Complex relationships make querying difficult.
Requires manual navigation of records using pointers.
Harder to implement compared to the relational model.

3.2.3 Relational Data Model

The Relational Data Model represents data in the form of tables (relations), where
each row is a record (tuple), and each column is an attribute (field). It is the most
widely used model in modern databases.

Characteristics of the Relational Model

Uses tables to store data.
Follows mathematical principles like set theory and relational algebra.
Provides data independence and flexibility.
Uses keys (Primary Key, Foreign Key) to establish relationships.

Example

Consider a student database with two tables:

Students Table

Student_ID Name Age

101 Alice 20
102 Bob 21

Courses Table

Course_ID Course_Name Student_ID

CSE101 Data Structures 101
CSE102 DBMS 102
 CS3492 - Database Management Systems
11
Jeppiaar Engineering College R-2021

Here, the Student_ID in the Courses Table is a Foreign Key that references
Student_ID in the Students Table.

Advantages
Easier to query using SQL.
Data consistency through keys and constraints.
Scalability and data independence.

Limitations
Performance issues for very large datasets.
Normalization overhead can impact speed.

3.2.4 Object-Oriented Data Model

The Object-Oriented Data Model integrates database concepts with object-
oriented programming. It stores data as objects, similar to programming languages
like Java and Python.

Characteristics of the Object-Oriented Model

Supports encapsulation, inheritance, and polymorphism.
Data is represented as objects instead of tables.
Objects can contain attributes (fields) and methods (functions).

Advantages
Better for complex data types (e.g., multimedia, CAD models).
Encapsulation ensures data security.

Limitations
Less efficient for simple applications.
Not as widely used as relational databases.

3.3 Concept of Entities, Attributes, and Relationships

3.3.1 Entities

An entity represents a real-world object that can have attributes. Examples: Students,
Employees, Books.

3.3.2 Attributes
Attributes define the properties of an entity. Example:

A Student entity may have attributes Name, Age, Roll Number.

An Employee entity may have ID, Name, Salary.

3.3.3 Relationships
A relationship defines associations between entities.
Example: A Student (entity) enrolls in a Course (entity).

Types of Relationships
One-to-One (1:1) – A student has one ID card.
One-to-Many (1:M) – A professor teaches many courses.
Many-to-Many (M:N) – Students enroll in multiple courses.
 CS3492 - Database Management Systems
12
Jeppiaar Engineering College R-2021 3.4 Examples of Different Data Models

Data Model Example Use Case

Hierarchical Organizational structure, File systems
Network Banking systems, Telecom networks
Relational Business applications, E-commerce
Object-Oriented Multimedia databases, CAD applications

3.5 Summary
Data models define how data is structured and manipulated in a database.
The Hierarchical Model organizes data in a tree structure with a strict parent-child
relationship.
The Network Model allows many-to-many relationships using pointers.
The Relational Model represents data in tables with keys and constraints.
The Object-Oriented Model integrates object-oriented programming concepts into
databases.
Entities, attributes, and relationships define how data elements are related.

Each model has its own strengths and weaknesses, and the choice of a model depends
on the requirements of the application.

CS3492 - Database Management Systems

13
 Jeppiaar Engineering College R-2021

Review Questions
Part-A

Sl. Question Knowledge Level (KL) Objectives (PO)

No. Program
1 What is a data model in a database system? 7 What is an object-oriented data model? Where is it
used?
2 List the different types of data models used in DBMS.
8 Define entities, attributes, and
3 What are the key components of an Entity Relationship
relationships in a database system.
(ER) model?
4 Define the relational data model. What are its main
characteristics? Part-B
KL1 PO1 KL1 PO1 KL2 PO1 KL2 PO1 KL2 PO1 KL2 PO1
5 How does the hierarchical model structure data?
6 Explain the concept of a network data model. How
does it differ from the
hierarchical model? KL2 PO1, PO5 KL1 PO1

Sl. No. Program Objectives (PO)

Question Knowledge Level (KL)

1 Explain the concept of data models in DBMS. Discuss examples. Discuss the key concepts such as entities,
the hierarchical, network, relational, and object- attributes, and relationships.
oriented data models with suitable diagrams and
examples.
1.4
2 Compare and contrast the hierarchical and network
data models. Provide real-world
examples where these models are used. 4.Database System Architecture
KL2, KL3 PO1
3 Discuss the advantages and disadvantages of the
relational data model. Why is it the most widely used
model?

4 Explain the Entity-Relationship (ER) model with KL3, KL4 PO1, PO2 KL3, KL4 PO1, PO2 KL2, KL3 PO1

CS3492 - Database Management Systems

14
Jeppiaar Engineering College R-2021

4.1 Introduction to Database System Architecture

A database system architecture defines the design and structure of a database
system, specifying how data is stored, processed, and managed. It determines how users
interact with the database, how queries are processed, and how data is maintained for
consistency and integrity.

The architecture of a DBMS can be understood through two key aspects:

1. The Three-Schema Architecture – Defines data abstraction levels.

2. DBMS Components – Handles query processing, storage, and transactions.

4.2 Three-Schema Architecture

The Three-Schema Architecture is a framework that defines how data is represented
in a DBMS at different levels of abstraction. It was introduced to provide data
independence, allowing changes at one level without affecting other levels.

4.2.1 Levels of the Three-Schema Architecture

The three levels in this architecture are:

1. Internal Schema (Physical Level) – Defines how data is physically stored in memory.
2. Conceptual Schema (Logical Level) – Defines the overall logical structure of the
database.
3. External Schema (View Level) – Defines how users interact with data through different
views.

Example

Consider a university database with a Student table.

Internal Level: Data is stored as disk blocks, indexes, and pointers.

Conceptual Level: The table is represented with attributes (Student_ID, Name, Age,
Course_ID).
External Level:
The admin views all details of students.
The student only sees their own records.

Advantages of Three-Schema Architecture

Data Abstraction – Users need not worry about how data is stored.
Data Independence –
Logical Data Independence: Changes in the conceptual schema do not affect
external schemas.
Physical Data Independence: Changes in physical storage do not affect the
conceptual schema.

4.3 DBMS Components

A DBMS consists of multiple components that manage data storage, retrieval, and
processing. The main components are:

CS3492 - Database Management Systems

15
Jeppiaar Engineering College R-2021

4.3.1 Storage Manager

 The Storage Manager is responsible for managing the way data is stored in the
database. It includes:

Buffer Manager – Manages main memory and disk storage.

File Manager – Handles physical storage of database files.
Index Manager – Optimizes data access using indexing techniques.
Authorization Manager – Enforces security policies for accessing data.

Example

When a user retrieves student records, the storage manager determines whether the
data is stored in memory or needs to be fetched from disk.

4.3.2 Query Processor

The Query Processor interprets and executes SQL queries. It has several components:

Query Parser – Checks syntax and translates the query into an internal representation.
Query Optimizer – Determines the most efficient way to execute the query.
Execution Engine – Executes the query and retrieves results.

Example

For the SQL query:

SQL

SELECT Name FROM Students WHERE Age > 20;

1. The Query Parser checks if the SQL syntax is correct.

2. The Query Optimizer decides whether to use an index or perform a full table scan.
3. The Execution Engine retrieves and displays the results.

4.3.3 Transaction Manager

The Transaction Manager ensures database consistency and handles concurrent
transactions. It includes:

Concurrency Control Manager – Ensures multiple transactions do not interfere with each
other.
Recovery Manager – Restores the database to a previous state in case of failure.

Example

Consider two users transferring money:

User A transfers ₹500 to User B.

User B's balance must update only if User A’s debit transaction succeeds.
If there is a system crash before both updates complete, the Transaction Manager
ensures no partial changes occur.

CS3492 - Database Management Systems

16
Jeppiaar Engineering College R-2021

4.4 DBMS Architectures

A DBMS can be deployed in different architectures based on system requirements and
user needs.

4.4.1 Centralized DBMS

A Centralized DBMS stores all data in a single location, typically on a single server.
Users access the database via terminals or remote connections.

Characteristics
Single database server for all users.
Easy to maintain and manage.
High risk of failure if the server crashes.

Example

A university administration system with a central database managing student records.

Advantages
Simpler security management as all data is in one place.
Easier backup and recovery.

Disadvantages
Single point of failure – If the server fails, all users lose access.
Performance bottleneck as multiple users increase the load.

4.4.2 Client-Server DBMS

A Client-Server DBMS has a server that manages the database and clients that send
requests. The server processes the requests and returns results.

Characteristics
The server handles database operations.
Clients send queries and receive responses.
Better scalability than a centralized system.

Example

A banking system where tellers and ATMs act as clients querying a central database server.

Advantages
Better performance – Multiple clients can interact simultaneously.
More secure than a centralized DBMS.

Disadvantages
Network dependency – If the network fails, client access is affected.
Server overload can occur with too many client requests.

4.4.3 Distributed DBMS

A Distributed DBMS (DDBMS) stores data across multiple geographically dispersed

locations. Each site has its own local database but works as a single system.

CS3492 - Database Management Systems

17
Jeppiaar Engineering College R-2021

Characteristics
 Data is distributed across multiple locations.
Sites communicate over a network.
Can be homogeneous (same DBMS at all sites) or heterogeneous (different DBMSs).

Example

A multinational company with offices in New York, London, and Tokyo having
separate local databases but functioning as a single DBMS.

Advantages
Improved availability – Even if one site fails, others continue working.
Faster data access – Users access the nearest database instead of a central server.

Disadvantages
Complex synchronization – Keeping data consistent across locations is difficult.
Higher costs – Requires advanced infrastructure.

4.5 Summary
Database System Architecture defines how a DBMS is structured.
Three-Schema Architecture provides data abstraction through internal, conceptual,
and external levels.
DBMS Components include:
Storage Manager (Handles data storage).
Query Processor (Executes SQL queries).
Transaction Manager (Ensures consistency and recovery).
DBMS Architectures include:
Centralized DBMS (Single database, easy to manage but prone to failure).
Client-Server DBMS (Clients send requests to a database server).
Distributed DBMS (Multiple databases work as a single system).

Understanding DBMS architecture helps in designing efficient, scalable, and reliable

database solutions.

CS3492 - Database Management Systems

18
Jeppiaar Engineering College R-2021
 Review Questions
Part-A

Sl. Question Knowledge Level (KL) Objectives (PO)

No. Program
1 What is database system architecture? KL1 PO1
2 Define the three-schema architecture in DBMS. 6 Define physical data independence. How does it benefit
database design?
3 What is the purpose of the internal schema in the three-
schema
architecture?
Part-B
4 How does the conceptual schema differ from the KL1 PO1 KL2 PO1
external schema?
5 What is logical data independence? Why is it important?
KL2 PO1 KL2 PO1 KL2 PO1

Sl. No. Program Objectives (PO)

Question Knowledge Level (KL)

1 Explain the three-schema architecture in detail with an 3 Explain the different components of a DBMS. How do
example. How does it these components interact with each other?
contribute to data abstraction?
2 Discuss the concepts of logical and physical data
1.5
independence. Why are they important in database KL2, KL3 PO1 KL2, KL4 PO1, PO2 KL2, KL3 PO1, PO5
system architecture?

5.Introduction to Relational Databases

5.1 Overview of Relational Databases
A Relational Database is a type of database that organizes data into tables (relations)
consisting of rows (tuples) and columns (attributes). It is based on the Relational
Model, which was introduced by E.F. Codd in 1970.

Relational databases use Structured Query Language (SQL) for data manipulation
and retrieval. They are widely used due to their simplicity, flexibility, scalability, and
data integrity features.

CS3492 - Database Management Systems

19
Jeppiaar Engineering College R-2021

5.2 Concept of Relations, Attributes, and Tuples

In a relational database, data is stored in relations (tables), which are structured in terms of:
 Relation (Table): A collection of data organized in rows and columns.
Attributes (Columns): The properties or fields of a table that define the type of data
stored.
Tuples (Rows): Individual records in a table representing a unique entity.

Example: Student Database

Student_ID Name Age Department

101 Alex 20 CSE
102 Brian 21 ECE
103 Clara 19 MECH

Relation: The table Student represents a relation.

Attributes: Student_ID , Name , Age , and Department are attributes.
Tuples: Each row (e.g., (101, Alex, 20, CSE)) is a tuple representing a student.

5.3 Characteristics of a Relational Database

Relational databases exhibit the following key characteristics:

1. Data is stored in tables – Data is organized into structured tables.

2. Each row is unique – A row (tuple) represents a single record.
3. Each column has a unique name – Attribute names must be distinct.
4. Atomicity of values – Each cell in a table holds a single value (no multiple values in a
single cell).
5. Uniqueness through keys – Each table has a primary key that uniquely
identifies records. 6. Relationships between tables – Tables can be linked using
foreign keys.
7. Data integrity and constraints – Constraints like NOT NULL, UNIQUE, PRIMARY KEY,
FOREIGN KEY, CHECK ensure data consistency.
8. Support for SQL – Relational databases use SQL for querying, inserting, updating, and
deleting records.

5.4 Role of Keys in Relational Databases

Keys are attributes or sets of attributes used to uniquely identify tuples in a relation.

Types of Keys:
1. Primary Key (PK): A unique identifier for each tuple in a table.
Example: In a Student table, Student_ID can be the primary key.
2. Candidate Key: A set of attributes that can act as a primary key.
Example: ( Student_ID , Name ) could both be candidate keys, but only one is chosen as
the primary key.
3. Super Key: A set of one or more attributes that uniquely identify a tuple.
Example: ( Student_ID , Name , Age ) is a super key if it uniquely identifies records.
4. Foreign Key (FK): An attribute in one table that refers to the primary key in another table.

CS3492 - Database Management Systems

20
Jeppiaar Engineering College R-2021

Example: In an Enrollment table, Student_ID is a foreign key referencing the Student

table.
Example: Keys in a Relational Database
Student Table:

Student_ID (PK) Name Age

101 Alex 20
102 Brian 21
103 Clara 19

Enrollment Table:

Enrollment_ID (PK) Student_ID (FK) Course_Name

501 101 DBMS
502 102 AI

Student_ID is a primary key in the Student table.

Student_ID in the Enrollment table is a foreign key linking to the Student table.

5.5 Summary
A relational database stores data in tables called relations consisting of attributes
(columns) and tuples (rows).
The relational model ensures data integrity,flexibility, and ease of access using SQL.
Keys play a crucial role in maintaining relationships and ensuring uniqueness in data
storage.
Primary keys uniquely identify tuples, whereas foreign keys establish relationships
between tables.

CS3492 - Database Management Systems

21
Jeppiaar Engineering College R-2021

Review Questions
 Part-A

Sl. Question Knowledge Level (KL) Objectives (PO)

No. Program
1 What is a relational database? KL1 PO1
2 Define the terms relation, attribute, and tuple in a 7 Why is data integrity important in relational
relational database. databases?
3 List any four characteristics of a relational database. 8 Explain the role of SQL in relational databases.
4 What is the purpose of a primary key in a relational
database?
Part-B
5 Differentiate between primary key and foreign key KL1 PO1 KL2 PO1 KL2 PO1, PO2 KL2 PO1, PO2 KL2 PO1,
with an example.
6 What is a candidate key? How is it different from a PO2 KL2 PO1 KL2 PO1, PO5
super key?

Sl. No. Program Objectives (PO)

Question Knowledge Level (KL)

1 Explain the relational model in detail. Discuss its key between tables.
components with suitable examples.
2 Discuss the characteristics of relational databases.
1.6
How do these characteristics improve data organization?

3 Define keys in relational databases. Explain different 6.Relational Model and Keys
types of keys with suitable examples. KL2 PO1, PO2 KL2 PO1, PO2
4 How do primary keys and foreign keys ensure data
integrity in relational databases? Illustrate with examples.

5 Consider a University Database with tables Student KL2 PO1, PO2 KL3 PO1, PO2
(Student_ID, Name, Age, Department) and
Enrollment (Enrollment_ID, Student_ID,
Course_Name). Identify the primary keys and foreign
keys. Explain how these keys establish relationships KL3 PO1, PO2, PO5

CS3492 - Database Management Systems

22
Jeppiaar Engineering College R-2021

6.1 Introduction to the Relational Model

The relational model is the foundation of modern database management systems. It
organizes data into relations (tables) consisting of rows (tuples) and columns
(attributes). This model is based on mathematical set theory and predicate logic,
ensuring data consistency, integrity, and easy retrieval.
 A relational database consists of:

Relations (Tables) – A set of rows and columns.

Attributes (Columns) – Properties or characteristics of entities.
Tuples (Rows) – Individual records in a table.
Domains – The set of permissible values for an attribute.

6.2 Relational Model Concepts

6.2.1 Relations and Attributes
A relation is represented as a table, where:

Each row represents a tuple (record).

Each column represents an attribute (field).
Each attribute has a domain, which specifies the possible values it can hold.

For example, consider a Student Relation:

Student_ID Name Age Department

101 Alice 20 CSE
102 Bob 21 ECE
103 Charlie 19 CSE

Here:

Student_ID , Name , Age , and Department are attributes.

Each row represents a student record.
The domain for Age could be {18, 19, 20, 21, 22, ...}.

6.2.2 Integrity Constraints

Integrity constraints ensure the correctness and validity of data. Common constraints
include:

Domain Constraint: Ensures attribute values belong to a defined set.

Entity Integrity: The primary key cannot have NULL values.
Referential Integrity: A foreign key must reference a valid primary key in another table.

6.3 Types of Keys

Keys uniquely identify records and maintain data integrity in relational databases.

6.3.1 Primary Key

A primary key is an attribute (or a set of attributes) that uniquely identifies a tuple.

CS3492 - Database Management Systems

23
Jeppiaar Engineering College R-2021

Example: In the Student relation, Student_ID is the primary key since no two students
can have the same ID.

6.3.2 Candidate Key

A candidate key is any attribute or set of attributes that can serve as a primary key.
 Example: If we add Email to the Student relation, both Student_ID and Email could be
candidate keys.

6.3.3 Super Key

A super key is a set of attributes that uniquely identifies a tuple. It may contain
unnecessary attributes.

Example: {Student_ID, Name} , {Student_ID, Email} , and {Email, Phone} are super
keys.

6.3.4 Foreign Key

A foreign key is an attribute in one table that refers to the primary key of another table.

Example: If we have an Enrollment table:

Enrollment_ID Student_ID Course_Name

501 101 DBMS
502 102 OS

Here, Student_ID in the Enrollment table is a foreign key referring to Student_ID in

the Student table.

6.4 Referential Integrity

Referential Integrity ensures that a foreign key in one table must match an existing
primary key in another table. This prevents orphan records.

6.4.1 Example of Referential Integrity Violation

If we try to insert the following record into Enrollment :

Enrollment_ID Student_ID Course_Name

503 999 AI

Since Student_ID = 999 does not exist in the Student table, referential integrity is

violated. 6.4.2 Importance of Referential Integrity

Prevents inconsistent data in relational databases.

Ensures valid relationships between tables.
Improves data integrity and accuracy.

CS3492 - Database Management Systems

24
Jeppiaar Engineering College R-2021

6.5 Summary
The relational model represents data as tables (relations) consisting of rows (tuples)
and columns (attributes).
Integrity constraints ensure valid data, including domain constraints, entity integrity,
 and referential integrity.
Primary keys uniquely identify records, while foreign keys establish relationships between
tables.
Referential integrity prevents invalid references and ensures data consistency.

CS3492 - Database Management Systems

25
Jeppiaar Engineering College R-2021

Review Questions
Part-A
Sl. Question Knowledge Level (KL) Objectives (PO)
No. Program
1 What is the relational model? KL1 PO1
2 Define the terms relation,tuple, and attribute in the KL1 PO1
relational model.

3 What is a primary key? Give an example. KL2 PO1

4 Explain the difference between candidate key and 6 Define referential integrity with an example.
primary key.
5 What is a foreign key? How does it help in maintaining
relationships between tables? Part-B
KL2 PO1 KL2 PO1, PO2 KL2 PO1, PO2

Sl. No. Program Objectives (PO)

Question Knowledge Level (KL)

1 Explain the relational model in detail. Discuss its

components with a suitable example.
2 Describe integrity constraints in the relational
model. Explain entity integrity,
referential integrity, and domain constraints with
examples.

1.7
KL2 PO1, PO2 KL3 PO1, PO2
7.Relational Algebra – Basic Operations
7.1 Introduction to Relational Algebra
Relational algebra is a procedural query language that provides a set of operations to
manipulate and retrieve data from relational databases. It serves as the theoretical
foundation for SQL and helps in optimizing query execution.

Characteristics of Relational Algebra

Operates on relations (tables) and produces new relations as results.
Provides a foundation for query optimization in database systems.
Queries are expressed as a sequence of operations applied to relations.

7.2 Fundamental Operations in Relational Algebra

Relational algebra consists of a set of operations that can be categorized into
fundamental and additional operations. This session focuses on the fundamental
operations, which are:

CS3492 - Database Management Systems

26
Jeppiaar Engineering College R-2021

1. Selection (σ) – Filtering Rows

2. Projection (π) – Selecting Columns
3. Union (∪) – Merging Relations
4. Set Difference (−) – Finding Differences
5. Cartesian Product (×) – Combining Relations

7.2.1 Selection (σ) – Filtering Rows

The selection operation (σ) is used to extract rows from a relation that satisfy a given
condition.

Syntax:
��condition(Relation)

Example:

Consider a relation Student with the following attributes:

Student (Student_ID, Name, Age, Department)

Student_ID Name Age Department

101 Alice 20 CSE
102 Bob 22 IT
103 Charlie 21 CSE
104 David 23 ECE

To retrieve students from the CSE department:

(Student)
��Department =′ ������′

Result:

Student_ID Name Age Department

 101 Alice 20 CSE
103 Charlie 21 CSE

7.2.2 Projection (π) – Selecting Columns

The projection operation (π) is used to extract specific columns from a relation,
removing duplicates.

Syntax:
��Column1, Column2, ...(Relation)

To retrieve only the Student_ID and Name from the Student relation:

��Student_ID, Name (Student)

Student_ID Name
101 Alice

CS3492 - Database Management Systems

27
Jeppiaar Engineering College R-2021

Student_ID Name
102 Bob
103 Charlie
104 David

7.2.3 Union (∪) – Merging Relations

The union operation (∪) is used to combine two relations that have the same attributes.
Duplicate records are eliminated in the result.

Syntax:
Relation1∪Relation2

Consider two relations:

CSE_Students

Student_ID Name
101 Alice

103 Charlie

IT_Students

Student_ID Name
102 Bob

105 Eve

The union operation:

CSE_Students ∪ IT_Students
 Student_ID Name

101 Alice

103 Charlie
102 Bob

105 Eve

7.2.4 Set Difference (−) – Finding Differences

The set difference operation (−) is used to find tuples present in one relation but not in
another.

Syntax:
Relation1−Relation2

CS3492 - Database Management Systems

28
Jeppiaar Engineering College R-2021

Using the previous CSE_Students and IT_Students relations,

to find students who are in CSE but not in IT:

CSE_Students-IT_Students

Student_ID Name
101 Alice
103 Charlie

7.2.5 Cartesian Product (×) – Combining Relations

The Cartesian product (×) combines every row of one relation with every row of
another relation. The result contains all possible combinations.

Syntax:
Relation1×Relation2

Consider two relations:

Students

Student_ID Name
101 Alice
102 Bob

Courses

Course_ID Course_Name
C1 DBMS
C2 AI
 The Cartesian Product:

Students×Courses

Student_ID Name Course_ID Course_Name

101 Alice C1 DBMS
101 Alice C2 AI
102 Bob C1 DBMS
102 Bob C2 AI

7.3 Summary
Relational Algebra is a procedural query language used to retrieve and manipulate data in
a relational database.
The selection operation (σ) filters rows based on conditions.

CS3492 - Database Management Systems

29
Jeppiaar Engineering College R-2021

The projection operation (π) selects specific columns from a table.

The union operation (∪) merges two relations while removing duplicates.
The set difference operation (−) finds tuples in one relation but not in another.
The Cartesian product (×) generates all possible combinations between two relations.
 CS3492 - Database Management Systems
30
Jeppiaar Engineering College R-2021

Review Questions
Part-A

Sl. Level
Question Knowledge No. PO(s)

1 What is relational algebra? KL1 PO1 2 Define selection operation in relational

algebra. KL1 PO1

3 What is the purpose of the projection operation in applications.

relational algebra?
5 Consider a university database with relations:
4 Differentiate between selection (σ) and projection (π) in Students (Student_ID, Name, Age, Department)
relational algebra. Subjects
(Subject_ID, Subject_Name, Department)
Enrollments (Student_ID, Subject_ID, Grade) Write
Part-B relational algebra queries for: (a) Retrieving all students
KL1 PO1 KL2 PO2 enrolled in
"Database Systems" (b) Finding students who are in the
"CSE" department but have not enrolled in any subject

Sl. 1.8
Question Knowledge No.

Level
8.Relational Algebra – Advanced
PO(s)
Operations 8.1 Introduction to
1 Explain the fundamental operations in relational Advanced Operations
algebra with suitable examples.
KL2 PO1, PO2
2 Given a relation Employee (Emp_ID, Name, Age,
Department, Salary), write relational algebra KL3 PO2, PO3
expressions for: (a) Selecting employees from the "HR"
department (b) Listing only the employee names and
salaries (c) Finding employees earning more than
₹50,000
3 Illustrate the union and set difference operations KL3 PO2, PO3
with an example. When should we use each operation?
KL4 PO3, PO4
4 Consider two relations: Students (Student_ID,
Name, Age) Courses (Course_ID, Course_Name)
Compute the Cartesian Product and discuss its practical KL6 PO3, PO5
In addition to the fundamental operations of relational algebra (Selection, Projection,
Union, Set Difference, Cartesian Product), there are advanced operations that provide
more powerful

CS3492 - Database Management Systems

31
Jeppiaar Engineering College R-2021

ways to manipulate and query relational data. These include:

1. Join Operations
Theta Join (θ-Join)
Equi-Join
Natural Join
2. Division Operation
3. Aggregation Operations

These operations are frequently used in SQL queries and help in efficiently retrieving
meaningful data from relational databases.

8.2 Join Operations in Relational Algebra

A join operation combines two relations based on a common attribute or predicate
condition. It is one of the most powerful operations in relational algebra, frequently
used to merge data from multiple tables.

8.2.1 Theta Join (θ-Join)

A theta join (θ-join) combines two relations based on a specified condition involving
comparison operators ( = , > , < , >= , <= , != ).

Syntax:

R ⋈θ S

where θ is a condition that relates attributes from R and S .

Example:

Consider the relations:

Employee

Emp_ID Name Dept_ID Salary

101 Alice 1 50000
102 Bob 2 60000
103 Charlie 1 55000
104 David 3 70000

Department

Dept_ID Dept_Name Location

1 HR New York
2 IT San Francisco
3 Finance Chicago
To retrieve employees who earn more than 55000 and their department details:

CS3492 - Database Management Systems

32
Jeppiaar Engineering College R-2021

Employee ⋈Employee.Salary>55000 Department

Result:

Emp_ID Name Dept_ID Salary Dept_Name Location

102 Bob 2 60000 IT San Francisco

104 David 3 70000 Finance Chicago

8.2.2 Equi-Join
An equi-join is a special case of theta join, where the condition involves only the equality ( =
) operator.

Example:

To retrieve employees along with their department details:

Employee ⋈Employee.Dept_ID=Department.Dept_ID Department

Result:

Emp_ID Name Dept_ID Salary Dept_Name Location

101 Alice 1 50000 HR New York

102 Bob 2 60000 IT San Francisco

103 Charlie 1 55000 HR New York

104 David 3 70000 Finance Chicago

8.2.3 Natural Join

A natural join is a special type of equi-join, where matching attributes with the same
name are implicitly joined.

Example:

Using the Employee and Department tables, the natural join would be:

Employee ⋈ Department

This operation automatically joins the tables on Dept_ID , removing duplicate columns
in the output.

Result:

Emp_ID Name Dept_ID Salary Dept_Name Location

101 Alice 1 50000 HR New York

102 Bob 2 60000 IT San Francisco

103 Charlie 1 55000 HR New York
 104 David 3 70000 Finance Chicago

CS3492 - Database Management Systems

33
Jeppiaar Engineering College R-2021

8.3 Division Operation

The division operation is used in queries that involve "for all" conditions. It is useful
when we need to find tuples that are related to all values of another relation.

Example:

Consider the relations:

Student_Course (students enrolled in courses)

Student_ID Course_ID
101 C1
101 C2
102 C1
103 C1
103 C2

Course (list of all required courses)

Course_ID
C1
C2

To find students enrolled in all courses (both C1 and C2 ):

πStudent_ID(Student_Course) ÷ πCourse_ID(Course)

Result:

Student_ID
101
103

Only Students 101 and 103 are enrolled in all available courses.

8.4 Aggregation Operations

Aggregation operations allow performing calculations on columns.

Common Aggregation Functions:

COUNT() – Counts number of rows.
SUM() – Sums up values in a column.
AVG() – Finds the average value.
 MIN() – Finds the smallest value.

CS3492 - Database Management Systems

34
Jeppiaar Engineering College R-2021

MAX() – Finds the largest value.

Example:

To find the total salary paid in each department:

γDept_ID,SUM(Salary)(Employee)

Result:

Dept_ID Total_Salary
1 105000
2 60000
3 70000

8.5 Summary
Join operations combine relations based on matching conditions:
Theta Join: Uses arbitrary conditions ( > , < , = ).
Equi-Join: A special case of theta join using = .
Natural Join: Automatically joins relations on common attributes.
Division operation helps in queries involving “for all” conditions.
Aggregation operations help perform calculations on grouped data.

These advanced relational algebra operations enhance the ability to query data
effectively and form the foundation of complex SQL queries.
 CS3492 - Database Management Systems
35
Jeppiaar Engineering College R-2021

Review Questions
Part-A
Q.No Question Knowledge Level (KL) Objective (PO)
Program
1 Define theta join and provide an example. KL1 PO1
2 Differentiate between equi-join and natural join. Dept_Name, Location). Construct relational
algebra expressions for:
3 Explain the division operation in relational algebra with
a) Finding employees earning more than 50,000 along
an example.
with their department details.
4 What is the significance of referential integrity in b) Counting the number of employees in each
relational algebra joins? department.
c) Identifying employees who work in all available
5 List any three aggregation operations in relational
departments.
algebra and explain their use.
3 Explain the division operation in relational algebra and
6 How does natural join differ from theta join?
illustrate with a real-world example. How is this operation
useful in SQL queries?

Part-B
KL2 PO1, PO2 KL2 PO1, PO2 KL2 PO1 KL1 PO1 KL2 PO1, 1.9

PO2 9.SQL Fundamentals

9.1 Introduction to SQL
KL3 PO1, PO2, PO5

KL4 PO1, PO2, PO5

Q.No Question Knowledge Level (KL)

Program Objective (PO)
1 Given two relations R(A, B, C) and S(B, D, E), illustrate
the different types of join operations with appropriate
examples.
2 Consider the relations Employee(Emp_ID, Name,
Dept_ID, Salary) and Department(Dept_ID,
KL4 PO1, PO2, PO3

Structured Query Language (SQL) is a standard programming language used to manage

relational databases. It allows users to define, manipulate, and control data effectively.
SQL is

CS3492 - Database Management Systems

36
Jeppiaar Engineering College R-2021

divided into different categories based on its functionality:

 Data Definition Language (DDL) – Defines and modifies the database structure.
Data Manipulation Language (DML) – Performs operations on the data stored in the
database.
Data Control Language (DCL) – Manages access to the database.
Transaction Control Language (TCL) – Handles transactions in the database.

9.2 SQL Data Definition Language (DDL)

DDL consists of commands that define the structure of the database. These commands
are responsible for creating, modifying, and deleting database objects.

9.2.1 CREATE Statement

The CREATE statement is used to define new database objects such as tables, indexes,
and views.

Example: Creating a table for student records

SQL

CREATE TABLE Student (

StudentID INT PRIMARY KEY,
Name VARCHAR(50),
Age INT,
Department VARCHAR(50)
);

This statement creates a table named Student with four attributes: StudentID , Name ,
Age , and Department , where StudentID is the Primary Key.

9.2.2 ALTER Statement

The ALTER statement modifies an existing table structure, such as adding or deleting

columns. Example: Adding a new column Email to the Student table

SQL

ALTER TABLE Student ADD Email VARCHAR(100);

9.2.3 DROP Statement

The DROP statement permanently deletes a database object, such as a table or index.

Example: Dropping the Student table

SQL

DROP TABLE Student;

9.3 SQL Data Manipulation Language (DML)

DML commands allow users to insert, update, delete, and retrieve data from tables.

CS3492 - Database Management Systems

37
Jeppiaar Engineering College R-2021

9.3.1 SELECT Statement

The SELECT statement retrieves data from a table.

Example: Fetching all records from the Student table

SQL

SELECT * FROM Student;

9.3.2 INSERT Statement

The INSERT statement adds new records into a table.

Example: Adding a new student record

SQL

INSERT INTO Student (StudentID, Name, Age, Department)

VALUES (101, 'Alice', 20, 'Computer Science');

9.3.3 UPDATE Statement

The UPDATE statement modifies existing records in a table.

Example: Updating Alice's age

SQL

UPDATE Student
SET Age = 21
WHERE StudentID = 101;

9.3.4 DELETE Statement

The DELETE statement removes records from a table.

Example: Deleting a student record

SQL

DELETE FROM Student

WHERE StudentID = 101;

9.4 SQL Constraints and Integrity Rules

Constraints in SQL enforce rules on the data to maintain its accuracy and integrity.

9.4.1 Primary Key Constraint

A Primary Key uniquely identifies each record in a table.

Example: Defining StudentID as a primary key

CS3492 - Database Management Systems

38
Jeppiaar Engineering College R-2021

SQL

CREATE TABLE Student (

 StudentID INT PRIMARY KEY,
Name VARCHAR(50),
Age INT,
Department VARCHAR(50)
);

9.4.2 Foreign Key Constraint

A Foreign Key maintains referential integrity between two tables.

Example: Creating a Course table with a foreign key referencing Student

SQL

CREATE TABLE Course (

CourseID INT PRIMARY KEY,
CourseName VARCHAR(100),
StudentID INT,
FOREIGN KEY (StudentID) REFERENCES Student(StudentID)
);

9.4.3 Not Null Constraint

The NOT NULL constraint ensures that a column cannot contain NULL values.

Example: Making Name a mandatory field

SQL

ALTER TABLE Student

MODIFY Name VARCHAR(50) NOT NULL;

9.4.4 Unique Constraint

The UNIQUE constraint ensures that all values in a column are distinct.

Example: Ensuring that Email is unique

SQL

ALTER TABLE Student

ADD CONSTRAINT unique_email UNIQUE (Email);

9.4.5 Check Constraint

The CHECK constraint imposes a condition on column values.

Example: Ensuring that Age is greater than 18

CS3492 - Database Management Systems

39
Jeppiaar Engineering College R-2021

SQL

ALTER TABLE Student

 ADD CONSTRAINT check_age CHECK (Age > 18);

9.4.6 Default Constraint

The DEFAULT constraint sets a default value for a column.

Example: Setting the default department as 'Undeclared'

SQL

ALTER TABLE Student

ALTER COLUMN Department SET DEFAULT 'Undeclared';

9.5 Summary
SQL is a language used for managing relational databases.
DDL commands like CREATE , ALTER , and DROP define and modify database structures.
DML commands like SELECT , INSERT , UPDATE , and DELETE manipulate the data in tables.
Constraints enforce rules for data integrity and consistency, including PRIMARY KEY ,
FOREIGN KEY , NOT NULL , UNIQUE , CHECK , and DEFAULT .

CS3492 - Database Management Systems

40
Jeppiaar Engineering College R-2021

Review Questions
 Part-A

Sl. Question Knowledge Level (KL) Mapping

No. PO
1 What is SQL? List its different categories. KL1 PO1
2 Differentiate between DDL and DML with examples. Provide an example.
3 Write an SQL query to create a table named Employee 7 What are integrity constraints in SQL? Why are they
with attributes EmpID , Name , and important?
Salary , where EmpID is the primary key.

4 What is the purpose of the ALTER command in SQL?

Part-B
Give an example.
KL2 PO1, PO2 KL3 PO1, PO5
5 How does the DELETE statement differ from DROP and
TRUNCATE in SQL?

6 Explain the significance of the CHECK constraint in SQL. KL2 PO1 KL3 PO1, PO2 KL3 PO2 KL2 PO1, PO2

Sl. Question Knowledge Level (KL) Mapping

No. PO

1 Explain various Data Definition Language (DDL) 1.10

commands in SQL with suitable examples. KL4 PO1, PO5

2 Discuss SQL constraints in detail. Write SQL queries

to demonstrate the use of Primary Key, Foreign Key, KL4 PO1, PO2, PO5
Unique, and Not Null constraints.

3 Write SQL queries for the following operations: (a) KL5 PO1, PO3, PO5
Create a table Customer with appropriate
attributes and constraints, (b) Insert five records, (c)
Update a record, (d) Delete a record.
KL4 PO1, PO2, PO5
4 Compare and contrast DELETE , TRUNCATE , and DROP
commands in SQL with examples. Which command is
preferred in different scenarios?

10.Advanced SQL Features & Embedded SQL

10.1 Introduction
SQL provides powerful features beyond basic queries and table manipulations. Advanced
SQL concepts such as nested queries, views, joins, grouping, and aggregation
enhance query

CS3492 - Database Management Systems

41
Jeppiaar Engineering College R-2021

efficiency and readability. Additionally, embedded SQL and dynamic SQL allow
integration with programming languages for database interactions.

10.2 Nested Queries

A nested query (or subquery) is a query inside another query. It is used when the result
of one query is needed as input for another query.

10.2.1 Types of Nested Queries

1. Scalar Subqueries – Return a single value.
2. Row Subqueries – Return a single row with multiple columns.
3. Table Subqueries – Return multiple rows and columns.
4. Correlated Subqueries – Depend on outer query results for execution.

10.2.2 Example of a Nested Query

Find employees earning more than the average salary in the company.

SQL

SELECT Name, Salary

FROM Employee
WHERE Salary > (SELECT AVG(Salary) FROM Employee);

10.3 Views
A view is a virtual table that represents the result of a query. It helps in securing data by
restricting access to specific columns or rows.

10.3.1 Creating a View

SQL

CREATE VIEW HighSalaryEmployees AS

SELECT Name, Salary
FROM Employee
WHERE Salary > 50000;

10.3.2 Using a View

SQL

SELECT * FROM HighSalaryEmployees;

10.3.3 Advantages of Views

Simplifies complex queries.
Provides an additional layer of security.
Enhances data abstraction.

10.4 Joins in SQL

A join combines rows from two or more tables based on a related column.

CS3492 - Database Management Systems

42
Jeppiaar Engineering College R-2021

10.4.1 Types of Joins

1. Inner Join – Returns matching rows from both tables.
2. Left Join – Returns all rows from the left table and matching rows from the right table.
 3. Right Join – Returns all rows from the right table and matching rows from the left table.
4. Full Outer Join – Returns all rows from both tables, filling in NULLs where there is no
match.

10.4.2 Example of an Inner Join

List all employees along with their department names.

SQL

SELECT Employee.Name, Department.DeptName

FROM Employee
INNER JOIN Department
ON Employee.DeptID = Department.DeptID;

10.5 Grouping and Aggregation

SQL provides functions for summarizing data.

10.5.1 Aggregate Functions

COUNT() – Counts rows.
SUM() – Adds values.
AVG() – Computes average.
MIN() – Finds minimum.
MAX() – Finds maximum.

10.5.2 Example of Grouping and Aggregation

Find the total salary per department.

SQL

SELECT DeptID, SUM(Salary) AS TotalSalary

FROM Employee
GROUP BY DeptID;

10.6 Embedded SQL

Embedded SQL allows SQL commands to be included in programming languages like C,
Java, or Python.

CS3492 - Database Management Systems

43
Jeppiaar Engineering College R-2021

10.6.1 Example of Embedded SQL in C

C

EXEC SQL BEGIN DECLARE SECTION;

 int emp_id;
char emp_name[50];
EXEC SQL END DECLARE SECTION;

EXEC SQL SELECT Name INTO :emp_name FROM Employee WHERE EmpID = :emp_id;

10.6.2 Advantages of Embedded SQL

Enables SQL execution inside applications.
Improves database connectivity in software systems.

10.7 Dynamic SQL

Dynamic SQL allows queries to be created and executed at runtime.

10.7.1 Example of Dynamic SQL

SQL

PREPARE stmt FROM 'SELECT * FROM Employee WHERE Salary > ?';
EXECUTE stmt USING 50000;

10.7.2 Benefits of Dynamic SQL

Allows flexibility in query execution.
Supports user-defined query parameters.

10.8 Summary
Nested Queries: Queries within queries; used for comparisons and filtering.
Views: Virtual tables that simplify complex queries and enhance security.
Joins: Combine rows from multiple tables based on a common column.
Grouping & Aggregation: Use functions like SUM() , COUNT() , AVG() to summarize data.
Embedded SQL: Integrate SQL within programming languages for seamless database
interaction.
Dynamic SQL: Construct and execute SQL statements at runtime for flexibility.

CS3492 - Database Management Systems

44
Jeppiaar Engineering College R-2021

Review Questions
Part-A
 Sl.
Question Knowledge No.
Level
1 What is a nested query in SQL? Give an example. KL2 2 Define views
in SQL. How are they different from tables? KL1 3 What is the purpose of
GROUP BY in SQL? KL2 4 Differentiate between inner join and outer
join. KL3

5 What are the advantages of using embedded SQL in KL2

programming?

6 Explain the significance of aggregate functions in SQL. KL1 7 What is

dynamic SQL? How is it different from static SQL? KL2

Part-B

Sl.
Question Knowledge No.
Level
1 Explain nested queries in SQL with different types. 3 What is embedded SQL? Explain its usage with an
Provide examples of scalar subqueries,table example in C or Java. How does it differ from dynamic
subqueries, and SQL?
correlated subqueries. KL3
2 Discuss various types of joins in SQL with syntax
and examples. How do joins enhance database querying
efficiency? KL4 KL3

CS3492 - Database Management Systems

45
Jeppiaar Engineering College R-2021
Unit-2
Database Design
Entity-Relationship model – E-R Diagrams – Enhanced-ER Model – ER-to-Relational
Mapping – Functional Dependencies – Non-loss Decomposition – First, Second, Third
Normal Forms, Dependency Preservation – Boyce/Codd Normal Form – Multi-valued
Dependencies and Fourth Normal Form – Join Dependencies and Fifth Normal Form

CS3492 - Database Management Systems

46
Jeppiaar Engineering College R-2021

1.Entity-Relationship Model (ER Model)

1.1 Introduction to ER Model
The Entity-Relationship (ER) Model is a high-level conceptual data model that
describes data in terms of entities, attributes, and relationships. It provides a
graphical representation of the data structure and is widely used in database design.

The ER model serves as a blueprint for designing databases by capturing the logical
structure of the data and its relationships.

1.2 Entities and Attributes

1.2.1 Entities
An entity is a real-world object that can be uniquely identified. Entities represent
objects, people, places, or events that store data in the database.

Example:

A student in a university database.

A car in a vehicle registration system.

Entities are classified into two types:

1. Strong Entities: Entities that have a unique key attribute to identify them.
Example: Student with Student_ID .
2. Weak Entities: Entities that depend on a strong entity for identification and do not have
sufficient attributes to form a primary key.
Example: Dependent in an insurance database, which depends on Employee .

E-R diagram showing entity sets instructor and student.

1.2.2 Attributes
An attribute is a property or characteristic of an entity. Each attribute holds a specific
piece of information about an entity.

Example:

A Student entity may have attributes: Student_ID , Name , DOB , Email .

Types of Attributes:
Simple (Atomic) Attribute: Cannot be divided further.
Example: Age , Salary .
Composite Attribute: Can be divided into smaller attributes.
Example: Name → (First Name, Last Name) .

CS3492 - Database Management Systems

47
Jeppiaar Engineering College R-2021

Derived Attribute: Derived from other attributes.

Example: Age derived from DOB .
Multivalued Attribute: Can hold multiple values.
 Example: Phone Numbers .
Key Attribute: Uniquely identifies an entity.
Example: Student_ID .

1.3 Relationships and Cardinality

1.3.1 Relationships

A relationship represents an association between two or more entities.

Example:

A Student enrolls in a Course.

An Employee works for a Department.

Relationship set advisor (only some attributes of instructor and student are shown)

E-R diagram showing relationship set advisor

E-R diagram with role indicators

CS3492 - Database Management Systems

48

Jeppiaar Engineering College R-2021

 E-R diagram with an attribute attached to a relationship set

1.3.2 Relationship Types

Relationships can be classified into three types based on cardinality:

1. One-to-One (1:1): Each entity in set A is related to at most one entity in set B, and vice
versa.
Example: A Country has only one President .
2. One-to-Many (1:M): A single entity in set A can be associated with multiple entities in set
B, but not vice versa.
Example: A Professor teaches multiple Courses , but each Course is taught by only
one Professor .
3. Many-to-Many (M:N): Multiple entities in set A can be associated with multiple entities in
set B.
Example: A Student enrolls in multiple Courses , and each Course has multiple
Students .

1.4 Weak and Strong Entities

1.4.1 Strong Entities

Strong entities have a primary key that uniquely identifies each instance.

Example:

Employee (Emp_ID, Name, Age, Department) .

Car (Car_ID, Model, Manufacturer) .

1.4.2 Weak Entities

A weak entity does not have a primary key and depends on a strong entity for
identification. It has a partial key that distinguishes it within the relationship.

Example:

Dependent (Dependent_Name, Relationship) depends on Employee (Emp_ID) .

Order_Item (Quantity, Price) depends on Order (Order_ID) .

A weak entity is always associated with a strong entity through an identifying

relationship, which is represented using a double rectangle in an ER diagram.

CS3492 - Database Management Systems

49
Jeppiaar Engineering College R-2021

1.5 Examples of ER Models

1.5.1 Example 1: University Database

Entities:

Student (Student_ID, Name, DOB, Major, Email)

Course (Course_ID, Title, Credits)
Instructor (Instructor_ID, Name, Department)
 Relationships:
Student enrolls in Course (Many-to-Many).
Instructor teaches Course (One-to-Many).

1.5.2 Example 2: Hospital Management System

Entities:

Patient (Patient_ID, Name, Age, Address, Phone)

Doctor (Doctor_ID, Name, Specialization)
Appointment (Appointment_ID, Date, Time)

Relationships:
Patient consults Doctor (Many-to-Many).
Doctor schedules Appointment (One-to-Many).

1.6 Summary
The ER Model provides a conceptual framework for designing databases.
Entities represent real-world objects, and attributes store their properties.
Relationships define associations between entities, categorized as 1:1, 1:M, or M:N.
Strong Entities have primary keys, while Weak Entities depend on strong entities for
identification.
The ER Model is widely used in designing real-world applications like universities,
hospitals, and e-commerce systems.

CS3492 - Database Management Systems

50
Jeppiaar Engineering College R-2021

Review Questions
Part-A

Sl. Question Knowledge Level (KL) Objectives (PO)

No. Program
1 Define an entity and provide an example. KL1 PO1
2 What are attributes in the ER model? Differentiate Part-B
between simple and KL2 PO1
composite attributes.
3 Explain the difference between weak and strong
entities with examples. KL2 PO1, PO2 KL3 PO1, PO2
4 What are the different types of relationships in an ER
model? Provide an example for each.

5 Why is cardinality important in database design? KL3 PO2, PO3

Explain with an example.

Sl. No. Program Objectives (PO)

Question Knowledge Level (KL)
1 Draw an ER diagram for a university database that are handled in this scenario.
includes students, courses, and instructors. Clearly KL4 PO2, PO3
indicate entities, attributes,
relationships, and cardinality constraints.
2 Consider a hospital management system. Identify
the major entities, their attributes, and relationships. KL5 PO2, PO3, PO5
Construct an ER diagram and explain how weak entities

2.E-R Diagrams and Enhanced ER Model (EER Model)

2.1. Introduction to ER Diagrams and EER Model
The Entity-Relationship (ER) Model is a fundamental conceptual framework used in
database design to represent real-world entities and their relationships. It serves as a
blueprint for constructing relational databases by defining entities, their attributes, and
the relationships among them.

However, real-world applications often involve more complex relationships and

hierarchies that the basic ER model cannot adequately represent. To address these
limitations, the Enhanced Entity-Relationship (EER) Model extends the ER model by
introducing advanced features such as generalization, specialization, aggregation,
and category (union). These

CS3492 - Database Management Systems

51
Jeppiaar Engineering College R-2021

concepts allow for better modeling of hierarchical relationships and complex

constraints in modern databases.

This section explores ER diagrams, their symbols and conventions, key constraints
in ER modeling, and the enhancements introduced in the EER model to support
advanced data modeling.

2.2. ER Diagrams: Symbols and Conventions

 2.2.1. Symbols in ER Diagrams
ER diagrams use standardized symbols to represent different components of a database
schema.

Symbol Representation Description

Rectangle Entity Represents an entity type
Ellipse Attribute Represents attributes of an entity
Double Ellipse Multivalued Attribute Represents attributes that can have multiple
values
Dashed Ellipse Derived Attribute Represents attributes derived from other
attributes
Diamond Relationship Represents relationships between entities
Line Link Connects entities to relationships
Double Rectangle Weak Entity Represents an entity that depends on a
strong entity
Double Diamond Identifying Relationship Underlined Attribute
Represents relationships involving weak entities Primary Key Represents a unique identifier for an entity

2.2.2. Example ER Diagram

Consider a university database where students enroll in courses.

Entities and Attributes:

Student (Student_ID, Name, DOB, Email)
Course (Course_ID, Title, Credits)
Relationships:
Enrollment (Many-to-Many): A student can enroll in multiple courses, and a course
can have multiple students.

ER Diagram Representation:

[STUDENT] ─── (ENROLLS) ─── [COURSE]

CS3492 - Database Management Systems

52
Jeppiaar Engineering College R-2021

2.3. Generalization, Specialization, and Aggregation

2.3.1. Generalization

Definition: Generalization is the process of merging multiple lower-level entity types into a
higher-level entity type by abstracting common attributes.
Purpose: It reduces redundancy and enhances data organization by introducing a
superclass that groups entities sharing common features.
Example:
 Car and Truck can be generalized into Vehicle (Superclass).
Common attributes: Vehicle_ID , Manufacturer .
Specific attributes remain in the subclasses.

Diagram Representation:

[VEHICLE]
▲
┌────┴────┐
[CAR] [TRUCK]

2.3.2. Specialization
Definition: Specialization is the opposite of generalization. It involves dividing a higher
level entity into multiple specialized subclasses based on unique characteristics.
Purpose: Specialization helps define entity-specific attributes and relationships.
Example:
A Person entity can be specialized into Student and Professor.
Person has common attributes like Name , Age , Address .
Student has specific attributes like Student_ID , Major .
Professor has specific attributes like Employee_ID , Department .

Diagram Representation:

[PERSON]
▼
┌──────┴──────┐
[STUDENT] [PROFESSOR]

2.3.3. Aggregation
Definition: Aggregation is a higher-level abstraction where a relationship itself is treated
as an entity and can participate in another relationship.
Purpose: It allows relationships to have their own attributes and be associated with other
entities.
Example:
A Project entity is associated with multiple Departments.
The Manages relationship between Employee and Department itself participates in a
relationship with Project.

Diagram Representation:

CS3492 - Database Management Systems

53
Jeppiaar Engineering College R-2021

[EMPLOYEE] ─── (MANAGES) ─── [DEPARTMENT]

▲
[PROJECT]

2.4. Constraints in ER Modeling

2.4.1. Mapping Cardinality
 Mapping cardinality specifies how many instances of one entity can be related to
instances of another entity.

1:1 (One-to-One): Each entity in A relates to at most one entity in B.

Example: A country has one president.
1:M (One-to-Many): One entity in A relates to many entities in B.
Example: A professor teaches multiple courses.
M:N (Many-to-Many): Many entities in A relate to many entities in B.
Example: Students enroll in multiple courses, and each course has multiple
students.

2.4.2. Participation Constraints

Total Participation: Every instance must be involved.
Example: Every student must enroll in at least one course.
Partial Participation: Some instances may not participate.
Example: Some professors may not be assigned to courses.

2.5. Enhanced ER Model (EER Model)

2.5.1. What is the EER Model?

The Enhanced Entity-Relationship (EER) Model extends the basic ER model by

incorporating additional concepts to support complex database designs.

Key Features of EER Model:

1. Generalization and Specialization (Hierarchical Relationships)

2. Aggregation (Higher-Level Relationships)
3. Category (Union) Relationships

These enhancements make the EER model more expressive and better suited for
real-world applications where hierarchical and complex relationships exist.

2.5.2. Differences Between ER Model and EER Model

Feature ER Model EER Model

Concepts Used Generalization, Specialization,
Entities, Attributes, Relationships Aggregation
Complexity Basic Database Design Supports Hierarchical Structures
Use Case Simple applications Large-scale, hierarchical applications

CS3492 - Database Management Systems

54
Jeppiaar Engineering College R-2021

2.6. Summary
ER Diagrams provide a graphical representation of entities, attributes, and relationships.
Generalization combines entities into a superclass, while specialization creates
subclasses from a superclass.
Aggregation allows relationships to act as higher-level entities.
Constraints like cardinality and participation define entity relationships.
EER Model extends ER concepts to model complex applications.
 CS3492 - Database Management Systems
55
Jeppiaar Engineering College R-2021

Review Questions
Part-A

Sl. Question Knowledge Level (KL) Objectives (PO)

No. Program
1 Define an ER Diagram and list its basic components. Part-B
2 Explain the difference between generalization and KL1 PO1 KL2 PO2
specialization with an example.
3 What is aggregation in an ER model? Why is it needed?
KL2 PO1, PO3 KL3 PO2, PO3
4 Differentiate between ER Model and Enhanced ER
Model (EER Model).

Sl. No. Program Objectives (PO)

Question Knowledge Level (KL)
1 Construct an ER diagram for a Library Management database modeling.
System where: (i) A Library has multiple Books, each KL4 PO2, PO3, PO5
identified by a unique ISBN. (ii) Members can borrow
Books for a limited period. (iii) Each Book is classified
under a
Category such as Fiction, Science, etc. Use appropriate
entities, attributes, relationships, and constraints.

2 Explain the EER Model in detail with a suitable

example. Discuss how generalization, KL5 PO1, PO2, PO3
specialization, and aggregation improve

CS3492 - Database Management Systems

56
Jeppiaar Engineering College R-2021
3.ER-to-Relational Mapping
3.1 Introduction to ER-to-Relational Mapping
The ER-to-Relational Mapping process involves converting an Entity-Relationship
(ER) model into a relational database schema. This transformation ensures that the
conceptual design of a database, represented by an ER model, can be efficiently
implemented in a relational database management system (RDBMS).

The main goal of ER-to-Relational Mapping is to systematically translate entities,

attributes, relationships, and constraints into tables, columns, keys, and
integrity constraints in the relational model.

Why is ER-to-Relational Mapping Important?

Ensures a smooth transition from conceptual database design to physical
implementation.
Helps in maintaining data integrity, consistency, and efficiency.
Provides a clear and structured method for database normalization.
Helps in handling real-world relationships, constraints, and cardinalities.

3.2 Steps in Mapping an ER Model to a Relational Model

The ER-to-Relational Mapping process follows a series of well-defined steps:

1. Mapping Strong Entities to Tables

2. Mapping Weak Entities and Identifying Relationships
3. Mapping Binary Relationships (1:1, 1:M, M:N)
4. Mapping Multivalued Attributes
5. Mapping Specialization and Generalization
6. Mapping Aggregation and Composition
7. Handling Constraints (Key, Participation, Referential Integrity)

3.3 Converting Entities, Relationships, and Attributes

3.3.1 Mapping Strong Entities
A strong entity is mapped to a table, where:

The attributes of the entity become the columns of the table.

The primary key of the entity becomes the primary key of the table.

Example:

Consider the entity Student:

Student_ID (PK) Name Age Major

101 John 20 CS
102 Mary 22 AI

This entity is mapped to a table named Student with attributes Student_ID , Name ,
Age , and Major .

CS3492 - Database Management Systems

57
Jeppiaar Engineering College R-2021
3.3.2 Mapping Weak Entities
A weak entity does not have a primary key and depends on a strong entity for
identification. It is mapped as:

A separate table including its attributes.

The primary key of the strong entity is added as a foreign key.
A composite key is created using the weak entity’s partial key and the strong entity’s
primary key.

Example:

Consider an Employee entity with a Dependent weak entity:

Employee_ID (PK) Name Age Department

201 Alice 30 HR
202 Bob 28 IT

Dependent_Name (PK) Relationship Employee_ID (FK)

Lily Daughter 201
Sam Son 202

Here, Dependent table has Dependent_Name as a partial key, and it references

Employee_ID as a foreign key.

3.3.3 Mapping Relationships

One-to-One (1:1) Relationship

A foreign key is placed in one of the tables.

If participation is total, then the foreign key is set as NOT NULL.

Example: A CEO is assigned to a Company (1:1 relationship).

Company_ID (PK) Name CEO_ID (FK)

501 Google 701
502 Amazon 702

CEO_ID (PK) Name

701 Mark
702 Jeff

CS3492 - Database Management Systems

58
Jeppiaar Engineering College R-2021
One-to-Many (1:M) Relationship
The primary key of the one-side entity is placed as a foreign key in the many-side
entity.

Example: A Professor teaches multiple Courses.

Professor_ID (PK) Name Department

1001 Dr. Smith CS
1002 Dr. Brown AI

Course_ID (PK) Title Professor_ID (FK)

CS101 DBMS 1001
AI202 Machine Learning 1002

Many-to-Many (M:N) Relationship

A junction table (or associative entity) is created.
The primary keys of both entities become foreign keys in the new table.

Example: A Student enrolls in multiple Courses.

Student_ID (PK) Name

101 John
102 Mary

Course_ID (PK) Title

CS101 DBMS
AI202 ML

Student_ID (FK) Course_ID (FK)

101 CS101
101 AI202
102 CS101

3.4 Handling Weak Entities and Hierarchical Structures

Mapping Weak Entities

The weak entity is converted into a table.

The primary key of the related strong entity is included as a foreign key.
A composite primary key is created if necessary.

CS3492 - Database Management Systems

59
Jeppiaar Engineering College R-2021
Mapping Specialization and Generalization
Generalization: Mapping multiple entities with common attributes into a superclass
table. Specialization: Mapping subclasses with additional attributes.

Example:

A Vehicle entity with Car and Bike specializations.

Vehicle_ID (PK) Type

V001 Car
V002 Bike

Vehicle_ID (FK, PK) Engine_Type Num_Seats

V001 Petrol 4

Vehicle_ID (FK, PK) Bike_Type

V002 Sports

3.5 Mapping Constraints and Participation

3.5.1 Mapping Constraints

Key Constraints: Ensure uniqueness using primary keys. Referential Integrity:

Foreign keys must reference valid primary keys.

3.5.2 Participation Constraints

Total Participation: Every entity in A must be related to an entity in B. Partial
Participation: Some entities in A may not be related to B.

3.6 Examples of ER-to-Relational Mapping

To better understand ER-to-Relational Mapping, let's go through two detailed case
studies. These examples demonstrate how different entities, attributes, and
relationships from an ER model can be systematically converted into relational tables.

Example 1: Hospital Management System

A hospital system manages Doctors, Patients, and Appointments.

Step 1: Identify the Entities and Attributes

The main entities in this system are:

1. Doctor

Doctor_ID (Primary Key)

Name

CS3492 - Database Management Systems

60
Jeppiaar Engineering College R-2021
 Specialization
Experience

2. Patient

Patient_ID (Primary Key)

Name
Age
Gender
Phone

3. Appointment (Relationship between Doctor and Patient)

Appointment_ID (Primary Key)

Doctor_ID (Foreign Key)
Patient_ID (Foreign Key)
Date
Time
Status (Confirmed, Pending, Cancelled)

Step 2: Draw the ER Model

The ER diagram consists of:

Entities: Doctor, Patient

Relationship: Appointment (M:N between Doctor and Patient)
Attributes: Each entity has its respective attributes

Step 3: Convertthe ER Modelto Relational Tables

Doctor Table Patient Table

Doctor_ID (PK) Patient_ID (PK)
Name Name
Specialization Age
Experience Gender
Phone

Appointment Table
Appointment_ID (PK)
Doctor_ID (FK)
Patient_ID (FK)
Date
Time
Status

Mapping Explanation:

The Doctor and Patient entities are mapped to separate tables.

CS3492 - Database Management Systems

61
Jeppiaar Engineering College R-2021
 The Appointment relationship is many-to-many (M:N), so a junction table
( Appointment ) is created.
The Appointment table contains foreign keys ( Doctor_ID , Patient_ID ) referencing their
respective tables.

Example Queries for Hospital Database

1. Retrieve all patients of a particular doctor:

SQL

SELECT Patient.Name, Patient.Age, Patient.Gender

FROM Patient
JOIN Appointment ON Patient.Patient_ID = Appointment.Patient_ID
WHERE Appointment.Doctor_ID = 101;

2. Find all appointments for a specific date:

SQL

SELECT Doctor.Name, Patient.Name, Appointment.Date, Appointment.Time

FROM Doctor
JOIN Appointment ON Doctor.Doctor_ID = Appointment.Doctor_ID
JOIN Patient ON Patient.Patient_ID = Appointment.Patient_ID
WHERE Appointment.Date = '2025-02-10';

Example 2: University Database System

A university database manages Students, Courses, and Enrollments.

Step 1: Identify the Entities and Attributes

The main entities and attributes in the system are:

1. Student

Student_ID (Primary Key)

Name
Major
Year

2. Course

Course_ID (Primary Key)

Title
Credits
Department

3. Enrollment (Relationship between Student and Course)

Student_ID (Foreign Key)

CS3492 - Database Management Systems

62
Jeppiaar Engineering College R-2021
 Course_ID (Foreign Key)
Grade

Step 2: Draw the ER Model

The ER diagram consists of:

Entities: Student, Course

Relationship: Enrollment (M:N between Student and Course)
Attributes: Each entity has its respective attributes

Step 3: Convertthe ER Modelto Relational Tables

Student Table Course Table

Student_ID (PK) Course_ID (PK)
Name Title
Major Credits
Year Department

Enrollment Table (M:N Relationship)

Student_ID (FK, PK)
Course_ID (FK, PK)
Grade

Mapping Explanation:

Students and Courses are stored in separate tables.

The Enrollment relationship is M:N, so a junction table ( Enrollment ) is created.
The Enrollment table includes foreign keys referencing Student_ID and Course_ID .
The composite primary key ( Student_ID, Course_ID ) ensures uniqueness, preventing
duplicate enrollments.

Example Queries for University Database

1. Retrieve all courses a student is enrolled in:

SQL

SELECT Course.Title, Course.Credits

FROM Course
JOIN Enrollment ON Course.Course_ID = Enrollment.Course_ID
WHERE Enrollment.Student_ID = 2001;

2. Find students enrolled in a specific course:

CS3492 - Database Management Systems

63
Jeppiaar Engineering College R-2021
 SQL

SELECT Student.Name, Student.Major

FROM Student
JOIN Enrollment ON Student.Student_ID = Enrollment.Student_ID
WHERE Enrollment.Course_ID = 'CS101';

Key Takeaways from ER-to-Relational Mapping

1. Entities become relational tables, and attributes become columns.
2. Relationships are mapped using primary and foreign keys.
3. Many-to-Many relationships require an additional junction table.
4. Weak entities include a foreign key from the strong entity.
5. Constraints (e.g., primary keys,foreign keys) ensure data integrity.

3.7 Summary
ER-to-Relational Mapping converts ER models into relational schemas.
Entities become tables, attributes become columns.
Relationships are mapped using foreign keys and junction tables.
Specialization, generalization, and weak entities require additional handling.

CS3492 - Database Management Systems

64
Jeppiaar Engineering College R-2021
 Review Questions
Part-A

Sl.
Question KL PO No.
1 What is ER-to-Relational Mapping? Explain its importance. KL1 PO1
2 How are weak entities handled in relational mapping? 4 Explain how multivalued attributes are converted in
Provide an example. relational mapping.
3 Differentiate between specialization and generalization
in ER mapping.
Part-B
KL2 PO1 KL3 PO2 KL3 PO2

Sl.
Question KL PO No.
1 Convert the given ER model for an Online Shopping 2 Discuss the process of mapping an ER model for a
System into a relational database schema. Library Management System to tables.
KL4 PO3 KL5 PO4

CS3492 - Database Management Systems

65
Jeppiaar Engineering College R-2021
 4.Functional Dependencies and Non-loss Decomposition
4.1 Introduction
Functional dependencies (FDs) form the foundation of relational database design by
establishing constraints between attributes in a relation. Understanding FDs is crucial for
normalizing databases, ensuring data integrity, and avoiding redundancy.

Non-loss decomposition, also called lossless decomposition, is a technique to break

down relations into smaller relations without losing information. This ensures that the
original relation can be reconstructed from the decomposed relations.

This session covers:

Functional dependencies and their types

Finding the closure of functional dependencies
Ensuring lossless decomposition in database design

4.2 Concept of Functional Dependencies

A functional dependency (FD) is a constraint between two sets of attributes in a
relation. It defines how one set of attributes uniquely determines another set of
attributes.

Definition

A functional dependency is denoted as:

X→Y
where:

X (determinant) is a set of one or more attributes.

Y (dependent) is another set of attributes.
The dependency means that if two tuples have the same values for X, they must also have
the same values for Y .

Example

Consider a relation Student(Roll_No, Name, Course, Department) with the following FDs:

1. Roll_No → Name, Course, Department (Roll number uniquely determines Name, Course,
and Department)
2. Course → Department (Each course belongs to a single department)

This means:

If two students have the same Roll_No, they must have the same Name, Course, and
Department.
If two students have the same Course, they must have the same Department.

4.3 Trivial and Non-trivial Functional Dependencies

Trivial Functional Dependencies

A functional dependency is trivial if the dependent attributes are a subset of the

determinant. Mathematically:

CS3492 - Database Management Systems

66
Jeppiaar Engineering College R-2021
 X → Y is trivial if Y ⊆ X

Example:

{Roll_No, Name} → Roll_No (Trivial because Roll_No is part of {Roll_No, Name})

{A, B} → A (Trivial because A is already in {A, B})

Non-trivial Functional Dependencies

A functional dependency is non-trivial if the dependent attributes are not a subset of
the determinant.

Mathematically:

X → Y is non-trivial if Y ⊈ X

Example:

Roll_No → Name (Non-trivial because Name is not part of Roll_No)

Course → Department (Non-trivial because Department is not part of Course)

Completely Non-trivial Dependencies

A dependency is completely non-trivial if the determinant and dependent sets have no
common attributes.

Example:

A → B is completely non-trivial if A and B are completely different attributes.

4.4 Closure of Functional Dependencies

The closure of a set of functional dependencies (F) refers to the complete set of all
functional dependencies that can be inferred from F.

Definition

Given a set of FDs F, the closure of an attribute set X, denoted as X+, is the set of all
attributes that can be functionally determined by X using the given FDs.

Algorithm to Compute Closure

To compute X+:

1. Start with X+ = X.
2. Iterate through the functional dependencies in F.
3. If X+ contains all attributes in the determinant of an FD A → B, add B to X+.
4. Repeat until no more attributes can be added.

Example
Consider the relation R(A, B, C, D, E) with FDs:

1. A → B
2. B → C
3. C → D

Find A⁺ (Closure of A):

4. A⁺ = {A} (Initial set)

CS3492 - Database Management Systems

67
Jeppiaar Engineering College R-2021
 5. A → B, so add B → A⁺ = {A, B}
6. B → C, so add C → A⁺ = {A, B, C}
7. C → D, so add D → A⁺ = {A, B, C, D}

Thus, A⁺ = {A, B, C, D}.

4.5 Non-loss (Lossless) Decomposition

Definition

A decomposition of a relation R into R1, R2,…, Rn is lossless if there is no loss of

information, meaning the original relation can be reconstructed using natural joins.

Mathematically:

R = R 1 ⋈ R2 ⋈ ⋯ ⋈ R n

must hold without producing spurious tuples.

Example of Lossless Decomposition

Consider R(A, B, C) with the FD A → B.

A possible decomposition:

R1(A, B)
R2(A, C)

This is lossless because performing R1 ⋈ R2 using A restores the original relation R.

4.6 Properties of Non-loss Decomposition

To ensure a decomposition is lossless, it must satisfy:

Functional Dependency Condition

At least one of the decomposed relations must contain a superkey of the original relation.

Attribute Preservation

Each attribute in the original relation must appear in at least one decomposed relation.

Dependency Preservation
The decomposed relations should still enforce all functional dependencies of the original
relation.

No Spurious Tuples

A lossless decomposition should not produce extra tuples when performing a natural join.

4.7 Summary
Functional dependencies define attribute constraints in a relation.
Trivial FDs have dependent attributes that are part of the determinant, while non-trivial
FDs do not.
Closure of FDs helps determine attribute dependencies.
Non-loss decomposition ensures that a relation can be decomposed without losing
information.

CS3492 - Database Management Systems

68
Jeppiaar Engineering College R-2021
A decomposition is lossless if at least one of the decomposed relations contains a
superkey.

CS3492 - Database Management Systems

69
Jeppiaar Engineering College R-2021
 Review Questions
Part-A
Q.No Question Knowledge Level (KL) Q.No Question Knowledge Level (KL)
Program Program Objective (PO)
Objective (PO)
1 Explain the concept of functional dependency. How do
1 Define functional dependency with an example. trivial and non-trivial functional
dependencies affect database design? Provide examples.
2 Differentiate between trivial and non-trivial functional
dependencies. 2 Describe the steps to compute the closure of a set of
functional dependencies. Illustrate with an example.
3 What is the closure of a functional dependency set?
Explain its significance. 3 Explain non-loss decomposition in detail. How do we
ensure a decomposition is lossless? Provide an example
4 State and explain Armstrong’s axioms for functional
with step-by-step
dependencies.
verification.
5 What is non-loss decomposition? Why is it important? KL3 PO2, PO3, PO5

6 List the properties of non-loss

decomposition.
KL3 PO2, PO4 KL4 PO3, PO5

Part-B
KL1 PO1, PO2 KL2 PO1, PO3 KL2 PO1, PO4 KL1 PO1, PO2

KL2 PO2, PO5 KL1 PO1, PO3

CS3492 - Database Management Systems

70
Jeppiaar Engineering College R-2021

5.First,Second,and Third Normal Forms

5.1. Concept of Normalization
Normalization is a process in database design that organizes data to reduce redundancy
and improve data integrity. It involves decomposing tables into smaller, well-structured
tables based on functional dependencies. The primary objectives of normalization are:

Eliminating redundant data to save storage space.

Minimizing update anomalies to maintain data consistency.
Ensuring data integrity by enforcing proper relationships.
Simplifying queries by structuring tables effectively.
The process of normalization is divided into multiple normal forms, each addressing
specific types of redundancy and anomalies.

5.2. First Normal Form (1NF) and its Characteristics

A relation is in First Normal Form (1NF) if:

1. All attributes contain atomic values (i.e., no multi-valued or composite attributes).

2. Each column contains values of a single type (e.g., integers, strings).
3. Each column has a unique name for clarity.
4. The order of rows and columns does not matter in a relation.

Example: Non-1NF Table

Student_ID Name Subjects

101 Alice Math, Physics
102 Bob Chemistry
103 Charlie Math, CS

This table violates 1NF because the Subjects column contains multiple values. To
convert it to 1NF, we need to eliminate multi-valued attributes by creating separate
rows for each subject.

Example: 1NF Compliant Table

Student_ID Name Subject

101 Alice Math
101 Alice Physics
102 Bob Chemistry
103 Charlie Math
103 Charlie CS

This table now satisfies 1NF because all attributes contain atomic values.

CS3492 - Database Management Systems

71
Jeppiaar Engineering College R-2021

5.3. Second Normal Form (2NF) and Partial Dependencies

A relation is in Second Normal Form (2NF) if:

1. It is in 1NF.
2. It does not have partial dependencies (i.e., non-key attributes should depend on the
whole primary key, not just part of it).

A partial dependency occurs when a non-key attribute depends only on a part of the
composite primary key.

Example: 1NF Table with Partial Dependency

 Student_ID Course_ID Course_Name Instructor
101 CSE101 DBMS Dr. Smith
101 CSE102 DSA Dr. Brown
102 CSE101 DBMS Dr. Smith

Primary Key: (Student_ID, Course_ID)

Issue: Course_Name and Instructor depend only on Course_ID, not on Student_ID.

To remove partial dependency, we split the table:

Example: 2NF Compliant Tables

Student-Course Table:

Student_ID Course_ID
101 CSE101
101 CSE102
102 CSE101

Course Table:

Course_ID Course_Name Instructor

CSE101 DBMS Dr. Smith
CSE102 DSA Dr. Brown

Now, all non-key attributes depend on the entire primary key.

5.4. Third Normal Form (3NF) and Transitive Dependencies

A relation is in Third Normal Form (3NF) if:

1. It is in 2NF.

CS3492 - Database Management Systems

72
Jeppiaar Engineering College R-2021

2. It does not have transitive dependencies, meaning no non-key attribute should depend
on another non-key attribute.

A transitive dependency occurs when a non-key attribute depends on another non-key

attribute, which in turn depends on the primary key.

Example: 2NF Table with Transitive Dependency

Employee_ID Name Department_ID Department_Name

201 John D001 HR

202 Alice D002 Finance
 203 Bob D001 HR

Primary Key: Employee_ID

Issue: Department_Name depends on Department_ID, which depends on Employee_ID.

To remove transitive dependency, we split the table:

Example: 3NF Compliant Tables

Employee Table:

Employee_ID Name Department_ID

201 John D001

202 Alice D002

203 Bob D001

Department Table:

Department_ID Department_Name

D001 HR
D002 Finance

Now, each non-key attribute directly depends on the primary key.

5.5. Examples of 1NF, 2NF, and 3NF

Example: Converting a Table to Normal Forms

Step 1: Unnormalized Table (UNF)

Order_ID Customer Items

501 Alice Laptop, Mouse

502 Bob Keyboard

CS3492 - Database Management Systems

73
Jeppiaar Engineering College R-2021

Issue: Items column has multiple values.

Step 2: Convert to 1NF

Order_ID Customer Item

501 Alice Laptop
501 Alice Mouse
502 Bob Keyboard

Now, each column contains atomic values.

Step 3: Convert to 2NF

Order_ID Customer
501 Alice
502 Bob

Order_ID Item
501 Laptop
501 Mouse
502 Keyboard

Removed partial dependency by separating items.

Step 4: Convert to 3NF

Customer_ID Customer_Name
C001 Alice
C002 Bob

Order_ID Customer_ID
501 C001
502 C002

Order_ID Item
501 Laptop
501 Mouse
502 Keyboard

Removed transitive dependency by creating a separate customer table.

CS3492 - Database Management Systems

74
Jeppiaar Engineering College R-2021

Example: Converting a Student Table to Normal Forms

Step 1: Unnormalized Table (UNF)

Student_ID Name Subjects Advisor Advisor_Dept

101 Alice Math, Physics Dr. Smith Science
102 Bob Chemistry Dr. Brown Chemistry
103 Charlie Math, CS Dr. Smith Science
 Issue: The Subjects column contains multiple values, violating atomicity.
Issue: The Advisor_Dept depends on Advisor, creating a transitive dependency.

Step 2: Convert to First Normal Form (1NF)

To achieve 1NF, we eliminate multi-valued attributes by creating separate rows for

each subject.

Student_ID Name Subject Advisor Advisor_Dept

101 Alice Math Dr. Smith Science
101 Alice Physics Dr. Smith Science
102 Bob Chemistry Dr. Brown Chemistry
103 Charlie Math Dr. Smith Science
103 Charlie CS Dr. Smith Science

Each attribute now contains atomic values.

No multi-valued attributes exist in the table.

Step 3: Convert to Second Normal Form (2NF)

To achieve 2NF, we need to remove partial dependencies. The primary key is

(Student_ID, Subject) because a student can take multiple subjects. However, Name
depends only on Student_ID, and Advisor, Advisor_Dept depend on Student_ID
rather than on the full primary key.

Decomposed Tables:

Student Table:

Student_ID Name
101 Alice
102 Bob
103 Charlie

Enrollment Table:

CS3492 - Database Management Systems

75
Jeppiaar Engineering College R-2021

Student_ID Subject
101 Math
101 Physics
102 Chemistry
103 Math
103 CS
 Advisor Table:

Student_ID Advisor Advisor_Dept

101 Dr. Smith Science
102 Dr. Brown Chemistry
103 Dr. Smith Science

Now, all non-key attributes depend on the full primary key.

No partial dependencies remain.

Step 4: Convert to Third Normal Form (3NF)

To achieve 3NF, we must remove transitive dependencies. Advisor_Dept depends

on Advisor, not directly on Student_ID.

Decomposed Tables:

Student Table:

Student_ID Name
101 Alice
102 Bob
103 Charlie

Enrollment Table:

Student_ID Subject
101 Math
101 Physics
102 Chemistry
103 Math
103 CS

CS3492 - Database Management Systems

76
Jeppiaar Engineering College R-2021 Advisor Table:

Student_ID Advisor
101 Dr. Smith
102 Dr. Brown
103 Dr. Smith

Department Table:
 Advisor Advisor_Dept
Dr. Smith Science
Dr. Brown Chemistry

Transitive dependency removed by separating Advisor_Dept into a new table.

Each non-key attribute depends only on the primary key.

Final Observations

Normal Form Key Improvements

1NF Eliminated multi-valued attributes by creating separate rows.

2NF Removed partial dependencies by separating attributes into different
tables.
3NF Removed transitive dependencies by creating a new department table.

This ensures that the database is free from redundancy, anomalies, and inconsistencies.

5.6. Summary
1NF eliminates multi-valued attributes, ensuring atomicity.
2NF removes partial dependencies, ensuring that attributes depend on the entire primary
key.
3NF eliminates transitive dependencies, ensuring that non-key attributes depend only on
the primary key.
Normalization improves data integrity, reduces redundancy, and prevents anomalies in
database design.

CS3492 - Database Management Systems

77
Jeppiaar Engineering College R-2021

Review Questions
Part-A

S. Question Knowledge Level (KL) Objective (PO)

No. Program
1 Define Normalization and explain its significance in 3 Differentiate between Partial
database design. Dependency and Transitive
Dependency.
2 What is First Normal Form (1NF)? Provide an
example.
4 Explain the conditions required for a table to be in Part-B
Second Normal Form (2NF). KL1 PO1 KL2 PO1, PO2 KL2 PO1, PO3
5 What are the advantages of Third Normal Form
(3NF)?

KL3 PO2 KL2 PO1

S. No. Program Objective (PO)

Question Knowledge Level (KL)
1 Consider a University Database that maintains 3 Given a Purchase Order database, discuss how
student enrollments in courses. The initial table normalization can help reduce redundancy and improve
structure is as follows: Student_ID, Name, efficiency. Provide examples for each normal form.
Course_Code, Course_Name, Instructor, KL4 PO2, PO3, PO4
Instructor_Office. (a) Identify and explain any
normalization issues. (b) Convert the table into 3NF with
proper decomposition and justification.

2 Explain Normalization with an example and

demonstrate how a table can be converted from UNF →
1NF → 2NF → 3NF. KL4 PO2, PO3 KL5 PO2, PO4

CS3492 - Database Management Systems

78
Jeppiaar Engineering College R-2021

6. Dependency Preservation and Boyce-Codd

Normal Form (BCNF)
6.1. Introduction to Dependency Preservation
Functional dependencies play a crucial role in the normalization process. When a relation
is decomposed into multiple relations, it is essential to ensure that all functional
dependencies can still be enforced without needing to recombine the relations. This
property is known as dependency preservation.

Why is Dependency Preservation Important?

Ensures that all functional dependencies from the original relation can be enforced on the
decomposed relations.
 Prevents unnecessary recombination of relations during query execution.
Maintains consistency and integrity of data.

Example of Dependency Preservation

Consider a relation R(Student_ID, Name, Course, Instructor) with the following
functional dependencies:

1. Student_ID → Name
2. Course → Instructor

If we decompose R into:

R1(Student_ID, Name)
R2(Student_ID, Course, Instructor)

Both dependencies are preserved in the decomposed relations because:

Student_ID → Name is in R1.

Course → Instructor is in R2.

Thus, this decomposition preserves functional dependencies.

6.2. Problems with Third Normal Form (3NF) and

Motivation for BCNF
Although Third Normal Form (3NF) eliminates transitive dependencies, it still allows
some anomalies if:

A functional dependency exists where the left-hand side is not a superkey.

It does not strictly enforce the superkey condition.

Example of a Problematic 3NF Relation

Consider the relation R(Student_ID, Course, Instructor) with the following functional
dependencies:

1. Student_ID, Course → Instructor

2. Course → Instructor (Partial dependency)

The candidate key for this relation is (Student_ID, Course).

CS3492 - Database Management Systems

79