cfVYWknC

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	nas			VARCHAR(9)	NOT NULL UNIQUE,
13	PRIMARY KEY (patientNo)
14);
15
16CREATE TABLE IF NOT EXISTS Doctor (
17	doctorID 	VARCHAR(10) NOT NULL,
18	doctorName	VARCHAR(50) NOT NULL,
19	address		VARCHAR(50) NOT NULL,
20	phoneNo		VARCHAR(10) NOT NULL,
21	dob			VARCHAR(8)	NOT NULL,
22	salary		NUMERIC(8,2)NOT NULL CHECK (salary > 100000),
23	PRIMARY KEY (doctorID)
24);
25
26CREATE TABLE IF NOT EXISTS Medical (
27	doctorID 	VARCHAR(10) NOT NULL,
28	overtimeRate	NUMERIC(8,2)NOT NULL,
29	PRIMARY KEY (doctorID),
30	FOREIGN KEY (doctorID) REFERENCES Doctor(doctorID)
31);
32
33CREATE TABLE IF NOT EXISTS Specialist (
34	doctorID 	VARCHAR(10) NOT NULL,
35	fieldArea	VARCHAR(20)	NOT NULL,
36	PRIMARY KEY (doctorID),
37	FOREIGN KEY (doctorID) REFERENCES Doctor(doctorID)
38);
39
40CREATE TABLE IF NOT EXISTS Appointment (
41	apptNo		VARCHAR(10)	NOT NULL,
42	apptDate	DATE		NOT NULL,
43	apptTime	TIME		NOT NULL,
44	patientNo	VARCHAR(10)	NOT NULL,
45	doctorID 	VARCHAR(10) NOT NULL,
46	PRIMARY KEY (apptNo, patientNo, doctorID),
47	FOREIGN KEY (patientNo) REFERENCES Patient(patientNo),
48	FOREIGN KEY (doctorID) 	REFERENCES Doctor(doctorID)
49);
50
51CREATE TABLE IF NOT EXISTS Bill (
52	billNo 		VARCHAR(10)	NOT NULL,
53	total		NUMERIC(8,2)NOT NULL,
54	doctorID	VARCHAR(10)	NOT NULL,
55	PRIMARY KEY (billNo, doctorID),
56	FOREIGN KEY (doctorID) 	REFERENCES Doctor(doctorID)
57		ON DELETE RESTRICT
58);
59
60CREATE TABLE IF NOT EXISTS Payment (
61	paymentNo	VARCHAR(10)	NOT NULL,
62	details		VARCHAR(50)	NOT NULL,
63	payMethod	VARCHAR(10)	NOT NULL,
64	patientNo	VARCHAR(10)	NOT NULL,
65	PRIMARY KEY (paymentNo, patientNo),
66	FOREIGN KEY (patientNo) REFERENCES Patient(patientNo)
67);
68
69CREATE TABLE IF NOT EXISTS BillPayment (
70	billNo 		VARCHAR(10)	NOT NULL,
71	doctorID	VARCHAR(10)	NOT NULL,
72	paymentNo	VARCHAR(10)	NOT NULL,
73	patientNo	VARCHAR(10)	NOT NULL,
74	PRIMARY KEY (billNo, doctorID, paymentNo, patientNo),
75	FOREIGN KEY (billNo, doctorID) REFERENCES Bill(billNo, doctorID),
76	FOREIGN KEY (paymentNo, patientNo) REFERENCES Payment(paymentNo, patientNo)
77);