/********************************************************************************/
/*																				                                      */
/*	Kroenke, Auer, Vandenberg, and Yoder 					*/
/*	Database Concepts (8th Edition) Chapter 03     		*/
/*							                                      */
/*	Art-Course-Database Create Tables					                      */
/*																				                                      */
/*	These are the Oracle Express 11GR2 SQL code solutions	                 				*/
/*																				                                      */
/*												                                                      */
/********************************************************************************/

CREATE  TABLE CUSTOMER(
	CustomerNumber	    Int			       NOT NULL,
	CustomerLastName	  Char(25)		   NOT NULL,
	CustomerFirstName	  Char(25)	     NOT NULL,
	Phone			          Char(12)		   NOT NULL,
	CONSTRAINT 		      CUSTOMER_PK 	 PRIMARY KEY(CustomerNumber)
	);
    
CREATE SEQUENCE seqCustomerID INCREMENT BY 1 START WITH 1;

CREATE  TABLE COURSE(
	CourseNumber	      Int				     NOT NULL,
	Course			        Char(50) 		   NOT NULL,
	CourseDate          Date 		   NOT NULL,
	Fee				          Number(8,2)	 NOT NULL,
	CONSTRAINT 		      COURSE_PK 		 PRIMARY KEY(CourseNumber)
	);
    
CREATE SEQUENCE seqCourseID INCREMENT BY 1 START WITH 1;

CREATE  TABLE ENROLLMENT(
	CustomerNumber	    Int				     NOT NULL,
	CourseNumber	      Int				     NOT NULL,
	AmountPaid		      Number(8,2)	 NULL,
  CONSTRAINT 		      ENROLLMENT_PK  PRIMARY KEY(CustomerNumber, CourseNumber),
  CONSTRAINT 		      ENROLL_CUST_FK FOREIGN KEY (CustomerNumber)
	            					    REFERENCES CUSTOMER(CustomerNumber),
  CONSTRAINT 		      ENROLL_COURSE_FK  FOREIGN KEY (CourseNumber)
                            REFERENCES COURSE(CourseNumber)
						 ON DELETE CASCADE
  );
  
/********************************************************************************/