----------------------------------------------------------------------------------------
-- Postgres-Create.sql
--
-- SQL statements for table creation for CAP2 database
-- 
-- Derived from the CAP examples in _Database Principles, Programming, and Performance_, 
--   Second Edition by Patrick O'Neil and Elizabeth O'Neil
-- 
-- Modified by Alan G. Labouseur
-- 
-- Tested on Postgres 8.3   (For earlier versions, you may need
--   to remove the "if exists" clause from the DROP TABLE commands.)
----------------------------------------------------------------------------------------

-- Connect to your Postgres server and set the active database to CAP2.  Then . . .

-- Customers --
DROP TABLE IF EXISTS customers;
CREATE TABLE customers (
  cid      char(4) not null,
  name     varchar(16),
  city     varchar(20),
  discount decimal(5,2),
primary key(cid)
);


-- Agents --
DROP TABLE IF EXISTS Agents;
CREATE TABLE agents (
  aid     char(3) not null,
  name    varchar(16),
  city    varchar(20),
  percent real,
 primary key(aid)
);        


-- Products --
DROP TABLE IF EXISTS products;
CREATE TABLE products (
  pid      char(3) not null,
  name     varchar(16),
  city     varchar(20),
  quantity integer,
  priceUSD numeric(10,2),
 primary key(pid)
);


-- Orders -- 
DROP TABLE IF EXISTS orders;
CREATE TABLE orders (
  ordno   integer not null,
  mon     char(3),    
  cid     char(4) not null references customers(cid),
  aid     char(3) not null references agents(aid),
  pid     char(3) not null references products(pid),
  qty     integer,
  dollars numeric(12,2),
 primary key(ordno)
);

-- End of Postgres-Create.SQL