Asclepius/dbscript.sql at main · NSP909/Asclepius · 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
-- SCHEMA: public

-- DROP SCHEMA IF EXISTS public CASCADE;

CREATE SCHEMA IF NOT EXISTS public AUTHORIZATION postgres;

COMMENT ON SCHEMA public IS 'standard public schema';

GRANT ALL ON SCHEMA public TO PUBLIC;

GRANT ALL ON SCHEMA public TO postgres;

CREATE TABLE usertable (
    user_id integer GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    username VARCHAR (32) UNIQUE NOT NULL,
    user_type SMALLINT NOT NULL
);

CREATE TABLE userpwd (
    pwd_id integer GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    user_id integer NOT NULL,
    pwd VARCHAR (32) NOT NULL,
    FOREIGN KEY (user_id) REFERENCES usertable (user_id)
);

CREATE TABLE userinfo (
    info_id integer GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    user_id integer NOT NULL,
    fullname VARCHAR (32) NOT NULL,
    user_height integer NOT NULL,
    user_weight integer NOT NULL,
    race VARCHAR (32) NOT NULL,
    date_of_birth DATE NOT NULL,
    ethnicity VARCHAR(32) NOT NULL,
    sex VARCHAR(32) NOT NULL,
    gender VARCHAR(32) NOT NULL,
    FOREIGN KEY (user_id) REFERENCES usertable (user_id)
);

CREATE TABLE notes (
    note_id integer GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    user_id integer NOT NULL,
    note VARCHAR (255) NOT NULL,
    note_date DATE NOT NULL,
    history_user_id integer NOT NULL,
    FOREIGN KEY (user_id) REFERENCES usertable (user_id)
);

CREATE TABLE medicine (
    med_id integer GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    user_id integer NOT NULL,
    med_name VARCHAR (32) NOT NULL,
    med_dosage VARCHAR (32) NOT NULL,
    med_frequency VARCHAR (32) NOT NULL,
    med_date DATE NOT NULL,
    history_user_id integer NOT NULL,
    FOREIGN KEY (user_id) REFERENCES usertable (user_id)
);

CREATE TABLE vitals (
    vital_id integer GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    user_id integer NOT NULL,
    vital_name VARCHAR (32) NOT NULL,
    vital_value VARCHAR (32) NOT NULL,
    vital_date DATE NOT NULL,
    history_user_id integer NOT NULL,
    FOREIGN KEY (user_id) REFERENCES usertable (user_id)
);

CREATE TABLE vaccine (
    vac_id integer GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    user_id integer NOT NULL,
    vac_name VARCHAR (32) NOT NULL,
    vac_date DATE NOT NULL,
    history_user_id integer NOT NULL,
    FOREIGN KEY (user_id) REFERENCES usertable (user_id)
);

CREATE TABLE lab_result (
    lab_id integer GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    user_id integer NOT NULL,
    lab_result VARCHAR (2048) NOT NULL,
    lab_date DATE NOT NULL,
    history_user_id integer NOT NULL,
    FOREIGN KEY (user_id) REFERENCES usertable (user_id)
);

CREATE TABLE surgeries (
    surgery_id integer GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    user_id integer NOT NULL,
    surgery VARCHAR (255) NOT NULL,
    surgery_date DATE NOT NULL,
    history_user_id integer NOT NULL,
    FOREIGN KEY (user_id) REFERENCES usertable (user_id)
);

CREATE TABLE emergencies (
    emergency_id integer GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    user_id integer NOT NULL,
    emergency_name VARCHAR (255) NOT NULL,
    emergency_date DATE NOT NULL,
    history_user_id integer NOT NULL,
    FOREIGN KEY (user_id) REFERENCES usertable (user_id)
);

CREATE TABLE diagnosis (
    diag_id integer GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    user_id integer NOT NULL,
    diagnosis VARCHAR (255) NOT NULL,
    diag_date DATE NOT NULL,
    history_user_id integer NOT NULL,
    FOREIGN KEY (user_id) REFERENCES usertable (user_id)
);

CREATE TABLE symptoms (
    symptom_id integer GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    user_id integer NOT NULL,
    diag_id integer NOT NULL,
    symptom VARCHAR (255) NOT NULL,
    symptom_date DATE NOT NULL,
    history_user_id integer NOT NULL,
    FOREIGN KEY (diag_id) REFERENCES diagnosis (diag_id),
    FOREIGN KEY (user_id) REFERENCES usertable (user_id)
);