Business scenario: Community Health Collective coordinates h…
Business scenario: Community Health Collective coordinates high-quality outpatient care through a network of neighborhood clinics. The database supports planning, staffing, and service offerings across locations. Branch Management: The Collective operates multiple branches across different neighborhoods, each coordinating resources and partnerships. Clinic Information: Within each branch are several clinics, each with its own operating hours and specialty focus. Service Catalog: At the core is a catalog of clinical services (e.g., preventive screenings, vaccinations, nutrition consults), with details maintained centrally. Category Classification: Categories organize services by type (e.g., “Preventive Care,” “Immunization,” “Counseling”). Provider Records: The database maintains provider records (external partner organizations or specialty groups) associated with services available to clinics. Database: Branch (Br_ID, Br_Name, Br_Contact, Br_Street, Br_City, Br_State, Br_Code) Clinic (CL_ID, CL_Name, CL_Website, CL_Contact, Br_ID) Service (Sr_ID, Sr_Name, Sr_Description, Sr_Origin, Sr_Level, Sr_Focus, Sr_Price, Cat_ID, CL_ID) Category (Cat_ID, Cat_Name, Cat_Description) Provider (Pr_ID, Pr_Name, Pr_Location, Pr_Contact) Question 1: For the given business rules, the database provided above does not include one of the tables. Identify the missing table and write a single SQL query to create the missing table. Make sure to include all the required constraints. Use Oracle naming conventions for constraints’ names. Do not add new business rules. Explain why this new table is needed. Question 2: For the database you completed in Question 1 above, write a single complex SQL query that is based on multiple tables and includes ALL of the following: Subquery GROUP BY, WHERE, and HAVING statements One group function One single-row function If you would like to, you can add more table(s) and/or columns to the existing tables. In that case, provide the structure of the new or updated table(s) using the same format as above. If you need to make any assumptions, provide a clear explanation of each assumption. Explain what the query is intended to do in a complete, coherent sentence without database jargon. Answers that just repeat the question and state that it is “a complex SQL query that is based on multiple tables and includes the following…” will not receive any points. You have to clearly explain what data your query will retrieve from the given database to receive points for the answer.