Concepts of Database Management, Sixth Edition
Solutions 2-1
Chapter 2 1.
A relat relation ion is is a two-d two-dime imensi nsiona onall table table in which which (1) (1) the the entrie entries s in the the tabl table e are are single single-va -value lued; d; (2) (2) each each colu column mn has has a dist distinc inctt name; (3) all of the values in a column are values of the same attribute; (4) the order of the columns is immaterial; (5) each row is distinct; and () the order of the rows is immaterial.
2.
A rel relat atio iona nall dat datab abas ase e is is a co collec llecttion ion of of rel relat atio ions ns..
3.
An unnor unnormal mali!e i!ed d relati relation on is a stru structu cture re that that satis satisfie fies s all the the "ro"e "ro"erti rties es of a rela relatio tion n e#ce"t e#ce"t the the restr restrict iction ion that that ent entries ries must must be single-valued. $t is not a relation.
4.
Attrib tribut ute e is is ano anotther her ter term m for for a col colum umn n in in a tabl able.
5.
$n the the shor shortha thand nd re"re re"resen sentat tation ion%% each each tabl table e is list listed% ed% and and after after each each table% table% all all the the colum columns ns of the the table table are are liste listed d in "arentheses. &rimar' e's are underlined. ranch (ranch*um% ranch*ame% ranch+ocation% *um,m"lo'ees) &ublisher (&ublisherode% &ublisher*ame% it') Author (Author*um% (Author*um% Author+ast% Author+ast% Authorirst) oo (ooode% /itle% &ublisherode% /'"e% &rice% &a"erbac) 0rote (ooode% Author*um% euence*um) $nventor' (ooode% ranch*um% nand)
. .
/o uali ualif' f' a fiel field d name name means means to to indica indicate te the the tabl table e to whic which h it belo belongs ngs b' "rec "recedi eding ng the the field field name name with with the the tabl table e name name and a "eriod. /o ualif' the treet field in the ustomer table% 'ou would enter ustomer.treet. /he "rim "rimar' ar' e' e' is the colu column mn or collec collectio tion n of colu columns mns that that uniu uniuel' el' iden identif tifies ies a given given row. row. /he /he "rim "rimar' ar' e' e' of of the the ranc ranch h table is ranch*um. /he "rimar' e' of the &ublisher table is &ublisherode. /he "rimar' e' of the Author table is Author*um. /he "rimar' e' of the oo table table is ooode. /he "rimar' e' of the 0rote table table is the concatenation concatenation (combination) of ooode and Author*um. /he "rimar' e' of the $nventor' table is the concatenation of ooode and ranch*um.
6.
$nclud $nclude e the the fiel field d in the the desig design n grid grid and mae mae sure sure the the fiel field7s d7s how how chec chec bo# bo# conta contains ins a chec chec mar mar..
8.
,nte ,nterr the the crit criter eria ia in the the ri rite teri ria a row row for for the the a""r a""ro" o"ri riat ate e fie field ld name name..
19.
/o combin combine e criteri criteria a with with A*:% A*:% enter enter the condition conditions s on the same riteria riteria row in in the the design design grid. grid. /o combine combine criteria criteria with % enter the conditions on different riteria rows in the design grid.
11.
/'"e the com"ut com"utation ation instead instead of a field field name name in the design design grid. Alternat Alternativel' ivel'%% 'ou 'ou can can enter enter the the com"uta com"utation tion in the the
12.
/o calcula calculate te funct functions% ions% clic the /otals /otals button button in the the how= how=ide ide grou" grou" on the the >uer' >uer' /ools :esign :esign tab% tab% and and then then add add one of the following functions to the /otal row for the column? ount% um% Avg (average)% @a# (largest value)% @in (smallest value)% t:ev (standard deviation)% ar (variance)% irst% and +ast.
13.
$ndica $ndicate te the the a""ro a""ro"ri "riate ate sort sort orde orderr (Asce (Ascendi nding ng or or :escen :escendin ding) g) in the ort ort row row of the the desig design n grid. grid.
14.
0hen sorting sorting on more than one field field in an Access Access uer'% uer'% the sort sort e' to to the the left left in the design design grid grid is the maBor maBor e' and and the the sort e' to the right is the minor e'. /he data is sorted on the maBor e'. 0ithin those rows with the same value in the maBor e'% the data is sorted b' the minor e'.
15.
$nclude $nclude the the field field lists lists from both tables tables in the uer' uer' design. design. &rovided &rovided the tables tables have have match matching ing fields fields%% a Boin Boin line line will will connect connect the tables. $nclude the desired fields from either table in the design grid.
1.
Cse an u"da u"date te uer uer' ' when when 'ou 'ou want want to mae mae the same same chang change e to all all rows rows satisf satisf'in 'ing g some some crit criteri eria. a.
1.
Cse a dele delete te uer' uer' when when 'ou 'ou want want to to dele delete te all rows rows sati satisf' sf'ing ing some some crit criteri eria. a.
16. 16.
Cse Cse a ma maee-ta tabl ble e ue uer' r' to save save the the res result ults s of of a uer uer' ' as as a tabl table. e.
18.
elational elational algebra algebra is a theoret theoretical ical wa' of mani"ulati mani"ulating ng a relational relational database. database. elational elational algebra algebra includ includes es o"erat o"erations ions that act on e#isting tables to "roduce new tables% similar to the wa' the o"erations of addition and subtraction act on numbers to "roduce new numbers in the mathematical algebra with which 'ou are familiar.
29. 29.
/he /he ,+ ,+, ,/ / comm comman and d sele select cts s all all rows rows sati satisf sf'i 'ing ng som some e cond condit itio ion. n.
21. 21.
/he /he & &D, D,/ / comm comman and d sel selec ects ts onl' onl' the the s"ec s"ecif ifie ied d col colum umns ns..
22. 22.
/he /he D$* D$* comm comman and d com combi bine nes s tabl tables es on matc matchin hing g colu column mns. s.
23.
/he C*$* C*$* com comman mand d selec selects ts all all rows rows that that are are in in the the first first table% table% in the the seco second nd tabl table% e% or or both. both.
24.
/he two two table tables s must must be union union com" com"ati atible ble;; that that is% is% the' the' must must have have the the same same numbe numberr of colum columns ns and and the colu columns mns must must corres"ond.
25. 25.
/he /he $*/, $*/, , ,/ / comm comman and d sel selec ects ts all all row rows s that that are are in both both tabl tables es..
2.
/he C/A C/A/ / comm command and sele selects cts all rows rows that that are are in in the the first first table% table% but not in the the seco second nd tabl table. e.
Concepts of Database Management, Sixth Edition
Solutions 2-2
2.
/he &:C/ command (mathematicall' called the artesian "roduct) is the table obtained b' concatenating ever' row in the first table with ever' row in the second table.
26.
0hen one table is divided b' a second table% the :$$$* command "roduces a table containing all values in the first table that match all rows in the second table.
Answers to Premiere Products Exercises: QBE 1.
$nclude the ustomer*um and ustomer*ame fields in the design grid. :o not include an' criteria.
2.
:ouble-clic the asteris in the &art table field list to add all fields to the design grid. :o not enter an' criteria.
3.
$nclude the ustomer*um% ustomer*ame% and e"*um fields in the design grid. ,nter 35 in the riteria row for the e"*um column. emove the chec mar from the how hec bo# in the e"*um column.
4.
&lace the ustomer*um% ustomer*ame% redit+imit% and e"*um fields in the design grid. ,nter 35 in riteria row for the e"*um column and 19999 in the riteria row for the redit+imit column. emove the chec mars from the how chec bo#es for the redit+imit and e"*um columns.
5.
&lace the ustomer*um% ustomer*ame% redit+imit% and e"*um fields in the design grid. ,nter 35 in riteria row for the e"*um column and 19999 in the r row for the redit+imit column. emove the chec mars from the how chec bo#es for the redit+imit and e"*um columns.
.
$nclude the field lists for the rders and ustomer tables in the uer' design. &lace the rder*um% rder:ate% ustomer*um% and ustomer*ame columns in the design grid. :o not enter an' criteria.
.
$nclude the field lists for the ustomer and e" tables in the uer' design. $nclude the ustomer*um and ustomer*ame fields from the ustomer table. $nclude the +ast*ame and irst*ame fields from the e" table. ,nter &ere! in the riteria row for the +ast*ame column and Duan in the riteria row for the irst*ame column. emove the chec mars from the how chec bo#es for the +ast*ame and irst*ame columns.
6.
&lace the ustomer*um and redit+imit fields in the design grid. $nclude the /otal row. &lace the ount function in the /otal row for the ustomer*um column. elect 0here in the /otal row for the redit+imit column and enter 19999 in the riteria row for the redit+imit column.
8.
&lace the alance and e"*um fields in the design grid. $nclude the /otal row. $n the alance column% select the um function in the /otal row. $n the e"*um column% select 0here in the /otal row and enter 35 in the riteria row.
19.
&lace the &art*um% :escri"tion% and lass fields in the design grid. $n the ne#t column% enter the com"utation for the nandalue (nand E &rice). /'"e 0 in the riteria row for the lass column% and remove the chec mar from the how chec bo# in the lass column.
11.
&lace all fields in the &art table in the design grid. elect Ascending in the ort row for the :escri"tion column.
12.
&lace all fields in the &art table in the design grid. $nclude the &art*um field a second time and remove the chec mar from its how chec bo#. elect Ascending in the ort row for the lass column. elect Ascending in the ort row for the second &art*um column.
13.
$nclude the /otal row. &lace the lass and nand fields in the design grid. $n the lass column% select the Frou" ' function in the /otal row. $n the /otal row for the nand column% select the um function.
14.
$nclude the field list for the &art table in the uer' design. &lace the &art*um% :escri"tion% lass% nand% 0arehouse% and &rice fields in the design grid. ,nter F in the riteria row for the lass column and remove the chec mar from the column7s how hec bo#. hange the uer' t'"e to @ae-/able uer'% and enter "ortingFoods as the name of the table.
15.
$nclude the field list for the "ortingFoods table in the uer' design and change the uer' t'"e to C"date uer'. &lace the &art*um and :escri"tion fields in the design grid. ,nter itness F'm in the C"date / o row for the :escri"tion column% and enter 9 as the criterion for the &art*um column.
1.
$nclude the field list for the "ortingFoods table in the uer' design and change the uer' t'"e to :elete uer'. &lace the &rice field in the design grid. ,nter G1999 as the criterion for the &rice column.
Answers to Premiere Products Exercises: Relational Algebra 1. &D,/ e" , (e"*um% +ast*ame% irst*ame) F$$*F Answer 2. ,+,/ &art 0,, &art*um H I:21I F$$*F Answer 3. D$* rders ustomer 0,, rders.ustomer*um H ustomer.ustomer*um F$$*F /em"1
Concepts of Database Management, Sixth Edition
Solutions 2-3
&D,/ /em"1 , (rder*um% rder:ate% ustomer*um% ustomer*ame) F$$*F Answer 4. D$* rders ustomer 0,, rders.ustomer*um H ustomer.ustomer*um F$$*F /em"1 D$* /em"1 e" 0,, /em"1.e"*um H e".e"*um F$$*F /em"2 ,+,/ /em"2 0,, e".+ast*ame H IJaiserI F$$*F /em"3 &D,/ /em"3 , (rder*um% rder:ate% ustomer*um% ustomer*ame) F$$*F Answer 5. ,+,/ rders 0 ,, rder:ateHI19=29=2919I F$$*F / em"1 &D,/ /em"1 , (rder*um% rder:ate) F$$*F /em"2 D$* rders ustomer 0,, rders.ustomer*um H ustomer.ustomer*um F$$*F /em"3 ,+,/ /em"3 0,, e"*um H I29I F$$*F /em"4 &D,/ /em"4 , (rder*um% rder:ate) F$$*F /em"5 C*$* /em"2 0$/ /em"5 F$$*F Answer . ,+,/ rders 0 ,, rder:ateHI19=29=2919I F$$*F / em"1 &D,/ /em"1 , (rder*um% rder:ate) F$$*F /em"2 D$* rders ustomer 0,, rders.ustomer*um H ustomer.ustomer*um F$$*F /em"3 ,+,/ /em"3 0,, e"*um H I29I F$$*F /em"4 &D,/ /em"4 , (rder*um% rder:ate) F$$*F /em"5 $*/,,/ /em"2 0$/ /em"5 F$$*F Answer . ,+,/ rders 0 ,, rder:ateHI19=29=2919I F$$*F / em"1 &D,/ /em"1 , (rder*um% rder:ate) F$$*F /em"2 D$* rders ustomer 0,, rders.ustomer*um H ustomer.ustomer*um F$$*F /em"3 ,+,/ /em"3 0,, e"*um H I29I F$$*F /em"4 &D,/ /em"4 , (rder*um% rder:ate) F$$*F /em"5 C/A/ /em"5 @ /em"2 F$$*F Answer
Answers to Henry Boos Case /he following answers indicate how to "erform the s"ecified tas in @icrosoft Access. /he "rocess for other database management s'stems would be different% although it should be similar. ee the $nstructor7s esource Jit on :-@ for a co"' of the enr' oos database that contains the solutions to these e#ercises. 1.
$nclude the Author*um and Author+ast fields in the design grid. :o not enter an' criteria.
2.
:ouble-clic the asteris in the ranch field list to add all fields to the design grid. :o not enter an' criteria.
3.
$nclude the &ublisher*ame and it' fields in the design grid. emove the chec mar from the how chec bo# in the it' column. /'"e oston in the riteria row for the it' column.
4.
$nclude the &ublisher*ame and it' fields in the design grid. emove the chec mar from the how chec bo# in the it' column. /'"e KGoston in the riteria row for the it' column.
5.
$nclude the ranch*ame and *um,m"lo'ees fields in the design grid. emove the chec mar from the how chec bo# in the *um,m"lo'ees column. /'"e GH8 in the riteria row for the *um,m"lo'ees column.
.
$nclude the ooode% /itle% and /'"e fields in the design grid. emove the chec mar from the how chec bo# in the /'"e column. /'"e $ in the riteria row for the / '"e column.
.
$nclude the ooode% /itle% /'"e% and &a"erbac fields in the design grid. emove the chec mar from the how chec bo# in the /'"e and &a"erbac columns. /'"e $ in the riteria row for the /'"e column. /'"e either /rue or Les in the riteria row of the &a"erbac column.
6.
$nclude the ooode% /itle% &ublisherode% and /'"e fields in the design grid. emove the chec mar from the how chec bo#es in the &ublisherode and /'"e columns. /'"e $ in the riteria row for the /'"e column. /'"e &, in the r row of the &ublisherode column.
8.
$nclude the ooode% /itle% and &rice fields in the design grid. /'"e G5 A*: K19 in the riteria row for the &rice column.
19.
$nclude the ooode% /itle% /'"e% and &rice fields in the design grid. emove the chec mar from the how chec bo#es in the /'"e and &rice columns. /'"e $ in the riteria row for the /'"e column. /'"e K19 in the riteria row for the &rice column.
Concepts of Database Management, Sixth Edition
Solutions 2-4
11.
$nclude the ooode and /itle fields in the design grid. ,nter the e#"ression :iscounted&rice?9.65EM&riceN in the first o"en column in the design grid.
12.
$nclude the ooode% /itle% and /'"e fields in the design grid. emove the chec mar from the how chec bo# in the /'"e column. /'"e $ in the riteria row for the /'"e column. /'"e in r row for the /'"e column. /'"e A/ in the row below the r row for the /'"e column.
13.
$nclude the ooode% /itle% and &ublisher fields in the design grid. $nclude the /itle column a second time and remove the chec mar from its how chec bo#. elect Ascending in the ort row for the &ublisherode column. elect Ascending in the ort row for the second /itle column.
14.
$nclude the /otal row. Add the ooode and /'"e fields to the design grid. elect the ount function in the /otal row for the ooode column. elect 0here in the /otal row for the /'"e column. ,nter $ in the riteria row for the /'"e column.
15.
$nclude the /otal row. Add the /'"e and &rice fields in the design grid. @ae sure the Frou" ' o"erator is selected in the /otal row for the /'"e column. elect the Avg function in the /otal row for the &rice column.
1.
$nclude the field lists for the oo and &ublisher tables in the uer' design. $nclude the ooode and /itle fields from the oo table. $nclude the &ublisherode and &ublisher*ame fields from the &ublisher table.
1.
$nclude the field lists for the oo and &ublisher tables in the uer' design. $nclude the /itle and &rice fields from the oo table. $nclude the &ublisher*ame field from the &ublisher table and remove the chec mar from the how chec bo# in the &ublisher*ame column. /'"e /aunton &ress in the riteria row for the &ublisher*ame column.
16.
$nclude the field lists for the oo and &ublisher tables in the uer' design. $nclude the ooode% /itle% and &rice fields from the oo table. emove the chec mar from the how chec bo# in the &rice column. $nclude the &ublisher*ame field from the &ublisher table and remove the chec mar from the how chec bo# in the &ublisher*ame column. /'"e &utnam &ublishing Frou" in the riteria row for the &ublisher*ame column and enter G15 in the riteria row for the &rice column.
18.
$nclude the ooode% /itle% &ublisherode% /'"e% and &rice columns in the design grid. ,nter $ in the riteria row for the /'"e column and remove the chec mar from the column7s how hec bo#. hange the uer' t'"e to @ae-/able uer'% and enter iction as the name of the table.
29.
$nclude the field list for the iction table in the design grid and change the uer' t'"e to C"date uer'. $nclude the &rice field in the design grid% enter 14.59 in the C"date /o row of the &rice column% and enter 14.99 in the riteria row for the &rice column.
21.
$nclude the field list for the iction table in the uer' design and change the uer' t'"e to :elete uer'. $nclude the &ublisherode field in the design grid and enter in the riteria row for the &ublisherode column.
Answers to Alexamara !arina "roup Case /he following answers indicate how to "erform the s"ecified tas in @icrosoft Access. /he "rocess for other database management s'stems would be different% although it should be similar. ee the $nstructor7s esource Jit on :-@ for a co"' of the Ale#amara database that contains the solutions to these e#ercises. 1.
$nclude the wner*um% +ast*ame% and irst*ame fields in the design grid. :o not enter an' criteria.
2.
:ouble-clic the asteris in the @arina field list to add all fields to the design grid. :o not enter an' criteria.
3.
$nclude the +ast*ame% irst*ame% and it' fields from the wner table in the design grid. emove the chec mar from the how chec bo# for the it' column. ,nter owton in the riteria row for the it' column.
4.
$nclude the +ast*ame% irst*ame% and it' fields from the wner table in the design grid. emove the chec mar from the how chec bo# for the it' column. ,nter KG owton in the riteria row for the it' column.
5.
$nclude the @arina*um% li"*um% and +ength fields from the @arinali" table in the design grid. emove the chec mar from the how chec bo# for the +ength column. ,nter KH39 in the riteria row for the +ength column.
.
$nclude the @arina*um% li"*um% and oat/'"e fields from the @arinali" table in the design grid. emove the chec mar from the how chec bo# for the oat/'"e column. ,nter :ol"hin 26 in the riteria row for the oat/'"e column.
.
$nclude the li"*um% @arina*um% and oat/'"e fields from the @arinali" table in the design grid. emove the chec mars from the how chec bo#es for the @arina*um and oat/'"e column. ,nter 1 in the riteria row for the @arin*um column. ,nter :ol"hin 26 in the riteria row for the oat/'"e column.
6.
$nclude the oat*ame and +ength fields from the @arinali" table in the design grid. emove the chec mar from the how chec bo# for the +ength column. ,nter GH25 A*: KH39 in the riteria row for the +ength column.
8.
$nclude the li"*um% @arina*um% and entalee fields from the @arinali" table in the design grid. emove the chec mar from the how chec bo#es for the @arina*um and entalee columns. /'"e 1 in the riteria row for the @arina*um column. /'"e K3999 in the riteria row for the entalee column.
Concepts of Database Management, Sixth Edition
Solutions 2-5
19.
$nclude the li"$:% ategor'*um% and ,stours fields from the erviceeuest table in the design grid. ,nter the e#"ression ,stimatedost?9EM,stoursN in the first o"en column in the design grid.
11.
$nclude the @arina*um% li"*um% and oat/'"e fields from the @arinali" table in the design grid. emove the chec mar from the how chec bo# for the oat/'"e column. ,nter "rite 4999 in the riteria row for the /'"e column. / '"e "rite 3999 in the r row for the /'"e column. /'"e a' 4925 in the row below the r row in the /'"e column.
12.
$nclude the @arina*um% li"*um% and oat*ame fields from the @arinali" table in the design grid. elect Ascending in the ort row for the @arina*um and oat*ame columns.
13.
Add the @arina*um and oat/'"e fields from the @arinali" table in the design grid. $nclude the /otal row. elect the ount function in the /otal row for the @arina*um column. elect 0here in the /otal row for the oat/'"e column. ,nter :ol"hin 25 in the riteria row for the oat/'"e column.
14.
Add the +ength and entalee fields from the @arinali" table in the design grid. $nclude the /otal row. @ae sure the Frou" ' o"erator is selected in the /otal row for the +ength column. elect the um function in the /otal row for the entalee column.
15.
$nclude the field lists for the @arinali" and wner tables in the uer' design. $nclude the @arina*um% li"*um% and oat*ame fields from the @arinali" table. $nclude the wner*um% irst*ame% and +ast*ame fields from the wner table.
1.
$nclude the field lists for the erviceeuest and erviceategor' tables in the uer' design. $nclude the li"$:% :escri"tion% and tatus fields from the erviceeuest table. $nclude the ategor':escri"tion field from the ategor' table and remove the chec mar from the how chec bo# for the ategor':escri"tion column. ,nter outine engine maintenance in the riteria row for the ategor':escri"tion column.
1.
$nclude the field lists for the erviceeuest% @arinali"% erviceategor'% and wner tables in the uer' design. $nclude the li"$: field from the erviceeuest table. $nclude the @arina*um and li"*um fields from the @arinali" table. $nclude the ,stours and "entours fields from the erviceeuest table. $nclude the ategor':escri"tion field from the erviceategor' table and remove the chec mar from the how chec bo# for the ategor':escri"tion column. /'"e outine engine maintenance in the riteria row for the ategor':escri"tion column. $nclude the wner*um and +ast*ame fields from the wner table.
16.
$nclude the field list for the @arinali" table in the uer' design. $nclude the @arina*um% li"*um% entalee% oat*ame% wner*um% and +ength columns in the design grid. ,nter 49 in the riteria r"w f"r the +ength column. emove the chec mar from the how chec bo# for the +ength column. hange the uer' t'"e to @ae-/able uer'% and enter +argeli" as the name of the table.
18.
$nclude the field list for the +argeli" table in the uer' design and change the uer' t'"e to C"date uer'. $nclude the entalee field in the design grid% enter 3899 in the C"date /o row for the entalee column% and enter 3699 in the riteria row for the entalee column.
29.
$nclude the field list for the +argeli" table in the uer' design and change the uer' t'"e to :elete uer'. $nclude the entalee field in the design grid and enter 399 in the riteria row for the entalee column.