CHAPTER 4: Basic SQL CHAPTER 4: Basic SQL
Answers to Selected Exercises Answers to Selected Exercises 4.5
4.5 - Consider the database shown in Figure 1.2, whose schema is shown in Figure 2.1. - 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
hat are the re!erentia" integrit# constraints that shou"d ho"d on the schema$on the schema$ rite appropriate SQL %%L statements to de!ine the
rite appropriate SQL %%L statements to de!ine the database.database. Answer:
Answer:
&he !o""owing re!erentia" integrit# constraints shou"d ho"d
&he !o""owing re!erentia" integrit# constraints shou"d ho"d 'we use the notation:'we use the notation: (.')1, ..., )n* --+ S.'B1, ..., Bn*
(.')1, ..., )n* --+ S.'B1, ..., Bn* to represent a !oreign e# !rom the
to represent a !oreign e# !rom the attributes )1, ..., )n o! ( 'the re!erencing re"ation*attributes )1, ..., )n o! ( 'the re!erencing re"ation* to S 'the re!erenced re"ation**:
to S 'the re!erenced re"ation**: ((Q/0S0&.'Course
((Q/0S0&.'Courseumber* umber* --+ C/(S.'Courseumber*--+ C/(S.'Courseumber* ((Q/0S0&.'rer
((Q/0S0&.'rere3uisiteumber* --+ e3uisiteumber* --+ C/(S.'Courseumber*C/(S.'Courseumber* SC&0.'Courseumber* --+ C/(S.'Courseumber*
SC&0.'Courseumber* --+ C/(S.'Courseumber* ()%5((&.'Studentumber* --+
()%5((&.'Studentumber* --+ S&/%&.'Studentumber*S&/%&.'Studentumber* ()%5((&.'Section0denti!ie
()%5((&.'Section0denti!ier* r* --+ SC&0.'Section0denti!ier*--+ SC&0.'Section0denti!ier* ne possib"e set o! C()& &)BL statements to de!ine
ne possib"e set o! C()& &)BL statements to de!ine the database is gi6en be"ow.the database is gi6en be"ow. C()& &)BL S&/%& ' ame 7)(C8)('9* &
C()& &)BL S&/%& ' ame 7)(C8)('9* & /LL,/LL, Studentumber 0&( & /LL,
Studentumber 0&( & /LL, C"ass C8)( & /LL, C"ass C8)( & /LL, ;a<or C8)('4*, ;a<or C8)('4*, (0;)(= >= 'Studentumber* *? (0;)(= >= 'Studentumber* *?
C()& &)BL C/(S ' Courseame 7)(C8)('9* &
C()& &)BL C/(S ' Courseame 7)(C8)('9* & /LL,/LL, Courseumber C8)('@* & /LL, Courseumber C8)('@* & /LL, Credit8ours 0&(, Credit8ours 0&(, %epartment C8)('4*, %epartment C8)('4*, (0;)(= >= 'Courseumber*, (0;)(= >= 'Courseumber*, /0Q/ 'Courseame* *? /0Q/ 'Courseame* *?
C()& &)BL ((Q/0S0& ' Courseumber C8)('@* &
C()& &)BL ((Q/0S0& ' Courseumber C8)('@* & /LL,/LL, rere3uisiteumber C8)('@* & /LL, rere3uisiteumber C8)('@* & /LL, (0;)(= >= 'Courseumber, rere3uisiteumber*, (0;)(= >= 'Courseumber, rere3uisiteumber*, F(0 >= 'Courseumber* F(0 >= 'Courseumber* (F(CS(F(CS C/(S 'Courseumber*, C/(S 'Courseumber*, F(0 >= 'rere3uisiteumber* F(0 >= 'rere3uisiteumber* (F(CS(F(CS C/(S 'Courseumber* *? C/(S 'Courseumber* *?
C()& &)BL SC&0 ' Section0denti!ier 0&( & /LL, C()& &)BL SC&0 ' Section0denti!ier 0&( & /LL, Courseumber C8)('@* & /LL,
Courseumber C8)('@* & /LL, Semester 7)(C8)('A* & /LL, Semester 7)(C8)('A* & /LL, =ear C8)('4* & /LL, =ear C8)('4* & /LL, 0nstructor 7)(C8)('1*, 0nstructor 7)(C8)('1*, (0;)(= >= 'Section0denti!ier*, (0;)(= >= 'Section0denti!ier*, F(0 >= 'Courseumber* F(0 >= 'Courseumber* (F(CS(F(CS C/(S 'Courseumber* *? C/(S 'Courseumber* *?
C()& &)BL ()%5((& ' Studentumber 0&( & /LL, C()& &)BL ()%5((& ' Studentumber 0&( & /LL, Section0denti!ier 0&( & /LL,
Section0denti!ier 0&( & /LL, rade C8)(, rade C8)(, (0;)(= >= 'Studentumber, Section0denti!ier*, (0;)(= >= 'Studentumber, Section0denti!ier*, F(0 >= 'Studentumber* (F(CS F(0 >= 'Studentumber* (F(CS S&/%& 'Studentumber*, S&/%& 'Studentumber*, F(0 >= 'Section0denti!ier* (F(CS F(0 >= 'Section0denti!ier* (F(CS SC&0 'Section0denti!ier* *? SC&0 'Section0denti!ier* *? 4.6
4.6 - (epeat ercise 4., b- (epeat ercise 4., but use the )0(L0 scut use the )0(L0 schema o! Figure 9.@.hema o! Figure 9.@.
Answer: Answer:
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,
);/& %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<ect, cascade, set to nu"", set to de!au"t* !or each re!erentia" integrit# constraint, both !or the deletiono! a re!erenced tup"e, and !or the update o! a primar# e# attribute 6a"ue in a re!erenced tup"e. usti!# #our choices.
Answer:
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
Chapter 4: Basic SQL
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,
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<ected i! eecuted on the database o! Figure .A.
'a* 0nsert J H(obertH, HFH, HScottH, HK49GG49H, H21-/-42H, H29A ewcast"e (d, Be""aire, &EH, ;, @, H@@@AAH, 1 + into ;L=.
0S(& 0& ;L=
7)L/S 'H(obertH, HFH, HScottH, HK49GG49H, H21-/-42H, H29A ewcast"e (d, Be""aire, &EH, ;, @, H@@@AAH, 1*
Chapter 4: Basic SQL
'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 ma<oring in HCSCH 'computer science*. 'b* (etrie6e the names o! a"" courses taught b# pro!essor >ing 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.
'd* (etrie6e the name and transcript o! each senior student 'C"ass* ma<oring in CSC. &ranscript inc"udes course name, course number, credit hours, semester, #ear, and grade !or each course comp"eted b# the student.
'e* (etrie6e the names and ma<or departments o! a"" straight ) students 'students who ha6e a grade o! ) in a"" their courses*.
'!* (etrie6e the names and ma<or departments o! a"" students who do not ha6e an# grade o! ) in an# o! their courses.
Answers:
'a* SLC& ame F(; S&/%& 8( ;a<orHCSCH '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<orHCSCH )% S&.Studentumber.Studentumber )% .Section0denti!ierS.Section0denti!ier )% S.CourseumberC.Courseumber 'e* SLC& ame, ;a<or
F(; S&/%&
8( & E0S&S ' SLC& M F(; ()%5((&
8( Studentumber S&/%&.Studentumber )% &'radeH)H** '!* SLC& ame, ;a<or
F(; S&/%&
8( & E0S&S ' SLC& M F(; ()%5((&
8( Studentumber S&/%&.Studentumber )% radeH)H *
4."% - rite SQL update statements to do the !o""owing on the database schema shown in 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.
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= *
