EXPERT RESPONSE
What happens is that the foreign key is composite too.
This can be a good or bad thing, depending on your point of view
and on the nature of the various queries you'll be running.
Consider the following example:
DIVISION
DVID DVNAME
ex Executive
mk Marketing
hr Human Resources
it Information Technology
DEPARTMENT
DVID DPID DVNAME
ex ex Executive
ex st Executive Staff
hr pr Payroll
hr pl Personnel
hr mg Management
it dv Development
it pr Processing
it mg Management
You can see that DVID and DPID together
are a composite primary key for the DEPARTMENT table, with
the DVID being a foreign key to the DIVISION table.
Each value of DVID occurs only once in the DIVISION table,
but multiple times in the DEPARTMENT table, reflecting the
one-to-many relationship. DPID by itself is not a suitable
primary key for DEPARTMENT because there are duplicates --
there's a 'mg' department in the 'hr' division, and another
'mg' departmentin the 'it' division.
Let's add a third table.
SECTION
DVID DPID SCID SCNAME
it dv an Analysis
it dv pr Programming
it pr de Data Entry
it pr op Operations
it mg sm Section Managers
it mg pl Project Leaders
The SECTION table has a composite primary key
consisting of three columns. DVID and DPID together
are a composite foreign key to the DEPARTMENT table.
At this point, you can predict what the foreign key
in the EMPLOYEE table is going to look like, to relate
employees to the sections they belong to -- a composite
foreign key consisting of three columns.
For some variation, let's use a different primary key
for the EMPLOYEE table.
EMPLOYEE
EMPNO EMPNAME DVID DPID SCID
024 J.Programmer it dv pr
027 T.Keypunch it pr de
034 P.H.Boss it mg sm
It is important to emphasize that the values of the keys should never be revealed to users
of the database. Keys which are codes are often natural keys,
and they make excellent primary and foreign keys.
In these examples, however, DVID, DPID, and SCID
aren't particularly natural, and could just as easily be autonumbers.
Just because keys in some application systems are exposed,
especially when they are seemingly meaningful codes such as
the ones in this example, doesn't make it right to expose
keys that aren't natural keys. But this is actually a different
debate from the discussion of composite keys, to which
I shall now return.
A major advantage of the composite key structure
is that because of the transitive nature of relations,
you can see that employees are related
not just to their sections, but also to their
departments and divisions, without the
need to declare additional foreign keys -- you could
declare them, but you don't need to.
Why is this an advantage? To find all the employees
in the Information Technology Division, you do not
have to have a four-way join in your query, you can just use --
select EMPNO, EMPNAME
from EMPLOYEE
where DVID = 'it'
A disadvantage of composite keys is the ripple effect
of changing key values. A Section moved to
a new Department would require a change to the DPID
value in its foreign key -- but DPID is also part
of the SECTION primary key! So, the change
would also have to be propagated
to all EMPLOYEE rows related to that Section,
since the EMPLOYEE composite foreign key
also includes DPID. If Departments and Sections are frequently
reorganized, composite keys do lose some of their appeal.
Part 2
|
