Assignment:
Instructions:
Given the following scenario: Create a preliminary table and field list, assign the primary keys for each table, and create a field specification using the Excel® worksheet template
Remember there are some possible inconsistencies in language due to the interview process, which you may need to resolve to get to your final results.
Charles Brinston wants to open a movie rental business. He needs you to help with database design activities. In each exercise, represent your answer with a diagram and include field specifications in the Excel worksheet template in document sharing.
1. Design a database for Charles. He is interested in movies and films and wants to keep information on movies, actors, and directors in a database, and he would like to produce the following reports:
A. For directors: list their number and name and the year they was born. If the director is deceased, list the year of death, and what movies they directed.
B. For each movie: list its number, its title, the year the movie was made, and its genre (for example, Comedy, Drama, or Science Fiction), who the lead actors were, and the director was.
C. For each movie: list its title, its number, the name of its director, the critics' rating, the MPAA rating (G, PG, PG-13, or R), the awards for which the movie was nominated, and the awards the movie won. (The movie is rated with a number of "stars." Five stars is the top rating possible. One star is the worst rating.)
D. For each lead actor/actress: list their name, number, birthplace, and the year they were born. If the actor is deceased, list the year of death.
E. For each movie: list its number and title, along with the number and names of the actors who appeared in it.
F. For each lead actor starring in each movie: list his or her number and name, along with the number and name of the other movies in which the actor starred.
2. Expand the database design you created above so that it supports the following situation: Charles wants to start a Disc rental program at his stores that he plans to call The Movie Club. He refers to his customers as "members." Every member in the club will be assigned a number. He needs to record members' names and addresses, and he needs to know what movies each member has rented and on what date it was rented and returned, and also the date the member joined the club. He will have promotions during which members can earn bonus units that they can later apply to the cost of renting discs. He needs to store the number of bonus units a member has earned.
3. Expand the database design you created in Step 1 and Step 2 so that it will also support the following situation:
Charles wants to store information about the discs the club owns. When the club purchases a disc, Ray assigns it a number. Along with the number, he stores the number of the movie on the disc (there can be more than one disc for each movie), the date the discs was purchased, the number of times it has been rented. Charles also needs to store the number of the branch to which the discs is assigned.
|
Field Specifications Sheet |
|
|
|
|
|
|
|
|
|
|
|
|
(Insert new rows as required for each Table) |
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
| Table |
Fields |
|
Data Type |
Length |
Required (Y/N) |
Key Type (PK / AK / FK) |
Parent Table if FK (table name) |
Default (Value) |
Range of Values |
Alias(s) (Alias Name) |
Generated (User /System) |
| "Table Name 1" |
|
|
|
|
|
|
|
|
|
|
|
"Field Name" |
|
varchar |
30 |
Y |
AK |
|
|
Alpha |
|
User |
|
"Field Name" |
|
integer |
30 |
N |
|
|
|
1 - 50,000 |
|
User |
|
"Field Name" |
|
Unique ID |
|
Y |
PK |
|
|
|
|
Systen |
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
| "Table Name 2" |
|
|
|
|
|
|
|
|
|
|
|
"Field Name" |
|
varchar |
30 |
Y |
|
|
|
Alpha |
|
|