A company that sells and installs tiles needs a database with the requirements specified below. Your task is to design and implement a relational database that meets all their requirements.
* Please let me know if you need further clarifications on any of the requirements.
* If you need to make any other assumptions, please state your assumptions explicitly.
Requirements:
The company has several branch offices. Each branch is identified by a branch_number. The name, address (street, city, ZIP), and revenue_target of each branch office are maintained. Each branch has a designated branch manager and the branch manager's employee_ID is maintained.
Each employee has a unique employee_ID. The company maintains the first name, last name, designation, starting_date, branch_number, salary, and supervisor's employee_ID for each employee.
Each product sold by the company is identified by a unique product_code. The description, unit price, and stock_level of each product are maintained.
Each type of installation performed by the company is identified by a unique installation_type. The description and the billing_rate per hour for the installation_type are maintained.
Customers are identified by unique customer_ID. Information is maintained on each customer's address (street, city, ZIP) and phone_number.
Each order is identified by unique order_number. Each order is placed by a unique customer on a specific date. An employee is designated as the salesperson for each order.
A customer order may include orders for multiple products and installation services. For each product in an order, the quantity_ordered is recorded. For each installation_type ordered, the estimated number_of_hours for installation is recorded. Note that there may be orders that include products but no installation services. Similarly, there may be orders that include installation services but no products.
Specific Tasks:
1. Present an Entity-Relationship model that meets the above requirements. Try to ensure that your model has no many-to-many relationships.
2. Present a logical data model to meet the requirements where all the tables are in the third normal form. Specify all the attributes, primary keys and foreign keys of the tables. For each attribute specify the data type and domain.
3. Implement the database designed in step 2 using any DBMS of your choice. Populate the tables with sample data.
4. Formulate SQL queries for the following:
(i) For each salesperson, list the salesperson's Employee_ID, Name, supervisor's Employee_ID, and supervisor's name.
(ii) For each product list the Product_code, stock_level, and the total quantity ordered.
(iii) For each Order list the Order_number, order date, Employee_ID of salesperson, total amount for products, and total amount for installation. The total amount for products is the sum of the unit price times quantity of the products ordered. The total amount for installation is the sum of number_of_hours times the billing_rate of the installation types.
(iv) The revenue_generated by a branch is the sum of the total amount of all orders for salespersons working at that branch. The total amount of an order is given by the sum of the total amount for products and the total amount for installation. List the branch number, branch name, revenue_target, and the revenue_generated for each branch that fails to meet its revenue_target.
(v) List the list the Employee_ID and name of salespersons who have sold only to customers located in the same city as the city in which the salesperson's branch is located.
(vi) List the list the Employee_ID and name of salespersons who have sold to every customer located in the same city as the city in which the salesperson's branch is located.