(Employee Database) In Section 10.5, we introduced an employee-payroll hierarchy

Question

(Employee Database) In Section 10.5, we introduced an employee-payroll hierarchy to calculate each employee’s payroll. In this exercise, we provide a database of employees that corresponds to the employee-payroll hierarchy. (A SQL script to create the employees database is provided with the examples for this chapter.) Write an application that allows the user to:

a) Add employees to the employee table.

b) Add payroll information to the appropriate table for each new employee.

For example, for a salaried employee add the payroll information to the salariedEmployees table. Figure 24.33 is the entity-relationship diagram for the employees database.

If i can get something without errors that will be great. Thanks

salaried Employees social SecurityNumber weekly Salary bonus hourlyEmployees social SecurityNumber hours wage bonus employees social SecurityNumber firstName last Name birthday employee Type departmentName commission Employees n social Security Number gross Sales commission Rate bonus ission Employees social SecurityNumber gross Sales commission Rate base Salary bonus

Explanation / Answer

Tables creation :
=================
Employees :
----------
create table employees
(
socialSecurityNumber varchar2(20) primary key,
firstName varchar2(20),
lastname varchar2(20),
birthday date,
employeeType varchar2(20),
departmentName varchar2(20)
);

salariedEmployees:
-----------------
create table salariedEmployees
(
socialSecurityNumber varchar2(20) FOREIGN KEY (socialSecurityNumber) REFERENCES Employees(socialSecurityNumber),
weeklySalary number(10),
bonus number(10)
);

commissionEmployees:
-------------------
create table commissionEmployees
(
socialSecurityNumber Varchar2(20) FOREIGN KEY (socialSecurityNumber) REFERENCES Employees(socialSecurityNumber),
grassSales number(10),
commisionRate number(10),
bonus number(10)
);

hourlyEmployees:
----------------
create table hourlyEmployes
(
socialSecurityNumber Varchar2(20) FOREIGN KEY (socialSecurityNumber) REFERENCES Employees(socialSecurityNumber),
hours number(10),
wage number(10),
bonus number(10)
);

basePluscommissionEmployees:
----------------------------
create table basePluscommissionEmployees
(
socialSecurityNumber Varchar2(20) FOREIGN KEY (socialSecurityNumber) REFERENCES Employees(socialSecurityNumber),
grossSales number(10),
commissionRate number(10),
baseSalary number(10),
bonus number(10)
);

Insearting data into a tables:
==============================

Employees:
---------
insert into Employees
(
socialSecurityNumber ,
firstName ,
lastname ,
birthday ,
employeeType ,
departmentName
)
values('123','ravi','teja','18-june-1993','salariedEmployees','dovelopment');

salariedEmployees:
------------------
insert into salariedEmployees
(
socialSecurityNumber ,
weeklySalary ,
bonus
)
values('123',5000,3000);

commissionEmployees:
-------------------
insert into commissionEmployees
(
socialSecurityNumber ,
grassSales ,
commisionRate ,
bonus
)
values('123',12000,5000,3000);

hourlyEmployees:
----------------
insert into hourlyEmployes
(
socialSecurityNumber ),
hours,
wage ,
bonus   
)
values('123',7,1000,3000);

basePluscommissionEmployees:
----------------------------
insert into basePluscommissionEmployees
(
socialSecurityNumber ,
grossSales ,
commissionRate ,
baseSalary ,
bonus
)
values('123',15000,3000,6000,2000);

Establishing a connection between two tables:
==============================================
connection between Employee table to salariedEmployee table:
--------------------------------------------------------------
SELECT employees.fristName,employees.fristName,salariedEmployees.weeklySalary,   
FROM employees ,salariedEmployees
WHERE employees.socialSecurityNumber = salariedEmployees.socialSecurityNumber
AND salariedEmployees.bonus=3000;

* from all my explanation is done using the ER diagram as you given like that you insert
the data and then connection is establish between the tables using the above querry.

Related Questions

Navigate

• Browse (All)
• Browse #
• Subjects

---

---

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