normalisation n.
Download
Skip this Video
Loading SlideShow in 5 Seconds..
Normalisation PowerPoint Presentation
Download Presentation
Normalisation

Loading in 2 Seconds...

play fullscreen
1 / 25

Normalisation - PowerPoint PPT Presentation


  • 88 Views
  • Uploaded on

Normalisation. Un-normalised Form (UNF). Identify an entity List all the attributes Identify a key. Un-normalised Form (UNF). Identify repeating data items. Un-normalised Form (UNF). Identify repeating data items. First Normal Form (1NF). Remove repeating data items to form a new entity

loader
I am the owner, or an agent authorized to act on behalf of the owner, of the copyrighted work described.
capcha
Download Presentation

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.While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server.


- - - - - - - - - - - - - - - - - - - - - - - - - - E N D - - - - - - - - - - - - - - - - - - - - - - - - - -
Presentation Transcript
un normalised form unf
Un-normalised Form (UNF)
  • Identify an entity
  • List all the attributes
  • Identify a key
un normalised form unf1
Un-normalised Form (UNF)

Identify repeating data items

un normalised form unf2
Un-normalised Form (UNF)
  • Identify repeating data items
first normal form 1nf
First Normal Form (1NF)
  • Remove repeating data items to form a new entity
  • Take the key with you!
first normal form 1nf1
First Normal Form (1NF)
  • Remove repeating data items to form a new entity
  • Take the key with you!
first normal form 1nf2
First Normal Form (1NF)
  • Identify a key for the new entity
  • It will be a compound key
  • Use the original key and add to it
first normal form 1nf3
First Normal Form (1NF)
  • Identify a key for the new entity
  • It will be a compound key
  • Use the original key and add to it
  • Label the foreign key
  • Order Number is both part of the compound primary key and also a foreign key.
first normal form 1nf4
First Normal Form (1NF)
  • A data model is in 1NF if it has no multi-valued attributes
first normal form 1nf6
First Normal Form (1NF)

But what if there were lots of orders for large deluxe red widgets…?

There are still update anomalies

second normal form 2nf
Second Normal Form (2NF)
  • Examine any entity with a compound key (in this case ORDER_ITEM)
  • See if any attributes are dependent on just one part of the compound key
  • These are called partial dependencies
second normal form 2nf1
Second Normal Form (2NF)
  • Order Number is part of the key
  • Item Code is part of the key
  • Description is dependent on the Item Code
  • Unit Cost is dependent on the Item Code
  • Quantity is dependent on both Order Number and Item Code.
second normal form 2nf2
Second Normal Form (2NF)
  • Description and Unit Cost are partial dependencies
  • They are dependent on Item Code
  • Remove these attributes to a new entity
  • Take a copy of the attribute they are dependent on
second normal form 2nf3
Second Normal Form (2NF)
  • Item Code becomes the key of the new entity
  • And becomes a foreign key in ORDER-ITEM
second normal form 2nf4
Second Normal Form (2NF)
  • A data model is in 2NF if it is in 1NF and there are no partial dependencies
second normal form 2nf5
Second Normal Form (2NF)
  • We can add an item to the Item table without it having to be on an order
  • We can delete an order in the Order table without deleting details of the items on the order
  • We can update item details once in the Item table without affecting the orders for that item in the Order-Item table
second normal form 2nf6
Second Normal Form (2NF)
  • But there are still update anomalies with the Order entity
third normal form 3nf
Third Normal Form (3NF)
  • Examine all the entities produced so far
  • See if there are any non-key attributes which are dependent on any other non-key attributes
  • These are called non-key dependencies
third normal form 3nf1
Third Normal Form (3NF)
  • In the ORDER entity, Customer Name, Address, Post Code and Telephone Number are all dependent on Customer Number
third normal form 3nf2
Third Normal Form (3NF)
  • Remove these attributes to a new entity
third normal form 3nf3
Third Normal Form (3NF)
  • Remove these attributes to a new entity
  • Customer Number is the key of the new entity
  • Leave Customer Number behind as a foreign key
third normal form 3nf4
Third Normal Form (3NF)
  • A data model is in 3NF if it is in 2NF and there are no non-key dependencies
third normal form 3nf5
Third Normal Form (3NF)
  • We can add a customer to the Customer table without the customer having to place an order
  • We can delete an order in the Order table without deleting details of the customer who placed the order
  • We can update a customer’s details once in the Customer table without affecting the orders placed by that customer in the Order table
memory aid
Memory Aid
  • In 3NF, each attribute is dependent on
  • the key
  • the whole key
  • and nothing but the key