Lesson 12 – The Last 5 steps of Normalization
Steps 1-4.3-Part Primary Keys: Writing the UNF, 1NF 2NF and 3NF
Step 5.Simplify Primary Keys
Step 6.Merge Relationships from more than one User View (must have Identical Primary Key)
Step 7.Eliminate New Transitive Dependancies
Step 8.Identify all Foreign Keys.
Step 9.Explode Multi-attribute fields.
SUPREME ART GALLERY ONTARIO INC.
Customer Transaction History Report
Customer: CustPhone:
Jackson, Elizabeth (206) 284-6783
123 – 4th Avenue
Fonthill, ON M3J 4S4
Title Artist Trans.Date Salesrep#Sales Price .
321-Laugh with TeethCarol Channing02/01/201212$7000.00
349-The Hills of HomeCarol Channing02/01/201241$3400.00
444-South Toward Red SeaDennis Frings02/01/2012 12 ( $1800.00 )
672-At the MoviesCarol Channing 02/14/2012 33 ( $5550.00 )
444-South Toward Red SeaDennis Frings03/21/2012 33$2200.00
321-Laugh with TeethCarol Channing03/25/201241 ( $7000.00 )
Page 29 of 52
The SUPREMEArtGallery wishes to maintain data on their customers, artists and paintings. They want to be able to list the transactions of each customer. The user view above shows that the gallery may buy a painting or it can sell a painting. If the gallery sells a painting the Sales Price is shown as a positive value such as $7000.00. If the gallery purchases a painting it is shown as a negative value such as ($1800.00). Paintings may be purchased and sold many times, but the painting may change ownership only once a day.
UNF Relation :[Customer#, CDetails, ( Title#, Title, Artist, ( TDate, Salesrep#, Amount ) ) ]
Note: CDetails carries: { Cname, Cstreet, Ccity, CpostalCode, Cphone }
1NF Relations:[Customer#, CDetails ]
[Customer#, Title#, Title, Artist ]
[Customer#, Title#, TDate, Salesrep#, Amount ]
2NF: Relations:
[Customer#, CDetails ]
[Title#, Title, Artist ]
[Customer#, Title# ] ?????
[Customer#, Title#, TDate, Salesrep#, Amount ]
3NF Relations:
[Customer#,CDetails ]
[Title#, Title, Artist ]
[Customer#, Title#, TDate, Salesrep#, Amount ]
Step 5. Simplify Primary Keys(Customer# is determined by Title#, Tdate )
CUSTOMER [Customer#,CDetails]
TITLE[Title#, Title, Artist ]
TITLE_TDATE[Title#, TDate, Customer#, Salesrep#, Amount ]
USER VIEW #2
SUPREME ART GALLERY ONTARIO INC.
SalesReport
Trans.Date Title ArtistEmailSalesRepNameSales Price .
02/01/2012321-Laugh with ill Grinn$7000.00
349-The Hills of om Grimmace$3400.00
444-South Toward Red ill Grinn ( $1800.00 )
02/14/2012 672-At the velyn Frowen ( $5550.00 )
444-South Toward Red velyn Frowen$2200.00
03/25/2012321-Laugh with om Grimmace ( $7000.00 )
Page 29 of 52
UNF Relation :
[ Tdate, ( Title#, Title, ArtistEmail, SalesrepName, Amount ) ]
1NF Relations:
[ Tdate, Title#, Title, ArtistEmail, SalesrepName, Amount ]
2NF: Relations:
[ Title#, Title, ArtistEmail ]
[ Tdate, Title#, SalesrepName, Amount ]
3NF Relations:
No Change
Step 5. Simplify Primary Keys
(No Change)
TITLE[ Title#, Title, ArtistEmail ]
TDATE_TITLE[ Tdate, Title#, SalesrepName, Amount ]
Step 6. Merge tables wih Idential Primary Key
From UV 1:
A. CUSTOMER [Customer#, CDetails ]
B. TITLE[Title#, Title, Artist ]
C. TITLE_TDATE[Title#, TDate, Customer#, Salesrep#, Amount ]
From UV 2:
D. TITLE[ Title#, Title, ArtistEmail ]
E. TDATE_TITLE[ Tdate, Title#, SalesRepName, Amount ]
We can merge B and D (the PK is Title#)
We can also merge C and E as they have an identical 2-part PK (the order does’t matter)
We Get:
CUSTOMER [Customer#, CDetails ]
TITLE[Title#, Title, Artist, ArtistEmail ]
TITLE_TDATE[Title#, TDate, Customer#, Salesrep#, SalesRepName, Amount ]
Step 7: Eliminate New Transitive Dependancies
The TITLE table has a new transitive dependancy in it: There is an Artist table !!!!
The TITLE_TDATE table has a new transitive dependancy: There is a SalesRep table !!!!
We Get:
CUSTOMER [Customer#, CDetails ]
TITLE[Title#, Title, Artist (FK) ]
TITLE_TDATE[Title#, TDate, Customer#(FK), Salesrep# (FK), Amount ]
ARTIST [ Artist#, Artist, ArtistEmail ]
SALESREP[ Salesrep#, SalesRepName ]
Step 8: Identify All FK’s( Customer# of TITLE_DATE points to CUSTOMER and
Title# of TITLE_DATE points to TITLE )
CUSTOMER [Customer#, CDetails ]
TITLE[Title#, Title, Artist (FK) ]
TITLE_TDATE[Title# (FK), TDate, Customer# (FK), Salesrep# (FK), Amount ]
ARTIST [ Artist#, Artist, ArtistEmail ]
SALESREP[ Salesrep#, SalesRepName ]
Step 9: Explode attribs that have more than 1 field
CUSTOMER[Customer#,CStreet, CCity, CProv, CPostalCode, CPhone ]