Any help with this project would be greatly appreciated and I will be sure to ra

Question

Any help with this project would be greatly appreciated and I will be sure to rate highly for all the help! I just want to turn in something for a grade but I don't know where to start. Any help at all please!

You are 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:

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:
define your entities
show all your attributes, including any keys
Show all your relations and name them accordingly
Define the correct cardinality for your relations.


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.


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


Create the following the following queries:


List all the employees (Name, SSN) and their dependents (Name, Birthdate). (Make sure you list Employees who don’t have dependents).
List the employees (Names) and the projects that they work on.
List the employees (Name, SSN) and their Manager (Names, SSN)
List the employee Name of who makes the most by department.
List the project Name which has the most hours logged.
List the employee Name who has the most dependents
List the employees, their departments, and their projects.
List the total number of hours worked per department. Show department Name in your results.

Upload the following files:

A VISIO IDEF1X or ER diagram.
A .doc, .txt, or .rtf document which includes your insert statements and queries.

Explanation / Answer

1.List all the employees (Name, SSN) and their dependents (Name, Birthdate). (Make sure you list Employees who don’t have dependents). SQL> Select e_name,SSN_number,birthdate From employee,dependent; 2.List the employees (Names) and the projects that they work on. SQL> Select e_name,P_name From employee e,projects p where e.SSN_Number=p.SSN_number; 3.List the employees (Name, SSN) and their Manager (Names, SSN) SQL> Select e_name,SSN_number,name as manager_name From employee,manager; 4.List the project Name which has the most hours logged. SQL> Select P_name,MAX(WORK_hour) as Most_hours From project; 5.List the employees, their departments, and their projects. SQL> Select e_name,dept_name,P_name From employee e,department d,project p where e.SSN_number=d.SSN_number and d.SSN_number==p.SSN_number ; 6.List the total number of hours worked per department. Show department Name in your results. SQL> Select dept_name,sum(work_hours) as number_of_hours From department order by dept_name;

Related Questions

Navigate

• Browse (All)
• Browse A
• Subjects

---

---

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