Results 1 to 15 of 15

Thread: 3NF or BCNF?

  1. #1
    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. #2
    Join Date
    May 2005
    Location
    San Antonio, Texas
    Posts
    134
    what is your primary key?

    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
    Vi veri veniversum vivus vici
    By the power of truth, I, a living man, have conquered the universe

  3. #3
    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. #4
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Quote 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?
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  5. #5
    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. #6
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    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
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  7. #7
    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. #8
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    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
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  9. #9
    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.
    Vi veri veniversum vivus vici
    By the power of truth, I, a living man, have conquered the universe

  10. #10
    Join Date
    Nov 2007
    Posts
    7
    Quote 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. #11
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    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
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  12. #12
    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. #13
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    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
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  14. #14
    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. #15
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    what to do next? go back to the assignment and check what the question asks

    "goals-scored,goals-against are dependant on played" -- no they're not
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •