database creation.txt

# email_id and P_id has been added to auth_patient table
# email_id and doctor_id has been added to auth_doctor table

create database project;
use project;



create table Patient (
P_id  int primary key AUTO_INCREMENT ,
first_name varchar(50) ,
last_name  varchar(50) ,
address    varchar(50) ,
gender     varchar(10) ,
phone      int

);

alter table Patient add column email varchar(50);

create table Doctor (
doctor_id   int  primary key AUTO_INCREMENT ,
first_name  varchar(50) ,
last_name   varchar(50) ,
rating      numeric(2,1)

);

create table Patient_Doctor (
pat_doc_id int primary key AUTO_INCREMENT ,
doctor_id  int ,
P_id       int ,
foreign key(P_id)  references Patient(P_id) ,
foreign key(doctor_id)  references Doctor(doctor_id)
 
);


create table Patient_department(
 pd_id       int primary key AUTO_INCREMENT ,
 dept_name   varchar(50),  
 P_id        int ,
 foreign key(P_id)  references Patient(P_id)
);


create table Auth_patient(
auth_id    int primary key  AUTO_INCREMENT,
email      varchar(50),
password   varchar(50) ,
P_id       int ,
foreign key(P_id)  references Patient(P_id) 
);

create table Med_Bills(
bill_id    int  primary key AUTO_INCREMENT,
P_id       int ,
amount     float ,
foreign key(P_id)  references Patient(P_id) 

);

create table Auth_doctor(
auth_id    int primary key  AUTO_INCREMENT,
email       varchar(50),
password    varchar(50),
doctor_id   int ,
foreign key(doctor_id)  references Doctor(doctor_id)

);

create table Bill_details(
bill_det_id     int  primary key AUTO_INCREMENT,
bill_id         int ,
type            varchar(50) ,
reason          varchar(50) ,
foreign key(bill_id)  references Med_Bills(bill_id) 
 
);

create table slots(
slot_id     int primary key AUTO_INCREMENT,
time_start  time,
time_end    time
);


#    D_id taken as doctor_id
create table Appointments(
appoint_id      int primary key  AUTO_INCREMENT ,
P_id            int ,
doctor_id       int ,
slot_id         int ,
Time            time ,
foreign key(P_id)  references Patient(P_id) ,
foreign key(doctor_id)  references Doctor(doctor_id) ,
foreign key(slot_id)  references slots(slot_id) 
 
);

create table unavailable(
unav_id      int primary key AUTO_INCREMENT,
doctor_id    int ,
slot_id      int ,
day          varchar(50),
foreign key(doctor_id)  references Doctor(doctor_id) ,
foreign key(slot_id)  references slots(slot_id) 
  
);


create table available(
av_id      int primary key AUTO_INCREMENT,
doctor_id    int ,
slot_id      int ,
day          varchar(50),
foreign key(doctor_id)  references Doctor(doctor_id) ,
foreign key(slot_id)  references slots(slot_id) 
  
);


create table Doctor_department(
dp_id       int primary key  AUTO_INCREMENT,
doctor_id   int ,
dept_name   varchar(50) ,
foreign key(doctor_id)  references Doctor(doctor_id) 

);


create table Prescription(
pres_id       int primary key AUTO_INCREMENT ,
P_id          int ,
Doctor_id     int ,
bill_id        int ,                 #Med_Bills  changed  to bill_id
amount        int ,
time          time ,
Symptoms      text ,
Diagnosis     text ,
foreign key(doctor_id)  references Doctor(doctor_id) ,
foreign key(P_id)  references Patient(P_id) , 
foreign key(bill_id)  references Med_Bills(bill_id) 

);



create table Reviews(
review_id     int primary key  AUTO_INCREMENT,
doctor_id     int ,
text          text ,
foreign key(doctor_id)  references Doctor(doctor_id) 

);