RonKyle,

Your design still allows departments without managers. It's easy to create a one-to-one mapping using a table with two candidate keys:

CREATE TABLE Dept

(DeptCode INT NOT NULL, DeptName VARCHAR(50) NOT NULL, EmployeeNumber INT NOT NULL,

PRIMARY KEY (DeptCode),

UNIQUE (EmployeeNumber));

This doesn't ensure that every Manager belongs to a department however. To do that you would need a similar constraint on the Manager table but then you can't populate either table without disabling the constraint(s). The problem is that SQL lacks the ability to update multiple tables simultaneously (multiple assignment). Without multiple assignment SQL's ability to support multi-table constraints is severely limited.