relational database systems n.
Download
Skip this Video
Loading SlideShow in 5 Seconds..
Relational Database Systems PowerPoint Presentation
Download Presentation
Relational Database Systems

Loading in 2 Seconds...

play fullscreen
1 / 26

Relational Database Systems - PowerPoint PPT Presentation


  • 44 Views
  • Uploaded on

Relational Database Systems. Bit and Bobs Worked Example. 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.

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

PowerPoint Slideshow about 'Relational Database Systems' - ira


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
relational database systems

Relational Database Systems

Bit and Bobs Worked Example

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