/********************************************************************************/
/*																				                                      */
/*	Kroenke, Auer, Vandenberg, and Yoder 					*/
/*	Database Concepts (8th Edition) Chapter 03 	       		*/
/*																				                                      */
/*	The Queen Anne Curiousity Shop Database Create Tables		           				  */
/*																				                                      */
/*	These are the Oracle Express 11GR2 SQL code solutions                        					*/
/*																				                                      */
/********************************************************************************/
/*																				                                      */
/*	NOTE:  These SQL Statements use the following surrogate keys:				        */
/*																				                                      */
/*		CUSTOMER.CustomerID:	Start at 1, Increment by 1 - seqCID				            */
/*		EMPLOYEE.EmployeeID:	Start at 1, Increment by 1 - seqEID				              */
/*		VENDOR.VendorID:		Start at 1, Increment by 1 - seqVID				              */
/*		ITEM.ItemID:			Start at 1, Increment by 1 - seqIID	    		              */
/*		SALE.SaleID:			Start at 1, Increment by 1 - seqSID				              */
/*																				                                      */
/********************************************************************************/

CREATE  TABLE CUSTOMER(
	CustomerID			        Int				        NOT NULL,
	LastName			          Char(25)		      NOT NULL,
	FirstName			          Char(25)		      NOT NULL,
	Address				          Char(35)		      NULL,
	City				            Char(35)		      NULL,
	State				            Char(2)			      NULL,
	ZIP					            Char(10)		      NULL,
	Phone				            Char(12)		      NOT NULL,
	EmailAddress				            VarChar(100)	    NULL,
	CONSTRAINT 			        CUSTOMER_PK 	    PRIMARY KEY(CustomerID)
	);

CREATE SEQUENCE seqCID INCREMENT BY 1 START WITH 1; 

CREATE  TABLE EMPLOYEE(
	EmployeeID			        Int 			        NOT NULL,
	LastName			          Char(25) 		      NOT NULL,
	FirstName			          Char(25) 		      NOT NULL,
	Phone				            Char(12)		      NULL,
	EmailAddress 				          VarChar(100)	    NOT NULL UNIQUE,
	CONSTRAINT 			        EMPLOYEE_PK 	    PRIMARY KEY(EmployeeID)
	);

CREATE SEQUENCE seqEID INCREMENT BY 1 START WITH 1; 

CREATE  TABLE VENDOR(
	VendorID			          Int				        NOT NULL,
	CompanyName			        Char(100)		      NULL,
	ContactLastName		      Char(25)		      NOT NULL,
	ContactFirstName	      Char(25)		      NOT NULL,
	Address				          Char(35)		      NULL,
	City				            Char(35)		      NULL,
	State				            Char(2)			      NULL,
	ZIP					            Char(10)		      NULL,
	Phone				            Char(12)		      NOT NULL,
	Fax					            Char(12)		      NULL,
	EmailAddress				            VarChar(100)	    NULL,
	CONSTRAINT 			    VENDOR_PK 		        PRIMARY KEY(VendorID)
	);

CREATE SEQUENCE seqVID INCREMENT BY 1 START WITH 1; 

CREATE  TABLE ITEM(
	ItemID				          Int	 			        NOT NULL,
	ItemDescription		      VarChar(255)	    NOT NULL,
	PurchaseDate		        Date		      NOT NULL,
	ItemCost			          Number(9,2)	    NOT NULL,
	ItemPrice			          Number(9,2)	    NOT NULL,
	VendorID			          Int				        NOT NULL,
	CONSTRAINT 			    ITEM_PK			          PRIMARY KEY (ItemID),
	CONSTRAINT 			    ITEM_VENDOR_FK        FOREIGN KEY	(VendorID)
								          REFERENCES VENDOR(VendorID)
  );

CREATE SEQUENCE seqIID INCREMENT BY 1 START WITH 1;   

CREATE  TABLE SALE(
  SaleID				          Int				       NOT NULL,
	CustomerID			        Int	 			       NOT NULL,
	EmployeeID			        Int				       NOT NULL,
	SaleDate			          Date		     NOT NULL,
	SubTotal			          Number(15,2)	   NULL,
	Tax					            Number(15,2)	   NULL,
	Total				            Number(15,2)	   NULL,
  CONSTRAINT 			    SALE_PK 		         PRIMARY KEY (SaleID),
	CONSTRAINT 			    SALE_CUSTOMER_FK     FOREIGN KEY (CustomerID)
								          REFERENCES Customer(CustomerID),
	CONSTRAINT 	        SALE_EMPLOYEE_FK     FOREIGN KEY(EmployeeID)
								          REFERENCES Employee(EmployeeID)
  );

CREATE SEQUENCE seqSID INCREMENT BY 1 START WITH 1; 

CREATE  TABLE SALE_ITEM(
  SaleID				          Int				       NOT NULL,
	SaleItemID			        Int				       NOT NULL,
	ItemID				          Int	 			       NOT NULL,
	ItemPrice			          Number(9,2)	   NOT NULL,
  CONSTRAINT 			    SALE_ITEM_PK 	   PRIMARY KEY (SaleID, SaleItemID),
	CONSTRAINT 			    SALE_ITEM_SALE_FK FOREIGN KEY (SaleID)
								          REFERENCES SALE(SaleID)			
										ON DELETE CASCADE,
	CONSTRAINT 			    SALE_ITEM_ITEM_FK FOREIGN KEY (ItemID)
								          REFERENCES ITEM(ItemID)
  );

/********************************************************************************/