/********************************************************************************/
/*																				*/
/*	Kroenke, Auer, Vandenberg, Yoder - DBC (8th Edition) Chapter 03				*/
/*																				*/
/*	Wedgewood Pacific [WP] Create New Tables In Exercises						*/
/*																				*/
/*	These are the MySQL 5.7						SQL code solutions				*/
/*																				*/
/********************************************************************************/

/* note that MySQL parses but does not actually enforce CHECK constraints */
CREATE  TABLE COMPUTER(	
	SerialNumber		Int					NOT NULL,
	Make				Char(12)			NOT NULL,
	Model				Char(24)			NOT NULL,
	ProcessorType		Char(24)			NULL,
	ProcessorSpeed		Numeric(3,2)		NOT NULL,
	MainMemory			Char(15)			NOT NULL,
	DiskSize			Char(15)			NOT NULL,
	CONSTRAINT 		COMPUTER_PK 			PRIMARY KEY(SerialNumber),
	CONSTRAINT 		MAKE_CHECK				CHECK
								(Make IN ('Dell', 'Gateway', 'HP', 'Other')),
	CONSTRAINT 		SPEED_CHECK				CHECK
								(ProcessorSpeed BETWEEN 1.0 AND 4.0)
	);

CREATE  TABLE COMPUTER_ASSIGNMENT(
	SerialNumber		Int					NOT NULL,
	EmployeeNumber		Int	 				NOT NULL,
	DateAssigned		Date				NOT NULL,
	DateReassigned		Date				NULL,
	CONSTRAINT 		COMP_ASSIGN_PK 			PRIMARY KEY(SerialNumber, EmployeeNumber,
										DateAssigned),
	CONSTRAINT 		COMP_ASSIGN_EMPLOYEE	FOREIGN KEY(EmployeeNumber)
						REFERENCES EMPLOYEE(EmployeeNumber)
							ON UPDATE NO ACTION
							ON DELETE NO ACTION,
	CONSTRAINT 		COMP_ASSIGN_COMPUTER	FOREIGN KEY(SerialNumber)
						REFERENCES COMPUTER(SerialNumber)
							ON UPDATE NO ACTION
							ON DELETE CASCADE
	);



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

