Complete Exercise 24.4 (Employee Database).

Submit a screenshot of your compiled working application and your .java source files for grading. The attached SQL file will be used with this exercise.

24.4(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.

sql-

DROP DATABASE IF EXISTS employees;

CREATE DATABASE employees;

USE employees;

CREATE TABLE employees (
socialSecurityNumber varchar (30) NOT NULL,
firstName varchar (30) NOT NULL,
lastName varchar (30) NOT NULL,
        birthday date NOT NULL,
employeeType varchar (30) NOT NULL,
departmentName varchar (30) NOT NULL,
PRIMARY KEY (socialSecurityNumber)
);

CREATE TABLE salariedEmployees (
socialsecurityNumber varchar (30) NOT NULL,
weeklySalary real NOT NULL,
bonus real,
INDEX (socialSecurityNumber),
FOREIGN KEY (socialSecurityNumber) REFERENCES employees (socialSecurityNumber)
);

CREATE TABLE commissionEmployees (
socialSecurityNumber varchar (30) NOT NULL,
grossSales int NOT NULL,
commissionRate real NOT NULL,
bonus real,
INDEX (socialSecurityNumber),
FOREIGN KEY (socialSecurityNumber) REFERENCES employees (socialSecurityNumber)
);

CREATE TABLE basePlusCommissionEmployees (
socialSecurityNumber varchar (30) NOT NULL,
grossSales int NOT NULL,
commissionRate real NOT NULL,
baseSalary real NOT NULL,
bonus real,
INDEX (socialSecurityNumber),
FOREIGN KEY (socialSecurityNumber) REFERENCES employees (socialSecurityNumber)
);

CREATE TABLE hourlyEmployees (
socialSecurityNumber varchar (30) NOT NULL,
hours int NOT NULL,
wage real NOT NULL,
bonus real,
INDEX (socialSecurityNumber),
FOREIGN KEY (socialSecurityNumber) REFERENCES employees (socialSecurityNumber)
);

INSERT INTO employees VALUES ('111-11-1111', 'John', 'Smith', '1945-1-2', 'salariedEmployee', 'R&D');

INSERT INTO employees VALUES ('222-22-2222', 'Sue', 'Jones', '1961-2-3', 'commissionEmployee', 'SALES');

INSERT INTO employees VALUES ('333-33-3333', 'Bob', 'Lowis', '1958-10-5', 'basePlusCommissionEmployee', 'SALES');

INSERT INTO employees VALUES ('444-44-4444', 'Karen', 'Price', '1972-5-25', 'hourlyEmployee', 'HR');

"Get 15% discount on your first 3 orders with us"
Use the following coupon
"FIRST15"

Order Now