K8fbQfn7

1CREATE DATABASE IF NOT EXISTS petclinic;
2#GRANT ALL PRIVILEGES ON petclinic.* TO pc@localhost IDENTIFIED BY 'pc';
3
4USE petclinic;
5
6CREATE TABLE IF NOT EXISTS vets (
7  id INT(4) UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
8  first_name VARCHAR(30),
9  last_name VARCHAR(30),
10  service_id INT(4) UNSIGNED NOT NULL,
11  FOREIGN KEY (service_id) REFERENCES service(id),
12  INDEX(last_name)
13) engine=InnoDB;
14
15CREATE TABLE IF NOT EXISTS specialties (
16  id INT(4) UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
17  name VARCHAR(80),
18  INDEX(name)
19) engine=InnoDB;
20
21CREATE TABLE IF NOT EXISTS vet_specialties (
22  vet_id INT(4) UNSIGNED NOT NULL,
23  specialty_id INT(4) UNSIGNED NOT NULL,
24  FOREIGN KEY (vet_id) REFERENCES vets(id),
25  FOREIGN KEY (specialty_id) REFERENCES specialties(id),
26  UNIQUE (vet_id,specialty_id)
27) engine=InnoDB;
28
29CREATE TABLE IF NOT EXISTS types (
30  id INT(4) UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
31  name VARCHAR(80),
32  INDEX(name)
33) engine=InnoDB;
34
35CREATE TABLE IF NOT EXISTS owners (
36  id INT(4) UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
37  first_name VARCHAR(30),
38  last_name VARCHAR(30),
39  address VARCHAR(255),
40  city VARCHAR(80),
41  telephone VARCHAR(20),
42  INDEX(last_name)
43) engine=InnoDB;
44
45CREATE TABLE IF NOT EXISTS pets (
46  id INT(4) UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
47  name VARCHAR(30),
48  birth_date DATE,
49  type_id INT(4) UNSIGNED NOT NULL,
50  owner_id INT(4) UNSIGNED NOT NULL,
51  INDEX(name),
52  FOREIGN KEY (owner_id) REFERENCES owners(id),
53  FOREIGN KEY (type_id) REFERENCES types(id)
54) engine=InnoDB;
55
56CREATE TABLE IF NOT EXISTS visits (
57  id INT(4) UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
58  pet_id INT(4) UNSIGNED NOT NULL,
59  visit_date DATE,
60  description VARCHAR(255),
61  FOREIGN KEY (pet_id) REFERENCES pets(id)
62) engine=InnoDB;
63
64CREATE TABLE IF NOT EXISTS memos (
65  id INT(4) UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
66  vet_id INT(4) UNSIGNED NOT NULL,
67  memo_date DATE,
68  description VARCHAR(80),
69  FOREIGN KEY (vet_id) REFERENCES vets(id)
70) engine=InnoDB;
71
72CREATE TABLE IF NOT EXISTS operation (
73  id INT(4) UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
74  vet_id INT(4) UNSIGNED NOT NULL,
75  pet_id INT(4) UNSIGNED NOT NULL,
76  memo_date DATE,
77  description VARCHAR(80),
78  FOREIGN KEY (vet_id) REFERENCES vets(id),
79  FOREIGN KEY (pet_id) REFERENCES pets(id)
80) engine=InnoDB;
81
82CREATE TABLE IF NOT EXISTS service (
83  id INT(4) UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
84  name VARCHAR(80),
85  etage VARCHAR(80),
86  specialty_id INT(4) UNSIGNED NOT NULL,
87  FOREIGN KEY (specialty_id) REFERENCES specialties(id),
88) engine=InnoDB;