
-------------------------
-- Create Customers table
-------------------------

CREATE TABLE Customers
(
  cust_id      varchar(10)  NOT NULL,
  cust_name    varchar(50)  NOT NULL,
  cust_address varchar(50),
  cust_city    varchar(50),
  cust_state   varchar(5),
  cust_zip     varchar(10),
  cust_country varchar(50),
  cust_contact varchar(50),
  cust_email   varchar(255) 
);

--------------------------
-- Create OrderItems table
--------------------------
CREATE TABLE OrderItems
(
  order_num  int             NOT NULL,
  order_item int             NOT NULL,
  prod_id    varchar(10)     NOT NULL,
  quantity   int             NOT NULL,
  item_price decimal(8,2)    NOT NULL 
);


----------------------
-- Create Orders table
----------------------
CREATE TABLE Orders
(
  order_num  int      NOT NULL,
  order_date date     NOT NULL,
  cust_id    varchar(10) NOT NULL 
);

------------------------
-- Create Products table
------------------------
CREATE TABLE Products
(
  prod_id    varchar(10)      NOT NULL,
  vend_id    varchar(10)      NOT NULL,
  prod_name  varchar(255)     NOT NULL,
  prod_price decimal(8,2)     NOT NULL,
  prod_desc  varchar(1000)    NULL   -- the default; not needed
);

-----------------------
-- Create Vendors table
-----------------------
CREATE TABLE Vendors
(
  vend_id      varchar(10) NOT NULL,
  vend_name    varchar(50) NOT NULL,
  vend_address varchar(50),
  vend_city    varchar(50),
  vend_state   varchar(5),
  vend_zip     varchar(10),
  vend_country varchar(50)
);


----------------------
-- Define primary keys
----------------------
ALTER TABLE Customers ADD PRIMARY KEY (cust_id);
ALTER TABLE OrderItems ADD PRIMARY KEY (order_num, order_item);
ALTER TABLE Orders ADD PRIMARY KEY (order_num);
ALTER TABLE Products ADD PRIMARY KEY (prod_id);
ALTER TABLE Vendors ADD PRIMARY KEY (vend_id);


----------------------
-- Define foreign keys
----------------------
ALTER TABLE OrderItems ADD CONSTRAINT FK_OrderItems_Orders FOREIGN KEY (order_num) REFERENCES Orders (order_num);
ALTER TABLE OrderItems ADD CONSTRAINT FK_OrderItems_Products FOREIGN KEY (prod_id) REFERENCES Products (prod_id);
ALTER TABLE Orders ADD CONSTRAINT FK_Orders_Customers FOREIGN KEY (cust_id) REFERENCES Customers (cust_id);
ALTER TABLE Products ADD CONSTRAINT FK_Products_Vendors FOREIGN KEY (vend_id) REFERENCES Vendors (vend_id);
