University of Maryland at College ParkCMSC 424 - Database Design - Spring 2012DATABASE DESIGN PROJECT - Pizza Delivery Database1 Application OverviewThe pizza delivery database you designed for your Homework 1 will now be implemented with a Web interface.It is to keep track of CUSTOMERs, their PREFERENCEs, and for each preference the INGREDIENTs(onions, ham, bacon, etc.). Each customer may have several preferences. Each preference CONTAINs oneor more ingredients. The pizza store will also deliver soda, water, and beer! The database has to keep trackof the DRIVERs delivering, dates, and money collected.You are free to change the details, as long as the main functions described here are supported. In caseof drastic changes, you should talk to us first. The project description is intentionally somewhat vague; youare free to decide what kind of interfaces to support etc.2 Application Details2.1 DataA brief description of the data that needs to be stored in the database follows. You may need to augmentthis with additional information/tables to implement the tasks.1. Customers: ID, name (first, last), address, account info such as amount spent, method of payment(credit card, check, or cash). If credit card, then card number.2. Pizza: Pizza Sizes (small, medium, large), ingredients (peperoni, sausage, mushrooms, onions, extracheese, etc.), price data, happy hour prices, thin or thick crust, etc. Use your imagination for theingredients or get it from an online pizza place.3. Drinks: product ID, description, manufacturer, supplier, price, sales tax (6% for MD address delivery,2.5% for VA).4. Delivery people: personal info, such as ID, name, address, work hours, salary (hourly), transporta-tion info, vehicle, cost per mile, current location or route, etc.5. Transactions: All the history of purchases through the system, records of pizzas with their ingredientpurchased including price and delivery dates.2.2 Functions to be supportedThere are several sets of tasks/queries that you need to support. The main tasks are provided below. Youmay choose to add and/or substitute other tasks depending on the emphasis of your project.2.2.1 Basic Web Interface1. Customer Set up: This task permits the customer to enter his/her data. The data is analyzed off-line, delivery address is assigned to a main route, and his/her distance is computed. Integrating thistask with a web map program to obtain distances will win you some extra credit. Otherwise you areallowed to simplify the task of computing the distances (simplest option might be generate a distancevalue randomly).1CMSC 424 DB Design Project - Spring 2012 22. Customer Data Update: This task permits the customer to update some of his/her data, i.e.address, credit card, etc.3. De-activate a customer: The customer may de-activate his/her account. This task does not deletethe customer. Just archives the relevant data.4. Re-activate a de-activated customer: Reinstate the customer for delivery.5. Order Placement: The customer connects and places his order. It should provide a menu withthe pizza size, ingredients, drinks, prices, number of each item ordered, and method of payment.This function must provide a listing of prior orders which can be easily repeated or modified beforereordering.6. Order Fulfillment: When an order is accepted, a “dispatch ticket” and a “bill” are generated for it.7. Receive payment: A payment has been received from a customer, the driver logs in the paymentand time of the delivery.8. Happy Hour: From time to time, the stores makes a 2 for 1 Happy Hour on some pizzas (only). Thisshould be displayed in the ordering form and appropriately computed in the bill.2.2.2 QueriesYou have to accommodate several queries that you think are appropriate including (at least the following):1. Order Query: Customer requests to see his/her order(s) and the expected delivery time.2. Customer Transaction History: The store manager requests to see a customer’s transactions duringa specified time period.2.2.3 Statistical QueriesIf the description below is unclear, feel free to make assumptions. As long as the basic functionality issupported, minor variations in the specifics are acceptable.1. Time-Window Revenue: One of the most important statistical information for the store manager isthe cash flow at different times: hour, day, week, etc. This report should be generated on the fly for aperiod specified by the store manager and it should provide the gross for each product category (pizzaand drinks). Furthermore, the report should include similar figures for the same time period of theprevious day, week, month, and year. Note, if the time-window is larger than 24 hours, the previousday is not meaningful. Similarly, if it is larger than a week, the previous week has no meaning.2. Happy Hour Analysis Report: This report will compare statistics of each happy hour offered withthe corresponding hours of the days it was not offered for a period of two weeks, one week before andone week after the happy hour.3. Preferred Customers Report: This report generates a list of the top customers (in terms of rev-enue), how often they order in the past 30 days, the amount of money per category collected fromthem, and the average amount collected per delivery.4. Inactive Customers Report: The system generates a list of all inactive customers.CMSC 424 DB Design Project - Spring 2012 32.2.4 A stand-alone program for XML report generationSome tasks are better done using stand alone programs that sit atop the database system. These include“data ingest”, i.e., data that is arriving from external sources and needs to be integrated into the database.Similarly, if data needs to be sent to a different location (say the Headquarters), an external program maybe needed to convert the data to a common, most often XML, format.Your goal here is to write a simple program that would generate a monthly XML report of the total salesat the store. Here is an example format:<report storeid=’xyz’ month=’January’><date id=’01/01/2012’><sales>1000.00</sales></date><date id=’01/02/2012’><sales>1200.00</sales></date>...</report>The program should take as input the month and the year, should connect to the database to fetch therelevant data, and then generate the report as shown here. The program is perhaps best written in Java,but other languages like Ruby, Perl, PHP also support connecting to the database.3 Project Details and Documentation• Groups: The project is
View Full Document
Unlocking...