The Broadway Café
You have recently inherited your grandfathers coffee shop, The Broadway Café, which is conveniently located in downtown Auckland. The café offers many different kinds of specialised coffees, teas, a full service bakery and homemade sandwiches, soups, and salads. Your grandfather first opened The Broadway Café in 1952 and it was a local hotspot for many years. Unfortunately, business has been steadily declining over the last five years. Although your grandfather was an expert at running the coffee shop, it is outdated. There are no computers in the store and all ordering takes place manually. Your grandfather had a terrific memory and knew all of his customers by name, but unfortunately, none of this information is located anywhere in the store. The family recipes for the baked goods and soups are also stored in your grandfathers memory. Inventory is tracked in a note pad, along with employee payroll, and marketing coupons. The café does not have a website, uses very little marketing except word-of-mouth and essentially still operates the same as it did in 1952.
1. On your way to work this morning you stopped for petrol. When you were inside paying someone broke into your car and stole your computer bag. You did not notice until you arrived at the café and began looking for your bag. As you begin to realise all of the data you just lost including customer lists, profit and loss analysis, tax returns, e-mail, sales information, payroll files, etc. you begin to wonder when you last backed up your hard drive. Now that you have been stung by a painful data loss experience you realise the importance of having a backup strategy. Develop a strategy to backup The Broadway Café data.
2. Review the data in Customers. and determine who the cafes best customers are, which best selling product and which month shows the highest sales. In addition, include the types of data quality issues you observe in the spreadsheet. Discuss how low quality information is affecting the cafés business.
3. You are working on a plan to offer an in-store music service allowing customers to sip on a mocha latte while using headphones to listen to any of 250,000 songs located on portable kiosks in the cafe. Customers can order songs they like for a set price and you will burn them on a CD and deliver the custom-burned CD to their table. The promotion is called The Broadway Cafe Music To Go package. You believe that selling music can greatly increase the cafes revenue. Prices will be comparable to Apple’s iTunes service: $6.99 for five songs, the minimum purchase. Albums will cost $12.95. To appeal to a younger set, you will eventually offer wireless downloads to laptops or portable players. At the very least, being able to listen or buy music at the cafe could enhance your core business and keep customers coming back. The time it takes a customer to order a latte, they could have any CD burned on demand. Develop two new products using network, telecommunication, or wireless tools to create new revenue streams for the cafe, such as burning CDs for your customers.
4. You understand that database technology is better equipped than spreadsheet applications to improve your ability to analyse information. Furthermore, you have a great deal of data available to turn into information to help you set the strategic direction for the café. However, your systems are not integrated and the quality of the data within the above disparate systems is low, so you cannot determine vital information such as which customers are ordering coffee and not food or music and not coffee. For example, the three details that a customer ordered four cappuccinos, three CDs and two coffee-of-the-month gift coupons, are discrete facts housed in separate systems. So you plan to build a small database for The Broadway Café to help you track and analyse customer orders and sales information.
a. Identify five different types of entities, attributes, keys and relationships in each entity you will want to consider when designing the database for The Broadway Café. [submit the answer in writing along with your answers in other questions]
b. From the five entities from 4a, create three tables in MS Access with appropriate fields, primary keys and relationships among the three tables. Populate each table with at least five rows of data.
c. Create a data entry form for one of the tables in MS Access