In this lab, you'll be creating a database for movie reviews. Here's what we'd like our database to be able to do:
As you'll be able to see once you're done, although our finished database will be a bit of a pain to navigate / view all of its information (note: we'll solve this in later labs), it has much of the same functionality and capabilities that sites like imdb.com have. Hopefully, after designing the tables things will make sense and you'll be able to see how we could have changed our database design to do more advanced things (like multiple directors per movie, producer information, etc.).
In this lab, we will only create the database tables and populate them with a few bits of information. Answering questions, such as "Can I see all reviews for this movie?" and "What movies was actor X in?", may be difficult to answer, for now, but will be answerable (so long as you don't mind switching through various tables of information).
Designing a database to do all of this is not simple, and there are many ways to do it. Some are better than others. We need to think about all possibilities. For example, an actor in one movie could later go on to direct another movie. Maybe even the director appears as one of the main actors in the same movie. When designing a database, we need to be able to be flexible. Making changes now (before we fill it up with data) is easier than making changes later (after we've invested time/money putting in data, that may need to be reentered).
Our database will definitely have these tables (do NOT make these right now, we'll make them step by step):
Then, we'll need a table to specify which people were actors in which movies (again, do NOT make this table right now, we'll make it soon enough). 1 person can act in many movies, and just because someone is an actor in 1 movie doesn't mean that they are an actor in another movie. So, we'll soon make a special table to kind of link people with the movies they acted in.
Let's open up Access and create a new, blank database. Be sure to remember where you save it and name it, whatever you want ("lab8" is a good name).
Now, let's create the "movies" table. Probably Access already has a new table created for you. You just need to click the save button, and Access will ask you to name it. There are a few properties about movies which do NOT change and which we always know. For example, all movies have a title, a release date, and made some money (some revenue). We will assume that all movies have exactly 1 director, but that there may be many actors in a movie. We will also assume that no two movies have the exact same title. So, how do we represent all of this?
Go to the design view of the movies table. Let's get rid of the automatically created primary key (which is a field of type "Autonumber"). Let's add in a title field (of type text, call it "title"), a revenue field (of type currency, call it "revenue"), a release date field (of type date, call it "releasedate"), and then a director field (of type number, called "director"). Change the properties of each field to make them all required. Then, make the title field the primary key of the table and click the save button.
Making a field a primary key does 2 things for us. Firstly, it can help speed up searching through the table, such as when we ask questions (known as "queries") about the data. For a small database like ours, this might not matter much, but with a big one it makes a HUGE difference. Secondly, and more important to us, it helps reduce the risk of errors in the data. By marking a field as a primary key, we tell Access to make sure there are NO two records with the same values for the primary keys. In other words, by making the title a primary key, Access will make sure that we don't accidentally try to create 2 entries in the movie table representing the same movie.
Why would we use a number to represent the director? We don't want to use their real name (text) because peoples' names change. Someone may get married, or divorced, or join a cult, or simply decide to change how their name is displayed in the movie (e.g., "J. K. Rowling" is also known as "Joanne Rowling"). We will later make a table to store people's information and we will link the identification numbers from that table to the director number in the movies table. That way, if the person changes their name, we will change it in one place (the people information table) and the director information will automatically reflect that change.
Speaking of the table holding people information, let's make that table now. Create a new table save it and call it "people." Then switch to design view. This table will hold the information any people related to our movies. For example, it will hold the names of directors and actors. What fields should we put in it? As we said earlier, we'll be using a number to represent each person. So, keep the automatically created field called "ID" whose type is "Autonumber." By using an autonumber, we'll let Access pick the identification numbers of each person for us. You should also notice that the ID field is a primary key. Again, this is to help speed up searches on the data and will make sure that no two people are given the same ID #. Go ahead and add a field called "name" of type text. This is where we'll store the persons name (e.g., "Christopher Walken" or "Sean Connery"). When you try to create a field called "name" Access complains. Recall that the word "name" has special meaning to Access and it doesn't want to get confused about which name you mean (either the built-in Access "name" or "name" meaning the field you just made). Instead, lets just rename the field to "personname". Make it a required field.
Still, we don't have a way to link an actor (each person consists of an ID and a name) with a movie. So, we'll create a table to help us. When properly working with databases there are often LOTS of tables. This table will be relatively simple. Name it "actorinformation" and get rid of the automatically created ID field using design view. We want this table to link two things: a person's id with the title of a movie that they were in. If an actor is in more than 1 movie, their ID will be in this table several times. To this actorinformation table, add in 2 new fields. The first field should be called "movie" and should be of type text. This'll be the link from this actor information table to the movie information. Then, create a new field called "actorid" of type number. Using this, we'll be able to link a person's ID to a movie's title.
So far, we haven't entered in any information. But we're almost there! Let's create one more table, to store the movie reviews in. Save and call this new table "reviews." Then, in Design View, remove the automatically created primary key and create 2 new fields. The first field should be called "title" and will be the title of the movie that the review talks about. It should be of type text. Then, create another field called "review" of type text. This is where the actual text of the review will go (e.g., "Two thumbs up!" or "A Sleeper Hit"). Make both required.
Our tables are now designed, and ready to have data entered into them. Although currently, you'll have switch through lots of tables to get a complete idea about what movie/actor information is stored, you should be able to understand/see how we can find out, for example, which people were in which movies (and who the actor was).
Our database is not very easy to use so far. We'll fix that in future labs. For now, I'll walk you through one example of how we might enter in data.
Open up the movies table in datasheet view. We've already designed the table, so now we can fill in some information.
For title, enter in "Airplane!" (no quotes). For revenue, enter in "$83,453,539." For release date, enter in 7/2/1980. Next we'd like to enter in the director information. But remember, this has to be a number and we don't have any director numbers yet entered. So, without closing the movies table, double click to open the people table. Notice how the ID field says "(New)". Remember that the ID field is an autonumber, so Access will pick an ID for us. In the personname field, type "Jim Abrahams" and hit enter. Notice that the ID field has changed and Jim Abrahams was assigned an ID. Probably this ID will be 1.
Open up the movies table again, and in the Airplane! record, in the director field type in the number 1. Hit enter, then hit save.
Now, let's add some actors. Go to the people table and create 2 more people. First create a record for "Robert Hays" and then one for "Julie Hagerty". Be sure to remember their IDs (probably they'll have an ID of 2 and 3). Even though we've entered them into the people table, they aren't considered to have been in the Airplane! movie. So, on the actors table let's establish a link between the movie and the actors. Open up the actors table. For the title field, enter "Airplane!" (no quotes, be sure to spell it exactly as you did earlier). And for the actorid enter 2 (assuming 2 was the ID of Robert Graves. Then enter another record with the title field of "Airplane!" (no quotes) and an actorid of 3 (assuming #3 was Julie Hagerty). Now, we have a way to know that Julie Hagerty and Robert Graves were in Airplane!.
Lastly, let's open up the reviews table. For the title, again type in "Airplane!" (no quotes) and for the review, type in: "A Must See!" - Ryan Moore
Now, use http://www.imdb.com/ to enter in the information for 2 more movies, time permitting. If you can't find the information for how much money a movie made, sometimes that number can be found on http://en.wikipedia.org/wiki/Main_Page or, just make up the revenue. I'd like the other information (main actors, director, etc) to be real/fact-based. If a movie has more than 2 main actors, you only have to type in the 2 most important/main actors. If a movie has more than one director, just pick one. Remember to do all the steps that we did for Airplane!, including create new entries in the actors table.
Call me over when you're done and I'll take a look at your database. Of course, if you run out of time email me what you've completed (be sure to do so before leaving the classroom).