Objective

• Create a database to manage requests for NYC vaccine scheduling

• Create a UML diagram

• Create searches and output using relational algebra

Database Design

Your design must include at least the following:

• Patients

• Definition: Patients schedule vaccine appointments.

• Attributes: Identify the patient first and last name, date of birth, street, city, state and zip, phone number, email, age, pre-existing conditioning and occupation. For instance, pre-existing conditions can be diabetics, cancer, or any number of medical conditions.

• Multi-value attributes: There can be more than 1 pre-existing conditions or occupation.

• Location

• Definition: Physical location where NYC is administering vaccines to patients.

• Attributes: Identify the address, city, state, zip, phone number, is parking available, wheelchair accessibility, name of organization (for instance, Yankee Stadium, Jones Beach, Walgreens, a church, etc).

• Multi value attributes: none.

• Vaccine

• Definition: The vaccine administered to patients.

• Attributes: Identify the vaccine manufacturer (Pfizer, Moderna, etc), quantity, location where the vaccine is located.

• Multi value attributes: 1 vacation can be available at many locations and can have different quantities at each location.

• Staff

• Definition: Staff assist and administer the vaccine to patients.

• Attribute: Identify the staff name, staff address and assigned location.

• Multi value attributes: Staff can be assigned to more than 1 location.

• Appointment

• Definition: Time slots available at locations for patients to schedule a vaccine. For instance, Yankee Stadium will have 500 appointments on Monday.

• Attributes: Identify the location, date, time, patient who is requesting vaccine and is this the first or second of 2 doses.

• Multi value attributes: none

• Vaccine Administer

• Definition: Tracks patients who actually receive a vaccine from staff at a location.

• Attributes: Identify patient who received the vaccine, which vaccine was administered, staff who administered the vaccine, location where the vaccine was administered and appointment slot.

• Multi value attributes: none

Patients are not required to pay for a vaccine.

Identify and create the following in your database design:

• A UML diagram will include all attributes types including single value, multi value, composite and derived.

• Identify all primary and foreign keys in the UML and relations.

• Degree

• Domains

• Relationship between entities

• Relationship type

• Attributes

• Cardinality

• Tuples

• Include at least 4 multi-value attributes in your design.

• Relations

• Convert the UML diagram to relations in the format of: relation(attribute1, attribute2, attribute3). For instance, staff(staff_id, staff_First, staff_Last, etc).

• Include at least nine relations and at least two attributes for each relation.

Relational Algebra

• Generate relational algebra to answer the queries below.

• Use standard notation and replace all underlined terms with your own values and maintain the intent of the search. For instance: replace Yankee Stadium with another location and replace Monday with another day.

• Create descriptive attribute labels.

1. Identify available appointment slots on March 1 at Yankee Stadium. Display the location, date and time slots available.

2. Identify patients with appointments today at Yankee Stadium. Display the patient name, vaccine manufacturer, patient address and email.

3. Identify patents that cancelled or didnt show up for appointments yesterday at Yankee Stadium. Display the patient name, vaccine manufacturer, appointment date, time and location.

4. Identify staff assigned to the Yankee Stadium vaccine administration site on March 1. Display the staff name.

5. Identify eligible patients without appointments. Eligible could be based on age, occupation, pre-existing medical conditions. Display the patient name, age address, pre-existing conditions and occupation.

6. Identify the number of vaccine doses available by borough now. Display two columns: Borough and number of vaccine doses available. Display one row for each distinct Borough. Use an aggregate function and grouping operation to answer this question.

7. Identify the number of appointments scheduled by borough tomorrow. Display two columns: Borough and number of appointments requested. Display one row for each distinct Borough. Use an aggregate function and grouping operation to answer this question.

8. There are insufficient available vaccine dosages to satisfy all the appointments tomorrow. Identify appointments of patients less than age 65 in Brooklyn. Display the patient name, address and email.

9. Identify vaccine dosages administered in the last 6 months by patient zipcode. Display two columns: Patient zip code and number of dosages administered. Display one row for each distinct patient zipcode. Use an aggregate function and grouping operation to answer this question.

10. Identify vaccine dosages administered in the last 6 months by vaccine manufacturer. Display two columns: Vaccine manufacturer and number of dosages administered. Display one row for each distinct vaccine manufacturer. Use an aggregate function and grouping operation to answer this question.

Formatting

• Your project must be typed.

• The UML design must include the same attribute names as your relational algebra.

• Your project must include the question and relational algebra operations to answer the question

• Use appropriate terminology.

• UML designs must be illustrated using software such as LucidChart (preferred) Microsoft Word, Microsoft Visio. Dont draw boxes by hand.

• Include all project requirements including UML design, relations, degree, domain, relationship between entities, attributes, cardinality, tuples, and multi value attributes.

• All pages of your output must include your name, class, date and project number in the header of each page.

• The first page of your project must include your name, the last four digits of your student id, class, the submission date and the project number.