Normalization and integrity constraints exam

Exam 1: Normalization
 

Part 1

 

Use the following table to answer questions 1 and 2:

 

Table: BOOK-DETAIL

BookID** GenreID GenreDesc Price
1 1 Gardening 25.99
2 2 Sports 12.99
3 1 Gardening 10.00
4 3 Travel 14.99
5 2 Sports 17.99

**Primary key

 

  1. What, if any normalization error is present in the table?
  2. None
  3. First Normal Form
  4. Second Normal Form
  5. Third Normal Form

 

  1. Display the modified table or tables that would correct the normalization error, if one is present. Be sure to indicate primary (**) and foreign (*) keys.

 

 

 

Use the following table to answer questions 3 and 4:

 

Table: BOOKS

Book-Code** Title Price Pub-Code** Publisher City
0180 Shyness 7.65 BB Bantam Books Boston
0189 Kane and Able 5.55 PB Pocket Books New York
0200 The Stranger 8.75 BB Bantam Books Boston
0378 The Dunwich Horror 19.75 PB Pocket Books New York
079X Smokescreen 4.55 PB Pocket Books New York

**Primary Key

 

 

  1. What, if any normalization error is present in the table?
  2. None
  3. First Normal Form
  4. Second Normal Form
  5. Third Normal Form

 

  1. Display the modified table or tables that would correct the normalization error, if one is present. Be sure to indicate primary (**) and foreign (*) keys.

 

 

 

Use the following table to answer questions 5 and 6:

 

Table: PRODUCT

ProductID** Sizes Available Price
1 Small, Medium 15.99
2 Small, Medium, Large 13.99
3 Small 22.99
4 Small 17.50
5 Large, Extra Large 19.99

**Primary key

 

  1. What, if any normalization error is present in the table?
  2. None
  3. First Normal Form
  4. Second Normal Form
  5. Third Normal Form

 

  1. Display the modified table or tables that would correct the normalization error, if one is present. Be sure to indicate primary (**) and foreign (*) keys.

 

 

 

 

Use the following table to answer questions 7 and 8:

 

Table: PURCHASE-DETAIL

CustomerID** StoreID** StoreLocation
1 1 Los Angeles
1 3 San Francisco
2 1 Los Angeles
3 2 New York
4 3 San Francisco

*Primary key

 

 

 

  1. What, if any normalization error is present in the table?
  2. None
  3. First Normal Form
  4. Second Normal Form
  5. Third Normal Form

 

  1. Display the modified table or tables that would correct the normalization error, if one is present. Be sure to indicate primary (**) and foreign (*) keys.

 

 

 

Use the following table to answer questions 9 and 10:

 

Table: BOOK-LOCATION

BookID** BranchID** Quantity-on-Hand
1 5W 4
1 3E 6
2 5W 3
3 5W 5
3 2S 4

**Primary key

 

  1. What, if any normalization error is present in the table?
  2. None
  3. First Normal Form
  4. Second Normal Form
  5. Third Normal Form

 

  1. Display the modified table or tables that would correct the normalization error, if one is present. Be sure to indicate primary (**) and foreign (*) keys.

 

 

Part 2

 

Use the following table to answer questions 11 through 16.

 

Order ID Order Date Customer ID Customer Name Product ID Product Desc. Product Price Quantity Ordered
1006 10/24/10 2 Value Furniture 7

 

5

 

4

Dining Table

Writers Desk

Entertain Center

800.00

 

325.00

 

650.00

2

 

2

 

1

1007 10/25/10 6 Furniture Gallery 11

 

4

4 Drawer Dresser

Entertain Center

500.00

 

650.00

4

 

3

 

Business rules:

  1. The OrderID is a unique number given to each order
  2. OrderIDs are not reused.
  3. The CustomerID is a unique number given to each customer
  4. CustomerIDs stay with the customer forever
  5. CustomerIDs are not reused
  6. ProductID is a unique number given to each product the company sells.
  7. ProductIDs stay with the product for as long as the product is sold by the company
  8. ProductIDs are not reused.

 

The questions:

  1. What errors prevent the table displayed above from being first normal form compliant? Be specific, identifying all columns that create the normalization error by name.
  2. Bring the table into first normal form compliance without loss of any data. Identify primary and foreign keys (when present) for all tables. Give each table its own name.
  3. What error(s), if any, prevent the now first normal form compliant table or tables from being second normal form compliant? Be specific, identifying all columns and tables that create the normalization error by name.
  4. Bring the table(s) into second normal form compliance without loss of any data. Identify primary and foreign keys. Be sure to present all tables, not just those changed in this step of the normalization process. Give each table a its own name.
  5. What error(s), if any, prevent the now second normal form compliant table or tables from being third normal form compliant? Be specific, identifying all columns and tables that create the normalization error by name.

 

  1. Bring the table(s) into third normal form compliance without loss of any data. Identify primary and foreign keys. Be sure to present all tables, not just those changed in this step of the normalization process. Give each table its own name.

                                                                                                                                                               Order Now