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
- What, if any normalization error is present in the table?
- None
- First Normal Form
- Second Normal Form
- Third Normal Form
- Describe or illustrate how you would correct the normalization error, if one is present.
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
What, if any normalization error is present in the table?
-
- None
- First Normal Form
- Second Normal Form
- Third Normal Form
- Describe or illustrate how you would correct the normalization error, if one is present.
- Use the following table to answer questions 5 and 6:
Table: PRODUCT
|
ProductID**
|
Color
|
Price
|
|
1
|
Red, Green
|
15.99
|
|
2
|
Yellow
|
13.99
|
|
3
|
Green
|
22.99
|
|
4
|
Yellow, Blue
|
17.50
|
|
5
|
Red
|
19.99
|
**Primary key
- What, if any normalization error is present in the table?
- None
- First Normal Form
- Second Normal Form
- Third Normal Form
- Describe or illustrate how you would correct the normalization error, if one is present.
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
- What, if any normalization error is present in the table?
- None
- First Normal Form
- Second Normal Form
- Third Normal Form
- Describe or illustrate how you would correct the normalization error, if one is present.
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
- What, if any normalization error is present in the table?
- None
- First Normal Form
- Second Normal Form
- Third Normal Form
- Describe or illustrate how you would correct the normalization error, if one is present.
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
|
- What errors prevent the table displayed above from being first normal form compliant?
- Bring the table(s) into first normal form compliance without loss of any data. Identify primary and foreign keys (when present) for all tables.
- What error(s), if any, prevent the now first normal form compliant table or tables from being second normal form compliant?
- 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.
- What error(s), if any, prevent the now second normal form compliant table or tables from being third normal form compliant?
- 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.