1 / 13

Databases – Attributes & Entities Normalisation

Databases – Attributes & Entities Normalisation It is important in Database Design to make sure that the correct attributes are grouped into the correct tables. Normalisation is a process that allows us to do this. This reduces the duplication of data and helps stop inconsistencies.

zavad
Download Presentation

Databases – Attributes & Entities Normalisation

An Image/Link below is provided (as is) to download presentation Download Policy: Content on the Website is provided to you AS IS for your information and personal use and may not be sold / licensed / shared on other websites without getting consent from its author. Content is provided to you AS IS for your information and personal use only. Download presentation by click this link. While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server. During download, if you can't get a presentation, the file might be deleted by the publisher.

E N D

Presentation Transcript

  1. Databases – Attributes & Entities Normalisation It is important in Database Design to make sure that the correct attributes are grouped into the correct tables. Normalisation is a process that allows us to do this. This reduces the duplication of data and helps stop inconsistencies. Normalisation is done in three phases – 1NF, 2NF and 3NF. These are as follows 1NF – Atomic Data Test 2NF – Partial key dependence test 3NF – Non-key dependence test

  2. Databases – Attributes & Entities Normalisation Consider this table: There are multiple entries for some of the attributes. How much space should we leave for these. These attributes are said to be “repeating groups” or non-atomic data. Lets remove them to another table.

  3. Databases – Attributes & Entities Normalisation Table 1 in 1NF

  4. Databases – Attributes & Entities Normalisation Table 2 in 1NF Order number and Item code make a composite key.

  5. Databases – Attributes & Entities Normalisation Which of these tables are NOT in first normal form?

  6. Databases – Attributes & Entities Normalisation Moving to 2NF The item details are only dependant on the item code which is part of the key and not the OrderNumber as well. This also means we only store details of the items when we order them. To make it 2NF all the attributes must be dependant on the full key. We create another table for the items.

  7. Databases – Attributes & Entities Normalisation Table 1 in 2NF

  8. Databases – Attributes & Entities Normalisation Table 2 in 2NF

  9. Databases – Attributes & Entities Normalisation Table 3 in 2NF

  10. Databases – Attributes & Entities Normalisation Moving to 3NF If we go back to first table - The primary key here is the OrderNumber. However the Delivery Address and Email Address is dependant on the Customer ID. So this table is not in Third Normal Form (3NF) because it fails the non-key dependance test. So we must create a new table for the customer information.

  11. Databases – Attributes & Entities Normalisation Let us have a look at our 4 tables now in 3NF.

  12. Databases – Attributes & Entities Normalisation Let us have a look at our 4 tables now in 3NF.

  13. Databases – Attributes & Entities • Normalisation • Tasks • Can you write the new tables in Database notation? • Can you create an Entity Relationship Diagram for the new Customer Order System? • Have a go at the scenarios below. Can you put the following flat files into 3NF. • CAR (Make, Model, Name of part, Price, Supplier name, supplier telephone number, Supplier Address) • RACES (Race name, venue, start point, end point, miles, runner name, time to finish, club name, club address, club telephone number) • Runners only belong to one club.

More Related