MedTest-Lab-Platform/database.sql at main · YEFeng614/MedTest-Lab-Platform · 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
CREATE DATABASE MedTest;
USE MedTest;

CREATE TABLE Patients (
    PATIENT_ID INT PRIMARY KEY AUTO_INCREMENT,
    NAME VARCHAR(255) NOT NULL,
    BIRTHDATE VARCHAR(255) NOT NULL,
    CONTACT VARCHAR(255),
    INSURANCE_DETAILS VARCHAR(255),
    PASSWORD VARBINARY(255) NOT NULL,
    SALT VARCHAR(255) NOT NULL,
    IV VARCHAR(255) NOT NULL,
    SESSION_KEY VARCHAR(255)
);

CREATE TABLE TestsCatalog (
    TEST_CODE INT PRIMARY KEY AUTO_INCREMENT,
    NAME VARCHAR(255) NOT NULL,
    DESCRIPTION TEXT,
    COST DECIMAL(10,2) NOT NULL
);
CREATE TABLE Staff (
    STAFF_ID INT PRIMARY KEY AUTO_INCREMENT,
    NAME VARCHAR(255) NOT NULL,
    ROLE ENUM('Lab Staff', 'Secretary') NOT NULL,
    CONTACT VARCHAR(255),
    PASSWORD VARBINARY(255) NOT NULL,
    SALT VARCHAR(255) NOT NULL,
    IV BINARY(255) NOT NULL,
    SESSION_KEY VARCHAR(255)
);

CREATE TABLE Appointments (
    APPOINTMENT_ID INT PRIMARY KEY AUTO_INCREMENT,
    PATIENT_ID INT NOT NULL,
    TESTCODE INT NOT NULL,
    DATETIME DATETIME NOT NULL,
    FOREIGN KEY (PATIENT_ID) REFERENCES Patients(PATIENT_ID),
    FOREIGN KEY (TESTCODE) REFERENCES TestsCatalog(TEST_CODE)
);

CREATE TABLE Orders (
    ORDER_ID INT PRIMARY KEY AUTO_INCREMENT,
    PATIENT_ID INT NOT NULL,
    APPOINTMENT_ID INT NOT NULL,
    TEST_CODE INT NOT NULL,
    ORDERING_PHYSICIAN VARCHAR(255) NOT NULL,
    ORDER_DATE TIMESTAMP DEFAULT CURRENT_TIMESTAMP NOT NULL,
    STATUS VARCHAR(255) NOT NULL,
    FOREIGN KEY (PATIENT_ID) REFERENCES Patients(PATIENT_ID),
    FOREIGN KEY (TEST_CODE) REFERENCES TestsCatalog(TEST_CODE),
    FOREIGN KEY (APPOINTMENT_ID) REFERENCES Appointments(APPOINTMENT_ID)
);

CREATE TABLE Results (
    RESULT_ID INT PRIMARY KEY AUTO_INCREMENT,
    ORDER_ID INT NOT NULL,
    REPORT_URL VARCHAR(255),
    INTERPRETATION VARCHAR(255),
    REPORTING_PATHOLOGIST VARCHAR(255),
    FOREIGN KEY (ORDER_ID) REFERENCES Orders(ORDER_ID)
);
CREATE TABLE Billing (
    BILLING_ID INT PRIMARY KEY AUTO_INCREMENT,
    ORDER_ID INT NOT NULL,
    BILLED_AMOUNT DECIMAL(10,2) NOT NULL,
    PAYMENT_STATUS VARCHAR(255) NOT NULL,
    INSURANCE_CLAIM_STATUS VARCHAR(255),
    FOREIGN KEY (ORDER_ID) REFERENCES Orders(ORDER_ID)
);


INSERT INTO Staff (NAME, ROLE, CONTACT, PASSWORD, SALT, IV, SESSION_KEY) VALUES ('Alice', 'Secretary', '98E2FD40354F3E527DB14E74F348AB57', '2da7b829ece2f74a45341d1247456cb5fca7e2e5dbd821c9ce338eb25645b548', '18e039ac5fce3eb6b182151fe9d58319', 'B564122E3A9F4C7193B8AF30F183708F', NULL);
INSERT INTO Staff (NAME, ROLE, CONTACT, PASSWORD, SALT, IV, SESSION_KEY) VALUES ('bob', 'Lab Staff', '98E2FD40354F3E527DB14E74F348AB57', '2da7b829ece2f74a45341d1247456cb5fca7e2e5dbd821c9ce338eb25645b548', '18e039ac5fce3eb6b182151fe9d58319', 'B564122E3A9F4C7193B8AF30F183708F', NULL);
INSERT INTO Staff (NAME, ROLE, CONTACT, PASSWORD, SALT, IV, SESSION_KEY) VALUES ('lily', 'Secretary', '98E2FD40354F3E527DB14E74F348AB57', '2da7b829ece2f74a45341d1247456cb5fca7e2e5dbd821c9ce338eb25645b548', '18e039ac5fce3eb6b182151fe9d58319', 'B564122E3A9F4C7193B8AF30F183708F', NULL);
INSERT INTO Staff (NAME, ROLE, CONTACT, PASSWORD, SALT, IV, SESSION_KEY) VALUES ('hey', 'Lab Staff', '98E2FD40354F3E527DB14E74F348AB57', '2da7b829ece2f74a45341d1247456cb5fca7e2e5dbd821c9ce338eb25645b548', '18e039ac5fce3eb6b182151fe9d58319', 'B564122E3A9F4C7193B8AF30F183708F', NULL);