Note the link to the database is as follows http://oracle.ecpicollege.com:8080/a

Question

Note the link to the database is as follows
http://oracle.ecpicollege.com:8080/apex/f?p=4550:11:516714645771406::NO:::

user and password is the same: RicJac7604. Access is under SQL icon

Note: Section 1 ER design and section 2 have been done by Shirlee, Section 3 was done by Edan. Section 4 is what I need the final answers for. See SQL code already done for question 4 at bottom, (need remainder) (request Edan, or Shirlee) Thanks for all your help!

You were tasked to create an Oracle database for a company to track their employees and projects. After speaking with the customer, you have determined the following as the user’s needs:

•The company is organized into departments. Each department has a unique name, a unique number, and a particular employee who manages the department. We keep track of the start date when that employee began managing the department.
A department may have several locations.

•A department controls a number of projects, each of which has a unique name, a unique number, and a single location
•They store each employee’s name, social security number, address, salary, sex, and birth date. An employee is assigned to one department but may work on several projects, which are not necessarily controlled by the same department. We keep track of the number of hours per week than an employee works on each project. We also keep track of the direct supervisor of each employee.
•They want to keep track of the dependents of each employee for insurance purposes. We keep each dependent’s first name, sex, birth date, and relationship to the employee.
Please complete the following tasks:

1.Create an IDEFIX or ER diagram for the database described above. You should have entities for employee, department, project, and dependent at a minimum.
Make sure you:

a.define your entities
b.show all your attributes, including any keys
c.Show all your relations and name them accordingly
d.Define the correct cardinality for your relations.





2.Import your IDEF1X diagram into Oracle and create the appropriate tables and relationships. Your design should include a specification of tables, attributes, primary and foreign keys, referential integrity constraints, and referential integrity actions.



3.Populate your database with at least 3 rows of data in each table. (Use INSERT statements and show the insert statements that you used.)



?List the employee Name of who makes the most by department.
?List the employee Name who has the most dependents

Explanation / Answer

Include columns – dept_name in Project and no_of_dependents in Employee tables List the employee Name of who makes the most by department SELECT e_name FROM employee e, project p WHERE (SELECT MAX(DISTINCT(dept_name) FROM project) WHERE e.SSN_Number = p.SSN_Number); List the employee Name who has the most dependents SELECT e_name FROM employee WHERE (SELECT MAX(no_of_dependents) FROM employee);

Related Questions

Navigate

• Browse (All)
• Browse N
• Subjects

---

---

Integrity-first tutoring: explanations and feedback only — we do not complete graded work. Learn more.