databaseCommands.txt

mysql -u root -p

create database hospitalDB;

use hospitalDB;


// Tables

create table users (
user_id int not null, 
name varchar(30) not null,
email varchar(30) not null, 
gender varchar(10), 
address varchar(80),
phone_number varchar(15),
primary key (user_id));


create table patients (
user_id int not null,
patient_id int not null,
weight int,
blood_group varchar(6),
blood_pressure varchar(25),
height int,
primary key (user_id, patient_id),
foreign key (user_id) references users(user_id)
);

create table doctors (
user_id int not null,
doctor_id int not null,
specialization varchar(30),
designation varchar(30),
department varchar(30),
room_no varchar(10),
primary key (user_id, doctor_id),
foreign key (user_id) references users(user_id)
);


create table hospital_admins (
admin_id int not null,
user_id int not null,
primary key(admin_id, user_id),
foreign key (user_id) references users(user_id)
);

create table test (
test_id int,
test_type varchar(20),
test_name varchar(30),
primary key(test_id));

///
create table booked_tests (
test_id int, 
user_id int not null,
slot_number int,
booked_date date,
test_report varchar(30),
test_status varchar(10),
primary key (test_id, patient_id),
foreign key (patient_id) references patients(patient_id)
);


create table rooms (
room_number varchar(10),
room_type varchar(15),
primary key (room_number));


create table admissions (
patient_id int not null,
room_number varchar(10),
admission_date date,
release_date date,
primary key(patient_id, room_number),
foreign key (patient_id) references patients(patient_id),
foreign key (room_number) references rooms(room_number)
);


create table appointments (
patient_id int not null,
doctor_id int not null,
appointment_id int,
prescription varchar(500),
slot_number int,
appointment_type varchar(20),
appointment_date date,
appointment_status varchar(10),
primary_key(appiintment_id),
foreign key (patient_id) references patients(patient_id),
foreign key (doctor_id) references doctors(doctor_id)
);