DBMS Case Study 2

Problem Statement :

Consider a data base table consists of student details. Management also wants to add marks scored by student for all the subjects.

Questions:

1.Eliminate the redundancy from the above table.

2.Eliminate Data manipulation anomalies and Data inconsistency from the above table.

Solution:

1. Normalization eliminates duplicates and data manipulation anomalies from data base.

Relation R à

1^st Normal Form

A relation R is said to be in the first normal form (1 NF) if and only if all the attributes of the relation are atomic in nature.

 There is a repeating group in Relation R, so split it into two as below

R1 àRegisterNo, Name, Department, semester, Subject_code, Marks and Grade

R2 àSubject_code and Subject_title

2^nd Normal Form

A relation R is said to be in the second normal form (2 NF) if and only if it is in the first normal form and no partial dependency exists between non-key attributes and key attributes.

·         There is a partial functional dependency in Relation R1, so split it into two as below.

R11 àRegisterNo, Name, Department and semester

R12 àRegisterNo, Subject_code, Marks and Grade

R2 àSubject_code and Subject_title

3^rd Normal Form

A relation R is said to be in the third normal form (3 NF) if and only if it is in the second normal form and no transitive dependency exists between non-key attributes and key attributes.

·         There is a transitive dependency in the Relation R12, so split it into two as below.

R11 àRegisterNo, Name, Department and semester

R121 àRegisterNo, Subject_code, Marks

R122 àMin_Marks, Max_Marks and Grade

R2 àSubject_code and Subject_title

Student

Subject

Grade

Exam_Marks