Project 2 Database (and SQL Instructions)

Although we were just getting started with the process (remember the humble Entity-Relationship diagram we drew together on the white board), I have jumped forward to the point where I suggest the database based on the answers to my questions. I used your project 1 results to determine the best shared database for all our restaurant needs. Here I document the result as well as show you the SQL statements I ran to create the database on a database server.

The Restaurant Database

createdb restaurant

which set up a blank database in its own restaurant subdirectory on the server.

The Customer Table consists of 11 fields:

Customer_ID * -- A unique integer value that guarantees each customer has a unique ID
First_Name -- Text that contains customer's self-proclaimed first name
Middle_Name -- Text that contains customer's self-proclaimed middle name
Last_Name -- Text that contains customer's self-proclaimed last name
Phone_Nbr -- Text that contains customer's self-proclaimed phone number
Street -- Text that contains customer's self-proclaimed street address
City -- Text that contains customer's self-proclaimed city
State -- Text that contains customer's self-proclaimed state
Comments -- Text containing Customer comments (with date of each comment and a ^ symbol between entries)
Income -- An integer classifying income level (0 is poorest and 5 is richest)
Mail_Status -- An integer classifying mail status (0 is no mail, 1 is email, 2 is post)

Notes: I decided the zip code would be created from the street, city, and state fields (using an on-line service provided for that purpose). If database size was more important that processing speed, I would have stored the zip code and not the city and state. The point is that zip code is redundant.

To create this table in our restaurant database, I entered the following SQL command:

CREATE TABLE Customer (
Customer_ID int,
First_Name text,
Middle_Name text,
Last_Name text,
Phone_Nbr text,
Street text,
City text,
State text,
Comments text,
Income int,
Mail_Status int );

The Meal Table consists of 7 fields:

Meal_ID * -- A unique integer value that guarantees each meal has a unique ID
Name -- A short text name for the meal for use in reports
Description -- A longer text description for the meal to help the customer decide
Price -- A floating point representation for the price of the meal
Prep_Time -- An integer for the number of minutes the meal takes to prepare
Category_LU -- An integer lookup index for the meal category
Comments -- A text field the chef can use to make comments about the meal (perhaps even the recipe)

To create this table in our restaurant database, I entered the following SQL command:

CREATE TABLE Meal (
Meal_ID int,
Name text,
Description text,
Price real,
Prep_Time int,
Category_LU int,
Comments text );

The Meal_Order Table consists of 9 fields:

Order_ID * -- An integer value that guarantees each order has a unique ID
Customer_FK -- An integer representing a valid Customer_ID from the Customer table
Meal_FK * -- An integer representing a valid Meal_ID from the Meal table
Quantity -- An integer quantity of a specific meal on the order
Delivery_LU -- An integer lookup index for the order delivery method
Payment_LU -- An integer lookup index for the order payment method
Due_Time -- A long integer value for the minute due since Jan 1, 1900.
Actual_Time -- A long integer value for the actual minute finished since Jan 1, 1900.
Comments -- A text field for entering special instructions to the chef

Notes: I decided to allow for the redundant use of the Order_ID field to allow for more than one meal per order. This will make it easier to determine the order details but will override some integrity checking the database could do for us. Also note that I am using an asterisk (*) to indicate the unique key for each table. In the meal order case, we need both the Order_ID and Meal_FK values to guarantee a unique record. And, of interest is the fact that PostGres would NOT allow me to name this table 'Order'. Order is a keyword to SQL (as you will see when creating queries that sort results automatically).

To create this table in our restaurant database, I entered the following SQL command:

CREATE TABLE Meal_Order (
Order_ID int,
Customer_FK int,
Meal_FK int,
Quantity int,
Delivery_LU int,
Payment_LU int,
Due_Time int,
Actual_Time int,
Comments text );

To create the Three Lookup Tables in our restaurant database, I entered the following SQL commands:

CREATE TABLE Category_Lookup (
Category_ID int,
Category text );

CREATE TABLE Delivery_Lookup (
Delivery_ID int,
Delivery text );

CREATE TABLE Payment_Lookup (
Payment_ID int,
Payment text );

To insert into the tables, use an INSERT INTO SQL command. For example, to create a customer record in our Customer table, I typed the following command into my SQL monitor (a monitor is an emulation of a CRT - Cathode Ray Tube - that opens as a separate interactive window on my PC and lets me type SQL commands):

INSERT INTO Customer VALUES (1, 'Barry', '', 'Sanders', '(208) 555-4681', '2167 Witchita Ave', 'Witchita', 'KS', 'Quality more important than Time', 4, 0);

Note the semi-colon on the end told the computer I was finished with the command.

The SQL monitor then replied:

INSERT 43985 1

which tells me the new customer record was given an oid (object identifier) with number 43985. The oid is a PostGres feature that adds an attribute to all tables with the name oid. You can use the oid at times when there are problems with your tables (or other times where it makes sense to do so). The oid is guaranteed to give you a unique integer number for each record. The 1 represents one record inserted. You don't change oid numbers as PostGres maintains them internally.

Since the reply tells me I have a successful addition, I now can run query requests against the table. A simple request is to show all customers in the Customer table. The proper SQL command is:

SELECT * FROM Customer;

Whereby, the SQL monitor responds with:

customer_id|first_name|middle_name|last_name|phone_nbr     |street           |city    |state|comments                        |income|mail_status

-----------+----------+-----------+---------+--------------+-----------------+--------+-----+--------------------------------+------+-----------

          1|Barry     |           |Sanders  |(208) 555-4681|2167 Witchita Ave|Witchita|KS   |Quality more important than Time|     4|          0

(1 row)

The rest of the SQL discussion for Project 2 will follow in lecture and in my updates to the The SQL Language document available from our class Web page.