Database Design Concepts Case Study Assignment

After completing the module you should be able to:

•  Understand databases and data management systems
•  Understand database design techniques
•  Design, create and document databases

Assignment Task

Scenario

You have been asked to design a database for a food franchise called FreshBurgersNow which currently has 10 outlets scattered across Scotland. The database proposed is one to cover a specific part of their ordering and employee systems.

So it must importantly contain a list of the orders taken, this is based on the customers placing an order, however only registered customers are able to make an order either one telephone or app connection. The order is then passed to the kitchen area (i.e. cooks) who will make the various items and package them ready for delivery. Finally the drivers will deliver these orders to the customer’s home address. The franchise is unique in that it does not take over the counter orders, and only concentrates on deliveries to its current database of customers.

When first registering the customer needs to provide the usual amount of personal information (e.g. name, address, email, mobile number etc.). Customers are also able to receive various promotional information via an email shot by the company that occurs every 2-3 months, this is based on a special menu that is devised by the local manager.

The staff that work for FBN also provide a similar amount of information when they start at the franchise, along with the various training courses they have completed and skill set they possess. Certain information concerning the staff employment status is also kept e.g. the National Insurance number, scanned copy of their passport and a scanned copy of their driving licence (for drivers). The various employees can be either given a role as a driver, cook, order staff or shift-leader, these are entered into the system and updated when necessary. Also some details about the basic pay rate for each member of staff will be kept on the system.

One part of the system is so the owner of the franchise can identify which member of staff takes the most orders and what are the most popular orders taken. The owner wants to track if the customer has made their payment by either cash or card. The owner can then collate this information for all the outlets, and determine which of the outlets is performing best (and of course which needs to improve its performance).

There are two menus that the customer can chose from — the regular menu and the savers menu. All of the products sold should either be on the regular menu or on the savers menu. The regular menu has a breakfast section that finishes at 11am each day. The savers menu has a start and end date and is changed monthly. For example in December they will have a festive savers menu.

The order system should keep track of the following:

 Which customer places which order.

 All items on that order.

 Customer paid by card of cash.

 Which member of staff took that order?

 Shifts for all staff members.

 Each item should relate to a food/drink product.

 The manager is responsible for keeping the stock up to date.

 The cooks do not take orders directly from customers.

Naturally NO CASE STUDY can capture all the details of the operations of the franchise; you are permitted to make any assumptions (provided you write these down).

Task 1 (LO 1.1, LO 1.2, LO 2.1, LO 2.2)

Extra notes:

The mock-ups explained in Task 1C, Task 1D – these are more for presentation and layout, so are non-functional, that is why I recommend some generic packages like MS Word. But they should show the command buttons, navigation buttons and layout for the system being proposed.

(You can however use the actual form/report generator that the database itself has, provided you know how to use it. Of course you would naturally do this once the database has being built)

Business Case

Task 1A. Explain why a relational database would be suitable information system for the organisation such as the one provided in the case study and provide (3) three reasons to support your recommendation (600 words).

Task 1B. Explain (3) three distinct advantages concerning what a database management system provides to any organisation. Note: Make these relevant to the organisation in the case study (600 words).

Requirements Definition

Read the case study carefully, and decide what are the important (i.e. key) features of the system. Construct a basic prototype that will display the following:

Task 1C. Design a set of Initial screens that can be shown to the client – this allows for the input of information to satisfy the user requirements, you should produce at a minimum (5) five forms. Submit a softcopy that has a set of screen shots that illustrate the screen designs with appropriate narrative for each screen. (200 Words)

Task 1D. Design a set of typical reports that would be appropriate for the proposed company database – this requires you to assume the role of the user/manager and list three (3) likely reports he/she might need to do their job. It must show the report, its main features, and then populate it with some likely test data. Submit a softcopy that has a set of screen shots that illustrate these proposed screen designs. (200 Words)

Note:

A suitable package to do the above task (A to D) would be Microsoft Word or any similar product.

Task 2 (LO 2.1, LO 2.2)

Extra notes:

Task 2B: A key part of the system is the Entity Relationship Diagram – you must you the correct formalism as shown in the ilearn notes and the textbook. Care must be taken to identify the candidate entities from the case study.

A second draft is typically done, and careful re-examination of the case study to ensure you have all the potential entities.

Task 2C: A complete and thorough analysis using Normalisation is required and evidence that every table satisfies 1, 2, and 3 Normal forms.

Development

Use database development strategy to decide the main elements of the database – this will include nominating the:

Fields (also the primary keys and foreign keys)

Tables

Links and Cardinalities

Task 2A. Produce a Data Dictionary – which contains the following information: table, field type, field size and field description. (400 words).

Note:

A suitable package to do Task 2B would be Microsoft Word or any similar product.

Database Design

Develop an initial prototype of this case study system, therefore in your design phase you and your team should create the following appropriate diagrams. Use the MySQL Workbench (or equivalent tool set) to create the following:

Task 2B. Propose an Entity - Relationship diagram that matches the information provided in the case study. Use the appropriate formalism and structure as explained in the theory. (Diagram along with 200 words).

Task 2C. Develop the Normalised Schema for the system you have proposed – normalise to 3rd normal form. Provide a relevant justification which shows how your ER-schema passes each of the normal forms. (Diagram along with 600 words).

Note:

A suitable package to do Task 2B would be Gliffy, EDraw, Microsoft Visio or any similar product.

Task 3 (LO 3.1, 3.2, 3.3, 3.4)

Extra notes:

Task 3A, Task 3B: Screenshots to show the tables and records inside of these tables is sufficient to show me the tables have being built and records entered.

Task 3C: User guide is done from the perspective of a novice user – that needs to learn how the system operates, you can employ re-use of the forms already devised in Task 1C and Task 1D, along with the tables you have built in Task 3A, 3B.

Implementation of a Relational Database

Task 3A. Generate a physical database, using the final normalised ER – Diagram to create this database. Use MySQL server, Oracle Express or an equivalent relational database to create the system. Provide a relevant screenshots of the final system that displays the entire set of tables as evidence of its creation. (Labelling - 100 words).

Populate the Database

Task 3B. Demonstrate the entry of six (6) records into the database system that you previously created. These records might be used to display to the client that the system is operational. Provide screenshots of the final system that displays (3) three of the tables with the mock data inserted. (Labelling - 100 words).

User Help Guide

Task 3C. Design a comprehensive user manual that will describe how to use the database system you have developed. Screenshots of the main menu and navigation between the sub-menus along with accurate descriptions is an important feature of this section of the assignment. (800 Words)

Note: You may use previous screenshots (forms, reports and data screens) to create this manual.

Improvement

Task 3D. Reflect upon your new developed understanding of database design and provide a lessons learned log – that indicates what you found important and any improvements you wish to make for the next version of the database, and other personal reflections. (200 Words)

Note:

A suitable package to do Task 3A, Task 3B would be MySQL community server or any similar product.

A suitable package to do Task 3C, Task 3D would be Microsoft Word or any similar product.

Formative Feedback Opportunity

In order to assist with your learning and to give you some early, formative feedback you are encouraged to submit a PLAN / OUTLINE of your report (i.e. not full answers – just a couple of sentences explaining what you intend to include in your answer for each section) to your tutor. If you do decide to complete the PLAN / OUTLINE then, in order to receive feedback, the document must be emailed to the tutor by 1st June 2016.

You are strongly encouraged to submit a full draft of your answers to all the tasks to your tutor by 17th July 2016.

The tutor’s email address can be found on the module front page on iLearn and you will receive feedback within seven days.

Student Guidelines

1. You should write the answers to the tasks in the form of a report, including a title page, contents page, and sections headings based on the tasks and sub-tasks.

2. You must ensure that the submitted assignment is all your own work and that all sources used are correctly attributed. Penalties apply to assignments which show evidence of academic unfair practice. (See the Student Handbook which is in the Induction Area). Please also refer to the ‘Guide to Unfair Practice in Assessment’ on the module page on ilearn.

3. You MUST underpin your analysis and evaluation of the key issues with appropriate and wide ranging academic research and ensure this is referenced using the Bournemouth University (BU) Harvard system. The ‘My Study Skills’ area contains the following useful resources; Study Skills Guide (containing a BU Harvard Referencing section) and a BU Harvard Referencing Interactive Tutorial. You must use the BU Harvard Referencing method in your assignment.

4. You should use diagrams and tables of figures where appropriate ensuring to reference their source using the BU Harvard Referencing method.

5. You are required to write your assignment within 4000 words in order for your research and summarising skills to be developed, and for effective time management. You are required to ensure that the assignment addresses all of the assessment tasks. In the interests of good academic practice, an assignment submitted with excessive word counts (i.e. more than 10% in excess of the limit) will be returned to you. You will be given a maximum of 48 hours to edit the work to reduce the word count to the permitted maximum. In the event that the submission is still regarded as excessively long then, in line with advice received from Pearson, we will be unable to accept it for marking and you will receive a Refer grade for Non-Submission.

The word count excludes the title page, reference list and appendices. Where assessment questions have been reprinted from the assessment brief these will also be excluded from the word count. ALL other printed words ARE included in the word count. Printed words include those contained within charts and tables.

6. Your assignment should be submitted as a single document. For more information please see the “Guide to Submitting an Assignment” document available on the module page on iLearn.