In this assignment you will build a small database to support the needs of a private library company called Libloan. To do this you will need to create tables to support the following scenario.
Libloan is a small library company that lends books to customers. Customers may borrow books for a period of 5 days and pay fees to hire the books of between one and five pounds depending on the book. The start date of each hiring of a book, and the borrower are recorded. Customer names, gender, telephone numbers, and address need to be recorded. Each time a customer borrows a copy they will give an opinion of the book (the evaluation is a number from 0 to 5). Libloan need to record the evaluation of each book by customers who borrow it. It is not a requirement that all books recorded on the system have copies in the library and it is also not required that they are actually borrowed. Neither is it required that every customer has to have borrowed a book. In order to cater for demand, the library stocks multiple copies of books in different locations (towns) as appropriate.
1. Design an Entity model and construct a set of tables with suitably defined columns to support this scenario.
2. Populate the tables with appropriate test data, bearing in mind the following :
a. There may be some books that have yet to be borrowed.
b. There may be some customers who have yet to borrow a book.
c. All copies of books are uniquely identified by a single key column called copy_id
d. Loan records are identified by a combination of foreign key columns (not by a single column surrogate key)
3. Create appropriate primary and foreign key constraints on the tables.
4. Develop a set of queries as follows :
a. Find details of all books stocked in London
b. Find the book that has the most copies.
c. Find the total number of hirings of each book.
d. Show details of the books with the total revenue that they each generate based on the hirings of copies of those books.
e. Show details of customer names and the titles of the books that they have borrowed
f. Write and test a query to list the title and purchase price of each book. Add a column that compares the cost of the book to the average cost i.e., shows the difference between the book cost and the average cost of all books.
g. List all customers who have not hired a book located in York.
h. Find the copies of books that have their number of hirings below
the average hirings for copies of that book.
i. Write and test a query to list the customer ID and name of every
Customer along with the books that they have hired within the past 200 days. Include starting date, ending date, and location name for those hirings. All customer details (ID and name) should be included in the output, whether or not they have actually borrowed any books.
j. Find names of all female customers who have borrowed a book
in 2005.
5. Develop a view that shows details of all books on record, along with the details of their copies. Include books for which there are presently no copies in the library. Display the contents of the view.
Test the view to show its ability to support DML activity. This should include examples of :
i Separate updates on each individual column within the view
ii Inserts of new rows into the view
iii Deletes of rows from the view
Comment on the outcomes with respect to the ‘updatability' of the view
Hint : You may wish to rollback any changes you have made to the data in order to obtain a consistent set of test data ready for question 6.
6. Create triggers that enforce the following business rules :
a Ensure that copies of books stored in London cannot be
borrowed during December.
b If a customer gives a zero evaluation, the details of their hiring (customer name, which book, the date of hiring, location of the copy and evaluation) must be placed in an audit table.
Hint: You will need to construct an audit table with the appropriate columns of correct datatype.
c Ensure that records cannot be deleted from the loan table if the start_date of the loan is earlier than the current date.
Test all of your triggers with statements that fire the trigger. Show the trigger code and the output that they generate Discuss their behaviour in your tests.