ITEC 340 – Spring 2005

Homework 8

NOTE: Do all of the following by constructing scripts that you can hand in.

Construct the following tables on your account:

Positions

Position	MinAllowedSalary	MaxAllowedSalary
Checker	20000	30000
Bagger	15000	20000
Butcher	35000	45000
Manager	45000	80000

Employees

EmpID	Lname	Fname	Position	Salary
101	Smith	Jane	Manager	55000
102	Thompson	Betty	Butcher	42000
103	Jones	Tom	Checker	21000
104	Rhimes	Rhonda	Bagger	15000
105	Lake	Rickie	Checker	24000
106	Tierney	Tirsha	Bagger	16000
107	Lowdermilk	Barbara	Checker	20000

SalaryChanges

EmpID

ChangeDate

OldSalary

NewSalary

ChangedBy

Create a sequence which starts at 108 and increments by 1

Write a trigger or triggers which do the following:

1) Disallows any transaction that would place an employees salary at a point not in the inclusive range defined in the positions table.

2) Records an entry into the SalaryChanges table any time an employee’s salary changes.

3) Automatically assigns an employee number (using the sequence you created) to any new employee that is inserted.

Create an RU08 folder in your ITEC 340 submissions folder on Neelix and submit a script that creates your tables, populates them, creates your sequence, and creates your trigger(s). Submit these by 5:00pm on Wednesday, April 6th.