Database Normalization and Functional Dependencies

Database Normalization

• Database normalization is a design principle for organizing data consistently and avoiding redundancy.
• It improves data integrity and reduces undesirable effects of insertion, deletion, or updates.

Functional Dependencies

• Functional dependency is a relationship between attribute sets in a table.
• It describes how the value of one attribute determines another's value.
• Represented as X → Y, where X and Y are attribute sets in a table.

Functional Dependencies Example

• A table "Employees" with attributes (employee ID, name, department, salary).
• "department → salary" means employees in the same department share the same salary.

Another Example

• 'roll_no' is a unique identifier (primary key) for a student table.
• 'name' and 'city' depend on 'roll_no'
• 'name' and 'city' are functionally dependent on 'roll_no'.
• You cannot get 'roll_no' based on 'name' or 'city' alone.

Why Functional Dependency Is Used?

• Maintains data integrity
• Facilitates easy data maintenance
• Enables efficient data storage
• Improves data redundancy

Types of Dependencies

• Full Dependency: All primary key attributes are needed to determine a non-primary attribute. Example: To get a student's mark, both roll number and subject ID are needed.

• Partial Dependency: Primary key is composed of multiple attributes; a non-key attribute depends on only part of the composite key. Example: In a student table, if we use roll number + subject ID, but a subject name only depends on the subject ID, then it's a partial dependency.

• Transitive Dependency: The value of a non-primary attribute is determined by another non-primary attribute. Example: If 'city' is dependent on 'zip_code', which is also not a primary key, then there is transitive dependency.

Primary Key

• Uniquely identifies each row in a table.
• Cannot contain NULL values.
• Example: 'STUD_NO' in a 'STUDENTS' table.

Foreign Key

• A column or set of columns referencing a primary key in another table, creating a link between them.
• Example: 'STUD_NO' in a 'STUDENT_COURSE' table references 'STUD_NO' in a 'STUDENT' table.

Composite Key

• Combination of two or more columns uniquely identifying each row.
• Example: 'STUD_NO' and 'PHONE' together uniquely define a student.

Super Key

• An attribute (or set of attributes) uniquely defining all attributes in a relation.
• It can include more than one candidate key.
• Example: If 'ID' and ('First Name', 'Last Name') are both candidate keys in a 'STUDENT' table, both together are a super key.

Database Normalization

• Database normalization is a database design principle for organizing data consistently to avoid complexities and redundancy.

Purpose of Normalization

• To avoid complexities
• To eliminate redundancy
• To organize data consistently.
• Primary, foreign, and composite keys support these relationships.

1NF, 2NF and 3NF

• 1NF, 2NF and 3NF are the first three types of database normalization steps. They stand for first normal form, second normal form, and third normal form, respectively.
• Other higher normal forms (4NF, 5NF) exist but are less commonly used by DB administrators
• Subsequent normal forms build upon previous ones.

The First Normal Form - 1NF

• A table is in 1NF if it meets atomicity (no single cell holds more than one value) and has a primary key for identification.

The Second Normal Form - 2NF

• A table is in 2NF if it's already in 1NF, with no partial dependencies (non-key attributes depend on the entire primary key, not just part of it).

The Third Normal Form - 3NF

• A table is in 3NF if it's in 2NF and has no transitive dependencies (non-key attributes don't depend on other non-key attributes).

BCNF (Boyce-Codd Normal Form)

• A stronger form than 3NF: if a functional dependency X --> A exists, then X must be a superkey.
• Each normal form builds on the previous one, with each new level being more strict.