/********************************************************************************/
/*																				*/
/*	Kroenke, Auer, Vandenberg, and Yoder 					*/
/*	Database Concepts (8th Edition) Chapter 03 	       		*/
/*																				*/
/*	Art-Course-Database Create Tables											*/
/*																				*/
/*	These are the Microsoft SQL Server 2014/2016 SQL code solutions				*/
/*																				*/
/********************************************************************************/

USE Art_Course_Database
GO

CREATE  TABLE CUSTOMER(
	CustomerNumber		Int				NOT NULL IDENTITY (1, 1),
	CustomerLastName	Char(25)		NOT NULL,
	CustomerFirstName	Char(25)		NOT NULL,
	Phone				Char(12)		NOT NULL,
	CONSTRAINT 			CUSTOMER_PK 	PRIMARY KEY(CustomerNumber)
	);

CREATE  TABLE COURSE(
	CourseNumber		Int				NOT NULL IDENTITY (1, 1),
	Course				Char(50) 		NOT NULL,
	CourseDate			Date	 		NOT NULL,
	Fee					Numeric(8,2)	NOT NULL,
	CONSTRAINT 			COURSE_PK 		PRIMARY KEY(CourseNumber)
	);

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

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