Office automation systems provide effective ways to process…

Questions

Office аutоmаtiоn systems prоvide effective wаys to process personal and organizational business data, to perform calculations, and to create documents.

Business scenаriо: Independent Cоffee Rоаsters аims to provide exceptional coffee experiences to its customers through its network of specialty coffee shops. The following business scenario outlines the purpose and functionality of the relational database developed for managing Independent Coffee Roasters' operations. Branch Management: Independent Coffee Roasters operates multiple branches across different neighborhoods, each offering a unique ambiance and coffee selection.  Coffee Shop Information: Within each branch, there are multiple coffee shops, each with its own distinct character and menu offerings.  Coffee Product Catalog: At the core of the database is the catalog of coffee products, which includes detailed information about each item available for sale in the coffee shops.  Category Classification: Categories serve as a means of organizing coffee products based on their type, roast level, or flavor profile.  Roaster Records: The database also maintains records of coffee roasters associated with the coffee products in the shops' collections. Database: Branch (Br_ID, Br_Name, Br_Contact, Br_Street, Br_City, Br_State, Br_Code) Coffee_Shop (Sh_ID, Sh_Name, Sh_Website, Sh_Contact, Br_ID) Coffee_Product (Pr_ID, Pr_Name, Pr_Description, Pr_Origin, Pr_Roast_Level, Pr_Flavor, Pr_Price, Cat_ID) Category (Cat_ID, Cat_Name, Cat_Description) Roaster (R_ID, R_Name, R_Location, R_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.