ep2SxFJj

1CREATE DATABASE courses;  
2
3USE courses;
4
5DROP TABLE IF EXISTS course;
6DROP TABLE IF EXISTS unit;
7DROP TABLE IF EXISTS formal_request;
8
9CREATE TABLE course (
10    course_id INT(11) NOT NULL AUTO_INCREMENT,
11    course_name VARCHAR(100) NOT NULL,
12    PRIMARY KEY(course_id)
13);
14
15INSERT INTO course (course_name) VALUES 
16("Bachelor of Science in Computer Science"), 
17("Bachelor of Science in Medicine and Surgery"),
18("Diploma in Science in Medicine and Surgery"),
19("Bachelor of Engineering in Electrical and Electronics"),
20("Bachelor of Science in Information Technology"),
21("Bachelor of Science in Computer Technology");
22
23CREATE TABLE unit (
24    unit_code VARCHAR(11) NOT NULL,
25    course_id INT(11) NOT NULL,
26    unit_name VARCHAR(100) NOT NULL,
27    PRIMARY KEY(unit_code),
28    FOREIGN KEY(course_id) 
29        REFERENCES course(course_id) 
30        ON UPDATE CASCADE ON DELETE CASCADE
31);
32
33INSERT INTO unit (unit_code, course_id, unit_name) VALUES 
34("HRD 536", 1, "Industrial Management"),
35("SMA 2102", 2, "Ordinary Differential Equations"),
36("SMA 2302", 3, "ANATOMY"),
37("ICS 2200", 4, "Discrete Structures"), 
38("SMA 2015", 5, "CALCULUS III"),
39("SMA 2103", 6, "Probability and Statistics");
40
41CREATE TABLE formal_request (
42    request_id INT(11) NOT NULL AUTO_INCREMENT,
43    course_id INT(11) NOT NULL,
44    prerequisite_id INT(11) NOT NULL,
45    PRIMARY KEY(request_id),
46    FOREIGN KEY(course_id) 
47        REFERENCES course(course_id) 
48        ON UPDATE CASCADE ON DELETE CASCADE
49);
50
51INSERT INTO formal_request (course_id, prerequisite_id) VALUES 
52(2, 3);