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:

1. 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.

4. 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)

1. 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).

1. Discuss how normalisation of each of the samples of data in the scenario contributed to your finished ER diagram (10 marks).
2. Produce a data dictionary for the entity relationship model (10 marks).

Task 2 – Data and Queries (32 Marks)

1. Data for twenty books. Provide a screen shot of the data for these books. (1 mark).
2. Data for twenty customers. Provide a screen shot of the data for these customers (1 mark).
3. Data on purchases for customers including those relating to Trevor Denyer. Provide a screen shot of the data for these purchases. (1 mark)
4. Data showing all book titles on the wants list and the staff member who is dealing with each request. (1 mark)
5. Write a query that selects all the customers. (3 marks).
6. Write a query that selects the purchases a customer has made. (3 marks).
7. Write a query that selects all customers who have purchased books on the French Revolution (3 marks).
8. Write a query that shows all the requested items shown on the wants list for the customer Arnie Gaiman. (3 marks).
9. Write a query that counts customers interested in English Medieval History (3 marks).
10. 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).
11. 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).
12. Update the customer credit limits so that any customer with a credit limit of 1000 should have it raised to 2000 (2 marks).
13. Update Tammy Bryson’s address to ‘35 Kings Rd, Wood Green, London N22 5SN, UK’ (2 marks).
14. 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 (1^st, 2^nd, 3^rd) (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