• +1-617-874-1011 (US)
  • +44-117-230-1145 (UK)
Live Chat
Follow Us:

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.

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