Question 3

1. Write the DML codes to create each of the tables shown above [4 Marks]

2. Write the DDL codes to insert the data into the tables as shown above. [4 Marks]

c) Identify the Superkeys for staff relation. [5 Marks]

d) Based on the tables of the above given schema, write SQL integrity enhancement features for the following constraints:

1. bookID in Borrow must be the one that exist as code in Book .
2. Salary must be an integer value above 0.
3. joinedDate is not present or future dates. [12 Marks]

Question 4

1. Based on the table given above provide the relevant relational algebra operations that is been described in the following statements:

1. List all employees in the library whose salary are greater than 2000.00.
   5. Marks]
2. Produce a list of students, showing only the student id, name and contact number.
   5. Marks] iii. List the book title and publisher that students had borrowed (No title should be repeated).

[5 Marks]

2. Using the Book, Student, Staff and Borrow tables above, derive the output of the following relation algebra operation:

1. publisher = Oxford AND pubYear >=2005(Book) [2 marks]
2. title, author, publisher (Book) [2 marks]

3. Result1 state = Penang (Staff)

Result2 state = Penang (Student)

FinalResult sName (Result1) sName (Result2) [3 marks]

4. (

   ( studentID,bookID(Borrow))

(bookID)(

id

(

publisher = Addison Wesley (Book))))

[3 marks]