COMP207 Assignment 2 Query Processing
COMP207 Assignment 2 Query Processing Issue Date: Monday, 18 November 2019
Submission Deadline: Tuesday, 03 December 2019, 17:00 About This Assignment
This is the second of two assignments for COMP207. It is worth 10 of the total marks for this module. It consists of four questions, which you can find at the end of this document.
Submit your solutions to these questions in PDF format by the given submission deadline. Your solutions must be submitted on Vital see the detailed submission instructions below.
Accuracy and relevance are more important in your answers, so dont write large volumes in your submission, but do ensure that what you write covers what is asked for and keeps to the problem statement.
Submission Details
Please submit one PDF file with your solutions. Name your file as follows:
your student IDAssignment2.pdf
If your student ID is 12345678, then your file should be named: 12345678Assignment2.pdf.
Please submit only this file no archives.
To act as your signature for the assignment, at the top of your PDF document put your Student ID number.
Page 1 of 6
COMP207 Assignment 2 Query Processing
Your solutions must be submitted on Vital see Vital for submission instructions.
The submission deadline for this assignment is Tuesday, 03 December 2019, 17:00. Earlier submission is possible, but any submission after the deadline attracts the standard lateness penalties. Plagiarism and collusion guidelines will apply throughout the assignment submission. For details on late submissions, how to claim extenuating circumstances, etc., please see the undergraduate student handbook, which can be found at http:intranet.csc.liv.ac.ukstudentughandbook.pdf , or in Section 6 of the Code of Practice on Assessment.1
Assessment information at a glance
Assignment Number
Weighting
Assignment Circulated
Deadline
Submission Mode
Purpose of Assessment
Marking Criteria
Submission necessary in order to satisfy module requirements?
Late Submission Penalty
2 of 2
10 of the final module mark
Monday, 18 November 2019
Tuesday, 03 December 2019, 17:00
Electronically on Vital
Assessment of knowledge of SQL query processing
See description of this assignment
NA
Standard UoL Policy
Learning Outcome Assessed LO2: Demonstrate an understanding of advanced SQL topics
1 https:www.liverpool.ac.ukmedialivacuktqsdcodeofpracticeon assessmentcodeofpracticeonassessment.pdf
Page 2 of 6
COMP207 Assignment 2 Query Processing
Question 1 10 marks
The following tables form part of a hotel booking database held in a relational DBMS primary keys are underlined:
Hotel hotelNo, hotelName, city
Room roomNo, hotelNo, type, price
Booking hotelNo, guestNo, dateFrom, dateTo, roomNo Guest guestNo, guestName, guestAddress
Hotel contains hotel details and hotelNo is the primary key.
Room contains room details for each hotel and roomNo, hotelNo forms the primary
key.
Booking contains details of the bookings and hotelNo, guestNo, dateFrom forms the primary key.
Guest contains guest details and guestNo is the primary key.
Give the relational algebra expressions to return the results for the following two queries:
a List the names and cities of those hotels who charge more than 85 for a room. 5 marks
b List the names and addresses of guests who have made a booking to stay Christmas Day
2019.
5 marks
Page 3 of 6
system:
property
repairs
COMP207 Assignment 2 Query Processing
Question 2 10 marks
Consider the following database schema and example instance for a property management
pId
price
owner sqrFeet location
1
100,000
Alice
560
Lake View
2
3,400,000
Bob
2,000
Hyde Park
3
1,200,000
Bob
1,200
Hyde Park
4
5,000,000
Martha
800
Evanston
rId
pId
company date type
1001
1
M.M. Plumbing Ltd.
20131212
Bathroom
1002
2
M.M. Plumbing Ltd.
20131213
Kitchen
1003
4
Robs Double Glazing
20120101
Windows
Hints:
Attributeswithagreybackgroundformtheprimarykeyofarelatione.g,pIdforrelation property.
The attribute pId of relation repairs is a foreign key to relation property.
Give the relational algebra expressions to return the results for the following two queries:
a Get the pId, owner and location details of all properties that are larger than 900 square feet sqrFeet. 5 marks
b Get the names of repair companies company that did a repair on a property in Hyde
Park.
5 marks
Page 4 of 6
COMP207 Assignment 2 Query Processing
Question 3 20 marks
a Consider the following relation:
studentCoursesStudentID, CourseNo, Quarter, Year, Units, Grade
The relation contains the grades for the courses completed by students. Assume that in studentCourses there are 200,000 different students, each identified by their StudentID. On average, a student took 40 different courses.
If the file blocks hold 2000 bytes and each studentCourses tuple requires 50 bytes, how many
blocks will then be needed to store the relation studentCourses? b A database includes two relations Student S and Program P.
SP
Give a relational expression that could possibly return the following result:
c TranslatethefollowingrelationalalgebraintoSQL: studId,lName courseBScSTUDENT
5 marks
StudentNo
FName
LName
ProgCode
04009991
Alicia
Smith
0001
04009992
Alan
Smith
0002
04009995
Alicia
Bush
0001
04009996
John
Smith
0001
ProgCode
PName
0001
Computing
0002
Software Engineering
FName
LName
PName
Alicia
Smith
Computing
John
Smith
Computing
d Given these relations, write the SQL statement that produced the equivalent queries below: Course courseNo, courseDept, courseLeader
Student studNo, name, type, tutorId, courseNo
Two sample equivalent corresponding queries have been produced:
studno,nametypeundergradcourseDeptCompSciStudent s.courseNoc.courseNo Course and
studno,name typeundergradStudent s.courseNoc.courseNo courseDeptComp SciCourse
5 marks 5 marks
Page 5 of 6
5 marks
COMP207 Assignment 2 Query Processing
Question 4 60 marks
Consider a database with relations RA,B,C, SD,E, and T F,G.
a Give the initial query plan constructed as in Lecture 13 for the SQL query SELECT B, E, G
FROM R, S, T
WHERE A 10 AND C D AND E F AND A G;
Then use the heuristics from Lecture 16 to transform the initial query plan into an optimised logical query plan. Perform the transformation stepwise, pushing a single operator over a single operator in
each step, and indicate the heuristics you apply.
b Suppose that
R1000,AR1000,BR100,CR500; S5000,DS300,ES10;
T4000,FT4000,GT1500.
Estimate the number of tuples returned by the following queries. Explain your calculations. i A10R
ii A10 OR BbR
iiiR CDS
20 marks
6 marks 6 marks 6marks
c Suppose that in addition to the assumptions on R, S, and T from part ii, we also have the following:
Each disk block can hold up to 10 tuples.
All relations are stored in consecutive blocks on disk. No indexes are available.
What is the best physical query plan in terms of the number of disk access operations you can find for Bb AND E100R CD S? Describe your plan and show the calculation of the number of disk access
operations.
22 marks
Page 6 of 6