Database Project

AGGREGATED
CATERING SYSTEM
PROJECT REPORT
11/12/2012

Contents
1 Problem Description ..............................................................................................................................................3
1.1 Description of the organization ................................................................................................................3
1.2 Scope of the database ....................................................................................................................................3
2 Entity-Relationship Diagram ..............................................................................................................................4
3 Relational Database Schema ...............................................................................................................................5
4 Printouts of Data Input Screen Form ..............................................................................................................6
4.1 Flavor Input Screen Forms ..........................................................................................................................6
4.2 Restaurant Input Screen Forms ................................................................................................................7
4.3 Menu Input Screen Form .............................................................................................................................8
4.4 Individual Customer Input Screen Form ...............................................................................................9
4.5 Group Customer Input Screen Form .................................................................................................... 10
4.6 Special Discount Input Screen Form .................................................................................................... 11
4.7 Order Main Screen Form ........................................................................................................................... 12
4.8 Order Detail Screen Form ......................................................................................................................... 13
4.9 Delivery Company Input Screen Form ................................................................................................ 14
4.10 Delivery Employee Input Screen Form ............................................................................................ 15
4.11 Delivery Detail Screen Form ................................................................................................................. 16
5 Sample Reports ..................................................................................................................................................... 17
5.1 Restaurant Sorted By Flavor ................................................................................................................... 17
5.2 Delivery Company Sorting ....................................................................................................................... 17
5.3 Order Completed .......................................................................................................................................... 18
5.4 Order Incompleted ...................................................................................................................................... 18
5.5 Income Before Discount ............................................................................................................................ 19
5.6 Income After Discount ............................................................................................................................... 19
5.7 Menu Of Restaurant .................................................................................................................................... 20
6 Printouts of Menu Screens................................................................................................................................ 21
6.1 Aggregated Catering System Main Menu ........................................................................................... 21
6.2 Backstage......................................................................................................................................................... 21
6.2.1 Restaurant ................................................................................................................................................... 22
6.2.2 Delivery ........................................................................................................................................................ 23
6.2.3 Customer ...................................................................................................................................................... 24
6.3 Customer Inquery ........................................................................................................................................ 24
6 Who contributed to what part of our project ........................................................................................... 25
6.1 Preparation ..................................................................................................................................................... 25
6.2 Earlier Period................................................................................................................................................. 25
6.3 Later Period .................................................................................................................................................... 25
2

1 Problem Description
1.1 Description of the organization
The project of our group is aggregated catering systems. The main function is to classify the dishes offered by various restaurants for customers to select .
Customers can choose and buy dishes through our system conveniently, and the order will be submitted to the merchant automatically. After the food is cooked, it will be delivered to the customer's home by local take-away company, and the delivery status of all orders can be tracked in the backstage.

1.2 Scope of the database
The database system consists of 12 tables, 11 forms and 6 reports, our database system includes 3 modules: customer information management, business information maintenance and order status tracking. The 5 tables, Restaurant,
Menu, OrderMain, Flavor and OrderDetail, belong to business information maintenance module. IndividualCustomer, GroupCustomer, SpecialDiscount, these 3 tables belong to the customer information module. The last 3 tables,
DeliveryCompany, DeliverEmployee and DeliveryDetail, belong to the order tracking module.
Following is detailed description of the database system：

3

2 Entity-Relationship Diagram
DeliveryCompany
DCNo
DCName
DCAdd
DCCity
DCState
DCZip
DCPhoneNo

DeliveryEmlyee
ENo
EFirstName
ELastName
DCNo

Order Detail
ONo
DishNo
DishQty

DeliveryDetail
DNo
ONo
ENo
DSt

Customer

OrderMain

CNo
CAdd
CCity
CState
Czip
CPhoneNo

ONo
RNo
CNo
ODate

D,C
Group Customer

DishNo
DishName
DishPrice
R.No

Flavor
FlavorID
FlavorDesc

Individual Customer

GCName

Menu

ICFirstName
ICLastname

Restaurant
RNo
RName
RAdd
RCity
RState
RZip
RPhoneNo
FlavorID

Special Discount
C.No
R.No
Discount

4

3 Relational Database Schema
Menu (RNo, Dishno, DishName, DishPrice)
Menu.Rno references Restaurant.Rno
Restaurant (RNo, RName, RAdd, RCity, RState, RZip, RPhoneNo, FlavorID)
Restaurant.FlavorID references Flavor.FlavorID
Flavor (FlavorID, FlavorDesc)
GroupCustomer
(CNo, GCName, GCSize, GCAdd, GCCity, GCState, GCZip, GCPhone)
GroupCustomer.CNo references Customer.CNo
IndividualCustomer
(CNo,ICFirstname, IClastName, ICAcc, ICCity, ICState, ICZip, ICPhoneNo)
Individual.CNo references Customer.CNo
OrderMain (ONo, RNo, ICNo, GCNo, ODate)
OrderMain.RNo references Restaurant.RNo
OrderMain.CNo references Customer.CNo
DeliveryDetail (DNo, ONo, ENo, DSt)
DeliveryDetail.ONo references OrderMain.ONo
DeliveryDetail.ENo references DeliveryEmployee.ENo
DeliveryEmployee (ENo, EFirstName, ELastName, DCNo)
DeliveryEmployee.DCNo references DeliveryCompany.DCNo
DeliveryCompany
(DCNo, DCName, DCAdd, DCCity, DCState, DCZip, DCPhoneNo)
OrderDetail (ONo, DishNo, DishQty)
OrderDetail.ONo references OrderMain.ONo
SpecialDiscount (RNo, CNo, Discount)
SpecialDiscount.RNo references Restaurant.RNo
SpecialDiscount.CNo references Customer.Cno

5

4 Printouts of Data Input Screen Form
4.1 Flavor Input Screen Forms
In the Flavor Form, we can add a new flavor or delete unwanted flavor.
In the field below the table, we list all restaurants come from that flavor, as well as restaurant information.

6

4.2 Restaurant Input Screen Forms
In the Restaurant Form, a new restaurant and its information can be added or deleted. What’s great is the state of the restaurant can be selected from a drop-down form, no need to be manually entered in. Besides, the flavor is selected from flavorID to classify the restaurant taste.
In the table below, we list the specific information about each restaurant’s cuisine and price.

7

4.3 Menu Input Screen Form
In the Menu Input Screen Form, the new dishes as well as the price can be added or deleted. Restaurant information is selected from a drop-down form, however other information need to be manually entered in.
We list each cuisine order in the table below the input field.

8

4.4 Individual Customer Input Screen Form
In our Database System, our customers are divided into two categories, one is the ordinary individual customers, the other is corporate clients.
Personal customer table can be added to the new individual customers as well as specific information of individual customers, and also can delete individual customers. Customer state is selected from a drop-down form, do not need to be manually entered in.
In the table below the input field, we list every individual customer order’s information. 9

4.5 Group Customer Input Screen Form
In the database, our customers are divided into two categories, one is the ordinary individual customer, the other is group customer.
With the group customer form, we can add or delete a new customer’s information. And the state of the customer is selected from a drop-down form.
We list each restaurant special discount to each group at below.

10

4.6 Special Discount Input Screen Form
In the Special Discount Input Screen Form, the new restaurant discounts for corporate clients can be added or deleted. Restaurant information, RNo, is selected from a drop-down menu. Group customers information, GCNo, is the same. But the discount is manually entered in.

11

4.7 Order Main Screen Form
In the Order Main Screen Form, the new orders, order customer ID, restaurant number and order date can be added and deleted. Restaurant information, RNo, is selected from a drop-down form, no need to be manually typed in, as well as the customer number. Order date can be selected from the calendar.
We list each order detail in the table below the input field.

12

4.8 Order Detail Screen Form
In the Order Detail Screen Form, the new order detail, the number of orders for specific dishes can be added or deleted. Order number, as well as the dishes number, can be selected from a drop-down form. Dishes quantity need to be manually input in.

13

4.9 Delivery Company Input Screen Form
In the Delivery Company Input Screen Form, the new delivery company and their specific information can be added or deleted. Company state is selected from a drop-down form, while other information are manually input in.
We list the name and ID of each Delivery Company In the table below in the input field. 14

4.10 Delivery Employee Input Screen Form
In the Delivery Employee Input Screen Form, the ID and name of a new Employee can be added, you can also delete it. The ID of the company which hires the employee, DCNo, can be selected from a drop-down form. Other information are manually input in.
In the table below in the input field, we list the status of delivery order by each employee. 15

4.11 Delivery Detail Screen Form
In the Delivery Detail Screen Form, We can add a new delivery detail and status, and the corresponding order number and ID of employee who is responsible for the delivery. We can also delete a detail of the order. Besides, order number ,as well as the Employee Number, is selected from a drop-down table. After delivery is done, we select the DSt, which means the order has been delivered.

16

5 Sample Reports
5.1 Restaurant Sorted By Flavor
Here's the specific list of restaurants sorted by flavors.

5.2 Delivery Company Sorting
Here's the list of all delivery companies and the number of employees of each company. 17

5.3 Order Completed
Here's the list of orders that have been delivered.

5.4 Order Incompleted
Here's the list of orders that have not been delivered.

18

5.5 Income Before Discount
Here's the list of total income of each restaurant simply according to the order detail. 5.6 Income After Discount
In the system, restaurants offer special discounts for group customers, so that the final income should to minus the discount.
Here's the list of total income of each restaurant after group customer discount.

19

5.7 Menu Of Restaurant
Here's the specific list of menus of all restaurants.

.

20

6 Printouts of Menu Screens
6.1 Aggregated Catering System Main Menu
The main menu includes submenu of Backstage, Customer inquery and exit.

6.2 Backstage
Under the Backstage menu, there are three submenus including restaurant, delivery, customer and go back.

21

6.2.1 Restaurant
Under the restaurant menu, there are eight buttons: restaurant, menu, ordermain, flavor, orderdetail, income after discount, income before discount and go back.
By clicking the income before/after discount buttons, system will display the total income of each restaurant before and after the group customer discount.

22

6.2.2 Delivery
Under the delivery menu, there are six buttons: order completed, order incompleted, delivery company, delivery employee, delivery detail and go back.
We can learn whether a order has been delivered or not by clicking the order completed and order incompleted buttons.

23

6.2.3 Customer
Under the customer menu, there are four buttons: group customer, individual customer, special discount and go back.

6.3 Customer Inquery
Under the customer inquery menu, there are three buttons: menu of restaurant, restaurant sorted by flavor, and go back.
So that customers can get the specific list of restaurants sorted by flavors and menus of all restaurants .

24

6 Who contributed to what part of our project
6.1 Preparation
First of all, all the six members of the group gather around and discuss the main idea of the database. When the main idea is determined, we started to create the database. 6.2 Earlier Period
Yangyin Lin drew ER diagrams.
Kai Lu created the table of Group Customer, Individual Customer and Special
Discount.
Yang Yang & Peipei Chen created the table of Delivery Company, Delivery
Employee, Delivery Detail.
Yizhou Song & Gefan Meng created the table of OrderMain, OrderDetail,
Restaurant , Menu and Flavor

6.3 Later Period
Yangyin Lin integrated data of each table and created the relationship between every table.
Kai Lu created Data Input Screen Forms.
Yang Yang created Sample Reports.
Peipei Chen created Menu Screens.
Yizhou Song & Gefan Meng wrote Report.

25