Database Reference
In-Depth Information
EmpId
ProjId
1,1
SocSecNo
*
ProjDesc
assigned
to
EMPLOYEE
PROJECT
ProjStart
EmpName
Relational Notation
EMPLOYEE ( EmpId , SocSecNo, EmpName, EmpAddr, ProjId)
ProjCost
EmpAddr
Foreign Key: ProjId REFERENCES PROJECT, NOT NULL
PROJECT ( ProjId , ProjDesc, ProjStart, ProjCost)
EMPLOYEE relation
EmpId
SocSecNo
EmpName
EmpAddr
ProjId
1234
101-38-2121 Daniel R. Grady
6 William Street, Jamesburg, NJ 08810
DESN
2345
320-55-9641 Alice M. Knox
1 Bradford Place, Iselin, NJ 08834
IMPL
3456
333-22-1111
Matthew Rader
47 Woods Drive, Edison, NJ 08817
55 Walker Lane, Metuchen, NJ 08819
DESN
4567
123-47-3257 Ramola Ruskin
DESN
PROJECT relation
ProjId
ProjDesc
ProjStart
ProjCost
DESN
DB Design
6/1/2000
200,000
IMPL
DB Implementation
12/15/2000
100,000
USER
User Training
10/22/2000
75,000
TUNE
DB Fine Tuning
3/1/2001
50,000
Figure 9-16
One-to-many relationship: mandatory and optional.
key column? In a mandatory relationship, the foreign key column cannot contain
null values. Observe the Foreign Key statement under relational notation in the
figure. It stipulates the constraints with the words “NOT NULL” expressing that
null values are not allowed in the foreign key attribute.
Next, consider the optional condition. Suppose the cardinality indicators (0,1)
are shown next to the PROJECT object set. Then the indicators will represent the
following conditions:
An employee can be assigned to a maximum of only one project. Not every
employee need be assigned to a project. That is, some employee instances may
not be associated with any project instance at all. At a minimum, an employee
instance may be associated with no project instance or with zero project
instances. In other words, not every employee instance needs to participate in
the relationship. The relationship as far as the employee instances are con-
cerned is optional.
It follows, therefore, that in an optional relationship of this sort, null values may
be allowed in the foreign key attribute. What do the rows with null foreign key
attribute in the EMPLOYEE relation represent? These rows represent those
employees who are not assigned to a project.
Minimum Cardinality in Many-to-Many Relationship Figure 9-17 shows an
example of many-to-many relationship between the two object sets PROJECT and
EMPLOYEE.
Search WWH ::




Custom Search