LEC9

Database Design and Development Lecture Notes

Today's Topics

  • Process of Normalisation

  • Transformations: UNF to 1NF, 1NF to 2NF, and 2NF to 3NF

  • Exemplifying the complete process to achieve the Third Normal Form (3NF)

  • SQL: Views

Normalisation

  • Definition: Normalisation is a process in database design that organizes tables and their relationships to minimize redundancy.

Process of Normalisation

  • As the normalisation process progresses:

    • Relations become progressively more restricted (stronger) in format.

    • The relations become less vulnerable to update anomalies.

Lossless-Join and Dependency Preservation

  • Lossless-Join Property: Ensures that any instance of the original relation can be reconstructed from the decomposed relations without loss of information.

  • Dependency Preservation Property: Allows us to enforce constraints on the original relation by maintaining some constraints on the smaller relations resulting from the decomposition.

    • Both properties are crucial requirements for legitimate relation decompositions.

Lossless-Join Decomposition Example

  • Relation example:

    • Attributes: fName, lName, height, weight

    • Instance 1: James Bond, 190, 75

    • Instance 2: Natalie Bond, 180, 65

    • Decomposed Relations:

    • S(fName, lName)

    • T(lName, weight)

    • Condition for Lossless-Join: If relation R(A, B, C) is decomposed into S(A, B) and T(A, C), it is a lossless-join if either $A
      ightarrow B$ or $A
      ightarrow C$ holds.

Example of Lossless Join

  • Given Relation: Enrolment(id, name, unitNo, unitName, DOB, telephone, ..)

  • Possible decompositions:

    • A(id, name, unitNo, DOB, telephone, ..) ➔ Likely lossless

    • B(unitNo, unitName) ➔ Likely lossless

    • C(id, name, unitNo, DOB, telephone, ..)

    • D(DOB, unitName) ➔ Not lossless due to redundancy in DOB.

Dependency Preservation

  • Given the relation R(saleNo, item, price):

    • Functional dependencies include:

      • $saleNo
        ightarrow item, price$

      • $item
        ightarrow price$

  • If R is decomposed into S(saleNo, item) and T(item, price) and all functional dependencies are verifiable on S and T, then Dependency Preservation is satisfied.

Identify Functional Dependencies

  • Most functional dependencies (FDs) arise from business rules or sample data.

  • Importance of establishing sample data values represents every possible relationship.

Functional Dependencies Identified

  • Given attributes A, B, C, D, and E, example values can lead to the identification of potential functional dependencies, such as:

    • $A
      ightarrow C$

Process of Normalisation Steps

  • Definitions:

    • UNF: A table that contains one or more repeating groups.

    • 1NF: A relation is in First Normal Form if every attribute has a single value.

    • 2NF: A relation is in Second Normal Form if it is in 1NF and every non-primary-key attribute is fully functionally dependent on the primary key.

    • 3NF: A relation is in Third Normal Form if it is in 2NF and no non-primary-key attribute is transitively dependent on the primary key.

Corporate Data Model

  • A comprehensive data model that attempts to encapsulate all data elements, types, and definitions used in an organization. This requires maintenance from staff as new data elements are created.

Example of Unnormalised Table

  • Already defined a table with repeating groups and transformed it into 1NF:

    • Unnormalised Form example:

    • PK: studentID, studentName, unitsDone

From UNF to 1NF

  • Transforming initial raw data into a standard table format.

  • Resulting Table in 1NF: Removing multi-valued attributes and ensuring a unique value in each cell.

First Normal Form (1NF)

  • Definition: A relation is in 1NF if it contains no repeating groups; all attributes must have atomic values.

  • Example:

    • SchoolId, Name, NumStaff, Campuses - captured in a single-valued format.

From 1NF to 2NF

  • Identify the primary key and functional dependencies.

  • Remove any partial dependencies by creating new relations as needed.

Second Normal Form (2NF)

  • Simple definition along with examples to illustrate how attributes should be fully functionally dependent on the primary key only.

From 2NF to 3NF

  • Continue identifying primary keys and their functional dependencies.

  • Remove transitive dependencies.

SQL - Views

  • Definition: Views are virtual tables created using one or more SQL queries. They can simplify queries and enhance security.

  • Example SQL commands for creating and using views.

Conclusion

  • The normalisation process ensures that database relations are structured appropriately to minimize redundancy and potential issues with data integrity.