Database Schema for the Blue Diner Restaurant

You are the chef and owner of the Blue Diner restaurant and you have implemented a database to track the transactions at your restaurant. The schema of the the database is as follows.

CREATE TABLE Order(
OID INT NOT NULL PRIMARY KEY,
customerName CHAR(10), 
datetime TIMESTAMP,
amountpaid FLOAT,
tax FLOAT,
tip FLOAT)

CREATE TABLE OrderItem(
OID INT NOT NULL, 
ItemID INT NOT NUL,
qty INT,
specialprocess CHAR(256),
MID INT,
PRIMARY KEY(OID, ItemID),
FOREIGN KEY(OID) REFERENCES Order,
FOREIGN KEY(MID) REFERENCES MenuItem)

CREATE TABLE MenuItem(
MID INT NOT NULL PRIMARY KEY,
mname CHAR(80), 
price FLOAT,
desc CHAR(512))

CREATE TABLE Uses(
MID INT NOT NULL, 
FID INT NOT NULL, 
uqty FLOAT,
PRIMARY KEY(MID, FID),
FOREIGN KEY (MID) REFERENCES MenuItem,
FOREIGN KEY (FID) REFERENCES Ingredients)

CREATE TABLE Ingredients(
FID INT NOT NULL PRIMARY KEY,
iname CHAR(80),
unitcost FLOAT)

The order relation represents a single “check” for a meal at a particular table. An order contains multiple order items. Each order item models a customer ordering possibly multiple quantities of a menu item from a set menu. For example, a customer ordering two cheese burgers is counted as one order item instance, and a customer ordering one loco moco and one cheese burger is counted as two order item instance. Each menu item models a “dish” made from possibly multiple ingredients. For example, the menu item “loco moco” uses two hamburger patties, two eggs, 8 oz. of rice etc.

The contents of the MenuItem, Uses, and Ingredients relations typically do not change. As new customers arrive and orders food, new tuples are inserted into the Orders and OrderItem relations. The amountpaid field includes tax and tip.