Database Design and Development for Folly Bee Pubs

Case Brief: Folly Bee Pubs

Folly Bee Public Houses are venues that provide beverages and food in many high street locations throughout the UK. They own approximately 60 buildings in the most popular areas of large cities in the UK. Each venue sells both drinks (alcoholic and non- alcoholic) as well as having a reasonably broad food menu. They provide breakfasts, lunches and dinners.

They require a new database management system to replace the original system they had in place as this is no longer viable for the amount of data they are storing. They would like a design for a centralised database that allows web interface access for the employees.

The database keeps records of all the sales of food and drink, stock control for food and drink and employee shifts to show who is on duty at what time.

Their menus are a Breakfast menu, a Lunch menu and a Dinner menu all with several options on each. These vary in price, so there is a small breakfast for £3.10 and a Large Breakfast for £5.00 or Toast with Tea or Coffee for £2.50.

Lunches are dishes such as Lasagne £5.50, Fish and Chips £6.00 etc. and the dinner menu prices are slightly higher with, for example, Burger and a Pint for £7.50 or a Steak at £10.

These are just an example of what is on the menus. The menus may change over time but they are not expecting them to change every week. The menu and prices are the same in every location.

The drinks are the usual range of alcoholic drinks such as beer, lager and spirits. Non-alcoholic drinks such as orange juice and hot drinks such as tea and coffee.

Folly Bee needs to capture the sale of all of these items. They use this data for stock control and to place large bulk orders for food and drink. Additionally, they use it for Business Intelligence showing them when their busy times are, how many repeat customers they get, comparison of sales across venues and to plan for staffing.

In every location there are Venue Managers, Bar Managers, Bar Staff, Waiters, Kitchen Porters, Chefs and Cleaners employed. The size of the venue will determine the number of staff employed in the venue. The three main staff are the venue manager, the bar manager and the chef. These are the only staff on a full time, permanent contract. All other staff are part time and have casual contracts. Even though they are a big chain and looking for efficiencies they do not believe in zero-hour contracts so all staff are paid for a minimum of 10 hours per week.

Task T1: EERD (15 marks)

The sales are captured through electronic tills and customers can pay by cash or card.

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 Olumuyiwa and the teaching team 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 scenario.

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, Like Lucidchart which is free to create an account with your University email.

Clearly state any assumptions you have made about the system.  Assumptions are made when the case study text does not give you enough information. 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)

Create a data dictionary for the tables you have identified in your EERD. Don’t forget the intersection tables for any many-to-many relationships. The tables should contain a logical range of attributes and clearly indicate the keys. All tables should be in 3NF.

For each table, the data dictionary should include the following:

• The attributes for the table, as they will be named in the database
• The data type and size you have chosen for each attribute
• Where appropriate, whether an attribute is a Primary Key or could be an Alternate Key
• For Foreign Keys, the table they relate to

You must use the template we have provided for you at the end of this document to complete this task.

Create and populate a working database for the given case study. You should take on board the feedback from us given for all exercises and class examples on EERD and data dictionary when creating the required tables.

Write FIVE SQL statements that reflect the needs of the business. These statements should include some of the more complex SQL syntax you have learned this year.

Your tutor will sit with you, execute and assess the queries, ask questions about how you have developed the tables, why you have chosen the keys you have etc.

• The level of complexity of each query,
• The query being of use to the business,
• Good input and output formatting,
• Good design (relating back to the EERD & Data Dictionary),
• Use of any feedback you have received about your design,
• Use of additional features such as constraints etc.,
• Good differentiation among your queries

You should come to the demo with a printout of the tables, data and the queries you are running, and with some notes regarding your reflections on the unit and your work.