· 4 years ago · Apr 05, 2021, 05:26 PM
1DROP TABLE IF EXISTS Customers CASCADE;
2DROP TABLE IF EXISTS Credit_cards CASCADE;
3DROP TABLE IF EXISTS Course_packages CASCADE;
4DROP TABLE IF EXISTS Sessions CASCADE;
5DROP TABLE IF EXISTS Offerings CASCADE;
6DROP TABLE IF EXISTS Courses CASCADE;
7DROP TABLE IF EXISTS Rooms CASCADE;
8DROP TABLE IF EXISTS Owns CASCADE;
9DROP TABLE IF EXISTS Registers CASCADE;
10DROP TABLE IF EXISTS Buys CASCADE;
11DROP TABLE IF EXISTS Redeems CASCADE;
12DROP TABLE IF EXISTS Cancels CASCADE;
13DROP TABLE IF EXISTS Conducts CASCADE;
14DROP TABLE IF EXISTS Specializes CASCADE;
15DROP TABLE IF EXISTS Course_areas CASCADE;
16DROP TABLE IF EXISTS Pay_slips CASCADE;
17DROP TABLE IF EXISTS Employees CASCADE;
18DROP TABLE IF EXISTS Part_time_Emp CASCADE;
19DROP TABLE IF EXISTS Full_time_Emp CASCADE;
20DROP TABLE IF EXISTS Instructors CASCADE;
21DROP TABLE IF EXISTS Part_time_instructors CASCADE;
22DROP TABLE IF EXISTS Full_time_instructors CASCADE;
23DROP TABLE IF EXISTS Managers CASCADE;
24DROP TABLE IF EXISTS Administrators CASCADE;
25
26CREATE TABLE Customers (
27 cust_id INTEGER PRIMARY KEY AUTO_INCREMENT,
28 address VARCHAR(250),
29 phone VARCHAR(15),
30 name VARCHAR(100),
31 email VARCHAR(100),
32 UNIQUE (address, phone, name, email)
33);
34
35CREATE TABLE Credit_cards (
36 number INTEGER PRIMARY KEY,
37 CVV INTEGER NOT NULL,
38 expiry_date DATE NOT NULL
39);
40
41CREATE TABLE Course_packages (
42 package_id INTEGER PRIMARY KEY AUTO_INCREMENT,
43 sale_start_date DATE NOT NULL,
44 sale_end_date DATE NOT NULL,
45 num_free_registrations INTEGER,
46 name VARCHAR(100) NOT NULL,
47 price DECIMAL NOT NULL
48);
49
50CREATE TABLE Rooms (
51 rid INTEGER PRIMARY KEY,
52 location VARCHAR(50) NOT NULL,
53 seating_capacity INTEGER NOT NULL
54);
55
56CREATE TABLE Employees (
57 eid INTEGER PRIMARY KEY,
58 name VARCHAR(30) NOT NULL,
59 phone INTEGER,
60 address VARCHAR(30),
61 email VARCHAR(50),
62 join_date DATE,
63 depart_date DATE
64);
65
66CREATE TABLE Part_time_Emp (
67 eid INTEGER PRIMARY KEY references Employees(eid) on delete cascade,
68 hourly_rate NUMERIC NOT NULL
69);
70
71CREATE TABLE Full_time_Emp (
72 eid INTEGER PRIMARY KEY references Employees(eid) on delete cascade,
73 monthly_salary NUMERIC NOT NULL
74);
75
76CREATE TABLE Instructors (
77 eid INTEGER PRIMARY KEY,
78 foreign key (eid) references Employees(eid) on delete cascade
79);
80
81CREATE TABLE Part_time_instructors (
82 eid INTEGER PRIMARY KEY,
83 foreign key (eid) references Part_time_Emp(eid) on delete cascade,
84 foreign key (eid) references Instructors(eid) on delete cascade
85);
86
87CREATE TABLE Full_time_instructors (
88 eid INTEGER PRIMARY KEY,
89 foreign key (eid) references Full_time_Emp(eid) on delete cascade,
90 foreign key (eid) references Instructors(eid) on delete cascade
91);
92
93CREATE TABLE Managers (
94 eid INTEGER PRIMARY KEY references Full_time_Emp(eid) on delete cascade
95);
96
97CREATE TABLE Administrators (
98 eid INTEGER PRIMARY KEY references Full_time_Emp(eid) on delete cascade
99);
100
101-- Integrate with Manages lecture ER page 44
102CREATE TABLE Course_areas (
103 name VARCHAR(50) primary key,
104 eid INTEGER NOT NULL,
105 foreign key(eid) references Managers(eid)
106);
107
108CREATE TABLE Courses (
109 course_id INTEGER PRIMARY KEY AUTO_INCREMENT,
110 title VARCHAR(50) NOT NULL,
111 duration INTEGER NOT NULL,
112 description VARCHAR(250),
113 name VARCHAR(50) NOT NULL,
114 foreign key (name) REFERENCES Course_areas(name)
115);
116
117-- integrate with Handles lecture ER page 44
118CREATE TABLE Offerings (
119 launch_date DATE NOT NULL,
120 course_id INTEGER NOT NULL,
121 eid INTEGER NOT NULL,
122 end_date DATE,
123 start_date DATE,
124 registration_deadline TIMESTAMP,
125 target_number_registrations INTEGER,
126 seating_capacity INTEGER,
127 fees NUMERIC NOT NULL,
128 primary key (launch_date, course_id),
129 foreign key (course_id) REFERENCES Courses(course_id) on delete cascade,
130 foreign key (eid) REFERENCES Administrators(eid)
131);
132
133CREATE TABLE Sessions (
134 sid INTEGER,
135 start_time TIME,
136 end_time TIME,
137 session_date DATE,
138 launch_date DATE NOT NULL,
139 course_id INTEGER NOT NULL,
140 rid INTEGER NOT NULL,
141 primary key (sid, launch_date, course_id, rid),
142 foreign key (rid) REFERENCES Rooms(rid),
143 foreign key (launch_date) REFERENCES Offerings(launch_date) on delete cascade,
144 foreign key (course_id) REFERENCES Courses(course_id) on delete cascade
145);
146
147CREATE TABLE Owns (
148 from_date DATE NOT NULL,
149 cust_id INTEGER NOT NULL,
150 number INTEGER,
151 foreign key (cust_id) REFERENCES Customers(cust_id),
152 foreign key (number) REFERENCES Credit_cards(number),
153 primary key (number)
154);
155
156CREATE TABLE Registers (
157 registers_date DATE,
158 sid INTEGER references Sessions(sid),
159 number INTEGER,
160 cust_id INTEGER,
161 primary key (registers_date, sid, number, cust_id),
162 foreign key (number) references Owns(number),
163 foreign key (cust_id) references Customers(cust_id)
164);
165
166CREATE TABLE Buys (
167 buys_date DATE,
168 num_remaining_redemptions INTEGER,
169 number INTEGER,
170 package_id INTEGER,
171 cust_id INTEGER,
172 PRIMARY KEY (buys_date, number, package_id, cust_id),
173 foreign key (number) references Owns(number),
174 foreign key (cust_id) references Customers(cust_id),
175 foreign key (package_id) REFERENCES Course_packages(package_id)
176);
177
178CREATE TABLE Redeems (
179 redeems_date DATE,
180 buys_date DATE,
181 number INTEGER,
182 package_id INTEGER,
183 sid INTEGER,
184 launch_date DATE,
185 course_id INTEGER,
186 foreign key (buys_date, number, package_id) REFERENCES Buys(buys_date, number, package_id),
187 foreign key (sid, launch_date, course_id) REFERENCES Sessions(sid, launch_date, course_id),
188 primary key (redeems_date, buys_date, number, package_id, sid, launch_date, course_id)
189);
190
191CREATE TABLE Cancels (
192 cancels_date DATE,
193 cust_id INTEGER NOT NULL,
194 sid INTEGER NOT NULL,
195 launch_date DATE NOT NULL,
196 course_id INTEGER NOT NULL,
197 refund_amt numeric,
198 package_credit integer
199 check(package_credit >= 0),
200 foreign key (sid, launch_date, course_id) REFERENCES Sessions(sid, launch_date, course_id),
201 foreign key (cust_id) REFERENCES Customers(cust_id),
202 primary key (cancels_date, cust_id, sid, launch_date, course_id)
203);
204
205CREATE TABLE Conducts (
206 rid INTEGER NOT NULL,
207 eid INTEGER NOT NULL,
208 sid INTEGER NOT NULL,
209 launch_date DATE NOT NULL,
210 course_id INTEGER NOT NULL,
211 foreign key (eid) REFERENCES Employees(eid),
212 foreign key (sid, launch_date, course_id, rid) REFERENCES Sessions(sid, launch_date, course_id, rid),
213 primary key (sid, launch_date, course_id, rid)
214);
215
216CREATE TABLE Specializes (
217 eid INTEGER references Instructors(eid),
218 name VARCHAR(50) references Course_areas(name),
219 primary key(eid, name)
220);
221
222CREATE TABLE Pay_slips (
223 eid INTEGER,
224 payment_date DATE,
225 amount float,
226 num_work_hours INTEGER,
227 num_work_days INTEGER,
228 primary key (payment_date, eid),
229 foreign key (eid) references Employees(eid) on delete cascade
230);