1.
Enterprise Constraints
● Meaning: Rules or conditions that must be followed in an
organization’s database to maintain correctness and
consistency of data.
● Examples:
○ Key constraints: Every record must have a unique
identifier (e.g., roll number in a student table).
○ Referential integrity: A foreign key must match a
primary key in another table (e.g., every order must
belong to an existing customer).
○ Domain constraints: Values must be from a specific
set or type (e.g., age must be a number between 0 and
120).
● Purpose: Prevent invalid, inconsistent, or incomplete data
from entering the database.
2. Features of Good Relational Database Design
A good relational design should:
1. Avoid redundancy — don’t store the same fact in multiple
places unnecessarily.
2. Preserve information — no data should be lost due to
design choices.
3. Ensure data integrity — maintain accuracy and
consistency.
4. Support efficient queries — retrieving data should be fast
and straightforward.
5. Allow easy maintenance — changes in structure should be
easy to handle without breaking the system.
3. Normalization
● Meaning: A step-by-step process of organizing data in
tables to reduce redundancy and improve data integrity.
● Goal: Split large, messy tables into smaller, related tables
without losing information.
4. Atomic Domains & First Normal Form (1NF)
● Atomic Domain: A column should have indivisible values
— no lists, no multiple values in the same field.
1. ❌ Wrong: PhoneNumbers = "12345, 67890"
2. ✅ Correct: Store each phone number in a separate row
or column.
● 1NF Rule:
1. Each cell contains only one value (atomicity).
2. Each record is unique.
5. Decomposition using Functional Dependencies
● Functional Dependency (FD):
In a table, if knowing the value of A lets you uniquely
determine B, we say A → B.
● Decomposition: Splitting one table into two or more tables
using FDs while:
1. Preserving dependencies (relationships between
attributes).
2. Avoiding redundancy.
3. Keeping the ability to reconstruct the original table
(lossless join).
6. Algorithms for Decomposition
When breaking a table:
1. Find functional dependencies from business rules.
2. Check normal form violations.
3. Split tables so that:
○ Each table follows the desired normal form.
○ We can join them back to get the original data.
7. Second Normal Form (2NF)
● Rule: Table must be in 1NF and no partial dependency
should exist.
● Partial dependency: A non-key attribute depends on only
part of a composite primary key.
● Solution: Move attributes that depend on part of the key
into another table.
8. Third Normal Form (3NF)
● Rule: Table must be in 2NF and no transitive
dependency should exist.
● Transitive dependency: A non-key attribute depends on
another non-key attribute.
● Solution: Remove these by creating separate tables.
9. Boyce-Codd Normal Form (BCNF)
● Stricter than 3NF:
For every functional dependency X → Y, X must be a
superkey.
● Fixes certain anomalies that 3NF may still allow.
10. Fourth Normal Form (4NF)
● Deals with multi-valued dependencies (MVDs).
● Rule: Table must be in BCNF and should not have two or
more independent multi-valued facts about the same key.
✅ Summary Table:
Normal Main Issue Rule
Form Fixed
1NF Repeating Atomic values only
groups
2NF Partial 1NF + no attribute depends on
dependency part of a key
3NF Transitive 2NF + no non-key depends on
dependency another non-key
BCNF Remaining Every determinant is a
anomalies superkey
4NF Multi-valued BCNF + no independent multi-
dependency valued facts
