Database.sql

DROP DATABASE IF EXISTS himsProDB;
CREATE DATABASE IF NOT EXISTS himsProDB;
USE himsProDB;

CREATE TABLE DepartmentTB(
	D_ID VARCHAR(6) PRIMARY KEY,
	D_NAME VARCHAR(20) NOT NULL,
	D_PHONE INT(10) NOT NULL
);

CREATE TABLE StaffTB(
	S_ID VARCHAR(6) PRIMARY KEY,
	U_ID VARCHAR(6) NOT NULL UNIQUE,
	S_PASSWORD VARCHAR(50) NOT NULL,
	D_ID VARCHAR(6),
	S_NAME VARCHAR(30) NOT NULL,
	S_PHONE INT(10) NOT NULL,
	S_ADDRESS VARCHAR(50) NOT NULL,
	S_GENDER CHAR(1) NOT NULL CHECK(S_GENDER ='M' OR S_GENDER ='m' OR S_GENDER ='F' OR S_GENDER ='f'),
	S_JOB VARCHAR(15) NOT NULL,
	S_LEVEL VARCHAR(30) NOT NULL,
	S_WORKHOUR INT(2) NOT NULL,
	S_SALARY DOUBLE(2,0) NOT NULL,
	S_DOB DATE NOT NULL,
	FOREIGN KEY STAFF_DEPARTMENT_D_ID_FK(D_ID) REFERENCES DepartmentTB(D_ID)
);

CREATE TABLE PatientTB(
	P_ID VARCHAR(6) PRIMARY KEY,
	U_ID VARCHAR(6) NOT NULL UNIQUE,
	P_PASSWORD VARCHAR(50) NOT NULL,
	P_NAME VARCHAR(30) NOT NULL,
	P_GENDER CHAR(1) NOT NULL CHECK(P_GENDER ='M' OR P_GENDER ='m' OR P_GENDER ='F' OR P_GENDER ='f'),
	P_DOB DATE NOT NULL,
	P_PHONE INT(10) NOT NULL,
	P_ADDRESS VARCHAR(50) NOT NULL
);

CREATE TABLE DiseaseTB(
	DISEASE_ID VARCHAR(6) PRIMARY KEY,
	DISEASE_NAME VARCHAR(30) NOT NULL,
	DISEASE_SYMPTOMS VARCHAR(50)
);

CREATE TABLE DiagnosisTB(
	DIA_ID VARCHAR(6) PRIMARY KEY,
	DIA_DISEASEID VARCHAR(6),
	DIA_DESCRIPTION VARCHAR(80) NOT NULL,
	FOREIGN KEY DIANOGSIS_DIA_DISEASEID_FK(DIA_DISEASEID) REFERENCES DiseaseTB(DISEASE_ID)
);

CREATE TABLE ServiceTB(
	SER_ID VARCHAR(6) PRIMARY KEY,
	D_ID VARCHAR(6),
	S_NAME VARCHAR(30) NOT NULL,
	S_DESCRIPTION VARCHAR(30),
	FOREIGN KEY SERVICE_D_ID_FK(D_ID) REFERENCES DepartmentTB(D_ID)
);

CREATE TABLE Utility_BillingTB(
	UBILL_ID VARCHAR(6) PRIMARY KEY,
	P_ID VARCHAR(6),
	W_ID VARCHAR(6),
	UBILL_DATE DATE NOT NULL,
	UBILL_DESC VARCHAR(30) NOT NULL,
	UBILL_PRICE DOUBLE(7,2) NOT NULL,
	FOREIGN KEY UTILITY_BILLTB_PID(P_ID) REFERENCES PatientTB(P_ID)
);

CREATE TABLE Medical_BillingTB(
	M_ID VARCHAR(6) PRIMARY KEY,
	P_ID VARCHAR(6),
	M_DATE DATE NOT NULL,
	M_DESC VARCHAR(30) NOT NULL,
	M_PRICE DOUBLE(7,2) NOT NULL,
	FOREIGN KEY MEDICAL_BILLTB_PID(P_ID) REFERENCES PatientTB(P_ID)
);

CREATE TABLE Surgery_BillingTB(
	SUR_ID VARCHAR(6) PRIMARY KEY,
	P_ID VARCHAR(6),
	SUR_DATE DATE,
	SUR_DESC VARCHAR(30) NOT NULL,
	SUR_PRICE DOUBLE(7,2) NOT NULL,
	FOREIGN KEY SURGERY_BILLTB_PID(P_ID) REFERENCES PatientTB(P_ID)
);

CREATE TABLE BillTB(
	B_ID VARCHAR(6) PRIMARY KEY,
	P_ID VARCHAR(6),
	B_SURID VARCHAR(6),
	B_UBILLID VARCHAR(6),
	B_MEDID VARCHAR(6),
	B_DATE DATE NOT NULL,
	B_TOTALAMOUNT DOUBLE(7,2) NOT NULL,
	FOREIGN KEY BillTB_PID(P_ID) REFERENCES PatientTB(P_ID),
	FOREIGN KEY BillTB_SURID(B_SURID) REFERENCES Surgery_BillingTB(SUR_ID),
	FOREIGN KEY BillTB_UBILLID(B_UBILLID) REFERENCES Utility_BillingTB(UBILL_ID),
	FOREIGN KEY BillTB_MEDID(B_MEDID) REFERENCES Medical_BillingTB(M_ID)
);

CREATE TABLE TransactionTB(
	T_ID VARCHAR(6) PRIMARY KEY,
	P_ID VARCHAR(6),
	T_DISCHARGEBY VARCHAR(6),
	T_ADMITDATE DATE NOT NULL,
	T_ADMITTIME TIME NOT NULL,
	T_DISCHARGEDATE DATE NOT NULL,
	T_DISCHARGETIME TIME NOT NULL,
	FOREIGN KEY TRANSACTION_TB_PID(P_ID) REFERENCES PatientTB(P_ID),
	FOREIGN KEY TRANSACTION_TB_DOCTORID(T_DISCHARGEBY) REFERENCES StaffTB(S_ID)
);

CREATE TABLE WardsTB(
	W_ID VARCHAR(6) PRIMARY KEY,
	W_CAPACITY INT(2) NOT NULL,
	W_TYPE VARCHAR(10) NOT NULL,
	W_OCCUPY INT(2),
	W_PRICE DOUBLE(7,2) NOT NULL
);

CREATE TABLE UtilizationTB(
	BED_ID VARCHAR(6) PRIMARY KEY,
	W_ID VARCHAR(6),
	P_ID VARCHAR(6),
	STATUS VARCHAR(12),
	START_DATE DATE NOT NULL,
	END_DATE DATE,
	FOREIGN KEY UTILIZATION_TB_PID(P_ID) REFERENCES PatientTB(P_ID),
	FOREIGN KEY WARD_TB_PID(W_ID) REFERENCES WardSTB(W_ID)
);