CREATE TABLE Class(
Crn INTEGER,
Class_Name CHAR (7),
Professor CHAR (15),
Class_Size INTEGER,
Enrollment INTEGER,
Credits INTEGER,
Day1 CHAR (10),
Time_Start1 CHAR (7),
Time_End1 CHAR (7),
Day2 CHAR (10),
Time_Start2 CHAR (8),
Time_End2 CHAR (8),
PRIMARY KEY (Crn));

//test case
INSERT INTO Class VALUES (49094, 'cs389', 'scharff', 20, 1, 3, 'M', '10:30', '11:25', 'W', '10:30', '11:25');

CREATE TABLE Student(
Stud_Id INTEGER,
Last_Name CHAR(15),
First_Name CHAR(15),
password CHAR(10),
email CHAR(30),
Status CHAR(2),
PRIMARY KEY (Stud_Id)
);

//test case
INSERT INTO Student VALUES (111111111, 'Charles', 'Deguen', 'hello', 'charles@pace.edu', 'so')
;

CREATE TABLE Registration(
Stud_Id INTEGER,
Crn INTEGER,
PRIMARY KEY (Stud_Id,Crn)
);

//test case
INSERT INTO Registration VALUES (111111111,49094);

//test cases as originally written by Scharff for refrence purpose in case I'm wrong (use "corrected" code below)
SELECT Enrollment
FROM Registration
WHERE Crn=49094;

UPDATE Registration
SET Enrollment = 4
WHERE Crn = 49094;

SELECT Class_name
FROM Class, Registration
WHERE Class.Crn = Registration.Crn
AND Registration.Stud_Id=111111111;

//"corrected" code for test cases
//updates the Enrollment number in Class table
SELECT Crn
FROM Registration
WHERE Crn=49094;

//actually the handling of Enrollment++ has to be done in Java
UPDATE Class
SET Enrollment = 4
WHERE Crn = 49094;

//shows the name of the classthe student registered
SELECT Class_name
FROM Class, Registration
WHERE Class.Crn = Registration.Crn
AND Registration.Stud_Id=111111111;