Part B. [6 points] Below is the customer purchase table of James River Jewelry s
ID: 3598907 • Letter: P
Question
Part B. [6 points] Below is the customer purchase table of James River Jewelry shop. Apply four steps of the normalization process. (You must show each step. Referential integrity constraint must be stated.) Phone Email InvoiceNumberInvoiceDate Pre TaxAmount Nam Elizabeth Stanley Fred Price Linda Becky Pamela Birch Ricardo Romez Elizabeth inda Beck Elizabeth Stanley Samantha Jackson555-236-1095 155.00 203.00 75.00 67.00 330.00 25.00 45.00 445.00 72.00 55/2010 S 5/7/2010S Elizabeth Stanley@somewhere.com Fred Price@somewhere.com Linda.Becky@somewhere.com Pamela Birch@somewhere com Ricardo Romez @somewhère.com Elizabeth Stanley @somewhere.com LIinda.Becky@somewhere.com Elizabeth Stanley@somewhere.com Samantha Jackson@somewhere.com 1000 1010 1020 1030 1040 1050 1060 1070 1080 555-236-7789 555-236-0091 5/152010s 15/2010S 5/16/2010S 5162010S 82010 Is 5/19/2010S -4493 555-236-3334 238-7789 Stanley 555-238-7789 Step 1. Identify all the candidate keys of the relation. Step 2. Identify all the functional dependencies in the relation. Step 3. Examine the determinants of the functional dependencies. If any determinant is not a candidate key, the relation is not well formed. In this case: a. b. c. d. Place the columns of the functional dependency in a new relation of their own Make the determinant of the functional dependency the primary key of the new relation Leave a copy of the determinant as a foreign key in the original relation Create a referential integrity constraint between the original relation and the new relation Repeat step 3 as many times as necessary until every determinant of every relation is a candidate key.Explanation / Answer
1. Lets Divide the Table (using Normalization techniques) into 2 Tables
a)Customer
b)Invoice
2. Customer Table
CustomeID ,
CustomerName ,
CustomePhone ,
CustomerEmail
"CustomeID" is Primary key (Cadidate key)
3. Invoice table
InvoiceNumber ,
CustomerId ,
InvoiceDate ,
PreTamAmt
"InvoiceNumber" is Pimary key (Candidate Key)
CusomerID is Foreign key (Referencial Integrity Constraint)
Step-1 :-
-----------------
Identified Candidate Keys are
CustomeID is Customer Table
InvoiceNumber in Invoice table
Step-2 :-
-----------------
All other Fields are functionally depending on Primary Keys
All other Columns in Customer Table are Functionall depending on CustomeID
All other Columns in Invoice Table are Functionall depending on InvoiceNumber
Step-3 :-
-----------------
a. Check how we divide the Table
CustomeID id foreign key in invoice Table so that each Invoice is linked to a Customer
whose details are stored in Customer Table
b. Using above normalization duplication of customer details are avoided using Foreign key to link Customer
Details
Lets check a sampe Select Query selecta all Invoices along with Customer Details
select c.CustomerName,c.CustomePhone,c.CustomerEmail,i.InvoiceNumber,i.InvoiceDate,i.PreTamAmt
from Customer c, Invoice i where i.CustomeID = c.CustomeID