s68AXVJQ

1SET search_path = medi_schema;
2
3DROP SCHEMA IF EXISTS medi_schema CASCADE;
4CREATE SCHEMA medi_schema;
5
6CREATE TABLE IF NOT EXISTS Patient (
7	patientNo 	VARCHAR(10) NOT NULL,
8	patientName	VARCHAR(50) NOT NULL,
9	address		VARCHAR(50) NOT NULL,
10	phoneNo		VARCHAR(10) NOT NULL,
11	dob			VARCHAR(8)	NOT NULL,
12	PRIMARY KEY (patientNo)
13);
14
15CREATE TABLE IF NOT EXISTS Doctor (
16	doctorID 	VARCHAR(10) NOT NULL,
17	doctorName	VARCHAR(50) NOT NULL,
18	address		VARCHAR(50) NOT NULL,
19	phoneNo		VARCHAR(10) NOT NULL,
20	dob			VARCHAR(8)	NOT NULL,
21	salary		NUMERIC(8,2)NOT NULL,
22	PRIMARY KEY (doctorID)
23);
24
25CREATE TABLE IF NOT EXISTS Medical (
26	doctorID 	VARCHAR(10) NOT NULL,
27	PRIMARY KEY (doctorID),
28	FOREIGN KEY (doctorID) REFERENCES Doctor(doctorID)
29);
30
31CREATE TABLE IF NOT EXISTS Specialist (
32	doctorID 	VARCHAR(10) NOT NULL,
33	PRIMARY KEY (doctorID),
34	FOREIGN KEY (doctorID) REFERENCES Doctor(doctorID)
35);
36
37CREATE TABLE IF NOT EXISTS Appointment (
38	apptNo		VARCHAR(10)	NOT NULL,
39	apptDate	DATE		NOT NULL,
40	apptTime	TIME		NOT NULL,
41	patientNo	VARCHAR(10)	NOT NULL,
42	doctorID 	VARCHAR(10) NOT NULL,
43	PRIMARY KEY (apptNo, patientID, doctorID),
44	FOREIGN KEY (patientNo) REFERENCES Patient(doctorID)
45	FOREIGN KEY (doctorID) REFERENCES Doctor(doctorID)
46);