DROP TABLE Customer;
DROP TABLE Delivery;
DROP TABLE Order_Item;
DROP TABLE Product;
DROP TABLE Region;
DROP TABLE Sales_Order;

CREATE TABLE Customer (
  Customer_ID 	VARCHAR(20) NOT NULL,
  Customer_Name VARCHAR(20),
  Customer_Address VARCHAR(30),
  Contact_No 	VARCHAR(20),
  Fax_No	VARCHAR(20),
  primary key (),
  foreign key() references ()
);

INSERT INTO Customer VALUES ('AAD1101427', 'Alice', '111, Tmn Emas, Perak', '012-1234567','555-555-5555');
INSERT INTO Customer VALUES ('AAD1101428', 'Janice', '222, Tmn Perak, Selangor', '013-1234567','666-666-6666');
INSERT INTO Customer VALUES ('AAD1101429', 'Jessica', '333, Tmn Gangsa, Perak', '016-1234567','777-777-7777');

CREATE TABLE Delivery (
  Delivery_Item_ID 	VARCHAR(15) NOT NULL,
  Sales_Order_ID 	VARCHAR(15),
  Delivery_Description 	VARCHAR(15),
  Delivery_Date 	DATE,
  primary key (),
  foreign key() references ()
);

INSERT INTO Delivery VALUES ('DO1010', 'SO1001', 'Arrived', '1/1/2012');
INSERT INTO Delivery VALUES ('DO2020', 'SO2002', 'Processing', '2/2/2012');
INSERT INTO Delivery VALUES ('DO3030', 'SO3003', 'Arrived', '3/3/2012');

CREATE TABLE Order_Item (
  Order_Item_ID  	  VARCHAR(15) NOT NULL,
  Order_Item_Date 	  DATE,
  Sales_Order_ID 	  VARCHAR(15),
  Product_ID		  VARCHAR(5),
  Order_Item_Description  VARCHAR(15),
  primary key (),
  foreign key() references ()
);

INSERT INTO Order_Item VALUES ('OI0110', '1/1/2012', 'SO1001', 'AA1100','Confirmed');
INSERT INTO Order_Item VALUES ('OI0220', '2/2/2012', 'SO2002', 'AA2200','Confirmed');
INSERT INTO Order_Item VALUES ('OI0330', '3/3/2012', 'SO3003', 'AA3300','Confirmed');

CREATE TABLE Product (
  Product_ID  	 	 VARCHAR(15) NOT NULL,
  Product_Name 	  	 VARCHAR(15),
  Order_Item_ID 	 VARCHAR(15),
  Delivery_Order_ID	 VARCHAR(5),
  Product_Description    VARCHAR(15),
  primary key (),
  foreign key() references ()
);

INSERT INTO Product VALUES ('AA1100', 'OBook 2', 'OI0110', 'DO1010','Completed');
INSERT INTO Product VALUES ('AA2200', 'OBook Air', 'OI0220', 'DO2020','Completed');
INSERT INTO Product VALUES ('AA3300', 'OBook Pro', 'OI0330', 'DO3030','Completed');

CREATE TABLE Region (
  State_Name 	VARCHAR(15) NOT NULL,
  Country_Name 	VARCHAR(15),
  State_Code 	VARCHAR(5),
  Country_Code 	VARCHAR(5),
  primary key (),
  foreign key() references ()
);

INSERT INTO Region VALUES ('Johor', 'Malaysia', '01', '60');
INSERT INTO Region VALUES ('Kedah', 'Malaysia', '02', '60');
INSERT INTO Region VALUES ('Kelantan', 'Malaysia', '03', '60');
INSERT INTO Region VALUES ('Melaka', 'Malaysia', '04', '60');
INSERT INTO Region VALUES ('Negeri Sembilan', 'Malaysia', '05', '60');
INSERT INTO Region VALUES ('Pahang', 'Malaysia', '06', '60');

CREATE TABLE Sales_Order (
  Sales_Order_ID  	  VARCHAR(15) NOT NULL,
  Sales_Order_Date 	  DATE,
  Customer_ID 		  VARCHAR(15),
  Order_Amount		  INTEGER,
  Sales_Order_Description VARCHAR(15),
  primary key (),
  foreign key() references ()
);

INSERT INTO Sales_Order VALUES ('SO1001', '1/1/2012', 'AAD 1101427', '5','Confirmed');
INSERT INTO Sales_Order VALUES ('SO2002', '2/2/2012', 'AAD 1101428', '10','Confirmed');
INSERT INTO Sales_Order VALUES ('SO3003', '3/3/2012', 'AAD 1101429', '15','Confirmed');