Chapter 4: Basic SQL
CHAPTER 4: Basic SQL Answers to Selected Exercises 4.5 - Consider the database shown in Figure 1.2, whose schema is shown in Figure 2.1.
hat are the re!erentia" integrit# constraints that shou"d ho"d on the schema$ rite appropriate SQL %%L statements to de!ine the database. Answer:
&he !o""owing re!erentia" integrit# constraints shou"d ho"d 'we use the notation: (.')1, ..., )n* --+ S.'B1, ..., Bn* to represent a !oreign e# !rom the attributes )1, ..., )n o! ( 'the re!erencing re"ation* to S 'the re!erenced re"ation**: ((Q/0S0&.'Courseumber* ((Q/0S0&.'Course umber* --+ C/(S.'Courseumber* ((Q/0S0&.'rere3uisiteumber* ((Q/0S0&.'rer e3uisiteumber* --+ C/(S.'Courseumber* SC&0.'Courseumber* --+ C/(S.'Courseumber* ()%5((&.'Studentumber* --+ S&/%&.'Studentumber* ()%5((&.'Section0denti!ier* ()%5((&.'Section0denti!ie r* --+ SC&0.'Section0denti!ier* ne possib"e set o! C()& &)BL statements to de!ine the database is gi6en be"ow. C()& &)BL S&/%& ' ame 7)(C8)('9* & /LL, Studentumber 0&( & /LL, C"ass C8)( & /LL, ;a= 'Studentumber* *? C()& &)BL C/(S ' Courseame 7)(C8)('9* & /LL, Courseumber C8)('@* & /LL, Credit8ours 0&(, %epartment C8)('4*, (0;)(= >= 'Courseumber*, /0Q/ 'Courseame* *? C()& &)BL ((Q/0S0& ' Courseumber C8)('@* & /LL, rere3uisiteumber C8)('@* & /LL, (0;)(= >= 'Courseumber, rere3uisiteumber*, F(0 >= 'Courseumber* (F(CS C/(S 'Courseumber*, F(0 >= 'rere3uisiteumber* (F(CS C/(S 'Courseumber* *? C()& &)BL SC&0 ' Section0denti!ier 0&( & /LL, Courseumber C8)('@* & /LL, Semester 7)(C8)('A* & /LL, =ear C8)('4* & /LL, 0nstructor 7)(C8)('1*, (0;)(= >= 'Section0denti!ier*, F(0 >= 'Courseumber* (F(CS C/(S 'Courseumber* *? C()& &)BL ()%5((& ' Studentumber 0&( & /LL, Section0denti!ier 0&( & /LL, rade C8)(, (0;)(= >= 'Studentumber, Section0denti!ier*, F(0 >= 'Studentumber* (F(CS S&/%& 'Studentumber*, F(0 >= 'Section0denti!ier* (F(CS SC&0 'Section0denti!ier* *? 4.6 - (epeat ercise 4., but but use the )0(L0 schema schema o! Figure 9.@. Answer:
Copyright © 2011 Pearson Education, Inc. Publishing as Pearson Addison-Wesley. Addison-Wesley.
1
2
Chapter 4: Basic SQL
&he !o""owing re!erentia" integrit# constraints shou"d ho"d: FL08&5L.'FL08&5/;B(* --+ FL08&.'/;B(* FL08&5L.'%)(&/(5)0((&5C%* --+ )0((&.')0((&5C%* FL08&5L.')((07)L5)0((&5C%* --+ )0((&.')0((&5C%* L50S&)C.'FL08&5/;B(, L5/;B(* --+ FL08&5L.'FL08&5/;B(, L5/;B(* L50S&)C.')0(L)50%* --+ )0(L).')0(L)50%* L50S&)C.'%)(&/(5)0((&5C%* --+ )0((&.')0((&5C%* L50S&)C.')((07)L5)0((&5C%* --+ )0((&.')0((&5C%* F)(S.'FL08&5/;B(* --+ FL08&.'/;B(* C)5L)%.')0(L)5&=5);* --+ )0(L)5&=.'&=5);* C)5L)%.')0((&5C%* --+ )0((&.')0((&5C%* )0(L).')0(L)5&=* --+ )0(L)5&=.'&=5);* S)&5(S(7)&0.'FL08&5/;B(, L5/;B(, %)&* --+ L50S&)C.'FL08&5/;B(, L5/;B(, %)&* ne possib"e set o! C()& &)BL statements to de!ine the database is gi6en be"ow. C()& &)BL )0((& ' )0((&5C% C8)('9* & /LL, ); 7)(C8)('9* & /LL, C0&= 7)(C8)('9* & /LL, S&)& 7)(C8)('9*, (0;)(= >= ')0((&5C%* *? C()& &)BL FL08& ' /;B( 7)(C8)('A* & /LL, )0(L0 7)(C8)('2* & /LL, >%)=S 7)(C8)('1* & /LL, (0;)(= >= '/;B(* *? C()& &)BL FL08&5L ' FL08&5/;B( 7)(C8)('A* & /LL, L5/;B( 0&( & /LL, %)(&/(5)0((&5C% C8)('9* & /LL, SC8%/L%5%)(&/(5&0; &0;S&); 0&8 &0; D, )((07)L5)0((&5C% C8)('9* & /LL, SC8%/L%5)((07)L5&0; &0;S&); 0&8 &0; D, (0;)(= >= 'FL08&5/;B(, L5/;B(*, F(0 >= 'FL08&5/;B(* (F(CS FL08& '/;B(*, F(0 >= '%)(&/(5)0((&5C%* (F(CS )0((& ')0((&5C%*, F(0 >= ')((07)L5)0((&5C%* (F(CS )0((& ')0((&5C%* *? C()& &)BL L50S&)C ' FL08&5/;B( 7)(C8)('A* & /LL, L5/;B( 0&( & /LL, L5%)& %)& & /LL, 5F5)7)0L)BL5S)&S 0&(, )0(L)50% 0&(, %)(&/(5)0((&5C% C8)('9*, %)(&/(5&0; &0;S&); 0&8 &0; D, )((07)L5)0((&5C% C8)('9*, )((07)L5&0; &0;S&); 0&8 &0; D, (0;)(= >= 'FL08&5/;B(, L5/;B(, L5%)&*, F(0 >= 'FL08&5/;B(, L5/;B(* (F(CS FL08&5L 'FL08&5/;B(, L5/;B(*, F(0 >= ')0(L)50%* (F(CS )0(L) ')0(L)50%*, F(0 >= '%)(&/(5)0((&5C%* (F(CS )0((& ')0((&5C%*, F(0 >= ')((07)L5)0((&5C%* (F(CS )0((& ')0((&5C%* *? C()& &)BL F)(S ' FL08&5/;B( 7)(C8)('A* & /LL, F)(5C% 7)(C8)('1* & /LL,
Copyright © 2011 Pearson Education, Inc. Publishing as Pearson Addison-Wesley.
Chapter 4: Basic SQL
);/& %C0;)L'@,2* & /LL, (S&(0C&0S 7)(C8)('2*, (0;)(= >= 'FL08&5/;B(, F)(5C%*, F(0 >= 'FL08&5/;B(* (F(CS FL08& '/;B(* *? C()& &)BL )0(L)5&= ' &=5); 7)(C8)('2* & /LL, ;)E5S)&S 0&( & /LL, C;)= 7)(C8)('1* & /LL, (0;)(= >= '&=5);* *? C()& &)BL C)5L)% ' )0(L)5&=5); 7)(C8)('2* & /LL, )0((&5C% C8)('9* & /LL, (0;)(= >= ')0(L)5&=5);, )0((&5C%*, F(0 >= ')0(L)5&=5);* (F(CS )0(L)5&= '&=5);*, F(0 >= ')0((&5C%* (F(CS )0((& ')0((&5C%* *? C()& &)BL )0(L) ' )0(L)50% 0&( & /LL, &&)L5/;B(5F5S)&S 0&( & /LL, )0(L)5&= 7)(C8)('2* & /LL, (0;)(= >= ')0(L)50%*, F(0 >= ')0(L)5&=* (F(CS )0(L)5&= '&=5);* *? C()& &)BL S)&5(S(7)&0 ' FL08&5/;B( 7)(C8)('A* & /LL, L5/;B( 0&( & /LL, L5%)& %)& & /LL, S)&5/;B( 7)(C8)('4*, C/S&;(5); 7)(C8)('9* & /LL, C/S&;(58 C8)('12*, (0;)(= >= 'FL08&5/;B(, L5/;B(, L5%)&, S)&5/;B(*, F(0 >= 'FL08&5/;B(, L5/;B(, L5%)&* (F(CS L50S&)C 'FL08&5/;B(, L5/;B(, L5%)&* *? 4.7 - Consider the L0B()(= re"ationa" database schema o! Figure 4.A. Choose the
appropriate action 're
Be"ow are possib"e choices. 0n genera", i! it is not c"ear which action to choose, (C& shou"d be chosen, since it wi"" not permit automatic changes to happen 'b# update propagation* that ma# be unintended. B>5)/&8(S.'Boo0d* --+ B>.'Boo0d* C)SC)% on both %L& or /%)& 'since this corresponds to a mu"ti-6a"ued attribute o! B> 'see the so"ution to ercise A.2G*? hence, i! a B> is de"eted, or the 6a"ue o! its Boo0d is updated 'changed*, the de"etion or change is automatica""# propagated to the re!erencing B>5)/&8(S tup"es* B>.'ub"isherame* --+ /BL0S8(.'ame* (C& on %L& 'we shou"d not de"ete a /BL0S8( tup"e which has eisting B> tup"es that re!erence the /BL0S8(* C)SC)% on /%)& 'i! a /BL0S8(Hs ame is updated, the change shou"d be propagated automatica""# to a"" re!erencing B> tup"es* B>5L)S.'Boo0d* --+ B>.'Boo0d* C)SC)% on both %L& or /%)& 'i! a B> is de"eted, or the 6a"ue o! its Boo0d is updated 'changed*, the de"etion or change is automatica""# propagated to the re!erencing B>5L)S tup"es* 'ote: ne cou"d a"so choose (C& on %L&* B>5C0S.'Boo0d* --+ B>.'Boo0d* C)SC)% on both %L& or /%)& 'i! a B> is de"eted, or the 6a"ue o! its Boo0d is updated 'changed*, the de"etion or change is automatica""# propagated to the re!erencing Copyright © 2011 Pearson Education, Inc. Publishing as Pearson Addison-Wesley.
9
Chapter 4: Basic SQL
4
B>5C0S tup"es* B>5L)S.'Cardo* --+ B(((.'Cardo* C)SC)% on both %L& or /%)& 'i! a B((( tup"e is de"eted, or the 6a"ue o! its Cardo is updated 'changed*, the de"etion or change is automatica""# propagated to the re!erencing B>5L)S tup"es* 'ote: ne cou"d a"so choose (C& on %L&, with the idea that i! a B((( is de"eted, it is necessar# !irst to mae a printout o! a"" B>5L)S outstanding be!ore de"eting the B(((? in this case, the tup"es in B>5L)S that re!erence the B((( being de"eted wou"d !irst be ep"icit"# de"eted a!ter maing the printout, and be!ore the B((( is de"eted* B>5C0S.'Branch0d* --+ L0B()(=5B()C8.'Branch0d* C)SC)% on both %L& or /%)& 'i! a L0B()(=5B()C8 is de"eted, or the 6a"ue o! its Branch0d is updated 'changed*, the de"etion or change is automatica""# propagated to the re!erencing B>5C0S tup"es* 'ote: ne cou"d a"so choose (C& on %L&* B>5L)S.'Branch0d* --+ L0B()(=5B()C8.'Branch0d* C)SC)% on both %L& or /%)& 'i! a L0B()(=5B()C8 is de"eted, or the 6a"ue o! its Branch0d is updated 'changed*, the de"etion or change is automatica""# propagated to the re!erencing B>5L)S tup"es* 'ote: ne cou"d a"so choose (C& on %L&* 4. - rite appropriate SQL %%L statements !or dec"aring the L0B()(= re"ationa" database
schema o! Figure 4.A. Speci!# the e#s and re!erentia" triggered actions. Answer:
ne possib"e set o! C()& &)BL statements is gi6en be"ow: C()& &)BL B> ' Boo0d C8)('2* & /LL, &it"e 7)(C8)('9* & /LL, ub"isherame 7)(C8)('2*, (0;)(= >= 'Boo0d*, F(0 >= 'ub"isherame* (F(CS /BL0S8( 'ame* /%)& C)SC)% *? C()& &)BL B>5)/&8(S ' Boo0d C8)('2* & /LL, )uthorame 7)(C8)('9* & /LL, (0;)(= >= 'Boo0d, )uthorame*, F(0 >= 'Boo0d* (F(CS B> 'Boo0d* %L& C)SC)% /%)& C)SC)% *? C()& &)BL /BL0S8( ' ame 7)(C8)('2* & /LL, )ddress 7)(C8)('4* & /LL, hone C8)('12*, (0;)(= >= 'ame* *? C()& &)BL B>5C0S ' Boo0d C8)('2* & /LL, Branch0d 0&( & /LL, o5!5Copies 0&( & /LL, (0;)(= >= 'Boo0d, Branch0d*, F(0 >= 'Boo0d* (F(CS B> 'Boo0d* %L& C)SC)% /%)& C)SC)%, F(0 >= 'Branch0d* (F(CS B()C8 'Branch0d* %L& C)SC)% /%)& C)SC)% *? C()& &)BL B((( ' Cardo 0&( & /LL, ame 7)(C8)('9* & /LL, )ddress 7)(C8)('4* & /LL, hone C8)('12*, (0;)(= >= 'Cardo* *? C()& &)BL B>5L)S ' Cardo 0&( & /LL, Copyright © 2011 Pearson Education, Inc. Publishing as Pearson Addison-Wesley.
Chapter 4: Basic SQL
Boo0d C8)('2* & /LL, Branch0d 0&( & /LL, %ateut %)& & /LL, %ue%ate %)& & /LL, (0;)(= >= 'Cardo, Boo0d, Branch0d*, F(0 >= 'Cardo* (F(CS B((( 'Cardo* %L& C)SC)% /%)& C)SC)%, F(0 >= 'Branch0d* (F(CS L0B()(=5B()C8 'Branch0d* %L& C)SC)% /%)& C)SC)%, F(0 >= 'Boo0d* (F(CS B> 'Boo0d* %L& C)SC)% /%)& C)SC)% *? C()& &)BL L0B()(=5B()C8 ' Branch0d 0&( & /LL, Branchame 7)(C8)('2* & /LL, )ddress 7)(C8)('4* & /LL, (0;)(= >= 'Branch0d* *? 4.! - 8ow can the e# and !oreign e# constraints be en!orced b# the %B;S$ 0s the
en!orcement techni3ue #ou suggest di!!icu"t to imp"ement$ Can the constraint checs be eecuted in an e!!icient manner when updates are app"ied to the database$ Answer:
ne possib"e techni3ue that is o!ten used to chec e!!icient"# !or the e# constraint is to create an inde on the combination o! attributes that !orm each e# 'primar# or secondar#*. Be!ore inserting a new record 'tup"e*, each inde is searched to chec that no 6a"ue current"# eists in the inde that matches the e# 6a"ue in the new record. 0! this is the case, the record is inserted success!u""#. For checing the !oreign e# constraint, an inde on the primar# e# o! each re!erenced re"ation wi"" mae this chec re"ati6e"# e!!icient. hene6er a new record is inserted in a re!erencing re"ation , its !oreign e# 6a"ue is used to search the inde !or the primar# e# o! the re!erenced re"ation, and i! the re!erenced record eists, then the new record can be success!u""# inserted in the re!erencing re"ation. For de"etion o! a re!erenced record, it is use!u" to ha6e an inde on the !oreign e# o! each re!erencing re"ation so as to be ab"e to determine e!!icient"# whether an# records re!erence the record being de"eted. 0! the indees described abo6e do not eist, and no a"ternati6e access structure '!or eamp"e, hashing* is used in their p"ace, then it is necessar# to do "inear searches to chec !or an# o! the abo6e constraints, maing the checs 3uite ine!!icient. 4."# I o so"ution pro6ided. 4."" - Speci!# the updates o! ercise 9.11 using the SQL update commands. Answers:
Be"ow, we show how each o! the updates ma# be speci!ied in SQL. otice that some o! these updates 6io"ate integrit# constraints as discussed in the so"ution to ercise .1, and hence shou"d be re
Copyright © 2011 Pearson Education, Inc. Publishing as Pearson Addison-Wesley.
Chapter 4: Basic SQL
A
'b* 0nsert J Hroduct)H, 4, HBe""aireH, 2 + into (C&. 0S(& 0& (C& 7)L/S 'Hroduct)H, 4, HBe""aireH, 2*
'c* 0nsert J HroductionH, 4, HK49GG49H, H1-C&-@@H + into %)(&;&. 0S(& 0& %)(&;& 7)L/S 'HroductionH, 4, HK49GG49H, H1-C&-@@H*
'd* 0nsert J HAGGAG@K@KH, nu"", H4.H + into (>S5. 0S(& 0& (>S5 7)L/S 'HAGGAG@K@KH, /LL, H4.H*
'e* 0nsert J H494949H, HohnH, ;, H12-%C-AH, HS/SH + into %%&. 0S(& 0& %%& 7)L/S 'H494949H, HohnH, ;, H12-%C-AH, HS/SH*
'!* %e"ete the (>S5 tup"es with SS H99944H. %L& F(; (>S5 8( SS H99944H
'g* %e"ete the ;L= tup"e with SS HK@GA4921H. %L& F(; ;L= 8( SS HK@GA4921H
'h* %e"ete the (C& tup"e with ); HroductEH. %L& F(; (C& 8( ); HroductEH
'i* ;odi!# the ;(SS and ;(S&)(&%)& o! the %)(&;& tup"e with %/;B( to H1294AG@KH and H1-C&-@@H, respecti6e"#. /%)& %)(&;& S& ;(SS H1294AG@KH, ;(S&)(&%)& H1-C&-@@H 8( %/;B(
'<* ;odi!# the S/(SS attribute o! the ;L= tup"e with SS HKKK@@GGGGH to HK49GG49H. /%)& ;L= S& S/(SS HK49GG49H 8( SS HKKK@@GGGGH
'* ;odi!# the 8/(S attribute o! the (>S5 tup"e with SS HKKK@@GGGGH and 1 to H.H. /%)& (>S5 S& 8/(S H.H 8( SS HKKK@@GGGGH )% 1 4."$ - Speci!# the !o""owing 3ueries in SQL on the database schema o! Figure 1.2.
'a* (etrie6e the names o! a"" senior students maing in @ and @A. 'c* For each section taught b# pro!essor >ing, retrie6e the course number, semester, #ear, and number o! students who too the section. Copyright © 2011 Pearson Education, Inc. Publishing as Pearson Addison-Wesley.
Chapter 4: Basic SQL
'd* (etrie6e the name and transcript o! each senior student 'C"ass* ma
'a* SLC& ame F(; S&/%& 8( ;a
'b* SLC& Courseame F(; C/(S, SC&0 8( C/(S.CourseumberSC&0.Courseumber )% 0nstructorH>ingH )% '=earH@H ( =earH@AH*
)nother possib"e SQL 3uer# uses nesting as !o""ows: SLC& Courseame F(; C/(S 8( Courseumber 0 ' SLC& Courseumber F(; SC&0 8( 0nstructorH>ingH )% '=earH@H ( =earH@AH* *
'c* SLC& Courseumber, Semester, =ear, C/&'M* F(; SC&0, ()%5((& 8( 0nstructorH>ingH )% SC&0.Section0denti!ier()%5((&.Section0denti!ier (/ B= Courseumber, Semester, =ear
'd* SLC& ame, Courseame, C.Courseumber, Credit8ours, Semester, =ear, rade F(; S&/%& S&, C/(S C, SC&0 S, ()%5((& 8( C"ass )% ;a
'e* SLC& ame, ;a
'!* SLC& ame, ;a
Figure 1.2. 'a* 0nsert a new student JHohnsonH, 2, 1, H;)&8H+ in the database. 'b* Change the c"ass o! student HSmithH to 2. Copyright © 2011 Pearson Education, Inc. Publishing as Pearson Addison-Wesley.
G
Chapter 4: Basic SQL
@
'c* 0nsert a new course JH>now"edge ngineeringH,HCSC49KH, 9,HCSCH+. 'd* %e"ete the record !or the student whose name is HSmithH and student number is 1G. Answers:
'a* 0S(& 0& S&/%& 7)L/S 'HohnsonH, 2, 1, H;)&8H*
'b* /%)& S&/%& S& CL)SS 2 8( ameHSmithH
'c* 0S(& 0& C/(S 7)L/S 'H>now"edge ngineeringH,HCSC49KH, 9,HCSCH*
'd* %L& F(; S&/%& 8( ameHSmithH )% Studentumber1G
4."4 I o so"ution pro6ided
4."5 - Consider the ;L= tab"eNs constraint ;S/(F> as speci!ied in Figure 4.2
is changed to read as !o""ows: CS&()0& ;S/(F> F(0 >= 'S/(SS* (F(CS ;L='SS* %L& C)SC)% /%)& C)SC)%, )nswer the !o""owing 3uestions: a. hat happens when the !o""owing command is run on the database state shown in Figure .A$ %L& ;L= 8( L); OBorgN b. 0s it better to C)SC)% or S& /LL in case o! ;S/(F> constraint %L&$ Answers:
a* &he ames . Borg entr# is de"eted !rom the tab"e, and each emp"o#ee with him as a super6isor is a"so 'and their super6isees, and so on*. 0n tota", @ rows are de"eted and the tab"e is empt#. b* 0t is better to S& /LL, since an emp"o#ee is not !ired '%L&%* when their super6isor is de"eted. 0nstead, their S/(SS shou"d be S& /LL so that the# can "ater get a new super6isor.
4."6 - rite SQL statements to create a tab"e ;L=5B)C>/ bacup o! ;L=
tab"e shown in Figure 9.A. Answer:
0S(& 0& ;L=5B)C>/ 7)L/S ' SLC& M F(; ;L= *
Copyright © 2011 Pearson Education, Inc. Publishing as Pearson Addison-Wesley.
