Course Details

1. Subject Code: CO202

2. Course Title: Database Management System

3. Contact Hours:

o Lecture (L): 3

o Tutorial (T): 0

o Practical (P): 2

4. Examination Duration (ETE):

o Theory: 3 hours

o Practical: 0

5. Relative Weightage:

o Classwork (CWS): 15%

o Practical Session (PRS): 15%

o Mid-Term Exam (MTE): 30%

o End-Term Exam (ETE): 40%

o Practical (PR): 0%

6. Credits: 4

7. Semester: IV

8. Subject Area: DCC (Discipline Core Course)

9. Pre-requisite: Data Structures

10. Objective: To provide knowledge about the principles, concepts, and applications of
Database Management Systems.

Details of Course Content

Contact
S. No. Content
Hours

Introduction: Database system concepts and architecture, data models, schema

and instances, data independence, database language, interfaces, DDL, DML.
Overview of database structure. Data modeling using ER Model: ER model
1 7
concepts, notation for ER diagrams, mapping constraints, keys (super key,
candidate key, primary key), generalizations, aggregation, reducing ER diagrams
to tables, extended ER model.

2 Relational Data Model and Language: Relational data model concepts, integrity 7
Contact
S. No. Content
Hours

constraints (keys, domain constraints, referential integrity, assertions, triggers),

foreign key, relational algebra, relational calculus (domain and tuple calculus),
SQL (data definition, queries, updates).

Database Design: Functional dependencies, normal forms (1NF, 2NF, 3NF,

BCNF), multi-valued dependencies, 4NF, join dependencies, 5NF, inclusion
3 6
dependencies, lossless join decompositions, normalization using FD, MVD, and
JD, alternative database design approaches.

File Organization, Indexing, and Hashing: File organization techniques, indexing

and hashing (static, dynamic), ordered indices, multi-level indexes, B-Tree, B+
4 Tree index files, buffer management. Transaction Processing: Transaction 8
processing systems, schedule and recoverability, testing of serializability, conflict
& view serializability, recovery from transaction failures, deadlock handling.

Concurrency Control Techniques: Locking techniques, timestamping protocols,

5 concurrency control in distributed systems, multiple granularities, multi-version 8
schemes.

6 Case Studies: Commercial databases like Oracle, PostgreSQL, MySQL. 6

Total
Contact 42
Hours

Suggested Books

Textbooks

1. Elmasri, Navathe, Fundamentals of Database Systems, Addison Wesley.

2. Korth, Silbertz, Sudarshan, Database Concepts, McGraw-Hill.

Reference Books

1. Ramakrishna, Gehrke, Database Management Systems, McGraw-Hill.

2. Date C.J., An Introduction to Database Systems.

This lesson covers Entity-Relationship (ER) Modeling, which is a conceptual database design
technique used to visually represent entities, attributes, and relationships. Students will learn how to
create ER diagrams, apply keys and constraints, and convert ER models into relational tables.

Learning Objectives

By the end of this lesson, students should be able to:

1. Explain ER model concepts and their significance in database design.

2. Identify and represent entities, attributes, and relationships in an ER diagram.

3. Apply keys (super key, candidate key, primary key) to uniquely identify records.

4. Implement mapping constraints (1:1, 1:M, M:N) in ER diagrams.

5. Utilize generalization, specialization, and aggregation for hierarchical relationships.

6. Convert an ER diagram into relational schemas.

7. Understand the Extended ER Model (EER) and advanced concepts.

Introduction to Data Modeling
Definition:
Data modeling is the process of creating a visual representation of a database structure that defines
how data is stored, organized, and manipulated.

It serves as a blueprint for designing databases and ensures data consistency, integrity, and
efficiency.

1.1. Importance of Data Modeling

Why is Data Modeling Important?

Data modeling is crucial in database design because it:

1. Ensures Data Accuracy & Integrity

o Clearly defines relationships between entities.

o Avoids data redundancy and inconsistencies.

2. Improves Database Efficiency

o Helps in performance optimization by structuring data correctly.

3. Facilitates Better Communication

o Provides a common language for database designers, developers, and business

analysts.

o Avoids misinterpretation of data structure.

4. Reduces Development Costs & Time

o A well-designed model reduces errors in later stages of database implementation.

o Ensures scalability and maintainability.

Real-World Example of Data Modeling Importance

• E-commerce Application (Amazon, Flipkart, etc.)

o Without proper data modeling, customer orders may not link properly to products,
leading to data integrity issues.

o A well-structured model ensures:

▪ Customers are linked to valid orders.

▪ Products have correct inventory levels.

▪ Payments are processed securely.

1.2. Levels of Data Abstraction
Data modeling follows three levels of abstraction to separate data storage from how users perceive
it.
These levels help organize, structure, and manipulate data efficiently.

Explanation of Database Design Process (Figure 7.1) – Including Stakeholders at Each Level

This diagram represents the main phases of database design, showing how a real-world system
(Miniworld) is translated into a structured database. The design process is divided into several
phases, from requirements gathering to implementation, involving different stakeholders at each
stage.

1. Miniworld (Real-World Context)

What Happens?

• The miniworld represents the real-world domain that the database is designed for.
• This is where the system operates, such as a university, hospital, or e-commerce platform.

Who is Involved?

✔ Business Owners / Organization Leaders – Define the scope of the database.

✔ End Users (Students, Doctors, Customers, etc.) – Provide real-world use cases.

Example: A university database tracks students, courses, professors, and enrollments.

2. Requirements Collection and Analysis

What Happens?

• This is the first phase where database designers interact with stakeholders to understand:

o Data Requirements (e.g., What data should be stored?)

o Functional Requirements (e.g., What operations will be performed?)

Who is Involved?

✔ Business Analysts – Gather data and functional requirements.

✔ Stakeholders (Managers, End Users, Clients) – Define what data needs to be stored.
✔ Database Designers / System Analysts – Document requirements and create an initial design.

Example: In a university system, requirements could include:

• Students enroll in multiple courses.

• Professors teach courses.

• Grades are recorded for each student.

3. Functional Analysis

What Happens?

• Focuses on high-level transaction specifications (operations users will perform).

• Identifies common queries, updates, and reports needed.

Who is Involved?

✔ System Analysts / Business Analysts – Identify business transactions.

✔ Database Designers – Determine how the system should process and store data.
✔ End Users (Professors, Librarians, HR, etc.) – Provide functional insights.

Example:

• “Retrieve all courses a student is enrolled in.”

• “Update student grades for a specific course.”

4. Conceptual Design

What Happens?

• Converts data requirements into a Conceptual Schema.

• Uses high-level data models such as Entity-Relationship (ER) Diagrams.

• DBMS-Independent → Does not depend on a specific database system.

Who is Involved?

✔ Database Architects / Designers – Create ER models.

✔ System Analysts – Ensure business rules are accurately represented.
✔ Domain Experts – Validate correctness.

Example:

• Identifies entities (STUDENT, COURSE, PROFESSOR).

• Defines relationships (STUDENT ENROLLS_IN COURSE).

• Specifies attributes (Student_ID, Name, Course_ID).

5. Logical Design (Data Model Mapping)

What Happens?

• Maps the Conceptual Schema into a specific DBMS data model (e.g., Relational, NoSQL).

• Converts ER diagrams into relational tables.

Who is Involved?

✔ Database Designers – Convert ER models into relational schemas.

✔ Software Engineers / Developers – Work with database designers to ensure system compatibility.

Example:

• STUDENT(Student_ID, Name, Age)

• COURSE(Course_ID, Course_Name, Credits)

• ENROLLMENT(Student_ID, Course_ID, Grade)

6. Physical Design

What Happens?

• Focuses on how data is stored and optimized for performance.

• Defines:

o Internal Schema (storage structures, indexing).

o Optimization Strategies (partitioning, indexing, file organization).

Who is Involved?

✔ Database Administrators (DBAs) – Handle indexing, storage optimization, and performance

tuning.
✔ System Architects – Ensure hardware compatibility and efficiency.

Example:

• Indexing Student_ID for fast retrieval.

• Using B+ Trees for query optimization.

7. Application Program Design

What Happens?

• Involves designing the database application that interacts with users.

• Includes:

o User Interfaces

o Database Queries

o Stored Procedures

Who is Involved?

✔ Software Developers / Programmers – Develop database-driven applications.

✔ UI/UX Designers – Ensure a user-friendly interface for interacting with the database.
✔ QA Testers – Validate database operations.

Example:

• A student portal that allows students to register for courses.

8. Transaction Implementation

What Happens?

• The final step where the database is implemented and runs real transactions.

• Application programs interact with the database to perform CRUD operations (Create, Read,
Update, Delete).

Who is Involved?

✔ Database Administrators (DBAs) – Maintain and monitor the database.

✔ System Administrators – Ensure uptime, security, and backups.
✔ Software Developers – Fix bugs and optimize database interactions.

Example:

• Student logs in → Registers for a course → Data is updated in ENROLLMENT table.

Key Takeaways

Phase Purpose Who is Involved?

Miniworld Defines the real-world domain. Business Owners, End Users

Identifies what data and operations Business Analysts, Stakeholders,

Requirements Analysis
are needed. System Analysts

Defines high-level transactions and System Analysts, Database Designers,

Functional Analysis
user needs. End Users

Creates a high-level ER model (DBMS- Database Architects, System

Conceptual Design
independent). Analysts, Domain Experts

Converts ER model to relational Database Designers, Software

Logical Design
schema (DBMS-specific). Engineers

Database Administrators (DBAs),

Physical Design Optimizes data storage and indexing.
System Architects

Application Program Software Developers, UI/UX

Develops user interfaces and queries.
Design Designers, QA Testers

Transaction Implements the database for real- DBAs, System Administrators,

Implementation world use. Developers

Conclusion

• This step-by-step process ensures a well-structured and optimized database.

• Helps in efficient query execution, scalability, and maintainability.

• Involves different stakeholders at each level to ensure a smooth transition from

requirements to implementation.

3.1 Conceptual Level (High-Level, Business View)

• What it is:

o Represents the overall database structure from a business perspective.

o Focuses on entities, attributes, and relationships without worrying about

implementation details.

• Who Uses It?

o Business analysts, database architects, stakeholders.

• Example:
o A University Database:

▪ Entities: Student, Course, Professor

▪ Relationships: Student ENROLLS_IN Course, Professor TEACHES Course

• Diagram Used: ER Diagram (Entity-Relationship Diagram)

Reference:
Elmasri & Navathe, Chapter 7: Conceptual Database Design

3.2 Logical Level (Implementation View)

• What it is:

o Converts the conceptual model into a logical structure suitable for implementation
in a specific database (e.g., MySQL, PostgreSQL).

o Defines tables, attributes, primary keys, foreign keys, and constraints.

• Who Uses It?

o Database designers and software developers.

• Example (University Database - Logical Model in SQL):

CREATE TABLE STUDENT (

Student_ID INT PRIMARY KEY,

Name VARCHAR(50),

Age INT

);

CREATE TABLE COURSE (

Course_ID INT PRIMARY KEY,

Course_Name VARCHAR(100)

);

CREATE TABLE ENROLLMENT (

Student_ID INT,

Course_ID INT,

PRIMARY KEY (Student_ID, Course_ID),

FOREIGN KEY (Student_ID) REFERENCES STUDENT(Student_ID),

FOREIGN KEY (Course_ID) REFERENCES COURSE(Course_ID)

);

Reference:
Korth & Silberschatz, Chapter 6: Relational Model

3.3 Physical Level (Storage & Performance View)

• What it is:

o Defines how data is stored physically on disk.

o Includes indexing, partitions, storage optimization, access paths.

• Who Uses It?

o Database administrators (DBAs) and system architects.

• Example:

o Using B+ Trees for indexing student records for faster search.

o Partitioning large datasets to optimize query performance.

Reference:
Ramakrishnan & Gehrke, Chapter 9: Storage & File Organization

Summary of Levels of Data Abstraction

Abstraction
Definition Users Representation
Level

Conceptual Business-level design with entities and Business Analysts, DB

ER Diagrams
Model relationships Architects

Table structures, primary keys, foreign Database Designers, Relational

Logical Model
keys, constraints Developers Schema

Storage format, indexes, partitions, Data Files,

Physical Model DBAs, System Architects
performance tuning Indexes
1.3. The ER Model as a Conceptual Model
What is the ER Model?
• The Entity-Relationship (ER) Model is a high-level conceptual data model used to define
data elements and relationships in a system.

• It helps in database design by visually representing the structure of a database.

ER Model Components
1. Entities (Objects or Concepts)

o Example: STUDENT, COURSE, LIBRARY

2. Attributes (Properties of Entities)

o Example: STUDENT (Student_ID, Name, Age)

3. Relationships (Associations between Entities)

o Example: STUDENT ENROLLS_IN COURSE

ER Model Representation

Reference:
Elmasri & Navathe, Chapter 7: ER Model & Conceptual Design

A sample database application, called COMPANY

Scenario:
Suppose that after the requirements collection and analysis phase, the database designers provide
the following description of the miniworld—the part of the company that will be represented in the
database.

■ The company is organized into departments. Each department has a unique name, a unique
number, and a particular employee who manages the department.We keep track of the start date
when that employee began man aging the department. A department may have several locations.

■ A department controls a number of projects, each of which has a unique name, a unique number,
and a single location.

■ We store each employee’s name, Social Security number,2 address, salary, sex (gender), and birth
date. An employee is assigned to one department, but may work on several projects, which are not
necessarily controlled by the same department. We keep track of the current number of hours per
week that an employee works on each project. We also keep track of the direct supervisor of each
employee (who is another employee).

■ We want to keep track of the dependents of each employee for insurance purposes. We keep each
dependent’s first name, sex, birth date, and relation ship to the employee.

Figure 7.2 shows how the schema for this database application can be displayed by means of the
graphical notation known as ER diagrams
Explanation of the ER Diagram for the COMPANY Database (Figure 7.2)

This Entity-Relationship (ER) diagram represents the structure of a COMPANY Database, including
entities, attributes, relationships, and constraints. It captures essential components of an
organization, such as employees, departments, projects, and dependents.

Key Components of the ER Diagram

The diagram consists of:

1. Entities (Rectangles)

2. Attributes (Ovals)

3. Relationships (Diamonds)

4. Cardinality Constraints (1:1, 1:M, M:N)

5. Weak Entities (Double Rectangles)

6. Derived Attributes (Dashed Ovals)

1. Entities and Their Attributes

a) EMPLOYEE (Strong Entity)

• Represents an employee in the company.

• Attributes (Ovals):

o Fname, Minit, Lname (First, Middle, Last Name)

o Bdate (Birth Date)

o Address

o Salary

o Sex

o Ssn (Social Security Number) → Primary Key

• Relationships:

o WORKS_FOR (with DEPARTMENT)

o MANAGES (with DEPARTMENT)

o WORKS_ON (with PROJECT)

o SUPERVISION (self-referential)

o DEPENDENTS_OF (with DEPENDENT)

b) DEPARTMENT (Strong Entity)

• Represents company departments.

• Attributes:

o Name

o Number (Department Number) → Primary Key

o Locations (Multivalued Attribute)

• Relationships:

o WORKS_FOR (with EMPLOYEE)

o MANAGES (with EMPLOYEE)

o CONTROLS (with PROJECT)

c) PROJECT (Strong Entity)

• Represents projects undertaken by the company.

• Attributes:

o Name

o Number (Project Number) → Primary Key

o Location

• Relationships:

o WORKS_ON (with EMPLOYEE)

o CONTROLS (with DEPARTMENT)

d) DEPENDENT (Weak Entity)

• Represents dependents of employees.

• Attributes:

o Name

o Sex

o Birth_date

o Relationship

• Relationship:

o DEPENDENTS_OF (with EMPLOYEE)

• Primary Key: Composite Key (Employee Ssn + Dependent Name)

o A weak entity does not have its own primary key.

o It depends on the EMPLOYEE entity.

2. Relationships and Their Constraints

a) WORKS_FOR (1:N Relationship)

• Meaning: Each employee works for one department, but a department can have many
employees.

• Attributes:

o Start_date (Date employee joined the department)

o Number_of_employees (Derived Attribute)

Cardinality:

• One department (1) → Many employees (N)

• Foreign Key: Dept_ID in EMPLOYEE references DEPARTMENT(Number)

b) MANAGES (1:1 Relationship)

• Meaning: Each department is managed by one employee, and an employee can manage
only one department.

• Cardinality: 1:1

• Foreign Key: Manager_ID in DEPARTMENT references EMPLOYEE(Ssn)

c) SUPERVISION (1:N Relationship - Recursive Relationship)

• Meaning: An employee (supervisor) can supervise multiple employees, but each employee
has only one supervisor.

• Roles: Supervisor (1), Supervisee (N)

Cardinality:

• One employee (1) → Supervises multiple employees (N)

• Foreign Key: Supervisor_ID in EMPLOYEE references EMPLOYEE(Ssn)

d) DEPENDENTS_OF (1:N Relationship)

• Meaning: Each employee can have multiple dependents, but each dependent is linked to
only one employee.

• Foreign Key: Emp_Ssn in DEPENDENT references EMPLOYEE(Ssn)

Cardinality:
• One employee (1) → Many dependents (N)

e) WORKS_ON (M:N Relationship)

• Meaning: An employee can work on multiple projects, and a project can have multiple
employees working on it.

• Attributes:

o Hours (Tracks the number of hours an employee spends on a project)

Cardinality:

• Many employees (M) → Work on Many projects (N)

• Foreign Keys:

o Emp_Ssn in WORKS_ON references EMPLOYEE(Ssn)

o Proj_Num in WORKS_ON references PROJECT(Number)

Solution: A junction table (WORKS_ON) is created with a composite primary key (Emp_Ssn,
Proj_Num).

f) CONTROLS (1:N Relationship)

• Meaning: A department controls multiple projects, but each project is controlled by one
department.

Cardinality:

• One department (1) → Controls many projects (N)

• Foreign Key: Dept_ID in PROJECT references DEPARTMENT(Number)

3. Cardinality Summary

Relationship Entities Involved Cardinality Foreign Key Placement

WORKS_FOR EMPLOYEE → DEPARTMENT 1:N Dept_ID in EMPLOYEE

MANAGES EMPLOYEE → DEPARTMENT 1:1 Manager_ID in DEPARTMENT

SUPERVISION EMPLOYEE → EMPLOYEE 1:N Supervisor_ID in EMPLOYEE

WORKS_ON EMPLOYEE → PROJECT M:N Emp_Ssn, Proj_Num in WORKS_ON

CONTROLS DEPARTMENT → PROJECT 1:N Dept_ID in PROJECT

DEPENDENTS_OF EMPLOYEE → DEPENDENT 1:N Emp_Ssn in DEPENDENT

4. Special Features in This ER Model

✔ Weak Entity (DEPENDENT): Requires an EMPLOYEE for identification.

✔ Recursive Relationship (SUPERVISION): EMPLOYEE supervises other EMPLOYEEs.
✔ Derived Attribute (Number_of_employees): Can be computed dynamically.
✔ Multi-Valued Attribute (Locations in DEPARTMENT): A department can be at multiple locations.
✔ Many-to-Many Relationship (WORKS_ON): Needs an intermediate table.

5. How This ER Model Converts to Relational Schema

• Entities become Tables:

CREATE TABLE EMPLOYEE (

Ssn INT PRIMARY KEY,

Fname VARCHAR(50),

Lname VARCHAR(50),

Minit CHAR(1),

Bdate DATE,

Address VARCHAR(100),

Salary DECIMAL(10,2),

Sex CHAR(1),

Dept_ID INT,

FOREIGN KEY (Dept_ID) REFERENCES DEPARTMENT(Number)

);

• Relationships with Foreign Keys:

CREATE TABLE WORKS_ON (

Emp_Ssn INT,

Proj_Num INT,

Hours DECIMAL(5,2),

PRIMARY KEY (Emp_Ssn, Proj_Num),

FOREIGN KEY (Emp_Ssn) REFERENCES EMPLOYEE(Ssn),

FOREIGN KEY (Proj_Num) REFERENCES PROJECT(Number)

);

6. Summary
• EMPLOYEE is the central entity with multiple relationships.

• WORKS_ON (M:N) Relationship represents many employees working on multiple projects.

• SUPERVISION is a recursive relationship (employees supervising other employees).

• DEPENDENT is a weak entity that depends on EMPLOYEE.

• Derived attributes like Number_of_employees are computed, not stored.

• Multi-valued attributes (e.g., Locations) need a separate table.

5. Summary

Data modeling helps in structuring and organizing data before database implementation.
Three levels of abstraction (Conceptual, Logical, Physical) ensure scalability and efficiency.
The ER Model is the foundation for relational database design.
ER diagrams help in visualizing real-world entities and their relationships.
Components of ER Model

Entities and Entity Types

1 Definition of an Entity

• An entity is a real-world object or concept that is distinguishable from other objects.

• Each entity has a set of attributes that define its properties.

• Example: A student, car, employee, product, etc.

2 Types of Entities

Entities are classified into two types:

1. Strong Entities:

o Can exist independently in the database.

o Have a primary key (unique identifier).

o Represented by a single rectangle in an ER diagram.

o Example:

▪ EMPLOYEE (Emp_ID, Name, Salary, Department)

▪ DEPARTMENT (Dept_ID, Dept_Name)

2. Weak Entities:

o Cannot exist independently and depend on a strong entity.

o Do not have a primary key of their own.

o Identified by a partial key and related to a strong entity using a foreign key.

o Represented by a double rectangle in an ER diagram.

o Example:

▪ DEPENDENT (Dependent_Name, Relationship, Birth_Date, Employee_ID)

▪ A dependent (e.g., child or spouse) exists only if an employee exists.

3 Entity Set

• An entity set is a collection of similar entities.

• Example: The set of all students in a university forms a STUDENT entity set.

Attributes in the ER Model

1 Definition of an Attribute

• An attribute is a characteristic or property that describes an entity.

• Example: The STUDENT entity has attributes: Student_ID, Name, Age, Major.

2 Types of Attributes

Attributes can be classified into four types:

1. Simple (Atomic) Attributes

• Definition: Cannot be further divided into sub-parts.

• Example:

o Age (Numeric Value)

o Salary (Monetary Value)

o Phone_Number

2. Composite Attributes

• Definition: Can be divided into smaller sub-parts.

• Example:

o Full_Name → {First_Name, Middle_Name, Last_Name}

o Address → {Street, City, State, Country}

3. Derived Attributes

• Definition: Calculated from existing attributes.

• Example:

o Age (Derived from Date_of_Birth)

o Number_of_Employees (Derived from Employee records in a department)

4. Multi-Valued Attributes

• Definition: Can have multiple values for a single entity.

• Example:

o Phone_Numbers (An employee may have multiple phone numbers)

o Skills (A job applicant may have multiple skills)

Comparison of Attribute Types

Attribute Diagram
Definition Example
Type Representation

Simple Cannot be divided Age, Salary Oval

Full_Name → {First, Middle,

Composite Can be divided Connected Ovals
Last}

Derived Calculated from other Age (from Birth_Date) Dashed Oval

Attribute Diagram
Definition Example
Type Representation

attributes

Multi-Valued Can have multiple values Phone_Numbers, Skills Double Oval

Relationships in the ER Model

1 Definition of a Relationship

• A relationship is an association between two or more entities.

• Example:

o EMPLOYEE works for DEPARTMENT

o STUDENT enrolls in COURSE

2 Relationship Types

Relationships are classified based on cardinality constraints:

1. One-to-One (1:1)

• Definition: One entity is related to at most one other entity.

• Example:

o A department is managed by one manager, and a manager manages only one

department.

2. One-to-Many (1:M)

• Definition: One entity is related to multiple entities, but each related entity is linked to only
one entity.

• Example:

o A department can have multiple employees, but each employee belongs to only
one department.

3. Many-to-Many (M:N)

• Definition: Multiple entities are related to multiple other entities.

• Example:

o A student can enroll in multiple courses, and each course can have multiple
students.

4 Attributes in Relationships

• A relationship can have its own attributes.

• Example:
o WORKS_ON relationship between EMPLOYEE and PROJECT has an attribute Hours
(number of hours worked).
Mapping Constraints
1. What are Mapping Constraints?

• Mapping constraints define the number of entities in one entity set that can be associated
with another entity set in a relationship.

• They describe how entities are related to each other in terms of cardinality.

• Types of Mapping Constraints:

o One-to-One (1:1)

o One-to-Many (1:M)

o Many-to-Many (M:N)

2. Why are Mapping Constraints Important?

✔ Prevent data inconsistencies in relationships.

✔ Help in database normalization and avoid redundancy.
✔ Ensure efficient storage and retrieval of data.
✔ Provide clear rules for converting the ER model into a relational schema.

3. Types of Mapping Constraints in the Company Database

1. One-to-One (1:1) Relationship in the Company Database

✔ Definition:

• A single instance of Entity A is related to a single instance of Entity B.

• Example:

o Each department is managed by one employee, and each employee manages only
one department.

o Relationship: MANAGES (Between EMPLOYEE and DEPARTMENT).

✔ Relational Schema Implementation:

CREATE TABLE DEPARTMENT (

Dept_ID INT PRIMARY KEY,

Dept_Name VARCHAR(50),

Manager_ID INT UNIQUE,

FOREIGN KEY (Manager_ID) REFERENCES EMPLOYEE(Emp_ID)

);

• Manager_ID is unique, ensuring each department has only one manager.

• Foreign key (Manager_ID) references EMPLOYEE(Emp_ID).

✔ Real-World Example in the Company Database:

• Each CEO leads only one company.

2. One-to-Many (1:M) Relationship in the Company Database

✔ Definition:

• A single instance of Entity A can be associated with multiple instances of Entity B, but each
instance of B is related to only one instance of A.

• Example:

o A department can have multiple employees, but each employee belongs to only
one department.

o Relationship: WORKS_FOR (Between EMPLOYEE and DEPARTMENT).

✔ Relational Schema Implementation:

CREATE TABLE EMPLOYEE (

Emp_ID INT PRIMARY KEY,

Name VARCHAR(50),

Dept_ID INT,

FOREIGN KEY (Dept_ID) REFERENCES DEPARTMENT(Dept_ID)

);

• Dept_ID in EMPLOYEE is a foreign key referencing DEPARTMENT(Dept_ID), ensuring each

employee is assigned to one department.

✔ Real-World Example in the Company Database:

• A company has multiple offices, but each office belongs to one company.

3. Many-to-Many (M:N) Relationship in the Company Database

✔ Definition:

• A single instance of Entity A can be associated with multiple instances of Entity B, and vice
versa.

• Example:

o Employees can work on multiple projects, and each project can have multiple
employees.

o Relationship: WORKS_ON (Between EMPLOYEE and PROJECT).

✔ Solution:
• A junction table (WORKS_ON) is created to handle M:N relationships.

✔ Relational Schema Implementation:

CREATE TABLE WORKS_ON (

Emp_ID INT,

Proj_ID INT,

Hours DECIMAL(5,2),

PRIMARY KEY (Emp_ID, Proj_ID),

FOREIGN KEY (Emp_ID) REFERENCES EMPLOYEE(Emp_ID),

FOREIGN KEY (Proj_ID) REFERENCES PROJECT(Proj_ID)

);

• The WORKS_ON table resolves the M:N relationship between EMPLOYEE and PROJECT.

✔ Real-World Example in the Company Database:

• Employees can be assigned to multiple teams, and each team has multiple employees.

4. Mapping Constraints in Participation (Total vs Partial Participation in the Company

Database)

4.1 Total Participation (Mandatory Participation)

✔ Definition:

• Every instance of an entity must be associated with at least one instance of another entity.

• Represented with a double line in ER diagrams.

• Example:

o Every employee must work for at least one department.

o Relationship: WORKS_FOR (Between EMPLOYEE and DEPARTMENT).

✔ Relational Schema Implementation:

ALTER TABLE EMPLOYEE

ADD CONSTRAINT WorksFor CHECK (Dept_ID IS NOT NULL);

• The CHECK constraint ensures every employee is assigned a department.

✔ Real-World Example in the Company Database:

• Every project must be controlled by at least one department.

4.2 Partial Participation (Optional Participation)

✔ Definition:

• Some instances of an entity may not be associated with any instance of another entity.

• Represented with a single line in ER diagrams.

• Example:

o Some employees may not be assigned to a project.

o Relationship: WORKS_ON (Between EMPLOYEE and PROJECT).

✔ Real-World Example in the Company Database:

• Not all employees are assigned to a project immediately after hiring.

4. Summary of Mapping Constraints in the Company Database

ER Diagram
Constraint Type Definition Example in Company DB
Representation

One entity maps to only A manager manages one

One-to-One (1:1) 1 ---- 1
one entity department

One-to-Many One entity maps to many A department has many

1 ---- M
(1:M) entities employees

Many-to-Many Many entities map to Employees work on multiple

M ---- N
(M:N) many entities projects

Total Every instance must Every employee must work for

Double line
Participation participate a department

Partial Some instances do not Some employees don’t work on

Single line
Participation participate projects
Keys in the ER Model
1. Introduction to Keys in the ER Model
Keys in an Entity-Relationship (ER) Model are used to uniquely identify an entity instance. They play
a crucial role in maintaining data integrity and avoiding duplication in a database.

• A key is an attribute (or set of attributes) that uniquely identifies an entity in an entity set.

• Types of keys in the ER Model:

o Super Key

o Candidate Key

o Primary Key

o Foreign Key

o Composite Key

o Alternate Key

o Partial Key (Discriminator Key) (used in weak entities)

2. Types of Keys in the ER Model

2.1 Super Key

✔ Definition:

• A super key is a set of one or more attributes that uniquely identifies an entity in an entity
set.

• It may contain extra attributes that are not necessary for uniqueness.

✔ Example (Company Database - EMPLOYEE Table):

Emp_ID Name Email Phone Dept_ID

101 Alice [email protected] 9876543210 D1

102 Bob [email protected] 9876543211 D2

103 Charlie [email protected] 9876543212 D1

• Possible super keys:

o {Emp_ID} → Uniquely identifies each employee.

o {Emp_ID, Email} → Still unique but contains an extra attribute.

o {Emp_ID, Phone, Dept_ID} → Also unique but redundant.

✔ General Rule:

• Super keys may contain extra attributes, but they still uniquely identify an entity.
2.2 Candidate Key

✔ Definition:

• A candidate key is a minimal super key.

• It contains the minimum number of attributes needed to uniquely identify an entity.

• Every candidate key is a super key, but not every super key is a candidate key.

✔ Example (Company Database - EMPLOYEE Table):

• Possible candidate keys:

o {Emp_ID} → Minimum unique key.

o {Email} → Minimum unique key.

o {Phone} → Minimum unique key.

✔ Properties of Candidate Keys:

• A relation can have multiple candidate keys.

• They must be unique and minimal.

2.3 Primary Key

✔ Definition:

• A primary key is one of the candidate keys chosen to uniquely identify records in a table.

• A database allows only one primary key per table.

✔ Example (Company Database - EMPLOYEE Table):

Emp_ID (Primary Key) Name Email Phone

101 Alice [email protected] 9876543210

102 Bob [email protected] 9876543211

• Candidate keys: {Emp_ID}, {Email}, {Phone}

• Primary key chosen: {Emp_ID}

✔ SQL Implementation:

CREATE TABLE EMPLOYEE (

Emp_ID INT PRIMARY KEY,

Name VARCHAR(50),

Email VARCHAR(50) UNIQUE,

Phone VARCHAR(15) UNIQUE

);

✔ Primary Key Rules:

1. Uniqueness – No two rows can have the same primary key value.

2. Non-nullability – A primary key cannot have a NULL value.

2.4 Foreign Key

✔ Definition:

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

• It establishes relationships between two entities.

✔ Example (Company Database - Employee-Department Relationship):

• Each employee belongs to a department.

• Dept_ID in EMPLOYEE is a foreign key referencing the DEPARTMENT table.

✔ SQL Implementation:

CREATE TABLE DEPARTMENT (

Dept_ID INT PRIMARY KEY,

Dept_Name VARCHAR(50)

);

CREATE TABLE EMPLOYEE (

Emp_ID INT PRIMARY KEY,

Name VARCHAR(50),

Dept_ID INT,

FOREIGN KEY (Dept_ID) REFERENCES DEPARTMENT(Dept_ID)

);

• Dept_ID in EMPLOYEE references Dept_ID in DEPARTMENT.

2.5 Composite Key

✔ Definition:

• A composite key is a primary key made up of multiple attributes.

• Used in many-to-many relationships.

✔ Example (Company Database - WORKS_ON Relationship):

• An employee can work on multiple projects.

• A project can have multiple employees.

• Primary Key of WORKS_ON: {Emp_ID, Proj_ID}.

✔ SQL Implementation:

CREATE TABLE WORKS_ON (

Emp_ID INT,

Proj_ID INT,

Hours DECIMAL(5,2),

PRIMARY KEY (Emp_ID, Proj_ID),

FOREIGN KEY (Emp_ID) REFERENCES EMPLOYEE(Emp_ID),

FOREIGN KEY (Proj_ID) REFERENCES PROJECT(Proj_ID)

);

2.6 Alternate Key

✔ Definition:

• A candidate key that is not chosen as the primary key.

• Example: If Emp_ID is the primary key, then Email and Phone are alternate keys.

✔ Example (Company Database - EMPLOYEE Table):

Emp_ID (Primary Key) Email (Alternate Key) Phone (Alternate Key)

101 [email protected] 9876543210

102 [email protected] 9876543211

2.7 Partial Key (Discriminator Key) – For Weak Entities

✔ Definition:

• Used only in weak entities that do not have a primary key.

• Uniquely identifies a weak entity relative to its strong entity.

✔ Example (Company Database - DEPENDENT Table):

• DEPENDENT is a weak entity.

• It does not have a unique ID, so it uses Dependent_Name along with Emp_ID.
✔ SQL Implementation:

CREATE TABLE DEPENDENT (

Dependent_Name VARCHAR(50),

Emp_ID INT,

Relationship VARCHAR(50),

PRIMARY KEY (Dependent_Name, Emp_ID),

FOREIGN KEY (Emp_ID) REFERENCES EMPLOYEE(Emp_ID)

);

3. Summary Table

Key Type Definition Example

Super Key Set of attributes that uniquely identify an entity {Emp_ID, Email, Phone}

Candidate Key Minimal super key {Emp_ID}, {Email}

Primary Key Chosen candidate key {Emp_ID}

Foreign Key References another table Dept_ID in EMPLOYEE

Composite Key Multiple attributes forming a key {Emp_ID, Proj_ID}

Alternate Key Candidate key not chosen as primary {Email}, {Phone}

Partial Key Used for weak entities {Dependent_Name, Emp_ID}

Generalisation and Specialisation

1. Introduction to Generalization & Specialization
Generalization and Specialization are two fundamental concepts in the Entity-Relationship (ER)
Model that help in designing a hierarchical structure of entities based on shared attributes and
unique characteristics.

• Generalization: The process of combining multiple lower-level entities into a higher-level

entity based on common features.

• Specialization: The process of dividing a higher-level entity into two or more lower-level
entities based on unique characteristics.

These concepts are essential for hierarchical modeling, improving database efficiency, minimizing
redundancy, and ensuring better data organization.
2. Generalization in the ER Model
2.1 Definition of Generalization

✔ Generalization is a bottom-up process in which multiple specific entities (subclasses) are merged
into a single higher-level entity (superclass).
✔ It abstracts common attributes and relationships into a generalized entity.
✔ Purpose:

• Reduce redundancy.

• Group entities with common features.

• Create a hierarchical structure.

2.2 Example of Generalization (Company Database)

In a company, there are different types of employees such as:

• Software Engineers

• HR Managers

• Sales Representatives

Each type of employee has unique attributes, but they also share common attributes such as
Emp_ID, Name, Salary.

✔ Generalization Process:

1. Entities Before Generalization:

o Software_Engineer(Emp_ID, Name, Salary, Programming_Languages)

o HR_Manager(Emp_ID, Name, Salary, Employee_Benefits)

o Sales_Rep(Emp_ID, Name, Salary, Sales_Region)

2. After Generalization (Super Entity - EMPLOYEE):

o EMPLOYEE(Emp_ID, Name, Salary)

o Subclasses:

▪ Software_Engineer(Programming_Languages)

▪ HR_Manager(Employee_Benefits)

▪ Sales_Rep(Sales_Region)

Relational Schema Implementation:

CREATE TABLE EMPLOYEE (

Emp_ID INT PRIMARY KEY,

Name VARCHAR(50),

Salary DECIMAL(10,2)
);

CREATE TABLE SOFTWARE_ENGINEER (

Emp_ID INT PRIMARY KEY,

Programming_Languages VARCHAR(100),

FOREIGN KEY (Emp_ID) REFERENCES EMPLOYEE(Emp_ID)

);

CREATE TABLE HR_MANAGER (

Emp_ID INT PRIMARY KEY,

Employee_Benefits VARCHAR(100),

FOREIGN KEY (Emp_ID) REFERENCES EMPLOYEE(Emp_ID)

);

CREATE TABLE SALES_REP (

Emp_ID INT PRIMARY KEY,

Sales_Region VARCHAR(50),

FOREIGN KEY (Emp_ID) REFERENCES EMPLOYEE(Emp_ID)

);

✔ Benefits of Generalization:

• Reduces redundancy (common attributes are stored in the superclass).

• Enhances reusability (new subtypes can be added easily).

• Improves database design (better organization of related data).

3. Specialization in the ER Model

3.1 Definition of Specialization

✔ Specialization is a top-down process where a single higher-level entity is divided into multiple
specialized lower-level entities based on unique characteristics.
✔ Purpose:

• Identify distinct subgroups within an entity.

• Improve data integrity by storing only relevant attributes for each entity.

3.2 Example of Specialization (Company Database)

In a company, all employees share some common attributes (e.g., Emp_ID, Name, Salary). However,
they may be classified into different specialized roles:

• Manager (Has Department_Managed attribute)

• Engineer (Has Technical_Skill attribute)

• Intern (Has Internship_Duration attribute)

✔ Specialization Process:

Before Specialization (Single Entity - EMPLOYEE):

• EMPLOYEE(Emp_ID, Name, Salary, Job_Title, Department, Technical_Skill,

Internship_Duration)

After Specialization (Sub-Entities - MANAGER, ENGINEER, INTERN):

• EMPLOYEE(Emp_ID, Name, Salary)

• Subclasses:

o Manager(Department_Managed)

o Engineer(Technical_Skill)

o Intern(Internship_Duration)

✔ Relational Schema Implementation:

CREATE TABLE EMPLOYEE (

Emp_ID INT PRIMARY KEY,

Name VARCHAR(50),

Salary DECIMAL(10,2)

);

CREATE TABLE MANAGER (

Emp_ID INT PRIMARY KEY,

Department_Managed VARCHAR(100),

FOREIGN KEY (Emp_ID) REFERENCES EMPLOYEE(Emp_ID)

);

CREATE TABLE ENGINEER (

Emp_ID INT PRIMARY KEY,

Technical_Skill VARCHAR(100),

FOREIGN KEY (Emp_ID) REFERENCES EMPLOYEE(Emp_ID)

);

CREATE TABLE INTERN (

Emp_ID INT PRIMARY KEY,

Internship_Duration INT,

FOREIGN KEY (Emp_ID) REFERENCES EMPLOYEE(Emp_ID)

);

✔ Benefits of Specialization:

• Avoids storing unnecessary attributes for certain entities.

• Enhances query performance by organizing data efficiently.

• Increases flexibility for adding specialized sub-entities.

4. Generalization vs. Specialization (Comparison Table)

Feature Generalization Specialization

Splitting a higher-level entity into

Merging multiple lower-level entities into
Definition multiple specialized lower-level
one higher-level entity
entities

Process Bottom-up Top-down

Reduce redundancy and store shared Create sub-entities based on unique

Purpose
attributes in a common entity characteristics

ER Diagram
Subclasses merge into a superclass A superclass splits into subclasses
Representation

Combining Software_Engineer,
Dividing EMPLOYEE into Manager,
Example HR_Manager, and Sales_Rep into
Engineer, and Intern
EMPLOYEE

5. Constraints in Generalization & Specialization

5.1 Disjoint Constraint

✔ Definition: Determines whether an entity instance can belong to multiple subclasses.

✔ Types:

1. Disjoint Specialization: An entity can belong to only one subclass.

o Example: An employee can either be a MANAGER or an ENGINEER, but not both.

o Representation: D symbol in ER diagram.

2. Overlapping Specialization: An entity can belong to multiple subclasses.

o Example: An employee can be both a MANAGER and an ENGINEER.

5.2 Completeness Constraint

✔ Definition: Specifies whether all higher-level entities must be included in some subclass.
✔ Types:

1. Total Specialization: Every entity must belong to at least one subclass.

o Example: Every employee must be either a MANAGER, ENGINEER, or INTERN.

o Representation: Double line from superclass.

2. Partial Specialization: Some entities may not belong to any subclass.

o Example: Some employees are general staff with no specific subclass.

6. Summary

Feature Generalization Specialization

Direction Bottom-up Top-down

Goal Reduce redundancy Organize data into categories

Combining Software_Engineer, HR_Manager, Splitting EMPLOYEE into Manager,

Example
Sales_Rep into EMPLOYEE Engineer, Intern

Constraints Overlapping, Disjoint Total, Partial

University Database Example Using EER Model

• The University Database keeps track of:

o Students and their majors, transcripts, and registration.

o The university’s course offerings.

o Faculty and their sponsored research projects.

• The EER schema is shown in Figure 8.9.

Entities and Attributes

• PERSON:

o Attributes: Name, Ssn, Address, Sex, Bdate.

o Subclasses: FACULTY and STUDENT.

• FACULTY:

o Attributes: Rank, Foffice, Fphone, Salary.

o Relationships:

▪ BELONGS to DEPARTMENT (M:N).

▪ CHAIRS a DEPARTMENT (1:1).

• STUDENT:

o Attributes: Class (1=Freshman, 5=Graduate Student).

o Relationships:

▪ MAJOR, MINOR in DEPARTMENT (1:N).

▪ REGISTERED in SECTION (M:N).

▪ TRANSCRIPT stores course grades.

• GRAD_STUDENT (Subclass of STUDENT):

o Predicate: Class = 5.

o Attributes: Degrees.

o Relationships:

▪ ADVISOR (1:M with FACULTY).

▪ COMMITTEE (M:N with FACULTY).

• DEPARTMENT:

o Attributes: Dname, Office, Dphone.

o Relationships:

▪ BELONGS to COLLEGE (1:N).

▪ CHAIRS FACULTY (1:1).

• COLLEGE:

o Attributes: Cname, Coffice, Dean.

• COURSE:

o Attributes: C#, Cname, Cdesc.

• SECTION:

o Attributes: Sec#, Year, Qtr.

o CURRENT_SECTION is a subclass with predicate: Qtr = Current_qtr AND Year =

Current_year.

• INSTRUCTOR_RESEARCHER:
o Union category of FACULTY and GRAD_STUDENT.

o Represents individuals involved in teaching or research.

• GRANT:

o Attributes: Title, No, Agency, St_date.

o Relationships:

▪ PI (Principal Investigator).

▪ SUPPORT (FACULTY or GRAD_STUDENT), with attributes Start, End, Time.

Solution: Design Choices for Specialization/Generalization

• Guidelines for Conceptual Design:

1. Too many specializations can clutter the schema.

2. Subclasses with few attributes should be merged into the superclass.

3. Union categories should be avoided unless necessary.

4. Use disjoint/overlapping constraints based on miniworld rules.

Aggregation in ER models
1. Introduction to Aggregation in ER Models
1.1 What is Aggregation in ER Models?

• Aggregation is a higher-level abstraction used when a relationship itself needs to be treated

as an entity.

• It allows modeling of complex relationships where an association between two entities acts
as a higher-level entity participating in another relationship.

• Used when a relationship has attributes or when a relationship needs to be linked to

another entity.

1.2 Why is Aggregation Important?

✔ Represents real-world scenarios more accurately.

✔ Reduces redundancy and maintains a cleaner ER diagram.
✔ Allows relationships to have attributes.
✔ Enables better organization of complex database structures.

2. Example: Aggregation in a Company Database

2.1 Scenario Without Aggregation

• Consider a company where employees work on projects, and the company keeps track of
the managers supervising each project.

• Entities:

o EMPLOYEE(Emp_ID, Name, Salary)

o PROJECT(Proj_ID, Proj_Name, Budget)

o Relationship: WORKS_ON(Emp_ID, Proj_ID, Hours_Worked)

o Relationship: MANAGES(Manager_ID, Proj_ID)

✔ Problem:

• If we want to track which employee is managed by whom on a project, a direct connection

between WORKS_ON and MANAGES is missing.

2.2 Solution Using Aggregation

• Treat WORKS_ON as an entity and link it to MANAGES.

• This allows us to store additional attributes (e.g., review score of employee by the manager
for that project).

✔ Relational Schema Implementation:

CREATE TABLE EMPLOYEE (

Emp_ID INT PRIMARY KEY,

Name VARCHAR(50),

Salary DECIMAL(10,2)

);

CREATE TABLE PROJECT (

Proj_ID INT PRIMARY KEY,

Proj_Name VARCHAR(100),

Budget DECIMAL(10,2)

);

CREATE TABLE WORKS_ON (

Emp_ID INT,

Proj_ID INT,

Hours_Worked DECIMAL(5,2),

PRIMARY KEY (Emp_ID, Proj_ID),

FOREIGN KEY (Emp_ID) REFERENCES EMPLOYEE(Emp_ID),

FOREIGN KEY (Proj_ID) REFERENCES PROJECT(Proj_ID)

);

CREATE TABLE MANAGES (

Manager_ID INT,

Emp_ID INT,

Proj_ID INT,

Review_Score DECIMAL(3,2),

PRIMARY KEY (Manager_ID, Emp_ID, Proj_ID),

FOREIGN KEY (Manager_ID) REFERENCES EMPLOYEE(Emp_ID),

FOREIGN KEY (Emp_ID, Proj_ID) REFERENCES WORKS_ON(Emp_ID, Proj_ID)

);

✔ Benefits of Aggregation Here:

• WORKS_ON is now treated as an entity to connect EMPLOYEE and PROJECT.

• MANAGES now associates a manager with employees in a specific project.

• Allows additional attributes (Review_Score) to be added to MANAGES.

3. Key Characteristics of Aggregation

✔ Allows Relationships to Have Attributes

• Relationships like WORKS_ON can have attributes (e.g., Hours_Worked).

✔ Reduces Complexity in ER Diagrams

• Instead of multiple separate relationships, a relationship can be represented as an entity.

✔ Enables Multi-Stage Relationships

• Helps in multi-step dependencies, such as an employee working on a project under a

specific manager.
✔ Used When Relationships Need to be Related to Other Entities

• Example: SUPERVISION relationship between an employee and a manager within a project.

4. Aggregation vs. Generalization vs. Specialization

Concept Definition Example

Treating a relationship as an entity to be used in WORKS_ON treated as an entity

Aggregation
another relationship related to MANAGES

Combining multiple lower-level entities into a

Generalization CAR and TRUCK → VEHICLE
higher-level entity

Dividing a higher-level entity into multiple sub- EMPLOYEE → MANAGER and

Specialization
entities based on attributes ENGINEER
Convert ER Diagrams to Relational Schema
1. Introduction: Why Convert ER Diagrams to Relational Schema?
An Entity-Relationship (ER) Model provides a high-level conceptual design of a database, while a
relational schema is the implementation structure used in relational databases.

Why Convert ER to Relational Schema?

✔ Enables database implementation in a DBMS like MySQL, PostgreSQL.
✔ Ensures data integrity and efficient storage.
✔ Establishes relationships and constraints in a structured format.

Steps in Conversion Process:

1. Convert entities into tables.

2. Convert relationships into foreign keys or separate tables.

3. Convert attributes into columns.

4. Handle weak entities and generalization/specialization.

5. Implement constraints (primary key, foreign key, referential integrity).

2. Steps for ER-to-Relational Conversion

Step 1: Convert Entities into Tables
1.1 Understanding Entities

Each entity in the ER model becomes a table in the relational model.

Each attribute of the entity becomes a column in the table.

1.2 Process

1. Create a table for each strong entity.

2. Identify the primary key.

3. Convert each attribute into a column.

1.3 Example

Relational Schema (SQL):

CREATE TABLE EMPLOYEE (

Ssn INT PRIMARY KEY,

Name VARCHAR(50),

Address VARCHAR(100),

Salary DECIMAL(10,2),

Sex CHAR(1),
Birth_date DATE

);

Step 2: Convert Relationships to Foreign Keys

2.1 Understanding Relationships

A relationship in an ER diagram represents an association between two entities.

These are converted using foreign keys or junction tables.

2.2 Conversion Process

1. Identify relationship type:

o 1:1 → Add a foreign key in one of the tables.

o 1:M → Add the foreign key in the table representing the "many" side.

o M:N → Create a new table (junction table).

2.3 Example: One-to-Many (EMPLOYEE → DEPARTMENT)

Each employee works for one department, but a department has many employees (1:N).

Relational Schema (SQL):

ALTER TABLE EMPLOYEE ADD Dept_ID INT;

ALTER TABLE EMPLOYEE ADD FOREIGN KEY (Dept_ID) REFERENCES DEPARTMENT(Dept_ID);

✔ Each EMPLOYEE has a Dept_ID referencing DEPARTMENT.

2.4 Example: Many-to-Many (EMPLOYEE ↔ PROJECT)

Each employee can work on multiple projects, and each project can have multiple employees (M:N).

Relational Schema (SQL):

CREATE TABLE WORKS_ON (

Emp_Ssn INT,

Proj_ID INT,

Hours_Worked DECIMAL(5,2),

PRIMARY KEY (Emp_Ssn, Proj_ID),

FOREIGN KEY (Emp_Ssn) REFERENCES EMPLOYEE(Ssn),

FOREIGN KEY (Proj_ID) REFERENCES PROJECT(Proj_ID)

);

✔ Created a junction table (WORKS_ON) to handle the M:N relationship.

Step 3: Convert Weak Entities to Tables
3.1 Understanding Weak Entities

• Weak entities do not have a primary key of their own.

• They depend on a strong entity.

• Require a composite primary key (including the owner entity's primary key).

3.2 Example: DEPENDENT (Weak Entity)

A dependent cannot exist without an employee.

Relational Schema (SQL):

CREATE TABLE DEPENDENT (

Dependent_Name VARCHAR(50),

Emp_Ssn INT,

Relationship VARCHAR(50),

PRIMARY KEY (Dependent_Name, Emp_Ssn),

FOREIGN KEY (Emp_Ssn) REFERENCES EMPLOYEE(Ssn) ON DELETE CASCADE

);

✔ If an employee is deleted, all their dependents are also deleted (ON DELETE CASCADE).

Step 4: Convert Multivalued Attributes

4.1 Understanding Multivalued Attributes

Some attributes may have multiple values for a single entity.

✔ Solution: Create a separate table.

4.2 Example: DEPARTMENT Locations

Each department can have multiple locations.

Relational Schema (SQL):

CREATE TABLE DEPARTMENT_LOCATIONS (

Dept_ID INT,

Location VARCHAR(100),

PRIMARY KEY (Dept_ID, Location),

FOREIGN KEY (Dept_ID) REFERENCES DEPARTMENT(Dept_ID)

);
✔ Each department can have multiple locations, stored separately.

Step 5: Convert Specialization and Generalization

5.1 Understanding Specialization & Generalization

• Specialization → Breaking a superclass into subclasses.

• Generalization → Combining multiple subclasses into a superclass.

5.2 Three Approaches for Conversion

Approach Method Example

Option 1: Separate Tables Each subclass has a table with a STUDENT(Ssn, Class),
(Default) foreign key to the superclass FACULTY(Ssn, Rank, Salary)

Option 2: Single Table for One table holds all attributes (NULL PERSON(Ssn, Name, Address, Sex,
Superclass & Subclasses values for missing attributes) Rank, Class, Salary)

Each subclass stores only its

Option 3: One Table per FACULTY(Ssn, Name, Salary),
attributes + Primary Key of
Subclass STUDENT(Ssn, Name, Class)
superclass

✔ Example SQL for Option 1 (Separate Tables)

CREATE TABLE PERSON (

Ssn INT PRIMARY KEY,

Name VARCHAR(50),

Address VARCHAR(100),

Sex CHAR(1),

Bdate DATE

);

CREATE TABLE FACULTY (

Ssn INT PRIMARY KEY,

Rank VARCHAR(20),

Salary DECIMAL(10,2),

FOREIGN KEY (Ssn) REFERENCES PERSON(Ssn)

);

✔ Keeps entities separate while maintaining relationships.

Step 6: Convert Aggregation
6.1 Understanding Aggregation

Sometimes, a relationship itself needs to be treated as an entity.

6.2 Example: EMPLOYEE Supervising WORKS_ON

To track which manager supervises an employee working on a project, WORKS_ON is aggregated.

Relational Schema (SQL):

sql

CopyEdit

CREATE TABLE MANAGES (

Manager_ID INT,

Emp_Ssn INT,

Proj_ID INT,

PRIMARY KEY (Manager_ID, Emp_Ssn, Proj_ID),

FOREIGN KEY (Manager_ID) REFERENCES EMPLOYEE(Ssn),

FOREIGN KEY (Emp_Ssn, Proj_ID) REFERENCES WORKS_ON(Emp_Ssn, Proj_ID)

);

✔ Allows storing manager relationships within the WORKS_ON entity.

7. Summary of ER-to-Relational Conversion

Step Action

1. Convert Entities Create tables for strong entities

2. Convert Relationships Use foreign keys or junction tables

3. Handle Weak Entities Use composite primary keys

4. Handle Multivalued Attributes Create separate tables

5. Convert Specialization/Generalization Use separate tables or a unified table

6. Convert Aggregation Treat relationships as entities

3. Advantages of This Conversion
Maintains Referential Integrity → Uses foreign keys to enforce relationships.
Efficient Query Processing → Indexes on primary keys improve speed.
Scalability → Allows adding new departments, employees, projects efficiently.
Data Integrity & Consistency → Cascade delete and update constraints maintain integrity.

Converting the COMPANY ER Diagram to a Relational Schema

1. Understanding the COMPANY ER Diagram

The ER Diagram represents an organizational structure with:
✔ Employees working for departments.
✔ Employees managing departments.
✔ Employees working on projects.
✔ Employees supervising other employees.
✔ Employees having dependents.
✔ Departments controlling projects.

Entities and Their Attributes

Entity Attributes

EMPLOYEE Ssn, Fname, Minit, Lname, Bdate, Address, Salary, Sex

DEPARTMENT Dept_ID, Name, Number_of_Employees, Locations

PROJECT Proj_ID, Name, Location

DEPENDENT Name, Sex, Birth_date, Relationship

Relationships in the ER Diagram

Relationship Entities Involved Type

WORKS_FOR EMPLOYEE → DEPARTMENT N:1

MANAGES EMPLOYEE → DEPARTMENT 1:1

WORKS_ON EMPLOYEE → PROJECT M:N

CONTROLS DEPARTMENT → PROJECT 1:N

SUPERVISION EMPLOYEE → EMPLOYEE 1:N

DEPENDENTS_OF EMPLOYEE → DEPENDENT 1:N

2. Step-by-Step Conversion to Relational Schema

Each entity and relationship from the ER model must be converted into a table.

Step 1: Convert Entities into Tables

Each entity in the ER diagram is converted into a table with its attributes.

1. EMPLOYEE Table

sql

CopyEdit

CREATE TABLE EMPLOYEE (

Ssn INT PRIMARY KEY,

Fname VARCHAR(30),

Minit CHAR(1),

Lname VARCHAR(30),

Bdate DATE,

Address VARCHAR(100),

Salary DECIMAL(10,2),

Sex CHAR(1)

);

2. DEPARTMENT Table

sql

CopyEdit

CREATE TABLE DEPARTMENT (

Dept_ID INT PRIMARY KEY,

Name VARCHAR(50),

Number_of_Employees INT

);

3. PROJECT Table

sql

CopyEdit

CREATE TABLE PROJECT (

Proj_ID INT PRIMARY KEY,

Name VARCHAR(50),

Location VARCHAR(100)

);

4. DEPENDENT Table

sql

CopyEdit

CREATE TABLE DEPENDENT (

Dependent_ID INT AUTO_INCREMENT PRIMARY KEY,

Emp_Ssn INT,
Name VARCHAR(50),

Sex CHAR(1),

Birth_date DATE,

Relationship VARCHAR(50),

FOREIGN KEY (Emp_Ssn) REFERENCES EMPLOYEE(Ssn) ON DELETE CASCADE

);

✔ Emp_Ssn as a foreign key links each dependent to an employee.

✔ ON DELETE CASCADE ensures dependents are removed if the employee is deleted.

Step 2: Convert Relationships into Foreign Keys

Relationships are implemented using foreign keys in relational tables.

1. WORKS_FOR (N:1)

Each employee works for one department.

sql

CopyEdit

ALTER TABLE EMPLOYEE ADD Dept_ID INT;

ALTER TABLE EMPLOYEE ADD FOREIGN KEY (Dept_ID) REFERENCES DEPARTMENT(Dept_ID);

✔ Each employee belongs to a single department.

2. MANAGES (1:1)

Each department is managed by one employee.

sql

CopyEdit

ALTER TABLE DEPARTMENT ADD Manager_Ssn INT UNIQUE;

ALTER TABLE DEPARTMENT ADD FOREIGN KEY (Manager_Ssn) REFERENCES EMPLOYEE(Ssn);

✔ UNIQUE ensures only one manager per department.

3. WORKS_ON (M:N)

An employee can work on multiple projects, and a project can have multiple employees.
A junction table is required.

sql

CopyEdit

CREATE TABLE WORKS_ON (

Emp_Ssn INT,

Proj_ID INT,

Hours_Worked DECIMAL(5,2),

PRIMARY KEY (Emp_Ssn, Proj_ID),

FOREIGN KEY (Emp_Ssn) REFERENCES EMPLOYEE(Ssn),

FOREIGN KEY (Proj_ID) REFERENCES PROJECT(Proj_ID)

);

✔ Prevents duplication of employee-project assignments.

4. CONTROLS (1:N)

Each department controls multiple projects.

sql

CopyEdit

ALTER TABLE PROJECT ADD Dept_ID INT;

ALTER TABLE PROJECT ADD FOREIGN KEY (Dept_ID) REFERENCES DEPARTMENT(Dept_ID);

✔ Ensures each project is assigned to one department.

5. SUPERVISION (1:N)

An employee supervises multiple employees, but each employee has only one supervisor.

sql

CopyEdit

ALTER TABLE EMPLOYEE ADD Supervisor_Ssn INT;

ALTER TABLE EMPLOYEE ADD FOREIGN KEY (Supervisor_Ssn) REFERENCES EMPLOYEE(Ssn);

✔ Self-referencing foreign key for employee hierarchy.

Step 3: Handling Multivalued Attributes

The Locations attribute in DEPARTMENT is multivalued.

✔ Solution: Create a separate table.

sql

CopyEdit

CREATE TABLE DEPARTMENT_LOCATIONS (

Dept_ID INT,

Location VARCHAR(100),
PRIMARY KEY (Dept_ID, Location),

FOREIGN KEY (Dept_ID) REFERENCES DEPARTMENT(Dept_ID)

);

✔ Each department can have multiple locations.

Step 4: Implementing Relationship Attributes

• The WORKS_FOR relationship has an attribute Start_date.

• Instead of keeping it in the EMPLOYEE table, store it in the WORKS_FOR relationship.

sql

CopyEdit

ALTER TABLE EMPLOYEE ADD Start_date DATE;

✔ Keeps the hire date of employees in their respective departments.

3. Final Relational Schema

Tables Created

Table Primary Key Foreign Keys

EMPLOYEE Ssn Dept_ID, Supervisor_Ssn

DEPARTMENT Dept_ID Manager_Ssn

PROJECT Proj_ID Dept_ID

WORKS_ON (Emp_Ssn, Proj_ID) Emp_Ssn, Proj_ID

SUPERVISION (Supervisor_Ssn, Ssn) Supervisor_Ssn

DEPENDENT Dependent_ID Emp_Ssn

DEPARTMENT_LOCATIONS (Dept_ID, Location) Dept_ID

UNIT 2:
1. Introduction to the Relational Data Model
The Relational Data Model is a fundamental concept in database management systems (DBMS) that
organizes data into tables (relations). It was introduced by E.F. Codd in 1970 and remains the
dominant model used in modern databases such as MySQL, PostgreSQL, SQL Server, and Oracle.

1.1 Why Use the Relational Data Model?

✔ Simplicity → Data is structured in rows and columns (like Excel).
✔ Flexibility → Easy to modify and scale.
✔ Data Integrity → Enforces constraints (e.g., primary key, foreign key).
✔ Efficiency → Optimized for queries and transactions.

1.2 Key Concepts of the Relational Model

The relational model consists of tables (relations) that store data, where:

• Rows (tuples) represent individual records.

• Columns (attributes) define data properties.

• Keys (Primary & Foreign) maintain uniqueness and relationships.

2. Components of the Relational Model

The relational model consists of several core components:

Component Definition Example

A collection of data organized in rows and

Relation (Table) STUDENT(SID, Name, Age, Major)
columns

Tuple
A single entry (record) in a relation (101, John, 21, 'CS')
(Row/Record)

Attribute (Column) A property/field of a relation SID, Name, Age, Major

Domain A set of allowable values for an attribute Age: Integer (18-60)

STUDENT has 4 attributes →

Degree of Relation The number of attributes in a relation
Degree = 4

1000 students → Cardinality =

Cardinality The number of tuples (rows) in a relation
1000

Uniquely identify rows and maintain Primary Key: SID, Foreign Key:
Keys (PK, FK)
relationships Dept_ID
3. Understanding Relational Schema
A Relational Schema is the blueprint of a database table.

• Defines column names, data types, and constraints.

• Describes the structure of the relation.

3.1 Example: STUDENT Relation

SID (PK) Name Age Major

101 John 21 CS

102 Alice 22 IT

103 Bob 20 EE

✔ Schema Representation:

STUDENT(SID INT PRIMARY KEY, Name VARCHAR(50), Age INT, Major VARCHAR(30))

✔ Primary Key (SID) uniquely identifies each student.

4. Tuples, Attributes, and Relations in Detail

4.1 Tuples (Rows)

• Each tuple represents one record in the relation.

• Order of tuples does not matter in a relation.

Example Tuples in STUDENT Table:

(101, 'John', 21, 'CS')

(102, 'Alice', 22, 'IT')

(103, 'Bob', 20, 'EE')

✔ Each row is unique and represents an individual entity.

4.2 Attributes (Columns)

• Each attribute represents a specific property of an entity.

• Attributes must have a domain (set of allowed values).

✔ Example: Attributes of STUDENT Relation

Attribute Description Domain (Allowed Values)

SID Student ID (Unique) Integer (100-999)

Attribute Description Domain (Allowed Values)

Name Student Name String (50 Characters)

Age Student's Age Integer (18-60)

Major Student's Major String (CS, IT, EE)

4.3 Relations (Tables)

• A relation is a set of tuples with the same structure.

• A relation must satisfy constraints like uniqueness (Primary Key), referential integrity
(Foreign Key), and domain rules.

✔ Example: COURSE Relation

COURSE(CID INT PRIMARY KEY, CName VARCHAR(50), Credits INT)

CID (PK) CName Credits

201 Database Systems 3

202 Operating Systems 4

✔ CID (Course ID) is the Primary Key.

5. Properties of Relations

A valid relational table must satisfy the following conditions:

Each row is unique (No duplicate tuples).
Each column has a unique name.
The order of rows does not matter.
Each column must have atomic (indivisible) values.

4. Integrity Constraints
1. Introduction to Integrity Constraints

Integrity constraints are rules that ensure data accuracy, consistency, and validity in a relational
database.

• These constraints prevent invalid data entry and maintain relationships between tables.

• Enforced using Primary Keys, Foreign Keys, Domain Constraints, and Referential Integrity.

• They help in avoiding data anomalies like redundancy, inconsistency, and NULL values in
important fields.

1.1 Why Are Integrity Constraints Important?

✔ Data Consistency → Ensures database remains valid.
✔ Prevents Data Anomalies → Eliminates redundancy, inconsistency, and deletion errors.
✔ Maintains Relationships → Enforces correct dependencies between tables.
✔ Automated Data Validation → Reduces manual errors.

2. Types of Integrity Constraints in the Relational Model

Integrity constraints in a relational database can be classified into the following categories:

1. Key Constraints → Ensures uniqueness of tuples.

2. Domain Constraints → Ensures attribute values are valid.

3. Referential Integrity Constraints → Maintains consistency between related tables.

3. Key Constraints in the Relational Model

Key constraints ensure uniqueness of data within a table.

3.1 Super Key

• A super key is a set of one or more attributes that uniquely identifies a row in a table.

• A table can have multiple super keys.

Example: EMPLOYEE Table

Emp_ID (PK) Name Dept_ID Phone

101 John 10 1234567890

102 Alice 20 9876543210

✔ Possible Super Keys:

pgsql

CopyEdit

{Emp_ID}, {Emp_ID, Name}, {Emp_ID, Phone}, {Emp_ID, Name, Phone}

✔ Every super key must contain at least one attribute that uniquely identifies a row.

3.2 Candidate Key

• A candidate key is the minimal super key (a key that uniquely identifies a row, but contains
no unnecessary attributes).

• A table can have multiple candidate keys, but only one is chosen as the Primary Key.

Example: EMPLOYEE Table

Candidate Keys: {Emp_ID}, {Phone}

✔ Emp_ID and Phone uniquely identify rows, but only one will be chosen as the Primary Key.

3.3 Primary Key (PK)

• A primary key (PK) is the chosen candidate key used to uniquely identify rows.

• It cannot be NULL and must always be unique.

Example: Primary Key in SQL

CREATE TABLE EMPLOYEE (

Emp_ID INT PRIMARY KEY,

Name VARCHAR(50),

Dept_ID INT,

Phone VARCHAR(15) UNIQUE

);

✔ Emp_ID is the primary key → Uniquely identifies an employee.

✔ Phone is a candidate key but is not chosen as the primary key.

4. Domain Constraints

A domain constraint restricts the values that an attribute can hold.

• Ensures only valid values are stored in a table.

• Helps in maintaining data integrity.

4.1 Example of Domain Constraints

Attribute Domain Constraint

Age Must be between 18 and 65

Salary Must be a positive number

Email Must follow email format ([email protected])

Phone Must have exactly 10 digits

✔ Enforcing Domain Constraints in SQL

CREATE TABLE EMPLOYEE (

Emp_ID INT PRIMARY KEY,

Name VARCHAR(50) NOT NULL,

Age INT CHECK (Age >= 18 AND Age <= 65),

Salary DECIMAL(10,2) CHECK (Salary > 0),

Email VARCHAR(100) CHECK (Email LIKE '_%@_%._%')

);

✔ CHECK ensures only valid values are inserted.

✔ Example: Age cannot be below 18 or above 65.

5. Referential Integrity Constraints

Referential Integrity ensures that relationships between tables remain valid.

• A foreign key (FK) enforces referential integrity by linking a column in one table to the
primary key (PK) of another table.

• Ensures data consistency across multiple tables.

• Prevents orphan records (e.g., an employee cannot belong to a non-existent department).

5.1 Example: EMPLOYEE and DEPARTMENT Tables

| EMPLOYEE Table |

Emp_ID (PK) Name Dept_ID (FK)

101 John 10

102 Alice 20

| DEPARTMENT Table |

Dept_ID (PK) Dept_Name

10 HR

20 IT

✔ Dept_ID in EMPLOYEE is a foreign key that references Dept_ID in DEPARTMENT.

5.2 Enforcing Referential Integrity in SQL

CREATE TABLE DEPARTMENT (

Dept_ID INT PRIMARY KEY,

Dept_Name VARCHAR(50)

);

CREATE TABLE EMPLOYEE (

Emp_ID INT PRIMARY KEY,

Name VARCHAR(50),

Dept_ID INT,

FOREIGN KEY (Dept_ID) REFERENCES DEPARTMENT(Dept_ID) ON DELETE CASCADE

);

✔ ON DELETE CASCADE ensures that if a department is deleted, all employees in that department
are also deleted.

6. Summary of Integrity Constraints

Constraint Type Definition Example

A set of attributes that uniquely

Super Key {Emp_ID, Phone}
identifies a row

A minimal super key (only necessary

Candidate Key {Emp_ID}
attributes)

Primary Key The chosen candidate key (must be

{Emp_ID}
(PK) unique and not NULL)

Ensures referential integrity between Dept_ID REFERENCES

Foreign Key (FK)
tables DEPARTMENT(Dept_ID)

Domain Ensures attribute values are within a

Age CHECK (Age >= 18 AND Age <= 65)
Constraint valid domain

Referential Ensures consistency between related FOREIGN KEY (Dept_ID) REFERENCES

Integrity tables DEPARTMENT(Dept_ID)

5. Foreign Key and Referential Integrity

1. Foreign Key and Referential Integrity
1.1 What is a Foreign Key?

A foreign key (FK) is an attribute (or a set of attributes) in one table that refers to the primary key
(PK) in another table.

• It establishes a relationship between two tables.

• A foreign key must match a value in the referenced table or be NULL.

1.2 Why are Foreign Keys Important?

✔ Enforces Referential Integrity → Prevents orphan records.
✔ Maintains Data Consistency → Ensures valid references between tables.
✔ Helps in Query Optimization → Facilitates efficient joins.
✔ Improves Data Organization → Supports 1:N and M:N relationships.

1.3 Example: Employee-Department Database

Consider a company database where:

• Each employee belongs to a department.

• The EMPLOYEE table references the DEPARTMENT table using a foreign key.

2. Relational Schema
Creating the DEPARTMENT Table

CREATE TABLE DEPARTMENT (

Dept_ID INT PRIMARY KEY,

Dept_Name VARCHAR(50),

Location VARCHAR(100)

);

Creating the EMPLOYEE Table with a Foreign Key

CREATE TABLE EMPLOYEE (

Emp_ID INT PRIMARY KEY,

Name VARCHAR(50),

Salary DECIMAL(10,2),

Dept_ID INT,

FOREIGN KEY (Dept_ID) REFERENCES DEPARTMENT(Dept_ID)

);

✔ FOREIGN KEY (Dept_ID) ensures that EMPLOYEE.Dept_ID must match a valid

DEPARTMENT.Dept_ID.

3. Referential Integrity
Referential Integrity is a rule that maintains consistency between related tables.

• It ensures that foreign key values always reference existing records in the primary key table.

• If a referenced record is deleted or updated, we must define how the dependent table
should react.

3.1 Referential Integrity Rules

Action Description

Insert Rule A foreign key value must match a primary key value in the referenced table or be NULL.

Delete Rule Prevents deletion of a referenced record unless handled using cascading actions.

Update If the primary key is updated, the foreign key should update accordingly or prevent the
Rule action.

4. Cascading Actions in Foreign Keys

Cascading actions define how the child table responds when the referenced record is modified or
deleted.

4.1 Types of Cascading Actions

Action Effect on Foreign Key

ON DELETE CASCADE Deletes child records when the parent is deleted.

ON DELETE SET NULL Sets foreign key to NULL when parent is deleted.

ON DELETE RESTRICT Prevents deletion of parent if referenced by child.

ON DELETE NO ACTION Similar to RESTRICT, no automatic change.

ON UPDATE CASCADE Updates child records when parent’s primary key is updated.

ON UPDATE SET NULL Sets foreign key to NULL when parent’s primary key is updated.

5. Implementing Cascading Actions in Employee-Department Database

5.1 ON DELETE CASCADE

• If a department is deleted, all employees in that department are also deleted.

CREATE TABLE EMPLOYEE (

Emp_ID INT PRIMARY KEY,

Name VARCHAR(50),

Salary DECIMAL(10,2),

Dept_ID INT,

FOREIGN KEY (Dept_ID) REFERENCES DEPARTMENT(Dept_ID) ON DELETE CASCADE

);

✔ If DEPARTMENT (Dept_ID = 10) is deleted, all employees with Dept_ID = 10 are also deleted.
5.2 ON DELETE SET NULL

• If a department is deleted, the employees' Dept_ID is set to NULL instead of deletion.

CREATE TABLE EMPLOYEE (

Emp_ID INT PRIMARY KEY,

Name VARCHAR(50),

Salary DECIMAL(10,2),

Dept_ID INT,

FOREIGN KEY (Dept_ID) REFERENCES DEPARTMENT(Dept_ID) ON DELETE SET NULL

);

✔ If DEPARTMENT (Dept_ID = 10) is deleted, employees remain, but their Dept_ID is set to NULL.

5.3 ON DELETE RESTRICT

• Prevents deletion of a department if there are employees referencing it.

CREATE TABLE EMPLOYEE (

Emp_ID INT PRIMARY KEY,

Name VARCHAR(50),

Salary DECIMAL(10,2),

Dept_ID INT,

FOREIGN KEY (Dept_ID) REFERENCES DEPARTMENT(Dept_ID) ON DELETE RESTRICT

);

✔ Deletion is blocked unless all related employees are first removed.

5.4 ON UPDATE CASCADE

• If a department’s Dept_ID is updated, all references in EMPLOYEE are updated.

CREATE TABLE EMPLOYEE (

Emp_ID INT PRIMARY KEY,

Name VARCHAR(50),

Salary DECIMAL(10,2),

Dept_ID INT,

FOREIGN KEY (Dept_ID) REFERENCES DEPARTMENT(Dept_ID) ON UPDATE CASCADE

);

✔ If Dept_ID is changed from 10 → 20 in DEPARTMENT, it is automatically updated in EMPLOYEE.

6. Summary of Foreign Key and Referential Integrity

Feature Description

Foreign Key Attribute linking a table to another table's primary key.

Referential Integrity Ensures foreign key values match existing primary key values.

ON DELETE CASCADE Deletes child records when parent record is deleted.

ON DELETE SET NULL Sets child foreign key to NULL when parent is deleted.

ON DELETE RESTRICT Prevents deletion if child records exist.

ON UPDATE CASCADE Updates child foreign key when parent’s primary key changes.
SQL
CREATE SCHEMA COMPANY AUTHORIZATION ‘Jsmith’
The SELECT-FROM-WHERE Structure of Basic SQL Queries
Tuple calculus:
VIEWS
A view in SQL terminology is a single table that is derived from other tables. These other tables can
be base tables or previously defined views. A view does not necessarily exist in physical form; it is
considered to be a virtual table, in contrast to base tables, whose tuples are always physically stored
in the database. This limits the possible update operations that can be applied to views, but it does
not provide any limitations on querying a view
A view is supposed to be always up-to-date; if we modify the tuples in the base tables on which the
view is defined, the view must automatically reflect these changes. Hence, the view is not realized or
materialized at the time of view definition but rather at the time when we specify a query on the
view. It is the responsibility of the DBMS and not the user to make sure that the view is kept up-to-
date.

View Implementation, View Update, and Inline Views

The problem of efficiently implementing a view for querying is complex. Two main approaches have
been suggested.

1. One strategy, called query modification, involves modifying or transforming the view query
(submitted by the user) into a query on the underlying base tables. For example, the query
QV1 would be automatically modified to the following query by the DBMS:

SELECT FROM WHERE Fname, Lname EMPLOYEE, PROJECT, WORKS_ON Ssn=Essn AND
Pno=Pnumber AND Pname=‘ProductX’;

The disadvantage of this approach is that it is inefficient for views defined via complex
Queries that are time-consuming to execute, especially if multiple queries are going to be
applied to the same view within a short period of time.

2. The second strategy, called view materialization, involves physically creating a temporary
view table when the view is first queried and keeping that table on the assumption that
other queries on the view will follow. In this case, an efficient strategy for automatically
updating the view table when the base tables are updated must be developed in order to
keep the view up-to-date.
Techniques using the concept of incremental update have been developed for this purpose,
where the DBMS can determine what new tuples must be inserted, deleted, or modified in a
materialized view table when a database update is applied to one of the defining base tables.
The view is generally kept as a materialized (physically stored) table as long as it is being
queried.
If the view is not queried for a certain period of time, the system may then automatically
remove the physical table and recompute it from scratch when future queries reference the
view.
Updating of views is complicated and can be ambiguous. In general, an update on a view
defined on a single table without any aggregate functions can be mapped to an update on
the underlying base table under certain conditions. For a view involving joins, an update
operation may be mapped to update operations on the underlying base relations in multiple
ways. Hence, it is often not possible for the DBMS to determine which of the updates is
intended. To illustrate potential problems with updating a view defined on multiple tables,
consider the WORKS_ON1 view, and suppose that we issue the command to update the
PNAME attribute of ‘John Smith’ from ‘ProductX’ to ‘ProductY’. This view update is shown in

In summary, we can make the following observations:

■ A view with a single defining table is updatable if the view attributes contain the primary
key of the base relation, as well as all attributes with the NOT NULL constraint that do not
have default values specified.
■ Views defined on multiple tables using joins are generally not updatable.
■ Views defined using grouping and aggregate functions are not updatable. In SQL, the
clause WITH CHECK OPTION must be added at the end of the view definition if a view is to be
updated. This allows the system to check for view updatability and to plan an execution
strategy for view updates.
It is also possible to define a view table in the FROM clause of an SQL query. This is known as
an in-line view. In this case, the view is defined within the query itself.
RELATIONAL ALGEBRA
Relational Algebra is a formal query language used to manipulate and retrieve data stored in a
relational database.

• It provides a set of operations that take one or more relations (tables) as input and return a
relation as output.

• Used as the theoretical foundation for SQL and query optimization.

1.2 Why Study Relational Algebra?

✔ Defines the theoretical basis of SQL (Structured Query Language).

✔ Helps in query optimization → Efficient database query execution.
✔ Provides a procedural approach to query formulation.
✔ Supports fundamental operations needed for retrieving and manipulating data.
Generalized Projection

Aggregate Functions and Grouping

Another type of operation that, in general, cannot be specified in the basic original relational algebra
is recursive closure. This operation is applied to a recursive relationship between tuples of the same
type, such as the relationship between an employee and a supervisor.
OUTER JOIN Operations

A set of operations, called outer joins, were developed for the case where the user wants to keep all
the tuples in R, or all those in S, or all those in both relations in the result of the JOIN, regardless of
whether or not they have matching tuples in the other relation. This satisfies the need of queries in
which tuples from two tables are to be combined by matching corresponding rows, but without
losing any tuples for lack of matching values. For example, suppose that we want a list of all
employee names as well as the name of the departments they manage if they happen to manage a
department; if they do not manage one, we can indicate it with a NULL value.
Comparison: Tuple Relational Calculus (TRC) vs. Domain Relational
Calculus (DRC) vs. Relational Algebra (RA)
1. Introduction
In the relational model, Tuple Relational Calculus (TRC), Domain Relational Calculus (DRC), and
Relational Algebra (RA) are three different formal query languages used for retrieving data. They
differ in their approach, notation, and expressiveness.

2. Comparison Table
Tuple Relational Calculus Domain Relational Calculus
Feature Relational Algebra (RA)
(TRC) (DRC)

Declarative query language Procedural query language

Declarative query language
Definition using domains (attribute that explicitly defines steps
using tuples as variables.
values) as variables. to retrieve data.

Type of
Non-procedural Non-procedural Procedural
Language

Uses a sequence of
Focuses on individual
Focus Focuses on tuples (rows). operations to manipulate
attribute values (columns).
relations.

Variables Uses tuple variables (t) to Uses domain variables (x, y, Operates directly on
Tuple Relational Calculus Domain Relational Calculus
Feature Relational Algebra (RA)
(TRC) (DRC)

Used represent entire rows. z) to represent attribute relations (tables).

values.

Uses predicate logic to Uses predicate logic to

Expression Uses a sequence of
specify conditions on specify conditions on
Type operators to retrieve results.
tuples. domains.

More complex since it

More intuitive for working Easier to visualize as it uses
Ease of Use focuses on individual
with entire rows. step-by-step operations.
attributes.

Query Queries specify what to Queries specify what to Queries specify both what
Execution retrieve, but not how. retrieve, but not how. and how to retrieve results.

Similar to SQL’s WHERE Similar to SQL’s SELECT Similar to SQL’s procedural

Relation to
clause but operates on clause but operates on execution model (JOINs,
SQL
tuples. domains. GROUP BY, etc.).

Used in theoretical Used in practical database

Used in formal database
Use Cases database studies and management systems
query optimization research.
logical query formulation. (DBMSs).

3. Detailed Explanation with Examples

A. Tuple Relational Calculus (TRC)

• Definition: TRC queries specify what to retrieve using tuples (rows) as variables.

• Structure: { t | condition(t) } (where t represents a tuple).

• Uses logical predicates like ∃ (exists) and ∀ (for all).

B. Domain Relational Calculus (DRC)

• Definition: DRC queries specify what to retrieve using domain variables (column values).

• Structure: { x | condition(x) }, where x represents an attribute value rather than a tuple.

• More mathematical and difficult to understand compared to TRC.

C. Relational Algebra (RA)

• Definition: RA is procedural, meaning it describes how to retrieve results.

• Uses operations like Selection (σ), Projection (π), Joins (⋈), Union (∪), Set Difference (-),
and Cartesian Product (×).

• Easier to visualize and implement in DBMSs.

Example 1: Find Employees Earning More than 50,000

TRC Query:

{ t | t ∈ EMPLOYEE ∧ t.Salary > 50000 }

DRC Query:

{ x, y, z | (x ∈ EMPLOYEE ∧ y ∈ EMPLOYEE ∧ z ∈ EMPLOYEE ∧ z > 50000) }

Relational Algebra Query:

σ Salary > 50000 (EMPLOYEE)

SQL Equivalent:

SELECT * FROM EMPLOYEE WHERE Salary > 50000;

Example 2: Find Employees Working in the IT Department

TRC Query:

{ t.Name | t ∈ EMPLOYEE ∧ ∃d (d ∈ DEPARTMENT ∧ t.Dept_ID = d.Dept_ID ∧ d.Dept_Name = 'IT') }

DRC Query:

{ x | ∃y (x ∈ EMPLOYEE ∧ y ∈ DEPARTMENT ∧ x.Dept_ID = y.Dept_ID ∧ y.Dept_Name = 'IT') }

Relational Algebra Query:

EMPLOYEE ⋈ EMPLOYEE.Dept_ID = DEPARTMENT.Dept_ID ∧ DEPARTMENT.Dept_Name = 'IT'

SQL Equivalent:

SELECT Name FROM EMPLOYEE

WHERE Dept_ID IN (SELECT Dept_ID FROM DEPARTMENT WHERE Dept_Name = 'IT');

Transforming the Universal and Existential Quantifier
DOMAIN RELATIONAL CALCULUS