· 5 years ago · Nov 05, 2020, 05:46 PM
1--MAKING THE TABLE FOR PHYSICIAN RELATION--
2create table if not exists physician(
3 physicianID serial generated always as identity,
4 physician_first_name varchar(255) not null,
5 physician_last_name varchar(255) not null,
6 physician_phone_number serial not null,
7 primary key(physicianID)
8);
9
10--MAKING THE TABLE FOR PATIENT RELATION--
11create table if not exists patient(
12 patient_first_name varchar(255) not null,
13 patient_last_name varchar(255) not null,
14 patient_phone_number int not null,
15 patientID int generated always as identity not null,
16 primary key(patientID)
17);
18
19--MAKING THE TABLE FOR TREATMENT RELATION--
20create table if not exists treatement(
21 description varchar(255) not null,
22 patient_treatment_code serial generated always as identity,
23 primary key(patient_treatment_code)
24
25);
26
27create table if not exists patient_treatement_relationship(
28 patient_visit date not null,
29 patient_copayment int not null,
30 physicianID serial,
31 patientID int,
32 patient_treatment_code int,
33
34--MAKING THE FOREING KEY FOR THE PHYSICIAN--
35 constraint fk_physician
36 foreign key(physicianID)
37 references physician(physicianID),
38
39--MAKING THE FOREING KEY FOR THE PATIENT--
40 constraint fk_patient
41 foreign key(patientID)
42 references patient(patientID),
43
44--MAKING THE FOREING KEY FOR THE TREATMENT--
45 constraint fk_treatement
46 foreign key(patient_treatement_code)
47 references patient(patient_treatement_code)
48);
49insert into physician(physician_first_name,
50 physician_last_name,
51 physician_phone_number,
52 physicianID)
53values('Diana', 'Paola', 347-344-8359, 333-794-0001);
54--insert into physician(physician_last_name)
55--values('Paola');
56--insert into physician(physician_phone_number)
57--values(347-344-8359);
58--insert into physician (physicianID)
59--values(333-794-0001);
60--update physician set physician_first_name = 'Diana';
61--update physician set physician_last_name = 'Paola';
62--update physician set physician_phone_number = 347-344-8359;
63--update physician set physicianID = 333-794-0001;
64
65--select * from physician;
66