This is an individual project that you will need to approach in the order of the tasks listed below. You should move through the tasks carefully, making sure that you have been successful with a task before beginning the next one: this will minimize your frustration level with this project! References to the associated Microsoft Access training videos are linked to each of the task sections below. These references should definitely help you to refresh your memory from your CIS 125 (or equivalent course) on the actions required to complete each of the tasks.
Background of the Project:
A movie buff has asked for your help in creating a Microsoft Access 2013 database solution to enter and maintain information on movies that she has viewed. You are to design, create, and implement a solution in Access for this hypothetical scenario. First, review these Microsoft Access 2013 training videos (Links to an external site.)for a refresher as you get started with your Access work (if you don’t view these, thisassignment will present a much greater challenge for you). In fact, viewing the videos first will make your experience much less frustrating.
I. Tables Tasks - In this database, you will need tables to represent Movies, Movie Types,Movie Stars, and Stars in Movies. Here is the information needed in each one of those tables:
- Movies – Movie Number (the primary key, hint: autonumber type will work for this), Movie Title, Year Released, Movie Type (hint: this will need to be linked to Type Number in the Movie Types table through a created relationship later), MPAA Rating (possible ratings are G, PG, PG-13, R, NC- 17, and NR), and Movie Length (in minutes).
- Movie Types – Type Number (the primary key, hint: autonumber type will work for this), Description (entries will be needed for the following types: Action, Comedy, Family, Drama, Horror, Sci-Fi, and Western).
- Movie Stars – Star Number (the primary key, hint: autonumber type will work for this), Star Last Name, Star First Name, Birth Month (use Number data type), Birth Day (use Number data type), Birth Year (use Number data type).
- Stars in Movies - Movie Number, Star Number (this is a composite primary key, where the two fields collectively serve as the primary key. Autonumber will not work for these).
You should create these tables in the order listed. Tip: Do not enter in data until all Tables and Relationships are created!
II. Relationships Tasks: First, watch this short video (Links to an external site.) for a demonstration of creating relationships in Microsoft Access 2013. There are three required relationships:
- Establish a relationship between Movies and Movie Types enforcing referential integrity.
- Establish a relationship between Movie Stars and Stars in Movies enforcing referential integrity.
- Establish a relationship between Movies and Stars in Movies enforcing referential integrity.
Once you have successfully established these relationships, enter a minimum of 20 records for your Movies table, using information available from the IMDb Genres (Links to an external site.)website. You may enter the movies of your choice. Be certain to add a least one movie from each of the types in your Movie Types table (see the indicated categories in item I. 2. above). You will find the Movies of the specified movie types by clicking on the links on IMDB Genre webpage. For your Movie Stars table, enter at least two stars from each of your movies entered into your Movies table. For the Stars in Movies table, you will need to enter an existing Movie Number from the Movies table and an existing Star Number of the actor starring in that movie from the Movie Stars table.
Tip: For data entry it is recommend that you enter in one row of data for each of your tables to test out that your relationships work. Don't fall into the trap of entering in 20 movies only to find out that the relationships are incorrect and you have to go back and edit each one. If you are comfortable with the data entry you may temporarily skip ahead to Section IV and create a form for data entry.
III. Query Tasks: There are four required queries:
- Create a query that prompts the user (hint: this is a parameter query, see this (Links to an external site.)video tutorial (Links to an external site.)) for a Movie Title and then display all fields associated with that movie in the Movies table plus associated Description field from the Movie Types table. Name this query QryMovieInfo.
- Create a query that lists all stars in all movies. The query must display Movie Title, Year Made, Star First Name, and Star Last Name, in that order. The query output should be sorted in ascending alphabetical order by Movie Title. Name this query QryStarsInMovies.
- Create a query that lists all stars who were younger than the age of 26 in all movies during the year in which the movie was released. The query must display Movie Title, Year Made, Star First Name, Star Last Name, and Age During Release Year (calculated field, see this (Links to an external site.)video tutorial (Links to an external site.)) in that order. The query output should be sorted in ascending alphabetical order by Movie Title. Name this query QryYoungStarsInMovies.
- Create a query that prompts the user (hint: this is a parameter query) for a Genre description and then displays all Movie Titles categorized as that genre. The query output should be sorted in ascending alphabetical order by Movie Title. Name this query QryMoviesinAGenre.
IV. Form Task: There are two required forms:
- Create a form to support the entry of new movies into the Movies table. The form should contain all fields in the Movies table and contain an appropriate title at the top of the form.
- Create a form based on the form created in item 1. above (contains all fields from the Movie table) that also contains a subform displaying each star (Star First Name, Star Last Name) in the movie (see this (Links to an external site.)video tutorial (Links to an external site.)). You can be creative with the form design!
V. Report Task: There is one required report: Create a report, grouped by Genre Description (see this (Links to an external site.)video tutorial (Links to an external site.)) (Links to an external site.), that contains Genres, all Movies within each Genre, the associated Description from the Movie Types table, and the Year that the movie was released. The report must be sorted in ascending numeric order by Genre and then by Movie Title. (Hint: you will first need to create a query producing this information and then base the report on that query).
Submission: When you have completed your Access 2013 database, submit it as an attachment to the Database Case assignment link. Only ".accdb" files are accepted. An ".laccdb" is the temporary working file of your database that only is visible when your database is open.