-
Notifications
You must be signed in to change notification settings - Fork 0
/
Copy pathdb.sql
183 lines (143 loc) · 4.48 KB
/
db.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
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
DROP DATABASE IF EXISTS zootest;
CREATE DATABASE zootest;
USE zootest;
CREATE TABLE IF NOT EXISTS employee_roles(
id int(2) NOT NULL,
role_name varchar(20) NOT NULL,
PRIMARY KEY (id)
);
CREATE TABLE IF NOT EXISTS states(
id int(2) NOT NULL,
state_name varchar(40) NOT NULL,
PRIMARY KEY (id)
);
CREATE TABLE IF NOT EXISTS users(
username varchar(20) NOT NULL,
password varchar(20) NOT NULL,
date_created date NOT NULL,
user_id varchar(36) NOT NULL, /*uuid*/
role enum('Customer','Employee'),
active varchar(1) NOT NULL,
PRIMARY KEY (username),
UNIQUE KEY (user_id)
);
CREATE TABLE IF NOT EXISTS employees(
employee_id varchar(36) NOT NULL, /*uuid*/
employee_firstname varchar(15) NOT NULL,
employee_lastname varchar(15) NOT NULL,
street_address varchar(25) NOT NULL,
city varchar(22) NOT NULL,
state int(2) NOT NULL,/*enumerated above*/
zipcode int(5) NOT NULL, /*limited zip to 5 digits - this is all we need to care about*/
phone_number varchar(12) NOT NULL,
employee_dob date NOT NULL,
email varchar(40) NOT NULL,
national_id int(9) NOT NULL,
manager_id varchar(36), /*uuid*/
salary int NOT NULL,
role int(2) NOT NULL,/*enumerated above*/
active varchar(1) NOT NULL,
PRIMARY KEY (employee_id),
FOREIGN KEY (employee_id) REFERENCES users(user_id),
FOREIGN KEY (role) REFERENCES employee_roles(id),
FOREIGN KEY (state) REFERENCES states(id)
);
CREATE TABLE IF NOT EXISTS customers(
membership_id varchar(36) NOT NULL, /*uuid*/
customer_firstName varchar(20) NOT NULL,
customer_LastName varchar(20) NOT NULL,
customer_streetAddress varchar(25) NOT NULL,
customer_city varchar(22) NOT NULL,
customer_state int(2) NOT NULL,
customer_zipCode int(5) NOT NULL,
customer_phoneNumber varchar(12) NOT NULL,
customer_DOB date NOT NULL,
customer_email varchar(40) NOT NULL,
active varchar(1) NOT NULL,
PRIMARY KEY(membership_id),
FOREIGN KEY (membership_id) REFERENCES users(user_id)
);
CREATE TABLE IF NOT EXISTS transactions(
transaction_id varchar(36) NOT NULL, /*uuid*/
member_id varchar(36) NOT NULL, /*uuid*/
transaction_time datetime NOT NULL,
/*items int(2), multivalued -- see items table*/
PRIMARY KEY(transaction_id),
FOREIGN KEY(member_id) REFERENCES customers(membership_id)
);
CREATE TABLE IF NOT EXISTS item_types(
id int(2) NOT NULL,
item_type varchar(20) NOT NULL,
item_cost int NOT NULL,
PRIMARY KEY (id)
);
CREATE TABLE IF NOT EXISTS transaction_items(
trans_id varchar(36) NOT NULL, /*uuid*/
item_id int(2) NOT NULL,
quantity int NOT NULL,
PRIMARY KEY(trans_id, item_id),
FOREIGN KEY(trans_id) REFERENCES transactions(transaction_id),
FOREIGN KEY(item_id) REFERENCES item_types(id)
);
CREATE TABLE IF NOT EXISTS behaviors(
id int(2) NOT NULL,
behavior varchar(40) NOT NULL,
PRIMARY KEY (id)
);
CREATE TABLE IF NOT EXISTS species(
scientific_name varchar(50) NOT NULL, /*maybe replace with surrogate key?*/
population int NOT NULL,
natural_habitat varchar(20) NOT NULL,
diet enum('Herbivore', 'Carnivore', 'Omnivore'),
behavior int(2), /*enumerated above*/
PRIMARY KEY (scientific_name),
FOREIGN KEY (behavior) REFERENCES behaviors(id)
);
CREATE TABLE IF NOT EXISTS areas(
id int(2) NOT NULL,
area varchar(20) NOT NULL,
PRIMARY KEY (id)
);
CREATE TABLE IF NOT EXISTS habitats(
habitat_id int NOT NULL,
employee_id varchar(36) NOT NULL,
last_time_fed datetime,
area int(2), /*enumerated above*/
PRIMARY KEY (habitat_id),
FOREIGN KEY (employee_id) REFERENCES employees(employee_id),
FOREIGN KEY (area) REFERENCES areas(id)
);
CREATE TABLE IF NOT EXISTS animals(
tag_number varchar(36) NOT NULL, /*uuid*/
gender enum('Male', 'Female'),
last_checkup date NOT NULL,
/*illnesses varchar(100),*/
date_of_birth date NOT NULL,
date_of_arrival date,
date_deceased date,
date_of_departure date,
blood_type varchar(3),
species varchar(50) NOT NULL,
habitat_id int NOT NULL,
PRIMARY KEY (tag_number),
FOREIGN KEY (species) REFERENCES species(scientific_name),
FOREIGN KEY (habitat_id) REFERENCES habitats(habitat_id)
);
CREATE TABLE IF NOT EXISTS animal_illnesses(
tag_number varchar(36) NOT NULL,
illness varchar(25) NOT NULL,
description varchar(300),
PRIMARY KEY (tag_number, illness),
FOREIGN KEY (tag_number) REFERENCES animals(tag_number)
);
CREATE TABLE IF NOT EXISTS zoo_events(
event_id int NOT NULL,
event_date date NOT NULL,
name varchar(30) NOT NULL,
start_time time NOT NULL,
end_time time NOT NULL,
area int(2), /*enumerated above*/
description varchar(100),
PRIMARY KEY (event_id),
FOREIGN KEY (area) REFERENCES areas(id)
);