Week 06 Normalisation INFOSYS 222 Agenda Normalisation and

Week 06 - Normalisation INFOSYS 222

Agenda • Normalisation and normal forms • 1 NF, 2 NF and 3 NF • Practice on normalisation with previous exam questions

Normalisation • What is normalisation? • Why do we need normalisation? • What would we achieve at the end of normalisation? • What are 1 NF, 2 NF and 3 NF? • What are functional dependencies? • What are partial functional dependencies? • What are transitive functional dependencies

Normalisation • Normalisation is a process that “improves” a database design by generating relations that are of higher normal forms. • The objective of normalisation: • “to create relations where every dependency is on the key, the whole key, and nothing but the key”.

Work Allocation Chart emp_N rank_I rank_De salary No ame d sc project_ No project_Desc project_Start _Date 123 John 01 Sr. Engr 52 K ABC 34 Waste disposal 01/03/15 456 Alan 03 App. Spec 35 K QXN 88 Sludge treatment 20/06/15 555 Peter 02 Engr 40 K ABC 34 Waste disposal 12/03/15 467 Jane 03 App. Spec 35 K BAT 20 N 2 O Generation 09/05/15 555 Peter 02 Engr 40 K QXN 88 Sludge treatment 15/06/15 123 John 01 Sr. Engr 52 K BAT 20 N 2 O Generation 03/05/15

Functional dependencies • We say an attribute, B, has a functional dependency on another attribute, A, if for any two records, which have • the same value for A, then the values for B in these two records must be the same. We illustrate this as: • A B Example: We only track one name for each employee. Suppose each employee is identified by their unique employee number. We say there is a functional dependency of name on employee number: employee number employee name

Functional dependencies If emp_No is the PK then the FDs: emp_No emp_Name emp_No rank_Desc emp_No salary must exist. • emp_No is not the PK : A row is not unique • emp_No and project_No : is unique

Partial dependency A partial dependency exists when an attribute B is functionally dependent on an attribute A, and A is a component of a multipart candidate key. emp_No project_Start. Date project_Desc Candidate keys: {emp_No, project_No} project_Desc is partially dependent on {emp_No, project_No} as project_No is a determinant of project_Desc and project_No is part of a candidate key

Transitive dependency Consider attributes A, B, and C, and where A B and B C. Functional dependencies are transitive, which means that we also have the functional dependency A C We say that C is transitively dependent on A through B.

Transitive dependency emp_No rank_Id emp_No emp_Name rank_Id rank_Desc rank_Id rank_Desc emp_No emp_Name rank_Id rank_Desc is transitively dependent on emp_No via rank_Id emp_No rank_Desc

INFOSYS 222 AC 2007 Exam 101 • Derive a set of relation in 3 NF. Show each step of normalisation clearly. All PKs should have a solid underline and FKs a dashed line. Draw an ERD for the derived relations

INFOSYS 222 AC 2016 Exam

INFOSYS 222 AC 2016 Exam • Derive a set of relations in third normal form (3 NF), using the data shown in figure above Clearly show each step of normalisation – 1 NF, 2 NF and 3 NF