Chapter 1: Databases and Database Users
1
CHAPTER 1: DATABASES DATABASES AND DATABASE DATABASE USERS Answers to Selected Exercises
- Identify some informal informal queries and update operations that you would expect to apply to the database shown in Figure 1! 1.8
Answer:
"a# "$uery# %ist the names of all students ma&oring in Computer 'cience "b# "$uery# (hat are the prerequisites of the Database course) "c# "$uery# *etrie+e the transcript of 'mith ,his is a list of Course.ame/ 'ectionIdentifier/ 'emester/ 'emester/ 0ear 0ear// rade2 for each course section that 'mith has completed "d# "Update# Insert a new student in the database whose .ame34ac5son/ 'tudent.umber3!6/ Class31 "freshman#/ and 7a&or378,9 7a&or378,9 "e# "Update# Change the grade that 'mith recei+ed in Intro to Computer 'cience section 11 to ;
1.9
- (hat is the difference between controlled and uncontrolled redundancy)
Answer:
*edundancy is when the same fact is stored multiple times in se+eral places in a database For example/ in Figure 1<"a# the fact that the name of the student with 'tudent.umber3= is ;rown is stored multiple times *edundancy is controlled when the D;7' ensures that multiple copies of the same data are consistent> for example/ if a new record with 'tudent.umber3= is stored in the database of Figure 1<"a#/ the D;7' will ensure that 'tudent.ame3'mith in that record If the D;7' has no control o+er this/ we ha+e uncontrolled redundancy redundancy 1.10 -
'pecify all the relationships among the records of the database shown in Figure 1!
Answer:
"a# ?ach '?C,I@. record is related to a C@U*'? record "b# ?ach *8D?A*?B@*, record is related to one ',UD?., record and one '?C,I@. record "c# ?ach B*?*?$UI'I,? record relates two C@U*'? records: one in the role of a course and the other in the role of a prerequisite to that course 1.11 -
i+e some additional +iews that may be needed by other user groups for the database shown in Figure 1!
Answer:
"a# 8 +iew that groups all the students who too5 each section and gi+es each students grade ,his may be useful for printing the grade report for each section for the
Copyright © 2011 Pearson Education, Inc. Publishing as Pearson Addison-Wesley.
!
Chapter 1: Databases and Database Users
uni+ersity administrations use "b# 8 +iew that gi+es the number of courses ta5en and the B8 "grade point a+erage# for each student ,his may be used to determine honors students 1.1
Cite some examples of integrity constraints that you thin5 can apply to the database shown in Figure 1! Answer:
(e gi+e a few constraints expressed in ?nglish Following each constraint/ we gi+e its type in the relational database terminology that will be co+ered in Chapter E/ for reference purposes "a# ,he 'tudent.umber should be unique for each ',UD?., record "5ey constraint# "b# ,he Course.umber should be unique for each C@U*'? record "5ey constraint# "c# 8 +alue of Course.umber in a '?C,I@. record must also exist in some C@U*'? record "referential integrity constraint# "d# 8 +alue of 'tudent.umber in a *8D?A*?B@*, record must also exist in some ',UD?., record "referential integrity constraint# "e# ,he +alue of rade in a *8D?A*?B@*, record must be one of the +alues in the set 8/ ;/ C/ D/ F/ I/ U/ 'G "domain constraint# "f# ?+ery record in C@U*'? must ha+e a +alue for Course.umber "entity integrity constraint# "g# 8 ',UD?., record cannot ha+e a +alue of Class3! "sophomore# unless the student has completed a number of sections whose total course Credit9ours is greater that !H credits "general semantic integrity constraint# 1.1! -
i+e examples of systems in which it may ma5e sense to use traditional file processing instead of a database approach Answer:
1 'mall internal utility to locate files ! 'mall single user application that does not require security "such as a customied calculator or a personal address and phone boo5# 6 *eal-time na+igation system "with hea+y computation and +ery little data# H ,he students may thin5 of others
1.1" -
Consider Figure 1! a If the name of the JC'K "Computer 'cience# Department changes to JC''?K "Computer 'cience and 'oftware ?ngineering# Department and the corresponding prefix for the course number also changes/ identify the columns in the database that would need to be updated b Can you restructure the columns in C@U*'?/ '?C,I@./ and B*?*?$UI'I,? tables so that only one column will need to be updated)
Copyright © 2011 Pearson Education, Inc. Publishing as Pearson Addison-Wesley.
Chapter 1: Databases and Database Users
Answer:
a ,he following columns will need to be updated ,able Column"s# ',UD?., 7a&or C@U*'? Course.umber and Department '?C,I@. Course.umber B*?*?$UI'I,? Course.umber and Brerequisite.umber b 0ou should split the following columns into two columns: ,able Column 'plit Columns C@U*'? Course.umber CourseDept and Course.um '?C,I@. Course.umber CourseDept and Course.um B*?*?$UI'I,? Course.umber CourseDept and Course.um B*?*?$UI'I,? Brerequisite.umber Bre*eqDept and Bre*eq.um .ote that in the C@U*'? table/ the column CourseDept will not be ne eded after the abo+e change/ since it is redundant with the Department column
Copyright © 2011 Pearson Education, Inc. Publishing as Pearson Addison-Wesley.
6
