Share
Explore BrainMass

Creating a Database Design Using Common Sense

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 "FieldSpecs.xlxs" (also attached).
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.

Attachments

Solution Preview

The best way to attack this, or any other database design problem is to read it over several times first. It is specifying data to be stored. And, much like a word problem in math, there are sometimes subtleties that come out when you read it over a few times. In doing so, it will make the sentence at the beginning much clearer, the one that says: "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." Don't worry about defining names of columns or what types of values they will store, at least not at first. Then you should be able to obtain a general idea of what is needed. After that, deciding the names of the fields and the data type of them will be much easier.

In the first part of the assignment, you are asked to create database tables to store information about movies. I'm sure you can see that a database called, for example, "movies" will be necessary. That table can store most of the information about the movie your assignment is asking for ("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.)")

Whenever you are designing a single table to store things in, like movies, you want to assume that there will only be one row per movie. One of the things you're being asked to store are the lead actors. One approach would be to assume that there can only be a certain number of lead actors in each movie, and allocate an arbitrary number of columns for each lead actor. You could even go through all the movies you can find, counting the number of lead actors in each them, to find the maximum number there'd be. You could maybe even double that amount when you decide on the number of columns you want to allocate to store actors.

That can create a few problems though. For one, it will be hard to determine whether or not an actor is in a specific movie when you query it. You'd always have to ask whether the actor was in the "actor1" column OR the "actor2" column OR the "actor3" column, specifying each column in the database you allocated to store actors in. Furthermore, "or"s can consume a lot of resources when the database query is being processed, both computing time and temporary disk space. You might run into problems executing queries, too, not just writing them.

A better approach is to use what's called a master/detail type of data storage. In the master/detail model, the actors in a specific movie are stored in a separate table with one actor per row. The key to the table is the specific movie, or movie code (a system generated id which is also referred to as a "surrogate key"). Each row in the table for a given movie will have multiple rows, all with the same surrogate key, with each row specifying a specific actor.

For example, the movie "Summer Breakers" could have been assigned the id of 38. In the master table "movies", it will be in the row that has 38 in the "id" column. The title column will have "Summer Breakers" in it. The year it was made and all the other information would be stored in the same row.

The "movie_detail" table would have several rows, one of each actor. The first column of "movie_detail" would be the same "id" that is in the master "movies" table, though it might be called, for example, "movie_id". There would be a second column that was also part of the key, called, for example, "actor". The rows that would be stored in there for "Summer Breakers" would look like this:

"movie_id" "actor"
38 "Selena Gomez"
38 "James Franco"
38 "Vanessa Hudgens"

Another movie stored in there might be, for example, "Eat, Pray, Love". Suppose that movie is stored in the "movies" master table with the id of 102. The rows in the "movie_detail" table would look like this:

"movie_id" "actor"
102 "James Franco"
102 "Julia Roberts"

Notice now that if you wanted to find out what movies "James Franco" has been in, you could just query the "movies_detail" table. A ...

Solution Summary

This solution, consisting of about 3,000 words, takes a common sense approach to designing the specific tables that will be used to run a small business. It follows through with the design, showing how it can be expanded over time as the products the business sells expands.

$2.19