-
Notifications
You must be signed in to change notification settings - Fork 0
/
Copy pathFinal_Query1.sql
157 lines (144 loc) · 4.42 KB
/
Final_Query1.sql
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
CREATE TABLE manager
( manid INT NOT NULL,
fname VARCHAR(30) NOT NULL,
lname VARCHAR(30) NOT NULL,
dob DATE NOT NULL,
hourlypay INT NOT NULL,
bonus INT NULL,
email VARCHAR(30) NOT NULL,
PRIMARY KEY (manid) );
CREATE TABLE manphone
( manid INT NOT NULL,
phonenum VARCHAR(30) NOT NULL,
label VARCHAR(30) NOT NULL,
PRIMARY KEY (manid, phonenum),
FOREIGN KEY (manid) REFERENCES manager(manid)
ON DELETE CASCADE);
CREATE TABLE cleaner
( cleanid INT NOT NULL,
fname VARCHAR(30) NOT NULL,
lname VARCHAR(30) NOT NULL,
dob DATE NOT NULL,
hourlypay INT NOT NULL,
phonenum VARCHAR(30) NOT NULL,
manid INT DEFAULT 1,
PRIMARY KEY (cleanid),
FOREIGN KEY (manid) REFERENCES manager(manid)
ON DELETE SET DEFAULT
ON UPDATE CASCADE);
CREATE TABLE receptionist
( recid INT NOT NULL,
fname VARCHAR(30) NOT NULL,
lname VARCHAR(30) NOT NULL,
dob DATE NOT NULL,
hourlypay INT NOT NULL,
phonenum VARCHAR(30) NOT NULL,
email VARCHAR(30) NOT NULL,
manid INT DEFAULT 1,
PRIMARY KEY (recid),
FOREIGN KEY (manid) REFERENCES manager(manid)
ON DELETE SET DEFAULT
ON UPDATE CASCADE);
CREATE TABLE trainer
( trainerid INT NOT NULL,
fname VARCHAR(30) NOT NULL,
lname VARCHAR(30) NOT NULL,
dob DATE NOT NULL,
hourlypay INT NOT NULL,
phonenum VARCHAR(30) NOT NULL,
email VARCHAR(30) NOT NULL,
manid INT DEFAULT 1,
PRIMARY KEY (trainerid),
FOREIGN KEY (manid) REFERENCES manager(manid)
ON DELETE SET DEFAULT
ON UPDATE CASCADE);
CREATE TABLE provider
( provid INT NOT NULL,
name VARCHAR(30) NOT NULL,
location VARCHAR(30) NOT NULL,
phonenum VARCHAR(30) NOT NULL,
email VARCHAR(30) NOT NULL,
manid INT DEFAULT 1,
PRIMARY KEY (provid),
FOREIGN KEY (manid) REFERENCES manager(manid)
ON DELETE SET DEFAULT
ON UPDATE CASCADE);
CREATE TABLE exercisetype
( typeid INT NOT NULL,
name VARCHAR(30) NOT NULL,
PRIMARY KEY (typeid) );
CREATE TABLE equipment
( eqid INT NOT NULL,
name VARCHAR(30) NOT NULL,
provid INT NOT NULL,
typeid INT NOT NULL,
cleanid INT DEFAULT 1,
weight INT NULL,
PRIMARY KEY (eqid),
FOREIGN KEY (provid) REFERENCES provider(provid)
ON DELETE CASCADE,
FOREIGN KEY (typeid) REFERENCES exercisetype(typeid),
FOREIGN KEY (cleanid) REFERENCES cleaner(cleanid)
ON DELETE SET DEFAULT
ON UPDATE CASCADE);
CREATE TABLE hasspecialty
( trainerid INT NOT NULL,
typeid INT NOT NULL,
PRIMARY KEY (trainerid, typeid),
FOREIGN KEY (trainerid) REFERENCES trainer(trainerid)
ON DELETE CASCADE,
FOREIGN KEY (typeid) REFERENCES exercisetype(typeid) );
CREATE TABLE class
( classid INT NOT NULL,
name VARCHAR(30) NOT NULL,
typeid INT NOT NULL,
daysheld VARCHAR(10) NOT NULL,
timeheld VARCHAR(30) NOT NULL,
location VARCHAR(30) NOT NULL,
trainerid INT NOT NULL,
PRIMARY KEY (classid),
FOREIGN KEY (typeid) REFERENCES exercisetype(typeid),
FOREIGN KEY (trainerid) REFERENCES trainer(trainerid)
ON DELETE CASCADE);
CREATE TABLE classuses
( classid INT NOT NULL,
eqid INT NOT NULL,
PRIMARY KEY (classid, eqid),
FOREIGN KEY (classid) REFERENCES class(classid)
ON DELETE CASCADE,
FOREIGN KEY (eqid) REFERENCES equipment(eqid)
ON DELETE CASCADE);
CREATE TABLE member
( memberid INT NOT NULL,
fname VARCHAR(30) NOT NULL,
lname VARCHAR(30) NOT NULL,
mbrstartdate DATE NOT NULL,
mbrenddate DATE NOT NULL,
dob DATE NOT NULL,
platenddate DATE NULL,
trainerid INT NULL,
referredby INT NULL,
PRIMARY KEY (memberid),
FOREIGN KEY (trainerid) REFERENCES trainer(trainerid)
ON DELETE SET NULL
ON UPDATE CASCADE,
FOREIGN KEY (referredby) REFERENCES member(memberid));
CREATE TABLE checksin
( memberid INT NOT NULL,
date DATE NOT NULL,
time VARCHAR(30) NOT NULL,
recid INT DEFAULT 0,
PRIMARY KEY (memberid, date, time),
FOREIGN KEY (memberid) REFERENCES member(memberid)
ON DELETE CASCADE,
FOREIGN KEY (recid) REFERENCES receptionist(recid)
ON DELETE SET NULL
ON UPDATE CASCADE);
CREATE TABLE registeredfor
( classid INT NOT NULL,
memberid INT NOT NULL,
PRIMARY KEY (classid, memberid),
FOREIGN KEY (classid) REFERENCES class(classid)
ON DELETE CASCADE,
FOREIGN KEY (memberid) REFERENCES member(memberid)
ON DELETE CASCADE);