• Home
• Question
• Database Design and Development Assignment

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

Purchases

2. List of customers

3. Wants List showing Customer Requests

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

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.