What is normalisation?
Ch4 pp 209-212
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.
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.
1. Convert this table to a relation (named PART SUPPLIER) in 1NF. Illustrate the relation with the sample data in the table.
2. List the functional dependencies in PART SUPPLIER and identify a candidate key.
3. Identify each of the following anomalies:
- an insert anomaly,
- a delete anomaly, and
- a modification anomaly.
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?
5. Develop a set of 3NF relations from PART SUPPLIER, showing functional and referential integrity constraints.
Grade report
The table below contains sample data for student grades.
1. Draw a relational schema and diagram the functional dependencies in the relation.
2. In what normal form is this relation?
1st Normal Form (1NF)
3. Decompose GRADE REPORT into a set of 3NF relations.
4. Draw a relational schema for your 3NF relations and show the functional and referential integrity constraints.
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.
