Case Description: Although COVID restrictions are mostly lifted, proof of vaccination is still sometimes needed for international travel, e.g., traveling to Japan.

You are invited to design a relational database to keep track of all the COVID-19 shots administered in the city.

Residents are advised to pre-register for a booster shot online. Individuals who walk-in without booking appointments will be registered onsite. Booster eligibility: At least 5 months after completing primary COVID-19 vaccination series, i.e., 2 shots for Pfizer & Moderna, 1 shot for Johnson & Johnson (J&J).

When a person shows up for an appointment and is administered a COVID-19 dose, a vaccination record is generated to keep track of:

Person-related details, including PersonName, ZipCode, PersonCounty, Booster-Eligibility (Y/N)

Vaccine-related details, including VacID, VacName, VacType

Vaccination-related details, including: VacDate, VacID, DoseNum (1st, 2nd, 3rd), SiteID

Vaccination Site-related details, including: SiteID, SiteName

Vaccine Types: COVID-19 vaccines can be classified into only one of the following three types -

mRNA vaccine: Uses lab-created mRNA encapsulated within nanoparticles, e.g., Pfizer, Moderna

Vector vaccine: Uses weakened virus (viral vector), e.g., Johnson & Johnson, Oxford-AstraZeneca

Protein subunit vaccine: Uses weakened/attenuated, or inactivated (killed) virus, e.g., Novavax

COVID-19 vaccines can also be categorized based on the number of doses required -

Multi-dose vaccines: Pfizer, Moderna, Oxford-AstraZeneca , Novavax

Single-dose vaccines: Johnson & Johnson (J&J)

Q1. Draw an Extended Entity Relationship Diagram (EERD) for this NYC COVID Shots System. List all entities, primary keys (underlined), foreign keys (marked as [FK]), attributes, and relationships (including generalization/specialization, disjoint/overlapping/partial/total completeness relationship).