Week 6

• Database Design - Normalisation


  The process of transforming ER data models into well-formed relational designs by undergoing data normalisation
• What you need to know


  • Learn more about transforming ER data models into relational designs
  • Turn customer's requirements into well-formed relations by undergoing data normalisation
• Normalisation


  The process of decomposing relations with anomalies to produce smaller, well-structured relations that are not susceptible to anomalies
• Goals of Normalisation


  • Remove data redundancy
  • Eliminate Insertion Anomalies, Deletion anomalies and Update anomalies
  • Ensure data dependencies make sense
• A well-structured relation usually contains only one business theme (one entity)
• Normalisation Principles


  • Turn every attribute into a Candidate key (so that it can be considered as a primary key)
  • Break down relations that are not well-formed into two or more well-formed relations
• Normalisation


  • Organise data efficiently
  • Eliminate redundant data
  • Ensure that only related data are stored in a table
• A database in 3NF (or above) is generally not susceptible to modification anomalies
• Un-normalised Form (0NF or UNF)


  • Data might be repeated
  • Data might be non-atomic
  • The table might not have a primary key
  • There might be repeating fields (i.e. each field does not have a unique name)
• First Normal Form (1NF)


  • All rows in a table must be unique (no duplicate rows)
  • Every attribute value must be atomic (can only be single value and non-divisible)
• Tables that are classified as relations must also be in 1st Normal Form
• Second Normal Form (2NF)


  • The relation must already be in 1NF
  • Every non-key attributes must be fully functionally dependant on the entire primary key (no partial dependencies)
• Partial dependencies violate 2NF
• Third Normal Form (3NF)


  • The relation must already be in 1NF and in 2NF
  • No transitive dependencies (every non-key attribute must NOT be determined by other non-key attributes)
• Transitive dependencies violate 3NF
• pan


  Third Normal Form (3NF)
• Third Normal Form (3NF)


  • The relation must already be in 1NF and in 2NF
  • No transitive dependencies
• Transitive dependency


  Every non-key attribute must NOT be determined by other non-key attributes (must be determinable by primary/composite key)
• This table violates 3NF
• Country is only dependent on Winner (a non-key attribute)


  Remove the transitive dependency
• Remove the transitive dependency


  Break down into 2 smaller relations
• Smaller relations


  • Each smaller relation with attributes only dependent on Primary key
• Normalisation Template


  • 0NF
  • 1NF
  • 2NF
  • 3NF
• 0NF: List of un-normalised attributes showing the chosen key field and repeating groups
• 1NF: Atomic Values and Unique Identifiers. Multivalued attributes have to be in atomic form. Mixing data types within the same column is not permitted. Each table must have a Primary key (either a simple primary key or a composite Primary key). Repeating groups are not permitted. Derived attributes should be omitted.
• 2NF: All Data must depend on the whole Primary key. This means each non-key attribute in a table must be dependent on the entire primary key. For a table with a composite key, then each non-key attribute must be dependent on the entire composite key.
• 3NF: Each non-key attribute in a table can't be dependent on another non-key attribute
• Normalisation Steps


  • Step A: represent the un-normalised data in tabular form
  • Step B: convert 0NF to 1NF
  • Step C: convert 1NF to 2NF

  
• Repeating groups have to be separated into a second table in 1NF
• @ means Foreign key
• The PK for the Enrolment table is a composite key of (ModuleCode, studentID)
• Foreign Key: An attribute, or set of attributes, within one relation that matches the candidate key of some (possible the same) relation
• Foreign Key


  An attribute, or set of attributes, within one relation that matches the candidate key of some (possibly the same) relation
• Convert 1NF to 2NF


  1. Remove partial dependencies

  2. Break down into smaller relations

  3. Each relation has attributes only dependent on primary key

  
• Partial dependency: ModuleTitle, lecturerCode, Lecturer are only dependent on part of the composite key of (ModuleCode, studentID)
• Second Normal Form (2NF)


  • The data must already be in 1NF
  • Non-key attributes must depend on every part of the primary key
• Convert 2NF to 3NF


  1. Remove transitive dependencies

  2. Break down into smaller relations
• Third Normal Form (3NF)


  • The data must already be in 2NF
  • There are no non-key attributes dependent on another non-key attribute
• Transitive dependency: Lecturer is only dependent on LecturerCode (a non-key attribute)
• 0NF to 3NF Schema


  • STUDENT[ studentID, firstName, lastName, Gender, streetName, City ]
  • ENROLMENT[ studentID@, moduleCode@ ]
  • COURSE[ moduleCode, moduleTitle, lecturerCode@ ]
  • LECTURER[ LecturerCode, Lecturer ]