Chapter 1: Databases and Database Users 1
CHAPTER 1: DATABASES AND DATABASE USERS
Answers to Selected Exercises
1.8 - Identify some informal queries and update operations that you would expect to apply to
the database shown in Figure 1.2.
Answer:
(a) (Query) List the names of all students majoring in Computer Science.
(b) (Query) What are the prerequisites of the Database course?
(c) (Query) Retrieve the transcript of Smith. This is a list of <CourseName,
SectionIdentifier, Semester, Year, Grade> for each course section that Smith has completed.
(d) (Update) Insert a new student in the database whose Name=Jackson,
StudentNumber=23, Class=1 (freshman), and Major=MATH.
(e) (Update) Change the grade that Smith received in Intro to Computer Science section
119 to B.
1.9 - What is the difference between controlled and uncontrolled redundancy?
Answer:
Redundancy is when the same fact is stored multiple times in several places in a database.
For example, in Figure 1.5(a) the fact that the name of the student with StudentNumber=8 is
Brown is stored multiple times. Redundancy is controlled when the DBMS ensures that
multiple copies of the same data are consistent; for example, if a new record with
StudentNumber=8 is stored in the database of Figure 1.5(a), the DBMS will ensure that
StudentName=Smith in that record. If the DBMS has no control over this, we have
uncontrolled redundancy.
1.10 - Specify all the relationships among the records of the database shown in Figure 1.2.
Answer:
(a) Each SECTION record is related to a COURSE record.
(b) Each GRADE_REPORT record is related to one STUDENT record and one SECTION
record.
(c) Each PREREQUISITE record relates two COURSE records: one in the role of a course
and the other in the role of a prerequisite to that course.
1.11 - Give some additional views that may be needed by other user groups for the database
shown in Figure 1.2.
Answer:
Copyright © 2016 Pearson Education, Inc., Hoboken NJ
, Chapter 2: Database System Concepts and Architecture 1
CHAPTER 2: DATABASE SYSTEM CONCEPTS AND ARCHITECTURE
Answers to Selected Exercises
2.12 - Think of different users for the database of Figure 1.2. What type of applications would
each user need? To which user category would each belong and what type of interface
would they need?
Answer:
(a) Registration Office User: They can enter data that reflect the registration of students in
sections of courses, and later enter the grades of the students. Applications can include:
- Register a student in a section of a course
- Check whether a student who is registered in a course has the appropriate prerequisite
courses
- Drop a student from a section of a course
- Add a student to a section of a course
- Enter the student grades for a section
Application programmers can write a number of canned transactions for the registration
office end-users, providing them with either forms and menus, or with a parametric interface.
(b) Admissions Office User: The main application is to enter newly accepted students into the
database. Can use the same type of interfaces as (a).
(c) Transcripts Office User: The main application is to print student transcripts.
Application programmers can write a canned transaction using a report generator utility to
print the transcript of a student in a prescribed format. The particular student can be
identified by name or social security number. Another application would be to generate grade
slips at the end of each semester for all students who have completed courses during that
semester. Again, this application could be programmed using a report generator utility.
2.13 - No solution provided.
2.14 - if you were designing a Web-based system to make airline reservations and to sell
airline tickets, which DBMS Architecture would you choose from Section 2.5? Why? Why
would the other architectures not be a good choice?
Answer:
2.5.4 Three-Tier Client/Server Architecture for Web Application is the best choice. The Client
consists of Web User Interface. The Web Server contains the application logic which
includes all the rules and regulations related to the reservation process and the issue of
tickets; the Database Server contains the DBMS.
2.5.1 Centralized DBMS Architecture would not work since the user interface and database
server are on different machines for a web-based system.
2.5.2 Basic Client/Server Architecture and 2.5.3 Two-Tier Client/Server Architecture would
work if the Business Logic can reside on server other than the DBMS Server. In general, if
the business logic was on the DBMS Server, it will put an excessive burden on the server. If
the business logic were to reside on the web client, it will burden the communication network
as well a possibly thin client.
2.15 - Consider Figure 2.1. In addition to constraints relating the values of columns in one
table to columns in another table, there are also constraints that impose restrictions on
Copyright © 2016 Pearson Education, Inc., Hoboken NJ
,2 Chapter 2: Database System Concepts and Architecture
values in a column or a combination of columns within a table. One such constraint forces
that a column or a group of columns must be unique across all rows in the table. For
example, in the STUDENT table, the StudentNumber column must be unique (to prevent two
different students from having the same StudentNumber). Identify the column or the group
of columns in the other tables that must be unique across all rows in the table?
Answer:
Table Column(s)
COURSE CourseNumber
Since this contains the combination of the department and the number
that must be unique within the department. Note we will overlook the
fact this does not accommodate a department from offering several
“Special Topics” course with the same CourseNumber but different
titles. We could make this a combination of CourseNumber and
CourseName, but this is more succeptible to someone mistyping while
entering data.
PREREQUISITE The combination of CourseNumber and PrerequisiteNumber
SECTION SectionIdentifier
We assume that no two sections can have the same SectionIdentifier.
If we were to consider that SectionIdentifier is unique only within a
given course offered in a given term (such as section 2 of CS101)
then the answer changes to the combination of SectionIdentifier,
CourseNumber, Semester, and Year.
GRADE_REPORT StudentNumber and SectionIdentifier
As per assumption stated in SECTION, the SectionIdentifier will be
different if a student takes the same course or a different course in
another term.
Copyright © 2016 Pearson Education, Inc., Hoboken NJ
, Chapter 3: Data Modeling Using the Entity-Relationship (ER) Model 1
CHAPTER 3: DATA MODELING USING THE ENTITY-RELATIONSHIP (ER) MODEL
Answers to Selected Exercises
3.16 - Consider the following set of requirements for a UNIVERSITY database that is used to
keep track of students' transcripts. This is similar but not identical to the database shown in
Figure 1.2:
(a) The university keeps track of each student's name, student number, social security
number, current address and phone, permanent address and phone, birthdate, sex, class
(freshman, sophomore, ..., graduate), major department, minor department (if any), and
degree program (B.A., B.S., ..., Ph.D.). Some user applications need to refer to the city,
state, and zip of the student's permanent address, and to the student's last name. Both
social security number and student number have unique values for each student.
(b) Each department is described by a name, department code, office number, office phone,
and college. Both name and code have unique values for each department.
(c) Each course has a course name, description, course number, number of semester hours,
level, and offering department. The value of course number is unique for each course.
(d) Each section has an instructor, semester, year, course, and section number. The section
number distinguishes different sections of the same course that are taught during the same
semester/year; its values are 1, 2, 3, ..., up to the number of sections taught during each
semester.
(e) A grade report has a student, section, letter grade, and numeric grade (0, 1, 2, 3,
4 for F, D, C, B, A, respectively).
Design an ER schema for this application, and draw an ER diagram for that schema.
Specify key attributes of each entity type and structural constraints on each relationship type.
Note any unspecified requirements, and make appropriate assumptions to make the
specification complete.
Answer:
Copyright © 2016 Pearson Education, Inc., Hoboken NJ
CHAPTER 1: DATABASES AND DATABASE USERS
Answers to Selected Exercises
1.8 - Identify some informal queries and update operations that you would expect to apply to
the database shown in Figure 1.2.
Answer:
(a) (Query) List the names of all students majoring in Computer Science.
(b) (Query) What are the prerequisites of the Database course?
(c) (Query) Retrieve the transcript of Smith. This is a list of <CourseName,
SectionIdentifier, Semester, Year, Grade> for each course section that Smith has completed.
(d) (Update) Insert a new student in the database whose Name=Jackson,
StudentNumber=23, Class=1 (freshman), and Major=MATH.
(e) (Update) Change the grade that Smith received in Intro to Computer Science section
119 to B.
1.9 - What is the difference between controlled and uncontrolled redundancy?
Answer:
Redundancy is when the same fact is stored multiple times in several places in a database.
For example, in Figure 1.5(a) the fact that the name of the student with StudentNumber=8 is
Brown is stored multiple times. Redundancy is controlled when the DBMS ensures that
multiple copies of the same data are consistent; for example, if a new record with
StudentNumber=8 is stored in the database of Figure 1.5(a), the DBMS will ensure that
StudentName=Smith in that record. If the DBMS has no control over this, we have
uncontrolled redundancy.
1.10 - Specify all the relationships among the records of the database shown in Figure 1.2.
Answer:
(a) Each SECTION record is related to a COURSE record.
(b) Each GRADE_REPORT record is related to one STUDENT record and one SECTION
record.
(c) Each PREREQUISITE record relates two COURSE records: one in the role of a course
and the other in the role of a prerequisite to that course.
1.11 - Give some additional views that may be needed by other user groups for the database
shown in Figure 1.2.
Answer:
Copyright © 2016 Pearson Education, Inc., Hoboken NJ
, Chapter 2: Database System Concepts and Architecture 1
CHAPTER 2: DATABASE SYSTEM CONCEPTS AND ARCHITECTURE
Answers to Selected Exercises
2.12 - Think of different users for the database of Figure 1.2. What type of applications would
each user need? To which user category would each belong and what type of interface
would they need?
Answer:
(a) Registration Office User: They can enter data that reflect the registration of students in
sections of courses, and later enter the grades of the students. Applications can include:
- Register a student in a section of a course
- Check whether a student who is registered in a course has the appropriate prerequisite
courses
- Drop a student from a section of a course
- Add a student to a section of a course
- Enter the student grades for a section
Application programmers can write a number of canned transactions for the registration
office end-users, providing them with either forms and menus, or with a parametric interface.
(b) Admissions Office User: The main application is to enter newly accepted students into the
database. Can use the same type of interfaces as (a).
(c) Transcripts Office User: The main application is to print student transcripts.
Application programmers can write a canned transaction using a report generator utility to
print the transcript of a student in a prescribed format. The particular student can be
identified by name or social security number. Another application would be to generate grade
slips at the end of each semester for all students who have completed courses during that
semester. Again, this application could be programmed using a report generator utility.
2.13 - No solution provided.
2.14 - if you were designing a Web-based system to make airline reservations and to sell
airline tickets, which DBMS Architecture would you choose from Section 2.5? Why? Why
would the other architectures not be a good choice?
Answer:
2.5.4 Three-Tier Client/Server Architecture for Web Application is the best choice. The Client
consists of Web User Interface. The Web Server contains the application logic which
includes all the rules and regulations related to the reservation process and the issue of
tickets; the Database Server contains the DBMS.
2.5.1 Centralized DBMS Architecture would not work since the user interface and database
server are on different machines for a web-based system.
2.5.2 Basic Client/Server Architecture and 2.5.3 Two-Tier Client/Server Architecture would
work if the Business Logic can reside on server other than the DBMS Server. In general, if
the business logic was on the DBMS Server, it will put an excessive burden on the server. If
the business logic were to reside on the web client, it will burden the communication network
as well a possibly thin client.
2.15 - Consider Figure 2.1. In addition to constraints relating the values of columns in one
table to columns in another table, there are also constraints that impose restrictions on
Copyright © 2016 Pearson Education, Inc., Hoboken NJ
,2 Chapter 2: Database System Concepts and Architecture
values in a column or a combination of columns within a table. One such constraint forces
that a column or a group of columns must be unique across all rows in the table. For
example, in the STUDENT table, the StudentNumber column must be unique (to prevent two
different students from having the same StudentNumber). Identify the column or the group
of columns in the other tables that must be unique across all rows in the table?
Answer:
Table Column(s)
COURSE CourseNumber
Since this contains the combination of the department and the number
that must be unique within the department. Note we will overlook the
fact this does not accommodate a department from offering several
“Special Topics” course with the same CourseNumber but different
titles. We could make this a combination of CourseNumber and
CourseName, but this is more succeptible to someone mistyping while
entering data.
PREREQUISITE The combination of CourseNumber and PrerequisiteNumber
SECTION SectionIdentifier
We assume that no two sections can have the same SectionIdentifier.
If we were to consider that SectionIdentifier is unique only within a
given course offered in a given term (such as section 2 of CS101)
then the answer changes to the combination of SectionIdentifier,
CourseNumber, Semester, and Year.
GRADE_REPORT StudentNumber and SectionIdentifier
As per assumption stated in SECTION, the SectionIdentifier will be
different if a student takes the same course or a different course in
another term.
Copyright © 2016 Pearson Education, Inc., Hoboken NJ
, Chapter 3: Data Modeling Using the Entity-Relationship (ER) Model 1
CHAPTER 3: DATA MODELING USING THE ENTITY-RELATIONSHIP (ER) MODEL
Answers to Selected Exercises
3.16 - Consider the following set of requirements for a UNIVERSITY database that is used to
keep track of students' transcripts. This is similar but not identical to the database shown in
Figure 1.2:
(a) The university keeps track of each student's name, student number, social security
number, current address and phone, permanent address and phone, birthdate, sex, class
(freshman, sophomore, ..., graduate), major department, minor department (if any), and
degree program (B.A., B.S., ..., Ph.D.). Some user applications need to refer to the city,
state, and zip of the student's permanent address, and to the student's last name. Both
social security number and student number have unique values for each student.
(b) Each department is described by a name, department code, office number, office phone,
and college. Both name and code have unique values for each department.
(c) Each course has a course name, description, course number, number of semester hours,
level, and offering department. The value of course number is unique for each course.
(d) Each section has an instructor, semester, year, course, and section number. The section
number distinguishes different sections of the same course that are taught during the same
semester/year; its values are 1, 2, 3, ..., up to the number of sections taught during each
semester.
(e) A grade report has a student, section, letter grade, and numeric grade (0, 1, 2, 3,
4 for F, D, C, B, A, respectively).
Design an ER schema for this application, and draw an ER diagram for that schema.
Specify key attributes of each entity type and structural constraints on each relationship type.
Note any unspecified requirements, and make appropriate assumptions to make the
specification complete.
Answer:
Copyright © 2016 Pearson Education, Inc., Hoboken NJ
