Database Technology

FIT9019: Week 4

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.

Leave a Reply

Your email address will not be published. Required fields are marked *