Assessment
MSAccess - Assessment 2 (50 pts)
Movie Database Prototype Demonstration
(This is a “project based” assessment; it is not a quiz)
NOTE: You will place all your answers within the remainder of this document. It is to your advantage to copy your SQL commands and paste them into this document. Simply capturing your commands as an image will not allow you to run them over again if it becomes necessary. SQL Results, however, should be captured as an image using, for example, the Snipping Tool. As you prepare to respond to questions from the Amy Miles, the CEO of Regal Theaters, review the MOVIE ERD (Entity Relationship Diagram) that you generated in fulfillment of Assessment 1. The MOVIE ERD is shown below. In this Assessment you will create and populate the DIRECTOR and MOVIE tables.
Task 1. (12 pts) In your MOVIE database design reviews Amy Miles, the CEO of Regal Theaters, has asked for some clarification of two terms you mentioned repeatedly. Mod1218
Answer her questions:
Primary Key:
|
Question |
Your Answer |
|
What is the purpose of a Primary Key? |
|
|
What is the Primary Key of the MOVIE table? |
|
|
What is the Primary Key of the DIRECTOR table? |
|
Foreign Key:
|
Question |
Your Answer |
|
What is the purpose of a Foreign Key? |
|
|
Does either the DIRECTOR table or the MOVIE table have a Foreign Key? List which table(s) have a Foreign Key. |
|
|
What is the relationship between the DIRECTOR table and the MOVIE table? |
|
NOTE: In preparation to accomplishing the following tasks you MUST download the MS Access database titled “Movie_Database.accdb” and save it to your PRG140 folder. Hold down your <Ctrl> key and click the following to get this file. You may also copy and paste this link into your web browser.
https://www.dropbox.com/s/yamsg1w4u8wao9x/Movie_Database.accdb?dl=0
Task 2. (2 pts) Create the DIRECTOR table by running the following SQL command. To enter the following SQL command you must: 1) Open the Movie_Database, 2) Select the “Create” tab, 3) Select “Query Design”, 4) Click “Close” in the “Show Table” popup, and 4) Click the “SQL View” icon in the upper left corner of your Access window.
After you have successfully created the DIRECTOR table display the structure of the DIRECTOR table by right-clicking the DIRECTOR table and selecting “Design View”.
Your results should appear similar to those shown below.
Put the Results of right-clicking the DIRECTOR table and then selecting the “Design View” option:
Task 3. (2 pts) Create the MOVIE table using the following SQL command.
Put the Results of right-clicking the MOVIE table and then selecting the “Design View” option:
NOTE: You MUST use the information contained in the “Movie Fact Data Sheet.docx” when you INSERT records into the tables of the MOVIE database.
Task 4. (4 pts) Add two rows (i.e., records) to the DIRECTOR table; one record for Steven Spielberg and a second record for Ron Howard. Hint: Check out section “Adding Rows to a Table” in your eBook, starting on page 72.
Use the following SQL command to add Steven Spielberg to the DIRECTOR table:
Put your SQL INSERT statement that adds Ron Howard to the DIRECTOR table here:
Put the Results of double clicking the DIRECTOR table. Your results should match the following:
Task 5. (8pts) Add four rows (i.e., records) to the MOVIE table. Use the data values extracted from the “Movie Fact Data Sheet.docx”. Make sure the value for DIRECTOR_ID in each MOVIE record specifies the appropriate director.
Use the following SQL command to add Apollo 13 to the MOVIE table:
Put your SQL INSERT command that adds Jaws as the second row of data here:
Put your SQL INSERT command that adds ET as the third row of data here:
Put your SQL INSERT command that adds Close Encounters of the Third Kind as the fourth row of data here:
Put the Results of double clicking the MOVIE table. Your results should match the following:
Task 6. (10pts) Demonstrate a query using the MOVIE table that lists the title and length of each movie. Order your results by the title of the MOVIE.
Put your SQL command here:
Put the results of running the above SQL command here:
Task 7. (6 pts) Demonstrate a second query using the MOVIE table that lists the title and length of those movies that have a running length of more than 2 hours and 10 minutes.
Put your SQL command here:
Put the results of running the above SQL command here:
Task 8. (6 pts) Using the COUNT function, how many movies are there in the MOVIE database?
Put your SQL command here:
Put the results of running the above SQL command here:
MSAccess
-
Assessment 2
(50 pts)
Movie Database
Prototype Demonstration
(This is a “project based” assessment
;
it is not a quiz)
NOTE:
You will place all your
answers
within the remainder of this document.
It is to your advantage to copy
your SQL
commands and paste them into this document.
Simply capturing your commands as an image will not
allow you to run them over again if it becomes necessary.
SQL
Results, however, should be captured as an
image using
, for example,
the Snipping Tool.
As you pr
epare
to
respond to questions from the Amy Miles, the
CEO of Regal Theaters,
review the MOVIE ERD (Entity Relationship Diagram) that you generated in fulfillment
of Assessment 1. The MOVIE ERD is shown below.
In t
his Assessment
you will create and popul
ate
the
DIRECTOR and MOVIE tables.
MSAccess - Assessment 2 (50 pts)
Movie Database Prototype Demonstration
(This is a “project based” assessment; it is not a quiz)
NOTE: You will place all your answers within the remainder of this document. It is to your advantage to copy
your SQL commands and paste them into this document. Simply capturing your commands as an image will not
allow you to run them over again if it becomes necessary. SQL Results, however, should be captured as an
image using, for example, the Snipping Tool. As you prepare to respond to questions from the Amy Miles, the
CEO of Regal Theaters, review the MOVIE ERD (Entity Relationship Diagram) that you generated in fulfillment
of Assessment 1. The MOVIE ERD is shown below. In this Assessment you will create and populate the
DIRECTOR and MOVIE tables.