ieXzZ8H9

1-- The Begin of the script
2DROP DATABASE IF EXISTS `cs122a`;
3CREATE DATABASE `cs122a`;
4USE `cs122a`;
5
6DROP TABLE IF EXISTS User;
7CREATE TABLE User(
8  user_id VARCHAR(20),
9  email VARCHAR(20) NOT NULL,
10  firstname VARCHAR(20) NOT NULL,
11  lastname VARCHAR(20) NOT NULL,
12  PRIMARY KEY(user_id)
13);
14
15DROP TABLE IF EXISTS Student;
16CREATE TABLE Student(
17  user_id VARCHAR(20),
18  major VARCHAR(20),
19  PRIMARY KEY(user_id),
20  FOREIGN KEY(user_id) REFERENCES User(user_id) ON DELETE CASCADE
21);
22
23DROP TABLE IF EXISTS Instructor;
24CREATE TABLE Instructor(
25  user_id VARCHAR(20),
26  title VARCHAR(20) NOT NULL,
27  PRIMARY KEY(user_id),
28  FOREIGN KEY(user_id) REFERENCES User(user_id) ON DELETE CASCADE
29);
30
31DROP TABLE IF EXISTS phone;
32CREATE TABLE phone(
33  user_id VARCHAR(20),
34  number VARCHAR(20),
35  type ENUM('Home', 'Office', 'Work'),
36  FOREIGN KEY(user_id) REFERENCES User(user_id) ON DELETE CASCADE
37);
38
39DROP TABLE IF EXISTS Course;
40CREATE TABLE Course(
41  title VARCHAR(20) NOT NULL,
42  level ENUM('lower division', 'upper division', 'graduate') NOT NULL,
43  dept VARCHAR(20),
44  cno VARCHAR(20),
45  PRIMARY KEY(dept, cno)
46);
47
48DROP TABLE IF EXISTS Class;
49CREATE TABLE Class(
50  dept VARCHAR(20),
51  cno VARCHAR(20),
52  class_no INTEGER,
53  start DATE NOT NULL,
54  end DATE NOT NULL,
55  max_students INTEGER NOT NULL,
56  term ENUM('Fall', 'Winter', 'Spring', 'Summer') NOT NULL,
57  year INTEGER NOT NULL,
58  PRIMARY KEY(dept, cno, class_no),
59  FOREIGN KEY(dept, cno) REFERENCES Course(dept, cno) ON DELETE CASCADE
60);
61
62DROP TABLE IF EXISTS Assignment;
63CREATE TABLE Assignment(
64  assign_id VARCHAR(20),
65  name VARCHAR(20) NOT NULL,
66  deadline DATETIME NOT NULL,
67  class_no INTEGER NOT NULL,
68  dept VARCHAR(20) NOT NULL,
69  cno VARCHAR(20) NOT NULL,
70  PRIMARY KEY(assign_id),
71  FOREIGN KEY(dept, cno, class_no) REFERENCES Class(dept, cno, class_no)
72);
73
74DROP TABLE IF EXISTS Post;
75CREATE TABLE Post(
76  post_id VARCHAR(20),
77  kind ENUM('question', 'announcement', 'comment'),
78  text VARCHAR(250) NOT NULL,
79  timestamp DATETIME NOT NULL,
80  popularity INTEGER,
81  user_id VARCHAR(20) NOT NULL,
82  assign_id VARCHAR(20),
83  resp_id VARCHAR(20),
84  PRIMARY KEY(post_id),
85  FOREIGN KEY(user_id) REFERENCES User(user_id) ON DELETE NO ACTION,
86  FOREIGN KEY(assign_id) REFERENCES Assignment(assign_id),
87  FOREIGN KEY(resp_id) REFERENCES Assignment(assign_id)
88);
89
90DROP TABLE IF EXISTS topic;
91CREATE TABLE topic(
92  post_id VARCHAR(20),
93  topic VARCHAR(20),
94  #PRIMARY KEY(post_id),
95  FOREIGN KEY(post_id) REFERENCES Post(post_id) ON DELETE CASCADE
96);
97###Relationships###
98
99DROP TABLE IF EXISTS Takes;
100CREATE TABLE Takes(
101  grade CHAR(2),
102  user_id VARCHAR(20),
103  class_no INTEGER,
104  dept VARCHAR(20),
105  cno VARCHAR(20),
106  PRIMARY KEY(user_id, dept, cno, class_no),
107  FOREIGN KEY(user_id) REFERENCES Student(user_id),
108  FOREIGN KEY(dept, cno, class_no) REFERENCES Class(dept, cno, class_no)  
109);
110
111DROP TABLE IF EXISTS Does;
112CREATE TABLE Does(
113  grade CHAR(2) NOT NULL,
114  user_id VARCHAR(20),
115  assign_id VARCHAR(20),
116  PRIMARY KEY(user_id, assign_id),
117  FOREIGN KEY(user_id) REFERENCES Student(user_id),
118  FOREIGN KEY(assign_id) REFERENCES Assignment(assign_id)
119);
120
121DROP TABLE IF EXISTS Offers;
122CREATE TABLE Offers(
123  user_id VARCHAR(20),
124  class_no INTEGER,
125  dept VARCHAR(20),
126  cno VARCHAR(20),
127  PRIMARY KEY(user_id, dept, cno, class_no),
128  FOREIGN KEY(user_id) REFERENCES Instructor(user_id),
129  FOREIGN KEY(dept, cno, class_no) REFERENCES Class(dept, cno, class_no)
130);
131
132
133INSERT INTO USER(user_id, email, firstname, lastname) VALUES ("20103446", "notbsheikh@uci.edu", "Basit", "Sheikh");
134INSERT INTO Student(user_id, major) VALUES ("20103446", "CS");
135#INSERT INTO Post(post_id) VALUES ("bsheikh");
136#INSERT INTO topic(post_id, topic) VALUES ("bsheikh", "Logistics");
137#INSERT INTO topic(post_id, topic) VALUES ("bsheikh", "Exam1");
138INSERT INTO User(user_id, email, firstname, lastname) VALUES ("bsheikh", "bsheikh@uci.edu", "basit", "sheikh");
139INSERT INTO Phone(user_id, number, type) VALUES ("bsheikh", 3106197810, "Office");
140INSERT INTO Course (title, level, dept, cno) VALUES ("Data Management", "upper division", "CS", "122A");
141INSERT INTO Course (title, level, dept, cno) VALUES ("ConceptsInPgmL", "upper division", "CS", "141");
142INSERT INTO Class (dept, cno, start, end, max_students, term, year, class_no)  VALUES ("CS", "122A", "2018-04-01", "2018-04-30", 400, "Spring", 2018, 1);
143INSERT INTO Takes(user_id, dept, cno, class_no) VALUES ("20103446","CS","122A",1);
144
145INSERT INTO Post(post_id, kind, text, timestamp, popularity, user_id)
146VALUES ("001", "question", "this is the body", '2015-03-03 01:02:03', 5, "20103446");
147
148-- The end of the script