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?

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.

pootle flump ur codings are working excelent.

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.

dportas - do you not find it an iterative process?

pootle flump ur codings are working excelent.

premature surrogatization

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.

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?