Part (PartNum, Description, OnHand, Class, Warehouse, Price,

1
Part ( PartNum, Description, OnHand, Class,
Warehouse, Price, (OrderNum, OrderDate,
CustomerNum, CustomerName,
RepNum, LastName,
FirstName, NumOrdered,
QuotedPrice )
)
2
Part ( PartNum, Description, OnHand, Class,
Warehouse, Price, (OrderNum, OrderDate,
CustomerNum, CustomerName,
RepNum, LastName,
FirstName, NumOrdered,
QuotedPrice )
)
This table is quite meaningful from the user
point of view. It describes the actual
operation---as reflected by the database.
3
Part ( PartNum, Description, OnHand, Class,
Warehouse, Price, (OrderNum, OrderDate,
CustomerNum, CustomerName,
RepNum, LastName,
FirstName, NumOrdered,
QuotedPrice )
)
This table is quite meaningful from the user
point of view. It describes the actual
operation---as reflected by the database. But
what if we have a part that has not been ordered
yet?
4
Part ( PartNum, Description, OnHand, Class,
Warehouse, Price, (OrderNum, OrderDate,
CustomerNum, CustomerName,
RepNum, LastName,
FirstName, NumOrdered,
QuotedPrice )
)
In that case we need to work on the table and
change it. The structure should be such that
update anomalies are not present. But what if
we have a part that has not been ordered yet?
5
Part ( PartNum, Description, OnHand, Class,
Warehouse, Price, (OrderNum, OrderDate,
CustomerNum, CustomerName,
RepNum, LastName,
FirstName, NumOrdered,
QuotedPrice )
)
Lets determine functional dependencies. But
what if we have a part that has not been ordered
yet?
6
Part ( PartNum, Description, OnHand, Class,
Warehouse, Price, (OrderNum, OrderDate,
CustomerNum, CustomerName,
RepNum, LastName,
FirstName, NumOrdered,
QuotedPrice )
)
PartNum determines Description OnHand Cla
ss WareHouse Price
7
Part ( PartNum, Description, OnHand, Class,
Warehouse, Price, (OrderNum, OrderDate,
CustomerNum, CustomerName,
RepNum, LastName,
FirstName, NumOrdered,
QuotedPrice )
)
PartNum determines Description OnHand Cla
ss WareHouse Price
8
Part ( PartNum, Description, OnHand, Class,
Warehouse, Price, (OrderNum, OrderDate,
CustomerNum, CustomerName,
RepNum, LastName,
FirstName, NumOrdered,
QuotedPrice )
)
OrderNum determines OrderDate CustomerNum
9
Part ( PartNum, Description, OnHand, Class,
Warehouse, Price, (OrderNum, OrderDate,
CustomerNum, CustomerName,
RepNum, LastName,
FirstName, NumOrdered,
QuotedPrice )
)
OrderNum determines OrderDate CustomerNum
10
Part ( PartNum, Description, OnHand, Class,
Warehouse, Price, (OrderNum, OrderDate,
CustomerNum, CustomerName,
RepNum, LastName,
FirstName, NumOrdered,
QuotedPrice )
)
CustomerNum determines CustomerName RepN
um
11
Part ( PartNum, Description, OnHand, Class,
Warehouse, Price, (OrderNum, OrderDate,
CustomerNum, CustomerName,
RepNum, LastName,
FirstName, NumOrdered,
QuotedPrice )
)
CustomerNum determines CustomerName RepN
um
12
Part ( PartNum, Description, OnHand, Class,
Warehouse, Price, (OrderNum, OrderDate,
CustomerNum, CustomerName,
RepNum, LastName,
FirstName, NumOrdered,
QuotedPrice )
)
CustomerNum determines CustomerName RepN
um which determines LastName FirstNam
e
13
Part ( PartNum, Description, OnHand, Class,
Warehouse, Price, (OrderNum, OrderDate,
CustomerNum, CustomerName,
RepNum,
LastName, FirstName,
NumOrdered, QuotedPrice
) )
14
Part ( PartNum, Description, OnHand, Class,
Warehouse, Price, (OrderNum, OrderDate,
CustomerNum, CustomerName,
RepNum,
LastName, FirstName,
NumOrdered, QuotedPrice
) )
15
Part ( PartNum, Description, OnHand, Class,
Warehouse, Price, (OrderNum, OrderDate,
CustomerNum, CustomerName,
RepNum,
LastName, FirstName,
NumOrdered, QuotedPrice
) )
Theres also (PartNum, OrderNum)
16
Part ( PartNum, Description, OnHand, Class,
Warehouse, Price, (OrderNum, OrderDate,
CustomerNum, CustomerName,
RepNum,
LastName, FirstName,
NumOrdered, QuotedPrice
) )
Theres also (PartNum, OrderNum) which
determines NumOrdered
17
Part ( PartNum, Description, OnHand, Class,
Warehouse, Price, (OrderNum, OrderDate,
CustomerNum, CustomerName,
RepNum,
LastName, FirstName,
NumOrdered, QuotedPrice
) )
Theres also (PartNum, OrderNum) which
determines NumOrdered and QuotedPrice
18
Part ( PartNum, Description, OnHand, Class,
Warehouse, Price, (OrderNum, OrderDate,
CustomerNum, CustomerName,
RepNum,
LastName, FirstName,
NumOrdered, QuotedPrice
) )
So now what do we do?
19
Part ( PartNum, Description, OnHand, Class,
Warehouse, Price, (OrderNum, OrderDate,
CustomerNum, CustomerName,
RepNum,
LastName, FirstName,
NumOrdered, QuotedPrice
) )
1. Normalize the table (it has repeating groups).
20
Part ( PartNum, Description, OnHand, Class,
Warehouse, Price, (OrderNum, OrderDate,
CustomerNum, CustomerName,
RepNum,
LastName, FirstName,
NumOrdered, QuotedPrice
) )
1. Normalize the table (it has repeating
groups). 2. Eliminate partial dependencies.
21
Part ( PartNum, Description, OnHand, Class,
Warehouse, Price, (OrderNum, OrderDate,
CustomerNum, CustomerName,
RepNum,
LastName, FirstName,
NumOrdered, QuotedPrice
) )
1. Normalize the table (it has repeating
groups). 2. Eliminate partial dependencies. 3.
Make sure the only determinants are candidate
keys.
22
Part ( PartNum, Description, OnHand, Class,
Warehouse, Price, OrderNum, OrderDate,
CustomerNum, CustomerName,
RepNum,
LastName, FirstName,
NumOrdered, QuotedPrice
)
1. Normalize the table (it has repeating
groups). 2. Eliminate partial dependencies. 3.
Make sure the only determinants are candidate
keys.
23
Parts (PartNum, Description, OnHand, Class,
Wartehouse, Price)
Part ( PartNum, OrderNum, OrderDate,
CustomerNum, CustomerName,
RepNum,
LastName, FirstName,
NumOrdered, QuotedPrice
)
1. Normalize the table (it has repeating
groups). 2. Eliminate partial dependencies. 3.
Make sure the only determinants are candidate
keys.
24
Parts (PartNum, Description, OnHand, Class,
Wartehouse, Price)
Part ( PartNum, OrderNum, OrderDate,
CustomerNum, CustomerName,
RepNum,
LastName, FirstName,
) OrderLine (PartNum, OrderNum, NumOrdered,
QuotedPrice)
1. Normalize the table (it has repeating
groups). 2. Eliminate partial dependencies. 3.
Make sure the only determinants are candidate
keys.
25
Parts (PartNum, Description, OnHand, Class,
Wartehouse, Price)
Part ( PartNum, OrderNum, OrderDate,
CustomerNum, CustomerName,
RepNum,
) Rep (RepNum,
LastName, FirstName) OrderLine (PartNum,
OrderNum, NumOrdered, QuotedPrice)
1. Normalize the table (it has repeating
groups). 2. Eliminate partial dependencies. 3.
Make sure the only determinants are candidate
keys.
26
Parts (PartNum, Description, OnHand, Class,
Wartehouse, Price)
Part ( PartNum, OrderNum, OrderDate,
CustomerNum,
) Customer (CustomerNum, CustomerName, RepNum)
Rep (RepNum, LastName, FirstName) OrderLine
(PartNum, OrderNum, NumOrdered, QuotedPrice)
1. Normalize the table (it has repeating
groups). 2. Eliminate partial dependencies. 3.
Make sure the only determinants are candidate
keys.
27
Parts (PartNum, Description, OnHand, Class,
Wartehouse, Price)
Part ( PartNum, OrderNum, OrderDate,
CustomerNum,
) Customer (CustomerNum, CustomerName, RepNum)
Rep (RepNum, LastName, FirstName) OrderLine
(PartNum, OrderNum, NumOrdered, QuotedPrice)
1. Normalize the table (it has repeating
groups). 2. Eliminate partial dependencies. 3.
Make sure the only determinants are candidate
keys.
28
Parts (PartNum, Description, OnHand, Class,
Wartehouse, Price)
Orders ( OrderNum, OrderDate,
CustomerNum,
) Customer (CustomerNum, CustomerName, RepNum)
Rep (RepNum, LastName, FirstName) OrderLine
(PartNum, OrderNum, NumOrdered, QuotedPrice)
1. Normalize the table (it has repeating
groups). 2. Eliminate partial dependencies. 3.
Make sure the only determinants are candidate
keys.
29
Parts (PartNum, Description, OnHand, Class,
Wartehouse, Price)
Orders (OrderNum, OrderDate, CustomerNum
) Customer (CustomerNum, CustomerName,
RepNum) Rep (RepNum, LastName, FirstName)
OrderLine (PartNum, OrderNum, NumOrdered,
QuotedPrice)
30
Parts (PartNum, Description, OnHand, Class,
Wartehouse, Price)
Orders (OrderNum, OrderDate, CustomerNum
) Customer (CustomerNum, CustomerName,
RepNum) Rep (RepNum, LastName, FirstName)
OrderLine (PartNum, OrderNum, NumOrdered,
QuotedPrice)
31
Parts (PartNum, Description, OnHand, Class,
Wartehouse, Price)
Orders (OrderNum, OrderDate, CustomerNum ) End
of story Customer (CustomerNum, CustomerName,
RepNum) Rep (RepNum, LastName, FirstName)
OrderLine (PartNum, OrderNum, NumOrdered,
QuotedPrice)
32
Second problem (Henry Books Case Problem One)
33
Using the types of entities found in the Henry
Books database (books, authors, publishers),
create an example of a table that is in first
normal form but not in second normal form and an
example of a table that is in second normal form
but not in third normal form. In each case,
justify your answer
34
Using the types of entities found in the Henry
Books database (books, authors, publishers),
create an example of a table that is in first
normal form but not in second normal form and an
example of a table that is in second normal form
but not in third normal form. In each case,
justify your answer
This is an example about the power of assumptions.
35
Using the types of entities found in the Henry
Books database (books, authors, publishers),
create an example of a table that is in first
normal form but not in second normal form and an
example of a table that is in second normal form
but not in third normal form. In each case,
justify your answer
This is an example about the power of assumptions.
Take this table (ISBN, AuthorSSN, AuthorName)
36
Using the types of entities found in the Henry
Books database (books, authors, publishers),
create an example of a table that is in first
normal form but not in second normal form and an
example of a table that is in second normal form
but not in third normal form. In each case,
justify your answer
This is an example about the power of assumptions.
Take this table (ISBN, AuthorSSN, AuthorName)
If we assume that a book can only have one author
(that's pretty restrictive, but think about
semester projects where group work is not
allowed) then the above table is in 2NF already
(ISBN is the key) but not in 3NF because of
AuthorSSN being a non-key determinant (of whom?)
37
Using the types of entities found in the Henry
Books database (books, authors, publishers),
create an example of a table that is in first
normal form but not in second normal form and an
example of a table that is in second normal form
but not in third normal form. In each case,
justify your answer
This is an example about the power of assumptions.
Take this table (ISBN, AuthorSSN, AuthorName)
If we relax the requirement, and allow
co-authors, then the table is no longer in 2NF.
That's because we need to include the AuthorSSN
column in the key and the functional dependency
(AuthorSSN
AuthorName) becomes a partial dependency it was
about the existence of non-key determinants in
the other context
38
Part (PartNum, Description, OnHand, Class,
Warehouse, Price, (OrderNum, OrderDate,
CustomerNum, CustomerName,
RepNum, LastName,
FirstName, NumOrdered,
QuotedPrice )
)
39
Part ( PartNum, Description, OnHand, Class,
Warehouse, Price, OrderNum, OrderDate,
CustomerNum, CustomerName,
RepNum, LastName,
FirstName, NumOrdered,
QuotedPrice )
40
Part ( PartNum, Description, OnHand, Class,
Warehouse, Price, OrderNum, OrderDate,
CustomerNum, CustomerName,
RepNum, LastName,
FirstName, NumOrdered,
QuotedPrice )
41
Part ( PartNum, Description, OnHand, Class,
Warehouse, Price, OrderNum, OrderDate,
CustomerNum, CustomerName,
RepNum
NumOrdered, QuotedPrice
) Rep (RepNum, LastName,
FirstName)
42
Part ( PartNum, Description, OnHand, Class,
Warehouse, Price, OrderNum, OrderDate,
CustomerNum, CustomerName,
RepNum
NumOrdered, QuotedPrice
) Rep (RepNum, LastName, FirstName)

43
Part ( PartNum, Description, OnHand, Class,
Warehouse, Price, OrderNum, OrderDate,
CustomerNum, CustomerName

NumOrdered, QuotedPrice
) Customer (CustomerName, RepNum) Rep
(RepNum, LastName, FirstName)
44
Part ( PartNum, Description, OnHand, Class,
Warehouse, Price, OrderNum, OrderDate,
CustomerNum,
NumOrdered,
QuotedPrice ) Customer
(CustomerName, RepNum) Rep (RepNum, LastName,
FirstName)
45
Part ( PartNum, Description, OnHand, Class,
Warehouse, Price, OrderNum,

NumOrdered, QuotedPrice
) Orders (OrderNum, OrderDate,
CustomerNum) Customer (CustomerName, RepNum)
Rep (RepNum, LastName, FirstName)
46
Part ( PartNum, OrderNum,

NumOrdered, QuotedPrice
) Parts (PartNum, Description,
OnHand, Class, Warehouse, Price) Orders
(OrderNum, OrderDate, CustomerNum) Customer
(CustomerName, RepNum) Rep (RepNum, LastName,
FirstName)
47
Part ( PartNum, OrderNum,

NumOrdered, QuotedPrice
) Parts (PartNum, Description,
OnHand, Class, Warehouse, Price) Orders
(OrderNum, OrderDate, CustomerNum) Customer
(CustomerName, RepNum) Rep (RepNum, LastName,
FirstName)
48
OrderLine ( PartNum, OrderNum,

NumOrdered, QuotedPrice
) Parts (PartNum, Description,
OnHand, Class, Warehouse, Price) Orders
(OrderNum, OrderDate, CustomerNum) Customer
(CustomerName, RepNum) Rep (RepNum, LastName,
FirstName)
49
OrderLine ( PartNum, OrderNum, NumOrdered,
QuotedPrice) Parts (PartNum, Description,
OnHand, Class, Warehouse, Price) Orders
(OrderNum, OrderDate, CustomerNum) Customer
(CustomerName, RepNum) Rep (RepNum, LastName,
FirstName)