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 answer for. (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.



Department
(
dept_name varchar (15) NOT NULL
dept_number Number (5) NOT NULL
Primary key (dept_name, dept_number)
);

Employee
(
ename varchar (10) not null
SSN Number (10) not null
address varchar (50)
salary number (10) not null
sex varchar (10)
date_of_birth Date not null
Primary key (SSN)
);


Manager
(
Id Number (10) not null
Name varchar (10) not null
Dept_name varchar (15) NOT NULL references department
Dept_number Number (5) NOT NULL references department
Start_date date not null
Primary key (id)
);

Dependent
(
First_name varchar(10) Not Null
Sex varchar(10) Not Null
SSN Number (10) References employee
Birth_date Date Not Null
Relationship varchar(10) Not Null
);

Project
(
P_name varchar (10) Not Null
P_number number (10) Not Null
SSN Number (10) References employee
Location_name references location
Primary key (P_name,P_number)
);


Supervisor
(
S_name varchar (10) Not Null
Dept_name varchar (15) NOT NULL references department
Dept_number Number (5) NOT NULL references department
);

Location
(
Location_name varchar (10) NOT Null
Dept_name varchar (15) NOT NULL references department
Dept_number Number (5) NOT NULL references department
);




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.)


Department:
SQL> insert into department values ('CSE', 101);
SQL> insert into department values ('IT', 102);
SQL> insert into department values ('ECE, 103);
SQL> insert into department values ('EEE, 104);
Employee:
SQL> insert into employee values ('abc', 201,’xyz’,10000,’male’,’01-01-2001’);
SQL> insert into employee values ('cde’, 202,’pqr’,50000,’female’,’01-01-2004’);
SQL> insert into employee values ('fgh', 203,’stu’,40000,’male’,’01-01-1984’);
SQL> insert into employee values ('sfda', 204,’sfdu’,80000,’male’,’01-01-1980’);
Manager:
SQL> insert into manager values (301,’junior’,’CSE’,101,’01-01-2001’);
SQL> insert into manager values (302,’senior’,’IT’,102,’01-01-2010’);
SQL> insert into manager values (303,’manager’,’ECE’,103,’01-01-2009’);
SQL> insert into manager values (304,’assistant’,’EEE’,104,’01-01-2000’);
Dependent:
SQL> insert into dependent values (’pqr’,’male’,1001,’01-01-2001’,’father’);
SQL> insert into dependent values (‘stu’,’female’,1002,’01-01-2010’,’mother’);
SQL> insert into dependent values (‘vwz’,’male’,1003,’01-01-20011’,’brother’);
SQL> insert into dependent values (‘hgf’,’male’,1004,’01-01-2000’,’sister’);
Project:
SQL> insert into project values (’edu’,7001,1001,’USA’);
SQL> insert into project values (’com’,7002,1002,’kuwit’);
SQL> insert into project values (’org’,7003,1003,’U.K’);
SQL> insert into project values (’info’,7004,1004,’U..S.A’);
Supervisor:
SQL> insert into supervisor values (’man’,’IT’,102);
SQL> insert into supervisor values (’women’,’CSE’,101);
SQL> insert into supervisor values (’Oldman’,’ECE’,103);
SQL> insert into supervisor values (’ man’,’EEE’,104);
Location:
SQL> insert into location values (’U.S.A’,’IT’,102);
SQL> insert into location values (’kuwit’,’CSE’,101);
SQL> insert into location values (’U.K’,’ECE’,103);
SQL> insert into location values (’U.K’,’EEE’,104);




4.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.

Explanation / Answer

4)

a) 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;

b) 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;

c)  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 ;

Related Questions

Navigate

• Browse (All)
• Browse N
• Subjects

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