Results 1 to 7 of 7

Thread: BCNF Help

  1. #1
    Join Date
    Dec 2008
    Posts
    10

    BCNF Help

    I have been posed the following problem for building relvars in BCNF. I've been struggling... Can anyone take a look and see if they can solve... I will put my relvars below.


    - Each video game has a name, quantity in stock, and is assigned a status of "New Release", "7-Day Rental", "Promotion", etc.

    - Each status has its own rental price, rental period (in days), and daily late fee rate.

    - Only members who register their name, address, and phone number can rent video games.

    - Once registered, all video game rentals for each member are tracked for rental date and return date.

    - Finally, each member's payments are also tracked, along with whether each payment is for rental or late fees.


    This was my attempt, but I don't think it is correct. I want to determine FDs as well.

    Game Relvar
    |gm_id | gm_name | gm_qty | status_id |

    gm_id->all attributes

    Status Relvar
    |st_id | st_name | st_price | st_price | st_fee|

    st_id->all attributes

    Member Relvar
    |member_id | mb_name | mb_address | mb_phone |

    member_id->all attributes

    Rental Relvar
    |rental_id | member_id | game_id | rental_date | return_date|

    rental_id -> all attributes

    Interaction Relvar
    |interaction_id | interaction_type | rental_id |

    interaction_id-> all attributes

    Payment Relvar
    |payment_id| interaction_id |payment_amount |
    payment_id -> all attributes


    Are these in BCNF?

  2. #2
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Hi

    I've seen the odd thing but I won't mention those as yet.

    The one thing is you have stuck a surrogate key (all those id columns) onto each relation and then considered only that with regard to your dependencies. You must consider all candidate keys.

    For example, you have a status id and state that all other attributes are wholly dependant on that. But the same must be considered for the other candidate key (status name). This applies to all your relations, and is especially important when the candidate keys are composite (i.e. made up of 2 or more columns).

    If I were you I would remove all the ids from any relation that does not require one, identify all candidate keys and go through your normal forms again.
    Testimonial:
    pootle flump
    ur codings are working excelent.

  3. #3
    Join Date
    Dec 2007
    Location
    London, UK
    Posts
    741
    Determine your FDs before the logical design, not afterwards.

    Pootle is right. Inventing your own keys for things and then failing to identify other keys (or any keys at all) in the model is a futile exercise. Unfortunately it's also an all-to-common error among database developers.

  4. #4
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Quote Originally Posted by dportas
    Determine your FDs before the logical design, not afterwards.
    dportas - do you not find it an iterative process?
    Testimonial:
    pootle flump
    ur codings are working excelent.

  5. #5
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    premature surrogatization

    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  6. #6
    Join Date
    Dec 2008
    Posts
    10
    Thank you everyone for your suggestions. I'm actually trying to teach myself via an online course so I apologize if my questions are not very refined.

    The suggestion the course says for the normalisation process is:
    1. Put in 1NF
    2. Determine FDs
    3. Use these to decompose to BCNF
    4 Determine join dependencies

    Can anyone provide me a beginner level explanation of the normalisation process? As well as how do you decided when a surrogate key is needed? BCNF I find the most confusing.

    I apologize for my lack of knowledge and thank you for your help.

  7. #7
    Join Date
    Dec 2008
    Posts
    10
    Quote Originally Posted by dportas
    Determine your FDs before the logical design, not afterwards.

    Pootle is right. Inventing your own keys for things and then failing to identify other keys (or any keys at all) in the model is a futile exercise. Unfortunately it's also an all-to-common error among database developers.
    These are the FDs I've determined from the description...

    game_name -> qty_in_stock, status
    status -> rental_price, rental_period, daily_fee
    member_name -> member_street, member_zip, member_phone, member_city, member_state
    member_zip -> member_city, member_state
    {member_street, member_city, member_state} -> member_zip
    status-> payment_type


    Am I missing any? Any suggestions? How should I proceed?

Posting Permissions

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