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 XY 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