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.
CUSTOMER (cust-id, surname, initials,{order-no, order-date})
//{} signify repeating values.
Now in 1NF :
CUSTOMER(cust-id, surname, initials)
ORDER(cust_id, order-no, order-date)
2NF: Remove partial key dependencies (ensure primary key is in its minimal form and partially dependent attributes are removed to an alternate relation). Ie:
ORDER-LINE(order_number, product_no, description, number-ordered)
// As above, the description attribute is dependent only on product_no
making it a partial key dependency.
Now in 2NF:
ORDER-LINE(order_number, product_no, number-ordered)
ORDER-LINE(product_no, description)
3NF: Remove transitive keys dependencies.
ORDER(order-no, order-date, cus-id, name, street, suburb)
//name, street and suburb transitively dependent on cus-id
Now in 3NF (Transitive dependency removed by creating new relation):
ORDER(order-no, order-date, cus-id)
CUSTOMER(cus-id, name, street, suburb) // new relation added to eliminate transitive dependency.
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.