1. Registered User
Join Date
Nov 2007
Posts
7

## 3NF or BCNF?

hi, is the following table in 3NF and BCNF (boyce codd normal form)?

PL(Team, played, Won, Draws, Lost, goals-scored, goals-against, Points)

I mean, its purpose seems simlpe enough, but it is confusing to me either whether its possible to decompose it to BCNF.
anyone got any clues??

x

2. Registered User
Join Date
May 2005
Location
San Antonio, Texas
Posts
134

what attributes are related to the primary key?

It depends on what the data is, and all we can see are names. does goals-scored mean for a team over their lifetime or is it for an individual game? how is points different from goas-scored? I am not sure what points are at all :P

3. Registered User
Join Date
Nov 2007
Posts
7
Well, the purpose of the database is to store information on a football,soccer league, stating the facts and figures about the teams. For example data would be updated accordingly for if a team would play a game and score 2 goals, the played column would increase 1 and the goals would increase by 2. Sorry for my bad description, heres a table .

team = team name
played = how many games a team has played
won = how many games a team has won
draws = how many games a team has drawed
lost = how many games a team has lost
goals- scored = how many goals a team has scored
goals-against = how many goals they have conceived
points = there points on the table is determined by all the previous attributes.

Team, played, Won, Draws, Lost, goals-scored, goals-against, Points
England, 8, 7, 1, 0, 19, 6, 22
Brazil, 9, 6, 2, 1, 11, 2, 20

hope my question is abit clearer.

4. SQL Consultant
Join Date
Apr 2002
Location
Posts
20,002
Originally Posted by numberone
hi, is the following table in 3NF and BCNF (boyce codd normal form)?
yes, it is

when is this homework assignment due?

5. Registered User
Join Date
Nov 2007
Posts
7
so the table is in BCNF? the question asks me to determine whether or not it is in 3nf or bcnf, and if not then i should decompose it to ensure bcnf is satisfied.

the assigment is due in 2weeks, i dont like to leave things to the very last minute =D

6. SQL Consultant
Join Date
Apr 2002
Location
Posts
20,002
three steps to homework success

1) post your questions on da internets
2) accept each reply you get at face value
3) hand in your paper on time

7. Registered User
Join Date
Nov 2007
Posts
7
well.. i was hesitant to accept your answer... as i do actually want to learn, i didnt just want the answer...

8. SQL Consultant
Join Date
Apr 2002
Location
Posts
20,002
okay, i will walk you through it

give me your definitions of 3NF and BCNF

then for each column in your table, describe whether or not you feel it is a candidate key, and if not, which column is it functionally dependent on

9. Registered User
Join Date
May 2005
Location
San Antonio, Texas
Posts
134
if points are calculated and played in a sum of win, lose, draw... then why store that information?

find your primary key and then go through each attribute and determine if it is dependent upon the primary key.

while r937's response may smack against your pride.. he is making a very valid point. You have to understand something yourself. When we don't take the time to understand things then we rely on other people to tell us how to think. That would make you a slave.

10. Registered User
Join Date
Nov 2007
Posts
7
Originally Posted by numberone
Team, played, Won, Draws, Lost, goals-scored, goals-against, Points
England, 8, 7, 1, 0, 19, 6, 22
Brazil, 9, 6, 2, 1, 11, 2, 20
3nf is when it satisfies all its previous nf's and only contains 1 primary key per table.
im not entireley sure on bcnf.

Well, i think Team can be a primary key because it is unqiue and there are no duplicate teams with the name name in this case, and the Points attribute is determined by played,Won,Draws,Lost,goals-scored and goals-against.

11. SQL Consultant
Join Date
Apr 2002
Location
Posts
20,002
"1 primary key per table" is a criterion for 1NF

(actually 0NF, because without a primary key, it isn't even a relational table, even though most database systems will let you declare it)

you need to understand the normal forms before working with them

i am guessing that you chose the football/soccer application for your assignment? or was that part of the assignment that was given

12. Registered User
Join Date
Nov 2007
Posts
7
apart of the assignment. anyways.. to add abit extra i now know that for 3nf that the non-key fields are dependent upon only the PK, and not other non-key fields for its existence. so does that mean the points column void it to be 3nf? as it is dependant on the previous attributes before it.

13. SQL Consultant
Join Date
Apr 2002
Location
Posts
20,002
that is correct

look at it like this: if for a particular team you change the wins by adding 1, is the points value still correct? answer: no, so points is dependent on wins

14. Registered User
Join Date
Nov 2007
Posts
7
alrite, so does that mean played is dependant on won, draws and lost as well? because when a team plays a match, the outcome of the match must be one of the triples. therefore an increase of 1 'played' must be an increase in 'won' or 'draws' or 'lost' by 1. ?

So now, Points can be depedant on Wins, and i have found out that 'played' is dependant on wins..

are goals-scored and goals-against dependant on 'played'? because if you look at it this way, if a team has played 0 games, the goals will have to be set 0.

so far concluded that..
Points is dependant on played,won,draws,lost,goals-scored,goals-against.
played is dependant on won,lost,draws
goals-scored,goals-against are dependant on played

im confused on what to do next

15. SQL Consultant
Join Date
Apr 2002
Location