Week 06 Lab Normalization INFOSYS 222 INFOSYS 222

Week 06 Lab – Normalization INFOSYS 222

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

1 NF Promotion(Promotion. ID, Promotion. Date, Promotion. Subject, Branch. No, Branch. Name, Feature. Category. ID, Feature. No, Feature. Desc, Cost) Note: no derived attributes are included

2 NF • Partial functional dependencies: – Promotion. ID Promotion. Date, Promotion. Subject – Branch. No Branch. Name Promotion(Promotion. ID, Promotion. Date, Promotion. Subject) Branch(Branch. No, Branch. Name) Marketing(Promotion. ID, Branch. No, Feature. Category. ID, Feature. No, Feature. Desc, Cost)

3 NF • Transitive functional dependencies: – Feature. No Feature. Desc – Feature. Category. ID Promotion(Promotion. ID, Promotion. Date, Promotion. Subject) Branch(Branch. No, Branch. Name) Marketing(Promotion. ID, Branch. No, Feature. No, Cost) Feature(Feature. No, Feature. Desc, Feature. Category. ID) Feature. Category(Feature. Category. ID)

A normalised ERD

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

1 NF • Rental (Customer. No, Customer. Name, Customer. Address, Customer. Category, Category. Discount, Painting. No, Painting. Title, Date. Of. Hire, Date. Due. Back, Return. Flag)

2 NF • Rental (Customer. No Painting. No Date. Of. Hire, Date. Due. Back, Return. Flag) • Customer (Customer. No, Customer. Name, Customer. Address, Customer. Category, Category. Discount) • Painting (Painting. No, Painting. Title)

3 NF • Rental (Customer. No, Painting. No, Date. Of. Hire, Date. Due. Back, Return. Flag) • Customer (Customer. No, Customer. Name, Customer. Address, Customer. Category. ID ) • Painting (Painting. No, Painting. Title) • Customer. Category (Customer. Category. ID, Customer. Category, Category. Discount)