1) A new company recently started a business where they buy highly discounted items in bulk...

 

Transcribed Image Text:

1) A new company recently started a business where they buy highly discounted items in bulk from different companies who are going out of business or need to get rid of additional items to save warehouse space. This new company has retail stores in big cities across the country where they sell the items to individuals. They also do business as wholesalers where they sell items in bulk to local retailers in small cities. Therefore, they have two types of customers: individuals and small retailers. The company has four departments: purchase, warehouse, sales (retail and wholesale) and accounting. They employ five different types of employees: managers, accounting staffs, warehouse staffs, retail associates, and purchase agents. Each staff, agent and associate works under a manager of the respective department. Purchase agents hunt for discount items, and they get a commission based on the items they are successfully able to buy. Create a relational model to manage the database of this company. You have the freedom to make necessary assumptions about relations and attributes to create a realistic model. 2) Make sure you define primary and foreign key constraints properly. Give descriptive names to relations and attributes. 3) Design the database schema in LucidChart following the conventions used in the textbook. For each relation schema, provide justifications for your choice of the primary and foreign keys. 4) Use SQL commands to create the schema of your database. Also use SQL commands to enter at least 5 dummy tuples in each relation. 5) Create examples of SQL query employing each of the SQL clauses: a. Union – 1 example b. Aggregate + Group by - 2 examples c. Nested query -2 examples For each example, include the query in natural language and corresponding SQL statements. Add additional tuples if required to run your queries successfully. For each query, provide the screenshot of the output of the query on MYSQL server. 1) A new company recently started a business where they buy highly discounted items in bulk from different companies who are going out of business or need to get rid of additional items to save warehouse space. This new company has retail stores in big cities across the country where they sell the items to individuals. They also do business as wholesalers where they sell items in bulk to local retailers in small cities. Therefore, they have two types of customers: individuals and small retailers. The company has four departments: purchase, warehouse, sales (retail and wholesale) and accounting. They employ five different types of employees: managers, accounting staffs, warehouse staffs, retail associates, and purchase agents. Each staff, agent and associate works under a manager of the respective department. Purchase agents hunt for discount items, and they get a commission based on the items they are successfully able to buy. Create a relational model to manage the database of this company. You have the freedom to make necessary assumptions about relations and attributes to create a realistic model. 2) Make sure you define primary and foreign key constraints properly. Give descriptive names to relations and attributes. 3) Design the database schema in LucidChart following the conventions used in the textbook. For each relation schema, provide justifications for your choice of the primary and foreign keys. 4) Use SQL commands to create the schema of your database. Also use SQL commands to enter at least 5 dummy tuples in each relation. 5) Create examples of SQL query employing each of the SQL clauses: a. Union – 1 example b. Aggregate + Group by - 2 examples c. Nested query -2 examples For each example, include the query in natural language and corresponding SQL statements. Add additional tuples if required to run your queries successfully. For each query, provide the screenshot of the output of the query on MYSQL server.