10. 3NF Examples

Reminder, 3NF means:

  • It is already in 2NF
  • There are no non-key attributes that depend on another non-key attribute

Example 1

CUSTOMER

CustomerID Firstname Surname City PostCode
12123 Harry Enfield London SW7 2AP
12443 Leona Lewis London WC2H 7JY
354 Sarah Brightman Coventry CV4 7AL

This is not in strict 3NF as the City could be obtained from the Post code attribute. If you created a table containing postcodes then city could be derived.

CustomerID Firstname Surname PostCode*
12123 Harry Enfield SW7 2AP
12443 Leona Lewis WC2H 7JY
354 Sarah Brightman CV4 7AL

POSTCODES

PostCode City
SW7 2AP London
WC2H 7JY London
CV4 7AL Coventry

 

Example 2.

VideoID Title Certificate Description
12123 Saw IV 18 Eighteen and over
12443 Igor PG Parental Guidance
354 Bambi U Universal Classification

The Description of what the certificate means could be obtained frome the certifcate attribute - it does not need to refer to the primary key VideoID. So split it out and use the primary key / secondary key approach.

Example 3

CLIENT

ClientID CinemaID* CinemaAddress
12123 LON23 1 Leicester Square. London
12443 COV2 34 Bramby St, Coventry
354 MAN4 56 Croydon Rd, Manchester

CINEMAS

CinemaID CinemaAddress
LON23 1 Leicester Square. London
COV2 34 Bramby St, Coventry
MAN4 56 Croydon Rd, Manchester

In this case the database is almost in 3NF - for some reason the Cinema Address is being repeated in the Client table, even though it can be obtained from the Cinemas table. So simply remove the column from the client table

Example 4

ORDER

OrderID Quantity Price Cost
12123 2 10.00 20.00
12443 3 20.00 60.00
354 4 30.00 120.00

In this case the cost of any order can be obtained by multiplying quantity by price. This is a 'calculated field'. The database is larger than it needs to be as a query could work out the cost of any order. So to be in strict 3NF you would remove the Cost column.

 

 

Copyright © www.teach-ict.com