COMP2400/COMP6240 – Relational Databases
Assignment 3
Due date: 10am, 11 October 2016
This assignment will be marked out of 10. It will count for 10% of the final grade. Below you will find 3 questions to reach this score. Marks are assigned for the process of finding a solution, not only for the result. Hence, include all essential ideas and steps that are necessary to derive a solution.
Instructions:
• This assignment should be done individually or by a pair of students.
• Your submission must include Assignment Cover Sheet, which is available from the
course website on Wattle.
• Hand written submissions will not be marked.
• You need to submit your assignment as a hard copy to the assignment box in the student foyer (i.e., on the ground floor of the CSIT building). You are also required to submit an electronic copy on Wattle.
• 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.
Question 1 3 Marks
Consider the following relational database schema about Twitter:
• User = {uid, name} with the primary key {uid};
• Tweet = {tid, owner id, time} with the primary key {tid} and the foreign key [owner id] ⊆ User[uid];
• Follow = {uid, follower id} with the primary key {uid, follower id} and the foreign keys [uid] ⊆ User[uid] and [follower id] ⊆ User[uid];
1
2 Assignment 3
• Tag = {tid, tag label} with the primary key {tid, tag label} and the foreign key [tid] ⊆ Tweet[tid].
Your tasks are to answer the following questions using relational algebra expressions.
1.1 Find all users who follow a user which has posted a tweet tagged by “ANU”. Show the uids and names of the users who follow.
(1 Mark)
1.2 Find all tweets that have exactly one tag. Show the tids of these tweets and their corresponding tag labels.
(1 Mark)
1.3 Find all users who follow at least two other users but have never posted any tweet. Show the uids and names of the users.
Question 2
We have a hotel booking system with the following relational database schema:
• Employee(eno, ename, ird) with the primary key {eno},
• Room(rno, floor, facilities) with the primary key {rno},
• Guest(gname, address, phone) with the primary key {gname, address},
(1 Mark)
5 Marks
• Booking(gname, address, rno, date, eno, price) with the primary key {gname, address, rno, date} and the foreign keys:
[gname, address] ⊆ Guest[gname, address] [rno] ⊆ Room[rno]
[eno] ⊆ Employee[eno]
Consider the following sample relations over the relational database schema:
Room
rno
floor
facilities
11
Level 18
Spa, Tennis court
13
Level 10
13 Bowling lanes
15
Level 18
Spa, Tennis court
17
Level 18
Pool, Pool table
eno
ename
ird
1
Daffy
875-649-322
2
Tom
875-657-300
3
Lindsay
875-657-667
COMP2400/COMP6240 – Relational Databases 3
Guest
gname
address
phone
Pepe
3 Tuner St
0800 443344
Taz
1 Bunker Hill
0800 232323
Coyote
90 Ring Road
0800 838383
Booking
gname
address
rno
date
eno
price
Pepe
3 Tuner St
11
29/04/2011
1
400
Coyote
90 Ring Road
17
29/04/2011
1
300
Pepe
3 Tuner St
13
13/04/2011
1
500
Taz
1 Bunker Hill
13
10/04/2011
1
500
Given the following relational algebra query:
πgname,address,phone(Guest) − πR1.gname,R1.address,R1.phone(σR1.gname=R2.gname(σR1.address=R2.address
(σR1.rno̸=R2.rno(ρR1(Room ◃▹ Guest ◃▹ Booking) × ρR2(Room ◃▹ Guest ◃▹ Booking))))) Your tasks are to do the following:
2.1 Draw one query tree for the given relational algebra query.
(1 Mark)
2.2 Evaluate the given relational algebra query over the given relational database, and show each of your results as a table, including: the attribute names and tuples. For example,
Employee
eno
ename
ird
1
Daffy
875-649-322
2
Tom
875-657-300
3
Lindsay
875-657-667
(1 Mark)
(a) Show the result of ρR2(Room ◃▹ Guest ◃▹ Booking).
(b) ShowtheresultofπR1.gname,R1.address,R1.phone(σR1.gname=R2.gname(σR1.address=R2.address (σR1.rno̸=R2.rno(ρR1(Room ◃▹ Guest ◃▹ Booking) × ρR2(Room ◃▹ Guest ◃▹
Booking))))).
4
Assignment 3
2.3
2.4
(c) Show the final result of the given relational algebra query.
Supposethatyouhaveadecentqueryoptmiserforoptimisingrelationalalgebraqueries. How would it optimise the given relational algebra query? You need to provide justi- fication for how the given relational algebra query can be optimised. Your marks will depend on how good your optimisation solution is.
(2 Marks)
Suppose that this hotel booking system allows a number of employees of the hotel to do the following concurrently:
• check an existing booking record,
• add a new booking record,
• modify an existing booking record, and • delete an existing booking record
In terms of the four SQL isolation levels we discussed in the lectures, which one would you like to use for concurrency control here? Explain why you use it.
(1 Mark)
Question 3 2 Marks
Consider the following three transactions T1, T2 and T3 which are concurrently running without any form of concurrency control. Each transaction is processed from left to right, where readij(X) and writeij(X) indicate that a transaction Ti reads and writes an object X (A or B) in its jth step, respectively.
−T1 :read11(A);read12(B);write13(B);write14(A);commit1 (B:=B+100) (A:=A+200)
−T2 :read21(B);read22(A);write23(A);abort2 (A:=A-50)
−T3 :read31(B);read32(B);write33(B);commit3 (B:=B-300)
Your tasks are to answer the following questions:
3.1 Are there any problems that may occur on T1? If any, explain why problems occur. 3.2 Are there any problems that may occur on T2? If any, explain why problems occur. 3.3 Are there any problems that may occur on T3? If any, explain why problems occur.
+++++