SQLize Online / PHPize Online  /  SQLtest Online

CREATE TABLE IF NOT EXISTS `department` ( `dept_name` varchar(100) NOT NULL, `building` varchar(255) NOT NULL, `budget` double(10, 2) NOT NULL, PRIMARY KEY (`dept_name`) ); CREATE TABLE IF NOT EXISTS `student` ( `id` int(255) NOT NULL, `name` varchar(255) NOT NULL, `dept_name` varchar(100) NOT NULL, `tot_cred` smallint(4) NOT NULL, PRIMARY KEY (`id`), FOREIGN KEY (`dept_name`) REFERENCES department(`dept_name`) ); CREATE TABLE IF NOT EXISTS `instructor` ( `id` int(255) NOT NULL, `name` varchar(255) NOT NULL, `dept_name` varchar(100) NOT NULL, `salary` double(10, 2), PRIMARY KEY (`id`), FOREIGN KEY (`dept_name`) REFERENCES department(`dept_name`) ); CREATE TABLE IF NOT EXISTS `advisor` ( `s_id` int(255) NOT NULL, `i_id` int(255) NOT NULL, PRIMARY KEY (`s_id`), FOREIGN KEY (`s_id`) REFERENCES student(`id`), FOREIGN KEY (`i_id`) REFERENCES instructor(`id`) ); CREATE TABLE IF NOT EXISTS `course` ( `course_id` int(255) NOT NULL, `title` varchar(255) NOT NULL, `dept_name` varchar(100) NOT NULL, `credits` smallint(4) NOT NULL, PRIMARY KEY (`course_id`), FOREIGN KEY (`dept_name`) REFERENCES department(`dept_name`) ); CREATE TABLE IF NOT EXISTS `prereq` ( `course_id` int(255) NOT NULL, `prereq_id` int(255) NOT NULL, PRIMARY KEY (`course_id`, `prereq_id`), FOREIGN KEY (`course_id`) REFERENCES course(`course_id`), FOREIGN KEY (`prereq_id`) REFERENCES course(`course_id`) ); CREATE TABLE IF NOT EXISTS `section` ( `course_id` int(255) NOT NULL, `sec_id` int(255) NOT NULL, `semester` smallint(1) NOT NULL, `year` smallint(1) NOT NULL, `building` varchar(255) NOT NULL, `room_no` smallint(5) NOT NULL, `time_slot_id` int(255) NOT NULL, PRIMARY KEY (`course_id`, `sec_id`, `semester`, `year`), FOREIGN KEY (`course_id`) REFERENCES course(`course_id`), FOREIGN KEY (`building`) REFERENCES classroom(`building`), FOREIGN KEY (`room_no`) REFERENCES classroom(`room_no`), FOREIGN KEY (`time_slot_id`) REFERENCES time_slot(`time_slot_id`), ); CREATE TABLE IF NOT EXISTS `teaches` ( `id` int(255) NOT NULL, `course_id` int(255) NOT NULL, `sec_id` int(255) NOT NULL, `semester` smallint(1) NOT NULL, `year` smallint(1) NOT NULL, PRIMARY KEY (`id`, `course_id`, `sec_id`, `semester`, `year`), FOREIGN KEY (`id`) REFERENCES instructor(`id`), FOREIGN KEY (`course_id`) REFERENCES section(`course_id`), FOREIGN KEY (`sec_id`) REFERENCES section(`sec_id`), FOREIGN KEY (`semester`) REFERENCES section(`semester`), FOREIGN KEY (`year`) REFERENCES section(`year`) );