Solution
4
)DOVH
)DOVH
7UXH
7UXH
)DOVH
7UXH
)DOVH
)DOVH
7UXH
7UXH

4
D
LQWHUIDFH3HUVRQ^
DWWULEXWHVWULQJQDPH
DWWULEXWHVWULQJELUWKBGDWH
UHODWLRQVKLS3HUVRQPRWKHU2I
LQYHUVH3HUVRQFKLOGUHQ2I)HPDOH
UHODWLRQVKLS3HUVRQIDWKHU2I
LQYHUVH3HUVRQFKLOGUHQ2I0DOH
UHODWLRQVKLS6HW3HUVRQ!FKLOGUHQ
LQYHUVH3HUVRQSDUHQWV2I
UHODWLRQVKLS6HW3HUVRQ!FKLOGUHQ2I)HPDOH
LQYHUVH3HUVRQPRWKHU2I
UHODWLRQVKLS6HW3HUVRQ!FKLOGUHQ2I0DOH
LQYHUVH3HUVRQIDWKHU2I
UHODWLRQVKLS6HW3HUVRQ!SDUHQWV2I
LQYHUVH3HUVRQFKLOGUHQ
`
E
L
Team play
Name Coach
Player
HomeSteals#
HomeRebounds#
HomeScores#
GID
Location
Time
ForeignScores#
ForeignRebounds#
ForeignSteals#
n
m
Game1
HomeTeam
ForeignTeam

LL
Name Coach Player Tname
GID homeSteals# homeRebounds# homeScores# Location Time ForeignScores# ForeignRebounds# ForeignSteals#
GID HomeTeam ForeignTeam
p.k
f.k
p.k
p.k
p.k
f.k f.k


4
(a)
.H\FRQVWUDLQWFDQGLGDWHNH\YDOXHVPXVWEHXQLTXHIRUHYHU\UHODWLRQLQVWDQFH
(QWLW\LQWHJULW\DQDWWULEXWHWKDWLVSDUWRIDSULPDU\NH\FDQQRWEH18//
Referential integrity: The third kind has to do with “foreign keys”.
L&KHFNDOOWKUHHFRQVWUDLQWV
LL5HIHUHQWLDOFRQVWUDLQWV
LLL1RQH
E1R%HFDXVHሺሻା ൌ ሼǡ ǡ ǡ ǡ ሽDQG
ב ሺሻା
FVW1)%HFDXVHLVWKHRQO\NH\DQGLVSDUWLDOO\GHSHQGHQWRQ
G ୫ ൌ ሼ ՜ ǡ ՜ ǡ ՜ ሽ
6WHS ୫ ൌ
6WHS5HSODFH ՜ ZLWK ՜ ǡ ՜ ǡ ՜
6WHS5HPRYH ՜ VLQFHLWFRXOGEHREWDLQHGE\ ՜ DQG ՜
6WHS1RPRUH)'FRXOGEHUHPRYHGGRQH

4
D
L$SULPDU\LQGH[LVDQLQGH[RQDVHWRIILHOGVWKDWLQFOXGHVWKHSULPDU\NH\,WLVJXDUDQWHHGQRWWR
FRQWDLQGXSOLFDWHV$VHFRQGDU\LQGH[LVDQLQGH[WKDWLVQRWDSULPDU\LQGH[,WFDQKDYHGXSOLFDWHV
LL$GHQVHLQGH[KDVDWOHDVWRQHGDWDHQWU\VHDUFKNH\YDOXHWKDWDSSHDUVLQDUHFRUGLQWKHLQGH[HG
ILOH$VSDUVHLQGH[FRQWDLQVDQHQWU\IRUHDFKSDJHRIUHFRUGVLQDGDWDILOH,WPXVWEHFOXVWHUHG7KXV
ZHFDQKDYHDWPRVWRQHVSDUVHLQGH[RQDGDWDILOH$VSDUVHLQGH[LVW\SLFDOO\PXFKVPDOOHUWKDQD
GHQVHLQGH[
LLL$FOXVWHUHGLQGH[LVRQHLQZKLFKWKHRUGHULQJRIGDWDHQWULHVLVWKHVDPHDVWKHRUGHULQJRIGDWD
UHFRUGV:HFDQKDYHDWPRVWRQHFOXVWHUHGLQGH[RQDGDWDILOH$QXQFOXVWHUHGLQGH[LVDQLQGH[WKDW
LVQRWFOXVWHUHG:HFDQKDYHVHYHUDOXQFOXVWHUHGLQGH[HVRQDGDWDILOH

E
1RLWLVQRWDJRRGLGHDEHFDXVHLWGRHVQRWSURYLGHXVHIXOLQGH[LQJLQIRUPDWLRQIRUUHWULHYDODQGLV
XVHOHVV

F
1RLQWKLVFDVHWKHLQGH[LVXQFOXVWHUHGHDFKTXDOLI\LQJGDWDHQWULHVFRXOGFRQWDLQDQULGWKDWSRLQWV
WRDGLVWLQFWGDWDSDJHOHDGLQJWRDVPDQ\GDWDSDJH,2DVWKHQXPEHURIGDWDHQWULHVWKDWPDWFKWKH
UDQJHTXHU\,QWKLVFDVHLIWKHQXPEHURIGDWDHQWULHVLVODUJHUWKDQWKHQXPEHURIGDWDEORFNVWKHQ
XVLQJLQGH[LVZRUVHWKDQILOHVFDQ


4
D6HHWKHOHFWXUHQRWHVIRUVXFKDQH[DPSOH
E
L$VWUDLJKWIRUZDUGZD\WRGRWKLVLVWRFRQVWUXFWDVHULDOVFKHGXOHXVLQJWKHWZRSKDVHORFNLQJ
SURWRFRO,MXVWVKRZWKHNH\SDUWRIVFKHGXOH
T1 T2
write_lock(A)

…

write_lock(B)
…

unlock(A)
unlock(B)

write_lock(B)
…

write_lock(A)
…

unlock(B)
unlock(A)

ii)
1RLWLVLPSRVVLEOHWRKDYHDQRQVHULDOEXWVHULDOL]DEOHVFKHGXOH&RQVLGHUWKDWWKHODVWRSHUDWLRQLQ
7LV:5,7(%DQGWKHODVWRSHUDWLRQLQ7LV:5,7($0HDQZKLOH7VWDUWVZLWK5($'$
DQG7VWDUWVZLWK5($'%7KHUHIRUHLQDQ\VHULDOL]DEOHVFKHGXOHRI7DQG7HLWKHU5($'$
LQ7VKRXOGEHDIWHU:5,7(%LQ7RU5($'%LQ7VKRXOGEHDIWHU:5,7(%LQ7
LLL
T1 T2
…

…

write_lock(A)
WRITE(A)
write_lock(B)
WRITE(B)
read_lock(B)
**waiting for B*** read_lock(A)
**waiting for B*** **waiting for A**
…

…



A
F
7KHVFKHGXOHLVQRWVHULDOL]DEOHEHFDXVHWKHUHLVDF\FOHLQLWVVFKHGXOHJUDSK
< <
<
; 77
7