Any comment for my Normal form concept is right ....
CAR_SALE TABLE
(Car #, Date_sold, Salesman#, Commission%, Discount_amt)
Assume that a car may be sold by multiple salesman and
hence {Car #, Salesman#} is the primary key.
Additional dependencies are:
Date_sold -> Discount_amt, and
Salesman# -> Commission%
Based on the given primary key, is this relation is 1NF, 2NF, or 3NF?
Sample Table:
Car # Date_sold Salesman# Commission% Discount_amt
100 12/12/02 001 3 300
100 12/12/02 002 5 300
101 12/25/02 002 5 500
102 12/20/02 001 3 400
102 10/20/02 003 2 400
1NF
Yes, this table is in first normal form since it has only one value at each row and column intersection.
(The First Normal Form requires that at each row and column intersection, there must be one and only one value. In other words, each column in a row can have only one value, and that must be atmoic.)
2NF
No, this table is not second normal form since commission is facts only about the Salesman#, and not about all parts of Car# Salesman# KEY.
Car # Date_sold Salesman# Commission% Discount_amt
100 12/12/02 001 3 300
100 12/12/02 002 5 300
101 12/25/02 002 5 500
101 12/25/02 003 2 500
102 12/20/02 001 3 400
102 10/20/02 003 2 400
(The Second Normal Form requires that no non-key column be a fact about a subset of the primary key.)
In other words, its attempts to reduce the amount of redundant data in a table by extracting it, placing it in new table(s) and creating relationships between those tables.
3NF
Of course, it is not third normal form since it is even not pass the rule of second nomral form.
(The Third Normal Form requires that no non key column depend on amother non key column. Each non key column must be a fact about the primary key column.)
Solution
Car # Date_sold Salesman#
100 12/12/02 001
100 12/12/02 002
101 12/25/02 002
101 12/25/02 003
102 12/20/02 001
102 10/20/02 003
Salesman# Commission%
001 3
002 5
003 2
Date_sold Discount_amt
12/12/02 300
12/25/02 500
12/20/02 400
CAR_SALE TABLE
(Car #, Date_sold, Salesman#, Commission%, Discount_amt)
Assume that a car may be sold by multiple salesman and
hence {Car #, Salesman#} is the primary key.
Additional dependencies are:
Date_sold -> Discount_amt, and
Salesman# -> Commission%
Based on the given primary key, is this relation is 1NF, 2NF, or 3NF?
Sample Table:
Car # Date_sold Salesman# Commission% Discount_amt
100 12/12/02 001 3 300
100 12/12/02 002 5 300
101 12/25/02 002 5 500
102 12/20/02 001 3 400
102 10/20/02 003 2 400
1NF
Yes, this table is in first normal form since it has only one value at each row and column intersection.
(The First Normal Form requires that at each row and column intersection, there must be one and only one value. In other words, each column in a row can have only one value, and that must be atmoic.)
2NF
No, this table is not second normal form since commission is facts only about the Salesman#, and not about all parts of Car# Salesman# KEY.
Car # Date_sold Salesman# Commission% Discount_amt
100 12/12/02 001 3 300
100 12/12/02 002 5 300
101 12/25/02 002 5 500
101 12/25/02 003 2 500
102 12/20/02 001 3 400
102 10/20/02 003 2 400
(The Second Normal Form requires that no non-key column be a fact about a subset of the primary key.)
In other words, its attempts to reduce the amount of redundant data in a table by extracting it, placing it in new table(s) and creating relationships between those tables.
3NF
Of course, it is not third normal form since it is even not pass the rule of second nomral form.
(The Third Normal Form requires that no non key column depend on amother non key column. Each non key column must be a fact about the primary key column.)
Solution
Car # Date_sold Salesman#
100 12/12/02 001
100 12/12/02 002
101 12/25/02 002
101 12/25/02 003
102 12/20/02 001
102 10/20/02 003
Salesman# Commission%
001 3
002 5
003 2
Date_sold Discount_amt
12/12/02 300
12/25/02 500
12/20/02 400
