INFO210_HW5_Solution

This is an example of a many to one relationship set

Info iconThis preview shows page 1. Sign up to view the full content.

View Full Document Right Arrow Icon
This is the end of the preview. Sign up to access the rest of the document.

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 many-to-one 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 one-to-one 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

Ask a homework question - tutors are online