| 1 | DROP TABLE IF EXISTS Review;
|
|---|
| 2 | DROP TABLE IF EXISTS Examination;
|
|---|
| 3 | DROP TABLE IF EXISTS IsApprovedMedicineRecepit;
|
|---|
| 4 | DROP TABLE IF EXISTS MedicineRecepit;
|
|---|
| 5 | DROP TABLE IF EXISTS Report;
|
|---|
| 6 | DROP TABLE IF EXISTS Comment;
|
|---|
| 7 | DROP TABLE IF EXISTS Specialist;
|
|---|
| 8 | DROP TABLE IF EXISTS Doctor;
|
|---|
| 9 | DROP TABLE IF EXISTS Patient;
|
|---|
| 10 | DROP TABLE IF EXISTS AppUser;
|
|---|
| 11 |
|
|---|
| 12 | CREATE TABLE AppUser (
|
|---|
| 13 | ssn VARCHAR(13) PRIMARY KEY,
|
|---|
| 14 | email VARCHAR(100) NOT NULL,
|
|---|
| 15 | password VARCHAR(100) NOT NULL,
|
|---|
| 16 | firstName VARCHAR(100) NOT NULL,
|
|---|
| 17 | lastName VARCHAR(100) NOT NULL
|
|---|
| 18 | );
|
|---|
| 19 | CREATE TABLE Patient (
|
|---|
| 20 | userId VARCHAR(13) PRIMARY KEY REFERENCES AppUser(ssn),
|
|---|
| 21 | patientId VARCHAR(13)
|
|---|
| 22 | );
|
|---|
| 23 | CREATE TABLE Doctor (
|
|---|
| 24 | userId VARCHAR(13) PRIMARY KEY REFERENCES AppUser(ssn),
|
|---|
| 25 | workingHours VARCHAR(50),
|
|---|
| 26 | licenseNumber VARCHAR(50),
|
|---|
| 27 | hospitalId VARCHAR(50)
|
|---|
| 28 | );
|
|---|
| 29 | CREATE TABLE Specialist (
|
|---|
| 30 | userId VARCHAR(13) PRIMARY KEY REFERENCES AppUser(ssn),
|
|---|
| 31 | licenseNumber VARCHAR(50),
|
|---|
| 32 | hospitalId VARCHAR(50)
|
|---|
| 33 | );
|
|---|
| 34 | CREATE TABLE Comment (
|
|---|
| 35 | commentId INT PRIMARY KEY,
|
|---|
| 36 | createdBy VARCHAR(13) REFERENCES AppUser(ssn),
|
|---|
| 37 | createdAt TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
|
|---|
| 38 | comment VARCHAR(300)
|
|---|
| 39 | );
|
|---|
| 40 | CREATE TABLE Report (
|
|---|
| 41 | reportId INT PRIMARY KEY,
|
|---|
| 42 | description VARCHAR(300),
|
|---|
| 43 | isFinished BOOLEAN
|
|---|
| 44 | );
|
|---|
| 45 | CREATE TABLE MedicineRecepit (
|
|---|
| 46 | medicineRecepitId INT PRIMARY KEY,
|
|---|
| 47 | reportId INT REFERENCES Report(reportId),
|
|---|
| 48 | medicineName VARCHAR(50),
|
|---|
| 49 | quantity INT
|
|---|
| 50 | );
|
|---|
| 51 | CREATE TABLE IsApprovedMedicineRecepit (
|
|---|
| 52 | approvalId INT PRIMARY KEY,
|
|---|
| 53 | receiptId INT REFERENCES MedicineRecepit(medicineRecepitId),
|
|---|
| 54 | approvedBy VARCHAR(13) REFERENCES AppUser(ssn),
|
|---|
| 55 | IsApproved BOOLEAN
|
|---|
| 56 | );
|
|---|
| 57 | CREATE TABLE Examination (
|
|---|
| 58 | examinationId INT PRIMARY KEY,
|
|---|
| 59 | patientId VARCHAR(13) REFERENCES AppUser(ssn),
|
|---|
| 60 | doctorId VARCHAR(13) REFERENCES AppUser(ssn),
|
|---|
| 61 | specialistId VARCHAR(13) REFERENCES AppUser(ssn),
|
|---|
| 62 | dateTime TIMESTAMP DEFAULT CURRENT_TIMESTAMP
|
|---|
| 63 | );
|
|---|
| 64 | CREATE TABLE Review (
|
|---|
| 65 | reviewId INT PRIMARY KEY,
|
|---|
| 66 | patientId VARCHAR(13) REFERENCES AppUser(ssn),
|
|---|
| 67 | doctorId VARCHAR(13) REFERENCES AppUser(ssn),
|
|---|
| 68 | rank INT CHECK (rank >= 1 AND rank <= 5)
|
|---|
| 69 | );
|
|---|
