dvknV0VK

1DROP TABLE IF EXISTS enrolls;
2DROP TABLE IF EXISTS schedule;
3DROP TABLE IF EXISTS course;
4DROP TABLE IF EXISTS role;
5DROP TABLE IF EXISTS user;
6
7-- Create user table
8CREATE TABLE user (
9    uid INT,
10    password VARCHAR(32),
11    fname VARCHAR(32),
12    lname VARCHAR(32),
13    address VARCHAR(128),
14    PRIMARY KEY (uid)
15);
16
17-- Create role table
18CREATE TABLE role (
19    uid INT,
20    type VARCHAR(16),
21    FOREIGN KEY (uid) REFERENCES user(uid)
22);
23
24-- Create course table
25CREATE TABLE course (
26    cid INT AUTO_INCREMENT,
27    dept VARCHAR(4),
28    cnum INT,
29    title VARCHAR(64),
30    credits INT,
31    instructor_id INT,
32    prereq1_id INT,
33    prereq2_id INT,
34    PRIMARY KEY (cid),
35    -- Uncomment when 'user' table is created
36    FOREIGN KEY (instructor_id) REFERENCES user(uid),
37    FOREIGN KEY (prereq1_id) REFERENCES course(cid),
38    FOREIGN KEY (prereq2_id) REFERENCES course(cid),
39    CONSTRAINT unique_course UNIQUE(dept, cnum)
40);
41
42-- Create schedule table
43CREATE TABLE schedule (
44    sid INT AUTO_INCREMENT,
45    cid INT,
46    section INT,
47    term VARCHAR(4),
48    day VARCHAR(1),
49    start TIME,
50    end TIME,
51    is_current INT, -- tells us whether this is the latest semester or not
52    PRIMARY KEY (sid),
53    FOREIGN KEY (cid) REFERENCES course(cid)
54);
55
56-- Create enrolls table
57CREATE TABLE enrolls (
58    uid INT,
59    sid INT,
60    grade VARCHAR(2),
61    -- Uncomment when 'user' table is created
62    FOREIGN KEY (uid) REFERENCES user(uid),
63    FOREIGN KEY (sid) REFERENCES schedule(sid)
64);
65
66source populate_users.sql;
67source populate_role.sql;
68source populate_courses.sql;
69source populate_schedule.sql;
70source populate_enrolls.sql;