Question

1. a database of employees that corresponds to the employee-payroll hierarchy is provided (see employees.sql to create the employees for a MySQL database). Write an application that allows the user to:
   1. Add employees to the employee table.
   2. 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
63182111. 1 is the entity-relationship diagram for the employees database

Figure 1: Table relationships in the employees database [1].

3. Add the following queries to Question 2:
   1. Select all employees working in Department SALES.
   2. Select hourly employees working over 30 hours.
   3. Select all commission employees in descending order of the commission rate.
   4. Increase base salary by 10% for all base-plus-commission employees.
   5. If the employee’s birthday is in the current month, add a $100 bonus.
   6. For all commission employees with gross sales over $10,000, add a $100 bonus.

salariedEmployees commissionEmployees 1 sSocialSecurityNumber socialSecurityNumber weeklySalary grossSales bonus commissionRate bonus employees 1 1 socialSecurityNumber 1 firstName 1 1astName birthday employeeType departmentName basePluscommissionEmployees hourlyEmployees 1 socialSecurityNumber socialSecurityNumber hours grossSales commissionRate wage baseSalary bonus bonus

DROP DATABASE IF EXISTS employees;

CREATE DATABASE employees;

USE employees;

DROP TABLE IF EXISTS salariedEmployees;
DROP TABLE IF EXISTS commissionEmployees;
DROP TABLE IF EXISTS basePlusCommissionEmployees;
DROP TABLE IF EXISTS hourlyEmployees;
DROP TABLE IF EXISTS 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');

INSERT INTO salariedEmployees VALUES ('111-11-1111', 2013.67, 0);

INSERT INTO commissionEmployees VALUES ('222-22-2222', 10100, 0.05, 0);

INSERT INTO basePlusCommissionEmployees VALUES ('333-33-3333', 5000, 0.04, 300, 0);

INSERT INTO hourlyEmployees VALUES ('444-44-4444', 30, 35.5, 0);

Answer 1

2.

a) SELECT * FROM employees WHERE departmentName = 'SALES';

b) SELECT * FROM hourlyEmployees WHERE hours > 30;

c) SELECT * FROM commissionEmployees ORDER BY commissionRate DESC;

d) UPDATE basePluscommissionEmployees UPDATE baseSalary = 1.1 * baseSalary;

NOTE: As per HOMEWORKLIB POLICY, I am allowed to answer only 4 questions (including sub-parts) on a single post. Kindly post the remaining questions separately and I will try to answer them. Sorry for the inconvenience caused.