B.tech(cse) normalisation, Study notes of Database Management Systems (DBMS)

Download B.tech(cse) normalisation and more Study notes Database Management Systems (DBMS) in PDF only on Docsity!

Normalization

Data base anomalies

• 1. Update

• 2. Insert

• 3 Delete anomalies

Normalization

• Is a technique for designing relational

database tables to minimize duplication of

information and to increase the logical

consistency.

Dependencies

• Functional dependency

• Full functional dependency

• Partial functional dependency

• Transitive functional dependency

• Multi-valued functional dependency

• Join functional dependency

Full functional dependency

• An attribute is FFD on a set of attributes if

– It is functionally dependent on S and

– Not functionally dependent on any proper subset

of S.

Roll_ num NAme Course_id course_title Grade 1 Raj CSE301 DBMS A 1 Raj CSE306 NW C 2 Ankur CSE301 DBMS B 2 Ankur CSE306 NW A 3 Arun CSE316 SOFT ENGG C roll_num ,course_id Grade Name and course_title are not fully functional dependent on composite key

Transitive functional dependency Dept_id Dept_name Hod_name 1 CSE Mr X 2 IT Mr Y 3 ECE Mr z 4 ME Mr A A B C Dept_id Dept_name Hod_name

Multi-valued functional dependency Name Ph_number Ram 987217701 Sham 982271661 Ram 876622134 Rajesh 872213477 Raj 657932721 Ajay 873539262 A B Name Ph_number

Model Price Make N12 10000 CANON P20 12000 NIKON A73 15000 CANON Model Make N12 CANON P20 NIKON A73 CANON price make 10000 CANON 12000 NIKON 15000 CANON

model price make N12 10000 CANON N12 15000 CANON P20 12000 NIKON A73 10000 CANON A73 15000 CANON

Properties of decomposition

• Lossless

• Dependency preserving

Functional dependency diagram

Faculty_name Course_code

• CSE Harish
• CSE
• CSE
• Rajesh INT
  • INT
  • CSE
• Raj CSE
  • CSE
  • CSE
• Harish CSE Faculty_name Course_code
• Harish CSE
• Harish CSE
• Rajesh INT
• Rajesh INT
• Rajesh CSE
• Raj CSE
• Raj CSE
• Raj CSE