Re: SQL Database design question

>> From Dan's original post "they [the employees] should be ab­le to
work at more than one branch". The JobAssignments table would the­refore represents a many-to-many relationship. That was my interpre­tation anyway. <<

My interpre­tation was that an employee is always at one branch, which makes physical sense, but we can move him by updating his assignment. If we want to have him in multiple branches, we could change the keys on JobAssignments and use

CREATE TABLE JobAssignments
(ssn CHAR(9) NOT NULL
REFERENCES Personnel (ssn)
ON UPDATE CASCADE
ON DELETE CASCADE,
branch_id INTEGER NOT NULL
REFERENCES Branches (branch_id)
ON UPDATE CASCADE
ON DELETE CASCADE,
PRIMARY KEY (ssn, branch_id));

Then use a DISTINCT in the assertion.

CREATE ASSERTION Everyone_assigned_at_least_once CHECK ((SELECT COUNT(DISTINCT ssn) FROM JobAssignments) = (SELECT COUNT(ssn) FROM Personnel)); Received on Sat Jan 29 2005 - 23:58:18 CET