An IT company has a small library of books on computing that its employees are able to borrow. When a particular book is on loan then employees are able to reserve it. Figure 1 gives the E-R model for the library. Entity-relationship diagram 0.. Employee Borrows 0..1 Reserves 0... Book 0... Entity types Employee (StaffNo, Name) Book (BookNo, Title, Author) Additional constraints c.1 Each employee is identified by a number that takes a value in the range 001 to 999. c.2 Each book is identified by a code that takes a value in the range B001 to B999. c.3 An employee may not reserve a book that he or she is currently borrowing. Limitations 1.1 Only current loans and reservations are recorded. Figure 1 The E-R model for the library (a) Give the SQL required to create a database that represents the E-R data model for the library given in Figure 1. In your database definition, you should use: the posted foreign key approach to implement the Borrows relationship: SQL domains to implement constraints c.1 and c.2; and a check constraint to implement constraint c.3. (15 marks) (b) When an employee leaves the IT company their details are removed from the database. (i) Explain why using an SQL DELETE statement to remove the row in the employee table that corresponds to the departed employee may not always succeed. (4 marks) (ii) Describe how you could revise your solution to part (a) to enable an SQL DELETE statement to always remove an employee's details. (6 marks) An IT company has a small library of books on computing that its employees are able to borrow. When a particular book is on loan then employees are able to reserve it. Figure 1 gives the E-R model for the library. Entity-relationship diagram 0.. Employee Borrows 0..1 Reserves 0... Book 0... Entity types Employee (StaffNo, Name) Book (BookNo, Title, Author) Additional constraints c.1 Each employee is identified by a number that takes a value in the range 001 to 999. c.2 Each book is identified by a code that takes a value in the range B001 to B999. c.3 An employee may not reserve a book that he or she is currently borrowing. Limitations 1.1 Only current loans and reservations are recorded. Figure 1 The E-R model for the library (a) Give the SQL required to create a database that represents the E-R data model for the library given in Figure 1. In your database definition, you should use: the posted foreign key approach to implement the Borrows relationship: SQL domains to implement constraints c.1 and c.2; and a check constraint to implement constraint c.3. (15 marks) (b) When an employee leaves the IT company their details are removed from the database. (i) Explain why using an SQL DELETE statement to remove the row in the employee table that corresponds to the departed employee may not always succeed. (4 marks) (ii) Describe how you could revise your solution to part (a) to enable an SQL DELETE statement to always remove an employee's details. (6 marks)
