Logical model: normalisation

Logical modelIt is imperative that the tables in our database are well structured. One method of ensuring this is normalisation. This topic explores the normalisation process and provides you with some practice of the technique.

 

 

What is normalisation?

Textbook reading
Ch4 pp 216-225

Normalisation is an alternative database design technique that examines the relationships (functional dependencies) between attributes. It is important to understand normalisation, as it is an effective tool in determining if a database is well-designed. The textbook reading, Introduction to Normalization and Normalization Example: Pine Valley Furniture Company provides a good introduction to normalisation and explains the process of normalisation.

This unit will only cover 1st, 2nd and 3rd normal form as 3NF is the most commonly used level of normalisation.

Whenever an ERD is mapped to a logical schema, it should always be checked to ensure that it is in 3NF. A well thought out and drawn E-R diagram that is mapped correctly will invariably be in 3NF. The reading, Schemas, shows how you can indicate that a logical schema is in 3NF.

 

Getting your hands dirty

Spare parts

The table below contains sample data for parts and for vendors who supply those parts. In discussing this data with users, we find that part numbers (but not descriptions) uniquely identify parts, and that vendor names uniquely identify vendors.

Spare parts

1. Convert this table to a relation (named PART SUPPLIER) in 1NF. Illustrate the relation with the sample data in the table.

View the answer

Part supplier

2. List the functional dependencies in PART SUPPLIER and identify a candidate key.

View the answer

Functional dependencies

3. Identify each of the following anomalies:

  • an insert anomaly,
  • a delete anomaly, and
  • a modification anomaly.

View the answer

Insert: We cannot insert a new vendor unless we also include a part number
Delete: If we delete part information, we also lose information about a vendor who supplies that part.
Modification: If a vendor’s address changes, we have to modify all records (or rows) for that vendor.

4. Draw a relational schema for PART SUPPLIER and show the functional dependencies. In what normal form is this relation?

View the answer

1st Normal Form

5. Develop a set of 3NF relations from PART SUPPLIER, showing functional and referential integrity constraints.

View the answer

3NF

 

Grade report

The table below contains sample data for student grades.

Grade report

1. Draw a relational schema and diagram the functional dependencies in the relation.

View the answer

Schema

2. In what normal form is this relation?

View the answer

1st Normal Form (1NF)

3. Decompose GRADE REPORT into a set of 3NF relations.

View the answer

Grade report

4. Draw a relational schema for your 3NF relations and show the functional and referential integrity constraints.

View the answer

Relational schema

Book Report

The table below contains sample data of books.

BookTitle Author BookType ListPrice Author_Nationality Publisher
ModernDB
Management
Hoffer,
Prescott,
McFadden
Textbook   $100   American
  Canadian
  Canadian
Pearson
DB Systems Elmasri,
Navathe
Textbook   $100   Canadian
  American
Addison
Harry Potter JK Rowling Fiction   $30   British Bloomsbury

Note for this relation:

  • the book title is unique across all publishers
  • the Book Type determines the List Price, that is all textbooks sell for $100 and Fiction for $30

1. Determine functional dependencies

2. Show 1NF, 2NF, 3NF

 The Business Rules change the tables  

  • the book title is still unique across all publishers but
  • the List Price is different for each book (that is not related to the book type)

3. Show 1NF, 2NF, 3NF

 

Check

The Answers to these will be available in Weekly Tasks normalisation.
The specific day is mentioned in announcements.