Week 4&5 Assignment Solution
1. Use the following following business business rules rules to create a Crow’s Crow’s Foot Foot ERD. Write Write all appropriate connectivities and cardinalities in the ERD. a. A depa depart rte ent nt epl eplo! o!s s an! an! epl eplo! o!ee ees" s" but but each each epl eplo! o!ee ee is eplo!ed b! one departent. b. #oe #oe epl eplo! o!ee ees" s" $now $nown n as %rov %rover ers" s"& & are are not not assi assign gned ed to an! an! departent. c. A divi divisi sion on oper operat ates es an! an! depa depart rte ent nts" s" but but each each depa depart rte ent nt is operated b! one division. d. An epl eplo! o!ee ee a! a! be assi assign gned ed an! an! pro' pro'ect ects" s" and and a pro' pro'ec ectt a! a! have an! eplo!ees assigned to it. e. A pro'ect pro'ect ust have have at least least one eplo!e eplo!ee e assigned assigned to it. f. (ne of the eplo!ees anages each depar partent" and each departent is anaged b! onl! one eplo!ee. g. (ne of the eplo!ees eplo!ees runs runs each division division"" and each division division is is run b! onl! one eplo!ee.
ERD #olution
As you discuss the ERD, note that this design refects several useul eatures that that eco ecome me es!e es!eci cial ally ly im!o im!ort rtan antt "hen "hen the the desi design gn is im!l im!lem emen ente ted# d# $or e%am!le
•
•
•
•
'he ASS()* entity is sho"n to e o!tional to the +R-E.'# 'his decision makes sense rom a !ractical !ers!ective, ecause it lets you create a ne" !ro/ect record "ithout having to create a ne" assignment record# 0( a ne" !ro/ect is started, there "ill not yet e any assignments#1 'he relationshi! e%!ressed y 2DE+AR'3E*' em!loys E3+EE6 is sho"n as mandatory on the E3+EE side# 'his means that a DE+AR'3E*' must have at least one E3+EE in order to have de!artmental status# 7o"ever, DE+AR'3E*' is o!tional to E3+EE, so an em!loyee can e entered "ithout entering a de!artmental $8 value# ( the e%istence o nulls is not acce!tale, you can create a 2*o assignment6 record in the DE+AR'3E*' tale, to e reerenced in the E3+EE tale i an em!loyee is not assigned to a de!artment# *ote also the im!lications o the 99 2E3+EE manages DE+AR'3E*'6 relationshi!# 'he fi! side o this relationshi! is that 2each DE+AR'3E*' is managed y one E3+EE6# 0'his latter relationshi! is sho"n as mandatory in the ERD# 'hat is, each de!artment must e managed y an em!loyee:1 'hereore, one o the E3+EE tale;s +8 values must a!!ear as the $8 value in the DE+AR'3E*' tale# 0 )ecause this is a 1*1 relationship" the inde+ propert! of the E,-/U, F0 in the DE-AR,E/ table ust be set to %uni2ue.& 1 Although you ought to a!!roach a 99 relationshi! "ith caution < most 99 relationshi!s are the result o a misidenti=cation o attriutes as entities < the 99 relationshi!s refected in the 2E3+EE manages DE+AR'3E*'6 and 2E3+EE runs D(S(S(*6 are a!!ro!riate# 'hese 99 relationshi!s avoid the data redundancies you "ould encounter i you du!licated em!loyee data < such a names, !hones, and e>mail addresses < in the D(?(S(* and DE+AR'3E*' entities#
Also, i you have multi!le relationshi!s et"een t"o entities >> such as the 2E3+EE manages DE+AR'3E*'6 and 2DE+AR'3E*' em!loys E3+EE6 relationshi!s < you must make sure that each relationshi! has a designated !rimary entity# $or e%am!le, the 99 relationshi! e%!ressed y 2E3+EE manages DE+AR'3E*'6 re@uires that the E3+EE entity e designated as the !rimary 0or 2=rst61 entity# ( you use ?isio to create your .ro";s $oot ERDs, $igure +4# sho" ho" the 99 relationshi! is s!eci=ed# ( you use some other .ASE tool, you "ill discover that it, too, is likely to re@uire similar relationshi! s!eci=cations#
3. United Helpers is a nonprofit organization that provides aid to people after natural disasters. Based on the following brief description of operations, create the appropriate fully labeled Crow’s Foot !". #ndividuals volunteer their ti$e to carry out the tas%s of the organization. For each volunteer, their na$e, address, and telephone nu$ber are trac%ed. ach volunteer $ay be assigned to several tas%s during the ti$e that they are doing volunteer wor%, and so$e tas%s re&uire $any volunteers. #t is possible for a volunteer to be in the
syste$ without having been assigned a tas% yet. #t is possible to have tas%s that no one has been assigned. 'hen a volunteer is assigned to a tas%, the syste$ should trac% the start ti$e and end ti$e of that assign$ent. For each tas%, there is a tas% code, tas% description, tas% type, and a tas% status. For e(a$ple, there $ay be a tas% with tas% code )*+*, description of )answer the telephone, a type of )recurring, and a status of )ongoing. -here could be another tas% with a code of )*+, description of )prepare /+++ pac%ages of basic $edical supplies, a type of )pac%ing, and a status of )open. For all tas%s of type )pac%ing, there is a pac%ing list that specifies the contents of the pac%ages. -here are $any different pac%ing lists to produce different pac%ages, such as basic $edical pac%ages, child care pac%ages, food pac%ages, etc. ach pac%ing list has a pac%ing list #" nu$ber, pac%ing list na$e, and a pac%ing list description, which describes the ite$s that ideally go into $a%ing that type of pac%age. very pac%ing tas% is associated with only one pac%ing list. 0 pac%ing list $ay not be associated with any tas%s, or $ay be associated with $any tas%s. -as%s that are not pac%ing tas%s are not associated with any pac%ing list. 1ac%ing tas%s result in the creation of pac%ages. ach individual pac%age of supplies that is produced by the organization is trac%ed. ach pac%age is assigned an #" nu$ber. -he date the pac%age was created, and total weight of the pac%age is recorded. 0 given pac%age is associated with only one tas%. 2o$e tas%s e.g., )answer the phones4 will not have produced any pac%ages, while other tas%s e.g., )prepare /+++ pac%ages of basic $edical supplies4 will be associated with $any pac%ages. -he pac%ing list describes the ideal contents of each pac%age, but it is not always possible to include the ideal nu$ber of each ite$. -herefore, the actual ite$s included in each pac%age should be trac%ed. 0 pac%age can contain $any different ite$s, and a given ite$ can be used in $any different pac%ages. For each ite$ that the organization provides, there is an ite$ #" nu$ber, ite$ description, ite$ value, and ite$ &uantity on hand stored in the syste$. 0long with trac%ing the actual ite$s that are placed in each pac%age, the &uantity of each ite$ placed in the pac%age $ust be trac%ed too. For e(a$ple, a pac%ing list $ay state that basic $edical pac%ages should include *++ bandages, 5 bottles of iodine, and 5 bottles of hydrogen pero(ide. However, because of the li$ited supply of ite$s, a given pac%age $ay include only *+ bandages, * bottle of iodine, and no hydrogen pero(ide. -he fact that this pac%age includes bandages and iodine needs to be recorded along with the &uantity of each that is included. #t is possible for the organization to have ite$s donated that have not been included in any pac%age yet, but every pac%age will contain at least one ite$.
United 3elpers ERD
This problem, however, does leave room for interesting discussion with the students regarding the need to verify requirements with the business users. In fact, getting una$biguous business rules can be one of the most difficult parts of the design process. In this problem, the potential for a relationship between the packing list (LIST and the items (IT!" stocked by the organi#ation can be a source for discussion. Students may envision that a LIST can specify many IT!"s and an IT!" can be specified in many LISTs. This would imply the need for a "$% relationship between IT!" and LIST. &owever, the business users may not intend for the packing list to be that specific. 'or eample, the packing list may specify that )* liter of iodine) should be included in a given type of package without specifying whether it should be two +liter bottles of iodine or four -ml bottles of iodine. %ote that )+liter bottle of iodine) and )-ml bottle of iodine) would have to be separate entity instances in IT!" because they have different values. If it is the case that the
packing list is intentionally generic in its description of the ideal contents, then a relationship between LIST and IT!" would not be appropriate.
