Caution: Carefully read the whole project and design your tables and processing before starting to implement. E-mail me with any questions.

Set up a payroll table containing information about the employee and their pay rate. We will do a simplified table with some of the usual fields not used - on this basic table, you need to have employee id, name, address, type of employee (code for full, part-time), code to check whether salaried or hourly, salary, pay per hour, sick time used, vacation time used, year-to-date gross pay.

Set up a transaction table where weekly transactions come in having to do with hours worked, overtime hours worked, sick time used, vacation time used. The file should have the employee id, the date of the transaction, the type of transaction (a code to indicate hours worked, overtime hours worked, sick time hours used this week, vacation time hours used this week), and a field for the hours. Note that there can be multiple transactions for a person or no transactions for a person if they are salaried and did not work overtime, take sick time or use vacation time.

• You need to establish the appropriate keys.
• You need to establish a constraints on the transaction file. Extra credit if you also put constraints on the payroll table.

• Using PL/SQL and cursors, process all transactions making changes to the payroll table to update sick time used, vacation time used and year-to-date-gross.
• For salaried employees their base salary is given on the payroll table. Their overtime rate is carried in the pay per hour field for their record. Multiple any overtime hours by this rate and add it to their salary before updating the year-to-date gross pay field.
• For part-time workers their basic pay per hour is stored in the pay per hour field on the payroll table (there is no entry in the salary field for part time workers). If they work overtime, they get time and one half for the overtime hours. Calculate their pay for the week and use it to update the year-to-date gross pay field.

• Write another program to process the data in a way of your choosing.
• In one of these programs (or you can write more), you must do something with:
• Exceptions
• Procedures
• Functions
• Triggers.