The trionlink-database from 3mileswind

公用的url

ReadMe

localHost: 5432
dataBase: postgres
user: postgres
password: 4645

String url = "jdbc:postgresql://localhost:5432/postgres?user=postgres&password=4645";

// undergrauate
insert into student values('A1001','1','Benjamin','','B','California resident','Current enrolled','Undergraduate');
insert into Undergraduate values('A1001','Computer Science','','Warren');

insert into student values('A1002','2','Kristen','','W','California resident','Current enrolled','Undergraduate');
insert into Undergraduate values('A1002','Computer Science','','Warren');

insert into student values('A1003','3','Daniel','','F','California resident','Current enrolled','Undergraduate');
insert into Undergraduate values('A1003','Computer Science','','Warren');

insert into student values('A1004','4','Claire','','J','California resident','Current enrolled','Undergraduate');
insert into Undergraduate values('A1004','Computer Science','','Warren');

insert into student values('A1005','5','Julie','','C','California resident','Current enrolled','Undergraduate');
insert into Undergraduate values('A1005','Computer Science','','Warren');

insert into student values('A1006','6','Kevin','','L','California resident','Current enrolled','Undergraduate');
insert into Undergraduate values('A1006','Mechanical Engineering','','Warren');

insert into student values('A1007','7','Michael','','B','California resident','Current enrolled','Undergraduate');
insert into Undergraduate values('A1007','Mechanical Engineering','','Warren');

insert into student values('A1008','8','Joseph','','J','California resident','Current enrolled','Undergraduate');
insert into Undergraduate values('A1008','Mechanical Engineering','','Warren');

insert into student values('A1009','9','Devin','','P','California resident','Current enrolled','Undergraduate');
insert into Undergraduate values('A1009','Mechanical Engineering','','Warren');

insert into student values('A1010','10','Logan','','F','California resident','Current enrolled','Undergraduate');
insert into Undergraduate values('A1010','Mechanical Engineering','','Warren');

insert into student values('A1011','11','Vikram','','N','California resident','Current enrolled','Undergraduate');
insert into Undergraduate values('A1011','Philosophy','','Warren');

insert into student values('A1012','12','Rachel','','Z','California resident','Current enrolled','Undergraduate');
insert into Undergraduate values('A1012','Philosophy','','Warren');

insert into student values('A1013','13','Zach','','M','California resident','Current enrolled','Undergraduate');
insert into Undergraduate values('A1013','Philosophy','','Warren');

insert into student values('A1014','14','Justin','','H','California resident','Current enrolled','Undergraduate');
insert into Undergraduate values('A1014','Philosophy','','Warren');

insert into student values('A1015','15','Rahul','','R','California resident','Current enrolled','Undergraduate');
insert into Undergraduate values('A1015','Philosophy','','Warren');

// master
insert into student values('A1016','16','Dave','','C','Non-CA US','Current enrolled','Master');
insert into master values('A1016','Master','CSE',0);

insert into student values('A1017','17','Nelson','','H','Non-CA US','Current enrolled','Master');
insert into master values('A1017','Master','CSE',0);

insert into student values('A1018','18','Andrew','','P','Non-CA US','Current enrolled','Master');
insert into master values('A1018','Master','CSE',0);

insert into student values('A1019','19','Nathan','','S','Non-CA US','Current enrolled','Master');
insert into master values('A1019','Master','CSE',0);

insert into student values('A1020','20','John','','H','Non-CA US','Current enrolled','Master');
insert into master values('A1020','Master','CSE',0);

insert into student values('A1021','21','Anwell','','W','Non-CA US','Current enrolled','Master');
insert into master values('A1021','Master','CSE',0);

insert into student values('A1022','22','Tim','','K','Non-CA US','Current enrolled','Master');
insert into master values('A1022','Master','CSE',0);


// faculty
insert into faculty values('Justin Bieber','Associate Professor','CSE');
insert into faculty values('Flo Rida','Professor','CSE');
insert into faculty values('Selena Gomez','Professor','CSE');
insert into faculty values('Adele','Professorr','CSE');
insert into faculty values('Taylor Swift','Professor','CSE');
insert into faculty values('Kelly Clarkson','Professor','CSE');
insert into faculty values('Adam Levine','Professor','CSE');
insert into faculty values('Bjork','Professor','CSE');

//degree

INSERT into degree values('B.S.','Computer Science','CSE',40,10,15,15);
INSERT into degree values('B.A.','Philosophy','CSE',35,15,20,0);
INSERT into degree values('B.S.','Mechanical Engineering','CSE',50,20,20,10);
INSERT into degree values('M.S.','Computer Science','CSE',45,0,0,0);

// concentration
Insert into concentration values('Database','Computer Science','M.S.','CSE232A','CSE250A,CSE221,CSE105,MAE107,MAE3',3,4);
Insert into concentration values('AI','Computer Science','M.S.','CSE255,CSE250A','CSE250A,CSE221,CSE105,MAE107,MAE3',3.1,8);
Insert into concentration values('System','Computer Science','M.S.','CSE221','CSE250A,CSE221,CSE105,MAE107,MAE3',3.3,4);

//past_couse
insert into past_course values('A1001','CSE8A',4,'A-','Winter','2017','CSE8A-wi17-1','Letter Grade');
insert into past_course values('A1003','CSE8A',4,'B+','Winter','2017','CSE8A-wi17-1','Letter Grade');
insert into past_course values('A1002','CSE8A',4,'C-','Fall','2017','CSE8A-fa17-1','Letter Grade');
insert into past_course values('A1004','CSE8A',4,'A-','Winter','2018','CSE8A-wi18-1','Letter Grade');
insert into past_course values('A1005','CSE8A',4,'B','Winter','2018','CSE8A-wi18-1','Letter Grade');

insert into past_course values('A1001','CSE105',4,'A-','Spring','2017','CSE105-sp17-1','Letter Grade');
insert into past_course values('A1005','CSE105',4,'B+','Spring','2017','CSE105-sp17-1','Letter Grade');
insert into past_course values('A1004','CSE105',4,'C','Spring','2017','CSE105-sp17-1','Letter Grade');

insert into past_course values('A1016','CSE250A',4,'C','Winter','2017','CSE250A-wi17-1','Letter Grade');
insert into past_course values('A1022','CSE250A',4,'B+','Winter','2018','CSE250A-wi18-1','Letter Grade');
insert into past_course values('A1018','CSE250A',4,'D','Winter','2018','CSE250A-wi18-1','Letter Grade');
insert into past_course values('A1019','CSE250A',4,'F','Winter','2018','CSE250A-wi18-1','Letter Grade');

insert into past_course values('A1017','CSE250B',4,'A','Spring','2017','CSE250B-sp17-1','Letter Grade');
insert into past_course values('A1019','CSE250B',4,'A','Spring','2017','CSE250B-sp17-1','Letter Grade');

insert into past_course values('A1020','CSE255',4,'B-','Winter','2018','CSE255-wi18-1','Letter Grade');
insert into past_course values('A1018','CSE255',4,'B','Winter','2018','CSE255-wi18-1','Letter Grade');
insert into past_course values('A1021','CSE255',4,'F','Winter','2018','CSE255-wi18-1','Letter Grade');

insert into past_course values('A1017','CSE232A',4,'A-','Winter','2018','CSE232A-wi18-1','Letter Grade');

insert into past_course values('A1022','CSE221',4,'A','Fall','2017','CSE221-fa17-1','Letter Grade');
insert into past_course values('A1020','CSE221',4,'A','Fall','2017','CSE221-fa17-1','Letter Grade');

insert into past_course values('A1010','MAE107',4,'B+','Fall','2017','MAE107-fa17-1','Letter Grade');

insert into past_course values('A1008','MAE108',2,'B-','Winter','2017','MAE108-wi17-1','Letter Grade');
insert into past_course values('A1007','MAE108',2,'A-','Winter','2017','MAE108-wi17-1','Letter Grade');
insert into past_course values('A1006','MAE108',2,'B','Spring','2017','MAE108-sp17-1','Letter Grade');
insert into past_course values('A1010','MAE108',2,'B+','Spring','2017','MAE108-sp17-1','Letter Grade');

insert into past_course values('A1011','PHIL10',4,'A','Winter','2018','PHIL10-wi18-1','Letter Grade');
insert into past_course values('A1012','PHIL10',4,'A','Winter','2018','PHIL10-wi18-1','Letter Grade');
insert into past_course values('A1013','PHIL10',4,'C-','Winter','2018','PHIL10-wi18-1','Letter Grade');
insert into past_course values('A1014','PHIL10',4,'C+','Winter','2018','PHIL10-wi18-1','Letter Grade');

insert into past_course values('A1015','PHIL165',2,'F','Fall','2017','PHIL165-fa17-1','Letter Grade');
insert into past_course values('A1012','PHIL165',2,'D','Fall','2017','PHIL165-fa17-1','Letter Grade');
insert into past_course values('A1011','PHIL165',2,'A-','Winter','2018','PHIL165-wi18-1','Letter Grade');

// course
insert into Course values ('CSE8A', '.', 'CSE', '.', 'Letter or S/U', 'No', '4', '5'),
('CSE105', '.', 'CSE', '.', 'Letter or S/U', 'No', '4', '5'),
('CSE123', '.', 'CSE', '.', 'Letter or S/U', 'No', '4', '5'),
('CSE250A', '.', 'CSE', '.', 'Letter or S/U', 'No', '4', '5'),
('CSE250B', '.', 'CSE', '.', 'Letter or S/U', 'No', '4', '5'),
('CSE255', '.', 'CSE', '.', 'Letter or S/U', 'No', '4', '4'),
('CSE232A', '.', 'CSE', '.', 'Letter or S/U', 'No', '4', '4'),
('CSE221', '.', 'CSE', '.', 'Letter or S/U', 'No', '4', '4'),
('MAE3', '.', 'CSE', '.', 'Letter or S/U', 'No', '4', '5'),
('MAE107', '.', 'CSE', '.', 'Letter or S/U', 'No', '4', '5'),
('MAE108', '.', 'CSE', '.', 'Letter or S/U', 'No', '4', '5'),
('PHIL10', '.', 'CSE', '.', 'Letter or S/U', 'No', '2', '2'),
('PHIL12', '.', 'CSE', '.', 'Letter or S/U', 'No', '4', '5'),
('PHIL165', '.', 'CSE', '.', 'Letter or S/U', 'No', '2', '5'),
('PHIL167', '.', 'CSE', '.', 'Letter or S/U', 'No', '2', '5');

// class
insert into Class values ('CSE8A-wi17', 'CSE8A', 'Introduction to Computer Science: Java', 'Winter', '2017', '2', 'Spring,2022'),
('CSE8A-fa17', 'CSE8A', 'Introduction to Computer Science: Java', 'Fall', '2017', '2', 'Spring,2022'),
('CSE8A-wi18', 'CSE8A', 'Introduction to Computer Science: Java', 'Winter', '2018', '2', 'Spring,2022'),
('CSE105-sp17', 'CSE105', 'Intro to Theory', 'Spring', '2017', '2', 'Fall,2021'),
('CSE250A-wi17', 'CSE250A', 'Probabilistic Reasoning', 'Winter', '2017', '2', 'Winter,2022'),
('CSE250A-wi18', 'CSE250A', 'Probabilistic Reasoning', 'Winter', '2018', '2', 'Winter,2022'),
('CSE250B-sp17', 'CSE250B', 'Machine Learning', 'Spring', '2017', '2', 'Fall,2022'),
('CSE255-wi18', 'CSE255', 'Data Mining and Predictive Analytics', 'Winter', '2018', '2', 'Winter,2022'),
('CSE232A-wi18', 'CSE232A', 'Databases', 'Winter', '2018', '2', 'Spring,2022'),
('CSE221-fa17', 'CSE221', 'Operating Systems', 'Fall', '2017', '2', 'Fall,2021'),
('MAE107-fa17', 'MAE107', 'Computational Methods', 'Fall', '2017', '2', 'Spring,2022'),
('MAE108-wi17', 'MAE108', 'Probability and Statistics', 'Winter', '2017', '2', 'Fall,2021'),
('MAE108-sp17', 'MAE108', 'Probability and Statistics', 'Spring', '2017', '2', 'Fall,2021'),
('PHIL10-wi18', 'PHIL10', 'Intro to Logic', 'Winter', '2018', '2', 'Winter,2022'),
('PHIL165-fa17', 'PHIL165', 'Freedom, Equality, and the Law', 'Fall', '2017', '2', 'Spring,2022'),
('MAE108-sp21', 'MAE108', 'Probability and Statistics', 'Spring', '2021', '2', 'Fall,2021'),
('CSE255-sp21', 'CSE255', 'Data Mining and Predictive Analytics', 'Spring', '2021', '2', 'Winter,2022'),
('CSE8A-sp21', 'CSE8A', 'Introduction to Computer Science: Java', 'Spring', '2021', '2', 'Spring,2022'),
('CSE105-sp21', 'CSE105', 'Intro to Theory', 'Spring', '2021', '2', 'Fall,2021'),
('CSE221-sp21', 'CSE221', 'Operating Systems', 'Spring', '2021', '2', 'Fall,2021'),
('PHIL12-sp21', 'PHIL12', 'Scientific Reasoning', 'Spring', '2021', '2', 'Spring,2022'),
('PHIL165-sp21', 'PHIL165', 'Freedom, Equality, and the Law', 'Spring', '2021', '2', 'Spring,2022'),
('PHIL165-wi18', 'PHIL165', 'Freedom, Equality, and the Law', 'Winter', '2018', '2', 'Spring,2022');

// section
insert into section values ('CSE8A', 'CSE8A-wi17', 'CSE8A-wi17-1', 'Justin Bieber', '30', '5'),
('PHIL165', 'PHIL165-fa17', 'PHIL165-fa17-1', 'Flo Rida', '30', '5'),
('CSE8A', 'CSE8A-fa17', 'CSE8A-fa17-1', 'Selena Gomez', '30', '5'),
('MAE108', 'MAE108-sp21', 'MAE108-sp21-1', 'Adele', '30', '5'),
('CSE105', 'CSE105-sp17', 'CSE105-sp17-1', 'Taylor Swift', '30', '5'),
('CSE8A', 'CSE8A-wi18', 'CSE8A-wi18-1', 'Kelly Clarkson', '30', '5'),
('CSE250A', 'CSE250A-wi17', 'CSE250A-wi17-1', 'Bjork', '30', '5'),
('PHIL10', 'PHIL10-wi18', 'PHIL10-wi18-1', 'Bjork', '30', '5'),
('CSE250B', 'CSE250B-sp17', 'CSE250B-sp17-1', 'Justin Bieber', '30', '5'),
('CSE255', 'CSE255-sp21', 'CSE255-sp21-1', 'Flo Rida', '30', '5'),
('MAE108', 'MAE108-sp21', 'MAE108-sp21-2', 'Selena Gomez', '30', '5'),
('CSE8A', 'CSE8A-sp21', 'CSE8A-sp21-1', 'Adele', '30', '5'),
('CSE105', 'CSE105-sp21', 'CSE105-sp21-1', 'Taylor Swift', '30', '5'),
('CSE232A', 'CSE232A-wi18', 'CSE232A-wi18-1', 'Kelly Clarkson', '30', '5'),
('PHIL165', 'PHIL165-wi18', 'PHIL165-wi18-1', 'Adam Levine', '30', '5'),
('MAE107', 'MAE107-fa17', 'MAE107-fa17-1', 'Bjork', '30', '5'),
('CSE221', 'CSE221-sp21', 'CSE221-sp21-1', 'Justin Bieber', '30', '5'),
('MAE108', 'MAE108-wi17', 'MAE108-wi17-1', 'Selena Gomez', '30', '5'),
('PHIL12', 'PHIL12-sp21', 'PHIL12-sp21-1', 'Adam Levine', '30', '5'),
('CSE250A', 'CSE250A-wi18', 'CSE250A-wi18-1', 'Bjork', '30', '5'),
('PHIL165', 'PHIL165-sp21', 'PHIL165-sp21-1', 'Adam Levine', '30', '5'),
('CSE221', 'CSE221-sp21', 'CSE221-sp21-2', 'Kelly Clarkson', '30', '5'),
('CSE221', 'CSE221-sp21', 'CSE221-sp21-3', 'Kelly Clarkson', '30', '5'),
('CSE255', 'CSE255-wi18', 'CSE255-wi18-1', 'Justin Bieber', '30', '5'),
('CSE221', 'CSE221-fa17', 'CSE221-fa17-1', 'Justin Bieber', '30', '5'),
('MAE108', 'MAE108-sp17', 'MAE108-sp17-1', 'Selena Gomez', '30', '5');


// enrollment
insert into enrollment values ('A1016', 'CSE221', 'CSE221-sp21-2', '4', 'Letter Grade'),
('A1017', 'CSE221', 'CSE221-sp21-1', '4', 'S/U'),
('A1018', 'CSE221', 'CSE221-sp21-3', '4', 'Letter Grade'),
('A1019', 'CSE221', 'CSE221-sp21-2', '4', 'Letter Grade'),
('A1020', 'CSE221', 'CSE221-sp21-1', '4', 'Letter Grade'),
('A1021', 'CSE221', 'CSE221-sp21-3', '4', 'S/U'),
('A1022', 'CSE255', 'CSE255-sp21-1', '4', 'Letter Grade'),
('A1016', 'CSE255', 'CSE255-sp21-1', '4', 'Letter Grade'),
('A1017', 'CSE255', 'CSE255-sp21-1', '4', 'Letter Grade'),
('A1001', 'CSE8A', 'CSE8A-sp21-1', '4', 'S/U'),
('A1005', 'CSE8A', 'CSE8A-sp21-1', '4', 'Letter Grade'),
('A1003', 'CSE8A', 'CSE8A-sp21-1', '4', 'Letter Grade'),
('A1007', 'MAE108', 'MAE108-sp21-1', '4', 'Letter Grade'),
('A1008', 'MAE108', 'MAE108-sp21-1', '4', 'Letter Grade'),
('A1009', 'MAE108', 'MAE108-sp21-2', '4', 'Letter Grade'),
('A1004', 'CSE105', 'CSE105-sp21-1', '4', 'Letter Grade'),
('A1012', 'PHIL12', 'PHIL12-sp21-1', '4', 'Letter Grade'),
('A1013', 'PHIL165', 'PHIL165-sp21-1', '4', 'S/U'),
('A1014', 'PHIL12', 'PHIL12-sp21-1', '4', 'Letter Grade'),
('A1015', 'PHIL165', 'PHIL165-sp21-1', '4', 'Letter Grade');

// meeting
insert into meeting values ('MAE108', 'MAE108-sp21-1', 'MAE108-sp21-1-le', 'Yes', 'LE', 'Monday,Wednesday,Friday', '10:00', '11:00', 'R203'),
('MAE108', 'MAE108-sp21-1', 'MAE108-sp21-1-di', 'Yes', 'DI', 'Tuesday,Thursday', '10:00', '11:00', 'R203'),
('MAE108', 'MAE108-sp21-1', 'MAE108-sp21-1-la', 'Yes', 'LA', 'Friday', '18:00', '19:00', 'R203'),
('CSE221', 'CSE221-sp21-2', 'CSE221-sp21-2-le', 'Yes', 'LE', 'Monday,Wednesday,Friday', '10:00', '11:00', 'R203'),
('CSE221', 'CSE221-sp21-2', 'CSE221-sp21-2-di', 'Yes', 'DI', 'Tuesday,Thursday', '11:00', '12:00', 'R203'),
('CSE255', 'CSE255-sp21-1', 'CSE255-sp21-1-le', 'Yes', 'LE', 'Monday,Wednesday,Friday', '12:00', '13:00', 'R203'),
('PHIL12', 'PHIL12-sp21-1', 'PHIL12-sp21-1-le', 'Yes', 'LE', 'Monday,Wednesday,Friday', '12:00', '13:00', 'R203'),
('PHIL12', 'PHIL12-sp21-1', 'PHIL12-sp21-1-di', 'Yes', 'DI', 'Wednesday,Friday', '13:00', '14:00', 'R203'),
('CSE221', 'CSE221-sp21-3', 'CSE221-sp21-3-le', 'Yes', 'LE', 'Monday,Wednesday,Friday', '12:00', '13:00', 'R203'),
('CSE221', 'CSE221-sp21-3', 'CSE221-sp21-3-di', 'Yes', 'DI', 'Tuesday,Thursday', '12:00', '13:00', 'R203'),
('CSE105', 'CSE105-sp21-1', 'CSE105-sp21-1-le', 'Yes', 'LE', 'Tuesday,Thursday', '14:00', '15:00', 'R203'),
('CSE105', 'CSE105-sp21-1', 'CSE105-sp21-1-di', 'Yes', 'DI', 'Friday', '18:00', '19:00', 'R203'),
('PHIL165', 'PHIL165-sp21-1', 'PHIL165-sp21-1-le', 'Yes', 'LE', 'Tuesday,Thursday', '15:00', '16:00', 'R203'),
('PHIL165', 'PHIL165-sp21-1', 'PHIL165-sp21-1-di', 'Yes', 'DI', 'Tuesday', '13:00', '14:00', 'R203'),
('MAE108', 'MAE108-sp21-2', 'MAE108-sp21-2-le', 'Yes', 'LE', 'Tuesday,Thursday', '15:00', '16:00', 'R203'),
('MAE108', 'MAE108-sp21-2', 'MAE108-sp21-2-di', 'Yes', 'DI', 'Monday', '15:00', '16:00', 'R203'),
('MAE108', 'MAE108-sp21-2', 'MAE108-sp21-2-la', 'Yes', 'LA', 'Friday', '17:00', '18:00', 'R203'),
('CSE221', 'CSE221-sp21-1', 'CSE221-sp21-1-le', 'Yes', 'LE', 'Tuesday,Thursday', '17:00', '18:00', 'R203'),
('CSE221', 'CSE221-sp21-1', 'CSE221-sp21-1-di', 'Yes', 'DI', 'Monday,Friday', '09:00', '10:00', 'R203'),
('CSE8A', 'CSE8A-sp21-1', 'CSE8A-sp21-1-le', 'Yes', 'LE', 'Tuesday,Thursday', '17:00', '18:00', 'R203'),
('CSE8A', 'CSE8A-sp21-1', 'CSE8A-sp21-1-di', 'Yes', 'DI', 'Wednesday', '19:00', '20:00', 'R203'),
('CSE8A', 'CSE8A-sp21-1', 'CSE8A-sp21-1-la', 'Yes', 'LA', 'Tuesday,Thursday', '15:00', '16:00', 'R203');

create Table Degree(
 	Degree_Type Text not null,
	Degree_Name text not null,
	Department text not null,
	Total_unit int default 0,
	lowerDivisionUnit int default 0,
	UpperDivisionUnit int default 0,
	ElectiveUnit int default 0,
	primary key(Degree_Name,Degree_Type)
);
create table concentration (
	concen_Name text not null,
	Degree_Name Text not null,
	Degree_Type text not null,
	courses text not null,
	elective text not null,
	minGPA DECIMAL default 2.0,
	minUnit int default 0,
	primary key(concen_Name,degree_name,degree_type),
	Foreign Key (degree_name,degree_type) references degree(degree_name,degree_type) on Delete CASCADE on update CASCADE
)
Create Table past_course (
	Student_ID TEXT not NUll,
	Course_ID TEXT not null,
	Units int default 2,
	Grade TEXT not null,
	Quarter text not null,
	year text not null,
	SectionId text not null,
	grade_opt text not null,
	primary key(Student_ID,Course_ID),
	Foreign Key (Course_ID,SectionId) references Section on Delete CASCADE on update CASCADE,
	Foreign Key (Student_ID) references student(student_id) on Delete CASCADE on update CASCADE,
	Foreign Key (Course_ID) references course(courseid) on Delete CASCADE on update CASCADE
);
create table GRADE_CONVERSION
( LETTER_GRADE CHAR(2) NOT NULL,
NUMBER_GRADE DECIMAL(2,1)
);
insert into grade_conversion values('A+', 4.0);
insert into grade_conversion values('A', 4.0);
insert into grade_conversion values('A-', 3.7);
insert into grade_conversion values('B+', 3.3);
insert into grade_conversion values('B', 3.0);
insert into grade_conversion values('B-', 2.7);
insert into grade_conversion values('C+', 2.3);
insert into grade_conversion values('C', 2.0);
insert into grade_conversion values('C-', 1.7);
insert into grade_conversion values('D', 1.0);
insert into grade_conversion values('F', 0.0);

// electives
insert into electives values ('CSE250A'),
('CSE221'),
('CSE105'),
('MAE107'),
('MAE3');

// reviewsession
insert into reviewsession values ('MAE108', 'MAE108-sp21-1', 'MAE108-sp21-1-final', '06/06', '08:00', '09:00', 'R204'),
('CSE221', 'CSE221-sp21-2', 'CSE221-sp21-2-review', '06/01', '08:00', '09:00', 'R204'),
('CSE221', 'CSE221-sp21-2', 'CSE221-sp21-2-final', '06/08', '08:00', '09:00', 'R204'),
('CSE255', 'CSE255-sp21-1', 'CSE255-sp21-1-final', '06/10', '08:00', '09:00', 'R204'),
('PHIL12', 'PHIL12-sp21-1', 'PHIL12-sp21-1-review', '06/01', '09:00', '10:00', 'R204'),
('PHIL12', 'PHIL12-sp21-1', 'PHIL12-sp21-1-final', '06/08', '08:00', '09:00', 'R204'),
('CSE221', 'CSE221-sp21-3', 'CSE221-sp21-3-review', '06/02', '08:00', '09:00', 'R204'),
('CSE221', 'CSE221-sp21-3', 'CSE221-sp21-3-final', '06/09', '08:00', '09:00', 'R204'),
('CSE105', 'CSE105-sp21-1', 'CSE105-sp21-1-review', '06/02', '13:00', '14:00', 'R204'),
('CSE105', 'CSE105-sp21-1', 'CSE105-sp21-1-final', '06/11', '08:00', '09:00', 'R204'),
('PHIL165', 'PHIL165-sp21-1', 'PHIL165-sp21-1-review-1', '05/01', '08:00', '09:00', 'R204'),
('PHIL165', 'PHIL165-sp21-1', 'PHIL165-sp21-1-review-2', '06/04', '17:00', '18:00', 'R204'),
('PHIL165', 'PHIL165-sp21-1', 'PHIL165-sp21-1-final', '06/12', '08:00', '09:00', 'R204'),
('MAE108', 'MAE108-sp21-2', 'MAE108-sp21-2-final', '06/09', '08:00', '09:00', 'R204'),
('CSE221', 'CSE221-sp21-1', 'CSE221-sp21-1-review', '05/27', '08:00', '09:00', 'R204'),
('CSE221', 'CSE221-sp21-1', 'CSE221-sp21-1-final', '06/10', '08:00', '09:00', 'R204'),
('CSE8A', 'CSE8A-sp21-1', 'CSE8A-sp21-1-review-1', '04/27', '11:00', '12:00', 'R204'),
('CSE8A', 'CSE8A-sp21-1', 'CSE8A-sp21-1-review-2', '06/01', '11:00', '12:00', 'R204'),
('CSE8A', 'CSE8A-sp21-1', 'CSE8A-sp21-1-final', '06/09', '08:00', '09:00', 'R204');

- General
1. attributes(ex. year, units, enrollmentlimit,
waitlist): should check if numeric (but not yet)
2. list some required formats on corresponding pages
3. Each button check duplicate

- Course
1. table should check minunits <= maxunits, 
but I didn't catch error, so insert invalid values
might mess up
2. should check if minunits & maxunits are numeric

- Class
1. current year should only be 2018?
2. attribute (nextOffer) should be if it is a duplicate (quarter,year)
with the same course id

- Meeting
1. time format (hh:mm) check for hh and mm

- Section
1. should check if enrollmentlimit and waitlist are numeric

- Milestone4 
(part 2): should check (just insert / multiple units / multiple sections & units)

-- milestone 4 - trigger 

-- (1) section should not overlaps 
create function check_meeting_function()
	returns trigger
	language plpgsql
as 
$$
declare
	day1 varchar;
	day2 varchar;
	day3 varchar;
	day4 varchar;
	day5 varchar;
begin
	day1 = split_part(new.meet_day, ',', 1);
	day2 = split_part(new.meet_day, ',', 2);
	day3 = split_part(new.meet_day, ',', 3);
	day4 = split_part(new.meet_day, ',', 4);
	day5 = split_part(new.meet_day, ',', 5);
	
	if exists(
		select * from meeting
		where meeting.sectionId = new.sectionId and 
					(
						(day1 <> '' and position(day1 in meeting.meet_day) <> 0) or
						(day2 <> '' and position(day2 in meeting.meet_day) <> 0) or
						(day3 <> '' and position(day3 in meeting.meet_day) <> 0) or
						(day4 <> '' and position(day4 in meeting.meet_day) <> 0) or
						(day5 <> '' and position(day5 in meeting.meet_day) <> 0)
					) and
					(
						(
							split_part(meeting.start_time, ':', 1) < split_part(new.start_time, ':', 1)
							and
							split_part(meeting.end_time, ':', 1) > split_part(new.start_time, ':', 1)
						) or
						(
							split_part(meeting.start_time, ':', 1) < split_part(new.end_time, ':', 1)
							and
							split_part(meeting.end_time, ':', 1) > split_part(new.start_time, ':', 1)
						)
					)
	) then 
		begin
			raise exception 'Overlapping existing meeting (LE/DI/LA)';
			rollback;
			return null;
		end;
	end if;
	return new;
end;
$$;

-- drop function check_meeting_function;
-- drop trigger check_meeting on meeting;

create trigger check_meeting
before insert
on meeting
for each row
	execute procedure check_meeting_function();

-- test cases for (1)
insert into meeting values('MAE108', 'MAE108-sp21-1', 'MAE108-sp21-1-di2', 'Yes', 'DI', 'Friday', '10:00', '11:00', 'R203');
insert into meeting values('CSE221', 'CSE221-sp21-1', 'CSE221-sp21-1-la', 'Yes', 'LA', 'Monday,Wednesday', '09:00', '10:00', 'R203');


-- (2) enrollment limit
create function check_enrollment_function()
	returns trigger
	language plpgsql
as 
$$
declare
	total_num integer;
	num_enrolling integer;
begin
	
	select cast(section.enrollmentlimit as integer) as count1 into total_num from section where section.sectionid = new.sectionid;

	select count(*) as count2 into num_enrolling from enrollment where enrollment.sectionid = new.sectionid group by enrollment.sectionid;
	
	if (num_enrolling >= total_num ) then 
		begin
			raise exception 'Exceed the enrollment limit';
			rollback;
			return null;
		end;
	end if;
	return new;
end;
$$;

-- drop function check_enrollment_function;
-- drop trigger check_enrollment on enrollment;

create trigger check_enrollment 
before insert
on enrollment
for each row
	execute procedure check_enrollment_function();

-- test cases for (2)
-- just insert (fixed unit & single section)
-- multiple sections
update section set enrollmentlimit = 2 where sectionid = 'CSE221-sp21-2';
insert into enrollment values('A1001', 'CSE221', 'CSE221-sp21-2', '4', 'Letter Grade');

update section set enrollmentlimit = 3 where sectionid = 'CSE255-sp21-1';
insert into enrollment values('A1001', 'CSE255', 'CSE255-sp21-1', '4', 'Letter Grade');

-- multiple units
-- multiple sections & units
update section set enrollmentlimit = 1 where sectionid = 'CSE105-sp21-1';
insert into enrollment values('A1002', 'CSE105', 'CSE105-sp21-1', '4', 'Letter Grade');

-- (3) prof should not have multiple sections at the same time 
create function check_prof_section_function()
	returns trigger
	language plpgsql
as 
$$
declare
	prof_name varchar;
	day1 varchar;
	day2 varchar;
	day3 varchar;
	day4 varchar;
	day5 varchar;
begin
	day1 = split_part(new.meet_day, ',', 1);
	day2 = split_part(new.meet_day, ',', 2);
	day3 = split_part(new.meet_day, ',', 3);
	day4 = split_part(new.meet_day, ',', 4);
	day5 = split_part(new.meet_day, ',', 5);

	select section.faculty_name into prof_name from section where section.sectionid = new.sectionid;

	create table other_section_taught_by_prof as select section.sectionid from section where section.faculty_name = prof_name and section.sectionid <> new.sectionid;
	create table other_section_info as select meeting.meet_day, meeting.start_time, meeting.end_time from other_section_taught_by_prof  inner join meeting on other_section_taught_by_prof.sectionid = meeting.sectionid;
	
	if exists (
		select * from other_section_info
		where (
						(day1 <> '' and position(day1 in other_section_info.meet_day) <> 0) or
						(day2 <> '' and position(day2 in other_section_info.meet_day) <> 0) or
						(day3 <> '' and position(day3 in other_section_info.meet_day) <> 0) or
						(day4 <> '' and position(day4 in other_section_info.meet_day) <> 0) or
						(day5 <> '' and position(day5 in other_section_info.meet_day) <> 0)
					) and
					(
						(
							split_part(other_section_info.start_time, ':', 1) < split_part(new.start_time, ':', 1)
							and
							split_part(other_section_info.end_time, ':', 1) > split_part(new.start_time, ':', 1)
						) or
						(
							split_part(other_section_info.start_time, ':', 1) < split_part(new.end_time, ':', 1)
							and
							split_part(other_section_info.end_time, ':', 1) > split_part(new.start_time, ':', 1)
						)
					)
	) then 
		begin
			raise exception 'A Professor cannot have multiple sections at the same time';
			rollback;
			drop table other_section_taught_by_prof;
			drop table other_section_info;
			return null;
		end;
	end if;
	drop table other_section_taught_by_prof;
	drop table other_section_info;
	return new;
end;
$$;


-- drop function check_prof_section_function;
-- drop trigger check_prof_section on meeting;


create trigger check_prof_section
before insert
on meeting
for each row
	execute procedure check_prof_section_function();

-- test cases for (3)
insert into meeting values('CSE8A', 'CSE8A-sp21-1', 'CSE8A-sp21-1-la2', 'Yes', 'LA', 'Friday', '18:00', '19:00', 'R203');
insert into meeting values('PHIL12', 'PHIL12-sp21-1', 'PHIL12-sp21-1-la', 'Yes', 'LA', 'Wednesday,Thrusday', '15:00', '16:00', 'R203');

-- Extra Credit
create function check_prof_rs_function()
	returns trigger
	language plpgsql
as 
$$
declare
	prof_name varchar;
	
begin
	

	select section.faculty_name into prof_name from section where section.sectionid = new.sectionid;

	create table section_taught_by_prof as select section.sectionid from section where section.faculty_name = prof_name;
	create table section_info as select reviewsession.review_date, reviewsession.start_time, reviewsession.end_time from section_taught_by_prof inner join reviewsession on section_taught_by_prof.sectionid = reviewsession.sectionid;
	
	if exists (
		select * from section_info
		where (
							section_info.review_date = new.review_date
					) and
					(
						(
							split_part(section_info.start_time, ':', 1) < split_part(new.start_time, ':', 1)
							and
							split_part(section_info.end_time, ':', 1) > split_part(new.start_time, ':', 1)
						) or
						(
							split_part(section_info.start_time, ':', 1) < split_part(new.end_time, ':', 1)
							and
							split_part(section_info.end_time, ':', 1) > split_part(new.start_time, ':', 1)
						)
					)
	) then 
		begin
			raise exception 'A Professor cannot have multiple review sessions at the same time';
			rollback;
			drop table section_taught_by_prof;
			drop table section_info;
			return null;
		end;
	end if;
	drop table section_taught_by_prof;
	drop table section_info;
	return new;
end;
$$;


-- drop function check_prof_rs_function;
-- drop trigger check_prof_rs on reviewsession;


create trigger check_prof_rs
before insert
on reviewsession
for each row
	execute procedure check_prof_rs_function();

-- test cases for ec
insert into reviewsession values('MAE108', 'MAE108-sp21-1', 'MAE108-sp21-1-review', '06/01', '11:00', '12:00', 'R204');

CREATE TABLE Student(
    Student_ID TEXT,
    SSN TEXT NOT NULL UNIQUE,
    FirstName TEXT NOT NULL,
    MiddleName TEXT,
    LastName TEXT NOT NULL,
    Residency TEXT NOT NULL,
    Enrollment TEXT NOT NULL,
		stu_type text not null,
    PRIMARY KEY (Student_ID)
);

CREATE TABLE Undergraduate (
    student_id TEXT,
    major TEXT NOT NULL,
	monir TEXT,
    college TEXT NOT NULL,
	Primary Key (student_id),
	Foreign Key (student_id) references student(student_id) on Delete CASCADE on update CASCADE
);

Create table Master(
	student_id TEXT,
  	Department TEXT NOT NULL,
  	Stu_type TEXT not null,
	Thesis_Committee int DEFAULT 0,
	Primary Key (student_id),
	Foreign Key (student_id) references student(student_id) on Delete CASCADE on update CASCADE
);

Create table phd(
	student_id TEXT,
  	Department TEXT NOT NULL,
  	Stu_type TEXT not null,
	advisor Text not null,
	Thesis_Committee int DEFAULT 0,
	diff_depart_thesis int DEFAULT 0,
	Primary Key (student_id),
	Foreign Key (student_id) references student(student_id) on Delete CASCADE on update CASCADE
);

CREATE TABLE Probation (
    student_id TEXT,
    start_date TEXT not null,
	end_date TEXT not null,
	Reason TEXT not null,
	Primary Key (start_date,end_date),
	Foreign Key (student_id) references student(student_id) on Delete CASCADE on update CASCADE
);

create table Faculty(
	faculty_name TEXT not NULL,
	Title TEXT not null,
	Department TEXT not null,
	primary key(faculty_name)
);

create table Thesis_committee (
	Student_id Text not null,
	Stu_Program text not null,
	Faculty_name text not null,
	Faculty_Dep text not null,
	Foreign Key (Student_id) references student(student_id) on Delete CASCADE on update CASCADE,
	Foreign Key (Faculty_name) references faculty(faculty_name) on Delete CASCADE on update CASCADE
);

Create Table past_course (
	Student_ID TEXT not NUll,
	Course_ID TEXT not null,
	Units int default 2,
	Grade TEXT not null,
	Quarter text not null,
	year text not null,
	SectionId text not null,
	grade_opt text not null,
	primary key(Student_ID,Course_ID),
	Foreign Key (Course_ID,SectionId) references Section on Delete CASCADE on update CASCADE,
	Foreign Key (Student_ID) references student(student_id) on Delete CASCADE on update CASCADE,
	Foreign Key (Course_ID) references course(courseid) on Delete CASCADE on update CASCADE
);

create table concentration (
	concen_Name text not null,
	Degree_Name Text not null,
	Degree_Type text not null,
	courses text not null,
	elective text not null,
	minGPA DECIMAL default 2.0,
	minUnit int default 0,
	primary key(concen_Name,degree_name,degree_type),
	Foreign Key (degree_name,degree_type) references degree(degree_name,degree_type) on Delete CASCADE on update CASCADE
)
create Table Degree(
 	Degree_Type Text not null,
	Degree_Name text not null,
	Department text not null,
	Total_unit int default 0,
	lowerDivisionUnit int default 0,
	UpperDivisionUnit int default 0,
	ElectiveUnit int default 0,
	primary key(Degree_Name,Degree_Type)
);


create table GRADE_CONVERSION
( LETTER_GRADE CHAR(2) NOT NULL,
NUMBER_GRADE DECIMAL(2,1)
);
insert into grade_conversion values('A+', 4.0);
insert into grade_conversion values('A', 4.0);
insert into grade_conversion values('A-', 3.7);
insert into grade_conversion values('B+', 3.3);
insert into grade_conversion values('B', 3.0);
insert into grade_conversion values('B-', 2.7);
insert into grade_conversion values('C+', 2.3);
insert into grade_conversion values('C', 2.0);
insert into grade_conversion values('C-', 1.7);
insert into grade_conversion values('D', 1.0);
insert into grade_conversion values('F', 0.0);

create table Course(
	CourseId          Text Not Null,
	Course_name       Text Not Null,
	Department        Text Not Null,
	Prerequisites     Text,
	// Units             Int
	GradeOption       Text Not Null,
	Lab               Text Not Null, // not sure
	MinUnits          Int Not Null,
	MaxUnits          Int Not Null,
	Primary key       (CourseId),
	Check (MinUnits <= MaxUnits)
);

create table Class(
	ClassId           Text Not Null,  
	CourseId          Text Not Null,
	Title             Text Not Null,
	Quarter           Text Not Null,
	Year              Text Not Null,
	NumberSec         Text Not Null,
	NextOffer         Text Not Null,
	Primary key       (ClassId),
	Foreign key       (CourseId) references Course on Delete CASCADE on update CASCADE,
	Check (Quarter In ('Fall', 'Winter', 'Spring', 'Summer'))
);


create table Section(
	CourseId          Text Not Null,
	ClassId           Text Not Null,	
	SectionId         Text Not Null,
	Faculty_name      TEXT not NULL,
	EnrollmentLimit   Text Not Null,
	WaitList          Text Not Null,
	Primary key       (CourseId, SectionId),
	Foreign key       (Faculty_name) references Faculty on Delete CASCADE on update CASCADE,
	Foreign key       (ClassId) references class on Delete CASCADE on update CASCADE,
	Foreign key       (CourseId) references Course on Delete CASCADE on update CASCADE
);

create table Enrollment(
	StudentId         Text Not Null,
	CourseId          Text Not Null,
	SectionId         Text Not Null,
	Units             Text Not Null,
    GradeOption       Text Not Null,
	Primary key       (StudentId, CourseId),
    Foreign key       (StudentId) references Student on Delete CASCADE on update CASCADE,
	Foreign key       (CourseId) references Course on Delete CASCADE on update CASCADE,
	Foreign key       (CourseId, SectionId) references Section on Delete CASCADE on update CASCADE
);

create table Meeting(
	CourseId          Text Not Null,
	SectionId         Text Not Null,	
	MeetingId         Text Not Null UNIQUE,
	Meet_required     Text Not Null,
	Meet_type	      Text Not Null,
	Meet_day          Text Not Null,
	Start_time        Text Not Null,
	End_time          Text Not Null,
	Meet_room         Text Not Null,
	Primary key       (MeetingId),
	Foreign key       (CourseId, SectionId) references Section on Delete CASCADE on update CASCADE,
	Check (Meet_type IN ('LE', 'DI', 'LA'))
);

create table ReviewSession(
	CourseId          Text Not Null,
    SectionId         Text Not Null,
    ReviewId          Text Not Null UNIQUE,
	Review_date       Text Not Null,
	Start_time        Text Not Null,
	End_time          Text Not Null,
	Review_room       Text Not Null,
	Primary key       (ReviewId),
	Foreign key       (CourseId) references Course on Delete CASCADE on update CASCADE,
	Foreign key       (CourseId, SectionId) references Section on Delete CASCADE on update CASCADE
);

create table Electives(
	CourseId          Text Not Null,
	Primary key       (CourseId),
	Foreign key       (CourseId) references Course on Delete CASCADE on update CASCADE
);

3mileswind / trionlink-database Goto Github PK

trionlink-database's Introduction

trionlink-database's People

Contributors

Stargazers

Watchers

Recommend Projects

React

Vue.js

Typescript

TensorFlow

Django

Laravel

D3

Recommend Topics

javascript

web

server

Machine learning

Visualization

Game

Recommend Org

Facebook

Microsoft

Google

Alibaba

D3

Tencent