Clinic SQL Script
CREATE TABLE patient (
code varchar PRIMARY KEY,
name varchar,
address varchar
);
CREATE TABLE physician (
number varchar PRIMARY KEY,
name varchar,
address varchar
);
CREATE TABLE condition (
ref integer PRIMARY KEY,
designation varchar
);
CREATE TABLE appointment (
num integer PRIMARY KEY,
date date,
room integer,
number varchar REFERENCES physician,
code varchar REFERENCES patient
);
CREATE TABLE diagnosed (
ref integer REFERENCES condition,
num integer REFERENCES appointment,
PRIMARY KEY (ref, num)
);
INSERT INTO physician VALUES ('99030', 'Luca Moore', '16 Victoria Road');
INSERT INTO physician VALUES ('12312', 'Tommy Cooke', '47 Bootham Terrace');
INSERT INTO physician VALUES ('43642', 'Joshua Bailey', '95 Nottingham Rd');
INSERT INTO patient VALUES ('12345', 'Xander Gibbs', '45 Petworth Rd');
INSERT INTO patient VALUES ('45643', 'Tatiana Barber', '16 Vicar Lane');
INSERT INTO patient VALUES ('23613', 'Amelie Harrison', '32 Thompsons Lane');
INSERT INTO patient VALUES ('74927', 'Kathryn Savege', '67 Cosworth Rd');
INSERT INTO appointment VALUES (456, '2007-01-01', 190, '99030', '12345');
INSERT INTO appointment VALUES (457, '2007-01-01', 187, '99030', '12345');
INSERT INTO appointment VALUES (458, '2007-01-01', 190, '12312', '23613');
INSERT INTO appointment VALUES (459, '2007-01-01', 190, '99030', '45643');
INSERT INTO appointment VALUES (460, '2007-01-01', 187, '99030', '45643');
INSERT INTO appointment VALUES (461, '2007-01-01', 187, '12312', '12345');
INSERT INTO appointment VALUES (462, '2007-01-01', 204, '12312', '23613');
INSERT INTO appointment VALUES (463, '2007-01-01', 187, '99030', '45643');
INSERT INTO appointment VALUES (464, '2007-01-02', 190, '12312', '12345');
INSERT INTO appointment VALUES (465, '2007-01-02', 190, '12312', '45643');
INSERT INTO appointment VALUES (466, '2007-01-03', 190, '43642', '12345');
INSERT INTO appointment VALUES (467, '2007-01-03', 190, '99030', '12345');
INSERT INTO appointment VALUES (468, '2007-01-03', 190, '43642', '74927');
INSERT INTO condition VALUES (1, 'Wizard Plague');
INSERT INTO condition VALUES (2, 'Arachnid Hepatitis');
INSERT INTO condition VALUES (3, 'Anxious Sleepwalking');
INSERT INTO diagnosed (num, ref) VALUES (456, 1);
INSERT INTO diagnosed (num, ref) VALUES (456, 2);
INSERT INTO diagnosed (num, ref) VALUES (457, 2);
INSERT INTO diagnosed (num, ref) VALUES (457, 3);
INSERT INTO diagnosed (num, ref) VALUES (458, 3);
INSERT INTO diagnosed (num, ref) VALUES (459, 2);
INSERT INTO diagnosed (num, ref) VALUES (460, 2);
INSERT INTO diagnosed (num, ref) VALUES (460, 3);
INSERT INTO diagnosed (num, ref) VALUES (460, 1);
INSERT INTO diagnosed (num, ref) VALUES (461, 2);
INSERT INTO diagnosed (num, ref) VALUES (462, 3);
INSERT INTO diagnosed (num, ref) VALUES (463, 1);
INSERT INTO diagnosed (num, ref) VALUES (464, 2);
INSERT INTO diagnosed (num, ref) VALUES (464, 3);
INSERT INTO diagnosed (num, ref) VALUES (465, 1);
INSERT INTO diagnosed (num, ref) VALUES (466, 3);
INSERT INTO diagnosed (num, ref) VALUES (467, 2);