This preview shows page 1. Sign up to view the full content.
Unformatted text preview: ry key in the table Bag), and that a
participation constraint holds over Bag (because A, a foreign key relating Bag
and Shelf, is not null).
Intuitively, this ER diagram represents a relationship set like: a Bag is placed on
a Shelf; each Bag can be found on exactly one Shelf, and a Shelf may hold any
number of bags (0, 1 or several). This is an example of a manytoone
relationship set. A$ B$ C$ SHELF$ D$
some_rel$ BAG$ (b) Consider create table statements below. Draw and ER diagram from which these create table statements could have been derived. Be sure to mark any key
and participation constraints. create t able Pocket (
X
number p rimary k ey ,
Y
number n ot n ull u nique ,
Z
number,
f oreign k ey (Y) r eferences Pocket(Y)
); c reate t able Drawer (
Y number p rimary k ey
); Solution: These create table statements model a onetoone relationship set
relating Pocket and Drawer. Note that both key and participation constraints
hold over the entity set Pocket: key constraint because X is a primary key in
Pocket, and participation because Y in Pocket is not null. A key constraint holds
over the entity set Drawer, because Y is designated as unique in Pocket. No
participation constraint holds over Drawer: a Drawer not related to a Pocket is
one for which there is a tuple in Drawer, but its Y value does not appear in the
table Pocket.
There is no good intuitive interpretation of this relationship set, perhaps
something like: the contents of a Drawer can be placed into at most one Pocket
(some drawers are too large and so their contents does not fit into any pocket).
Each pocket is designated to hold the contents of exactly one drawer.
X& Z& POCKET& Y&
some_rel& DRAWER& (c) Draw an ER diagram that correctly represents the following business rules. A small family is made up of exactly one mother, exactly one father and at most
one child. You may assume that each family member is uniquely identified by his
or her name.
Solution: This was a trick question. You may be tempted to draw the following
diagram, which is incorrect. For this diagram to be correct, the specification
would have to read as fol...
View Full
Document
 Winter '13
 Stoy

Click to edit the document details