-
Notifications
You must be signed in to change notification settings - Fork 2
/
Copy pathqueries.sql
224 lines (199 loc) · 11.1 KB
/
queries.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
184
185
186
187
188
189
190
191
192
193
194
195
196
197
198
199
200
201
202
203
204
205
206
207
208
209
210
211
212
213
214
215
216
217
218
219
220
221
222
223
224
CREATE DATABASE `happytails`;
USE `happytails`;
CREATE TABLE `user` (
`user_id` varchar(36) NOT NULL,
`name` varchar(250) NOT NULL,
`email` varchar(250) NOT NULL,
`password` varchar(32) NOT NULL,
phone varchar(15),
address varchar(100),
PRIMARY KEY (`user_id`) );
CREATE TABLE `pet` (
`PetID` int(11) NOT NULL AUTO_INCREMENT,
`PetName` varchar(100) NOT NULL,
`Species` varchar(50) NOT NULL,
`Breed` varchar(100) DEFAULT NULL,
`Gender` char(1) NOT NULL,
`DateOfBirth` date NOT NULL,
Owner varchar(36) NOT NULL,
picURL varchar(256),
PRIMARY KEY (`PetID`),
FOREIGN KEY (Owner) REFERENCES user(user_id) ON DELETE CASCADE ON UPDATE CASCADE
);
INSERT INTO `user` VALUES ('eb0c9283-a79f-11ef-8d99-00155dc4b958','John Doe','John@happytails.org','12121212',NULL,NULL);
INSERT INTO pet (PetID, PetName, Species, Breed, Gender, DateOfBirth, Owner, picURL)
VALUES
(1, 'Buddy', 'Dog', 'Golden Retriever','M', '2021-06-15', 'eb0c9283-a79f-11ef-8d99-00155dc4b958', '7acfd702-0873-4e55-afd9-2889590467dc.png'),
(2, 'Mittens', 'Cat', 'Siamese', 'F', '2022-03-10', 'eb0c9283-a79f-11ef-8d99-00155dc4b958', '569851e4-0585-4f27-82a3-c9ccaf0ad736.png'),
(3, 'Charlie', 'Dog', 'Beagle','M', '2019-09-25', 'eb0c9283-a79f-11ef-8d99-00155dc4b958', 'a573e552-1d83-40da-ae84-20caa3f92bea.png');
CREATE TABLE `measurements` (
`MeasurementID` INT NOT NULL AUTO_INCREMENT,
`PetID` INT NOT NULL,
`MeasurementType` ENUM('weight', 'height') NOT NULL,
`Value` DECIMAL(10,2) NOT NULL,
`RecordDate` DATE NOT NULL,
PRIMARY KEY (`MeasurementID`),
FOREIGN KEY (`PetID`) REFERENCES `pet`(`PetID`) ON DELETE CASCADE
);
INSERT INTO measurements (MeasurementID, PetID, RecordDate, Value, MeasurementType)
VALUES
(1, 1, '2024-02-01', 3.0, 'weight'),
(2, 1, '2024-03-01', 4.2, 'weight'),
(3, 1, '2024-04-01', 5.3, 'weight'),
(4, 1, '2024-05-01', 6.0, 'weight'),
(5, 1, '2024-06-01', 6.8, 'weight'),
(6, 1, '2024-07-01', 7.5, 'weight'),
(7, 1, '2024-08-01', 8.1, 'weight'),
(8, 1, '2024-09-01', 8.5, 'weight'),
(9, 1, '2024-10-01', 8.9, 'weight'),
(10, 1, '2024-11-01', 9.0, 'weight'),
(11, 1, '2024-02-01', 20.0, 'height'),
(12, 1, '2024-03-01', 22.0, 'height'),
(13, 1, '2024-04-01', 24.0, 'height'),
(14, 1, '2024-05-01', 26.0, 'height'),
(15, 1, '2024-06-01', 28.0, 'height'),
(16, 1, '2024-07-01', 30.0, 'height'),
(17, 1, '2024-08-01', 32.0, 'height'),
(18, 1, '2024-09-01', 33.5, 'height'),
(19, 1, '2024-10-01', 35.0, 'height'),
(20, 1, '2024-11-01', 36.0, 'height');
-- Insert for PetID 2 (Cat)
INSERT INTO measurements (MeasurementID, PetID, RecordDate, Value, MeasurementType)
VALUES
(21, 2, '2024-02-01', 1.5, 'weight'),
(22, 2, '2024-03-01', 2.1, 'weight'),
(23, 2, '2024-04-01', 2.8, 'weight'),
(24, 2, '2024-05-01', 3.3, 'weight'),
(25, 2, '2024-06-01', 3.8, 'weight'),
(26, 2, '2024-07-01', 4.2, 'weight'),
(27, 2, '2024-08-01', 4.4, 'weight'),
(28, 2, '2024-09-01', 4.6, 'weight'),
(29, 2, '2024-10-01', 4.7, 'weight'),
(30, 2, '2024-11-01', 5.0, 'weight'),
(31, 2, '2024-02-01', 22.0, 'height'),
(32, 2, '2024-03-01', 24.0, 'height'),
(33, 2, '2024-04-01', 26.0, 'height'),
(34, 2, '2024-05-01', 28.0, 'height'),
(35, 2, '2024-06-01', 29.0, 'height'),
(36, 2, '2024-07-01', 30.0, 'height'),
(37, 2, '2024-08-01', 31.0, 'height'),
(38, 2, '2024-09-01', 31.5, 'height'),
(39, 2, '2024-10-01', 32.0, 'height'),
(40, 2, '2024-11-01', 32.5, 'height');
-- Insert for PetID 3 (Large Dog)
INSERT INTO measurements (MeasurementID, PetID, RecordDate, Value, MeasurementType)
VALUES
(41, 3, '2024-02-01', 10.0, 'weight'),
(42, 3, '2024-03-01', 14.2, 'weight'),
(43, 3, '2024-04-01', 18.5, 'weight'),
(44, 3, '2024-05-01', 22.5, 'weight'),
(45, 3, '2024-06-01', 25.0, 'weight'),
(46, 3, '2024-07-01', 28.0, 'weight'),
(47, 3, '2024-08-01', 30.5, 'weight'),
(48, 3, '2024-09-01', 32.0, 'weight'),
(49, 3, '2024-10-01', 33.5, 'weight'),
(50, 3, '2024-11-01', 35.0, 'weight'),
(51, 3, '2024-02-01', 40.0, 'height'),
(52, 3, '2024-03-01', 45.0, 'height'),
(53, 3, '2024-04-01', 50.0, 'height'),
(54, 3, '2024-05-01', 55.0, 'height'),
(55, 3, '2024-06-01', 58.0, 'height'),
(56, 3, '2024-07-01', 60.0, 'height'),
(57, 3, '2024-08-01', 62.0, 'height'),
(58, 3, '2024-09-01', 63.0, 'height'),
(59, 3, '2024-10-01', 64.0, 'height'),
(60, 3, '2024-11-01', 65.0, 'height');
CREATE TABLE todo (
todo_id INT NOT NULL AUTO_INCREMENT,
user_id VARCHAR(36) NOT NULL,
text VARCHAR(255) NOT NULL,
color VARCHAR(50),
done BOOLEAN NOT NULL DEFAULT FALSE,
created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
PRIMARY KEY (todo_id),
FOREIGN KEY (user_id) REFERENCES user(user_id)
);
CREATE TABLE vaccinations (
id INT AUTO_INCREMENT PRIMARY KEY,
species VARCHAR(50) NOT NULL,
vaccination_name VARCHAR(100) NOT NULL,
description TEXT NOT NULL,
due_age_description VARCHAR(100) NOT NULL
);
-- Insert sample data for dogs
INSERT INTO vaccinations (species, vaccination_name, description, due_age_description)
VALUES
('Dog', 'Canine Distemper', 'Protects against a viral disease affecting the respiratory, gastrointestinal, and nervous systems.', 'First dose at 6-8 weeks, boosters every 3-4 weeks until 16 weeks.'),
('Dog', 'Canine Parvovirus', 'Protects against a highly contagious virus causing severe diarrhea and vomiting.', 'Start at 6-8 weeks, boosters every 3-4 weeks until 16 weeks.'),
('Dog', 'Rabies', 'Protects against a fatal viral disease transmissible to humans.', 'Administered at 12-16 weeks, booster every 1-3 years.'),
('Dog', 'Leptospirosis', 'Prevents a bacterial infection affecting the liver and kidneys.', 'First dose at 12 weeks, second dose after 2-4 weeks, annual booster.'),
('Dog', 'Bordetella', 'Protects against kennel cough, a respiratory infection.', 'First dose at 8 weeks, booster annually.');
-- Insert sample data for cats
INSERT INTO vaccinations (species, vaccination_name, description, due_age_description)
VALUES
('Cat', 'Feline Panleukopenia', 'Protects against a severe and highly contagious viral disease.', 'Start at 6-8 weeks, boosters every 3-4 weeks until 16 weeks.'),
('Cat', 'Feline Herpesvirus', 'Protects against a respiratory disease caused by herpesvirus.', 'First dose at 6-8 weeks, boosters every 3-4 weeks until 16 weeks.'),
('Cat', 'Rabies', 'Protects against a fatal viral disease transmissible to humans.', 'Administered at 12-16 weeks, booster every 1-3 years.'),
('Cat', 'Feline Leukemia Virus (FeLV)', 'Protects against a virus causing leukemia and immune suppression.', 'Administer at 8 weeks, second dose after 3-4 weeks, annual booster.');
-- Insert sample data for rabbits
INSERT INTO vaccinations (species, vaccination_name, description, due_age_description)
VALUES
('Rabbit', 'Rabbit Hemorrhagic Disease Virus (RHDV)', 'Protects against a highly fatal viral disease affecting the liver.', 'Administer at 4-6 weeks, booster annually.'),
('Rabbit', 'Myxomatosis', 'Protects against a viral disease causing swelling and skin lesions.', 'Administer at 4-6 weeks, booster every 6-12 months.');
-- Insert sample data for horses
INSERT INTO vaccinations (species, vaccination_name, description, due_age_description)
VALUES
('Horse', 'Tetanus', 'Protects against a bacterial infection causing muscle stiffness and spasms.', 'First dose at 4-6 months, booster 4-6 weeks later, then annually.'),
('Horse', 'Rabies', 'Protects against a fatal viral disease transmissible to humans.', 'Administer at 6 months or older, booster annually.'),
('Horse', 'Eastern/Western Equine Encephalomyelitis', 'Protects against viral diseases causing brain inflammation.', 'First dose at 4-6 months, booster 4 weeks later, then annually.');
-- Insert sample data for ferrets
INSERT INTO vaccinations (species, vaccination_name, description, due_age_description)
VALUES
('Ferret', 'Canine Distemper', 'Protects against a highly fatal viral disease in ferrets.', 'First dose at 6-8 weeks, second dose at 10-12 weeks, booster annually.'),
('Ferret', 'Rabies', 'Protects against a fatal viral disease transmissible to humans.', 'Administer at 12-16 weeks, booster annually.');
CREATE TABLE vaccination_records (
pet_id INT NOT NULL,
vac_id INT NOT NULL,
vaccination_date DATE,
status varchar(20) NOT NULL,
PRIMARY KEY (pet_id, vac_id), -- Composite primary key
FOREIGN KEY (pet_id) REFERENCES pet(PetID) ON DELETE CASCADE,
FOREIGN KEY (vac_id) REFERENCES vaccinations(id) ON DELETE CASCADE
);
CREATE TABLE `clinics` (
`clinicID` VARCHAR(36) NOT NULL,
`email` VARCHAR(64) NOT NULL,
`password` VARCHAR(32) NOT NULL,
`name` VARCHAR(128) NOT NULL,
`rating` DECIMAL(2,1) default 4,
`address` VARCHAR(256) NOT NULL,
`joinDate` DATE,
`locationURL` VARCHAR(1024),
`availableHours` VARCHAR(32),
PRIMARY KEY (`clinicID`)
);
INSERT INTO clinics (clinicID, email, password, name, rating, address, joinDate, locationURL,availableHours) VALUES
(UUID(), 'pethouse@happytails.org', '12121212', 'Pet House - Animal Hospital', 4.6, 'No. 123, Main Street, Colombo 01, Sri Lanka', '2023-01-15', 'https://maps.app.goo.gl/Wc5uiNzij6jxcU1a9','8 AM - 5 PM'),
(UUID(), 'care4pets@happytails.org', '12121212', 'Care4Pets Veterinary Clinic', 4.8, 'No. 45, Flower Road, Colombo 07, Sri Lanka', '2023-05-10', 'https://maps.app.goo.gl/Wc5uiNzij6jxcU1a9','9 AM - 5 PM'),
(UUID(), 'vethaven@happytails.org', '12121212', 'Vet Haven Clinic', 4.5, 'No. 89, Peradeniya Road, Kandy, Sri Lanka', '2023-04-20', 'https://maps.app.goo.gl/Wc5uiNzij6jxcU1a9','10 AM - 6 PM'),
(UUID(), 'furryfriends@happytails.org', '12121212', 'Furry Friends Veterinary Center', 4.7, 'No. 12, Sea View Avenue, Galle, Sri Lanka', '2023-03-01', 'https://maps.app.goo.gl/Wc5uiNzij6jxcU1a9','8 AM - 8 PM'),
(UUID(), 'animalcare@happytails.org', '12121212', 'Animal Care Clinic', 4.3, 'No. 67, Station Road, Jaffna, Sri Lanka', '2023-06-25', 'https://maps.app.goo.gl/Wc5uiNzij6jxcU1a9','8 AM - 5 PM'),
(UUID(), 'pawspital@happytails.org', '12121212', 'Pawspital Veterinary Center', 4.9, 'No. 34, Hill Street, Nuwara Eliya, Sri Lanka', '2023-07-15', 'https://maps.app.goo.gl/Wc5uiNzij6jxcU1a9','8 AM - 5 PM'),
(UUID(), 'happyvet@happytails.org', '12121212', 'Happy Vet Clinic', 4.6, 'No. 78, Lake Road, Kurunegala, Sri Lanka', '2023-02-05', 'https://maps.app.goo.gl/Wc5uiNzij6jxcU1a9','7 AM - 9 PM'),
(UUID(), 'petsafe@happytails.org', '12121212', 'PetSafe Animal Hospital', 4.4, 'No. 25, Fort Street, Trincomalee, Sri Lanka', '2023-09-12', 'https://maps.app.goo.gl/Wc5uiNzij6jxcU1a9','8 AM - 9 PM'),
(UUID(), 'vetzone@happytails.org', '12121212', 'VetZone Clinic', 4.5, 'No. 90, Canal Road, Batticaloa, Sri Lanka', '2023-08-22', 'https://maps.app.goo.gl/Wc5uiNzij6jxcU1a9','7 AM - 7 PM'),
(UUID(), 'provet@happytails.org', '12121212', 'ProVet Veterinary Hospital', 4.7, 'No. 56, City Center, Anuradhapura, Sri Lanka', '2023-10-30', 'https://maps.app.goo.gl/Wc5uiNzij6jxcU1a9','6 AM - 10 PM');
CREATE TABLE appointments (
appointmentID INT AUTO_INCREMENT PRIMARY KEY,
ownerID VARCHAR(36) NOT NULL,
contactNo VARCHAR(15) NOT NULL,
petName VARCHAR(128) NOT NULL,
reason VARCHAR(128) NOT NULL,
clinicID VARCHAR(36) NOT NULL,
date DATE NOT NULL,
startTime VARCHAR(10) NOT NULL,
endTime VARCHAR(10) NOT NULL,
FOREIGN KEY (ownerID) REFERENCES user(user_id) ON DELETE CASCADE,
FOREIGN KEY (clinicID) REFERENCES clinics(clinicID) ON DELETE CASCADE
);
select * from user;