Homework Review:
1) HW3-4 Review
2) Identify the functional dependencies in the following tables and then states whether each one is in 1st normal form and 2nd normal form or not. If not, fix it.
|
OrderID
|
OrderDate
|
CustomerID
|
CName
|
CAddress
|
ItemID
|
ItemName
|
UnitPrice
|
QTY
|
SubTotal
|
OrderTotal
|
|
103
|
10/5/98
|
1002
|
Cooper
|
3256 Grand Avenue
|
1015
1010
1025
|
Toaster
Blender
Television
|
19.95
29.95
699.95
|
1
1
2
|
19.95
29.95
1399.9
|
1459.8
|
|
|
|
|
|
108
|
10/16/98
|
1000
|
Kris
|
637 Johnson Street
|
1025
1045
|
Clock
|
99.95
|
1
1
|
99.95
|
799.9
|
|
|
|
110
|
10/24/98
|
1002
|
|
|
1045
|
|
|
3
|
299.95
|
299.95
|
Lecture 1: Boyce-Codd Normal Form
Every determinant is a key. A table may have many functional dependencies, and BCNF requires that every the determinant in every functional dependency must be a key, i.e., can functionally determine all other columns.
BCNF is more stringent than 3NF and 2NF. Thus, a table that is in 2NF or 3NF may still violate BCNF.
Example: Warehouse Table
BCNF is more stringent than 3NF and 2NF. Thus, a table that is not in 2NF or 3NF will also violate BCNF. A table is not in 2NF or 3NF can be fixed using the same procedure for fixing BCNF violations.
Example: Student Registration Table and Royalty Table
Homework:
Reading:
- Chapter 19 of LIU
- SQL Tutorial
Correctness Questions: online
Hands-on Assignments (due on 10/07):
1. Is the following table in BCNF? If not, fix it to be so.
|
Name
|
SSN
|
Street
|
City
|
Acct No
|
Balance
|
|
Shiver
|
508-08-0808
|
North
|
Albany
|
908
|
1,080
|
|
Shiver
|
508-08-0808
|
North
|
Albany
|
805
|
5,000
|
|
Rodies
|
510-10-1010
|
South
|
Macon
|
805
|
5,000
|
|
Rodies
|
510-10-1010
|
South
|
Macon
|
105
|
1,050
|
|
Doe
|
509-09-0909
|
|
|
110
|
110,000,000
|
|
