Design and Creation of a Database System for Portsmouth Home Furniture Company

Task

Setting and Case Study: You will conduct in-depth study on the design and creation of a database system relating to real world concepts. The coursework tasks relate to a case study whose details are attached. If your database, your written work or final demo SQL does not relate to the case study you will be marked down even as far as a zero! You are a junior database administrator (DBA) working for a Systems Development company and have been in post approximately 9 months. During this time you have been working on existing database systems that have been developed by the Company’s Senior DBA. (You can assume that your lecturers would have this role.) One of the Company’s clients has asked for a system to be developed for them (see attached case study). The Senior DBA does not have the time to devote to this project yet and so has asked you to work out the design, development and justification. Therefore the work you produce is for a Senior DBA – to save them thinking time when starting the project – and as such you can assume they understand more about databases than you do. Your report should take this into account and you should not describe or explain topics that the Senior DBA already knows but you should analyse their importance and relevance in the given case study

Portsmouth Home Furniture (PHF) Company PHF Factory is a furniture factory that manufactures different types of furniture ranging from sofas, beds, dining sets, wardrobes, kitchen cabinets, office chairs etc. This factory covers Hampshire in England with six different showrooms throughout the region. Products are manufactured centrally and transported to all the showrooms on a regular basis for display and sales. Not all products are available at all showrooms but they have catalogues for the customers to browse all products to make their choices. Orders can be placed manually at any of the showrooms by the customers. These orders are processed and managed by the company via emails and telephone from the showroom to the factory. The deliveries are made by an external delivery company. As a new DBA working for an external systems development company you have been contacted to design and develop a database system for PHF. This new system should be able to meet all company’s requirements. It should be able to display all products with locations where they are available, product description and price. The customer details like name, address, phone number must also be available on the database for the purpose of contacting them. The order record must also be available to PHF and the details about delivery should be available to customers. Order record must indicate type of delivery either customer’s address or pickup from store and if it’s the case of the later, the pickup store must also reflect. The system should be able to search for products based on location and price. The company also allows customers to return products within the first 28 days of delivery or pickup. There is a customer loyalty scheme where customers can earn points on purchases and receive money-off coupons and special offers that are not available to normal customers. PHF has a number of staff spread across the six locations. Some are full-time, they have a permanent contract and are paid monthly. There are also a number of part-time employees who are paid an hourly rate and receive their salary weekly. The manager oversees the store managers across the company.

Typical reports should include the following but not limited to these:

1. Basic stats on all customers per showrooms for a specific time period.

2. Order record with delivery either home or pickup details.

3. Monthly income generated per location.

4. Report of products availability and their location.

Sign-off 1 (in your lab, 20 marks)- Individual or group Task T1: EERD (15 marks) Using the information given in the case study, draw an Enhanced Entity-Relationship Diagram for the database system. Do not include any attributes. The EERD should fit on ONE page and must be produced electronically. Hand-drawn diagrams will receive No marks. There are a number of free or trial-period EERD drawing software packages that you can use. 

T2: Rationale and Assumptions (5 marks) Clearly state any assumptions you have made about the system. Assumptions are made when the case study text does not give you enough information. Some assumptions can be written in the form of "The case study doesn't specify XYZ, therefore I'm assuming ABC". These assumptions should only relate to the design of the system. For example you may have decided to include or omit certain entities, or you may have decided to generalise rather than specialize some entities – tell us why you did this. (ca 200 words