COMP518 Assignment 3 (of 3)
The submission of your solutions should be in PDF or DOC format. The MySQL com mands in questions 1 and 2 should be submitted separately in a plain text format (.TXT) file. You may include your MySQL code also in the pdf file, for the sake of clarity, if you wish, but it should be stressed that only the code that appears in the txt file will be assessed. Make sure that you test that your MySQL code works in the version installed in the lab, because this is the version which is going to be used for the assessment.
Please write your full name and student id number on the first page of your submitted solutions.
Learning outcomes:
1.Critical understanding of the nature of relational databases.
2.Design and implement large-scale database systems.
3.Develop the ability to use SQL as a data definition and data manipulation language, and to develop a critical understanding of querying a relational database withSQL.
4.Develop a systematic understanding of transaction management and concurrency control in database systems
Assessment's purpose:
1.Create a relational database and express queries to a relational database by using SQL.
2.Check concurrency control.
3.Find the outcome for simultaneous transactions.
Total: 100 marks
Question One (30 marks)
Consider the following relational database schema,
?Book(isbn, title, publisher)
?Author(id, name)
?Writes(isbn, id)
?BookStore(bsid, address, bsName)
?Sells(bsid, isbn)
1.(8 marks) Create the above schemas in MySQL, using the CREATE TABLE state ment. Make sure that you define all possible keys, and that entity integrity and ref erential integrity are guaranteed. Explain in detail any assumptions you may make.
2.Provide MySQL queries for the following:
(a)(2 marks) Find the addresses of all the bookstores, which sell the book with title 'Database Systems7.
(b)(3 marks) Find the titles of all the books written by 'Agatha Christie'. 0rderthe titles in ascending order
(c)(5 marks) Find the titles of the books which are written by ‘Agatha Christie' but not ‘Ian Rankin'.
(d)(6 marks) If a book is written by more than one author, those authors ‘co-authored’ this book. Find the names of the authors who have written some ‘co-authored’ books.
(e)(6 marks) List the names of the authors that wrote more than 5 books, along with the number of the books they wrote, in decreasing order of the number of books they wrote.
Hint: A good idea would be to populate sample data into your database using MySQL. This will help you to verify that your MySQL queries are correct.
Question Two (30 marks)
Consider the following relational database schema,
?Employees(eid, ename, age)
?Department (did, dname, dtype, address)
?WorksIn(eid, did, since)
?Products(pid, pname, ptype, pcolor)
?Sells(did, pid, quantity);
1.(8 marks) Create the above schemas in MySQL, using the CREATE TABLE state ment. Make sure that you define all possible keys, and that entity integrity and ref erential integrity are guaranteed. Explain in detail any assumptions you may make.
2.Provide MySQL queries for the following:
(a)(2 marks) Find the names of departments which sell blue products.
(b)(4 marks) Find the names of departments which sell blue products and do not have any employee older than 40.
(c)(5 marks) For each department report the department-id and the age of the oldest employee working in it.
(d)(5 marks) Find the names of employees who are older than at least one employee working in department 'Central'.
(e)(6 marks) Find the names of employees working in departments which have sold at least 5 types of products.
Hint: A good idea would be to populate sample data into your database using MySQL. This will help you to verify that your MySQL queries are correct.
Question Three (26 marks)
Assume that there are three transactions T1,T2,T3 that operate (read and write) on the data items A, B, and C. We are using the following notation: RJ(X) means that the transaction TJ reads the data item X, while WJ(X) means that the transaction TJ writes on the data item X. For example R1(A) means that the transaction T1 reads the data item A, i.e., read(T1 ,A), while W3(B) would mean that the transaction T3 writes on the data item B, i.e., write(T3 ,B).
You are given the following schedules S1, S2
1.SI: R1(A),R1(B),W1(A),R2(A),R1(C),W1(C),R3(C),W2(A),R3(B),W3(A)
2.S2: R1(A),R1(B),W1(A),R2(A),W3(C),W1(C),W2(A)
For each of the above schedules
1.(5 marks) create the precedence graph of the conflicts.
SI R1(A)W2(A) to T1->T2 W1(A)R2(A) to T1->T2 W1(C)R3(C) to T1->T3
S2 R1(A)W2(A) to T1->T2 W1(A)R2(A) to T1->T2
2.(2 marks) show whether the schedule is conflict-serializable or not. In case it is conflict- serializable, show a corresponding serial schedule. In case it is not conflict-serializable, explain shortly why this is the case.
SI Since the graph is acyclic schedule, a is conflict-serializable.
T3 , T1 ,T2 is the equivalent serial schedule.
T3<-T1->T2
S2 Since the graph is acyclic schedule, a is conflict-serializable.
T3 , T1 ,T2 is the equivalent serial schedule. T1->T2
3.(6 marks) can this schedule occur by use of (two-phase locking) 2PL? Explain your answer.
Question Four (14 marks)
Consider the following transactions and
Time T1 T2
1 read item(A)
2 A=A-2
3 product = 1
4 read item(A)
5 write item(A)
6 product = product*A
7 A=A-1
8 read item(B)
9 write item(A)
10 read item(B)
11 product = product*B
12 B=B+1
13 write item(B)
14 read item(C)
15 C=C-1
16 write item(C)
17 read item(C)
18 product = product*C product*。
At time step 0 the value of A is 3, B is 5 and C is 6.
1.(10 marks) What are the values of the data items A, B and C after time step 18? What value does the "product" have1?(1.'Note that "product" is a local variable of the transaction, that does not necessarily exist in the database) You should give a table, having the values of the data items at each time step, as well as the value of the local variable "product”. We assume that the local variable "product" doesn't have a value before the time step 3. Your solution should start like in the following table.
Time A B c product
0 3 5 6 n/a
1 3 5 6 n/a
. . . . .
2.(2 marks) What are the final values of the data items A, B and C if we first execute T1, and then T2 What final value does the "product" have?
3.(2 marks) What are the final values of the data items A, B and C if we first execute T2 and then T1? What final value does the "product" have?