Exercise 2
1.
A renta entall car agen agenc cy clas classi sif fes the vehi vehicl cles es it rents ents into into our our cat catego egories ries:: compact, midsize, ull-size, ull-size, and sport utility. utility. The agency wants to record record the ollowing data or all vehicles: Veh!", Veh!", #a$e, #odel, %ear, %ear, and &olor. &olor. There are no uni'ue attri(utes or or any o the our classes o vehicle. vehicle. The entity type vehicle has a relationship relationship )named *ents+ with with a customer entity type. one o the our vehicle classes has a uni'ue relationship with an entity type. ould ould you you cons consid ider er crea creati ting ng a supe supert rtyp ype esu( su(ty type pe relat elatio ionsh nship ip or or this this pro(lem/ hy or why not/
0.
A (an$ (an$ has has three three typ types es o o acco account unts: s: chec chec$i $ing ng,, savi saving ng,, and and loan loan.. ol ollo lowi wing ng are are the attri(utes or each type o account: &hec$ing: Acctno, "ateopened, 2alance, servicecharge 3avings: Acctno, "ateopened, 2alance, !nterest*ate !nterest*ate 4oan: Acctno, "ateopened, 2alance, !nterestrate, 5ayment Assume that each (an$ account must (e a mem(er o e6actly one o these su(types. 7sing generalisation, develop an 88* model segment to represent this situation. *emem(er to include a su(type discriminator. discriminator.
9.
"raw "raw an an 88* 88* diag diagra ram m or or the the oll ollow owin ing g pro( pro(le lem. m. A nonnon-pr prof oftt orga organi niza zati tion on depe depend nds s on a num(e num(err o die diere rent nt type types s o pers person ons s or or its its succe success ssu ull operation. The organization organization is interested in the ollowing ollowing attri(utes or all all o these persons: persons: 33, ame, Address, Address, &ity3tate; &ity3tate;ip, ip, and Telep Telephone. hone. Three Three types o persons are o greatest interest: employees, volunteers, and donors. 8mployees only have a "ate
>.
At a we wee$ e$en end d retr retrea eat, t, the the entit entity y type type 58*3= 58*3= has thr three ee su(t su(typ ypes es:: &A#5 &A#58* 8*,, 2!?8*, and *78*. "raw a separate 88* diagram segment or each o the ollowing situations: a. At a giv given en time time,, a pers person on must must (e e6ac e6actl tly y one one o these these su(ty su(type pes. s. (. A per perso son n may may or may may not not (e one one o o the these se su(t su(typ ypes es..
@.
The The #!3 #!3 depa depart rtme ment nt o o our our (usi (usine ness ss wan wants ts to to (uil (uild d a dat data( a(as ase e to tra trac$ c$ all all our our hardware and and sotware. e won won wor$stations, networ$ servers, and peripherals. The department wants to $eep $eep trac$ o sotware sotware pac$ages pac$ages as
well as the licenses or those pac$ages. 3ome sotware licenses are or single machines. e can install them on networ$ servers, (ut we can permit only as many networ$ users as we own licenses. e also own networ$ licenses. A single networ$ license authorizes a specifc num(er o users. o networ$ licenses may (e installed on either wor$stations or servers. etwor$ licenses may (e installed only on servers. e want to $eep trac$ o where sotware licenses are installed. 3ome licenses may not (e installed anywhere at any given time. e must also (e a(le to prove the legality o any sotware we have installed. 8ach license must (e traced to either a purchase order, a git, or a loan. e may also have certain sotware on order. e order pac$ages, (ut we receive licenses. &onstruct the data model and attri(ute it through (rainstorming. .
#ost students have (an$ accounts. &onstruct a data model that shows the relationships that e6ist among customers, dierent types o accounts )e.g., chec$ing, savings, loan, unds+, and transactions )e.g., deposits, withdrawals, payments, AT#3+. Attri(ute your model such that it could (e used to produce a consolidated (an$ statement.
B.
To schedule classes, your school needs to $now a(out courses that can (e oered, instructors and their availa(ility, e'uipment re'uirements or courses, and rooms )and their e'uipments+. rom the courses, that can (e scheduled, the schedulers select the courses that will (e scheduled. or each o those courses, they schedule one or more classes )sometimes called sections or divisions+. The schedulers must assign classes to instructors, rooms, and time slots. The schedulers are constrained (y the reality that )1+ some courses cannot conCict (ecause may students ta$e them during the same terms, )0+ instructors cannot (e in two places at the same time, and )9 rooms cannot (e dou(le-(oo$ed. &onstruct a data model to help the schedulers.
3.
Address City/State/Zip
Name
SSN
PERSON
Telephone Person"Type+
Person"Type O D& E&
V& E&()!* V&()!*
D&()!*
EPLO!EE VOLUNTEER
Date"#ired
S$ill
DONOR
Donates
%TE
,. No - none o the ehi0le 0lassii0ations has a 1ni21e attri1te or a 1ni21e relationship. 4. PERSON
a
d
CAPER
RUNNER
5%6ER
. PERSON
d
CAPER
RUNNER 5%6ER
0.
PERSON
O
CAPER
RUNNER 5%6ER
d. PERSON
O
CAPER
5%6ER
RUNNER
7. Date"Opened
A00o1nt "Type
ACCOUNT
5alan0e
A00t"No A00o1nt"Type(
d )C*
C#EC6%N8
Seri0e "Chan9e
)S*
SAV%N8S
%nterest "Rate
)L*
LOAN
Payment
%nterest "Rate
5 .
A338T
=*?3TAT!=,
3=1TA*8
A338T
A338T
38*V8*
has
58*!5<8*A4
4!&8,38
has installed
,8T=*?
,=,-,8T=*?
4!&8,38
4!&8,38
has installed
has installed was o(tained via
was o(tained via was o(tained via
4=A,
D!1T
57*&
6 .
has
A&&=7,T ?ey "ata Account-!" E5?1F ,on-?ey "ata Account-=rigination-"ate Account-3tatus
&73T=#8* ?ey "ata &ustomer-!" E5?1F ,on-?ey "ata &ustomer-,ame &ustomer-Address &ustomer-Telephone-,o 33,
3AV!,D3
&<8&?!,D
A&&=7,T ,on-?ey "ata 3avings-!nterest-*ate 3avings-#inimum-2alance-4!mit 3avings-&urrent-2alance 3avings-%T"-"eposit-Amount 3avings-%T"-ithdrawl-Amount 3avings-%T"-Accum-!nterest
A&&=7,T ,on-?ey "ata &hec$ing-minimum-(alance &hec$ing-%T"-"eposits &hec$ing-%T"-ithdrawals &hec$ing-&urrent-2alance
4=A, A&&=7,T ,on-?ey "ata =riginal-4oan-Amount 4oan-4ength 4oan-!nterest-*ate 5rinciple-Amount-"ue 5ayment-"ue %T"-!nterest-5aid
A&&=7,T e6ecutes
T*A,3A&T!=, ?ey "ata Transaction-!" E5?1F Account-!" E5?0F E?F &us tomer-!" E5?9F E?F ,on-?ey "ata Transaction-"ate Transaction-Time
"85=3!T ?ey "ata Transaction-!" E5?1F E?F Account-!" E5?0F E?F ,on-?ey "ata "eposit-Amount
!T<"*AA4 ?ey "ata Transaction-!" E5?1F E?F Account-!" E5?0F E?F ,on-?ey "ata ithdrawal-Amount
is involved with
5A%#8,T ?ey "ata Transaction-!" E5?1F E?F Account-!" E5?0F E?F ,on-?ey "ata 5ayment-Amount
7 . 27!4"!D
has
*==#
*==#
has
8G7!5#8T
is or
8AT7*83
8AT7*83
is assoc iated with
*==# 3&<8"748
is taught at
is or
!3T*7&T=*
is or
T!#8 34=T
8G7!5#8T
3&<8"748
*8G7!*8#8T3
is associated with
!3T*7&T=*
is or
is assigned
38&T!=
&=7*38
contains
