Unfortunately I missed Manoj’s 4th lecture, the notes however are online and I will run through a summary of them.

Part 1: Normalization

Data normalization is required to ensure that a relational database will perform UPDATE, CREATE and DELETE queries without anomalies (loss of data integrity).

The process of normalization has numerous levels (1st Normal Form [1NF], 2NF, 3NF, BCNF, 4NF, 5NF) which will be shown below. Prior to running through each level, the terminology for dependencies must be clarified.

Functional Dependence: A relationship between two attributes each value of A is associated with exactly 1 value of B, ie; ID is a determinant of Name. Partial Key dependencies are determinant relationships between two or more non-primary key attributes(leading to update anomalies). Transitive Key dependencies occur when an attribute is more immediately identified be a non-Primary Key attribute/s (leading to update anomalies).

1NF: Ensuring atmoic values in the relation by removing unnecessary attributes, separating relations if repeating values occur.

Now in 1NF :

2NF: Remove partial key dependencies (ensure primary key is in its minimal form and partially dependent attributes are removed to an alternate relation). Ie:

Now in 2NF:

3NF: Remove transitive keys dependencies.

Now in 3NF (Transitive dependency removed by creating new relation):

Boyce-Codd Normal Form [BCNF]: 3NF + only a single candidate key for each relation.

4NF: No non-trivial multi-valued dependencies. *

5NF: No join dependencies. *

* It seems we will not be covering 4NF and 5NF in this subject although other lecturers have mentioned they are the most prevalent in the business world.

That brought to a close the section on Normalization, next up was ER/EER -> Relational Model. Again as I did not attend this lecture I will just simply run through a summary of Manoj’s slides.

Inputs: ER/EER diagrams -> Outputs: relational schemas

Properties of Relations:

  • Each relation has a unique name..
  • Each tuple is unique
  • Attributes names are meaningful
  • Order of attributes is immaterial
  • Order of tuples is immaterial
  • entries are atomic (single valued)

Types of entities -> Regular(Strong), Weak, Associative(Composite).

In preparation for the transition from ER model to relation schema, the ER model must be expanded from conceptual to logical level. This involves removal of M:N relationships (Unary, Binary or Ternary) and recognition of Primary Key.

There was much more detail in this lecture but I will await the next presentation to elaborate further.