assignment

assignment.sql

create table Allergy
(
    AllergyID   int auto_increment,
    AllergyName varchar(100) not null,
    constraint AllergyPrimaryKey
        primary key (AllergyID)
);
create table Speciality
(
    SpecialityNumber varchar(100),
    SpecialityName   varchar(100) not null,
    constraint SpecialityPrimaryKey
        primary key (SpecialityNumber)
);

create table Doctor
(
    DoctorID         int auto_increment,
    Name             varchar(100) not null,
    Phone            varchar(100) not null,
    SpecialityNumber varchar(100) not null,
    SupervisorID     int          null,
    constraint DoctorPrimaryKey
        primary key (DoctorID),
    constraint DoctorSpecialityNumberForgienKey
        foreign key (SpecialityNumber) references Speciality (SpecialityNumber),
    constraint DoctorSupervisorForgienKey
        foreign key (SupervisorID) references Doctor (DoctorID)
);

create table Patient
(
    PatientID int auto_increment,
    Name      varchar(100) not null,
    Phone     varchar(100) not null,
    Email     varchar(100) null,
    Address   varchar(100) not null,
    AddedDate DATE         not null,
    DoctorID  int          not null,
    constraint Patient_pk
        primary key (PatientID),
    constraint PatientDoctorForgienKey
        foreign key (DoctorID) references Doctor (DoctorID)
);


create table PatientAllergy
(
    PatientID int not null,
    AllergyID int not null,
    constraint PatientAllergyAllergyForgienKey
        foreign key (AllergyID) references Allergy (AllergyID),
    constraint PatientAllergyPatientForgienKey
        foreign key (PatientID) references Patient (PatientID)
);

create table Medicine
(
    MedicineID   int auto_increment,
    MedicineName varchar(100) not null,
    constraint MedicinePrimaryKey
        primary key (MedicineID)
);

create table Appointment
(
    AppointmentID   int auto_increment,
    DoctorID        int           not null,
    PatientID       int           not null,
    AppointmentDate DATETIME      not null,
    BloodPressure   varchar(100)  not null,
    Weight          decimal(6, 2) not null,
    TreatmentNotes  text          not null,
    constraint Appointment_pk
        primary key (AppointmentID),
    constraint AppointmentDoctorForgienKey
        foreign key (DoctorID) references Doctor (DoctorID),
    constraint AppointmentPatientForgienKey
        foreign key (PatientID) references Patient (PatientID)
);


create table PatientMedicine
(
    AppointmentID int not null,
    MedicineID    int not null,
    constraint PatientMedicineMedicineForgienKey
        foreign key (MedicineID) references Medicine (MedicineID),
    constraint PatientMedicinePatientForgienKey
        foreign key (AppointmentID) references Appointment (AppointmentID)
);