DBMS CASE STUDY 1
March 21, 2020, 7:11 a.m. | Database Management
Faculty
Name |
Mrs.
C. Kalpana |
Course
Code / Title |
CS8492&Database Management Systems |
Focus
Area |
Database Design & Normalization |
Learning
Outcome |
Apply normalization concept for real world
applications |
Teaching
Pedagogy |
Case
Study &Discussion |
Knowledge
Level |
K3 |
Problem Statement:
Consider a database table that consists of details of members of the library and books available in the library. The library table has entailment details for members in the same table to restrict the number of books taken by a member.The Table Contains:
The Table Contains:
Book_id,
|
Title |
Price |
Member_id |
Member_ Name |
Member_type, |
Entailment |
101 |
Computer Networks |
400 |
CS11 |
Ravi K |
Faculty |
5 |
102 |
Operating Systems |
700 |
CS11 |
Ravi K |
Faculty |
5 |
103 |
System Software |
300 |
CS12 |
Shyam Sundar |
Student |
4 |
104 |
Software Engineering |
450 |
CS14 |
Venkatesh |
Student |
4 |
105 |
Data Base Systems |
750 |
CS12 |
Shyam Sundar |
Student |
4 |
Questions:
1.Eliminate the redundancy from the above table.
2.Eliminate Data manipulation anomalies and Data Inconsistency from the above table.
Solution:
Normalization
eliminates duplicates and data manipulation anomalies from data base.
Relation R à Book_id, Title,
Price, Member_id, Member_ Name, Member_type and
Entailment
1st 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 are no multi value attributes
· There is a repeating group in Relation R, so split the table into two as below.
R1 à Book_id, Member_id,
Title and Price
R2 àMember_id, Member_ First_Name, Member_ Last_Name, Member_type and Entailment
2nd Normal Form
A relation R is said to be in the second normal form (2 NF) if and only ifit is in the first normal form and no partial dependency exists between non-key attributes and key attributes.
· There is transitive dependency in Relation R2, so split it into two as below.
R11 à Book_id, Title and
Price
R12 à Book_id, Member_id
R21 àMember_id, Member_ Name, Member_ Last_Name and Member_type
R22 àMember_type and Entailment
Book_id |
Title |
Price |
|
|
|
|
|
|
Transaction
Book_id |
Member_id |
|
|
|
|
Member
Member_id |
Member_First_ Name |
Member_ Last_Name |
Member_type
|
|
|
|
|
|
|
|
|
Entailment
Member_type |
Entailment |
|
|
|
|