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.
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:
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 |
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 |
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.
Staff Name |
Specialism |
Dave Lewis |
Chinese History |
Cynthia Smith |
Medieval History, Indian History, African History |
Robin Lane |
French Revolution, Napoleonic Wars |
Lawrence Historical Books. It should be a fully normalised model to 3rd normal form (20 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.
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).
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 |
Follow Us