Database Design and Development Assignment
Assignment title: Lawrence Historical Books
Introduction
This assignment contains four parts: design; data and queries; derived data; and an evaluation. All parts of the assessment relate to the Lawrence Historical Books scenario below.
Scenario
Lawrence Historical Books is a book retailer based in London (UK) that specialises in selling historical books online and locating rare books for customers. Customers must register and set up an account in order to either purchase books or to place requests for books they want the company to locate.
A customer makes a purchase for one or more books. Books are categorised by subject. Books may have one or more authors. An author can have written one or more books.
Customers can place requests on a ‘wants list’. These requests may be for a particular book, books by a particular author, or books on a particular subject.
You have been asked to develop a single integrated database system that can keep records of customers and their purchases of particular books. This database system will also store a ‘wants lists’ for books that a specific customer would like the company to locate. Furthermore, the system should also be able to record customer complaints and whether the complaint has been resolved or not. As demonstrated in an Entity
Relationship Model, the underlying data structure should be able to provide a basis for the development of the queries requested in the assignment.
Please Note: The data shown in the assignment is not necessarily normalised and that it the candidate’s task to organise the data in the most optimal way possible. For example, the paper records shown below will not necessarily map directly to data base tables. The candidate is expected to use these tables as a starting point for their own normalisation and optimisation of the Lawrence Historical Books data. Please note also that all SQL scripts should be shown along with their results.
Below are a sample of the paper records currently kept by the company:
- A customer purchase sheet showing the customer details and the purchases for that customer.
Customer Details
Customer Number |
C569 |
Customer Name |
Trevor Denyer |
Address |
80 Sale Street, Hackney, London, E9 6EE |
Customer Telephone |
0207 544 9800 |
Credit Limit |
1000 |
. |
Purchases
Book ID |
Book Name |
Subject |
Author(s) |
B2200 |
The Road to Glory |
French Revolution |
William Doyle |
B3550 |
Citizens of France |
French Revolution |
Simon Sharma |
B4567 |
The People and Revolution |
French Revolution |
William Doyle |
B3345 |
Oxford History of the French Revolution |
French Revolution |
Bill Gough |
B3511 |
Citizens |
French Revolution |
Alice Jung and William McKenzie |
B3400 |
Origins of the French Revolution |
Napoleonic Wars |
Christine King and Arnold Kallender |
2. List of customers
Customer ID |
Customer Name |
Address |
Telephone |
Credit Limit |
C569 |
Trevor Denyer |
80 Sale Street, Hackney, London, E9 6EE, UK |
0207 544 9800 |
1000 |
C666 |
Arnie Gaiman |
3 Cephas St, London E1 4JD, UK |
0207 609 1234 |
1000 |
C721 |
Fatima Khan |
18C Romford St, London E1 1HX, UK |
0207 609 4567 |
2500 |
C873 |
Zoe Coltrane |
64-100 A1202, London E1, UK |
0207 609 9876 |
1000 |
C111 |
Bill Wordsworth |
19 Granville Square, London SE15 6DU, UK |
0208 732 5678 |
500 |
C112 |
Dave Cohen |
29B Muschamp Rd, London SE15 4EG, UK |
0208 639 8723 |
1000 |
C981 |
Rob Singh |
69B Ondine Rd, London SE15 4EA, UK |
0208 732 1234 |
1000 |
C876 |
Tammy Bryson |
760 Old Kent Rd, London SE15 1NJ, UK |
0208 732 0001 |
1000 |
C877 |
Colin Clarke |
21 Nunhead Grove, London SE15 3LZ, UK |
0208 639 8711 |
6000 |
C878 |
Maggie Fable |
1 Trinity Rd, Wood Green, London N22 8LB, UK |
0208 703 1234 |
1000 |
C456 |
Ernest Anusium |
2 Marlborough Rd, London N22 8NB, UK |
0208 703 3456 |
1000 |
C457 |
Mohammed Patel |
5 Hawke Park Rd, London N22 6RE, UK |
0208 703 5678 |
1000 |
C458 |
Sadiq Islam |
Irving Ct, London N22 5DY, UK |
0208 703 6749 |
1000 |
3. Wants List showing Customer Requests
Customer Name |
By Title |
By Author |
By Subject |
Notes |
Staff Member dealing with |
Trevor Denyer |
‘The Crowd in the French Revolution’ By Alfred Sobel |
French Revolution Napoleonic Wars |
Paperback editions only |
Robin Lane | |
Arnie Gaiman |
‘Europe’s Inner Demons’ by Norman Cohen |
Zoe Oldenburg |
English Medieval History French Medieval History |
All potential purchases should be checked with customer first. |
Cynthia Smith |
Fatima Khan |
‘India under the Mughuls’ by Manfred Able |
Indian History |
Cynthia Smith | ||
Zoe Coltrane |
‘The Fifteenth Century’ by E.F. Jacob ‘The Fourteenth Century’ by A. McKisack |
English Medieval History |
Cynthia Smith | ||
Bill Wordsworth |
‘Ancient China’ by David Smith |
Alistair Melville |
Chinese History |
Dave Lewis |
Note: This table is used to show where customers might want the company to locate a particular book, or books by a particular author, or books on a particular subject. Each category (‘By title’, ’By Author’, ’By Subject’) are separate and do not relate to each other e.g. the author of a ‘By title’ book does not relate to authors listed in the ‘By Author’ column.
- List of Staff and their specialisms
Staff Name |
Specialism |
Dave Lewis |
Chinese History |
Cynthia Smith |
Medieval History, Indian History, African History |
Robin Lane |
French Revolution, Napoleonic Wars |
Task 1 – Design (40 Marks)
- Produce an entity relationship model for the proposed database system for
Lawrence Historical Books. It should be a fully normalised model to 3rd normal form (20 marks).
- Discuss how normalisation of each of the samples of data in the scenario contributed to your finished ER diagram (10 marks).
- Produce a data dictionary for the entity relationship model (10 marks).
Task 2 – Data and Queries (32 Marks)
- Data for twenty books. Provide a screen shot of the data for these books. (1 mark).
- Data for twenty customers. Provide a screen shot of the data for these customers (1 mark).
- Data on purchases for customers including those relating to Trevor Denyer. Provide a screen shot of the data for these purchases. (1 mark)
- Data showing all book titles on the wants list and the staff member who is dealing with each request. (1 mark)
- Write a query that selects all the customers. (3 marks).
- Write a query that selects the purchases a customer has made. (3 marks).
- Write a query that selects all customers who have purchased books on the French Revolution (3 marks).
- Write a query that shows all the requested items shown on the wants list for the customer Arnie Gaiman. (3 marks).
- Write a query that counts customers interested in English Medieval History (3 marks).
- Write a query to show all customers, their addresses and credit limits for customers who have requested items by particular authors. You must show the author in relation to the requested books. (3 marks).
- Write a query that produces the output that shows all the information for a customer, including all the books they have purchased and any requests they have made. (3 marks).
- Update the customer credit limits so that any customer with a credit limit of 1000 should have it raised to 2000 (2 marks).
- Update Tammy Bryson’s address to ‘35 Kings Rd, Wood Green, London N22 5SN, UK’ (2 marks).
- Alter the database so that it keeps a record of the number of editions for a book requested by a customer. It should include the publisher’s name, year of publication, country of publication and the edition number (1st, 2nd, 3rd) (3 marks)
Task 3 – Derived Data (13 Marks)
Lawrence Historical Books has said they would like to extend the database to include costing information. A record will need to be kept of payments for staff allocations to a customer to deal with their requests. Staff will be paid £100 (UK pound sterling) for each customer they are allocated to plus a standard monthly salary of £1000 (UK pound sterling). Explain how you would derive data for a report that showed all the relevant data for staff including their standard salary and payment for each customer they are allocated to. You should include an outline of how you would derive it using SQL and specify the method of implementing it.
Task 4 – Evaluation (15 Marks)
Provide an evaluation of how the work you have done has met the requirements of the company. This should include data storage and applications. You should also discuss how you have standardised the company’s data. Your discussion should show: (1) an understanding of what the original requirements (5 marks); (2) how the initial design attempted to meet these requirements (5 marks); and (3) an overall assessment of how well the requirements have been met (5 marks).
Submission requirements
- Your submission should be in the form of a single word-processed document that includes any necessary diagrams.
- The word count for the document is 1250 words (+/- 250 words) (excluding text in any diagrams). You should explain any assumptions you have made.
- A digital version must be submitted on a CD, USB flash drive or other similarly acceptable medium, along with a copy of the developed database.
Candidate checklist
Please use the following checklist to ensure that your work is ready for submission.
Have you read the NCC Education documents ‘What is Academic | |
Misconduct? Guidance for Candidates’ and ‘Avoiding Plagiarism and Collusion: Guidance for Candidates’ and ensured that you have acknowledged all the sources that you have used in your work? |
q |
Have you completed the ‘Statement and Confirmation of Own Work’ form and attached it to your assignment? You must do this. |
q |
Have you ensured that your work has not gone over or under the recommended word count by more than 10%? |
q |
Have you ensured that your work does not contain viruses and can be run directly? |
q |