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.)
4. Create the following the following queries:
a. List all the employees (Name, SSN) and their dependents (Name, Birthdate). (Make sure you list Employees who don't have dependents).
b. List the employees (Names) and the projects that they work on.
c. List the employees (Name, SSN) and their Manager (Names, SSN)
d. List the employee Name of who makes the most by department.
e. List the project Name which has the most hours logged.
f. List the employee Name who has the most dependents
g. List the employees, their departments, and their projects.
h. List the total number of hours worked per department. Show department Name in your results.