Microsoft Office Tutorials and References
In Depth Information
Bonus field added to the Employee table
Figure A-8
Employee
EmployeeNum
FirstName
LastName
DeptNum
Bonus
nulls for
these Bonus
field values
2173
Hennessey
27
Barbara
4519
Lee
Noordsy
31
8005
Amidon
27
Pat
Bonus field
value for the
fourth record
8112
Chris
Wandzell
24
$20,000
Figure A-9 shows an alternative approach, in which the Bonus field is placed in a sepa-
rate table, the EmployeeBonus table. The EmployeeBonus table’s primary key is the
EmployeeNum field, and the table contains one row for each employee earning a bonus.
Because some employees do not earn a bonus, the EmployeeBonus table has fewer rows
than the Employee table. However, each row in the EmployeeBonus table has a matching
row in the Employee table, with the EmployeeNum field serving as the common field; the
EmployeeNum field is the primary key in the Employee table and is a foreign key in the
EmployeeBonus table.
Storing bonus values in a separate table, an entity subtype
Figure A-9
Employee
primary key
(Employee table)
EmployeeNum
FirstName
LastName
DeptNum
2173
Barbara
Hennessey
27
4519
Noordsy
31
Lee
8005
Pat
Amidon
27
8112
Wandzell
24
Chris
primary key
(EmployeeBon us
table)
EmployeeBonus
EmployeeNum
Bonus
entity subtype
foreign key
8112
$20,000
The EmployeeBonus table, in this situation, is called an entity subtype , a table whose
primary key is a foreign key to a second table and whose fields are additional fields for the
second table. Database designers create an entity subtype in two situations. In the first situ-
ation, some users might need access to all employee fields, including employee bonuses,
while other employees might need access to all employee fields except bonuses. Because
most database management systems allow you to control which tables a user can access,
you can specify that some users can access both tables and that other users can access the
Employee table but not the EmployeeBonus table, keeping the employee bonus informa-
tion hidden from the latter group. In the second situation, you can create an entity subtype
when a table has fields that could have nulls, as was the case for the Bonus field stored in
the Employee table in Figure A-8. You should be aware that database experts are currently
debating the validity of the use of nulls in relational databases, and many experts insist that
you should never use nulls. This warning against nulls is partly based on the inconsistent
way different RDBMSs treat nulls and partly due to the lack of a firm theoretical foundation
for how to use nulls. In any case, entity subtypes are an alternative to the use of nulls.
Search JabSto ::




Custom Search