-
Notifications
You must be signed in to change notification settings - Fork 0
/
create_db.sql
153 lines (119 loc) · 3.34 KB
/
create_db.sql
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
145
146
147
148
149
150
151
152
153
--
-- Name: Donors; Type: TABLE;
--
DROP TABLE IF EXISTS Donors;
CREATE TABLE Donors(
donor_id INT PRIMARY KEY NOT NULL,
name VARCHAR(50) NOT NULL,
blood_type VARCHAR(50) NOT NULL,
blood_id INT NOT NULL,
contact_info VARCHAR(50)
);
--
-- Name: Drives; Type: TABLE;
--
DROP TABLE IF EXISTS Drives;
CREATE TABLE Drives(
drive_id INT PRIMARY KEY NOT NULL,
name VARCHAR(50) NOT NULL,
location VARCHAR(50) NOT NULL,
date DATE NOT NULL
);
--
-- Name: Labs; Type: TABLE;
--
DROP TABLE IF EXISTS Labs;
CREATE TABLE Labs(
lab_id INT PRIMARY KEY NOT NULL,
name VARCHAR(50) NOT NULL,
location VARCHAR(50) NOT NULL,
tests_passed BOOLEAN NOT NULL
);
--
-- Name: Hospitals; Type: TABLE;
--
DROP TABLE IF EXISTS Hospitals;
CREATE TABLE Hospitals(
hospital_id INT PRIMARY KEY NOT NULL,
name VARCHAR(50) NOT NULL,
location VARCHAR(50) NOT NULL
);
--
-- Name: Banks; Type: TABLE;
--
DROP TABLE IF EXISTS Banks;
CREATE TABLE Banks(
bank_id INT PRIMARY KEY NOT NULL,
name VARCHAR(50) NOT NULL,
location VARCHAR(50) NOT NULL
);
--
-- Name: Recipients; Type: TABLE;
--
DROP TABLE IF EXISTS Recipients;
CREATE TABLE Recipients(
recipient_id INT PRIMARY KEY NOT NULL,
name VARCHAR(50) NOT NULL,
recipient_blood_type VARCHAR(50) NOT NULL,
contact_info VARCHAR(50)
);
--
-- Name: donate; Type: TABLE;
--
DROP TABLE IF EXISTS donate;
CREATE TABLE donate(
donation_id INT PRIMARY KEY NOT NULL,
donor_id INT NOT NULL,
blood_id INT NOT NULL,
drive_id INT NOT NULL,
FOREIGN KEY(donor_id) REFERENCES Donors(donor_id),
FOREIGN KEY(drive_id) REFERENCES Drives(drive_id)
);
--
-- Name: test; Type: TABLE;
--
DROP TABLE IF EXISTS test;
CREATE TABLE test(
test_id INT PRIMARY KEY NOT NULL,
drive_id INT NOT NULL,
lab_id INT NOT NULL,
blood_id INT NOT NULL,
FOREIGN KEY(lab_id) REFERENCES Labs(lab_id),
FOREIGN KEY(drive_id) REFERENCES Drives(drive_id)
);
--
-- Name: store; Type: TABLE;
--
DROP TABLE IF EXISTS store;
CREATE TABLE store(
storage_id INT PRIMARY KEY NOT NULL,
bank_id INT NOT NULL,
lab_id INT NOT NULL,
blood_id INT NOT NULL,
FOREIGN KEY(lab_id) REFERENCES Labs(lab_id),
FOREIGN KEY(bank_id) REFERENCES Banks(bank_id)
);
--
-- Name: distribute; Type: TABLE;
--
DROP TABLE IF EXISTS distribute;
CREATE TABLE distribute(
distribution_id INT PRIMARY KEY NOT NULL,
bank_id INT NOT NULL,
hospital_id INT NOT NULL,
blood_id INT NOT NULL,
FOREIGN KEY(hospital_id) REFERENCES Hospitals(hospital_id),
FOREIGN KEY(bank_id) REFERENCES Banks(bank_id)
);
--
-- Name: transfusion; Type: TABLE;
--
DROP TABLE IF EXISTS transfusion;
CREATE TABLE transfusion(
distribution_id INT PRIMARY KEY NOT NULL,
recipient_id INT NOT NULL,
hospital_id INT NOT NULL,
blood_id INT NOT NULL,
FOREIGN KEY(hospital_id) REFERENCES Hospitals(hospital_id),
FOREIGN KEY(recipient_id) REFERENCES Recipients(recipient_id)
);