# DBMS CASE STUDY 1

D Shyam Prakash & C Kalpana

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
 Book_id Title Price

Transaction

 Book_id Member_id

Member

 Member_id Member_First_ Name Member_ Last_Name Member_type

Entailment

 Member_type Entailment