Database.txt

drop database hms;
create database hms;
use hms;

create table Ward(
	WardID varchar(10) not null,
	WardType varchar(50),
	Description varchar(500),
	constraint primary key (WardID)
);

create table Employee(
	EmployeeID varchar(10) not null,
	EmployeeType varchar(100),
	constraint primary key (EmployeeID)
);

create table Doctor(
	DoctorID varchar(10) not null,
	EmployeeID varchar(10),
	DoctorFirstName varchar(50),
	DoctorLastName varchar(200),
	DoctorNIC varchar(10),
	DoctorDOB varchar(20),
	DoctorGender varchar(10),
	Specialization varchar(50),
	Degrees varchar(100),
	RegistrationNo varchar(10),
	Title varchar(20),
	DoctorContactNo varchar(20),
	DoctorPostalCode varchar(10),
	DoctorStreet varchar(200),
	DoctorCity varchar(50),
	DoctorDistrict varchar(50),
	DoctorEmail varchar(200),
	constraint primary key (DoctorID),
	constraint foreign key (EmployeeID) references Employee (EmployeeID)
	on update cascade on delete cascade
);

create table Patient(
	PatientID varchar(10) not null,
	PatientFirstName varchar(50),
	PatientLastName varchar(200),
	PatientNIC varchar(10),
	PatientDOB varchar(20),
	PatientGender varchar(10),
	BloodGroup varchar(5),
	AlergyDetails varchar(500),
	SpecialNotes varchar(500),
	PatientPostalCode varchar(10),
	PatientStreet varchar(200),
	PatientCity varchar(50),
	PatientDistrict varchar(50),
	constraint primary key (PatientID)
);

create table Nurse(
	NurseID varchar(10),
	EmployeeID varchar(10),
	NurseFirstName varchar(50),
	NurseLastName varchar(200),
	NurseNIC varchar(10),
	NurseDOB varchar(20),
	NurseGender varchar(10),
	NurseContactNo varchar(20),
	NursePostalCode varchar(10),
	NurseStreet varchar(200),
	NurseCity varchar(50),
	NurseDistrict varchar(50),
	NurseEmail varchar(200),
	constraint primary key (NurseID),
	constraint foreign key (EmployeeID) references Employee (EmployeeID)
	on update cascade on delete cascade
);

create table Pharmacist(
	PharmacistID varchar(10),
	EmployeeID varchar(10),
	PharmacistFirstName varchar(50),
	PharmacistLastName varchar(200),
	PharmacistNIC varchar(10),
	PharmacistDOB varchar(20),
	PharmacistGender varchar(10),
	PharmacistContactNo varchar(20),
	PharmacistPostalCode varchar(10),
	PharmacistStreet varchar(200),
	PharmacistCity varchar(50),
	PharmacistDistrict varchar(50),
	PharmacistEmail varchar(200),
	constraint primary key (PharmacistID),
	constraint foreign key (EmployeeID) references Employee (EmployeeID)
	on update cascade on delete cascade
);

create table MaintenanceStaff(
	MStaffID varchar(10),
	EmployeeID varchar(10),
	MStaffFirstName varchar(50),
	MStaffLastName varchar(200),
	MStafftNIC varchar(10),
	MStaffDOB varchar(20),
	MStaffGender varchar(10),
	MStaffContactNo varchar(20),
	MStaffPostalCode varchar(10),
	MStaffStreet varchar(200),
	MStaffCity varchar(50),
	MStaffDistrict varchar(50),
	MStaffEmail varchar(200),
	constraint primary key (MStaffID),
	constraint foreign key (EmployeeID) references Employee (EmployeeID)
	on update cascade on delete cascade	
);

create table Admission(
	AdmissionID varchar(10),
	PatientID varchar(10),
	WardID varchar(10),
	AdmittedDate varchar(20),
	RecommendedBy varchar(200),
	ConfirmedBy varchar(200),
	LeadingConsultant varchar(200),
	LeadingConsultantID varchar(10),
	AdvancePayment decimal(10,2),
	constraint primary key (AdmissionID),
	constraint foreign key (PatientID) references Patient (PatientID)
	on update cascade on delete cascade,
	constraint foreign key (WardID) references Ward (WardID)
	on update cascade on delete cascade,
	constraint foreign key (LeadingConsultantID) references Doctor (DoctorID)
	on update cascade on delete cascade
);

create table Test(
	TestID varchar(10),
	TestName varchar(100),
	constraint primary key (TestID)
);

create table TestResult(
	ResultID varchar(10),
	TestID varchar(10),
	PatientID varchar(10),
	AdmissionID varchar(10),
	ResultDescription varchar(10000),
	constraint primary key (ResultID),
	constraint foreign key (TestID) references Test (TestID)
	on update cascade on delete cascade,
	constraint foreign key (PatientID) references Patient (PatientID)
	on update cascade on delete cascade,
	constraint foreign key (AdmissionID) references Admission (AdmissionID)
	on update cascade on delete cascade
);

create table PatientContact(
	PatientID varchar(10),
	Residence varchar(15),
	Mobile varchar(15),
	constraint primary key (PatientID),
	constraint foreign key (PatientID) references Patient (PatientID)
	on delete cascade on update cascade
);

create table EmergencyContact(
	PatientID varchar(10),
	Relationship varchar(50),
	Name varchar(100),
	Mobile varchar(15),
	Residence varchar(15),
	constraint primary key (PatientID),
	constraint foreign key (PatientID) references Patient (PatientID)
	on delete cascade on update cascade
);

create table Account(
	FirstName varchar(100),
	LastName varchar(100),
	Email varchar(200),
	UserName varchar(50),
	Password varchar(50),
	EmployeeID varchar(10),
	Constraint primary key (Username),
	Constraint foreign key (EmployeeID) references Employee (EmployeeID)
	on delete cascade on update cascade
);

create table Bill(
	BillID varchar(10),
	AdmissionID varchar(10),
	PatientID varchar(10),
	MedicalCharge float(10,2),
	ServiceCharge float(10,2),
	RoomCharge float(10,2),
	HospitalCharge float(10,2),
	Total float(10,2),
	Discount float(5,2),
	NetTotal float(10,2),
	constraint primary key (BillID),
	constraint foreign key (AdmissionID) references Admission (AdmissionID)
	on delete cascade on update cascade,
	constraint foreign key (PatientID) references Patient (PatientID)
	on delete cascade on update cascade
);

create table Payment(
	AdmissionID varchar(10),
	BillID varchar(10),
	PaymentDate varchar(10),
	Amount decimal (10,2),
	AditionalNotes varchar(100),
	constraint primary key (AdmissionID),
	constraint foreign key (BillID) references Bill (BillID)
	on delete cascade on update cascade
);

create table Attendance(
	ID varchar(10),
	Date varchar(10),
	Time varchar(10)
);

insert into Employee values("E001","Doctor");
insert into Employee values("E002","Nurse");
insert into Employee values("E003","Pharmacist");
insert into Employee values("E004","Maintenance Staff");
insert into Employee values("E005","Admin");