1 / 17

Pheonix Circus School

Pheonix Circus School. Normalisation Practice. Pheonix Circus School. Your task is to analyse the data requirements and produce a workable database design through applying the normalisation process You will work in pairs/groups………. Normalisation - Stage 1.

wood
Download Presentation

Pheonix Circus School

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. Pheonix Circus School Normalisation Practice

  2. Pheonix Circus School • Your task is to analyse the data requirements and produce a workable database design through applying the normalisation process • You will work in pairs/groups………

  3. Normalisation - Stage 1 • Study the worksheet and make sure you understand the information you have been given • Arrange all the attributes in the first column on your board

  4. Normalisation – Stage 1 • Looking at the data and discuss / agree on the answers to the following questions: • Which group of attributes are repeated? • Which group of attributes are not repeated? • From the non-repeating attributes which is a suitable primary key?

  5. Establishing 1NF • Separate the repeating attributes and non repeating attributes into 2 tables • Identify the primary key of the table containing the non-repeating attributes • Identify the primary key of the table containing the repeating attributes • Add the primary key of the non-repeating attributes table to the other table to form a composite key

  6. Pheonix Circus School – 1NF • Your data should now be in 1NF • Make sure your group agree and can explain your choices • We will check before moving on…….

  7. 1NF Solution

  8. 1NF Solution

  9. Establishing 2NF • A table with a single primary key is already in 2NF so look at tables with composite keys • Remove part key dependencies…….. • Does each attribute depend on both parts of the key? • if yes then keep it • if no then remove it from the current table and place in a new table • Give the new table a primary key (it will be one of the attributes in the composite key)

  10. Pheonix Circus School – 2NF • Your data should now be in 2NF • Make sure your group agree and can explain your choices • We will check before moving on…….

  11. 2NF Solution

  12. 2NF Solution

  13. Establishing 3NF • Remove non-key dependencies • For each table • look at each attribute and decide if it depends on the key or another attribute • remove any non-dependent attributes to a new table along with a copy of the attribute it depends on (the new primary key) • Indicate a link between the tables by assigning a foreign key/primary key pairing (primary key in new table, foreign key in old table) • Name each table

  14. Pheonix Circus School – 3NF • Your data should now be in 3NF • Make sure your group agree and can explain your choices • We will check before moving on…….

  15. 3NF Solution

  16. 3NF Solution

  17. Process Summary • UNF • identify all attributes present • 1NF • remove repeating attributes into a table • place non-repeating attributes into a table • identify the primary keys • 2NF • remove part key dependencies • 3NF • remove non-key dependencies • name all tables

More Related