Database Normalization: Understanding 2NF and 3NF

Normalisation

Normalising a Database to 3NFWhat is Normalisation?

• Organises data into a set of related tables.

Emp Id Name Surname Dept Id Department 10095M James Abela 100 HR 19391M Andrew Grech 100 HR 38988M Anna Saliba 200 IT Emp Id Name Surname Dept Id 10095M James Abela 100 19391M Andrew Grech 100 38988M Anna Saliba 200 Dept Id Department 100 HR 200 ITBenefits of Normalisation?

• Remove data redundancy / duplication
• Minimise storage space
• Remove (or reduce) integrity problemsAnomalies
• Anomalies are mistakes in data which is stored in an un-normalised relational database. . There are three main types:
• Insert Anomalies
• Update Anomalies
• Delete AnomaliesInsert Anomaly

Employee ID First Name Last Name Department Phone Department Name 10088M Alex Borg 21256888 IT 10286M Andrea Saliba 21256881 Accounts 57390M Peter Grech 21256888 IT A new record is entered Employee ID First Name Last Name Department Phone Department Name 10088M Alex Borg 21256888 IT 10286M Andrea Saliba 21256881 Accounts 57390M Peter Grech 21256888 IT 285378M Sandra Debono 21256881 Accounting It is entered differently!Update Anomaly

Employee ID First Name Last Name Department Phone Department Name 10088M Alex Borg 21256888 IT 10286M Andrea Saliba 21256881 Accounts 57390M Peter Grech 21256888 IT 285378M Sandra Debono 21256881 Accounting Telephone for IT department needs to be changed Employee ID First Name Last Name Department Phone Department Name 10088M Alex Borg 21256822 IT 10286M Andrea Saliba 21256881 Accounts 57390M Peter Grech 21256888 IT 285378M Sandra Debono 21256881 Accounting Only changed in one place, hence incorrect data!Delete Anomaly

Employee ID First Name Last Name Department Phone Department Name 10088M Alex Borg 21256888 IT 10286M Andrea Saliba 21256881 Accounts 57390M Peter Grech 21256888 IT Andrea Saliba left, and her record will be deleted Employee ID First Name Last Name Department Phone Department Name 10088M Alex Borg 21256888 IT 57390M Peter Grech 21256888 IT Although deleted correctly, the details for the Accounts department are lost as well! Department Phone Department Name 21256881 AccountsNormalisation Process . In order to fully normalise a database, there are various stages. . There are five common ones. The focus is on the first three:

• 1NF - Remove Repeating Groups
• 2NF - Eliminate Partial Dependencies
• 3NF - Eliminate Transitive DependenciesNormalisation - 1NF
• Problems to resolve:
• Multivalued Attributes . A field that contains more than one of the same value. . For instance in a field called colour one cannot have Red, Green, Blue but only one of them.
• Composite Attributes . A field that contains a group of data that can be split (refined) into other fields . For instance Full Name can be split in Name and Surname
• Different data types within same column . All values in the same column must have the same data type . For instance in a column Price, one cannot have a price typed as 1.50 and another one as two Euro
• Find a primary keyNormalisation - 1NF
• Problems with this table?

Supplier Supplier Address Products Price Fresh Products 6, Triq il-Ward, San Pawl Lettuce, Rucola, Tomatoes 0.65, 1.20, 1.5 Zaren Farms 10, Triq in-Naspli, Mellieha Eggs One euroNormalisation - 1NF

Problems with Table Data

• Problems with this table?

Multivalued Field Supplier Supplier Address Products Price Fresh Products 6, Triq il-Ward, San Pawl Lettuce, Rucola, Tomatoes 0.65, 1.20, 1.5 Zaren Farms 10, Triq in-Naspli, Mellieha Eggs One euro Multivalued Field Composite Field Data TypeNormalisation - 1NF

Supplier ID Supplier Door Street Locality Product ID (PK) Product Price 1 Fresh Products 6 Triq il-Ward San Pawl 1 Lettuce 0.65 1 Fresh Products 6 Triq il-Ward San Pawl 2 Rucola 1.20 1 Fresh Products 6 Triq il-Ward San Pawl 3 Tomatoes 1.5 4 Zaren Farms 10 Triq in-Naspli Mellieha 4 Eggs 1.00Normalisation - 1NF

Table Problems with Student Enrollment

• Problems with this table?

ID Number Full Name Locality DOB Course Enrolled 10095M Alex Borg Valletta 3/6/1981 ED in IT, 1/10/2014, Introduction to Art 2/10/2015 201495M Anna Gatt Sliema 3/5/1994 ED in IT 2/10/2014 29193M Lorna Zammit Valletta 6/4/1995 ED in Business 1/10/2014 89694M Jack Zarb Bormla 1/12/1996 ED in Business, 5/10/2015, Masonry Part II 1/6/2014Normalisation - 1NF

Student Enrollment Table Issues

• Problems with this table?

ID Number Full Name Locality DOB Course Enrolled 10095M Alex Borg Valletta 3/6/1981 ED in IT, 1/10/2014, Introduction to Art 2/10/2015 201495M Anna Gatt Sliema 3/5/1994 ED in IT 2/10/2014 29193M Lorna Zammit Jack Zarb Valletta 6/4/1995 ED in Business 1/10/2014 89694M Bormla 1/12/1996 ED in Business, 5/10/2015, Masonry Part II 1/6/2014 Composite Field Multivalued FieldNormalisation - 1NF

Student ID (PK) ID Number First Name Last Name Locality DOB Course Id (PK) Course Enrolled 1 10095M Alex Borg Valletta 3/6/1981 1 ED in IT 1/10/2014 1 10095M Alex Borg Valletta 3/6/1981 2 Introducti 2/10/2015 on to Art 2 201495M 29193M Anna Gatt Sliema 3/5/1994 1 ED in IT 2/10/2014 3 Lorna Zammit Valletta 6/4/1995 3 ED in 1/10/2014 Business 4 89694M Jack Zarb Bormla 1/12/1996 3 ED in 5/10/2015 Business 4 89694M Jack Zarb Bormla 1/12/1996 4 Masonry Part II 1/6/2014Normalisation - 2NF

Resolving Partial Dependency

• Problems to Resolve:
• Partial Dependency - occurs when a non-key field depends on part of the composite key

Order Id Product Id Price Quantity Order Id and Product Id are used as a composite key. Quantity depends on all of the composite key (Fully Dependent) Price depends only on just a part of the composite key Product Id (Partial Dependency)Normalisation - 2NF

Composite Key Identification

• To perform 2NF, there must be a composite key.
• To identify if a table requires a composite key, one must identify the relationship that will be generated by splitting the table (one-to-many, or many-to-many).

Many-to- Many 2NF One-to- Many 3NFNormalisation - 2NF

Supplier-Product Relationship

• What is the relationship between suppliers and products?
• A supplier can provide a number of products (e.g. Fresh Products provides Lettuce, Rucola an Tomatoes)
• A product is bought from one supplier only (e.g Rucola can only bought from Fresh Products)
• So, the relationship is one-to-many (one supplier, many products); hence we skip directly to 3NF

Product ID Supplier ID Supplier Door Street Locality Product Price (PK) Fresh 6 Triq il-Ward San Pawl 1 Lettuce 0.65 Products Fresh 1 6 Triq il-Ward San Pawl 2 Rucola 1.20 Products 1 Fresh 6 Triq il-Ward San Pawl 3 Tomatoes 1.5 Products 4 Zaren Farms 10 Triq in-Naspli Mellieha 4 Eggs 1.00 1Normalisation - 2NF

Student-Course Relationship

• What is the relationship between students and courses?
• A student can enrol into a number of courses (e.g. Alex Borg is enrolled in ED in IT, and Introduction to Art)
• A course can be enrolled by a number of students (e.g. ED in IT is enrolled by Alex Borg, and Anna Gatt) . So, the relationship is many-to-many (many students, many courses); hence we carry out 2NF

Student ID (PK ID Number First Name Last Name Locality DOB Course Id (PK) Course Enrolled 1 10095M Alex Borg Valletta 3/6/1981 1 ED in IT 1/10/2014 1 10095M Alex Borg Valletta 3/6/1981 2 Introduction to Art 2/10/2015 2 201495M Anna Gatt Sliema 3/5/1994 1 ED in IT 2/10/2014 3 29193M Lorna Zammit Valletta 6/4/1995 3 ED in Business 1/10/2014 4 89694M Jack Zarb Bormla 1/12/1996 3 ED in Business 5/10/2015 4 89694M Jack Zarb Bormla 1/12/1996 4 Masonry Part II 1/6/2014Normalisation - 2NF

Student and Course IDs

• The main elements in this table are the student and the course, hence we need an ID for each.
• Student Id serves as a primary key for student.
• Course Id serves as a primary key for the course.

Student ID (PK) ID Number First Name Last Name Locality DOB Course ID (PK) Course Enrolled 1 10095M Alex Borg Valletta 3/6/1981 1 ED in IT 1/10/2014 1 10095M Alex Borg Valletta 3/6/1981 2 Introduction to Art 2/10/2015 2 201495M Anna Gatt Sliema 3/5/1994 1 ED in IT 2/10/2014 3 29193M Lorna Zammit Valletta 6/4/1995 3 ED in Business 1/10/2014 4 89694M Jack Zarb Bormla 1/12/1996 3 ED in Business 5/10/2015 4 89694M Jack Zarb Bormla 1/12/1996 4 Masonry Part II 1/6/2014Normalisation - 2NF

Splitting Tables for Dependencies

• Check for partial dependencies
• Fully dependent on just Student Id: Id Number, First Name, Last Name, Locality, DOB
• Fully dependent on just Course Id: Course
• Fully dependent on the Composite Key: Enrolled . Hence we need to split into three tables.

Student ID First Last Locality DOB Course Course Enrolled ID (PK) Number Name Name ID (PK) 1 10095M Alex Borg Valletta 3/6/1981 1 ED in IT 1/10/2014 1 10095M Alex Borg Valletta 3/6/1981 2 Introduction to Art 2/10/2015 2 201495M Anna Gatt Sliema 3/5/1994 1 ED in IT 2/10/2014 3 29193M Lorna Zammit Valletta 6/4/1995 3 ED in Business 1/10/2014 4 89694M Jack Zarb Bormla 1/12/1996 3 ED in Business 5/10/2015 4 89694M Jack Zarb Bormla 1/12/1996 4 Masonry Part II 1/6/2014Normalisation - 2NF

Student ID ID Number First Name Last Name Locality DOB 1 10095M Alex Borg Valletta 3/6/1981 2 201495M Anna Gatt Sliema 3/5/1994 3 29193M Lorna Zammit Valletta 6/4/1995 1 ED in IT 4 89694M Jack Zarb Bormla 1/12/1996 2 Introduction to Art 3 ED in Business 4 Masonry Part II Student ID (FK) Course ID (FK) Enrolled 1 1 1/10/2014 1 2 2/10/2015 2 1 2/10/2014 3 3 1/10/2014 4 3 5/10/2015 4 4 1/06/2014 Course ID CourseNormalisation - 3NF

Resolving Transitive Dependency

• Problems to Resolve:
• Transitive Dependency - occurs when a non-key attribute depends on another non-key attribute, which in turn is fully dependent on the primary key.

Product ID Product Price Supplier ID Supplier Door Street Locality Product ID is the primary key in this table. Product and Price all depend on Product Id (Fully Dependent). Supplier, Door, Street and Locality depend on Supplier ID, which as we've seen depends on Product Id (Transitive Dependency).Normalisation - 3NF

Supplier-Product Relationship in 3NF

• What is the relationship between suppliers and products?
• A product is bought from one supplier only (e.g Rucola can only bought from Fresh Products)
• A supplier can provide a number of products (e.g. Fresh Products provides Lettuce, Rucola an Tomatoes)
• So, the relationship is one-to-many (one supplier, many products).

Product ID Supplier ID Supplier Door Street Locality Product Price (PK) Fresh 1 6 Triq il-Ward San Pawl 1 Lettuce 0.65 Products Fresh 6 Triq il-Ward San Pawl 2 Rucola 1.20 Products 1 6 Triq il-Ward San Pawl 3 Tomatoes 1.5 Products 4 Zaren Farms 10 Triq in-Naspli Mellieha 4 Eggs 1.00 1 Fresh