CMIS 320
Homework

You're taking over the wine database project left vacant by a colleague who
has entered rehab after way too much "research" in the application domain.
The following set of related tables for the database is incomplete.
Draw a crowfoot ERD of the tables.  Clearly indicate the parent-child
relationships. 
Add the foreign keys to the table definitions.  Test that it works.

Turn in the drawing and a printout of the file of table definitions
and email the file to dwills@ad.umuc.edu



CREATE TABLE users (
  cust_id Number(5) NOT NULL,
  user_name Varchar2(50) NOT NULL,
  password Varchar2(32) NOT NULL,
  PRIMARY KEY (user_name)
);

CREATE TABLE customer (
  cust_id Number(5) NOT NULL,
  surname Varchar2(50),
  firstname Varchar2(50),
  mid_initial char(1),
  title_id Number(3),
  address Varchar2(50),
  city Varchar2(50),
  state Varchar2(20),
  zipcode Varchar2(10),
  country_id Number(4),
  phone Varchar2(15),
  birth_date Date,
  PRIMARY KEY (cust_id)
);


CREATE TABLE inventory (
  wine_id Number(5) NOT NULL,
  inventory_id Number(3) NOT NULL,
  on_hand Number(5) NOT NULL,
  cost Number(5,2) NOT NULL,
  date_added Date,
  PRIMARY KEY (wine_id,inventory_id)
);

CREATE TABLE orders (
  cust_id Number(5) NOT NULL,
  order_id Number(5) NOT NULL,
  date_placed timestamp(0),
  instructions Varchar2(128),
  creditcard char(16),
  expirydate char(5),
  PRIMARY KEY (cust_id,order_id)
);

CREATE TABLE items (
  cust_id Number(5) NOT NULL,
  order_id Number(5) NOT NULL,
  item_id Number(3) NOT NULL,
  wine_id Number(4) NOT NULL,
  qty Number(3),
  price decimal(5,2),
  PRIMARY KEY (cust_id,order_id,item_id)
);

CREATE TABLE region (
  region_id Number(4) NOT NULL,
  region_name Varchar2(100) NOT NULL,
  PRIMARY KEY (region_id)
);

CREATE TABLE wine_type(
  wine_type_id Number(2) NOT NULL,
  wine_type Varchar2(32) NOT NULL,
  PRIMARY KEY (wine_type_id)
);

CREATE TABLE wine (
  wine_id Number(5) NOT NULL,
  wine_name Varchar2(50) NOT NULL,
  wine_type_id Number(2) NOT NULL,
  year Number(4) NOT NULL,
  winery_id Number(4) NOT NULL,
  description Varchar2(400),
  PRIMARY KEY (wine_id)
);

CREATE TABLE wine_variety (
  wine_id Number(5) DEFAULT 0 NOT NULL,
  variety_id Number(3) DEFAULT 0 NOT NULL,
  id Number(1) DEFAULT 0 NOT NULL,
  PRIMARY KEY (wine_id,variety_id)
);

CREATE TABLE grape_variety (
  variety_id Number(3) NOT NULL,
  variety Varchar2(50) DEFAULT ' ' NOT NULL,
  PRIMARY KEY (variety_id)
);

CREATE TABLE winery (
  winery_id Number(4) NOT NULL,
  winery_name Varchar2(100) NOT NULL,
  region_id Number(4) NOT NULL,
  PRIMARY KEY (winery_id)
);

CREATE TABLE titles (
  title_id Number(2) NOT NULL,
  title char(10),
  PRIMARY KEY (title_id)
);

CREATE TABLE countries (
  country_id Number(4) NOT NULL,
  country char(30) NOT NULL,
  PRIMARY KEY (country_id)
);