ITM 692: Special Topics in Information Technology
PROJECT - PART A (30 points)
Instructions: Read the following case and use it to perform the below tasks. Ideally, groups should be working on the project throughout the semester as each class progresses to avoid too much work at the end of the semester. The project is due on November 9, 2010 submitted in-person in class before the exam.
You are master chefs and are opening a new Italian restaurant in Albany, NY. You want to develop a system for managing the restaurant. The most important element of the management system is the database which will do several things. It should keep a detailed account of costs and revenues. This includes sales for each day based on orders, supplies (raw materials) purchased and associated costs, inventory, employee information (e.g. labor, salaries, hours, tips, and contact information), other operational costs (e.g. maintenance, washing linens, wasted food). An order would include a table number, amount of customers at the table, waiter id, and menu items, prices, tax, tip, and final cost. It will also have payment used and whether the sale was made at the bar or the dining area. Any coupons used are also indicated. Promotions and specials are maintained, e.g. half-priced menu items and coupons, etc. In addition to specials, advertisements in different forums (e.g. radio, television, newspaper, magazine) are also maintained so that they can be measured in terms of success. For example, the running period of a radio ad as well as the radio station used, cost, and other details of the advertising agreement (e.g. time of day run, # of spots) will be stored to compare to amount of sales. The cost of this advertisement will also be captured. The restaurant also has a loyalty card where for every $100 spent, customers receive a coupon for $10. The date of coupon receipt by a customer should also be listed. The loyalty card is associated with a customer's profile and linked to the purchases the customer makes. Menu items and descriptions can change seasonally, based on availability of supplies, or due to customer suggestions. Costs for making menu items and prices charged to customers can also change over time. These changes should be recorded.
Entity-Relationship Model (8pts): For each entity, identify the attributes and relationships. List all possible keys for each entity and select a suitable primary key. Draw an entity relationship model. Also, list all assumptions.
Normalization (8pts): Normalize your data to Third Normal Form and describe your normalization. List all assumptions you made to come up with your normalization.
Queries (12pts): Create the database in Access and populate the tables with the data. Put enough data to demonstrate the queries on the data. Formulate at least 12 queries, including SELECT, nested queries and Data Definition Language (DDL) queries. Run the queries and present the results of running the query. Make sure that you have both single table and multiple table queries. Also show use of aggregate functions. Use the SQL option for entering the queries. Explain what each of your queries are doing -- they should all represent answers to questions that, you, as the owners would want to ask in order to make educated strategic decisions or perform functions discussed in the description.
Project Submittal (2pts): Please provide a hard copy of the project report (including all the elements of the project report and a write-up highlighting each member's contributions.) and the database on a marked CD-R, CD-RW, DVD-R, or DVD-RW.
Project Teams: 3-4 people should work in a team. Teams should work together for the project.