Computer Science homework help

Computer Science homework help. 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.
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-tomany 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:
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

Computer Science homework help