· 8 years ago · Jul 28, 2018, 01:36 AM
1DROP DATABASE IF EXISTS emr;
2CREATE DATABASE IF NOT EXISTS emr;
3USE emr;
4
5
6-- DISEASE
7
8DROP TABLE IF EXISTS disease;
9CREATE TABLE disease (
10 disease_id int(11) PRIMARY KEY,
11 disease_name varchar(100) DEFAULT NULL
12) ;
13
14INSERT INTO disease VALUES
15(1,'Cold'),
16(2,'Flu'),
17(3,'Typhus'),
18(4, 'The plague'),
19(5, 'Flesh-eating bacteria');
20
21
22-- SPECIALTY
23DROP TABLE IF EXISTS specialty;
24CREATE TABLE specialty (
25 specialty_id INT PRIMARY KEY AUTO_INCREMENT,
26 specialty_name VARCHAR(20)
27);
28
29INSERT INTO specialty (specialty_name) VALUES
30('GENERAL PRACTITIONER'),
31('CARDIOLOGY'),
32('UROLOGY'),
33('DIAGNOSTICS'),
34('BRAIN SURGERY'),
35('INTERNAL MEDICINE');
36
37
38
39-- DOCTOR
40
41DROP TABLE IF EXISTS doctor;
42CREATE TABLE doctor (
43 doctor_id int(11) PRIMARY KEY,
44 doctor_name varchar(100) DEFAULT NULL,
45 specialty_id INT NULL,
46 CONSTRAINT fk_doctor_spec FOREIGN KEY (specialty_id) REFERENCES specialty (specialty_id)
47) ;
48
49INSERT INTO doctor VALUES
50(1,'Marcus', 5),
51(2,'House', 4),
52(3,'Howser', 1),
53(4,'Quinn', 1),
54 (5, 'Oz', 2),
55 (6, 'McCoy', null),
56 (7, 'Cooper', 1),
57 (8, 'Crusher', 4);
58
59-- PATIENT
60
61DROP TABLE IF EXISTS patient;
62CREATE TABLE patient (
63 patient_id int(11) PRIMARY KEY,
64 patient_name varchar(100) DEFAULT NULL,
65 sex char(1) DEFAULT NULL,
66 is_pregnant tinyint(1) DEFAULT FALSE,
67 flushot tinyint(1) DEFAULT NULL,
68 state char(2) DEFAULT NULL,
69 dob date DEFAULT NULL,
70 height float DEFAULT NULL,
71 weight float DEFAULT NULL,
72 pcp_id int(11) NOT NULL,
73
74 CONSTRAINT fk_patient_pcp FOREIGN KEY (pcp_id) REFERENCES doctor (doctor_id)
75
76);
77
78
79INSERT INTO patient VALUES
80(1,'Smith','M',0,1,'VT','1995-01-22',72,240,3),
81(2,'Jones','F',1,0,'MA','1964-12-29',71.5,160,3),
82(3,'Johnson','F', 0,1,'MA','1922-04-22',65,130,3),
83(4,'Phillips','F',0,1,'MA','1999-11-11',61,116,3),
84(5,'Lee','M', 0, 0,'CT','1970-10-08',73,240,3),
85(6,'Marcus','M', 0, 0,'NY','1966-02-01',70,150,4),
86(7,'Samuels','F', 0, 0,'CA','1969-08-11',68,130,4),
87(8,'van Dyke','M', 0, 0,'CA','1978-03-27',71,165,4);
88
89
90-- DIAGNOSIS
91
92DROP TABLE IF EXISTS diagnosis;
93CREATE TABLE diagnosis (
94 doctor_id int(11) DEFAULT NULL,
95 patient_id int(11) DEFAULT NULL,
96 disease_id int(11) DEFAULT NULL,
97 diagnosis_date date DEFAULT NULL,
98 CONSTRAINT fk_diag_doc FOREIGN KEY (doctor_id) REFERENCES doctor (doctor_id),
99 CONSTRAINT fk_diag_pat FOREIGN KEY (patient_id) REFERENCES patient (patient_id),
100 CONSTRAINT fk_diag_dis FOREIGN KEY (disease_id) REFERENCES disease (disease_id)
101) ;
102
103INSERT INTO diagnosis VALUES
104(1,1,1, '2016-01-11'),
105(1,1,3, '2016-11-22'),
106(3,1,1, '2017-05-15'),
107(1,2,2, '2017-03-19'),
108(3,2,2, '2016-02-23'),
109(1,2,1, '2016-06-04'),
110(2,3,1, '2016-09-12'),
111(2,3,2, '2017-04-15'),
112(2,4,1, '2016-03-30'),
113(4,4,3, '2016-08-31'),
114(2,5,2, '2016-01-08'),
115(2,6,2, '2017-11-01'),
116(3,7,2, '2016-06-19');
117
118
119
120-- RECOMMENDATIONS
121
122DROP TABLE IF EXISTS recommendation;
123CREATE TABLE recommendation (
124 patient_id int(11) NOT NULL,
125 message varchar(255) NOT NULL,
126 CONSTRAINT fk_recommendations FOREIGN KEY (patient_id) REFERENCES patient (patient_id)
127) ;
128
129
130insert into recommendation values
131(1, 'Recomend getting a flushot'),
132(2, 'Recomend getting a flushot'),
133(3, 'Recomend getting a flushot'),
134(4, 'Recomend getting a flushot'),
135(5, 'Recomend getting a flushot'),
136(6, 'Recomend getting a flushot'),
137(7, 'Recomend getting a flushot'),
138(8, 'Recomend getting a flushot');
139
140
141show tables;