Scribd
Upload
7 views27 pages

DBMS Unit3 Slides Expanded

This document covers Functional Dependencies (FD) and Normalization in database management, detailing types of FDs, keys, and the normalization process to reduce data redundancy. It explains the different normal forms (1NF, 2NF, 3NF, BCNF, 4NF, 5NF) and their significance in schema design, along with transaction concepts and ACID properties. The document emphasizes the importance of these concepts as foundational elements for effective database design.

Uploaded by

faiza2002.ayesha
Copyright
© © All Rights Reserved
We take content rights seriously. If you suspect this is your content, claim it here.
Available Formats
Download as PPTX, PDF, TXT or read online on Scribd
7 views27 pages

DBMS Unit3 Slides Expanded

This document covers Functional Dependencies (FD) and Normalization in database management, detailing types of FDs, keys, and the normalization process to reduce data redundancy. It explains the different normal forms (1NF, 2NF, 3NF, BCNF, 4NF, 5NF) and their significance in schema design, along with transaction concepts and ACID properties. The document emphasizes the importance of these concepts as foundational elements for effective database design.

Uploaded by

faiza2002.ayesha
Copyright
© © All Rights Reserved
We take content rights seriously. If you suspect this is your content, claim it here.
Available Formats
Download as PPTX, PDF, TXT or read online on Scribd
You are on page 1/ 27

DBMS Unit 3

• Functional Dependencies & Normalization


• (Extended Teaching Slides - 25 Slides)
What is a Functional Dependency?
• X → Y means X uniquely determines Y
• Improves schema design
Types of FD
• Trivial
• Non-Trivial
• Partial
• Transitive
• Multivalued Dependency
Keys & FD
• Primary Key
• Candidate Key
• Super Key
• Attribute Closure
Example of FD
• StudentID → StudentName
• CourseID → CourseName
Normalization
• Process of organizing data to reduce
redundancy
• Improves integrity
Why Normalize?
• Avoid anomalies:
• • Insert
• • Update
• • Delete
1NF - First Normal Form
• Atomic values only
• No repeating groups
1NF Example
• Before: Course1, Course2
• After: Separate rows for each course
2NF - Second Normal Form
• Remove partial dependencies
• Depends on whole composite key
2NF Example
• EmpID + Project → Manager
• Remove Manager details into separate table
3NF - Third Normal Form
• Remove transitive dependencies
• Attributes depend only on primary key
3NF Example
• ManagerPhone depends on Manager → move
to separate table
BCNF - Boyce Codd Normal Form
• Determinant must be a candidate key
• Stronger 3NF
4NF & 5NF
• Handles Multivalued Dependency & Join
Dependency
ERD & Normalization
• ERD refined using normalization steps
Example - Library
• Library(BookID, BookTitle, Author, StudentID,
BorrowDate)
• Split into 3NF
Normalization Summary Table
• 1NF – Remove repeating groups
• 2NF – Remove partial dependency
• 3NF – Remove transitive dependency
Transaction Concepts
• What is a Transaction?
• Sequence of operations
ACID Properties
• Atomicity
• Consistency
• Isolation
• Durability
Concurrency Issues
• Lost Update
• Dirty Read
• Unrepeatable Read
• Phantom Read
Locking Techniques
• Shared Lock (S)
• Exclusive Lock (X)
• Two-Phase Locking (2PL)
Transaction Commands
• COMMIT
• ROLLBACK
• SAVEPOINT
Real-Time Example
• Bank Transaction:
• Debit + Credit ≠ Partial Commit
Unit 3 Summary
• FD + Normalization + Transaction Control
• Foundation for DB design
FD Diagram Example
• StudentID ----> StudentName
• |
• |----> Course
• |
• |----> Instructor
Normalization Workflow
• UNF → 1NF → 2NF → 3NF → BCNF → 4NF →
5NF

You might also like