Database Management System (DBMS) Full Notes provides comprehensive coverage of key concepts, including functional dependencies, normalization, ACID properties, and transaction management. This resource is essential for students studying database design and implementation, offering detailed explanations of normalization forms such as 1NF, 2NF, 3NF, and BCNF. It also explores multivalued dependencies, join dependencies, and recovery techniques, making it a valuable study aid for exam preparation. Ideal for computer science students and professionals looking to deepen their understanding of database systems.

Key Points

  • Explains functional dependencies and their types in DBMS.
  • Covers normalization forms including 1NF, 2NF, 3NF, and BCNF.
  • Details ACID properties essential for transaction management.
  • Discusses multivalued dependencies and join dependencies.
  • Outlines recovery techniques including log-based recovery and checkpoints.
Dipali Verma
24 pages
Language:English
Type:Study Guide
Databases
Dipali Verma
24 pages
Language:English
Type:Study Guide
Databases
274
/ 24
Unit-IV: Database Design & Normalization
1. Functional Dependencies (FD)
Definition
A functional dependency (FD) is a constraint between two sets of attributes in a relation
from a database. In a relation R, an attribute B is functionally dependent on another
attribute A (denoted as A → B) if, for any two tuples in R, whenever the values of A are the
same, then the values of B must also be the same. In simpler terms, A uniquely determines
B.
Types of Functional Dependencies
Trivial Functional Dependency: A → B is trivial if B
⊆
A. For example, {A, B} → A is
trivial.
Non-trivial Functional Dependency: A → B is non-trivial if B is not a subset of A.
For example, Student_ID → Student_Name.
Completely Non-trivial Functional Dependency: A → B is completely non-trivial if
A ∩ B = Φ.
Inference Rules for Functional Dependencies (Armstrong’s Axioms)
These rules are used to infer all functional dependencies from a given set of FDs.
1. Reflexivity Rule: If B
⊆
A, then A → B. (If B is a subset of A, then A determines B).
2. Augmentation Rule: If A → B, then A, C → B, C. (Adding attributes to the left side of
an FD does not change the dependency).
3. Transitivity Rule: If A → B and B → C, then A → C. (If A determines B and B
determines C, then A determines C).
Additional Inference Rules (Derived from Armstrong’s Axioms)
Decomposition Rule: If A → B, C, then A → B and A → C.
Union Rule: If A → B and A → C, then A → B, C.
Pseudotransitivity Rule: If A → B and C, B → D, then A, C → D.
Example
Consider a relation Student with attributes (StudentID, StudentName, CourseID,
CourseName, Instructor).
StudentID → StudentName (A student ID uniquely determines the student’s name).
CourseID → CourseName (A course ID uniquely determines the course name).
StudentID, CourseID → Instructor (A student taking a specific course has a
specific instructor).
Exam-oriented Points
Understanding FDs is crucial for normalization.
Be able to apply Armstrong’s Axioms to find the closure of a set of attributes.
Identify candidate keys using FDs.
Important Questions
1. What is a functional dependency? Explain with an example.
2. State and explain Armstrong’s Axioms.
3. Given a relation R(A, B, C, D, E) and a set of FDs F = {A → B, BC → D, A → C}, find
the closure of {A, C}.
2. Normal Forms (1NF, 2NF, 3NF, BCNF)
Normalization is the process of organizing the columns and tables of a relational database
to minimize data redundancy and improve data integrity. It involves decomposing a table
into smaller tables while ensuring that the original data can be reconstructed.
1. First Normal Form (1NF)
Definition
A relation is in 1NF if and only if all attribute domains contain only atomic (indivisible)
values, and there are no repeating groups of attributes.
Step-by-step Explanation
1. Eliminate repeating groups by placing each into a new table along with a copy of the
original table’s primary key.
2. Identify a primary key for the new table.
Example
Before 1NF:
StudentID StudentName Course
101 Alice Math, Physics
102 Bob Chemistry
After 1NF:
StudentID StudentName Course
101 Alice Math
101 Alice Physics
102 Bob Chemistry
2. Second Normal Form (2NF)
Definition
A relation is in 2NF if it is in 1NF and every non-key attribute is fully functionally dependent
on the primary key. This means no non-key attribute is dependent on only a part of the
primary key (for composite primary keys).
/ 24
End of Document
274

You May Also Like

FAQs

What is a functional dependency in database management?
A functional dependency (FD) is a constraint between two sets of attributes in a relation from a database. It is denoted as a → b, meaning that if two tuples in a relation have the same values for attribute a, they must also have the same values for attribute b. For example, in a relation where student_id uniquely determines student_name, we can express this as student_id → student_name.
What are Armstrong's axioms in functional dependencies?
Armstrong's axioms are a set of rules used to infer all functional dependencies from a given set of FDs. The axioms include: 1) Reflexivity Rule: If b is a subset of a, then a → b. 2) Augmentation Rule: If a → b, then a, c → b, c. 3) Transitivity Rule: If a → b and b → c, then a → c. These rules help in understanding and deriving functional dependencies in database relations.
How is normalization defined in database management systems?
Normalization is the process of organizing the columns and tables of a relational database to minimize data redundancy and improve data integrity. This involves decomposing a table into smaller tables while ensuring that the original data can be reconstructed. The normalization process includes several normal forms, such as 1NF, 2NF, 3NF, and BCNF, each addressing specific types of redundancy and dependency issues.
What is the difference between 2NF and 3NF in database normalization?
A relation is in Second Normal Form (2NF) if it is in 1NF and every non-key attribute is fully functionally dependent on the primary key, meaning no non-key attribute is dependent on only part of a composite primary key. In contrast, a relation is in Third Normal Form (3NF) if it is in 2NF and there are no transitive dependencies, where a non-key attribute depends on another non-key attribute. This distinction helps eliminate different types of redundancy in database design.
What is Boyce-Codd Normal Form (BCNF) and how does it differ from 3NF?
Boyce-Codd Normal Form (BCNF) is a stricter version of the Third Normal Form (3NF). A relation is in BCNF if, for every non-trivial functional dependency a → b, a is a superkey. This means that BCNF addresses anomalies that 3NF might miss, particularly when a table has multiple overlapping candidate keys. For example, a relation might be in 3NF but still have a non-trivial FD where the determinant is not a superkey, thus violating BCNF.
What is a multivalued dependency (MVD) in database design?
A multivalued dependency (MVD) occurs when there are at least three attributes (a, b, c) in a relation, and for each value of a, there exists a set of values for b and a set of values for c, where b and c are independent of each other. It is denoted as a →→ b, indicating that a multidetermines b. For instance, in a relation where an employee can work on multiple projects and possess multiple skills, the relationship between projects and skills is independent for each employee.
What is lossless join decomposition and why is it important?
Lossless join decomposition refers to the decomposition of a relation into two or more relations such that the natural join of these relations yields exactly the original relation, ensuring no spurious tuples are generated and no information is lost. This property is crucial for maintaining data integrity during the normalization process, as it ensures that the original data can be accurately reconstructed from the decomposed tables.

Related