Decode School Karpagam Tech

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   

Book_id

Title

Price

 

 

 

 

 

 

Transaction

Book_id

Member_id

 

 

 

 




Member

Member_id

Member_First_ Name

Member_ Last_Name

Member_type

 

 

 

 

 

 

 

 

 

Entailment

Member_type

Entailment

 

 

 

 







Comments

Related Posts