A-1 Database Tables and Normalization

Study Notes

Relational databases aim to minimize data redundancy to enhance data integrity and reduce anomalies.
Normalization is the method to refine table structures through normal forms to achieve this goal.
Key normal forms include:
- First Normal Form (1NF)
- Second Normal Form (2NF)
- Third Normal Form (3NF)
The hierarchy: 3NF > 2NF > 1NF, with 3NF typically sufficient for business applications.
Higher normal forms can add complexity and increase resource demands for database operations, sometimes requiring denormalization for performance enhancement.
Denormalization introduces data redundancy to improve query performance by converting higher normal forms to lower ones.

Functional dependence is critical in understanding relationships between attributes in a table.
Identifying dependencies is essential for deriving the functional dependencies that guide the normalization process.
A relational table should not have repeating groups; they indicate that the table is below 1NF requirements.

Step 1: Eliminate Repeating Groups
- Ensure each cell contains a single value and no repeating groups are present.
Step 2: Identify the Primary Key (PK)
- A proper primary key must uniquely identify every row in the table; sometimes composed of multiple attributes (e.g., PROJ_NUM + EMP_NUM).
Step 3: Identify All Dependencies
- Establish dependencies for every attribute, ensuring they are accurately defined within tables to avoid transitive dependencies.

Evaluate PK Assignments
- Use unique identifiers (e.g., JOB_CODE) to minimize data entry errors and maintain referential integrity.
Evaluate Naming Conventions
- Use descriptive naming for attributes, improving clarity and maintaining consistency.
Refine Attribute Atomicity
- Design attributes to be atomic, preventing decomposition for better querying flexibility (e.g., splitting EMP_NAME into EMP_LNAME, EMP_FNAME, EMP_INITIAL).
Identify New Attributes
- Consider additional attributes relevant to business functions (e.g., compensation details, hire dates) to support operational needs.

Boyce-Codd Normal Form (BCNF)
- A stronger version of 3NF, requiring that every determinant in the table is a candidate key.
- Transitive dependencies may still exist, which does not automatically violate 3NF but may prevent BCNF compliance.
Understanding the relationship between 3NF and BCNF:
- A table can be in 3NF but not in BCNF if a key attribute is determined by another nonprime attribute.
- To comply with BCNF, adjustments may be necessary, such as redefining primary keys based on determinant relationships.