For the following prob l em, d efineanERO for t he initial requiremen t s and t hen revise the ERO f or the new requirements Your solu t ion s houldha ve aninit i al E RO , a revised ERO,anda li st of design decis i ons for each ERO Inperforming yo ur ana l ys i s, yo u ma y want t o follow t heapproach presented in Section 6 1 Des i gn a database t o assis t phys i cal plant personnel in managing assignments of keys t o employees The primary purpose of t hedatabase i s t o ensure proper accounting for all ke ys An employee has a unique employee number, a name, a position, andanop t ional office number A building ha s a unique building number, a name , anda location wi t hin t he campus A roomhasa room number, a size (p h ysica l dimen sions), a capac i ty, a numberofentrances,anda descript i on of equipment in the room Because each room is loca t edin exactlyonebuilding, the identification o f a roo m depends onthe identification of a building Key types (also knownas mas t er ke ys) are designed to open one ormore r ooms A r oom mayhave one or more ke y types tha t open it A key t ype has a unique key type number, adatedesigned,andthe employee authorizing the key type A key t ype mu st beau t horized before i t is created A copy of a key t ype i s known as a ke y Keys are assigned t o employees Each key is assigned t o exac t ly oneemployee, butan employee can hold multip l e ke ys The ke y type numberplusa copy number uniquely identif y a key Theda t e the copywas made should b e r ecorded in the database After r ev ie wingyour initial design, the physicalplan t supervisor decid es to r ev i se the requiremen t s Make a separa t e ERD t o s h owyour refinements Refine your original ERD t o support t he following new requiremen t s T he physicalplant supervisor needs t o know not only thecurren t holder of a keybutthepastho l ders of a ke y Forpast ke y holders, the date range t hat a key was held should ber eco rd ed T he physicalplant supervisor needs t o know t he current s t atus of each ke y in useby an employee, in storage, o r repor t edlost Iflost, the date repor t ed l os t should be s t ored

The Answer is in the image, click to view ...

Answered step by step

Verified Expert Solution

Link Copied!

Question

1 Approved Answer

Posted on Oct 22, 2024

For the following prob l em, d efineanERO for t he initial requiremen t s and t hen revise the ERO f or the new

Forthe following problem,defineanERO fortheinitial requirementsandthenrevise the EROforthe new requirements. Your solutionshouldhaveaninitial

ERO,a revised ERO,andalistof design decisionsfor each ERO.Inperformingyouranalysis,you maywantto followtheapproach presentedinSection 6.1.

Design a databaseto assistphysical plant personnel in managing assignments of keystoemployees. The primary purposeofthedatabaseisto ensure proper accounting forall keys.

An employee has a unique employee number, a name, a position, andanoptionalofficenumber.
A buildinghasa unique building number, aname,anda location withinthecampus.
A roomhasa room number, asize (physicaldimensions),a capacity, a numberofentrances,anda description of equipment in theroom.Because each room is locatedin exactlyonebuilding, theidentificationofaroom dependsonthe identificationofabuilding.
Keytypes (alsoknownasmasterkeys)are designed toopenone ormorerooms.Aroommayhaveone ormorekey typesthatopenit. A key
typehas a unique key type number, adatedesigned,andtheemployeeauthorizing the keytype.A keytypemustbeauthorized beforeitis created.
A copy of a keytypeisknown as akey.Keys are assignedtoemployees.Each key is assignedto exactlyoneemployee,butan employee can hold multiplekeys.Thekeytype numberplusa copy number uniquelyidentifya key. Thedate thecopywasmadeshouldberecordedin thedatabase.
Afterreviewingyourinitial design, the physicalplantsupervisordecidestorevisethe requirements.Make aseparate ERDtoshowyourrefinements. RefineyouroriginalERDtosupportthefollowing new requirements:
Thephysicalplantsupervisorneedstoknow not only thecurrentholder of a keybutthepastholdersofakey.Forpastkeyholders, the date rangethata keywasheldshouldberecorded.
Thephysicalplantsupervisorneedstoknowthecurrentstatusofeachkey:in useby anemployee,instorage, orreportedlost.Iflost, the datereportedlostshouldbestored.