DataBase_Project_Team_3/Postgres_code.txt at main · SeifAlsayed02/DataBase_Project_Team_3 · GitHub

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144

CREATE TABLE IF NOT EXISTS Departement (
    Code VARCHAR(50) UNIQUE PRIMARY KEY,
    Name VARCHAR(255) UNIQUE NOT NULL
);


CREATE TABLE IF NOT EXISTS Admins (
    Code VARCHAR(50) PRIMARY KEY,
    Username VARCHAR(50) UNIQUE NOT NULL,
    Email VARCHAR(100) UNIQUE NOT NULL,
    Password VARCHAR(255) NOT NULL
);


CREATE TABLE IF NOT EXISTS Doctors (
    ID SERIAL PRIMARY KEY,
    Age INT,
    Name VARCHAR(255) NOT NULL,
    Username VARCHAR(50) UNIQUE NOT NULL,
    Gender VARCHAR(10) NOT NULL,
    Email VARCHAR(100) UNIQUE NOT NULL,
    Password VARCHAR(255) NOT NULL
);


CREATE TABLE IF NOT EXISTS Patients (
    ID SERIAL PRIMARY KEY,
    Age INT,
    Name VARCHAR(255) NOT NULL,
    Username VARCHAR(50) UNIQUE NOT NULL,
    Gender VARCHAR(10) NOT NULL,
    Email VARCHAR(100) UNIQUE NOT NULL,
    Password VARCHAR(255) NOT NULL
);


CREATE TABLE IF NOT EXISTS Treats (
    DoctorID INT REFERENCES Doctors(ID),
    PatientID INT REFERENCES Patients(ID),
    PRIMARY KEY (DoctorID, PatientID)
);


CREATE TABLE IF NOT EXISTS Belongs_to (
    PatientID INT REFERENCES Patients(ID),
    DeptCode VARCHAR(50) REFERENCES Departement(Code),
    Dept_name VARCHAR(255) NOT NULL,
    PRIMARY KEY (PatientID, DeptCode)
);


CREATE TABLE IF NOT EXISTS works_for (
    DoctorID INT REFERENCES Doctors(ID),
    DeptCode VARCHAR(50) REFERENCES Departement(Code),
    Dept_name VARCHAR(255) NOT NULL,
    PRIMARY KEY (DoctorID, DeptCode)
);


CREATE TABLE IF NOT EXISTS Access (
    AccessID SERIAL PRIMARY KEY,
    AdminCode VARCHAR(50) REFERENCES Admins(Code),
    DeptCode VARCHAR(50) REFERENCES Departement(Code),
    DoctorID INT REFERENCES Doctors(ID),
    PatientID INT REFERENCES Patients(ID),
    AccessDate DATE,
    AccessTime TIME
);


CREATE TABLE IF NOT EXISTS Scans (
    ScanID SERIAL PRIMARY KEY,
    DepartementCode VARCHAR(50) REFERENCES Departement(Code),
    PatientID INT REFERENCES Patients(ID),
    DoctorID INT REFERENCES Doctors(ID),
    ScanDate DATE,
    ScanTime TIME
);


CREATE TABLE IF NOT EXISTS ScanFiles (
    ScanID INT REFERENCES Scans(ScanID),
    File TEXT,
    PRIMARY KEY (ScanID, File)
);


CREATE TABLE IF NOT EXISTS Appointments (
    AppointmentID SERIAL PRIMARY KEY,
    DepartementCode VARCHAR(50) REFERENCES Departement(Code),
    PatientID INT REFERENCES Patients(ID),
    DoctorID INT REFERENCES Doctors(ID),
    AppointmentDate DATE,
    AppointmentTime TIME
);


CREATE TABLE IF NOT EXISTS Prescriptions (
    PrescriptionID SERIAL PRIMARY KEY,
    DepartementCode VARCHAR(50) REFERENCES Departement(Code),
    PatientID INT REFERENCES Patients(ID),
    DoctorID INT REFERENCES Doctors(ID),
    PrescriptionDate DATE,
    PrescriptionTime TIME
);


CREATE TABLE IF NOT EXISTS PrescriptionFiles (
    PrescriptionID INT REFERENCES Prescriptions(PrescriptionID),
    File TEXT,
    PRIMARY KEY (PrescriptionID, File)
);


CREATE TABLE IF NOT EXISTS Scans_belongs_to (
    ScanID INT REFERENCES Scans(ScanID),
    PatientID INT REFERENCES Patients(ID),
    DoctorID INT REFERENCES Doctors(ID),
    DepartementCode VARCHAR(50) REFERENCES Departement(Code),
    PRIMARY KEY (ScanID, PatientID, DoctorID, DepartementCode)
);


CREATE TABLE IF NOT EXISTS Appointments_belongs_to (
    AppointmentID INT REFERENCES Appointments(AppointmentID),
    PatientID INT REFERENCES Patients(ID),
    DoctorID INT REFERENCES Doctors(ID),
    DepartementCode VARCHAR(50) REFERENCES Departement(Code),
    PRIMARY KEY (AppointmentID, PatientID, DoctorID, DepartementCode)
);


CREATE TABLE IF NOT EXISTS Prescriptions_belongs_to (
    PrescriptionID INT REFERENCES Prescriptions(PrescriptionID),
    PatientID INT REFERENCES Patients(ID),
    DoctorID INT REFERENCES Doctors(ID),
    DepartementCode VARCHAR(50) REFERENCES Departement(Code),
    PRIMARY KEY (PrescriptionID, PatientID, DoctorID, DepartementCode)
);