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)
le. You may include your MySQL code also in the pdf le, for the sake of clarity, if you wish,
but it should be stressed that only the code that appears in the txt le 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 rst page of your submitted
solutions.
Learning outcomes:
Critical understanding of the nature of relational databases.
Design and implement large-scale database systems.
Develop the ability to use SQL as a data denition and data manipulation language, and to develop a critical understanding of querying a relational database withSQL.
Develop a systematic understanding of transaction management and concurrency control in database systems
Assessment's purpose:
Create a relational database and express queries to a relational database by using SQL.
Check concurrency control.
Find the outcome for simultaneous transactions.
Failure in the assessment may be compensated for by higher marks in other components of the module. Marking of subquestions will be based on the marking descriptors of the University's Code of Practice on Assessment. Standard UoL penalty applies for late submission. Please be aware of the University guidelines on plagiarism and collusion.
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 dene 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 Systems'.
(b) (3 marks) Find the titles of all the books written by `Agatha Christie'. Order the
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 dene 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
S1: R1(A),R1(B),W1(A),R2(A),R1(C),W1(C),R3(C),W2(A),R3(B),W3(A)
S2: R1(A),R1(B),W1(A),R2(A),W3(C),W1(C),W2(A)
For each of the above schedules
(5 marks) create the precedence graph of the con
(2 marks) show whether the schedule is conict-serializable or not. In case it is con serializable, show a corresponding serial schedule. In case it is not con explain shortly why this is the case.
(6 marks) can this schedule occur by use of (two-phase locking) 2PL? Explain your answer.
Question Four (14 marks)
Consider the following transactions T1 and T2
(2 marks) What are the nal values of the data items A, B and C if we rst execute T1,and then T2? What nal value does the "product" have?
(2 marks) What are the nal values of the data items A, B and C if we rst execute T2, and then T1? What nal value does the "product" have?
contact us for this assignment solution or Send your project/assignment directly at codersarts@gmail.com
