COVID-19 Testing Application Database Implementation

Relational Model:

1. Category (categoryName, priority)

2. Person (healthInsuranceNo, personName, doBirth, gender, phoneNo, streetAddress, postalCode, city, registrationDate, categoryName)
   categoryName foreign key references relation Category

3. VaccinationLocation (locationName, streetAddress, postalCode, city)

4. Hospital (locationName)
   locationName foreign key references relation VaccinationLocation

5. Nurse (licenseNo, nurseName, locationName)
   locationName foreign key references relation Hospital

6. AdministratedDate (slotDate, locationName)
   locationName foreign key references relation VaccinationLocation

7. Vaccine (vaccineName, totalDosesNeeded, timeBetweenDoses)

8. Batch (vaccineName, batchNo, expiryDate, manufactureDate, count, locationName)
   vaccineName foreign key references relation Vaccine
   locationName foreign key references relation VaccinationLocation

9. Vial (vaccineName, batchNo, vialNo)
   (vaccineName, batchNo) foreign key references relation Batch

10. Deputed (licenseNo, slotDate, locationName)
    (slotDate, locationName) foreign key references relation AdministratedDate
    licenseNo foreign key references relation Nurse

11. Slot (slotID, slotDate, locationName, slotTime, allocationDate, allocationTime, healthInsuranceNo, (vaccineName, batchNo, vialNo), licenseNo)
    (slotDate, locationName) foreign key references relation AdministratedDate
    healthInsuranceNo foreign key references relation Person
    (vaccineName, batchNo, vialNo) foreign key references relation Vial
    licenseNo foreign key references relation Nurse
    

ER Diagram:

Database Creation:

For this project, IBM DB2 was used. The database tables can be created, dropped and populated with the given SQL files.

JDBC Program:

JDBC Program

Demonstration of the JDBC Program on Command Line