数据库代写 database

程序代写 CS代考

ANUC2400 – RELATIONAL DATABASES

SEMESTER 2 – 2018

Assignment 3

Due date: Friday week 13, 5:00pm

This assignment will be marked out of 15. It will count for 15% of the final grade. Below you will find 2 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 providing the details of this assignment including student IDs.
  • You need to submit your assignment on Wattle by 5:00pm, Friday Week 13.
  • Late submission, unless authorized by the lecturers, will attract the penalties 5% per day. Assignment submissions received after one week of the deadline will not be marked.
  • Plagiarism will attract academic penalties in accordance with the ANU guidelines.

 

 

 

 

 

 

 

 

 

 

 

  1. Question 1 (12 marks):

Consider the LIBRARY relational database schema shown below which is used to keep track of books, borrowers, and book loans.

 

Your tasks are as follows: –

Write down relational expressions for the following queries.

(a) For each book that is loaned out from the ‘Georgetown’ branch retrieve the book title, the author’s name, and number of copies remained.

 

(b) For all the authors, retrieve name, book_ id, title of the book and name of publisher of the books written by them.

 

(c) Retrieve the names, addresses, phone numbers of all the borrowers who have borrowed the book ‘Gone with the Wind’.

 

(d) For each book borrowed by a borrower Simon Hill, retrieve the title and the number of copies owned by the library branch whose name is ‘Georgetown’.

 

  1. Question 2 (3 marks):

 

Consider the Employee database schema:

 

Staff (sid: integer, sname: string, position: string, age: integer, did: integer)

Department (name: string, address: string, room: string, did: integer)

Project (pnum: integer, pname: string)

Customer (cid: integer, cname: string, orderid: integer)

 

For the following transaction (i.e. Transaction 1), state and explain the SQL isolation level that is suitable.

 

Transaction 1: Change department for a staff identified by his/her sid from one department to another department.

Related Posts