Assignment 2: Relational Databases, Access, and SQL

Due: Due by 11:30 pm, Friday, March 6, 2020.

 Marks: 50 marks.

Learning Goals:

At the end of this assignment you will be able to:

·          Analyse a basic problem from a structured, relational perspective;

·          Create an entity-relationship (ER) diagram to solve a basic database problem;

·          Create a simple relational database using MS Access;

·          Input data into an MS Access database;

·          Create, use, and display SQL queries from a relational database.

 Submission: When completed you will upload the files to your Assignment Submission in Assignments link in ConneX.

Here you will do the following:

— Attach ALL documents for this assignment (1 Access file and 1 PDF file)

— Press the Submit Button. You should get confirmation that you have successfully submitted the assignment.

References:

 Research work for this lab can be found in your text – module 11, in the on-line Access documentation, from your lab and lecture notes, and, of course, by asking the CSC Consulting/Help Desk, your Lab Instructor, and/or your Instructor (Rich or Eduard) for assistance with any questions you might have.

 Notes:

 DO NOT LEAVE THIS LAB TO THE LAST MINUTE!!

Databases can be very picky, (some would say extremely frustrating!). Use your time to work through the Tasks in order. With database work, time spent in planning is often more than repaid when you actually go to build the database.

The questions below are to be answered in written form using MS Word. Please make sure that you include the following for this Task:

— Task 1, centred and bold as a title

— Please include a copy of each question you are attempting, with your response below.

 Task 1 has you doing some role playing. I play the role of a film studies professor and you are a database designer. You are allowed to ask questions of me (the TA can stand in for me if I’m not available). Your job is to develop a Movies and Actors database for me.

 The project seems straightforward. I need a database to hold records of all the actors and movies I am interested in. My research speciality is “actors” and the database must allow me to rate all of the actors I am interested in. I am also interested in the movies these actors appear in. I want to be able to rate and comment on each movie. Moreover, I also want to rate and make comments on the performances of each of these actors in each film.

 After interviewing me, you discover I have some requirements regarding how I do my ranking. The requirements are as follows:

 A.      I always like to rate the actors out of 10, with 10 being excellent and 1 being horrid – the database will need to take this into account. I only use integers (e.g. no 6.5s).

B.      I like to rate movies the same way.

C.      I like to rate performances by each actor in a given movie in the same manner. .

D.      I have no particular order to my data entry. Sometimes I will enter data about the actor before anything else. Sometimes I will enter data about the movie first.

E.       I expect to always have the data on actors and movies completed before I rate their performances in a given role.

You find that I have already made a start on the database, but you note that there are some problems and data discrepancies. Here is what I have done so far:

A)     (5 marks) What are two of the problems exhibited by the professor’s attempt at making a database? Why are they problems?

B)     (10 marks) As the database designer you decide to redesign the database so that there are three tables:

A table Actors that holds info about the actors. Actors will be the name of this table.

A table Movies that holds info about the movies. Movies will be the name of this table.

A table Roles that holds info on the role each actor plays in a given movie. This table will be called Roles.

In each table you will want to ensure that there is a field that will serve as a primary key.

·       Draw an entity-relationship diagram of this newly redesigned database. Use the drawing capabilities of Word (or another package) to create this diagram. Do not use the ER (Entity-Relationship) diagram from Access.

·       The ER diagram will include workable attribute names, datatypes, and keys, primary and foreign. Use Bold to name the fields that are to be considered Primary Keys. On the diagram illustrate the relationships amongst the two tables. Identify the one-to- many relationships in the entity-relationship diagram you are creating using the crow’s feet notation we discussed in class.

·       Label this image Task 1, Diagram 1: Entity Relationship Diagram. This will form the specification for your database.

 TASK 2: Creating the Database (15 marks)

Rather than providing a step-by-step “recipe” to get you to accomplish this, I am keeping the instructions fairly general and am providing lots of tips. This will allow you to “experiment” a bit.

Research tip: Use the on-line help from Access. You might want to put aside 35 minutes to go through the on-line access training . Also, ask questions at the Consultants counter, see Rich or the Senior Lab Instructor in office hours (or make an appointment). See Rich, and/or a Lab Instructor with questions too – but try and be specific with the question.

Note: You must complete all the deliverables to get any marks for this task.

Using Access, create the database you specified in Task 1 above. You will need to do the following:

Create the tables as specified in Task 1. Use the Create tab to create each table. Name one of the tables “Actors”, another “Movies”, and a third “Roles”. Remember to set your primary key indexes (fields) and your data types appropriately. After you have created the fields in all your tables, go to the Database Tools tab and use the “Relationship” tool to set the relationships you specified in Task 1. Be careful here, Access will not let you set the relationships if any of the tables are open.

Head back to the Tables. Enter the data given above into the appropriate fields in the appropriate tables. Be sure that you are consistent with you primary and foreign keys.

Add at least four new actors and at least three new movies. Use the Internet Movie Data Base (IMDB), or your own knowledge to get the necessary info on actors, movies, directors, and roles (http://www.imdb.com/ ). IMDB doesn’t have ranks on given roles – that will be up to you to make up.

When we look at your database we will be looking at the following:

 A). The Entity-Relationship Diagram from within Access (One to Many).

B). The datasheet view of the Actors, Movies, and Roles tables.

C). The design view of the Actors, Movies, and Roles tables. D). The data you entered for your actors and movies.

 TASK 3: Query the information in the database (15 marks)

Posing queries to a database is one of the main uses of a database. After having designed the Actors and Movies database you know the relationships between the various attributes (fields) in the three tables.

In your Access database, answer the following questions by writing SQL statements in SQL View to build queries. Using the key words SELECT, FROM, WHERE, and AND, design and write out how you would accomplish the queries. You will need to use the name of your tables and the name of various fields in the tables.

Use the example below as a guide — DO NOT use the query design abilities of Access for this.

Consider the following example:

SELECT First Name, Last Name

FROM Actors

WHERE Rank >= 8

Follow the above example to create the following queries:

1.        Create a query to list all the movies in the Movies table that have been rated as worse than 9? When prompted in Access call this query “Q1”.

2.        List the names of the actors (First and Last) in the Actors table that have been rated as better than 7. Make sure that the actor ratings are also visible in the response to the query. When prompted in Access call this query “Q2”.

3.        List the titles of the movies in the Movies table that have been rated as 7 or better, who directed them and what category they are. Oh yes, make sure that the ratings for the movie are also visible in the response to the query, and sort the response by category. When prompted in Access call this query “Q3”.

4.        List all the movies and roles where both the movie and the role are rated 7 or better and the category of the movie is drama? (Hint — you will need to get data from both the Movies table and the Roles table, so you will need to use the fully qualified field names.) When prompted in Access call this query “Q4”.

5.        What is the list of combinations from the Roles, Movies, and Actors tables where the rating for the movie is 7 or better and the performance by an actor in the role is 8 or better? Include only the title of the movie, the first and last name of the actor, the role the actor was playing, the ranking of the movie, the ranking of the role, and the comments on the role. When prompted in Access call this query “Q5”.