129 lines
4.7 KiB
SQL
129 lines
4.7 KiB
SQL
DROP TABLE "Phone Number" IF EXISTS;
|
|
DROP TABLE "Schedule" IF EXISTS;
|
|
DROP TABLE "Assignment" IF EXISTS;
|
|
DROP TABLE "Patient" IF EXISTS;
|
|
DROP TABLE "Medical Doctor" IF EXISTS;
|
|
DROP TABLE "Therapist" IF EXISTS;
|
|
DROP TABLE "Therapist Number" IF EXISTS;
|
|
DROP TABLE "Medication" IF EXISTS;
|
|
DROP TABLE "Patient Medication" IF EXISTS;
|
|
DROP TABLE "Psychiatrist" IF EXISTS;
|
|
|
|
CREATE TABLE "Psychiatrist" (
|
|
"ID Number" INTEGER GENERATED BY DEFAULT AS IDENTITY(START WITH 0) NOT
|
|
NULL PRIMARY KEY,
|
|
"First Name" VARCHAR(25) NOT NULL,
|
|
"Surname" VARCHAR(25) NOT NULL,
|
|
"Gender" CHAR(6),
|
|
"Street and number" VARCHAR(50),
|
|
"City" VARCHAR(25),
|
|
"Postal code" CHAR(5),
|
|
"State" CHAR(2),
|
|
"Phone Number" VARCHAR(10)
|
|
);
|
|
CREATE TABLE "Medical Doctor" (
|
|
"ID Number" INTEGER GENERATED BY DEFAULT AS IDENTITY(START WITH 0) NOT NULL PRIMARY KEY,
|
|
"First Name" VARCHAR(25) NOT NULL,
|
|
"Surname" VARCHAR(25) NOT NULL,
|
|
"Gender" CHAR(6),
|
|
"Street and number" VARCHAR(50),
|
|
"City" VARCHAR(25),
|
|
"Postal code" VARCHAR(5),
|
|
"State" CHAR(2),
|
|
"Phone Number" VARCHAR(10)
|
|
);
|
|
CREATE TABLE "Patient" (
|
|
"ID Number" INTEGER GENERATED BY DEFAULT AS IDENTITY(START WITH 0) NOT NULL PRIMARY KEY,
|
|
"First Name" VARCHAR(25) NOT NULL,
|
|
"Surname" VARCHAR(25) NOT NULL,
|
|
"Gender" CHAR(6),
|
|
"Date of Birth" DATE,
|
|
"Street and number" VARCHAR(50),
|
|
"City" VARCHAR(25),
|
|
"Postal code" VARCHAR(5),
|
|
"State" CHAR(2),
|
|
"Diagnosis" VARCHAR(60),
|
|
"Medical Doctor ID" INTEGER,
|
|
"Psychiatrist ID" INTEGER,
|
|
"Time of registry" TIMESTAMP,
|
|
CONSTRAINT "CK_PAT_GNDR" CHECK( "Gender" in ( 'Male', 'Female' ) ),
|
|
CONSTRAINT FK_PAT_PSY FOREIGN KEY ("Psychiatrist ID") REFERENCES "Psychiatrist" ("ID Number"),
|
|
CONSTRAINT FK_PAT_DOC FOREIGN KEY ("Medical Doctor ID") REFERENCES "Medical Doctor" ("ID Number")
|
|
);
|
|
CREATE TABLE "Phone Number" (
|
|
"Phone ID" INTEGER GENERATED BY DEFAULT AS IDENTITY(START WITH 0) NOT NULL PRIMARY KEY,
|
|
"Patient ID" INTEGER NOT NULL,
|
|
"Number" VARCHAR(10),
|
|
"Description" VARCHAR(10),
|
|
CONSTRAINT FK_PAT_PHN FOREIGN KEY ("Patient ID") REFERENCES "Patient" ("ID Number")
|
|
);
|
|
CREATE TABLE "Therapist" (
|
|
"ID Number" INTEGER GENERATED BY DEFAULT AS IDENTITY(START WITH 0) NOT NULL PRIMARY KEY,
|
|
"First Name" VARCHAR(25) NOT NULL,
|
|
"Surname" VARCHAR(25) NOT NULL,
|
|
"Gender" CHAR(6),
|
|
"Street and number" VARCHAR(50),
|
|
"City" VARCHAR(25),
|
|
"Postal code" VARCHAR(5),
|
|
"State" CHAR(2),
|
|
"Tax number" VARCHAR(20),
|
|
"Academic degree" VARCHAR(25),
|
|
"License number" VARCHAR(15),
|
|
"Hiring date" DATE NOT NULL,
|
|
"Termination date" DATE,
|
|
CONSTRAINT "CK_THP_GNDR" CHECK( "Gender" in ( 'Male', 'Female' ) ),
|
|
CONSTRAINT "CK_TERM_DT" CHECK( "Termination date" > "Hiring date" )
|
|
);
|
|
CREATE TABLE "Therapist Number" (
|
|
"Phone ID" INTEGER GENERATED BY DEFAULT AS IDENTITY(START WITH 0) NOT NULL
|
|
PRIMARY KEY,
|
|
"Therapist ID" INTEGER,
|
|
"Number" VARCHAR(10),
|
|
"Description" VARCHAR(10),
|
|
CONSTRAINT FK_THP_PHN FOREIGN KEY ("Therapist ID") REFERENCES "Therapist" ("ID Number")
|
|
);
|
|
CREATE TABLE "Assignment" (
|
|
"Assignment ID" INTEGER GENERATED BY DEFAULT AS IDENTITY(START WITH 0) NOT NULL PRIMARY KEY,
|
|
"Patient ID" INTEGER NOT NULL,
|
|
"Therapist ID" INTEGER NOT NULL,
|
|
"Date assigned" DATE DEFAULT CURRENT_DATE NOT NULL,
|
|
"Date case closed" DATE,
|
|
CONSTRAINT FK_PAT_ASMT FOREIGN KEY ("Patient ID") REFERENCES "Patient" ("ID Number"),
|
|
CONSTRAINT FK_THP_ASMT FOREIGN KEY ("Therapist ID") REFERENCES "Therapist" ("ID Number"),
|
|
CONSTRAINT "CK_CLOSE_DT" CHECK( "Date case closed" >= "Date assigned" )
|
|
);
|
|
CREATE TABLE "Schedule" (
|
|
"Schedule ID" INTEGER GENERATED BY DEFAULT AS IDENTITY(START WITH 0) NOT NULL PRIMARY KEY,
|
|
"Assignment ID" INTEGER,
|
|
"Slot date" DATE NOT NULL,
|
|
"Slot hour" TIME NOT NULL,
|
|
"Status of the session" VARCHAR(20),
|
|
CONSTRAINT FK_SCH_ASMT FOREIGN KEY ("Assignment ID") REFERENCES "Assignment" ("Assignment ID")
|
|
);
|
|
CREATE TABLE "Payment" (
|
|
"Payment ID" INTEGER GENERATED BY DEFAULT AS IDENTITY(START WITH 0) NOT NULL PRIMARY KEY,
|
|
"Patient ID" INTEGER NOT NULL,
|
|
"Date and time of Payment" TIMESTAMP(6) DEFAULT CURRENT_TIMESTAMP,
|
|
"Amount" DECIMAL (10, 2) NOT NULL,
|
|
"Notes" VARCHAR(100),
|
|
"Result" CHAR(6) NOT NULL,
|
|
CONSTRAINT FK_PAT_PYMNT FOREIGN KEY ("Patient ID") REFERENCES "Patient" ("ID Number"),
|
|
CONSTRAINT CK_DBT CHECK("Result" IN ('DEBIT', 'CREDIT'))
|
|
);
|
|
CREATE TABLE "Medication" (
|
|
"Medication ID" INTEGER GENERATED BY DEFAULT AS IDENTITY(START WITH 0) NOT NULL PRIMARY KEY,
|
|
"Name" VARCHAR(30) NOT NULL,
|
|
"Description" VARCHAR(256)
|
|
);
|
|
CREATE TABLE "Patient Medication" (
|
|
"Patient ID" INTEGER NOT NULL,
|
|
"Medication ID" INTEGER NOT NULL,
|
|
"Dosage" VARCHAR(50),
|
|
"Start date" DATE DEFAULT CURRENT_DATE,
|
|
"End date" DATE,
|
|
CONSTRAINT PK_PAT_MED PRIMARY KEY ("Patient ID", "Medication ID" ),
|
|
CONSTRAINT FK_MED_PAT FOREIGN KEY ("Medication ID") REFERENCES "Medication" ("Medication ID"),
|
|
CONSTRAINT FK_PAT_MED FOREIGN KEY ("Patient ID") REFERENCES "Patient" ("ID Number"),
|
|
CONSTRAINT CK_END_DT CHECK( "End date" >= "Start date" )
|
|
);
|