(Solved) : Mmis 630 Project Requirements Need Help Creating Entity Relationship Model Meets Requireme Q37187732 . . .
MMIS 630 Project Requirements
I need help with creating an Entity-Relationship modelthat meets the above requirements, Present a logicaldata model to meet the requirements where all the tables are in thethird normal form. Specify all the attributes, primary keys andforeign keys of the tables. For each attribute specify the datatype and domain. Implement the database designed in step 2 usingany DBMS of your choice. Populate the tables with sampledata.
A company that sells and installs flooring needs a database withthe requirements specified below. Your task is to design andimplement a relational database that meets all theirrequirements.
- Please let me know if you need further clarifications on any ofthe requirements.
- If you need to make any other assumptions, please state yourassumptions explicitly.
Requirements:
The company has several branchoffices. Each branch is identified by a branch number. The name,address (street, city, ZIP), and revenue target of each branchoffice are maintained. Each branch has a designated branch managerand the branch manager’s employeeID is maintained.
Each employee has a unique employeeID.The company maintains the first name, last name, designation,starting date, branch number, salary, and supervisor’s employeeIDfor each employee.
Each product sold by the company isidentified by unique product code. The description, unit price, andstock level of each product are maintained.
Each type of installation performed bythe company is identified by a unique installation type. Thedescription and the billing rate per hour for the installation typeare maintained.
Customers are identified by uniquecustomerID. Information is maintained on each customer’s address(street, city, ZIP) and phone number.
Each order is identified by a uniqueorder number. Each order is placed by a unique customer on aspecific date. An employee is designated as the salesperson foreach order.
A customer order may include ordersfor multiple products and installation services. For each productin an order, the quantity ordered is recorded. For eachinstallation type ordered, the estimated number of hours forinstallation is recorded. Note that there may be orders thatinclude products but no installation services. Similarly, there maybe orders that include installation services but no products.
Specific Tasks:
- Present an Entity-Relationship model that meets the aboverequirements.
- Present a logical data model to meet the requirements where allthe tables are in the third normal form. Specify all theattributes, primary keys and foreign keys of the tables. For eachattribute specify the data type and domain.
- Implement the database designed in step 2 using any DBMS ofyour choice. Populate the tables with sample data.
- Formulate SQL queries for the following so that they producecorrect results for all valid instances of the database:
- For each employee who has received an order, list theemployee’s employeeID, name, supervisor’s employeeID, andsupervisor’s name.
- List the product code, stock level, and the total quantityordered for those products where the total quantity ordered is morethan 80% of the stock level.
- For each Order list the order number, order date, employeeID ofsalesperson receiving the order, the total amount for products, andthe total amount for installation. The total amount for a productis the sum of the unit price times the quantity ordered. The totalamount for installation is the sum of the billing rates times thehours required for installation.
- The revenue generated by a branch is the sum of the totalamount generated by all orders received by salespersons working atthat branch. The total amount of an order is given by the sum ofthe total amount for products and the total amount forinstallation. List the branch number, branch name, revenue target,and the revenue generated for each branch that meets its revenuetarget.
- List the list the employeeID and name of salespersons who havesold exclusively to customers located in the same state as thestate in which the salesperson’s branch is located.
- List the employeeID and name of salespersons who have sold toevery customer located in the same state as the state in which thesalesperson’s branch is located.
Expert Answer
Answer to MMIS 630 Project Requirements I need help with creating an Entity-Relationship model that meets the above requirements, …
OR