University of Wolverhampton
School of Technology
6CI007 Database Server Management
Resit Assessment
Hand in December 14th 2012
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.
15 marks
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)
Hint: In order to keep the amount of test data to a minimum, we will assume that Libloan is unlikely to run with more than six book titles and that the number of customers about which they have details is less than 11.
Take a look at the query specifications in question 4. You will need to have data values that are relevant to these queries. BUT the design of your tables and test data should not be compromised merely to support just these queries.
The creation of a good set of WELL DESIGNED test data is VERY IMPORTANT.
15 marks
6CI007 Database Server Management
Resit Assessment (continued)
3. Create appropriate primary and foreign key constraints on the tables.
15 marks
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.
30 Marks
6CI007 Database Server Management
Resit Assessment (continued)
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.
10 Marks
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.
15 Marks
6CI007 Database Server Management
Resit Assessment (continued)
The Hand-in
Your report should include
i A simple entity model (Extended Entity Relationship diagrams are not necessary)
ii All table creation statements
iii The SQL used to create your constraints
iv Listings of all test data and an explanation of the chosen test data. (Use SELECT * FROM <>; to show the test data. Do not show the INSERT statements)
v The view definition and how its data can be changed (and why)
vi The trigger definitions and demonstrations of how they work.
vii The output of all queries (properly formatted and presented) along with the code used to generate the output.
Hint : IMPORTANT : Use courier new font for presenting all code and test data (see style adopted in lecture notes)
IMPORTANT : Avoid wraparound effects when displaying output in SQL*Plus (use column formatting commands and LINESIZE commands)
PLEASE NOTE:
This is an individual assignment.
Submit the assessment to the Student Office in the MI building.
Penalties for late submission of coursework
Standard School of Technology arrangements apply.
"ANY late submission (without valid cause) will result in the grade F0 being allocated to the coursework".
Grade Attainment Criteria
The following is given as a general guideline only. Marks may vary away from this rigid framework based on the professional judgement of the module team and the overall performance of each student in attempting to accurately reflect the scenario.
6CI007 Database Server Management
Resit Assessment (continued)
Grading Criteria
Grade A:
An entity model that correctly reflects the scenario
A set of tables that adheres to the entity model and scenario
Properly designed test data and constraints
A correct set of SQL queries
A thoroughly tested view with discussion on its updatability
Triggers that are properly coded and tested with illustrative examples
B: The answer must almost correctly reflect the scenario
A set of tables that generally adhere to the entity model and scenario
Properly designed test data and constraints
A set of SQL queries that is mostly correct
A properly constructed view with partial testing and discussion
Triggers that are properly coded and tested
C: The answer must generally reflect the scenario
A set of tables that generally adhere to the entity model and scenario
Properly designed test data and constraints
A set of SQL queries that is generally correct
A properly constructed view with some testing and discussion
Triggers that show evidence of testing
D: The answer must be at least supportive of the scenario
A set of tables that reflect the entity model and scenario in some meaningful way
Test data and constraints that support most of the queries
A set of SQL queries that show evidence of SQL knowledge and skills
A view with some testing and discussion
Triggers that show some evidence of trigger processing knowledge
E: The answer does not reasonably reflect the scenario although some good points are included.
Entity-relationship diagram produced, but has major flaws.
Few queries correctly specified
View has little or no discussion of its updatability
Little evidence of triggers and testing
F: The answer does not reasonably reflect the scenario and has no
redeeming features.
This assignment is intended as an individual piece of coursework. On no account should you work on the assignment in groups to produce a group answer.