db design and normalization?

[lithium]

Hi,

I don't know whether this is the right forum to post this. But anyway I'm doing a small project to capture the Academy Awards database, and so far I have these tables:

(_key_ denotes primary key)

Movie
| _CodeNum_ | Title | Type | ProdYear | Rating |

sample:
| 00001 | Gladiator | Movie | 2000 | 8.4 |

CastCrew
| _SSN_ | FName | LName |

sample:
| 123456789 | Russel | Crowe |

Award
| _AYear_ | _Category_ |

sample:
| 2001 | Best Leading Actor |

MovProduction
| _SSN_ | _JobDesc_ | _CodeNum_ |

sample:
| 123456789 | Leading Actor | 00001

Nomination
| _AYear_ | _Category_ | _SSN_ | _JobDesc_ | _CodeNum_ | isAWinner |

sample:
| 2001 | Best Leading Actor | 123456789 | Leading Actor | 00001 | Y |

My question is; Do you think that the tables are already normalized to BCNF? Because I have a nagging feeling that I should do something about the composite primary keys. Err.. I admit that I'm a newbie to (good) database design, so I'd really appreciate if someone could point me the weakness of my current db design and give me some suggestions to improve it.

Thanks in advance

[DevilBear]

I think you're fine.

I'm wondering why JobDesc is even in the Nomination table... I mean, even if someone both directed and acted in a film, you would know which job was referred to if they were nominated for Best Director rather than Best Leading...

But in any case, do you think you have any other candidate keys for Nomination? For this not to be in BCNF, you'd have to have at least two, all composite, with some attributes in common.

[lithium]

Hello DevilBear, thanks for your feedback.. sorry I couldn't get back earlier

Actually, the Nomination table is the relationship between MovProduction and Award, so it has the primary keys of Award (AYear and Category) and MovProduction (SSN, JobDesc, CodeNum). And the MovProduction table is a weak entity (JobDesc is the partial key) from the relationship between Movie and CastCrew, so its primary key should be the composite of Movie's primary key, CastCrew's primary key, and its own partial key, right? Oh well, maybe you should see the ER diagram at http://sunsite.ui.ac.id/dempo/~sari100/ER.bmp

I'm wondering why JobDesc is even in the Nomination table...

I think that's because JobDesc is the part of Nomination's primary key. Right, now I really should do something to the composite keys. Do you think that I should change the tables to these one? Is it better than the previous one?

Movie
=====
CodeNum (PK)
Title
Type
ProdYear
Rating

CastCrew
========
SSN (PK)
FName
LName

Award
=====
AID (PK)
AYear
Category

MovProduction
=============
MPID (PK)
SSN (FK from CastCrew)
CodeNum (FK from Movie)
JobDesc

Nominee
=======
NID (PK)
AID (FK from Award)
MPID (FK from MovProduction)

Winner
======
NID (PK, also FK from Nominee)

Hmm.. Does this mean I have to join 4 tables just to get the titles of movies that won the Best Pictures? That sounds horrible.. Oh, by the way do you know any good online tutorial about db design and normalization? Now that I stuck here it seems that I haven't grasped its concepts yet..

Thanks for helping

[DevilBear]

You're pushing the limits of my experience here, as I am a newbie at this, too.

I don't think the new tables you proposed are better than the old! They certainly don't seem to conform to the ERD any better. And I'm still not convinced that there was anything wrong with the originals you posted--unless you care to give a specific example of a problem users could encounter with it.

Did you create this ERD, or was it given to you? And why are you so worried about BCNF? Is it part of an assignment?

You can look at the following webpage for the list of conditions a relation must satisfy to be in 3NF but *not* BCNF:
http://www.google.com/search?q=cache...mal+form&hl=en

[lithium]

Hi, I'm back again.. sorry it took me soo long to reply (I've gone camping since saturday !)

And I'm still not convinced that there was anything wrong with the originals you posted--unless you care to give a specific example of a problem users could encounter with it.

Well, I guess the composite primary keys won't make any inconsistency--unless a user wrongly enter the data to the tables (say maybe he/she populated them by hand).

But how about some redundancies.. The Award table doesn't have nonprime attributes, and every tuples in it can be obtained from the Nomination table.. so the Award table seemed useless.

Then some tuples in MovProduction table are also stored in the Nomination table (eventhough the MovProduction table isn't really that useless since it also contains the movies and their casts/crews that didn't get any award nomination).

Hmm, maybe I should add some nonprime attributes to Award and MovProduction so that they can be more 'useful'..

I don't think the new tables you proposed are better than the old! They certainly don't seem to conform to the ERD any better. Did you create this ERD, or was it given to you? And why are you so worried about BCNF? Is it part of an assignment?

I created the ERD based on the specs at http://sunsite.ui.ac.id/dempo/~sari100/spec.txt
and yes, BCNF is a part of the assignment..

You can look at the following webpage for the list of conditions a relation must satisfy to be in 3NF but *not* BCNF:

Wow, thanks for the link! Now I think I don't have to worry about BCNF anymore (the tables don't have enough candidate keys to be normalized further than 3NF, right?).

Anyway.. I think I have a bad news.. I just realized that my db design doesn't handle the best original song of movie soundtracks nomination! Aargh... how come I didn't notice it before!! Now I have to redo the ERD again.. :~(

Oh, nevermind.. and thanks for your help so far, DevilBear! I really appreciate that