(Employee Database) In Section 10 5, we introduced an employee payro

(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 week ly
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 Employee s social
SecurityNumber gross Sales commission Rate base Salary
bonus
Solution
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-june1993\’,\’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,salariedEmploye
es.weeklySalary,
FROM employees ,salariedEmployees
WHERE employees.socialSecurityNumber =
salariedEmployees.socialSecurityNu mber
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.
Name:
Description:
…