There is an MM Company, which purchases some parts from vendors to produce some products. It has several departments, ma

[answerhappygod]

There is an MM Company, which purchases some parts from vendorsto produce some products. It has several departments, marketingsites, and parts supply vendors in the company.
8) For each product in the company, the system needs to recordProduct ID, Product Type, Size, List Price, Weight, and Style.
9) There are many marketing sites for the company. For eachsite, Site ID, Site Name, and Site Location are recorded.
10) There are several people working for each site, andmeanwhile, one person can work on different sites. It is able totrack the details of each sale history --- salesmen, customers,product, sale time, and sites.
11) Part purchase is also a vital activity in the company. Thesystem needs to record each vendor’s Vendor ID, Name, Address,Account Number, Credit Rating, and Purchasing Web Service URL.
12) One vendor may supply many types of parts. The price of thesame part type may vary from different vendors but the price of onepart type of one vendor will keep same. It is able to track whichpart types used in each product and the number of each type of partused for the product.
13) In addition, the system maintains the information of eachemployee’s monthly salary which includes transaction number,pay_date, and amount (Note: transaction number could be same amongdifferent employees. However, for each employee, the transactionnumber is unique).
Project Questions
Project Exercises
I. Draw an EER to accurately represent this setof requirements. This will be your Conceptual Design. Clearlyspecify any assumption that you are making. You can use any tools(software) to draw the EER. You don’t need describe the valueconstraints of the attributions in the EER diagram. (25%)
II. Use a relational DBMS to implement thedatabase. Perform the following steps. (20%)
a) Convert yourConceptual model to a Logical model that can be implemented in arelational DBMS like Oracle. During this process you replace M-Nrelationships and multi-valued attributes with constructs that canbe implemented in the relational DBMS. Draw EER for the logicalmodel after your modifications. Feel free to change your conceptualmodel (first delivery) if needed.
b) Convert the EER toa database design. Document your design in Database Schema formatlike the one we discussed in the class.
III. Use appropriate naming conventions for allyour tables and attributes. (40%)
a) Normalize all yourtables to third normal form. Make any necessary changes to the EER.Explain why these changes needed to be made.
b) Draw a dependency diagram for each table
c) Write SQLstatements to create database, tables, and all other structures.Primary keys and foreign keys must be defined appropriately. Thequantity constraints of the relation between the entities, whichshould be described in EER diagram, are not required.
d) Use the Create View statement to create thefollowing views:
1) View1: This viewreturns the average salary each employee has earned from thecompany monthly after she/he becomes an employee in thecompany.
2) View2: This viewreturns the number of interviews rounds each interviewee pass foreach job position.
3) View3: This viewreturns the number of items of each product type sold.
4) View4: This viewreturns the part purchase cost for each product.
e) Answer thefollowing Queries. Feel free to use any of the views that youcreated in part (d).
1) Return the ID and Name ofinterviewers who participate in interviews where the interviewee’sname is “Hellen Cole” arranged for job “11111”.
2) Return the ID of all jobs which areposted by department “Marketing” in January 2011.
3) Return the ID and Name of theemployees having no supervisees.
4) Return the Id and Location of themarketing sites which have no sale records during March, 2011.
5) Return the job’s id and descriptionwhich does not hire a suitable person one month after it isposted.
6) Return the ID and Name of thesalesmen who have sold all product type whose price is above$200.
7) Return the department’s id and namewhich has no job post during 1/1/2011 and 2/1/2011.
8) Return the ID, Name, and DepartmentID of the existing employees who apply job “12345”.
9) Return the best seller’s type inthe company (sold the most items).
10) Return the product type whose netprofit is highest in the company (money earned minus the partcost).
11) Return the name and id of theemployees who has worked in all departments after hired by thecompany.
12) Return the name and email addressof the interviewee who is selected.
13) Retrieve the name, phone number,email address of the interviewees selected for all the jobs theyapply.
14) Return the employee’s name and idwhose average monthly salary is highest in the company.
15) Return the ID and Name of thevendor who supply part whose name is “Cup” and weight is smallerthan 4 pound and the price is lowest among all vendors.
IV. Document the final term project report.(15%)
a) Problem description
b) Project questions (Answer questions listedin this project).
c) EER diagram with all assumptions.
d) Relation schema after normalization. Allrelations must be in 3NF. The relation schema should includeprimary keys as well as foreign keys (if any) for allrelations.
e) All requested SQL statements.
f) Dependency diagram.