Create a relational scenario following the given ERD and SQL statements. Create a scenario describing the relationships between patient, doctor, BloodBanks, Patients, Donors, BloodInventory, BloodTests.

ERD $($Entity$-$Relationship Diagram$)$

Tables and their attributes:

Patients

Patient ID $($primary key$)$

Patient Name

Patient Blood Group

Patient Disease

Hospital ID $($foreign key$)$

Donors

Donor ID $($Primary Key$)$

Donor Name

Donor Blood Group

Donor Medical Report

Donor Address Donor

Contact Number

Blood Bank ID $($Foreign Key$)$

Blood banks

Blood Bank ID $($Primary Key$)$

Blood Bank Name

Blood Bank Address

Blood Bank Contact Number

Doctors

Doctor ID $($Primary Key$)$

Doctor Name

Doctor Specialization

Hospital ID $($Foreign Key$)$

Blood Inventory

Inventory ID $($primary key$)$

blood bank id $($foreign key$)$

blood group units available expiration date

blood tests

Test ID $($Primary Key$)$

Donor ID $($Foreign Key$)$

Test Date

Test Results

Hospitals

Hospital ID $($primary key$)$

Hospital Name

Hospital Address

Hospital Contact Number

Step $3$ : Sql DDL statements

CREATE TABLE Hospitals $($

Hospital$\_$Id INT PRIMARY KEY,

Hospital$\_$Name VARCHAR$2(100),$

Hospital$\_$Address VARCHAR$2(255),$

Hospital$\_$Contact$\_$Number VARCHAR$2(20)$

$)$;

CREATE TABLE Doctors $($

Doctor$\_$Id INT PRIMARY KEY,

Doctor$\_$Name VARCHAR$2(100),$

Doctor$\_$Specialization VARCHAR$2(50),$

Hospital$\_$Id INT,

FOREIGN KEY $($Hospital$\_$Id$)$ REFERENCES Hospitals$($Hospital$\_$Id$)$

$)$;

CREATE TABLE BloodBanks $($

Blood$\_$Bank$\_$Id INT PRIMARY KEY,

Blood$\_$Bank$\_$Name VARCHAR$2(100),$

Blood$\_$Bank$\_$Address VARCHAR$2(255),$

Blood$\_$Bank$\_$Contact$\_$Number VARCHAR$2(20)$

$)$;

CREATE TABLE Patients $($

Patient$\_$Id INT PRIMARY KEY,

Patient$\_$Name VARCHAR$2(100),$

Patient$\_$Blood$\_$Group VARCHAR$2(3),$

Patient$\_$Disease VARCHAR$2(100),$

Hospital$\_$Id INT,

FOREIGN KEY $($Hospital$\_$Id$)$ REFERENCES Hospitals$($Hospital$\_$Id$)$

$)$;

CREATE TABLE Donors $($

Donor$\_$Id INT PRIMARY KEY,

Donor$\_$Name VARCHAR$2(100),$

Donor$\_$Blood$\_$Group VARCHAR$2(3),$

Donor$\_$Medical$\_$Report VARCHAR$2(255),$

Donor$\_$Address VARCHAR$2(255),$

Donor$\_$Contact$\_$Number VARCHAR$2(20),$

Blood$\_$Bank$\_$Id INT,

FOREIGN KEY $($Blood$\_$Bank$\_$Id$)$ REFERENCES BloodBanks$($Blood$\_$Bank$\_$Id$)$

$)$;

CREATE TABLE BloodInventory $($

Inventory$\_$Id INT PRIMARY KEY,

Blood$\_$Bank$\_$Id INT,

Blood$\_$Group VARCHAR$2(3),$

Units$\_$Available INT,

Expiry$\_$Date DATE,

FOREIGN KEY $($Blood$\_$Bank$\_$Id$)$ REFERENCES BloodBanks$($Blood$\_$Bank$\_$Id$)$

$)$;

CREATE TABLE BloodTests $($

Test$\_$Id INT PRIMARY KEY,

Donor$\_$Id INT,

Test$\_$Date DATE,

Test$\_$Result VARCHAR$2(50),$

FOREIGN KEY $($Donor$\_$Id$)$ REFERENCES Donors$($Donor$\_$Id$)$

$)$;