Question

a. picture

b. Using the tables created on the preview question that should be on BCNF create a SQL statement to denormalize it.

Convert the below table up to BCNE Salesinformation LastHarnest Name Phone InvoldeDate Invoice temPrice Tax Total LastName FirstName Phone InvoiceDate Invoiceltem Price Tax T Shire Robert 206-524-2433 14-Dec-13 Antique Desk 3,000.00 249.00 3,249.00 Shire Robert 206-524-2433 14-Dec-13 Antique Desk Chair 500.00 41.50 541.50 Goodyear Katherine 206-524-3544 15-Dec-13 Dining Table Linens 1,000.00 83.00 1.083.00 Bancroft Chris 425-635-9788 15-Dec-13 Candles 50.00 4.15 54.15 Griffith John 206-524-4655 23-Dec-13 Candles 48.74 45.00 250.00 3.74 20.75 Shire Robert 206-524-2433 5-Jan-14 Desk Lamp 270.75 Tlemey Doris 425-636-8677 10-Jan-14 Dining Table Linens 750.00 62.25 81225 Anderson Donna 360-538-7566 12-Jan-14 Book Shelf 250.00 20.75 270.75 Goodyear Katherine 206-524-3544 15-Jan-14 Antique Chair 1.250.00 103.75 1,353.75 Katherine 206-524-3544 1 5-Jan-14 Antique Chair 1,750.00 145.25 1,89525 Goodyear Tlemey Doris 425-635-8677 25-Jan-14 Antique Candle Holders 350.00 29.05 379.05

Answer 1

BOYCE-CODD NORMAL FORM(BCNF):

It is an extended version of 3NF. It satisfies the #NF and every functional dependency X->Y,X should be the superkey of the table.

NORMALIZATION:

it is to organize the data in the table to avoid data redundancy.

1NF:

it should have the atomic values for each column and row.

2NF:

it should satisfy the 1NF and no non-prime attribute is dependent on the proper subset of any candidate key of table.

3NF:

it has to satisfy the 3NF and transitive functional dependency should be removed.

TO NORMALIZE THE TABLE :

we have to break the table.

here the candidate key is:

{firstname}

functional dependency:

firstname->lastname,phone,invoice item,invoice date

price->tax,total

>>CREATE TABLE PRICE_INFO(TAX(NUM),TOTAL(NUM));

>>CREATE TABLE FIRSTNAME(LASTNAME(VARCHAR),PHONE(NUM),INVOICE ITEM(VARCHAR),INVOICE DATE(VARCHAR));

for the first table:

candidate key:price

for the second table:

candiadate key:Firstname

>>SELECT * FROM PRICE_INFO JOIN FIRSTNAME;

from the use of above key we can denormalize the table.