数据库代写 comp518 cw3

数据库代写代考 DB Database SQL

comp518_cw3_2018

University of Liverpool Department of Computer Science

COMP518 – Database & Information Systems

Coursework 3

Weighting: 25%

Deadline: Thursday 13th December 2018 at 12:00

Submit your coursework as two files via the drop-box provided on Vital. Please be aware of the University guidelines on plagiarism and collusion.

  • The SQL commands you create for Part A should be submitted in a text tile (with a .sql extension). There should be no other text in the file apart from SQL comments if you want to use them. Ensure your SQL commands work on the version of MySQL installed in the department.
  • Any extra discussion and explanation required for Part A, plus the answers to the questions in Part B, should be submitted as a single PDF. You can also include your SQL commands in this file if you want to, but only those in the text file will be marked. Marking will be based on the grade descriptors for level M (FHEQ 7) modules published in the Postgraduate Student Handbook. Penalties for late submission will be applied as outlined in the University’s Code of Practice on Assessment. Part A – SQL Statements & Queries (60%) For the following questions you will find it useful to populate your database with example data. This will help you to verify that your queries are correct. You don’t need to submit your example data.
    1. 1)  Create the following schemas in MySQL using the CREATE TABLE statement. Make sure you
      define all possible keys, and that entity integrity and referential integrity are guaranteed.
      Explain any assumptions you make (remembering to include your discussion in the PDF file,
      not the text file). [6] Book ( isbn, title, publisher )
      Author ( id, name )
      Writes ( isbn, id )
      BookStore ( bsid, address, bsName ) Sells ( bsid, isbn )
    2. 2)  Provide SQL SELECT statements for the following queries.
      1. a)  Find the addresses of all the stores that sell the book ‘Database Systems’. [2]
      2. b)  Find the titles of all books written by ‘Agatha Christie’. [2]
      3. c)  Find the titles of the books which are written by ‘Agatha Christie’ but not ‘Ian Rankin’. [4]
      4. d)  Find the names of the authors who have co-written some books, and order the names in ascending order. Note that this means multiple authors are stored for some books. [5]
      5. e)  List the names of the authors who wrote more than five books, along with the number of books they wrote, in decreasing order of the number of books they wrote. [5]
      6. f)  List the names of the bookstores that sell every book by ‘Agatha Christie’. [6]
  1. 3)  Create the following schemas in MySQL using the CREATE TABLE statement. Make sure you
    define all possible keys, and that entity integrity and referential integrity are guaranteed.
    Explain any assumptions you make. [6] Employee ( eid, ename, age )
    Department ( did, dname, dtype, address ) WorksIn ( eid, did, since )
    Product ( pid, pname, ptype, pcolour ) Sells ( did, pid, quantity )
  2. 4)  Provide SQL SELECT statements for the following queries.
    1. a)  Find the names of blue products. [1]
    2. b)  Find the names of departments that sell blue products. [2]
    3. c)  Find the names of departments that sell blue products and do not have any employees older than 40 years. [4]
    4. d)  Find the ID of each department and the age of the oldest person working in it. [4]
    5. e)  Find the names of employees who are older than at least one employee working in the department called ‘Central’. [4]
    6. f)  Find the names of employees working in departments that have no employees older than 40 years. [4]
    7. g)  Find the names of employees working in departments that have sold at least five types of product. [5]

Part B – Schedules & Transactions (40%)

1) Assume that there are three transactions T1, T2 and T3 that operate (read and write) on the data items A, B and C. In the following notation, RN(X) means that transaction TN reads data item X. For example, R1(A) means that T1 reads item A. Similarly, WN(X) means that TN writes data item X.

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 schedules S1 and S2 above, do the following. Note that the marks in square brackets apply to each schedule, so the total for this question is 26 marks.

  1. a)  Create the precedence graph of the conflicts. [6]
  2. b)  Show whether the schedule is conflict-serialisable or not. If it is, show a corresponding serial schedule. If it isn’t, briefly explain why. [2]
  3. c)  Explain whether or not the schedule can occur by use of two-phase locking (2PL). [5] Question 2 appears on the next page.

2) Consider the following transactions T1 and T2, and answer the questions below.

Time T1 1

T2

read item(A)
A=A−2
write item(A)
read item(B)
B=B+1
write item(B)
read item(C)
C=C−1
write item(C)

2
3 prod=1
4 read item(A) 5
6 prod=prod*A 7 A=A−1
8
9 write item(A)

10 read item(B) 11 prod=prod*B 12
13

14
15
16
17 read item(C)

18

prod=prod*C

At time step 0 the value of A is 3, B is 5, and C is 6. Note that ‘prod’ is a local variable of the transaction that doesn’t necessarily exist in the database.

  1. a)  What are the values of data items A, B and C after time step 18? What is the value of
    local variable ‘prod’? Present your answer as a table that starts as follows, showing
    the values at each step. We can assume that ‘prod’ has no values before time step 3. [10] Time A B C prod 0 n/a 1 n/a ……………
  2. b)  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 ‘prod’ have? [2]
  3. c)  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 ‘prod’ have? [2]
3 5 6
3 5 6

Related Posts