Please solve the following sql problem and give an detailed explanation. I will definitely give you helpful evaluation if you finished it, thank you!
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. Key - Primary Key is underlined - Foreign Key is italic Relations Patients (patient_ID, patient_first, patient_last, patient_street, patient_city, patient_state, patient_zip, patient_DOB, patient_age, patient_email, patient_phone) This relation identifies attributes for the patient. There is 1 tuple for each patient, even if a patient doesn't make an appointment. These patients can be from a hospital or doctors office. The multi-value attributes preexisting condition and occupation are not saved in this relation. Preexisting_Condition (PEC_ID, PEC_Name) This relation identifies all possible preexisting conditions. This relation does not associate the preexisting condition with a patient. For instance, PEC_Name can be cancer, diabetes or high blood pressure. Occupation(occupation ID, Occupation_Title) This relation identifies all possible occupations This relation does not associate the occupation with a patient. For instance, the occupation title can be waiter, doctor, teacher, bus driver or application developer. Patient_Preexisting (patient ID,,PEC ID ) This relation associates a preexisting condition with a patient. Preexisting condition is a multi value attribute, so 1 patient can have more than 1 preexisting condition. Or a patient can have no preexisting conditions. Patient_ID and PEC_ID are composite primary keys Patient_Occupation (patient ID, Occupation ID) This relation associates an occupation with a patient. Occupation is a multi-value attribute, so 1 patient can have more than 1 occupation. Or a patient can have no occupations. Patient_ID and Occupation_ID are composite primary keys
