Wages, training, etc.
8.3 An inexperienced database designer suggested the following schema for one of the tables in the antique
store database:
Sales
Is this schema in 1NF?
yes
Is this schema in 2NF?
No, aside from attributes Description and Price, none of the attributes of the Sales table is
functionally dependent on the key Item_ID.
Is this schema in 3NF?
No, several transitive dependencies exist; for instance, Addr, City, St, ZIP, and Phone are
dependent upon Customer_Name.
If the answer to any of these questions is, ???No,??? redesign the table so that the result is in 3NF.
Sales
Customer
Customer_ID | Customer_Name | Addr | City | St | ZIP | Phone
Item_ID | Description | Price | Date | Customer_Name
Item_ID | Description | Price | Date | Customer_Name | Addr | City | St | ZIP | Phone
8.4 Two of the tables in the antique store database will be a table of Deliveries, and a table of
Delivery_Services. Here are the schemas for the two tables:
Deliveries
Delivery_Services
Write the SQL code to create these tables. Specify which columns may not be NULL, and specify primary
and foreign key constraints. Note that the Delivery_Service column in the Deliveries table is
meant to have the same meaning as the Name column in the Delivery_Service table.
Pages:
513
514
515
516
517
518
519
520
521
522
523
524
525
526
527
528
529
530
531
532
533
534
535
536
537