CMPT 308 Lab 8: Normalization Two Solution  Lab 8: Normalization Two - 20 points

CMPT 308 Lab 8: Normalization Two Solution Lab 8: Normalization Two - 20 points

Goals
To continue developing your facility with the art and science of normalization.
Scenario
You have been hired as a database consultant by EON productions to work in the
 
casting department for the next James Bond film. They need a new Bond because he-
 
who-must-not-be-named is clearly not working out. They want a database of actors,
 
the movies in which they have appeared, and the director of those movies. They have
 
collected the following data for your use:
 
Actor Data
 
name, address, birth date, hair color, eye color, height in inches, weight, spouse name,
 
favorite color, screen actors guild anniversary date
 
Movie Data
 
name, year released, MPAA number, domestic box office sales, foreign box office sales,
 
DVD/Blu-ray sales
 
Director Data
 
name, address, spouse name, film school attended, directors guild anniversary date,
 
favorite lens maker
Deliverables
Build this database. You may add or rename any Pields you like. You must create a
 
relational database in Boyce-Codd normal form (BCNF). Document your database with . . .
 
1. a fully decorated and aesthetically beautiful E/R diagram.
 
2. SQL create statements for each table.
 
3. Functional dependencies for each table.
 
Then…
 
4. Write a query to show all the directors with whom actor “Sean Connery” has worked.
Hints
This is not as easy as it sounds. There are more than three tables. Impress me by using
 
entity subtypes to better represent the model.
 
Remember:
 
- Several actors can appear in the same movie under one or more directors.
 
- Actors can also be directors, and therefore directors can also be actors.
 
- Sometimes there is more than one director for a movie.
Resources

Chapter 3 in our text
 

Stack OverPlow - http://stackoverPlow.com/questions/tagged/normalization
 

Microsoft on Normalization - http://support.microsoft.com/kb/283878
                                                                           Page 1? of 1?
Powered by