Solution Manual for Fundamentals of Database Systems , 7th Edition
Solution Manual for Fundamentals of Database Systems , 7th Edition gives you the answers you need, explained in a simple and clear way.
Victoria Lee
Contributor
4.8
65
about 2 months ago
Preview (31 of 150)
Sign in to access the full document!
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:
(a) A view that groups all the students who took each section and gives each student's
grade. This may be useful for printing the grade report for each section for the university
administration's use.
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:
(a) A view that groups all the students who took each section and gives each student's
grade. This may be useful for printing the grade report for each section for the university
administration's use.
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:
(a) A view that groups all the students who took each section and gives each student's
grade. This may be useful for printing the grade report for each section for the university
administration's use.
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:
(a) A view that groups all the students who took each section and gives each student's
grade. This may be useful for printing the grade report for each section for the university
administration's use.
Chapter 1: Databases and Database Users2
(b) A view that gives the number of courses taken and the GPA (grade point average) for
each student. This may be used to determine honors students.
1.12 – Cite some examples of integrity constraints that you think can apply to the database
shown in Figure 1.2.
Answer:
We give a few constraints expressed in English. Following each constraint, we give its type
in the relational database terminology that will be covered in Chapter 6, for reference
purposes.
(a) The StudentNumber should be unique for each STUDENT record (key constraint).
(b) The CourseNumber should be unique for each COURSE record (key constraint).
(c) A value of CourseNumber in a SECTION record must also exist in some COURSE record
(referential integrity constraint).
(d) A value of StudentNumber in a GRADE_REPORT record must also exist in some
STUDENT record (referential integrity constraint).
(e) The value of Grade in a GRADE_REPORT record must be one of the values in the set
{A, B, C, D, F, I, U, S} (domain constraint).
(f) Every record in COURSE must have a value for CourseNumber (entity integrity
constraint).
(g) A STUDENT record cannot have a value of Class=2 (sophomore) unless the student has
completed a number of sections whose total course CreditHours is greater than 24 credits
(general semantic integrity constraint).
1.13 - Give examples of systems in which it may make sense to use traditional file
processing instead of a database approach.
Answer:
1. Small internal utility to locate files
2. Small single user application that does not require security (such as a customized
calculator or a personal address and phone book)
3. Real-time navigation system (with heavy computation and very little data)
4. The students may think of others.
1.14 - Consider Figure 1.2.
a. If the name of the ‘CS’ (Computer Science) Department changes to ‘CSSE’ (Computer
Science and Software Engineering) 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 COURSE, SECTION, and PREREQUISITE tables so
that only one column will need to be updated?
Answer:
(b) A view that gives the number of courses taken and the GPA (grade point average) for
each student. This may be used to determine honors students.
1.12 – Cite some examples of integrity constraints that you think can apply to the database
shown in Figure 1.2.
Answer:
We give a few constraints expressed in English. Following each constraint, we give its type
in the relational database terminology that will be covered in Chapter 6, for reference
purposes.
(a) The StudentNumber should be unique for each STUDENT record (key constraint).
(b) The CourseNumber should be unique for each COURSE record (key constraint).
(c) A value of CourseNumber in a SECTION record must also exist in some COURSE record
(referential integrity constraint).
(d) A value of StudentNumber in a GRADE_REPORT record must also exist in some
STUDENT record (referential integrity constraint).
(e) The value of Grade in a GRADE_REPORT record must be one of the values in the set
{A, B, C, D, F, I, U, S} (domain constraint).
(f) Every record in COURSE must have a value for CourseNumber (entity integrity
constraint).
(g) A STUDENT record cannot have a value of Class=2 (sophomore) unless the student has
completed a number of sections whose total course CreditHours is greater than 24 credits
(general semantic integrity constraint).
1.13 - Give examples of systems in which it may make sense to use traditional file
processing instead of a database approach.
Answer:
1. Small internal utility to locate files
2. Small single user application that does not require security (such as a customized
calculator or a personal address and phone book)
3. Real-time navigation system (with heavy computation and very little data)
4. The students may think of others.
1.14 - Consider Figure 1.2.
a. If the name of the ‘CS’ (Computer Science) Department changes to ‘CSSE’ (Computer
Science and Software Engineering) 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 COURSE, SECTION, and PREREQUISITE tables so
that only one column will need to be updated?
Answer:
Chapter 1: Databases and Database Users 3
a. The following columns will need to be updated.
Table Column(s)
STUDENT Major
COURSE CourseNumber and Department
SECTION CourseNumber
PREREQUISITE CourseNumber and PrerequisiteNumber
b. You should split the following columns into two columns:
Table Column Split Columns
COURSE CourseNumber CourseDept and CourseNum
SECTION CourseNumber CourseDept and CourseNum
PREREQUISITE CourseNumber CourseDept and CourseNum
PREREQUISITE PrerequisiteNumber PreReqDept and PreReqNum
Note that in the COURSE table, the column CourseDept will not be needed after the above
change, since it is redundant with the Department column.
a. The following columns will need to be updated.
Table Column(s)
STUDENT Major
COURSE CourseNumber and Department
SECTION CourseNumber
PREREQUISITE CourseNumber and PrerequisiteNumber
b. You should split the following columns into two columns:
Table Column Split Columns
COURSE CourseNumber CourseDept and CourseNum
SECTION CourseNumber CourseDept and CourseNum
PREREQUISITE CourseNumber CourseDept and CourseNum
PREREQUISITE PrerequisiteNumber PreReqDept and PreReqNum
Note that in the COURSE table, the column CourseDept will not be needed after the above
change, since it is redundant with the Department column.
Loading page 4...
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
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
Loading page 5...
Chapter 2: Database System Concepts and Architecture2
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.
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.
Loading page 6...
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:
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:
Loading page 7...
Chapter 3: Data Modeling Using the Entity-Relationship (ER) Model2
3.17 - Composite and multi-valued attributes can be nested to any number of levels.
Suppose we want to design an attribute for a STUDENT entity type to keep track of previous
college education. Such an attribute will have one entry for each college previously attended,
and this entry is composed of: college name, start and end dates, degree entries (degrees
awarded at that college, if any), and transcript entries (courses completed at that college, if
any). Each degree entry is formed of degree name and the month and year it was awarded,
and each transcript entry is formed of a course name, semester, year, and grade. Design an
attribute to hold this information. Use the conventions of Figure 7.5.
Answer:
{ PreviousEducation ( CollegeName, StartDate, EndDate,
{ Degree (DegreeName, Month, Year) },
{ Transcript (CourseName, Semester, Year, Grade) } ) }
3.17 - Composite and multi-valued attributes can be nested to any number of levels.
Suppose we want to design an attribute for a STUDENT entity type to keep track of previous
college education. Such an attribute will have one entry for each college previously attended,
and this entry is composed of: college name, start and end dates, degree entries (degrees
awarded at that college, if any), and transcript entries (courses completed at that college, if
any). Each degree entry is formed of degree name and the month and year it was awarded,
and each transcript entry is formed of a course name, semester, year, and grade. Design an
attribute to hold this information. Use the conventions of Figure 7.5.
Answer:
{ PreviousEducation ( CollegeName, StartDate, EndDate,
{ Degree (DegreeName, Month, Year) },
{ Transcript (CourseName, Semester, Year, Grade) } ) }
Loading page 8...
Chapter 3: Data Modeling Using the Entity-Relationship (ER) Model 3
3.18 - Show an alternative design for the attribute described in Exercise 7.17 that uses only
entity types (including weak entity types if needed) and relationship types.
Answer:
This example illustrates a perceived weakness of the ER model, which is: how does the
database designer decide what to model as an entity type and what to model as a
relationship type. In our solution, we created a weak entity type ATTENDANCE; each (weak)
entity in ATTENDANCE represents a period in which a STUDENT attended a particular
COLLEGE, and is identified by the STUDENT and the StartDate of the period. Hence, the
StartDate attribute is the partial key of ATTENDANCE. Each ATTENDANCE entity is related
to one COLLEGE and zero or more DEGREEs (the degrees awarded during that attendance
period). The TRANSCRIPT of the STUDENT during each attendance period is modeled as a
weak entity type, which gives the records of the student during the attendance period. Each
(weak) entity in TRANSCRIPT gives the record of the sudent in one course during the
attendance period, as shown in the ER diagram below. Other ER schema designs are also
possible for this problem.
3.18 - Show an alternative design for the attribute described in Exercise 7.17 that uses only
entity types (including weak entity types if needed) and relationship types.
Answer:
This example illustrates a perceived weakness of the ER model, which is: how does the
database designer decide what to model as an entity type and what to model as a
relationship type. In our solution, we created a weak entity type ATTENDANCE; each (weak)
entity in ATTENDANCE represents a period in which a STUDENT attended a particular
COLLEGE, and is identified by the STUDENT and the StartDate of the period. Hence, the
StartDate attribute is the partial key of ATTENDANCE. Each ATTENDANCE entity is related
to one COLLEGE and zero or more DEGREEs (the degrees awarded during that attendance
period). The TRANSCRIPT of the STUDENT during each attendance period is modeled as a
weak entity type, which gives the records of the student during the attendance period. Each
(weak) entity in TRANSCRIPT gives the record of the sudent in one course during the
attendance period, as shown in the ER diagram below. Other ER schema designs are also
possible for this problem.
Loading page 9...
Chapter 3: Data Modeling Using the Entity-Relationship (ER) Model4
3.19 - Consider the ER diagram of Figure 7.20, which shows a simplified schema for an
airline reservations system. Extract from the ER diagram the requirements and constraints
that resulted in this schema. Try to be as precise as possible in your requirements and
constraints specification.
Answer:
(1) The database represents each AIRPORT, keeping its unique AirportCode, the AIRPORT
Name, and the City and State in which the AIRPORT is located.
(2) Each airline FLIGHT has a unique number, the Airline for the FLIGHT, and the Weekdays
on which the FLIGHT is scheduled (for example, every day of the week except Sunday can
be coded as X7).
(3) A FLIGHT is composed of one or more FLIGHT LEGs (for example, flight number
CO1223 from New York to Los Angeles may have two FLIGHT LEGs: leg 1 from New York
to Houston and leg 2 from Houston to Los Angeles). Each FLIGHT LEG has a DEPARTURE
AIRPORT and Scheduled Departure Time, and an ARRIVAL AIRPORT and Scheduled
Arrival Time.
(4) A LEG INSTANCE is an instance of a FLIGHT LEG on a specific Date (for example,
CO1223 leg 1 on July 30, 1989). The actual Departure and Arrival AIRPORTs and
Times are recorded for each flight leg after the flight leg has been concluded. The
Number of available seats and the AIRPLANE used in the LEG INSTANCE are also kept.
(5) The customer RESERVATIONs on each LEG INSTANCE include the Customer Name,
Phone, and Seat Number(s) for each reservation.
3.19 - Consider the ER diagram of Figure 7.20, which shows a simplified schema for an
airline reservations system. Extract from the ER diagram the requirements and constraints
that resulted in this schema. Try to be as precise as possible in your requirements and
constraints specification.
Answer:
(1) The database represents each AIRPORT, keeping its unique AirportCode, the AIRPORT
Name, and the City and State in which the AIRPORT is located.
(2) Each airline FLIGHT has a unique number, the Airline for the FLIGHT, and the Weekdays
on which the FLIGHT is scheduled (for example, every day of the week except Sunday can
be coded as X7).
(3) A FLIGHT is composed of one or more FLIGHT LEGs (for example, flight number
CO1223 from New York to Los Angeles may have two FLIGHT LEGs: leg 1 from New York
to Houston and leg 2 from Houston to Los Angeles). Each FLIGHT LEG has a DEPARTURE
AIRPORT and Scheduled Departure Time, and an ARRIVAL AIRPORT and Scheduled
Arrival Time.
(4) A LEG INSTANCE is an instance of a FLIGHT LEG on a specific Date (for example,
CO1223 leg 1 on July 30, 1989). The actual Departure and Arrival AIRPORTs and
Times are recorded for each flight leg after the flight leg has been concluded. The
Number of available seats and the AIRPLANE used in the LEG INSTANCE are also kept.
(5) The customer RESERVATIONs on each LEG INSTANCE include the Customer Name,
Phone, and Seat Number(s) for each reservation.
Loading page 10...
Chapter 3: Data Modeling Using the Entity-Relationship (ER) Model 5
(6) Information on AIRPLANEs and AIRPLANE TYPEs are also kept. For each AIRPLANE
TYPE (for example, DC-10), the TypeName, manufacturing Company, and
Maximum Number of Seats are kept. The AIRPORTs in which planes of this type
CAN LAND are kept in the database. For each AIRPLANE, the AirplaneId, Total number of
seats, and TYPE are kept.
3.20 - No solution provided.
3.21 -
Additional information:
- There are 435 congresspersons in the U.S. House of Representatives.
- States have between one (AK, DE, MT, ND, SD, VT, and WY) and 52 (CA) representatives.
- M represents number of bills during the 2-year session.
The resulting ER Diagram is shown in Figure A.
3.22 - A database is being constructed to keep track of the teams and games of a sports
league. A team has a number of players, not all of whom participate in each game.
It is desired to keep track of the players participating in each game for each team, the
positions they played in that game, and the result of the game. Try to design an ER schema
diagram for this application, stating any assumptions you make. Choose your favorite sport
(soccer, football, baseball ...).
Answer:
The following design may be used for a baseball league. Here, we assumed that each game
in the schedule is identified by a unique Game#, and a game is also identified uniquely by
the combination of Date, starting Time, and Field where it is played. The Performance
attribute of PARTICIPATE is used to store information on the individual performance of each
(6) Information on AIRPLANEs and AIRPLANE TYPEs are also kept. For each AIRPLANE
TYPE (for example, DC-10), the TypeName, manufacturing Company, and
Maximum Number of Seats are kept. The AIRPORTs in which planes of this type
CAN LAND are kept in the database. For each AIRPLANE, the AirplaneId, Total number of
seats, and TYPE are kept.
3.20 - No solution provided.
3.21 -
Additional information:
- There are 435 congresspersons in the U.S. House of Representatives.
- States have between one (AK, DE, MT, ND, SD, VT, and WY) and 52 (CA) representatives.
- M represents number of bills during the 2-year session.
The resulting ER Diagram is shown in Figure A.
3.22 - A database is being constructed to keep track of the teams and games of a sports
league. A team has a number of players, not all of whom participate in each game.
It is desired to keep track of the players participating in each game for each team, the
positions they played in that game, and the result of the game. Try to design an ER schema
diagram for this application, stating any assumptions you make. Choose your favorite sport
(soccer, football, baseball ...).
Answer:
The following design may be used for a baseball league. Here, we assumed that each game
in the schedule is identified by a unique Game#, and a game is also identified uniquely by
the combination of Date, starting Time, and Field where it is played. The Performance
attribute of PARTICIPATE is used to store information on the individual performance of each
Loading page 11...
Chapter 3: Data Modeling Using the Entity-Relationship (ER) Model6
player in a game. This attribute can be designed to keep the information needed for
statistics, and may be quite complex. One possible design for the Performance attribute may
be the following (using the notation of Figure 7.8):
Performance( {Hitting(AtBat#, Inning#, HitType, Runs, RunsBattedIn, StolenBases)},
{Pitching(Inning#, Hits, Runs, EarnedRuns, StrikeOuts, Walks, Outs,
Balks, WildPitches)},
{Defense(Inning#, {FieldingRecord(Position, PutOuts, Assists, Errors)})} )
Here, performance is a composite attribute made up of three multivalued components:
Hitting, Pitching, and Defense. Hitting has a value for each AtBat of a player, and records the
HitType (suitable coded; for example, 1 for single, 2 for double, 3 for triple, 4 for home run, 0
for walk, -1 for strikeout, -2 for fly out, ...) and other information concerning the AtBat.
Pitching has a value for each inning during which the player pitched. Defense has a value for
each inning a player played a fielding position. We can have a less detailed or a more
detailed design for the performance of a player in each game, depending on how much
information we need to keep in the database. Suitable variations of the ER diagram shown
below can be used for other sports.
3.23 - Consider the ER diagram shown in Figure 7.21 for part of a BANK database. Each
bank can have multiple branches, and each branch can have multiple accounts and loans.
(a) List the strong (nonweak) entity types in the ER diagram.
(b) Is there a weak entity type? If so, give its name, its partial key, and its identifying
relationship.
(c) What constraints do the partial key and the identifying relationship of the weak entity type
specify in this diagram?
player in a game. This attribute can be designed to keep the information needed for
statistics, and may be quite complex. One possible design for the Performance attribute may
be the following (using the notation of Figure 7.8):
Performance( {Hitting(AtBat#, Inning#, HitType, Runs, RunsBattedIn, StolenBases)},
{Pitching(Inning#, Hits, Runs, EarnedRuns, StrikeOuts, Walks, Outs,
Balks, WildPitches)},
{Defense(Inning#, {FieldingRecord(Position, PutOuts, Assists, Errors)})} )
Here, performance is a composite attribute made up of three multivalued components:
Hitting, Pitching, and Defense. Hitting has a value for each AtBat of a player, and records the
HitType (suitable coded; for example, 1 for single, 2 for double, 3 for triple, 4 for home run, 0
for walk, -1 for strikeout, -2 for fly out, ...) and other information concerning the AtBat.
Pitching has a value for each inning during which the player pitched. Defense has a value for
each inning a player played a fielding position. We can have a less detailed or a more
detailed design for the performance of a player in each game, depending on how much
information we need to keep in the database. Suitable variations of the ER diagram shown
below can be used for other sports.
3.23 - Consider the ER diagram shown in Figure 7.21 for part of a BANK database. Each
bank can have multiple branches, and each branch can have multiple accounts and loans.
(a) List the strong (nonweak) entity types in the ER diagram.
(b) Is there a weak entity type? If so, give its name, its partial key, and its identifying
relationship.
(c) What constraints do the partial key and the identifying relationship of the weak entity type
specify in this diagram?
Loading page 12...
Chapter 3: Data Modeling Using the Entity-Relationship (ER) Model 7
(d) List the names of all relationship types, and specify the (min,max) constraint on each
participation of an entity type in a relationship type. Justify your choices.
(e) List concisely the user requirements that led to this ER schema design.
(f) Suppose that every customer must have at least one account but is restricted
to at most two loans at a time, and that a bank branch cannot have more than
1000 loans. How does this show up on the (min,max) constraints?
Answer:
(a) Entity types: BANK, ACCOUNT, CUSTOMER, LOAN
(b) Weak entity type: BANK-BRANCH. Partial key: BranchNo.
Identifying relationship: BRANCHES.
(c) The partial key BranchNo in BANK-BRANCH specifies that the same BranchNo value ay
occur under different BANKs. The identifying relationship BRANCHES specifies that
(d) List the names of all relationship types, and specify the (min,max) constraint on each
participation of an entity type in a relationship type. Justify your choices.
(e) List concisely the user requirements that led to this ER schema design.
(f) Suppose that every customer must have at least one account but is restricted
to at most two loans at a time, and that a bank branch cannot have more than
1000 loans. How does this show up on the (min,max) constraints?
Answer:
(a) Entity types: BANK, ACCOUNT, CUSTOMER, LOAN
(b) Weak entity type: BANK-BRANCH. Partial key: BranchNo.
Identifying relationship: BRANCHES.
(c) The partial key BranchNo in BANK-BRANCH specifies that the same BranchNo value ay
occur under different BANKs. The identifying relationship BRANCHES specifies that
Loading page 13...
Chapter 3: Data Modeling Using the Entity-Relationship (ER) Model8
BranchNo values are uniquely assigned for those BANK-BRANCH entities that are related to
the same BANK entity. Hence, the combination of BANK Code and BranchNo together
constitute a full identifier for a BANK-BRANCH.
(d) Relationship Types: BRANCHES, ACCTS, LOANS, A-C, L-C. The (min, max) constraints
are shown below.
(e) The requirements may be stated as follows: Each BANK has a unique Code, as well as a
Name and Address. Each BANK is related to one or more BANK-BRANCHes, and the
BranhNo is unique among each set of BANK-BRANCHes that are related to the same BANK.
Each BANK-BRANCH has an Address. Each BANK-BRANCH has zero or more LOANS and
zero or more ACCTS. Each ACCOUNT has an AcctNo (unique), Balance, and Type and is
related to exactly one BANK-BRANCH and to at least one CUSTOMER. Each LOAN has a
LoanNo (unique), Amount, and Type and is related to exactly one BANK-BRANCH and to at
least one CUSTOMER. Each CUSTOMER has an SSN (unique), Name, Phone, and
Address, and is related to zero or more ACCOUNTs and to zero or more LOANs.
(f) The (min, max) constraints would be changed as follows:
3.24 - Consider the ER diagram in Figure 7.22. Assume that an employee may work in up to
two departments or may not be assigned to any department. Assume that each department
must have one and may have up to three phone numbers. Supply (min, max) constraints on
this diagram. State clearly any additional assumptions you make. Under what conditions
would the relationship HAS_PHONE be redundant in this example?
Answer:
Assuming the following additional assumptions:
- Each department can have anywhere between 1 and 10 employees.
- Each phone is used by one, and only one, department.
- Each phone is assigned to at least one, and may be assigned to up to 10 employees.
- Each employee is assigned at least one, but no more than 6 phones.
The resulting ER Diagram will have the (min, max) constraints shown in Figure A.
Relationship HAS-PHONE would be redundant under the following conditions:
- Each employee is assigned all of the phones of each department that he/she works in.
BranchNo values are uniquely assigned for those BANK-BRANCH entities that are related to
the same BANK entity. Hence, the combination of BANK Code and BranchNo together
constitute a full identifier for a BANK-BRANCH.
(d) Relationship Types: BRANCHES, ACCTS, LOANS, A-C, L-C. The (min, max) constraints
are shown below.
(e) The requirements may be stated as follows: Each BANK has a unique Code, as well as a
Name and Address. Each BANK is related to one or more BANK-BRANCHes, and the
BranhNo is unique among each set of BANK-BRANCHes that are related to the same BANK.
Each BANK-BRANCH has an Address. Each BANK-BRANCH has zero or more LOANS and
zero or more ACCTS. Each ACCOUNT has an AcctNo (unique), Balance, and Type and is
related to exactly one BANK-BRANCH and to at least one CUSTOMER. Each LOAN has a
LoanNo (unique), Amount, and Type and is related to exactly one BANK-BRANCH and to at
least one CUSTOMER. Each CUSTOMER has an SSN (unique), Name, Phone, and
Address, and is related to zero or more ACCOUNTs and to zero or more LOANs.
(f) The (min, max) constraints would be changed as follows:
3.24 - Consider the ER diagram in Figure 7.22. Assume that an employee may work in up to
two departments or may not be assigned to any department. Assume that each department
must have one and may have up to three phone numbers. Supply (min, max) constraints on
this diagram. State clearly any additional assumptions you make. Under what conditions
would the relationship HAS_PHONE be redundant in this example?
Answer:
Assuming the following additional assumptions:
- Each department can have anywhere between 1 and 10 employees.
- Each phone is used by one, and only one, department.
- Each phone is assigned to at least one, and may be assigned to up to 10 employees.
- Each employee is assigned at least one, but no more than 6 phones.
The resulting ER Diagram will have the (min, max) constraints shown in Figure A.
Relationship HAS-PHONE would be redundant under the following conditions:
- Each employee is assigned all of the phones of each department that he/she works in.
Loading page 14...
Chapter 3: Data Modeling Using the Entity-Relationship (ER) Model 9
- An employee cannot have any other phones outside the departments he/she works is.
EMPLOYEE
PHONE
DEPARTMENT
HAS-PHONE CONTAINS
WORKS-IN
(0, 2)
(1, 3)
(1, 10)
(1, 10) (1, 1)
(1, 6)
3.25 - Consider the ER diagram in Figure 7.23. Assume that a course may or may not use a
textbook, but that a text by definition is a book that is used in some course. A course may not
use more than five books. Instructors teach from two to four courses. Supply (min, max)
constraints on this diagram. State clearly any additional assumptions you make. If we add
the relationship ADOPTS between INSTRUCTOR and TEXT, what (min, max) constraints
would you put on it? Why?
Answer:
Assuming the following additional assumptions:
- Each course is taught by exactly one instructor.
- Each textbook is used by one and only one course.
- An instructor does not have to adopt a textbook for all courses.
- If a text exists:
- ___it is used in some course,
- ___hence it is adopted by some instructor who teaches that course.
- An instructor is considered to adopt a text if it is used in some course taught
- by that instructor.
The resulting ER Diagram will have the (min, max) constraints shown in Figure B.
3.26 - Consider an entity type SECTION in a UNIVERSITY database, which describes the
section offerings of courses. The attributes of SECTION are SectionNumber, Semester,
Year, CourseNumber, Instructor, RoomNo (where section is taught), Building (where section
is taught), Weekdays (domain is the possible combinations of weekdays in which a section
can be offered {MWF, MW, TT, etc.}). Assume tat SectionNumber is unique for each course
- An employee cannot have any other phones outside the departments he/she works is.
EMPLOYEE
PHONE
DEPARTMENT
HAS-PHONE CONTAINS
WORKS-IN
(0, 2)
(1, 3)
(1, 10)
(1, 10) (1, 1)
(1, 6)
3.25 - Consider the ER diagram in Figure 7.23. Assume that a course may or may not use a
textbook, but that a text by definition is a book that is used in some course. A course may not
use more than five books. Instructors teach from two to four courses. Supply (min, max)
constraints on this diagram. State clearly any additional assumptions you make. If we add
the relationship ADOPTS between INSTRUCTOR and TEXT, what (min, max) constraints
would you put on it? Why?
Answer:
Assuming the following additional assumptions:
- Each course is taught by exactly one instructor.
- Each textbook is used by one and only one course.
- An instructor does not have to adopt a textbook for all courses.
- If a text exists:
- ___it is used in some course,
- ___hence it is adopted by some instructor who teaches that course.
- An instructor is considered to adopt a text if it is used in some course taught
- by that instructor.
The resulting ER Diagram will have the (min, max) constraints shown in Figure B.
3.26 - Consider an entity type SECTION in a UNIVERSITY database, which describes the
section offerings of courses. The attributes of SECTION are SectionNumber, Semester,
Year, CourseNumber, Instructor, RoomNo (where section is taught), Building (where section
is taught), Weekdays (domain is the possible combinations of weekdays in which a section
can be offered {MWF, MW, TT, etc.}). Assume tat SectionNumber is unique for each course
Loading page 15...
Chapter 3: Data Modeling Using the Entity-Relationship (ER) Model10
within a particular semester/year combination (that is, if a course if offered multiple times
during a particular semester, its section offerings are numbered 1, 2, 3, etc.). There are
several composite keys for SECTION, and some attribute sare components of more than one
key. Identify three composite keys, and show how they can be represented in an ER schema
diagram.
Answer:
3.27 - Cardinality ratios often dictate the detailed design of a database. The cardinality ratio
depends on the real-world meaning of the entity types involved and is defined by the specific
application. For the binary relationships below, suggest cardinality ratios based on common-
sense meaning of the entity types. Clearly state any assumptions you make.
Entity 1 Cardinality Ratio Entity 2
1. Student SocialSecurityCard
2. Student Teacher
3. ClassRoom Wall
4. Country CurrentPresident
5. Course TextBook
6. Item (that can be
found in an order) Order
within a particular semester/year combination (that is, if a course if offered multiple times
during a particular semester, its section offerings are numbered 1, 2, 3, etc.). There are
several composite keys for SECTION, and some attribute sare components of more than one
key. Identify three composite keys, and show how they can be represented in an ER schema
diagram.
Answer:
3.27 - Cardinality ratios often dictate the detailed design of a database. The cardinality ratio
depends on the real-world meaning of the entity types involved and is defined by the specific
application. For the binary relationships below, suggest cardinality ratios based on common-
sense meaning of the entity types. Clearly state any assumptions you make.
Entity 1 Cardinality Ratio Entity 2
1. Student SocialSecurityCard
2. Student Teacher
3. ClassRoom Wall
4. Country CurrentPresident
5. Course TextBook
6. Item (that can be
found in an order) Order
Loading page 16...
Chapter 3: Data Modeling Using the Entity-Relationship (ER) Model 11
7. Student Class
8. Class Instructor
9. Instructor Office
10. E-bay Auction
item E-bay bid
Answer:
Entity 1 Cardinality Ratio Entity 2
1. Student
1-many
A student may have more
than one social security
card (legally with the
same unique social
security number), and
every social security
number belongs to a
unique student.
SocialSecurityCard
2. Student
Many-many
Generally students are
taught by many teachers
and a teacher teaches
many students.
Teacher
3. ClassRoom
Many-many
Don’t forget that the wall
is usually shared by
adjacent rooms.
Wall
4. Country
1-1
Assuming a normal
country under normal
circumstances having one
president at a time.
CurrentPresident
5. Course
Many-many
A course may have many
textbooks and a text book
may be prescribed for
different courses.
TextBook
7. Student Class
8. Class Instructor
9. Instructor Office
10. E-bay Auction
item E-bay bid
Answer:
Entity 1 Cardinality Ratio Entity 2
1. Student
1-many
A student may have more
than one social security
card (legally with the
same unique social
security number), and
every social security
number belongs to a
unique student.
SocialSecurityCard
2. Student
Many-many
Generally students are
taught by many teachers
and a teacher teaches
many students.
Teacher
3. ClassRoom
Many-many
Don’t forget that the wall
is usually shared by
adjacent rooms.
Wall
4. Country
1-1
Assuming a normal
country under normal
circumstances having one
president at a time.
CurrentPresident
5. Course
Many-many
A course may have many
textbooks and a text book
may be prescribed for
different courses.
TextBook
Loading page 17...
Chapter 3: Data Modeling Using the Entity-Relationship (ER) Model12
6. Item (that can be
found in an order)
Many-many
Assuming the same item
can appear in different
orders.
Order
7. Student
Many-many
One student may take
several classes. Every
class usually has several
students.
Class
8. Class
Many-to-1
Assuming that every class
has a unique instructor. In
case instructors were
allowed to team teach,
this will be many-many.
Instructor
9. Instructor
1-1
Assuming every instructor
has only one office and it
is not shared. In case of
offices shared by 2
instructors, the
relationship will be 2-1.
Conversely, if an
instructor has a joint
appointment (in two
departments) and offices
in both departments, then
the relationship will be 1-
2. In a very general case,
it may be many-many.
Office
10. E-bay Auction
item
1-many
1 item has many bids and
a bid is unique to an item
(assuming a regular
auction format).
E-bay bid
3.28-3.29 – No solutions provided.
3.30 - Illustrate the UML Diagram for exercise 7.16. Your UML design should observe the
following requirements:
a. The student should have the ability to compute his/her GPA and add or drop majors
and minors.
6. Item (that can be
found in an order)
Many-many
Assuming the same item
can appear in different
orders.
Order
7. Student
Many-many
One student may take
several classes. Every
class usually has several
students.
Class
8. Class
Many-to-1
Assuming that every class
has a unique instructor. In
case instructors were
allowed to team teach,
this will be many-many.
Instructor
9. Instructor
1-1
Assuming every instructor
has only one office and it
is not shared. In case of
offices shared by 2
instructors, the
relationship will be 2-1.
Conversely, if an
instructor has a joint
appointment (in two
departments) and offices
in both departments, then
the relationship will be 1-
2. In a very general case,
it may be many-many.
Office
10. E-bay Auction
item
1-many
1 item has many bids and
a bid is unique to an item
(assuming a regular
auction format).
E-bay bid
3.28-3.29 – No solutions provided.
3.30 - Illustrate the UML Diagram for exercise 7.16. Your UML design should observe the
following requirements:
a. The student should have the ability to compute his/her GPA and add or drop majors
and minors.
Loading page 18...
Chapter 3: Data Modeling Using the Entity-Relationship (ER) Model 13
b. Each department should be to able add or delete courses and hire or terminate
faculty.
c. Each instructor should be able to assign or change a grade to a student for a course.
Note: Some of these functions may be spread over multiple classes.
Answer:
COURSE
CourseName
Description
CourseNumber: {Course}
SemesterHours
Level
Department
SECTION
Instructor
Semester
Year
CourseNumber
SectionNumber: {1,2,…}
GRADE REPORT
StudentNumber
SectionNumber
LetterGrade: {A,B,C,D,F}
NumberGrade: {0,1,2,3,4}
PREREQUISITE
CourseNumber
PrerequisiteNumber
STUDENT
Name:
<Name>
StudentNumber
SocialSecNumber
CurrentAddress:
<Address>
CurrentPhone:
<Phone>
PermanentAddress:
<Address>
PermanentPhone:
<Phone>
Birthdate: Date
Sex: {M,F}
Class: {F,So,J,Se,G}
MajorDepartment
MinorDepartment
Degree: {BA,BS,…}
compute_gpa
add_major
drop_major
add_minor
drop_minor
DEPARTMENT
Name: {CS,…}
DepartmentCode: {Codes}
OfficeNumber
OfficePhone:
<Phone>
College: {Colleges}
add_course
delete_course
hire_faculty
fire_faculty
INSTRUCTOR
Name:
<Name>
Phone:
<Phone>
DepartmentCode
assign_grade
change_grade
b. Each department should be to able add or delete courses and hire or terminate
faculty.
c. Each instructor should be able to assign or change a grade to a student for a course.
Note: Some of these functions may be spread over multiple classes.
Answer:
COURSE
CourseName
Description
CourseNumber: {Course}
SemesterHours
Level
Department
SECTION
Instructor
Semester
Year
CourseNumber
SectionNumber: {1,2,…}
GRADE REPORT
StudentNumber
SectionNumber
LetterGrade: {A,B,C,D,F}
NumberGrade: {0,1,2,3,4}
PREREQUISITE
CourseNumber
PrerequisiteNumber
STUDENT
Name:
<Name>
StudentNumber
SocialSecNumber
CurrentAddress:
<Address>
CurrentPhone:
<Phone>
PermanentAddress:
<Address>
PermanentPhone:
<Phone>
Birthdate: Date
Sex: {M,F}
Class: {F,So,J,Se,G}
MajorDepartment
MinorDepartment
Degree: {BA,BS,…}
compute_gpa
add_major
drop_major
add_minor
drop_minor
DEPARTMENT
Name: {CS,…}
DepartmentCode: {Codes}
OfficeNumber
OfficePhone:
<Phone>
College: {Colleges}
add_course
delete_course
hire_faculty
fire_faculty
INSTRUCTOR
Name:
<Name>
Phone:
<Phone>
DepartmentCode
assign_grade
change_grade
Loading page 19...
Chapter 3: Data Modeling Using the Entity-Relationship (ER) Model14
This one isn’t finished yet. The relationships need to be added and some of the functions
should be moved to span classes.
This one isn’t finished yet. The relationships need to be added and some of the functions
should be moved to span classes.
Loading page 20...
Chapter 4: The Enhanced Entity-Relationship (EER) Model 1
CHAPTER 4: THE ENHANCED ENTITY-RELATIONSHIP (EER) MODEL
Answers to Selected Exercises
4.16 – No solution provided.
4.17 - Consider the BANK ER schema of Figure 7.21, and suppose that it is necessary to
keep track of different types of ACCOUNTS (SAVINGS_ACCTS, CHECKING_ACCTS,
...) and LOANS (CAR_LOANS, HOME_LOANS, ...). Suppose that it is also desirable to keep
track of each account's TRANSACTIONs (deposits, withdrawals, checks, ...) and each loan's
PAYMENTs; both of these include the amount, date, time, ... Modify the BANK schema,
using ER and EER concepts of specialization and generalization. State any assumptions you
make about the additional requirements.
Answer:
4.18 - No solution provided.
4.19 - Identify all the important concepts represented in the library database case study
described here. In particular, identify the abstraction of classification (entity types and
relationship types), aggregation, identification, and specialization/generalization. Specify
(min, max) cardinality constraints whenever possible. List details that will affect the eventual
design but which have no bearing on the conceptual design. List the semantic separately.
Draw an EER diagram of the library database.
Answer:
CHAPTER 4: THE ENHANCED ENTITY-RELATIONSHIP (EER) MODEL
Answers to Selected Exercises
4.16 – No solution provided.
4.17 - Consider the BANK ER schema of Figure 7.21, and suppose that it is necessary to
keep track of different types of ACCOUNTS (SAVINGS_ACCTS, CHECKING_ACCTS,
...) and LOANS (CAR_LOANS, HOME_LOANS, ...). Suppose that it is also desirable to keep
track of each account's TRANSACTIONs (deposits, withdrawals, checks, ...) and each loan's
PAYMENTs; both of these include the amount, date, time, ... Modify the BANK schema,
using ER and EER concepts of specialization and generalization. State any assumptions you
make about the additional requirements.
Answer:
4.18 - No solution provided.
4.19 - Identify all the important concepts represented in the library database case study
described here. In particular, identify the abstraction of classification (entity types and
relationship types), aggregation, identification, and specialization/generalization. Specify
(min, max) cardinality constraints whenever possible. List details that will affect the eventual
design but which have no bearing on the conceptual design. List the semantic separately.
Draw an EER diagram of the library database.
Answer:
Loading page 21...
Chapter 4: The Enhanced Entity-Relationship (EER) Model2
4.20 - No solution provided.
4.21 - Figure 8.12 shows an example of an EER diagram for a small private airport database
that is used to keep track of airplanes, their owners, airport employees, and pilots. From the
requirements for this database, the following information was collected: Each airplane has a
registration number [Reg#], is of a particular plane type [of_type], and is stored in a particular
hangar [stored_in]. Each plane_type has a model number [Model], a capacity [Capacity], and
a weight [Weight]. Each hangar has a number [Number], a capacity [Capacity], and a
location [Location]. The database also keeps track of the owners of each plane [owns] and
the employees who have maintained the plane [maintain]. Each relationship instance in owns
relates an airplane to an owner and includes the purchase date [Pdate]. Each relationship
instance in maintain relates to an employee to a service record [service]. Each plane
undergoes service many times; hence, it is related by [plane_service] o a number of service
records. A service record includes as attributes the date of maintenance [Date], the number
of hours spent on the work [Hours], and the type of work done [Workcode]. We use a weak
entity type [service] to represent airplane service, because the airplane registration number
is used to identify a service record. An owner is either a person or a corporation. Hence, we
use a union type (category) [owner] that is a subset of the union of corporation [Corporation]
and person [Person] entity types. Both pilots {Pilot] and employees [Employee] are
subclasses of person. Each pilot has specific attributes license number [Lic_Num] and
restrictions [Restr]; each employee has specific attributes salary [Salary] and shift {Shift]. All
person entities in the database have data kept on their social security number [Ssn], name
4.20 - No solution provided.
4.21 - Figure 8.12 shows an example of an EER diagram for a small private airport database
that is used to keep track of airplanes, their owners, airport employees, and pilots. From the
requirements for this database, the following information was collected: Each airplane has a
registration number [Reg#], is of a particular plane type [of_type], and is stored in a particular
hangar [stored_in]. Each plane_type has a model number [Model], a capacity [Capacity], and
a weight [Weight]. Each hangar has a number [Number], a capacity [Capacity], and a
location [Location]. The database also keeps track of the owners of each plane [owns] and
the employees who have maintained the plane [maintain]. Each relationship instance in owns
relates an airplane to an owner and includes the purchase date [Pdate]. Each relationship
instance in maintain relates to an employee to a service record [service]. Each plane
undergoes service many times; hence, it is related by [plane_service] o a number of service
records. A service record includes as attributes the date of maintenance [Date], the number
of hours spent on the work [Hours], and the type of work done [Workcode]. We use a weak
entity type [service] to represent airplane service, because the airplane registration number
is used to identify a service record. An owner is either a person or a corporation. Hence, we
use a union type (category) [owner] that is a subset of the union of corporation [Corporation]
and person [Person] entity types. Both pilots {Pilot] and employees [Employee] are
subclasses of person. Each pilot has specific attributes license number [Lic_Num] and
restrictions [Restr]; each employee has specific attributes salary [Salary] and shift {Shift]. All
person entities in the database have data kept on their social security number [Ssn], name
Loading page 22...
Chapter 4: The Enhanced Entity-Relationship (EER) Model 3
[Name], address [Address], and telephone number [Phone]. For corporation entities, the data
kept includes name [Name], address [Address], and telephone number [Phone]. The
database also keeps track of the types of planes each pilot is authorized to fly [Flies] and the
types of planes each employee can do maintenance work on [Works_on]. Show how the
small airport EER schema of Figure 8.12 may be represented in UML notation. (Note: We
have not discussed how to represent categories (union types) in UML, so you do not have to
map the categories in this and the following question.)
Answer:
4.22 – 4.23 – No solutions provided.
4.24 - Draw a UML diagram for storing a played game of chess in a database. You may look
at http://www.chessgames.com for an application similar to what you are designing. State
clearly any assumptions you make in your UML diagram. A sample of assumptions you can
[Name], address [Address], and telephone number [Phone]. For corporation entities, the data
kept includes name [Name], address [Address], and telephone number [Phone]. The
database also keeps track of the types of planes each pilot is authorized to fly [Flies] and the
types of planes each employee can do maintenance work on [Works_on]. Show how the
small airport EER schema of Figure 8.12 may be represented in UML notation. (Note: We
have not discussed how to represent categories (union types) in UML, so you do not have to
map the categories in this and the following question.)
Answer:
4.22 – 4.23 – No solutions provided.
4.24 - Draw a UML diagram for storing a played game of chess in a database. You may look
at http://www.chessgames.com for an application similar to what you are designing. State
clearly any assumptions you make in your UML diagram. A sample of assumptions you can
Loading page 23...
Chapter 4: The Enhanced Entity-Relationship (EER) Model4
make about the scope is a follows:
1. The game of chess is played between two players.
2. The game is played on an 8x8 board like the one shown below:
3. The players are assigned a color of black or white at the start of the game.
4. Each player starts with the following pieces (traditionally called chessmen):
a. 1 king
b. 1 queen
c. 2 rooks
d. 2 bishops
e. 2 knights
f. 8 pawns
5. Every piece has its own initial position.
6. Every piece has its own set of legal moves based on the state of the game. You do not
need to worry about which moves are legal and which are not other than the following
issues:
a. A piece may move to an empty square or capture an opposing piece.
b. If a piece is captured, it is removed from the board.
c. If a pawn moves to the last row, it is “promoted” but converting it into another
piece (a queen, a rook, a bishop, or a knight).
Note: Some of these functions may be spread over multiple classes.
4.25 – No solution provided.
4.26 - Which of the following EER diagram(s) is/are incorrect and why? State clearly any
assumptions you make.
a) 1 N
R
A
E
E3
B
o
E1
E2
make about the scope is a follows:
1. The game of chess is played between two players.
2. The game is played on an 8x8 board like the one shown below:
3. The players are assigned a color of black or white at the start of the game.
4. Each player starts with the following pieces (traditionally called chessmen):
a. 1 king
b. 1 queen
c. 2 rooks
d. 2 bishops
e. 2 knights
f. 8 pawns
5. Every piece has its own initial position.
6. Every piece has its own set of legal moves based on the state of the game. You do not
need to worry about which moves are legal and which are not other than the following
issues:
a. A piece may move to an empty square or capture an opposing piece.
b. If a piece is captured, it is removed from the board.
c. If a pawn moves to the last row, it is “promoted” but converting it into another
piece (a queen, a rook, a bishop, or a knight).
Note: Some of these functions may be spread over multiple classes.
4.25 – No solution provided.
4.26 - Which of the following EER diagram(s) is/are incorrect and why? State clearly any
assumptions you make.
a) 1 N
R
A
E
E3
B
o
E1
E2
Loading page 24...
Chapter 4: The Enhanced Entity-Relationship (EER) Model 5
Answer:
Only (c) is incorrect.
4.27 - Consider the following EER diagram that describes computer systems at a company.
Provide your own attributes and key for each entity type. Supply max cardinality constraints
justifying your choice. Write a complete narrative description of what this EER diagram
represents.
NM
1
1
b)
A
E
c)
R
A
E1
E2
C
E3
d
E1
o
R
Computer
Laptop Desktop
Accessory
Keyboard
Monitor
Mouse
Software
Operating
System
Component
Memory
Sound card
Video card
d
d d
U U U
U U
U
U U
U
SoldWith
Options Options
Supports
Installed
InstalledOS
Answer:
Only (c) is incorrect.
4.27 - Consider the following EER diagram that describes computer systems at a company.
Provide your own attributes and key for each entity type. Supply max cardinality constraints
justifying your choice. Write a complete narrative description of what this EER diagram
represents.
NM
1
1
b)
A
E
c)
R
A
E1
E2
C
E3
d
E1
o
R
Computer
Laptop Desktop
Accessory
Keyboard
Monitor
Mouse
Software
Operating
System
Component
Memory
Sound card
Video card
d
d d
U U U
U U
U
U U
U
SoldWith
Options Options
Supports
Installed
InstalledOS
Loading page 25...
Chapter 5: The Relational Data Model and Relational Database Constraints 1
CHAPTER 5: THE RELATIONAL DATA MODEL AND RELATIONAL DATABASE
CONSTRAINTS
Answers to Selected Exercises
5.11 - Suppose each of the following Update operations is applied directly to the database of
Figure 3.6. Discuss all integrity constraints violated by each operation, if any, and the
different ways of enforcing these constraints:
(a) Insert < 'Robert', 'F', 'Scott', '943775543', '21-JUN-42', '2365 Newcastle Rd,
Bellaire, TX', M, 58000, '888665555', 1 > into EMPLOYEE.
(b) Insert < 'ProductA', 4, 'Bellaire', 2 > into PROJECT.
(c) Insert < 'Production', 4, '943775543', '01-OCT-88' > into DEPARTMENT.
(d) Insert < '677678989', null, '40.0' > into WORKS_ON.
(e) Insert < '453453453', 'John', M, '12-DEC-60', 'SPOUSE' > into DEPENDENT.
(f) Delete the WORKS_ON tuples with ESSN= '333445555'.
(g) Delete the EMPLOYEE tuple with SSN= '987654321'.
(h) Delete the PROJECT tuple with PNAME= 'ProductX'.
(i) Modify the MGRSSN and MGRSTARTDATE of the DEPARTMENT tuple with
DNUMBER=5 to '123456789' and '01-OCT-88', respectively.
(j) Modify the SUPERSSN attribute of the EMPLOYEE tuple with SSN= '999887777' to
'943775543'.
(k) Modify the HOURS attribute of the WORKS_ON tuple with ESSN= '999887777' and
PNO= 10 to '5.0'.
Answers:
(a) No constraint violations.
(b) Violates referential integrity because DNUM=2 and there is no tuple in the
DEPARTMENT relation with DNUMBER=2. We may enforce the constraint by: (i) rejecting
the insertion of the new PROJECT tuple, (ii) changing the value of DNUM in the new
PROJECT tuple to an existing DNUMBER value in the DEPARTMENT relation, or (iii)
inserting a new DEPARTMENT tuple with DNUMBER=2.
(c) Violates both the key constraint and referential integrity. Violates the key constraint
because there already exists a DEPARTMENT tuple with DNUMBER=4. We may enforce
this constraint by: (i) rejecting the insertion, or (ii) changing the value of DNUMBER in the
new DEPARTMENT tuple to a value that does not violate the key constraint. Violates
referential integrity because MGRSSN='943775543' and there is no tuple in the EMPLOYEE
relation with SSN='943775543'. We may enforce the constraint by: (i) rejecting the insertion,
(ii) changing the value of MGRSSN to an existing SSN value in EMPLOYEE, or (iii) inserting
a new EMPLOYEE tuple with SSN='943775543'.
CHAPTER 5: THE RELATIONAL DATA MODEL AND RELATIONAL DATABASE
CONSTRAINTS
Answers to Selected Exercises
5.11 - Suppose each of the following Update operations is applied directly to the database of
Figure 3.6. Discuss all integrity constraints violated by each operation, if any, and the
different ways of enforcing these constraints:
(a) Insert < 'Robert', 'F', 'Scott', '943775543', '21-JUN-42', '2365 Newcastle Rd,
Bellaire, TX', M, 58000, '888665555', 1 > into EMPLOYEE.
(b) Insert < 'ProductA', 4, 'Bellaire', 2 > into PROJECT.
(c) Insert < 'Production', 4, '943775543', '01-OCT-88' > into DEPARTMENT.
(d) Insert < '677678989', null, '40.0' > into WORKS_ON.
(e) Insert < '453453453', 'John', M, '12-DEC-60', 'SPOUSE' > into DEPENDENT.
(f) Delete the WORKS_ON tuples with ESSN= '333445555'.
(g) Delete the EMPLOYEE tuple with SSN= '987654321'.
(h) Delete the PROJECT tuple with PNAME= 'ProductX'.
(i) Modify the MGRSSN and MGRSTARTDATE of the DEPARTMENT tuple with
DNUMBER=5 to '123456789' and '01-OCT-88', respectively.
(j) Modify the SUPERSSN attribute of the EMPLOYEE tuple with SSN= '999887777' to
'943775543'.
(k) Modify the HOURS attribute of the WORKS_ON tuple with ESSN= '999887777' and
PNO= 10 to '5.0'.
Answers:
(a) No constraint violations.
(b) Violates referential integrity because DNUM=2 and there is no tuple in the
DEPARTMENT relation with DNUMBER=2. We may enforce the constraint by: (i) rejecting
the insertion of the new PROJECT tuple, (ii) changing the value of DNUM in the new
PROJECT tuple to an existing DNUMBER value in the DEPARTMENT relation, or (iii)
inserting a new DEPARTMENT tuple with DNUMBER=2.
(c) Violates both the key constraint and referential integrity. Violates the key constraint
because there already exists a DEPARTMENT tuple with DNUMBER=4. We may enforce
this constraint by: (i) rejecting the insertion, or (ii) changing the value of DNUMBER in the
new DEPARTMENT tuple to a value that does not violate the key constraint. Violates
referential integrity because MGRSSN='943775543' and there is no tuple in the EMPLOYEE
relation with SSN='943775543'. We may enforce the constraint by: (i) rejecting the insertion,
(ii) changing the value of MGRSSN to an existing SSN value in EMPLOYEE, or (iii) inserting
a new EMPLOYEE tuple with SSN='943775543'.
Loading page 26...
Chapter 5: The Relational Data Model and Relational Database Constraints2
(d) Violates both the entity integrity and referential integrity. Violates entity integrity because
PNO, which is part of the primary key of WORKS_ON, is null. We may enforce this constraint
by: (i) rejecting the insertion, or (ii) changing the value of PNO in the new WORKS_ON tuple
to a value of PNUMBER that exists in the PROJECT relation. Violates referential integrity
because ESSN='677678989' and there is no tuple in the EMPLOYEE relation with
SSN='677678989'. We may enforce the constraint by: (i) rejecting the insertion, (ii) changing
the value of ESSN to an existing SSN value in EMPLOYEE, or (iii) inserting a new
EMPLOYEE tuple with SSN='677678989'.
(e) No constraint violations.
(f) No constraint violations.
(g) Violates referential integrity because several tuples exist in the WORKS_ON,
DEPENDENT, DEPARTMENT, and EMPLOYEE relations that reference the tuple being
deleted from EMPLOYEE. We may enforce the constraint by: (i) rejecting the deletion, or (ii)
deleting all tuples in the WORKS_ON, DEPENDENT, DEPARTMENT, and EMPLOYEE
relations whose values for ESSN, ESSN, MGRSSN, and SUPERSSN, respectively, is equal
to'987654321'.
(h) Violates referential integrity because two tuples exist in the WORKS_ON relations that
reference the tuple being deleted from PROJECT. We may enforce the constraint by: (i)
rejecting the deletion, or (ii) deleting the tuples in the WORKS_ON relation whose value for
PNO=1 (the value for the primary key PNUMBER for the tuple being deleted from
PROJECT).
(i) No constraint violations.
(j) Violates referential integrity because the new value of SUPERSSN='943775543' and there
is no tuple in the EMPLOYEE relation with SSN='943775543'. We may enforce the constraint
by: (i) rejecting the deletion, or (ii) inserting a new EMPLOYEE tuple with SSN='943775543'.
(k) No constraint violations.
5.12 - Consider the AIRLINE relational database schema shown in Figure 3.8, which
describes a database for airline flight information. Each FLIGHT is identified by a flight
NUMBER, and consists of one or more FLIGHT_LEGs with LEG_NUMBERs 1, 2, 3, etc.
Each leg has scheduled arrival and departure times and airports, and has many
LEG_INSTANCEs--one for each DATE on which the flight travels. FARES are kept for each
flight. For each leg instance, SEAT_RESERVATIONs are kept, as is the AIRPLANE used in
the leg, and the actual arrival and departure times and airports. An AIRPLANE is identified
by an AIRPLANE_ID, and is of a particular AIRPLANE_TYPE. CAN_LAND relates
AIRPLANE_TYPEs to the AIRPORTs in which they can land. An AIRPORT is identified by
an AIRPORT_CODE. Consider an update for the AIRLINE database to enter a reservation
on a particular flight or flight leg on a given date.
(a) Give the operations for this update.
(b) What types of constraints would you expect to check?
(c) Which of these constraints are key, entity integrity, and referential integrity constraints
and which are not?
(d) Violates both the entity integrity and referential integrity. Violates entity integrity because
PNO, which is part of the primary key of WORKS_ON, is null. We may enforce this constraint
by: (i) rejecting the insertion, or (ii) changing the value of PNO in the new WORKS_ON tuple
to a value of PNUMBER that exists in the PROJECT relation. Violates referential integrity
because ESSN='677678989' and there is no tuple in the EMPLOYEE relation with
SSN='677678989'. We may enforce the constraint by: (i) rejecting the insertion, (ii) changing
the value of ESSN to an existing SSN value in EMPLOYEE, or (iii) inserting a new
EMPLOYEE tuple with SSN='677678989'.
(e) No constraint violations.
(f) No constraint violations.
(g) Violates referential integrity because several tuples exist in the WORKS_ON,
DEPENDENT, DEPARTMENT, and EMPLOYEE relations that reference the tuple being
deleted from EMPLOYEE. We may enforce the constraint by: (i) rejecting the deletion, or (ii)
deleting all tuples in the WORKS_ON, DEPENDENT, DEPARTMENT, and EMPLOYEE
relations whose values for ESSN, ESSN, MGRSSN, and SUPERSSN, respectively, is equal
to'987654321'.
(h) Violates referential integrity because two tuples exist in the WORKS_ON relations that
reference the tuple being deleted from PROJECT. We may enforce the constraint by: (i)
rejecting the deletion, or (ii) deleting the tuples in the WORKS_ON relation whose value for
PNO=1 (the value for the primary key PNUMBER for the tuple being deleted from
PROJECT).
(i) No constraint violations.
(j) Violates referential integrity because the new value of SUPERSSN='943775543' and there
is no tuple in the EMPLOYEE relation with SSN='943775543'. We may enforce the constraint
by: (i) rejecting the deletion, or (ii) inserting a new EMPLOYEE tuple with SSN='943775543'.
(k) No constraint violations.
5.12 - Consider the AIRLINE relational database schema shown in Figure 3.8, which
describes a database for airline flight information. Each FLIGHT is identified by a flight
NUMBER, and consists of one or more FLIGHT_LEGs with LEG_NUMBERs 1, 2, 3, etc.
Each leg has scheduled arrival and departure times and airports, and has many
LEG_INSTANCEs--one for each DATE on which the flight travels. FARES are kept for each
flight. For each leg instance, SEAT_RESERVATIONs are kept, as is the AIRPLANE used in
the leg, and the actual arrival and departure times and airports. An AIRPLANE is identified
by an AIRPLANE_ID, and is of a particular AIRPLANE_TYPE. CAN_LAND relates
AIRPLANE_TYPEs to the AIRPORTs in which they can land. An AIRPORT is identified by
an AIRPORT_CODE. Consider an update for the AIRLINE database to enter a reservation
on a particular flight or flight leg on a given date.
(a) Give the operations for this update.
(b) What types of constraints would you expect to check?
(c) Which of these constraints are key, entity integrity, and referential integrity constraints
and which are not?
Loading page 27...
Chapter 5: The Relational Data Model and Relational Database Constraints 3
(d) Specify all the referential integrity constraints on Figure 3.8.
Answers:
(a) One possible set of operations for the following update is the following:
INSERT <FNO,LNO,DT,SEAT_NO,CUST_NAME,CUST_PHONE> into
SEAT_RESERVATION; MODIFY the LEG_INSTANCE tuple with the condition:
( FLIGHT_NUMBER=FNO AND LEG_NUMBER=LNO AND DATE=DT) by setting
NUMBER_OF_AVAILABLE_SEATS = NUMBER_OF_AVAILABLE_SEATS - 1; These
operations should be repeated for each LEG of the flight on which a reservation is made.
This assumes that the reservation has only one seat. More complex operations will be
needed for a more realistic reservation that may reserve several seats at once.
(b) We would check that NUMBER_OF_AVAILABLE_SEATS on each LEG_INSTANCE of
the flight is greater than 1 before doing any reservation (unless overbooking is permitted),
and that the SEAT_NUMBER being reserved in SEAT_RESERVATION is available.
(c) The INSERT operation into SEAT_RESERVATION will check all the key, entity integrity,
and referential integrity constraints for the relation. The check that
NUMBER_OF_AVAILABLE_SEATS on each LEG_INSTANCE of the flight is greater than 1
does not fall into any of the above types of constraints (it is a general semantic integrity
constraint).
(d) We will write a referential integrity constraint as R.A --> S (or R.(X) --> T) whenever
attribute A (or the set of attributes X) of relation R form a foreign key that references the
primary key of relation S (or T). FLIGHT_LEG.FLIGHT_NUMBER --> FLIGHT
FLIGHT_LEG.DEPARTURE_AIRPORT_CODE --> AIRPORT
FLIGHT_LEG.ARRIVAL_AIRPORT_CODE --> AIRPORT
LEG_INSTANCE.(FLIGHT_NUMBER,LEG_NUMBER) --> FLIGHT_LEG
LEG_INSTANCE.DEPARTURE_AIRPORT_CODE --> AIRPORT
LEG_INSTANCE.ARRIVAL_AIRPORT_CODE --> AIRPORT
LEG_INSTANCE.AIRPLANE_ID --> AIRPLANE
FARES.FLIGHT_NUMBER --> FLIGHT
CAN_LAND.AIRPLANE_TYPE_NAME --> AIRPLANE_TYPE
CAN_LAND.AIRPORT_CODE --> AIRPORT
AIRPLANE.AIRPLANE_TYPE --> AIRPLANE_TYPE
SEAT_RESERVATION.(FLIGHT_NUMBER,LEG_NUMBER,DATE) --> LEG_INSTANCE
5.13 - Consider the relation CLASS(Course#, Univ_Section#, InstructorName, Semester,
BuildingCode, Room#, TimePeriod, Weekdays, CreditHours). This represents classes taught
in a university with unique Univ_Section#. Give what you think should be various candidate
keys and write in your own words under what constraints each candidate key would be valid.
Answer:
Possible candidate keys include the following (Note: We assume that the values of the
Semester attribute include the year; for example "Spring/94" or "Fall/93" could be values for
Semester):
1. {Semester, BuildingCode, Room#, TimePeriod, Weekdays} if the same room cannot
be used at the same time by more than one course during a particular semester.
2. {Univ_Section#} if it is unique across all semesters.
3. {InstructorName, Semester} if an instructor can teach at most one course during each
semester.
4. If Univ_Section# is not unique, which is the case in many universities, we have to
examine the rules that the university uses for section numbering. For example, if the
(d) Specify all the referential integrity constraints on Figure 3.8.
Answers:
(a) One possible set of operations for the following update is the following:
INSERT <FNO,LNO,DT,SEAT_NO,CUST_NAME,CUST_PHONE> into
SEAT_RESERVATION; MODIFY the LEG_INSTANCE tuple with the condition:
( FLIGHT_NUMBER=FNO AND LEG_NUMBER=LNO AND DATE=DT) by setting
NUMBER_OF_AVAILABLE_SEATS = NUMBER_OF_AVAILABLE_SEATS - 1; These
operations should be repeated for each LEG of the flight on which a reservation is made.
This assumes that the reservation has only one seat. More complex operations will be
needed for a more realistic reservation that may reserve several seats at once.
(b) We would check that NUMBER_OF_AVAILABLE_SEATS on each LEG_INSTANCE of
the flight is greater than 1 before doing any reservation (unless overbooking is permitted),
and that the SEAT_NUMBER being reserved in SEAT_RESERVATION is available.
(c) The INSERT operation into SEAT_RESERVATION will check all the key, entity integrity,
and referential integrity constraints for the relation. The check that
NUMBER_OF_AVAILABLE_SEATS on each LEG_INSTANCE of the flight is greater than 1
does not fall into any of the above types of constraints (it is a general semantic integrity
constraint).
(d) We will write a referential integrity constraint as R.A --> S (or R.(X) --> T) whenever
attribute A (or the set of attributes X) of relation R form a foreign key that references the
primary key of relation S (or T). FLIGHT_LEG.FLIGHT_NUMBER --> FLIGHT
FLIGHT_LEG.DEPARTURE_AIRPORT_CODE --> AIRPORT
FLIGHT_LEG.ARRIVAL_AIRPORT_CODE --> AIRPORT
LEG_INSTANCE.(FLIGHT_NUMBER,LEG_NUMBER) --> FLIGHT_LEG
LEG_INSTANCE.DEPARTURE_AIRPORT_CODE --> AIRPORT
LEG_INSTANCE.ARRIVAL_AIRPORT_CODE --> AIRPORT
LEG_INSTANCE.AIRPLANE_ID --> AIRPLANE
FARES.FLIGHT_NUMBER --> FLIGHT
CAN_LAND.AIRPLANE_TYPE_NAME --> AIRPLANE_TYPE
CAN_LAND.AIRPORT_CODE --> AIRPORT
AIRPLANE.AIRPLANE_TYPE --> AIRPLANE_TYPE
SEAT_RESERVATION.(FLIGHT_NUMBER,LEG_NUMBER,DATE) --> LEG_INSTANCE
5.13 - Consider the relation CLASS(Course#, Univ_Section#, InstructorName, Semester,
BuildingCode, Room#, TimePeriod, Weekdays, CreditHours). This represents classes taught
in a university with unique Univ_Section#. Give what you think should be various candidate
keys and write in your own words under what constraints each candidate key would be valid.
Answer:
Possible candidate keys include the following (Note: We assume that the values of the
Semester attribute include the year; for example "Spring/94" or "Fall/93" could be values for
Semester):
1. {Semester, BuildingCode, Room#, TimePeriod, Weekdays} if the same room cannot
be used at the same time by more than one course during a particular semester.
2. {Univ_Section#} if it is unique across all semesters.
3. {InstructorName, Semester} if an instructor can teach at most one course during each
semester.
4. If Univ_Section# is not unique, which is the case in many universities, we have to
examine the rules that the university uses for section numbering. For example, if the
Loading page 28...
Chapter 5: The Relational Data Model and Relational Database Constraints4
sections of a particular course during a particular semester are numbered 1, 2, 3, …then
a candidate key would be {Course#, Univ_Section#, Semester}. If, on the other hand, all
sections (of any course) have unique numbers during a particular semester only, then the
candidate key would be {Univ_Section#, Semester}.
5.14 - Consider the following six relations for an order-processing database application in a
company:
CUSTOMER (Cust#, Cname, City)
ORDER (Order#, Odate, Cust#, Ord_Amt)
ORDER_ITEM (Order#, Item#, Qty)
ITEM (Item#, Unit_price)
SHIPMENT (Order#, Warehouse#, Ship_date)
WAREHOUSE (Warehouse#, City)
Here, Ord_Amt refers to total dollar amount of an order; Odate is the date the order was
placed; Ship_date is the date an order (or part of an order) is shipped from the warehouse.
Assume that an order can be shipped from several warehouses. Specify the foreign keys for
this schema, stating any assumptions you make. What other constraints can you think of for
this database?
Answer:
Strictly speaking, a foreign key is a set of attributes, but when that set contains only one
attribute, then that attribute itself is often informally called a foreign key. The schema of this
question has the following five foreign keys:
1. the attribute Cust# of relation ORDER that references relation CUSTOMER,
2. the attribute Order# of relation ORDER_ITEM that references relation ORDER,
3. the attribute Item# of relation ORDER_ITEM that references relation ITEM,
4. the attribute Order# of relation SHIPMENT that references relation ORDER, and
5. the attribute Warehouse# of relation SHIPMENT that references relation WAREHOUSE.
We now give the queries in relational algebra:
sections of a particular course during a particular semester are numbered 1, 2, 3, …then
a candidate key would be {Course#, Univ_Section#, Semester}. If, on the other hand, all
sections (of any course) have unique numbers during a particular semester only, then the
candidate key would be {Univ_Section#, Semester}.
5.14 - Consider the following six relations for an order-processing database application in a
company:
CUSTOMER (Cust#, Cname, City)
ORDER (Order#, Odate, Cust#, Ord_Amt)
ORDER_ITEM (Order#, Item#, Qty)
ITEM (Item#, Unit_price)
SHIPMENT (Order#, Warehouse#, Ship_date)
WAREHOUSE (Warehouse#, City)
Here, Ord_Amt refers to total dollar amount of an order; Odate is the date the order was
placed; Ship_date is the date an order (or part of an order) is shipped from the warehouse.
Assume that an order can be shipped from several warehouses. Specify the foreign keys for
this schema, stating any assumptions you make. What other constraints can you think of for
this database?
Answer:
Strictly speaking, a foreign key is a set of attributes, but when that set contains only one
attribute, then that attribute itself is often informally called a foreign key. The schema of this
question has the following five foreign keys:
1. the attribute Cust# of relation ORDER that references relation CUSTOMER,
2. the attribute Order# of relation ORDER_ITEM that references relation ORDER,
3. the attribute Item# of relation ORDER_ITEM that references relation ITEM,
4. the attribute Order# of relation SHIPMENT that references relation ORDER, and
5. the attribute Warehouse# of relation SHIPMENT that references relation WAREHOUSE.
We now give the queries in relational algebra:
Loading page 29...
Chapter 5: The Relational Data Model and Relational Database Constraints 5
The above query lists all orders for which no “timely” shipment was made, including orders
for which no shipment was ever made. It is instructive to compare the above query with the
one below that lists those orders for which at least one “late” shipment was made.
5.15 - Consider the following relations for a database that keeps track of business trips of
salespersons in a sales office:
SALESPERSON (SSN, Name, Start_Year, Dept_No)
TRIP (SSN, From_City, To_City, Departure_Date, Return_Date, Trip_ID)
EXPENSE (Trip_ID, Account#, Amount)
Specify the foreign keys for this schema, stating any assumptions you make.
Answer:
The schema of this question has the following two foreign keys:
1. the attribute SSN of relation TRIP that references relation SALESPERSON, and
2. the attribute Trip_ID of relation EXPENSE that references relation TRIP.
In addition, the attributes Dept_No of relation SALESPERSON and Account# of relation
EXPENSE are probably also foreign keys referencing other relations of the database not
mentioned in the question. We now give the queries in relational algebra:
5.16 - Consider the following relations for a database that keeps track of student enrollment
in courses and the books adopted for each course:
STUDENT (SSN, Name, Major, Bdate)
COURSE (Course#, Quarter, Grade)
ENROLL (SSN, Course#, Quarter, Grade)
BOOK_ADOPTION (Course#, Quarter, Book_ISBN)
TEXT (Book_ISBN, Book_Title, Publisher, Author)
Specify the foreign keys for this schema, stating any assumptions you make.
The above query lists all orders for which no “timely” shipment was made, including orders
for which no shipment was ever made. It is instructive to compare the above query with the
one below that lists those orders for which at least one “late” shipment was made.
5.15 - Consider the following relations for a database that keeps track of business trips of
salespersons in a sales office:
SALESPERSON (SSN, Name, Start_Year, Dept_No)
TRIP (SSN, From_City, To_City, Departure_Date, Return_Date, Trip_ID)
EXPENSE (Trip_ID, Account#, Amount)
Specify the foreign keys for this schema, stating any assumptions you make.
Answer:
The schema of this question has the following two foreign keys:
1. the attribute SSN of relation TRIP that references relation SALESPERSON, and
2. the attribute Trip_ID of relation EXPENSE that references relation TRIP.
In addition, the attributes Dept_No of relation SALESPERSON and Account# of relation
EXPENSE are probably also foreign keys referencing other relations of the database not
mentioned in the question. We now give the queries in relational algebra:
5.16 - Consider the following relations for a database that keeps track of student enrollment
in courses and the books adopted for each course:
STUDENT (SSN, Name, Major, Bdate)
COURSE (Course#, Quarter, Grade)
ENROLL (SSN, Course#, Quarter, Grade)
BOOK_ADOPTION (Course#, Quarter, Book_ISBN)
TEXT (Book_ISBN, Book_Title, Publisher, Author)
Specify the foreign keys for this schema, stating any assumptions you make.
Loading page 30...
Chapter 5: The Relational Data Model and Relational Database Constraints6
Answer:
The schema of this question has the following four foreign keys:
3. the attribute SSN of relation ENROLL that references relation STUDENT,
4. the attribute Course# in relation ENROLL that references relation COURSE,
5. the attribute Course# in relation BOOK_ADOPTION that references relation COURSE, and
6. the attribute Book_ISBN of relation BOOK_ADOPTION that references relation TEXT.
We now give the queries in relational algebra:
5.18 - Database design often involves decisions about the storage of attributes. For example
a Social Security Number can be stored as a one attribute or split into three attributes (one
for each of the three hyphen-deliniated groups of numbers in a Social Security Number—
XXX-XX-XXXX). However, Social Security Number is usually stored in one attribute. The
decision is usually based on how the database will be used. This exercise asks you to think
about specific situations where dividing the SSN is useful.
Answer:
a. We need the area code (also know as city code in some countries) and perhaps the
country code (for dialing international phone numbers).
b. I would recommend storing the numbers in a separate attribute as they have their own
independent existence. For example, if an area code region were split into two regions, it
would change the area code associated with certain numbers, and having area code in a
separate attribute will make it is easier to update the area code attribute by itself.
c. I would recommend splitting first name, middle name, and last name into different
attributes as it is likely that the names may be sorted and/or retrieved by the last name, etc.
d. In general, if the each attribute has an independent logical existence based on the
Answer:
The schema of this question has the following four foreign keys:
3. the attribute SSN of relation ENROLL that references relation STUDENT,
4. the attribute Course# in relation ENROLL that references relation COURSE,
5. the attribute Course# in relation BOOK_ADOPTION that references relation COURSE, and
6. the attribute Book_ISBN of relation BOOK_ADOPTION that references relation TEXT.
We now give the queries in relational algebra:
5.18 - Database design often involves decisions about the storage of attributes. For example
a Social Security Number can be stored as a one attribute or split into three attributes (one
for each of the three hyphen-deliniated groups of numbers in a Social Security Number—
XXX-XX-XXXX). However, Social Security Number is usually stored in one attribute. The
decision is usually based on how the database will be used. This exercise asks you to think
about specific situations where dividing the SSN is useful.
Answer:
a. We need the area code (also know as city code in some countries) and perhaps the
country code (for dialing international phone numbers).
b. I would recommend storing the numbers in a separate attribute as they have their own
independent existence. For example, if an area code region were split into two regions, it
would change the area code associated with certain numbers, and having area code in a
separate attribute will make it is easier to update the area code attribute by itself.
c. I would recommend splitting first name, middle name, and last name into different
attributes as it is likely that the names may be sorted and/or retrieved by the last name, etc.
d. In general, if the each attribute has an independent logical existence based on the
Loading page 31...
30 more pages available. Scroll down to load them.
Preview Mode
Sign in to access the full document!
100%
Study Now!
XY-Copilot AI
Unlimited Access
Secure Payment
Instant Access
24/7 Support
AI Assistant
Document Details
Subject
Information Technology