Need 1 Access file and 1 PDF file
 

**If you could finish at least Task 1 and Task 2 within 24 hrs, I will hire you**

Assignment2: 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.

TASK 1: Designing the Database

(15 marks)

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:

First_N

ame

Last_Name R

an

k

Title Year Director Category Rank Movie_Review Chara. Comment Rank

Jack Nicholson 10 One Flew

Over the

Cuckoo’s Nest

1976 Milos

Forman

drama 10 A perfect blend of entertainment

and drama.

R.P.

McMurp

hy

Perfect crazy guy.

10

Jack Nicholson 10 The Departed 2006 Martin

Scorsese

drama 7 Great action, but Scorsese needed a

firmer hand in the editing suite.

Frank

Costello

Mean and nasty,

but touching.

10

Jack Nicholson 9 Batman 1989 Tim

Burton

action 8 Good plot — love the dark tones of

Gotham City. Some discontinuity in

plot.

The

Joker

Wild and crazy 7

Matt Damon 8 The Departed 2006 Martin

Scorsese
drama 7 Great action, but Scorsese needed a
firmer hand in the editing suite.

Colin

Sullivan

Sly and cunning. 9

Kim Basinger 8 Batman 1989 Tim

Burton
action 8 Good plot — love the dark tones of
Gotham City. Some discontinuity in
plot.

Vicki

Vale

Beautiful reporter 7

Christo

pher

Lloyd 9 One Flew

Over the
Cuckoo’s Nest
1976 Milos
Forman
drama 10 A perfect blend of entertainment
and drama.

Taber Quite mad 10

Christo
pher

Lloyd 9 Star Trek III:

The Search for

Spock

1984 Leonard

Nimoy

sci-fi 6 Action packed, great characters —

but the crew is getting a bit tired.

Cmdr.

Kruge

Love the nail-on

eye patch.

10

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.

http://office.microsoft.com/training/training.aspx?AssetID=RC061181381033

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:

http://www.imdb.com/

Write the following query: List the directors (first and last name) in the Directors table that

have been rated as 8 or better?

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”.

TASK 4: Adding on to the database

(5 marks)

In the same Word document you used in TASK 1, under the title TASK 4, create the

following updated ER Diagram:

Continue the role playing exercise from Task 1: Your client wants to include more

information on directors. She wants to have their names (first and last), general comments on

them, and the same rating system as she uses on actors and movies.

Your task is to update your Entity-Relationship (ER) diagram from Task 1 to include the new

table you will use to solve this problem. Create a new diagram under the Task 4 heading.

Your new diagram will use the same rules as your first ER diagram.

Here are some hints:

The minimal solution to this problem requires you to create one new table;

You will need to change a field name on one of your other tables.

You do not have to add any data, or make any changes to your actual Access database –

– simply create the new ER diagram that solves this problem.

Don’t forget to indicate primary keys in your new tables.

Don’t forget to include foreign key(s), if any, in your new tables. (3 marks)

As well, create an SQL query using the new ER diagram you just designed (no need to use

all the braces and punctuation – just the key words SELECT, FROM, WHERE, and AND)

to find and list the movie titles and the names of the directors where both the directors and

the movies have been rated as 8 or worse. (2 marks)

ASSIGNMENT DELIVARABLES:

Now, save your Word file as a PDF of the same name. (If you hand in the Word file your ER

diagrams can become disconnected. Converting to a PDF ensures that your diagrams stay

connected)

You need to submit two documents in the Assignment submission area; a PDF document

with the answers to TASK 1 and TASK 4, and an Access document with the tables from

TASK 2 and the queries from TASK 3.

Final note — the staff at the Computer Science Consultants Office (Help Desk) are there

to help you. They can’t “do” the assignment for you, but they can help out with application

problems, printer problems, understanding questions, saving documents, uploading and

moving files, etc. Call on them for help.