Design


 * Interview Transcript
 * The client requires a delivery system that shows the customers the availability of dishes and the dishes that are served by the canteen. The client also requires a list of the daily customers and past ones with their contact details and available (i.e. contact number, location, name). The client would also like to have a poll for the customers to decide next week's dishes in two ways: the dish to be continued and the theme of next week's batch of dishes.


 * Sample Forms and Reports
 * media type="custom" key="8087844"


 * Entity-relationship Diagram v1.0




 * Query List:
 * Order Queue and state of orders
 * Available Dishes
 * Highest Selling Dish
 * Dish to be continued for the week
 * Theme of dishes the following week
 * Entity-Relationship Diagram v2.0




 * Entity Relationship Diagram v2.1




 * Entity-Relationship Diagram v2.2




 * Entity Relationship Diagram v2.3




 * Physical Database Design




 * Physical Database Design v1.1




 * Physical Database Design v1.2



Tables:

media type="custom" key="8087846"

Tables' Descriptions:


 * MAIN TABLES**
 * **customers table** - keeps track of customers who have ordered from the canteen
 * **cpno** - //**primary key**// used to identify the customer
 * **room** - room number of the customer's location
 * **building** - building name of the customer's location
 * **fname** - family name of the customer
 * **gname** - given name of the customer
 * **minitial** - middle initial of the customer
 * **count -** integer value of customer order frequency
 * **user table** - a list of staff members qualified to take orders from the canteen
 * **username - //primary key//** used to identify the user
 * **password -** user password
 * **orders table -** a list of the orders done that day together with information about it
 * **order# - //auto increment, unique, primary key//** used to identify the orders
 * **cpno -** //foreign key references customers table// used to identify the customer who made the order
 * **time -** time stamp noting when the order was taken
 * ** isdone - ** boolean value checks if the order is cleared already
 * **items table -** stores the items that can be purchased from the canteen together with the needed information
 * **item# - //auto increment, unique, primary key//** used to uniquely identify an item
 * **iname -** name of the item
 * **desc -** description of the item
 * **size -** size of the item (reg,large)
 * **cat# -**//foreign key references category// identifies the category of the item
 * **cno -**//foreign key references cuisine// identifies cuisine of the item
 * **isavailable -** tells whether the item is still available
 * **price -** price of the item
 * **category table -** shows the categories of items in the canteen (drinks,dishes,snacks,cereals)
 * **cat# - //auto increment, unique, primary key//** uniquely identifies each category
 * **catname -** name of the category
 * **cuisine table -** shows the cuisines available
 * **cno -** **//auto increment, unique, primary key//** uniquely identifies each cuisine
 * ** cname - ** name of cuisine
 * **polls table -** shows the history of polls
 * **poll# -** **//auto increment, unique, primary key//** uniquely identifies each poll
 * ** pname - ** name of poll
 * ** type - ** type of poll (dish,cuisine,finished)
 * ** timestamp - ** date when poll was created


 * CONNECTOR TABLES**
 * verifies table - **a table mapping verified orders to their respected verifiers**
 * **o** **rder#** - //primary key foreign key references orders table// used to identify the orders
 * **username** - //primary key foreign key references customers table// used to identify users who validated the order
 * **timestamp** - time when the order was validated


 * **includes table** - table noting all the items included in the order
 * **order# - //primary key//,//foreign key references orders//**
 * **item# - //primary key foreign key references items//**
 * **//qty -//** notes the quantity of a certain item in an order
 * **updates table -** table noting all changes in the items database
 * **username - //primary key foreign key references customers table//** used to identify users who updated the items
 * **item# - //primary key foreign key references items table//** used to identify updated item
 * **timestamp -** timestamp when the item was updated
 * **isInDish table -** stores the dishes that are participating in polls
 * **item# -** **//primary key//,** //foreign key references items//
 * **poll# -** **//primary key//,** //foreign key references polls//
 * **dvotes -** number of votes of the dish in a certain poll
 * **isInCuisine table -** stores cuisines that are participating in polls
 * **cno -** **//primary key//,** //foreign key references cuisine//
 * **poll# -** **//primary key//,** //foreign key references polls//
 * **cvotes -** number of votes of the cuisine in a certain poll