COMP2400 – Relational Databases SQL Assessment
Due date: 8am, 20 September 2016
This assessment will be marked out of 20. It will count for 20% of the final grade. Below you will find 2 questions about SQL. In addition to these two questions, you need to attend a 5 minute oral exam in Week 8, which will count 2 marks. Remember, marks are assigned for the process of finding a solution, not for the result.
Instructions:
• This assignment should be done individually, and no group work is allowed.
• You must submit two files: mydatabase.sql for Question 1 and myqueries.sql for Question 2 on Wattle before the due date. For each file, we have provided a template in the folder “Mid-term SQL assessment (for COMP2400 students)” on Wattle. You must download the templates, and enter your UID into the first section of each file. You must also add your answers into the templates following the provided instructions, and ensure that your files are executable in Postgres without producing any errors. More specifically,
– For Question 1, it should be executable in your own database u1234567, i.e., “u1234567=> \i mydatabse.sql”.
– For Question 2, it should be executable in the given database moviedb, i.e., “moviedb=> \i myqueries.sql”.
• Using “psql” will connect you to your own database u1234567 by default. To connect to moviedb, you can use
– u1234567@partch: ∼$ psql moviedb
– u1234567@partch: ∼$ psql -d moviedb -U u1234567
– or use “\connect” in psql, e.g., “u1234567=> \connect moviedb”.
• No hard copy is required to submit for this assessment.
• Late submission, unless authorized by the lecturers, will attract the penalties 5% per day.
If submitted after one week of its deadline, the assignment will not be marked.
• Plagiarism will attract academic penalties in accordance with the ANU guidelines.
Good luck and enjoy the time you will spend on this assignment.
1
Question 1 7 Marks
A university manages car parking on its campus. The IT Service of the university has designed the following database schema:
• PUser(UserID, Name, Email)
• ParkingPermit(PermitTypeID, Location, Fee, Duration, Description) • ActivePermit(PermitTypeID, UserID, IssueTime, VehicleRegNo)
PUser contains user information and UserID uniquely identifies a user. ParkingPermit con- tains the information about different types of parking permits, which can be uniquely identified by PermitTypeID. ActivePermit contains the parking permits issued to users, which has the primary key {PermitTypeID, UserID}.
The template file mydatabase.sql on Wattle contains the SQL statements which were used by the IT Service to create this database. However, this initial implementation made a few mistakes, and missed some key constraints. Your task is to answer the following questions by adding SQL statements into mydatabase.sql to get the desired database. Note that, do not delete or change any existing SQL statements that were written in the template file and do not add your DROP TABLE statements into mydatabase.sql.
1.1 The attribute Fee in ParkingPermit is initially implemented as having the data type INT. Now the IT Service realises that this is not well designed because it does not allow to store fee that are decimal numbers (e.g. 178.95) but do allow to store “negative fee” (e.g. -100). Can you fix these issues by ensuring that Fee can only contain values between 0 and 10,000 which may have two decimal places? If you can, add your SQL statements into mydatabase.sql.
(1 Mark)
1.2 Can you ensure that the values of Location in ParkingPermit can only be one of the three car parks on campus: ‘Main Car Park’, ‘Building Front Reserved Parking’, or ‘Visitor Parking Area’? If you can, add your SQL statements into mydatabase.sql.
(1 Mark)
1.3 It turns out to be essential to have a phone number for each user so that they can be contacted promptly. A valid phone number should be either 10-digit numbers (mobile) or 8-digit (landline without area code). Can you add an additional attribute PhoneNumber into PUser and also ensure a valid phone number can be entered into PhoneNumer for each user? If you can, add your SQL statements into mydatabase.sql.
(1.5 Mark)
1.4 Can you ensure that the values of UserID in ActivePermit must also exist in PUser, and similarly, the values of PermitTypeID in ActivePermit exist in ParkingPermit? If you can, add your SQL statements into mydatabase.sql.
(1 Mark)
2
1.5 Write SQL statements to insert all the following records into PUser, ActivePermit, and ParkingPermit. Add your SQL statements into mydatabase.sql.
PUser
UserID
Name
Email
PhoneNumber
0
J Smith
jsmith@gmail.com
0425123456
1
Young Kerry
young10@hotmail.com
65003047
ActivePermit
PermitTypeID
UserID
IssueTime
VehicleRegNo
10000
0
2016-08-01 00:00:00
JWEUK7
10001
1
2016-08-23 00:00:00
SW4GKD
ParkingPermit
PermitTypeID
Location
Fee
Duration
Description
10000
Main Car Park
400
6 months
Employee 6-months Parking
10001
Building Front Reserved Parking
800
3 months
Reserved 3-months Parking
10003
Visitor Parking Area
10
1 hour
Visitor 1-hour Parking
(1.5 Mark)
1.6 Suppose that you want to increase the parking fee of ‘Main Car Park’ by 10%. How can you achieve this using a SQL statement?
(1 Mark)
Question 2 11 Marks
Consider a relational database moviedb on partch which has the following database schema: Movie(title, production year, country, run time, major genre)
primary key : {title, production year} Person(id, first name, last name, year born)
primary key : {id} Award(award name, institution, country)
primary key : {award name} Restriction Category(description, country)
primary key : {description, country}
3
Director(id, title, production year)
primary key : {title, production year}
foreign keys : [title, production year] ⊆ Movie[title, production year]
[id] ⊆ Person[id]
Writer(id, title, production year, credits)
primary key : {id, title, production year}
foreign keys : [title, production year] ⊆ Movie[title, production year]
[id] ⊆ Person[id] Crew(id, title, production year, contribution)
primary key : foreign keys :
{id, title, production year}
[title, production year] ⊆ Movie[title, production year] [id] ⊆ Person[id]
Scene(title, production year, scene no, description)
primary key : foreign keys :
{title, production year, scene no}
[title, production year] ⊆ Movie[title, production year]
Role(id, title, production year, description, credits)
primary key : {title, production year, description}
foreign keys : [title, production year] ⊆ Movie[title, production year]
[id] ⊆ Person[id]
Restriction(title, production year, description, country)
primary key : {title, production year, description, country}
foreign keys : [title, production year] ⊆ Movie[title, production year]
[description, country] ⊆ Restriction Category[description, country]
Appearance(title, production year, description, scene no)
primary key : {title, production year, description, scene no}
foreign keys : [title, production year, scene no]⊆Scene[title, production year, scene no]
[title, production year, description]⊆Role[title, production year, description] Movie Award(title, production year, award name, year of award,category, result)
primary key : foreign keys :
Crew Award(id, title, primary key : foreign keys :
{title, production year, award name, year of award, category} [title, production year] ⊆ Movie[title, production year] [award name] ⊆ Award[award name]
production year, award name, year of award, category, result)
{id, title, production year, award name, year of award, category} [id, title, production year] ⊆ Crew[id, title, production year] [award name] ⊆ Award[award name]
4
Director Award(title, production year, award name, year of award, category, result) primary key : {title, production year, award name, year of award, category}
foreign keys : [title, production year] ⊆ Director[title, production year] [award name] ⊆ Award[award name]
Writer Award(id, title, production year, award name, year of award, category, result) primary key : {id, title, production year, award name, year of award, category}
foreign keys : [id, title, production year] ⊆ Writer[id, title, production year] [award name] ⊆ Award[award name]
Actor Award(title, production year, description, award name, year of award,category,result) primary key : {title, production year, description, award name, year of award, category}
foreign keys : [award name] ⊆ Award[award name]
[title,production year,description]⊆Role[title,production year,description]
There are five different categories of awards: movie awards, crew awards, director awards, writer awards and actor awards. A movie can only win an award after being nominated for the award.
Your task is to answer the following questions using SQL queries. For each question, your answer must be a single SQL query and you must write your answers into the template file myqueries.sql, which can be downloaded from Wattle.
2.1 Who has won the Oscar Writer Award in 1993? List the first name and last name of that person.
(1 Mark)
2.2 Which directors have directed at least one movie when they were 28 years old or younger. List the first names and last names of the directors and the total number of movies they have directed at the age of 28 or younger.
(1 Mark)
2.3 Out of the actors/actresses who have played a role in the movie “Traffic”, who is the oldest? List the first name, last name and the current age of that actor/actress.
(1 Mark)
2.4 Which movies have received different restriction categories in New Zealand and Germany? List the titles, production years and their restriction descriptions in New Zealand and Germany.
(1 Mark)
2.5 Which movie has the most actors/actresses that have played in the same scene? List the title and production year of that movie.
(1 Mark)
2.6 Which directors who are also a writer have received an AFI Director Award? List the first names and last names of the directors and the titles and production years of their awarded movies.
(1 Mark)
5
2.7 Which romance movies have at least 3 hour running time? List the titles and production years of such movies and their running times.
(1 Mark)
2.8 Which directors have only directed thriller movies? List the first names and last names of the directors. Order your result in the descending order of last names.
(1 Mark)
2.9 Which movies have been nominated for at least two different categories of Oscar awards but never won? List the titles and production years of such movies.
(1.5 Mark)
2.10 Which actors/actresses have played in at least two different movies and every movie they have played have been directed by the same director? List the first names and last names of these actors/actresses.
+++++
6
(1.5 Mark)