- 1 (91.86 KiB) Viewed 43 times
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 schemaafter normalization. All relations must be in 3NF. The relationschema should include primary keys as well as foreign keys (if any)for all relations.
e) All requested SQL statements.
f) Dependency diagram.
name name deptid 1 입 department 1 ㅍ supervisor phone has work at 1 rank id lastname employee title firstname people d customer city gender address salesperson age state potential employee zip name