Kingston School of Management and Science

BCA 2ND yr 4TH Semester

NOTES OVER MODULE V (Relational Model and Relational Database Design: Concept of
Relational Model, Design Issues, Keys, Closure set, Functional Dependency, Different anomalies in designing a
Database., Normalization using functional dependencies, Decomposition, Boyce-Codd Normal Form, 3NF,
Normalization using multivalued dependencies, 4NF,5NF.)

DATE: 05.06.2026
Paper Name: DBMS
Paper Code: BCAC401

1. Concept of Relational Model

The Relational Model was proposed by Edgar F. Codd in 1970.

It is the most widely used database model where data is stored in the form of relations (tables).

Basic Terminology
Relational Model Term Table Equivalent

Relation Table

Tuple Row

Attribute Column

Domain Set of allowed values

Degree Number of attributes

Cardinality Number of rows

Example: Student Relation

StudentID Name Department Age

101 Rahul BCA 20

StudentID Name Department Age

102 Priya BCA 21

103 Amit BBA 22

Here,

 Relation = Student
 Tuple = Each row
 Attribute = StudentID, Name, Department, Age
 Cardinality = 3
 Degree = 4

2. Design Issues in Relational Database

While designing a database, we must avoid:

1. Data Redundancy

Same data stored repeatedly.

Example:

StudentID StudentName Course Faculty

101 Rahul DBMS Roy

101 Rahul Java Sen

Rahul's information is repeated.

2. Inconsistency

Different values stored for the same information.

StudentID Name

101 Rahul
StudentID Name

101 Rahul Kumar

This creates confusion.

3. Update Problems

Changing data in multiple places may create errors.

Good Database Design Characteristics

✔Minimum Redundancy

✔No anomalies

✔Data Consistency

✔Easy Maintenance

✔Efficient Queries

3. Keys in Relational Model

Keys uniquely identify records in a table.

3.1 Super Key

A set of attributes that uniquely identifies a tuple.

Example

Student Table
Roll Name Email

101 Rahul rahul@[Link]

Possible Super Keys:

 Roll
 Email
 Roll + Name
 Roll + Email

3.2 Candidate Key

Minimal Super Key.

Example:

 Roll
 Email

Both uniquely identify students.

3.3 Primary Key

Candidate key selected to identify tuples.

Example:

Student(Roll, Name, Email)

Primary Key = Roll

3.4 Alternate Key

Candidate keys not selected as Primary Key.

Example:

Primary Key = Roll

Alternate Key = Email

3.5 Composite Key

Combination of attributes.

Example:

StudentID CourseID

(StudentID, CourseID) together uniquely identify records.

3.6 Foreign Key

Attribute referencing Primary Key of another table.

Student Table
StudentID Name

101 Rahul

Result Table
StudentID Marks

101 90

StudentID in Result table is Foreign Key.

4. Closure Set

Closure helps determine all attributes functionally dependent on a set of attributes.

Denoted as:

X+

Example

Relation:
R(A,B,C,D)

FDs:

A → B
B → C
C → D

Find A+

Start:

A+
= {A}

Using A → B

A+={A,B}

Using B → C

A+={A,B,C}

Using C → D

A+={A,B,C,D}

Thus,

A+= {A,B,C,D}

Since A determines all attributes, A is Candidate Key.

5. Functional Dependency (FD)

A Functional Dependency exists when one attribute determines another.

Notation:

A → B

Meaning:

Value of A determines value of B.

Example

Student Table

Roll Name

101 Rahul

102 Priya

Here,

Roll → Name

Because each Roll determines one Name.

Types of Functional Dependency

1. Full Functional Dependency
(A,B) → C

C depends on entire key.

2. Partial Dependency
(A,B) → C

But C depends only on A.

Violation of 2NF.

3. Transitive Dependency
A → B
B → C

Then

A → C

Violation of 3NF.
6. Database Anomalies

Poor design causes anomalies.

6.1 Insertion Anomaly

Cannot insert data without another data.

Example

Cannot add a new course unless a student enrolls.

6.2 Deletion Anomaly

Deleting one record removes useful information.

Example:

Deleting last student of DBMS course removes course information.

6.3 Update Anomaly

Same information must be updated multiple times.

Example:

Faculty name appears in many rows.

Updating all rows is required.

7. Normalization

Normalization organizes data to reduce redundancy and anomalies.

Objectives

 Remove redundancy
 Remove anomalies
 Improve consistency
 Simplify maintenance

8. Decomposition

Breaking a large relation into smaller relations.

Example

Before

StudentID StudentName Course

101 Rahul DBMS

After decomposition

Student
StudentID StudentName

101 Rahul

Course
StudentID Course

101 DBMS

9. Third Normal Form (3NF)

A relation is in 3NF if:

1. It is in 2NF.
2. No transitive dependency exists.
Example
StudentID Name DeptID DeptName

101 Rahul D1 BCA

FDs:

StudentID → DeptID
DeptID → DeptName

Transitive Dependency:

StudentID → DeptName

Convert to 3NF

Student

StudentID Name DeptID

Department

DeptID DeptName

10. Boyce-Codd Normal Form (BCNF)

A stronger version of 3NF.

Condition:

For every FD

X → Y

X must be a Super Key.

Example

Relation:
R(Student, Subject, Teacher)

FDs:

(Student,Subject) → Teacher
Teacher → Subject

Teacher is not Super Key.

Therefore relation violates BCNF.

BCNF Decomposition

Teacher-Subject

Teacher Subject

Student-Teacher

Student Teacher

Now BCNF is achieved.

Difference Between 3NF and BCNF

3NF BCNF

Less strict More strict

May allow some anomalies Eliminates more anomalies

Every BCNF is 3NF Not every 3NF is BCNF

11. Multivalued Dependency (MVD)

Occurs when one attribute determines multiple independent values.

Notation:
A →→ B

Example

A student may have multiple skills and multiple hobbies.

Student Skill Hobby

Rahul Java Cricket

Rahul Java Music

Rahul Python Cricket

Rahul Python Music

Here:

Student →→ Skill
Student →→ Hobby

Skill and Hobby are independent.

12. Fourth Normal Form (4NF)

A relation is in 4NF if:

1. It is in BCNF.
2. No non-trivial multivalued dependency exists.

Example

StudentSkillsHobbies

Student Skill Hobby

Violates 4NF.
Decompose

StudentSkill

Student Skill

StudentHobby

Student Hobby

Now 4NF is satisfied.

13. Fifth Normal Form (5NF)

Also called:

Project Join Normal Form (PJNF)

A relation is in 5NF if it cannot be decomposed further without losing information.

Example

Relation

Supplier Part Project

Suppose:

 Supplier supplies Parts

 Parts used in Projects
 Supplier works for Projects

Complex many-to-many relationships exist.

Decompose Into

Supplier-Part
| Supplier | Part |

Supplier-Project

| Supplier | Project |

Part-Project

| Part | Project |

Joining these relations recreates original relation.

Thus relation satisfies 5NF.

Summary Table for Exam

Normal Form Removes

1NF Repeating Groups

2NF Partial Dependency

3NF Transitive Dependency

BCNF Candidate Key Anomalies

4NF Multivalued Dependency

5NF Join Dependency

Important MAKAUT Exam Questions

Short Questions

1. Define Relation and Tuple.

2. What is Functional Dependency?
3. What is Closure Set?
4. Define Candidate Key and Primary Key.
5. What is BCNF?
6. Define Multivalued Dependency.
7. What is Decomposition?
Long Questions

1. Explain all types of keys with examples.

2. Discuss Functional Dependency and Closure with examples.
3. Explain database anomalies with examples.
4. Explain normalization up to BCNF with examples.
5. Differentiate 3NF and BCNF.
6. Explain 4NF and 5NF with suitable examples.
7. Discuss decomposition and lossless decomposition.