Databases
What is Databases?
A process of organizing data in a database to reduce redundancy and improve data integrity.
Key formula / rule: Functional Dependency
Key points
- Understand the purpose and benefits of database normalization.
- Identify and resolve data anomalies.
- Apply the rules for 1NF, 2NF, 3NF, and BCNF.
- Decompose unnormalized relations into normalized forms.
Common exam trap
Over-normalization leading to complex joins and performance issues.
Definitions
- Term
Normalization
- Meaning
A process of organizing data in a database to reduce redundancy and improve data integrity.
- Term
Functional Dependency (FD)
- Meaning
A relationship between two sets of attributes in a relation, where one set determines the other.
- Term
First Normal Form (1NF)
- Meaning
A relation is in 1NF if all attribute values are atomic and there are no repeating groups.
- Term
Second Normal Form (2NF)
- Meaning
A relation is in 2NF if it is in 1NF and all non-prime attributes are fully functionally dependent on the primary key.
- Term
Third Normal Form (3NF)
- Meaning
A relation is in 3NF if it is in 2NF and all non-prime attributes are not transitively dependent on the primary key.
- Term
Boyce-Codd Normal Form (BCNF)
- Meaning
A relation is in BCNF if for every non-trivial functional dependency X → Y, X is a superkey.
- Term
Data Anomaly
- Meaning
An error or inconsistency in data that can arise due to redundancy, such as insertion, update, or deletion anomalies.
Learning objectives
Understand the purpose and benefits of database normalization.
Identify and resolve data anomalies.
Apply the rules for 1NF, 2NF, 3NF, and BCNF.
Decompose unnormalized relations into normalized forms.
Analyze the trade-offs in normalization.
Formulae
- Name
Functional Dependency
- Note
Attribute set Y is functionally dependent on attribute set X. If X has a specific value, Y must also have a specific value.
- Expression
X → Y
Prerequisites
Relational Algebra
Functional Dependencies
Keys (Primary Key, Candidate Key, Super Key)
Database Design Principles
Common mistakes
Over-normalization leading to complex joins and performance issues.
Incorrectly identifying functional dependencies.
Not considering the trade-offs between normalization and performance.
Confusing partial and transitive dependencies.
Applying normalization rules without understanding the data's context.
Keywords
Normalization
Database Design
Functional Dependency
Normal Forms
1NF
2NF
3NF
BCNF
Data Integrity
Redundancy
Anomalies
Practice preview
Which of the following is a characteristic of a good database design?…
easy
Which SQL statement is used to retrieve data from a database?…
easy
In a relational database, a primary key is used to:…
easy