Transcript PPTX
Problem 15.29 6th ed
Consider the table:
Orders (O#,I#,Odate, Cust#, Total_amount, Qty_ordered,
Total_price, Discount%)
CSE4701
What are the Functional Dependencies?:
O# Odate Cust# Total_amt
O# I# Qty_ordered Total_price Discount%
Is it in 2 NF ?
No! Odate, Cust#, Total_amt Partially Dependent on
only Part of Key O# I# namely O#
ANN.1
Problem 15.29 6th ed
How do you Fix the Single Orders Table?
Create Two Tables
CSE4701
Order (O#,Odate, Cust#, Total_amount)
OrderedItem (O#,I#, Qty_ordered, Total_price, Discount%)
Is it in 3NF?
Yes – No transitive functional
dependencies XY and Y Z , with X
as the Primary Key, where Y is not a
candidate key
ANN.2
Problem 15.30 6th ed
CSE4701
Consider the table:
CAR_SALE( Car# , Date_sold, Salesman#, Commision%,
Discount_amt)
Assumptions
Car can be sold by Multiple Salepersons
Thus, Primary Key: Car# , Salesman# w/ FDs
Car# Date_sold
Car# Discount_amt
Car# Salesman#
Salesman# Commission%
Is it in 2NF?
No! Car# Date_sold and Car# Discount_amt are
not FFD on Primary Key – since Depend on Only Part
ANN.3
Problem 15.30
How Do you Convert to 2NF?
Recall FDs
Car# Date_sold
Car# Discount_amt
Car# Salesman#
Salesman# Commission%
CSE4701
Split into Three Tables
CAR1( Car# , Date_sold, Discount_amt)
CAR2( Car# , Salesman#)
CAR3(Salesman#, Commision%)
ANN.4
Problem 15.33 6th ed
CSE4701
Consider the table:
TreatPatient (Doctor#, Patient#, Date, Diagnosis,
Treat_code, Charge)
Assumptions and FDs
Patient Treated by Physician on Date with a Diagnosis,
Treatment Code, and Charge
Every Treatment Code has a Charge
{Doctor#, Patient#, Date}{Diagnosis, Treat_code, Charge}
{Treat_code}{Charge}
Is it in 3NF?
No - Convert
ANN.5
Problem 15.33
What do you look for? Transitivity
What is the Problem?
CSE4701
Charge a non-Key Attribute is Dependent
on Treat Code another non-Key Attribute
Split into Two Tables
FDs Now are Fine
TreatPatient (Doctor#, Patient#, Date,
Diagnosis, Treat_code)
BillingAmount(Treat_code, Charge)
ANN.6
Problem 15.35
CSE4701
Given Table Below - What is Candidate Key?
BOOK (Book_Name, Author, Edition, Year)
Book_Name, Author, Edition
Why?
Sometimes Edition Issues Twice in 1 Year
What is the main FD?
Book_Name, Edition Year
Is it in 2NF?
No: Since Year Dependent on only Part of Key
Convert:
BOOK (Book_Name, Author, Edition)
BOOK_YEAR (Book_Name, Edition, Year)
ANN.7
Problem 15.35
BOOK(Book_Name, Author, Edition)
BOOK_YEAR(Book_Name, Edition,Year)
CSE4701
What are the Multi-Valued Dependencies?
Book_Name Author
Book_Name Edition.
How Do You Separate the Dependencies
SPLIT INTO THREE TABLES
BOOK (Book_Name, Edition)
BOOK_AUTHOR (Book_Name, Edition, Author)
BOOK_YEAR (Book_Name, Edition, Year)
ANN.8