· 7 years ago · Dec 17, 2018, 02:34 PM
1#------------------------------------------------------------
2# Script MySQL.
3#------------------------------------------------------------
4
5
6#------------------------------------------------------------
7# Table: specialities
8#------------------------------------------------------------
9
10CREATE TABLE IF NOT EXISTS specialities(
11 id Int NOT NULL AUTO_INCREMENT,
12 name Varchar (50) NOT NULL
13 ,CONSTRAINT specialities_PK PRIMARY KEY (id)
14)ENGINE=InnoDB;
15
16
17#------------------------------------------------------------
18# Table: doctors
19#------------------------------------------------------------
20
21CREATE TABLE IF NOT EXISTS doctors(
22 id Int NOT NULL AUTO_INCREMENT,
23 lastName Varchar (50) NOT NULL ,
24 firstName Varchar (50) NOT NULL ,
25 email Varchar (255) NOT NULL ,
26 id_specialities Int NOT NULL
27 ,CONSTRAINT doctors_PK PRIMARY KEY (id)
28
29 ,CONSTRAINT doctors_specialities_FK FOREIGN KEY (id_specialities) REFERENCES specialities(id)
30)ENGINE=InnoDB;
31
32
33#------------------------------------------------------------
34# Table: patients
35#------------------------------------------------------------
36
37CREATE TABLE IF NOT EXISTS patients(
38 id Int NOT NULL AUTO_INCREMENT,
39 lastName Varchar (50) NOT NULL ,
40 firstName Varchar (50) NOT NULL ,
41 birthDay Int NOT NULL ,
42 id_doctors Int NOT NULL
43 ,CONSTRAINT patients_PK PRIMARY KEY (id)
44
45 ,CONSTRAINT patients_doctors_FK FOREIGN KEY (id_doctors) REFERENCES doctors(id)
46)ENGINE=InnoDB;
47
48
49#------------------------------------------------------------
50# Table: meetings
51#------------------------------------------------------------
52
53CREATE TABLE IF NOT EXISTS meetings(
54 id Int NOT NULL AUTO_INCREMENT,
55 meetingDate Int NOT NULL ,
56 id_patients Int NOT NULL ,
57 id_doctors Int NOT NULL
58 ,CONSTRAINT meetings_PK PRIMARY KEY (id)
59
60 ,CONSTRAINT meetings_patients_FK FOREIGN KEY (id_patients) REFERENCES patients(id)
61 ,CONSTRAINT meetings_doctors0_FK FOREIGN KEY (id_doctors) REFERENCES doctors(id)
62)ENGINE=InnoDB;
63
64INSERT INTO specialities (name) VALUES
65('gastro-entérologie'),
66('endocrinologie'),
67('chirurgie maxillo-faciale');
68
69INSERT INTO doctors (lastName, firstName, email, id_specialities) VALUES
70('Quennel', 'Benoit', 'benoitQ@docteurdeouf.fr', RAND()*(3-1)+1),
71('Royer', 'Mayhew', 'mayhewR@docteurdeouf.fr', RAND()*(3-1)+1),
72('Descoteaux', 'Harriette', 'harrietteD@docteurdeouf.fr', RAND()*(3-1)+1),
73('Mouet', 'William', 'williamM@docteurdeouf.fr', RAND()*(3-1)+1),
74('Bélanger', 'Ferrau', 'FerrauB@docteurdeouf.fr', RAND()*(3-1)+1);
75
76INSERT INTO patients (lastName, firstName, birthDay, id_doctors) VALUES
77('Pierre', 'Jean', 816613314, RAND()*(5-1)+1),
78('Rose', 'Lise', 816613314, RAND()*(5-1)+1),
79('Neymar', 'Jean', 942843714, RAND()*(5-1)+1),
80('Bruelle', 'Patrick', 929624514, RAND()*(5-1)+1),
81('Lora', 'Lisy', 732373314, RAND()*(5-1)+1),
82('Jason', 'Annabelle', 742914114, RAND()*(5-1)+1),
83('Brille', 'Alexis', 50763714, RAND()*(5-1)+1),
84('Praise', 'Alexandre', 429454914, RAND()*(5-1)+1),
85('Farhanah', 'Maliha', 531838914, RAND()*(5-1)+1),
86('Sabih', 'Fakhir', 515941314, RAND()*(5-1)+1),
87('France', 'Patenaude', 799938114, RAND()*(5-1)+1),
88('Diane', 'Chatigny', 831560514, RAND()*(5-1)+1),
89('Létourneau', 'Julie', 894632514, RAND()*(5-1)+1),
90('Plante', 'Archard', 913122114, RAND()*(5-1)+1),
91('Rodrigue', 'Natalie', 802620114, RAND()*(5-1)+1),
92('Plourde', 'Pierrette', 531838914, RAND()*(5-1)+1),
93('Boulé', 'Jérôme', 515941314, RAND()*(5-1)+1),
94('Arpin', 'Aymon', 799938114, RAND()*(5-1)+1),
95('Vaillancourt', 'Curtis', 799938114, RAND()*(5-1)+1),
96('LaGrande', 'Yvon', 515941314, RAND()*(5-1)+1);
97
98INSERT INTO meetings (meetingDate, id_patients, id_doctors) VALUES
99(1545746400, RAND()*(20-1)+1, RAND()*(5-1)+1),
100(1574690400, RAND()*(20-1)+1, RAND()*(5-1)+1),
101(1545141600, RAND()*(20-1)+1, RAND()*(5-1)+1),
102(1550476800, RAND()*(20-1)+1, RAND()*(5-1)+1),
103(1550563200, RAND()*(20-1)+1, RAND()*(5-1)+1),
104(1550584800, RAND()*(20-1)+1, RAND()*(5-1)+1),
105(1550599200, RAND()*(20-1)+1, RAND()*(5-1)+1),
106(1550649600, RAND()*(20-1)+1, RAND()*(5-1)+1),
107(1581148800, RAND()*(20-1)+1, RAND()*(5-1)+1),
108(1392537600, RAND()*(20-1)+1, RAND()*(5-1)+1),
109(1391760000, RAND()*(20-1)+1, RAND()*(5-1)+1),
110(1455609600, RAND()*(20-1)+1, RAND()*(5-1)+1),
111(1531382400, RAND()*(20-1)+1, RAND()*(5-1)+1),
112(1531821600, RAND()*(20-1)+1, RAND()*(5-1)+1),
113(1531836000, RAND()*(20-1)+1, RAND()*(5-1)+1),
114(1555509600, RAND()*(20-1)+1, RAND()*(5-1)+1),
115(1555682400, RAND()*(20-1)+1, RAND()*(5-1)+1),
116(1574172000, RAND()*(20-1)+1, RAND()*(5-1)+1),
117(1575986400, RAND()*(20-1)+1, RAND()*(5-1)+1),
118(1568469600, RAND()*(20-1)+1, RAND()*(5-1)+1),
119(1594044000, RAND()*(20-1)+1, RAND()*(5-1)+1),
120(1601991000, RAND()*(20-1)+1, RAND()*(5-1)+1),
121(1539790200, RAND()*(20-1)+1, RAND()*(5-1)+1),
122(1545057000, RAND()*(20-1)+1, RAND()*(5-1)+1),
123(1529245800, RAND()*(20-1)+1, RAND()*(5-1)+1),
124(1528381800, RAND()*(20-1)+1, RAND()*(5-1)+1),
125(1493044200, RAND()*(20-1)+1, RAND()*(5-1)+1),
126(1500388200, RAND()*(20-1)+1, RAND()*(5-1)+1),
127(1560868200, RAND()*(20-1)+1, RAND()*(5-1)+1),
128(1521124200, RAND()*(20-1)+1, RAND()*(5-1)+1);