· 6 years ago · Oct 08, 2019, 10:02 AM
1DROP TABLE IF EXISTS Medical_Site ;
2CREATE TABLE Medical_Site (
3 id_Medical_Site BIGINT AUTO_INCREMENT NOT NULL,
4 name_Medical_Site VARCHAR,
5 id_Address BIGINT,
6 PRIMARY KEY (id_Medical_Site)
7);
8
9DROP TABLE IF EXISTS Doctor ;
10CREATE TABLE Doctor (
11 id_Doctor BIGINT AUTO_INCREMENT NOT NULL,
12 CDHP_Doctor BIGINT,
13 gps_coordonate_id_gps_coordonate BIGINT,
14 social_details_id_social_details BIGINT,
15 PRIMARY KEY (id_Doctor)
16);
17
18DROP TABLE IF EXISTS Profession_Type ;
19CREATE TABLE Profession_Type (
20 id_Profession_Type BIGINT AUTO_INCREMENT NOT NULL,
21 name_Profession_Type VARCHAR,
22 PRIMARY KEY (id_Profession_Type)
23);
24
25DROP TABLE IF EXISTS Tag ;
26CREATE TABLE Tag (
27 id_Tag BIGINT AUTO_INCREMENT NOT NULL,
28 name_Tag VARCHAR,
29 type_Tag VARCHAR,
30 PRIMARY KEY (id_Tag)
31);
32
33DROP TABLE IF EXISTS Doctor_Diagnostic ;
34CREATE TABLE Doctor_Diagnostic (
35 id_Doctor_Diagnostic BIGINT AUTO_INCREMENT NOT NULL,
36 title_Doctor_Diagnostic VARCHAR,
37 report_Doctor_Diagnostic VARCHAR,
38 gravityGrade_Doctor_Diagnostic TINYINT,
39 date_Doctor_Diagnostic DATE,
40 id_Patient_Medical_Record BIGINT,
41 id_Doctor BIGINT,
42 PRIMARY KEY (id_Doctor_Diagnostic)
43);
44
45DROP TABLE IF EXISTS Patient_Medical_Record ;
46CREATE TABLE Patient_Medical_Record (
47 id_Patient_Medical_Record BIGINT AUTO_INCREMENT NOT NULL,
48 height_Patient_Medical_Record SMALLINT,
49 weight_Patient_Medical_Record SMALLINT,
50 socialSecurityNumber_Patient_Medical_Record VARCHAR,
51 id_Blood_Group BIGINT,
52 gps_coordonate_id_gps_coordonate BIGINT,
53 id_Address BIGINT,
54 social_details_id_social_details BIGINT,
55 doctor_id_doctor BIGINT,
56 PRIMARY KEY (id_Patient_Medical_Record)
57);
58
59DROP TABLE IF EXISTS Blood_Group ;
60CREATE TABLE Blood_Group (
61 id_Blood_Group TINYINT AUTO_INCREMENT NOT NULL,
62 name_Blood_Group VARCHAR,
63 PRIMARY KEY (id_Blood_Group)
64);
65
66DROP TABLE IF EXISTS Authentification ;
67CREATE TABLE Authentification (
68 id_Authentification BIGINT AUTO_INCREMENT NOT NULL,
69 username_Authentification VARCHAR,
70 mail_Authentification VARCHAR,
71 password_Authentification VARCHAR,
72 keystrokeSpeed_Authentification SMALLINT,
73 isAdmin_Authentification BOOLEAN,
74 isDoctor_Authentification BOOLEAN,
75 PRIMARY KEY (id_Authentification)
76);
77
78DROP TABLE IF EXISTS GPS_coordonate ;
79CREATE TABLE GPS_coordonate (
80 id_GPS_coordonate BIGINT AUTO_INCREMENT NOT NULL,
81 latitude_GPS_coordonate FLOAT,
82 longitude_GPS_coordonate FLOAT,
83 patient_medical_record_id_patient_medical_record BIGINT,
84 doctor_id_doctor BIGINT,
85 PRIMARY KEY (id_GPS_coordonate)
86);
87
88DROP TABLE IF EXISTS Address ;
89CREATE TABLE Address (
90 id_Address BIGINT AUTO_INCREMENT NOT NULL,
91 number_Address SMALLINT,
92 street_Address VARCHAR,
93 zipcode_Address VARCHAR,
94 PRIMARY KEY (id_Address)
95);
96
97DROP TABLE IF EXISTS Social_Details ;
98CREATE TABLE Social_Details (
99 id_Social_Details BIGINT AUTO_INCREMENT NOT NULL,
100 firstname_Social_Details VARCHAR,
101 lastname_Social_Details VARCHAR,
102 sex_Social_Details BOOLEAN,
103 birth_Social_Details DATE,
104 birthZipcode_Social_Details INT,
105 phoneNumber_Social_Details VARCHAR,
106 picture_Social_Details BLOB,
107 doctor_id_doctor BIGINT,
108 patient_medical_record_id_patient_medical_record BIGINT,
109 PRIMARY KEY (id_Social_Details)
110);
111
112DROP TABLE IF EXISTS Imagery ;
113CREATE TABLE Imagery (
114 id_Imagery BIGINT AUTO_INCREMENT NOT NULL,
115 name_Imagery VARCHAR,
116 image_Imagery BLOB,
117 id_Doctor_Diagnostic BIGINT,
118 PRIMARY KEY (id_Imagery)
119);
120
121DROP TABLE IF EXISTS Numerical ;
122CREATE TABLE Numerical (
123 id_Numerical BIGINT AUTO_INCREMENT NOT NULL,
124 name_Numerical VARCHAR,
125 value_Numerical FLOAT,
126 id_Doctor_Diagnostic BIGINT,
127 PRIMARY KEY (id_Numerical)
128);
129
130DROP TABLE IF EXISTS Appoitement ;
131CREATE TABLE Appoitement (
132 id_Appoitement BIGINT AUTO_INCREMENT NOT NULL,
133 date_Appoitement DATE,
134 id_Doctor BIGINT,
135 id_Patient_Medical_Record BIGINT,
136 address_id_address BIGINT,
137 gps_coordonate_id_gps_coordonate BIGINT,
138 PRIMARY KEY (id_Appoitement)
139);
140
141DROP TABLE IF EXISTS practise ;
142CREATE TABLE practise (
143 id_Doctor BIGINT AUTO_INCREMENT NOT NULL,
144 id_Profession_Type BIGINT NOT NULL,
145 PRIMARY KEY (id_Doctor, id_Profession_Type)
146);
147
148DROP TABLE IF EXISTS belong ;
149CREATE TABLE belong (
150 id_Medical_Site BIGINT AUTO_INCREMENT NOT NULL,
151 id_Doctor BIGINT NOT NULL,
152 PRIMARY KEY (id_Medical_Site, id_Doctor)
153);
154
155DROP TABLE IF EXISTS discribe ;
156CREATE TABLE discribe (id_Doctor_Diagnostic BIGINT AUTO_INCREMENT NOT NULL,
157id_Tag BIGINT NOT NULL,
158PRIMARY KEY (id_Doctor_Diagnostic,
159 id_Tag));
160
161DROP TABLE IF EXISTS login ;
162CREATE TABLE login (id_Patient_Medical_Record BIGINT AUTO_INCREMENT NOT NULL,
163 id_Authentification BIGINT NOT NULL,
164 id_Doctor BIGINT NOT NULL,
165 PRIMARY KEY (id_Patient_Medical_Record, id_Authentification, id_Doctor)
166);
167
168DROP TABLE IF EXISTS provide ;
169CREATE TABLE provide (
170 id_Medical_Site BIGINT AUTO_INCREMENT NOT NULL,
171 id_Tag BIGINT NOT NULL,
172 PRIMARY KEY (id_Medical_Site, id_Tag)
173);
174
175ALTER TABLE Medical_Site ADD CONSTRAINT FK_Medical_Site_id_Address FOREIGN KEY (id_Address) REFERENCES Address (id_Address);
176ALTER TABLE Doctor ADD CONSTRAINT FK_Doctor_gps_coordonate_id_gps_coordonate FOREIGN KEY (gps_coordonate_id_gps_coordonate) REFERENCES GPS_coordonate (id_GPS_coordonate);
177ALTER TABLE Doctor ADD CONSTRAINT FK_Doctor_social_details_id_social_details FOREIGN KEY (social_details_id_social_details) REFERENCES Social_Details (id_Social_Details);
178ALTER TABLE Doctor_Diagnostic ADD CONSTRAINT FK_Doctor_Diagnostic_id_Patient_Medical_Record FOREIGN KEY (id_Patient_Medical_Record) REFERENCES Patient_Medical_Record (id_Patient_Medical_Record);
179ALTER TABLE Doctor_Diagnostic ADD CONSTRAINT FK_Doctor_Diagnostic_id_Doctor FOREIGN KEY (id_Doctor) REFERENCES Doctor (id_Doctor);
180ALTER TABLE Patient_Medical_Record ADD CONSTRAINT FK_Patient_Medical_Record_id_Blood_Group FOREIGN KEY (id_Blood_Group) REFERENCES Blood_Group (id_Blood_Group);
181ALTER TABLE Patient_Medical_Record ADD CONSTRAINT FK_Patient_Medical_Record_gps_coordonate_id_gps_coordonate FOREIGN KEY (gps_coordonate_id_gps_coordonate) REFERENCES GPS_coordonate (id_GPS_coordonate);
182ALTER TABLE Patient_Medical_Record ADD CONSTRAINT FK_Patient_Medical_Record_id_Address FOREIGN KEY (id_Address) REFERENCES Address (id_Address);
183ALTER TABLE Patient_Medical_Record ADD CONSTRAINT FK_Patient_Medical_Record_social_details_id_social_details FOREIGN KEY (social_details_id_social_details) REFERENCES Social_Details (id_Social_Details);
184ALTER TABLE Patient_Medical_Record ADD CONSTRAINT FK_Patient_Medical_Record_doctor_id_doctor FOREIGN KEY (doctor_id_doctor) REFERENCES Doctor (id_Doctor);
185ALTER TABLE GPS_coordonate ADD CONSTRAINT FK_GPS_coordonate_patient_medical_record_id_patient_medical_record FOREIGN KEY (patient_medical_record_id_patient_medical_record) REFERENCES Patient_Medical_Record (id_Patient_Medical_Record);
186ALTER TABLE GPS_coordonate ADD CONSTRAINT FK_GPS_coordonate_doctor_id_doctor FOREIGN KEY (doctor_id_doctor) REFERENCES Doctor (id_Doctor);
187ALTER TABLE Social_Details ADD CONSTRAINT FK_Social_Details_doctor_id_doctor FOREIGN KEY (doctor_id_doctor) REFERENCES Doctor (id_Doctor);
188ALTER TABLE Social_Details ADD CONSTRAINT FK_Social_Details_patient_medical_record_id_patient_medical_record FOREIGN KEY (patient_medical_record_id_patient_medical_record) REFERENCES Patient_Medical_Record (id_Patient_Medical_Record);
189ALTER TABLE Imagery ADD CONSTRAINT FK_Imagery_id_Doctor_Diagnostic FOREIGN KEY (id_Doctor_Diagnostic) REFERENCES Doctor_Diagnostic (id_Doctor_Diagnostic);
190ALTER TABLE Numerical ADD CONSTRAINT FK_Numerical_id_Doctor_Diagnostic FOREIGN KEY (id_Doctor_Diagnostic) REFERENCES Doctor_Diagnostic (id_Doctor_Diagnostic);
191ALTER TABLE Appoitement ADD CONSTRAINT FK_Appoitement_id_Doctor FOREIGN KEY (id_Doctor) REFERENCES Doctor (id_Doctor);
192ALTER TABLE Appoitement ADD CONSTRAINT FK_Appoitement_id_Patient_Medical_Record FOREIGN KEY (id_Patient_Medical_Record) REFERENCES Patient_Medical_Record (id_Patient_Medical_Record);
193ALTER TABLE Appoitement ADD CONSTRAINT FK_Appoitement_address_id_address FOREIGN KEY (address_id_address) REFERENCES Address (id_Address);
194ALTER TABLE Appoitement ADD CONSTRAINT FK_Appoitement_gps_coordonate_id_gps_coordonate FOREIGN KEY (gps_coordonate_id_gps_coordonate) REFERENCES GPS_coordonate (id_GPS_coordonate);
195ALTER TABLE practise ADD CONSTRAINT FK_practise_id_Doctor FOREIGN KEY (id_Doctor) REFERENCES Doctor (id_Doctor);
196ALTER TABLE practise ADD CONSTRAINT FK_practise_id_Profession_Type FOREIGN KEY (id_Profession_Type) REFERENCES Profession_Type (id_Profession_Type);
197ALTER TABLE belong ADD CONSTRAINT FK_belong_id_Medical_Site FOREIGN KEY (id_Medical_Site) REFERENCES Medical_Site (id_Medical_Site);
198ALTER TABLE belong ADD CONSTRAINT FK_belong_id_Doctor FOREIGN KEY (id_Doctor) REFERENCES Doctor (id_Doctor);
199ALTER TABLE discribe ADD CONSTRAINT FK_discribe_id_Doctor_Diagnostic FOREIGN KEY (id_Doctor_Diagnostic) REFERENCES Doctor_Diagnostic (id_Doctor_Diagnostic);
200ALTER TABLE discribe ADD CONSTRAINT FK_discribe_id_Tag FOREIGN KEY (id_Tag) REFERENCES Tag (id_Tag);
201ALTER TABLE login ADD CONSTRAINT FK_login_id_Patient_Medical_Record FOREIGN KEY (id_Patient_Medical_Record) REFERENCES Patient_Medical_Record (id_Patient_Medical_Record);
202ALTER TABLE login ADD CONSTRAINT FK_login_id_Authentification FOREIGN KEY (id_Authentification) REFERENCES Authentification (id_Authentification);
203ALTER TABLE login ADD CONSTRAINT FK_login_id_Doctor FOREIGN KEY (id_Doctor) REFERENCES Doctor (id_Doctor);
204ALTER TABLE provide ADD CONSTRAINT FK_provide_id_Medical_Site FOREIGN KEY (id_Medical_Site) REFERENCES Medical_Site (id_Medical_Site);
205ALTER TABLE provide ADD CONSTRAINT FK_provide_id_Tag FOREIGN KEY (id_Tag) REFERENCES Tag (id_Tag);