Duane said:
"Reason for leaving" is not an attribute of employee. This would be an
attribute of an employee termination.
You can set up constraints based on referential integrity and business
rules.
With your model, I don't think I can. But I'm willing to try. I assume
you mean *you* could come up with the required constraints, so if I
promise to try my best, will you fill in the gaps for me, please?
You implied a table of EmployeeTerminations, so we need tables for
Employees and Terminations.
I'll start with the easy one: Terminations. We can use a surrogate key
as the primary key (PK). A description is required and we don't want
duplicates:
CREATE TABLE Terminations (
termination_ID INTEGER NOT NULL PRIMARY KEY,
termination_description VARCHAR(100) NOT NULL UNIQUE,
CHECK (LEN(termination_description) > 0)
)
;
INSERT INTO Terminations (termination_ID, termination_description)
VALUES (1,'Pay')
;
INSERT INTO Terminations (termination_ID, termination_description)
VALUES (2,'Argumnet')
;
INSERT INTO Terminations (termination_ID, termination_description)
VALUES (3,'Retired')
;
INSERT INTO Terminations (termination_ID, termination_description)
VALUES (4,'Died')
;
Next is Employees. We are required to use social security number (SSN)
as a key. I don't know what a SSN looka like in your region, so change
my LEN(ssn)>9 for something more appropriate <g>. We hit the first
issue: given the existence of an EmployeeTerminiations table, is
employment_end_date an attribute of an employee or a termination? Let's
put it in the Employees table for now:
CREATE TABLE Employees (
ssn CHAR(9) NOT NULL PRIMARY KEY,
CHECK(LEN(ssn) = 9),
employment_start_date DATETIME DEFAULT DATE() NOT NULL,
employment_end_date DATETIME,
CHECK (employment_start_date <= employment_end_date)
)
;
EmployeeTerminations: call it what you will - a relationship table,
junction table, lookup table, etc - I prefer 'relationship table'. It
holds a foreign key (FK) from other tables but we shouldn't forget to
give it its own PK to avoid duplicate relationships:
CREATE TABLE EmployeeTerminations (
ssn CHAR(9) NOT NULL,
termination_ID INTEGER NOT NULL,
CONSTRAINT pk__eeterm PRIMARY KEY (ssn, termination_ID),
CONSTRAINT fk__eeterm__ee FOREIGN KEY (ssn)
REFERENCES Employees (ssn)
ON UPDATE CASCADE ON DELETE CASCADE,
CONSTRAINT fk__eeterm__term FOREIGN KEY (termination_ID)
REFERENCES Terminations (termination_ID)
ON UPDATE CASCADE
ON DELETE CASCADE
);
Let's hire someone:
INSERT INTO Employees (ssn) VALUES ('123456789');
We have another issue here: because we have split the 'reason for
leaving' attribute off from the employee, we have no constraint to
prevent us from creating EmployeeTerminations for this employee. We
need to make alterations, which will also address our earlier issue:
ALTER TABLE Employees
ADD CONSTRAINT uq__ee__ssn__end
UNIQUE (ssn, employment_end_date)
;
ALTER TABLE EmployeeTerminations
ADD employment_end_date DATETIME NOT NULL)
;
ALTER TABLE EmployeeTerminations
DROP CONSTRAINT fk__eeterm__ee
;
ALTER TABLE EmployeeTerminations
ADD CONSTRAINT fk__eeterm__ee
FOREIGN KEY (ssn, employment_end_date)
REFERENCES Employees (ssn, employment_end_date)
ON UPDATE CASCADE ON DELETE CASCADE
;
By making employment_end_date NOT NULL in EmployeeTerminations, we
enforce the rule that an employee must have left employment to have
rows in EmployeeTerminations
OK, let's make our employee a leaver:
UPDATE Employees
SET employment_end_date = DATE()
WHERE ssn = '123456789'
;
Right, here's problem number one: if I take no further action I have
a leaver with no corresponding rows in EmployeeTerminations. Our
business rule that at least one reason for leaving must be recorded for
a leaver remains unenforced. Suggested fix here, please.
OK, proceeding to record reasons for leaving, we note that the employee
had an argument relating to pay:
INSERT INTO EmployeeTerminations
(ssn, employment_end_date, termination_ID)
VALUES ('123456789', DATE(), 1)
;
INSERT INTO EmployeeTerminations
(ssn, employment_end_date, termination_ID)
VALUES ('123456789', DATE(), 2)
;
INSERT INTO EmployeeTerminations
(ssn, employment_end_date, termination_ID)
VALUES ('123456789', DATE(), 3)
;
INSERT INTO EmployeeTerminations
(ssn, employment_end_date, termination_ID)
VALUES ('123456789', DATE(), 4)
;
And there we have problem number two: while Argument + Pay is a legal
combination, + retirement + death are clearly violations. How do we
fix this one, please?
Jamie.
--
