Solution Manual For Fundamentals Of Database Systems, 6th Edition
Solution Manual For Fundamentals Of Database Systems, 6th Edition is packed with detailed solutions to help you grasp concepts effortlessly.
Victoria Thompson
Contributor
4.2
65
about 2 months ago
Preview (31 of 155)
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
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
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
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
Chapter 1: Databases and Database Users2
university administration's use.
(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 that 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.1. Small internal utility to locate files
2.2. Small single user application that does not require security (such as a customized
calculator or a personal address and phone book)
3.3. Real-time navigation system (with heavy computation and very little data)
4.4. The students may think of others.
1.14 - Consider Figure 1.2.
a.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.b. Can you restructure the columns in COURSE, SECTION, and PREREQUISITE tables so
that only one column will need to be updated?
Formatted: Bullets and Numbering
Formatted: Bullets and Numbering
university administration's use.
(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 that 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.1. Small internal utility to locate files
2.2. Small single user application that does not require security (such as a customized
calculator or a personal address and phone book)
3.3. Real-time navigation system (with heavy computation and very little data)
4.4. The students may think of others.
1.14 - Consider Figure 1.2.
a.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.b. Can you restructure the columns in COURSE, SECTION, and PREREQUISITE tables so
that only one column will need to be updated?
Formatted: Bullets and Numbering
Formatted: Bullets and Numbering
Chapter 1: Databases and Database Users 3
Answer:
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.
Answer:
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.
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.
Loading page 5...
Chapter 2: Database System Concepts and Architecture2
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
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.
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
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: The Relational Data Model and Relational Database Constraints 1
CHAPTER 3: THE RELATIONAL DATA MODEL AND RELATIONAL DATABASE
CONSTRAINTS
Answers to Selected Exercises
3.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'.
(d) Violates both the entity integrity and referential integrity. Violates entity integrity
CHAPTER 3: THE RELATIONAL DATA MODEL AND RELATIONAL DATABASE
CONSTRAINTS
Answers to Selected Exercises
3.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'.
(d) Violates both the entity integrity and referential integrity. Violates entity integrity
Loading page 7...
Chapter 3: The Relational Data Model and Relational Database Constraints2
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.
3.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?
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.
3.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 8...
Chapter 3: 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
3.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
(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
3.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
Loading page 9...
Chapter 3: The Relational Data Model and Relational Database Constraints4
examine the rules that the university uses for section numbering. For example, if the
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}.
3.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:
examine the rules that the university uses for section numbering. For example, if the
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}.
3.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 10...
Chapter 3: 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.
3.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:
3.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)
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.
3.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:
3.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)
Loading page 11...
Chapter 3: The Relational Data Model and Relational Database Constraints6
Specify the foreign keys for this schema, stating any assumptions you make.
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:
3.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.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.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.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.
Formatted: Bullets and Numbering
Specify the foreign keys for this schema, stating any assumptions you make.
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:
3.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.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.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.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.
Formatted: Bullets and Numbering
Loading page 12...
Chapter 3: The Relational Data Model and Relational Database Constraints 7
d.d. In general, if the each attribute has an independent logical existence based on the
application, it would make sense to store it in a separate column otherwise there is no clear
advantage. For example, SSN need not be split into its component unless we are using the
subsequences to make deductions about validity, geography, etc. In the two cases above, it
made logical and business sense to split the attributes.
3.19 - Consider a STUDENT relation in a UNIVERSITY database with the following attributes
(Name, SSN, Local_phone, Address, Cell_phone, Age, GPA). Note that the cell phone may
be from a different city and state (or province) from the local phone. A possible tuple of the
relation is shown below:
Name SSN LocalPhone Address CellPhone Age GPA
George Shaw
William Edwards
123-45-
6789
555-1234 123 Main St.,
Anytown, CA 94539
555-4321 19 3.75
a. Identify the critical missing information from the LocalPhone and CellPhone attributes as
shown in the example above. (Hint: How do call someone who lives in a different state or
province?)
b. Would you store this additional information in the LocalPhone and CellPhone attributes or
add new attributes to the schema for STUDENT?
c. Consider the Name attribute. What are the advantages and disadvantages of splitting this
field from one attribute into three attributes (first name, middle name, and last name)?
d. What general guideline would you recommend for deciding when to store information in a
single attribute and when to split the information.
Answer:
a. A combination of first name, last name, and home phone may address the issue assuming
that there are no two students with identical names sharing a home phone line. It also
assumes that every student has a home phone number. Another solution may be to use first
name, last name, and home zip code. This again has a potential for duplicates, which would
be very rare within one university. An extreme solution is to use a combination of characters
from last name, major, house number etc.
b. If we use name in a primary key and the name changes then the primary key changes.
Changing the primary key is acceptable but can be inefficient as any references to this key in
the database need to be appropriately updated, and that can take a long time in a large
database. Also, the new primary key must remain unique. [Footnote: Name change is an
example of where our database must be able to model the natural world. In this case, we
recognize that the name change can occur regardless of whether it is due to marriage, or a
consequence of a religious and/or spiritual conversion, or for any other reason.]
c. The challenge of choosing an invariant primary key from the natural data items leads to
the concept of generated keys, also known as surrogate keys. Specifically, we can use
surrogate keys instead of keys that occur naturally in the database. Some database
professionals believe that it is best to use keys that are uniquely generated by the database,
for example each row may have a primary key that is generated in the sequence of creation
of rows (tuples). There are many advantages and disadvantages that are often been argued
in design sessions. The main advantage is that it gives us an invariant key without any
worries about choosing a unique primary key. The main disadvantages of surrogate keys are
that they do not have a business meaning (making some aspects of database management
challenging) and that they are slightly less efficient (because they require another pass when
inserting a row because the key often needs to be returned to the application after a row is
d.d. In general, if the each attribute has an independent logical existence based on the
application, it would make sense to store it in a separate column otherwise there is no clear
advantage. For example, SSN need not be split into its component unless we are using the
subsequences to make deductions about validity, geography, etc. In the two cases above, it
made logical and business sense to split the attributes.
3.19 - Consider a STUDENT relation in a UNIVERSITY database with the following attributes
(Name, SSN, Local_phone, Address, Cell_phone, Age, GPA). Note that the cell phone may
be from a different city and state (or province) from the local phone. A possible tuple of the
relation is shown below:
Name SSN LocalPhone Address CellPhone Age GPA
George Shaw
William Edwards
123-45-
6789
555-1234 123 Main St.,
Anytown, CA 94539
555-4321 19 3.75
a. Identify the critical missing information from the LocalPhone and CellPhone attributes as
shown in the example above. (Hint: How do call someone who lives in a different state or
province?)
b. Would you store this additional information in the LocalPhone and CellPhone attributes or
add new attributes to the schema for STUDENT?
c. Consider the Name attribute. What are the advantages and disadvantages of splitting this
field from one attribute into three attributes (first name, middle name, and last name)?
d. What general guideline would you recommend for deciding when to store information in a
single attribute and when to split the information.
Answer:
a. A combination of first name, last name, and home phone may address the issue assuming
that there are no two students with identical names sharing a home phone line. It also
assumes that every student has a home phone number. Another solution may be to use first
name, last name, and home zip code. This again has a potential for duplicates, which would
be very rare within one university. An extreme solution is to use a combination of characters
from last name, major, house number etc.
b. If we use name in a primary key and the name changes then the primary key changes.
Changing the primary key is acceptable but can be inefficient as any references to this key in
the database need to be appropriately updated, and that can take a long time in a large
database. Also, the new primary key must remain unique. [Footnote: Name change is an
example of where our database must be able to model the natural world. In this case, we
recognize that the name change can occur regardless of whether it is due to marriage, or a
consequence of a religious and/or spiritual conversion, or for any other reason.]
c. The challenge of choosing an invariant primary key from the natural data items leads to
the concept of generated keys, also known as surrogate keys. Specifically, we can use
surrogate keys instead of keys that occur naturally in the database. Some database
professionals believe that it is best to use keys that are uniquely generated by the database,
for example each row may have a primary key that is generated in the sequence of creation
of rows (tuples). There are many advantages and disadvantages that are often been argued
in design sessions. The main advantage is that it gives us an invariant key without any
worries about choosing a unique primary key. The main disadvantages of surrogate keys are
that they do not have a business meaning (making some aspects of database management
challenging) and that they are slightly less efficient (because they require another pass when
inserting a row because the key often needs to be returned to the application after a row is
Loading page 13...
Chapter 3: The Relational Data Model and Relational Database Constraints8
inserted).
3.20 - Recent changes in privacy laws have disallowed organizations from using SSN to
identify individuals unless certain restrictions are satisfied. As a result, most US universities
cannot use SSNs as primary keys (except for financial data). In practice, StudentID, a unique
ID, a unique identifier, assigned to every student, is likely to be used as the primary key
rather than SSN since StudentID is usable across all aspects of the system.
a. Some database designers are reluctant to use generated keys (also known as surrogate
keys) for primary keys (such as StudentID) because they are artificial. Can you propose any
natural choices of keys that can be used to store the student record in a UNIVERSITY
database?
b. Suppose that you were able to guarantee uniqueness of a natural key that included last
name. Are you guaranteed that the last name will not change during the lifetime of the
database? If the last name last name can change, what solutions can you propose for
creating a primary key that still includes last name but remains unique?
c. What are the advantages and disadvantages of using generated (surrogate) keys?
Answer:
a. By keeping the name attributes separated, we allow the possibility of looking these pieces
of their name. In a practical use, it is not likely that the user will know the correct primary key
for a given student and so we must consider how a user will locate the correct row without
this information. If we were to collapse the name into a single attribute, then we have
complicated any sort of “lookup by name” query; such a query would then require partial
string matching and any results might not disambiguate between FirstName and LastName.
Therefore, a practical system should allow name searches by FirstName and LastName; we
must leave MiddleInitial separated still to avoid ambiguities from combining these pieces
together.
b. A single attribute Phone# would no longer suffice if a student were able to have multiple
phone numbers. We could possibly have multiple rows for a single student to allow this to
happen, but then we have violated key principles of database design (e.g. having redundant
data). A better solution would be to include the additional attributes HomePhone, CellPhone,
and OfficePhone and allow the possibility of these attributes to have no value. Again, this is
not most desirable because most students will not have all three of these attributes, and we
will have many valueless key/attribute pairs. An excellent solution would be add an
additional relation Phone# (SSN, Type, Number) while removing PhoneNumber from the
Student relationship. This new relationship would allow the one-to-many relationship from
students to phone numbers without creating redundant data or wasting space on sparse,
valueless attributes.
inserted).
3.20 - Recent changes in privacy laws have disallowed organizations from using SSN to
identify individuals unless certain restrictions are satisfied. As a result, most US universities
cannot use SSNs as primary keys (except for financial data). In practice, StudentID, a unique
ID, a unique identifier, assigned to every student, is likely to be used as the primary key
rather than SSN since StudentID is usable across all aspects of the system.
a. Some database designers are reluctant to use generated keys (also known as surrogate
keys) for primary keys (such as StudentID) because they are artificial. Can you propose any
natural choices of keys that can be used to store the student record in a UNIVERSITY
database?
b. Suppose that you were able to guarantee uniqueness of a natural key that included last
name. Are you guaranteed that the last name will not change during the lifetime of the
database? If the last name last name can change, what solutions can you propose for
creating a primary key that still includes last name but remains unique?
c. What are the advantages and disadvantages of using generated (surrogate) keys?
Answer:
a. By keeping the name attributes separated, we allow the possibility of looking these pieces
of their name. In a practical use, it is not likely that the user will know the correct primary key
for a given student and so we must consider how a user will locate the correct row without
this information. If we were to collapse the name into a single attribute, then we have
complicated any sort of “lookup by name” query; such a query would then require partial
string matching and any results might not disambiguate between FirstName and LastName.
Therefore, a practical system should allow name searches by FirstName and LastName; we
must leave MiddleInitial separated still to avoid ambiguities from combining these pieces
together.
b. A single attribute Phone# would no longer suffice if a student were able to have multiple
phone numbers. We could possibly have multiple rows for a single student to allow this to
happen, but then we have violated key principles of database design (e.g. having redundant
data). A better solution would be to include the additional attributes HomePhone, CellPhone,
and OfficePhone and allow the possibility of these attributes to have no value. Again, this is
not most desirable because most students will not have all three of these attributes, and we
will have many valueless key/attribute pairs. An excellent solution would be add an
additional relation Phone# (SSN, Type, Number) while removing PhoneNumber from the
Student relationship. This new relationship would allow the one-to-many relationship from
students to phone numbers without creating redundant data or wasting space on sparse,
valueless attributes.
Loading page 14...
Chapter 4: Basic SQL 1
CHAPTER 4: Basic SQL
Answers to Selected Exercises
4.5 - Consider the database shown in Figure 1.2, whose schema is shown in Figure 2.1.
What are the referential integrity constraints that should hold on the schema?
Write appropriate SQL DDL statements to define the database.
Answer:
The following referential integrity constraints should hold (we use the notation:
R.(A1, ..., An) --> S.(B1, ..., Bn)
to represent a foreign key from the attributes A1, ..., An of R (the referencing relation)
to S (the referenced relation)):
PREREQUISITE.(CourseNumber) --> COURSE.(CourseNumber)
PREREQUISITE.(PrerequisiteNumber) --> COURSE.(CourseNumber)
SECTION.(CourseNumber) --> COURSE.(CourseNumber)
GRADE_REPORT.(StudentNumber) --> STUDENT.(StudentNumber)
GRADE_REPORT.(SectionIdentifier) --> SECTION.(SectionIdentifier)
One possible set of CREATE TABLE statements to define the database is given below.
CREATE TABLE STUDENT ( Name VARCHAR(30) NOT NULL,
StudentNumber INTEGER NOT NULL,
Class CHAR NOT NULL,
Major CHAR(4),
PRIMARY KEY (StudentNumber) );
CREATE TABLE COURSE ( CourseName VARCHAR(30) NOT NULL,
CourseNumber CHAR(8) NOT NULL,
CreditHours INTEGER,
Department CHAR(4),
PRIMARY KEY (CourseNumber),
UNIQUE (CourseName) );
CREATE TABLE PREREQUISITE ( CourseNumber CHAR(8) NOT NULL,
PrerequisiteNumber CHAR(8) NOT NULL,
PRIMARY KEY (CourseNumber, PrerequisiteNumber),
FOREIGN KEY (CourseNumber) REFERENCES
COURSE (CourseNumber),
FOREIGN KEY (PrerequisiteNumber) REFERENCES
COURSE (CourseNumber) );
CREATE TABLE SECTION ( SectionIdentifier INTEGER NOT NULL,
CourseNumber CHAR(8) NOT NULL,
Semester VARCHAR(6) NOT NULL,
Year CHAR(4) NOT NULL,
Instructor VARCHAR(15),
PRIMARY KEY (SectionIdentifier),
FOREIGN KEY (CourseNumber) REFERENCES
COURSE (CourseNumber) );
CREATE TABLE GRADE_REPORT ( StudentNumber INTEGER NOT NULL,
SectionIdentifier INTEGER NOT NULL,
Grade CHAR,
PRIMARY KEY (StudentNumber, SectionIdentifier),
FOREIGN KEY (StudentNumber) REFERENCES
STUDENT (StudentNumber),
FOREIGN KEY (SectionIdentifier) REFERENCES
SECTION (SectionIdentifier) );
4.6 - Repeat Exercise 4.5, but use the AIRLINE schema of Figure 3.8.
Answer:
CHAPTER 4: Basic SQL
Answers to Selected Exercises
4.5 - Consider the database shown in Figure 1.2, whose schema is shown in Figure 2.1.
What are the referential integrity constraints that should hold on the schema?
Write appropriate SQL DDL statements to define the database.
Answer:
The following referential integrity constraints should hold (we use the notation:
R.(A1, ..., An) --> S.(B1, ..., Bn)
to represent a foreign key from the attributes A1, ..., An of R (the referencing relation)
to S (the referenced relation)):
PREREQUISITE.(CourseNumber) --> COURSE.(CourseNumber)
PREREQUISITE.(PrerequisiteNumber) --> COURSE.(CourseNumber)
SECTION.(CourseNumber) --> COURSE.(CourseNumber)
GRADE_REPORT.(StudentNumber) --> STUDENT.(StudentNumber)
GRADE_REPORT.(SectionIdentifier) --> SECTION.(SectionIdentifier)
One possible set of CREATE TABLE statements to define the database is given below.
CREATE TABLE STUDENT ( Name VARCHAR(30) NOT NULL,
StudentNumber INTEGER NOT NULL,
Class CHAR NOT NULL,
Major CHAR(4),
PRIMARY KEY (StudentNumber) );
CREATE TABLE COURSE ( CourseName VARCHAR(30) NOT NULL,
CourseNumber CHAR(8) NOT NULL,
CreditHours INTEGER,
Department CHAR(4),
PRIMARY KEY (CourseNumber),
UNIQUE (CourseName) );
CREATE TABLE PREREQUISITE ( CourseNumber CHAR(8) NOT NULL,
PrerequisiteNumber CHAR(8) NOT NULL,
PRIMARY KEY (CourseNumber, PrerequisiteNumber),
FOREIGN KEY (CourseNumber) REFERENCES
COURSE (CourseNumber),
FOREIGN KEY (PrerequisiteNumber) REFERENCES
COURSE (CourseNumber) );
CREATE TABLE SECTION ( SectionIdentifier INTEGER NOT NULL,
CourseNumber CHAR(8) NOT NULL,
Semester VARCHAR(6) NOT NULL,
Year CHAR(4) NOT NULL,
Instructor VARCHAR(15),
PRIMARY KEY (SectionIdentifier),
FOREIGN KEY (CourseNumber) REFERENCES
COURSE (CourseNumber) );
CREATE TABLE GRADE_REPORT ( StudentNumber INTEGER NOT NULL,
SectionIdentifier INTEGER NOT NULL,
Grade CHAR,
PRIMARY KEY (StudentNumber, SectionIdentifier),
FOREIGN KEY (StudentNumber) REFERENCES
STUDENT (StudentNumber),
FOREIGN KEY (SectionIdentifier) REFERENCES
SECTION (SectionIdentifier) );
4.6 - Repeat Exercise 4.5, but use the AIRLINE schema of Figure 3.8.
Answer:
Loading page 15...
Chapter 4: Basic SQL2
The following referential integrity constraints should hold:
FLIGHT_LEG.(FLIGHT_NUMBER) --> FLIGHT.(NUMBER)
FLIGHT_LEG.(DEPARTURE_AIRPORT_CODE) --> AIRPORT.(AIRPORT_CODE)
FLIGHT_LEG.(ARRIVAL_AIRPORT_CODE) --> AIRPORT.(AIRPORT_CODE)
LEG_INSTANCE.(FLIGHT_NUMBER, LEG_NUMBER) -->
FLIGHT_LEG.(FLIGHT_NUMBER, LEG_NUMBER)
LEG_INSTANCE.(AIRPLANE_ID) --> AIRPLANE.(AIRPLANE_ID)
LEG_INSTANCE.(DEPARTURE_AIRPORT_CODE) --> AIRPORT.(AIRPORT_CODE)
LEG_INSTANCE.(ARRIVAL_AIRPORT_CODE) --> AIRPORT.(AIRPORT_CODE)
FARES.(FLIGHT_NUMBER) --> FLIGHT.(NUMBER)
CAN_LAND.(AIRPLANE_TYPE_NAME) --> AIRPLANE_TYPE.(TYPE_NAME)
CAN_LAND.(AIRPORT_CODE) --> AIRPORT.(AIRPORT_CODE)
AIRPLANE.(AIRPLANE_TYPE) --> AIRPLANE_TYPE.(TYPE_NAME)
SEAT_RESERVATION.(FLIGHT_NUMBER, LEG_NUMBER, DATE) -->
LEG_INSTANCE.(FLIGHT_NUMBER, LEG_NUMBER, DATE)
One possible set of CREATE TABLE statements to define the database is given below.
CREATE TABLE AIRPORT ( AIRPORT_CODE CHAR(3) NOT NULL,
NAME VARCHAR(30) NOT NULL,
CITY VARCHAR(30) NOT NULL,
STATE VARCHAR(30),
PRIMARY KEY (AIRPORT_CODE) );
CREATE TABLE FLIGHT ( NUMBER VARCHAR(6) NOT NULL,
AIRLINE VARCHAR(20) NOT NULL,
WEEKDAYS VARCHAR(10) NOT NULL,
PRIMARY KEY (NUMBER) );
CREATE TABLE FLIGHT_LEG ( FLIGHT_NUMBER VARCHAR(6) NOT NULL,
LEG_NUMBER INTEGER NOT NULL,
DEPARTURE_AIRPORT_CODE CHAR(3) NOT NULL,
SCHEDULED_DEPARTURE_TIME TIMESTAMP WITH TIME ZONE,
ARRIVAL_AIRPORT_CODE CHAR(3) NOT NULL,
SCHEDULED_ARRIVAL_TIME TIMESTAMP WITH TIME ZONE,
PRIMARY KEY (FLIGHT_NUMBER, LEG_NUMBER),
FOREIGN KEY (FLIGHT_NUMBER) REFERENCES FLIGHT (NUMBER),
FOREIGN KEY (DEPARTURE_AIRPORT_CODE) REFERENCES
AIRPORT (AIRPORT_CODE),
FOREIGN KEY (ARRIVAL_AIRPORT_CODE) REFERENCES
AIRPORT (AIRPORT_CODE) );
CREATE TABLE LEG_INSTANCE ( FLIGHT_NUMBER VARCHAR(6) NOT NULL,
LEG_NUMBER INTEGER NOT NULL,
LEG_DATE DATE NOT NULL,
NO_OF_AVAILABLE_SEATS INTEGER,
AIRPLANE_ID INTEGER,
DEPARTURE_AIRPORT_CODE CHAR(3),
DEPARTURE_TIME TIMESTAMP WITH TIME ZONE,
ARRIVAL_AIRPORT_CODE CHAR(3),
ARRIVAL_TIME TIMESTAMP WITH TIME ZONE,
PRIMARY KEY (FLIGHT_NUMBER, LEG_NUMBER, LEG_DATE),
FOREIGN KEY (FLIGHT_NUMBER, LEG_NUMBER) REFERENCES
FLIGHT_LEG (FLIGHT_NUMBER, LEG_NUMBER),
FOREIGN KEY (AIRPLANE_ID) REFERENCES
AIRPLANE (AIRPLANE_ID),
FOREIGN KEY (DEPARTURE_AIRPORT_CODE) REFERENCES
AIRPORT (AIRPORT_CODE),
FOREIGN KEY (ARRIVAL_AIRPORT_CODE) REFERENCES
AIRPORT (AIRPORT_CODE) );
CREATE TABLE FARES ( FLIGHT_NUMBER VARCHAR(6) NOT NULL,
FARE_CODE VARCHAR(10) NOT NULL,
The following referential integrity constraints should hold:
FLIGHT_LEG.(FLIGHT_NUMBER) --> FLIGHT.(NUMBER)
FLIGHT_LEG.(DEPARTURE_AIRPORT_CODE) --> AIRPORT.(AIRPORT_CODE)
FLIGHT_LEG.(ARRIVAL_AIRPORT_CODE) --> AIRPORT.(AIRPORT_CODE)
LEG_INSTANCE.(FLIGHT_NUMBER, LEG_NUMBER) -->
FLIGHT_LEG.(FLIGHT_NUMBER, LEG_NUMBER)
LEG_INSTANCE.(AIRPLANE_ID) --> AIRPLANE.(AIRPLANE_ID)
LEG_INSTANCE.(DEPARTURE_AIRPORT_CODE) --> AIRPORT.(AIRPORT_CODE)
LEG_INSTANCE.(ARRIVAL_AIRPORT_CODE) --> AIRPORT.(AIRPORT_CODE)
FARES.(FLIGHT_NUMBER) --> FLIGHT.(NUMBER)
CAN_LAND.(AIRPLANE_TYPE_NAME) --> AIRPLANE_TYPE.(TYPE_NAME)
CAN_LAND.(AIRPORT_CODE) --> AIRPORT.(AIRPORT_CODE)
AIRPLANE.(AIRPLANE_TYPE) --> AIRPLANE_TYPE.(TYPE_NAME)
SEAT_RESERVATION.(FLIGHT_NUMBER, LEG_NUMBER, DATE) -->
LEG_INSTANCE.(FLIGHT_NUMBER, LEG_NUMBER, DATE)
One possible set of CREATE TABLE statements to define the database is given below.
CREATE TABLE AIRPORT ( AIRPORT_CODE CHAR(3) NOT NULL,
NAME VARCHAR(30) NOT NULL,
CITY VARCHAR(30) NOT NULL,
STATE VARCHAR(30),
PRIMARY KEY (AIRPORT_CODE) );
CREATE TABLE FLIGHT ( NUMBER VARCHAR(6) NOT NULL,
AIRLINE VARCHAR(20) NOT NULL,
WEEKDAYS VARCHAR(10) NOT NULL,
PRIMARY KEY (NUMBER) );
CREATE TABLE FLIGHT_LEG ( FLIGHT_NUMBER VARCHAR(6) NOT NULL,
LEG_NUMBER INTEGER NOT NULL,
DEPARTURE_AIRPORT_CODE CHAR(3) NOT NULL,
SCHEDULED_DEPARTURE_TIME TIMESTAMP WITH TIME ZONE,
ARRIVAL_AIRPORT_CODE CHAR(3) NOT NULL,
SCHEDULED_ARRIVAL_TIME TIMESTAMP WITH TIME ZONE,
PRIMARY KEY (FLIGHT_NUMBER, LEG_NUMBER),
FOREIGN KEY (FLIGHT_NUMBER) REFERENCES FLIGHT (NUMBER),
FOREIGN KEY (DEPARTURE_AIRPORT_CODE) REFERENCES
AIRPORT (AIRPORT_CODE),
FOREIGN KEY (ARRIVAL_AIRPORT_CODE) REFERENCES
AIRPORT (AIRPORT_CODE) );
CREATE TABLE LEG_INSTANCE ( FLIGHT_NUMBER VARCHAR(6) NOT NULL,
LEG_NUMBER INTEGER NOT NULL,
LEG_DATE DATE NOT NULL,
NO_OF_AVAILABLE_SEATS INTEGER,
AIRPLANE_ID INTEGER,
DEPARTURE_AIRPORT_CODE CHAR(3),
DEPARTURE_TIME TIMESTAMP WITH TIME ZONE,
ARRIVAL_AIRPORT_CODE CHAR(3),
ARRIVAL_TIME TIMESTAMP WITH TIME ZONE,
PRIMARY KEY (FLIGHT_NUMBER, LEG_NUMBER, LEG_DATE),
FOREIGN KEY (FLIGHT_NUMBER, LEG_NUMBER) REFERENCES
FLIGHT_LEG (FLIGHT_NUMBER, LEG_NUMBER),
FOREIGN KEY (AIRPLANE_ID) REFERENCES
AIRPLANE (AIRPLANE_ID),
FOREIGN KEY (DEPARTURE_AIRPORT_CODE) REFERENCES
AIRPORT (AIRPORT_CODE),
FOREIGN KEY (ARRIVAL_AIRPORT_CODE) REFERENCES
AIRPORT (AIRPORT_CODE) );
CREATE TABLE FARES ( FLIGHT_NUMBER VARCHAR(6) NOT NULL,
FARE_CODE VARCHAR(10) NOT NULL,
Loading page 16...
Chapter 4: Basic SQL 3
AMOUNT DECIMAL(8,2) NOT NULL,
RESTRICTIONS VARCHAR(200),
PRIMARY KEY (FLIGHT_NUMBER, FARE_CODE),
FOREIGN KEY (FLIGHT_NUMBER) REFERENCES FLIGHT (NUMBER) );
CREATE TABLE AIRPLANE_TYPE ( TYPE_NAME VARCHAR(20) NOT NULL,
MAX_SEATS INTEGER NOT NULL,
COMPANY VARCHAR(15) NOT NULL,
PRIMARY KEY (TYPE_NAME) );
CREATE TABLE CAN_LAND ( AIRPLANE_TYPE_NAME VARCHAR(20) NOT NULL,
AIRPORT_CODE CHAR(3) NOT NULL,
PRIMARY KEY (AIRPLANE_TYPE_NAME, AIRPORT_CODE),
FOREIGN KEY (AIRPLANE_TYPE_NAME) REFERENCES
AIRPLANE_TYPE (TYPE_NAME),
FOREIGN KEY (AIRPORT_CODE) REFERENCES
AIRPORT (AIRPORT_CODE) );
CREATE TABLE AIRPLANE ( AIRPLANE_ID INTEGER NOT NULL,
TOTAL_NUMBER_OF_SEATS INTEGER NOT NULL,
AIRPLANE_TYPE VARCHAR(20) NOT NULL,
PRIMARY KEY (AIRPLANE_ID),
FOREIGN KEY (AIRPLANE_TYPE) REFERENCES AIRPLANE_TYPE (TYPE_NAME) );
CREATE TABLE SEAT_RESERVATION ( FLIGHT_NUMBER VARCHAR(6) NOT NULL,
LEG_NUMBER INTEGER NOT NULL,
LEG_DATE DATE NOT NULL,
SEAT_NUMBER VARCHAR(4),
CUSTOMER_NAME VARCHAR(30) NOT NULL,
CUSTOMER_PHONE CHAR(12),
PRIMARY KEY (FLIGHT_NUMBER, LEG_NUMBER, LEG_DATE, SEAT_NUMBER),
FOREIGN KEY (FLIGHT_NUMBER, LEG_NUMBER, LEG_DATE) REFERENCES
LEG_INSTANCE (FLIGHT_NUMBER, LEG_NUMBER, LEG_DATE) );
4.7 - Consider the LIBRARY relational database schema of Figure 4.6. Choose the
appropriate action (reject, cascade, set to null, set to default) for each referential integrity
constraint, both for the deletion of a referenced tuple, and for the update of a primary key
attribute value in a referenced tuple. Justify your choices.
Answer:
Below are possible choices. In general, if it is not clear which action to choose, REJECT
should be chosen, since it will not permit automatic changes to happen (by update
propagation) that may be unintended.
BOOK_AUTHORS.(BookId) --> BOOK.(BookId)
CASCADE on both DELETE or UPDATE (since this corresponds to a multi-valued attribute
of BOOK (see the solution to Exercise 6.27); hence, if a BOOK is deleted, or the value of
its BookId is updated (changed), the deletion or change is automatically propagated to the
referencing BOOK_AUTHORS tuples)
BOOK.(PublisherName) --> PUBLISHER.(Name)
REJECT on DELETE (we should not delete a PUBLISHER tuple which has existing BOOK
tuples that reference the PUBLISHER)
CASCADE on UPDATE (if a PUBLISHER's Name is updated, the change should be
propagated automatically to all referencing BOOK tuples)
BOOK_LOANS.(BookId) --> BOOK.(BookId)
CASCADE on both DELETE or UPDATE (if a BOOK is deleted, or the value of its BookId is
updated (changed), the deletion or change is automatically propagated to the referencing
BOOK_LOANS tuples) (Note: One could also choose REJECT on DELETE)
BOOK_COPIES.(BookId) --> BOOK.(BookId)
CASCADE on both DELETE or UPDATE (if a BOOK is deleted, or the value of its BookId is
updated (changed), the deletion or change is automatically propagated to the referencing
AMOUNT DECIMAL(8,2) NOT NULL,
RESTRICTIONS VARCHAR(200),
PRIMARY KEY (FLIGHT_NUMBER, FARE_CODE),
FOREIGN KEY (FLIGHT_NUMBER) REFERENCES FLIGHT (NUMBER) );
CREATE TABLE AIRPLANE_TYPE ( TYPE_NAME VARCHAR(20) NOT NULL,
MAX_SEATS INTEGER NOT NULL,
COMPANY VARCHAR(15) NOT NULL,
PRIMARY KEY (TYPE_NAME) );
CREATE TABLE CAN_LAND ( AIRPLANE_TYPE_NAME VARCHAR(20) NOT NULL,
AIRPORT_CODE CHAR(3) NOT NULL,
PRIMARY KEY (AIRPLANE_TYPE_NAME, AIRPORT_CODE),
FOREIGN KEY (AIRPLANE_TYPE_NAME) REFERENCES
AIRPLANE_TYPE (TYPE_NAME),
FOREIGN KEY (AIRPORT_CODE) REFERENCES
AIRPORT (AIRPORT_CODE) );
CREATE TABLE AIRPLANE ( AIRPLANE_ID INTEGER NOT NULL,
TOTAL_NUMBER_OF_SEATS INTEGER NOT NULL,
AIRPLANE_TYPE VARCHAR(20) NOT NULL,
PRIMARY KEY (AIRPLANE_ID),
FOREIGN KEY (AIRPLANE_TYPE) REFERENCES AIRPLANE_TYPE (TYPE_NAME) );
CREATE TABLE SEAT_RESERVATION ( FLIGHT_NUMBER VARCHAR(6) NOT NULL,
LEG_NUMBER INTEGER NOT NULL,
LEG_DATE DATE NOT NULL,
SEAT_NUMBER VARCHAR(4),
CUSTOMER_NAME VARCHAR(30) NOT NULL,
CUSTOMER_PHONE CHAR(12),
PRIMARY KEY (FLIGHT_NUMBER, LEG_NUMBER, LEG_DATE, SEAT_NUMBER),
FOREIGN KEY (FLIGHT_NUMBER, LEG_NUMBER, LEG_DATE) REFERENCES
LEG_INSTANCE (FLIGHT_NUMBER, LEG_NUMBER, LEG_DATE) );
4.7 - Consider the LIBRARY relational database schema of Figure 4.6. Choose the
appropriate action (reject, cascade, set to null, set to default) for each referential integrity
constraint, both for the deletion of a referenced tuple, and for the update of a primary key
attribute value in a referenced tuple. Justify your choices.
Answer:
Below are possible choices. In general, if it is not clear which action to choose, REJECT
should be chosen, since it will not permit automatic changes to happen (by update
propagation) that may be unintended.
BOOK_AUTHORS.(BookId) --> BOOK.(BookId)
CASCADE on both DELETE or UPDATE (since this corresponds to a multi-valued attribute
of BOOK (see the solution to Exercise 6.27); hence, if a BOOK is deleted, or the value of
its BookId is updated (changed), the deletion or change is automatically propagated to the
referencing BOOK_AUTHORS tuples)
BOOK.(PublisherName) --> PUBLISHER.(Name)
REJECT on DELETE (we should not delete a PUBLISHER tuple which has existing BOOK
tuples that reference the PUBLISHER)
CASCADE on UPDATE (if a PUBLISHER's Name is updated, the change should be
propagated automatically to all referencing BOOK tuples)
BOOK_LOANS.(BookId) --> BOOK.(BookId)
CASCADE on both DELETE or UPDATE (if a BOOK is deleted, or the value of its BookId is
updated (changed), the deletion or change is automatically propagated to the referencing
BOOK_LOANS tuples) (Note: One could also choose REJECT on DELETE)
BOOK_COPIES.(BookId) --> BOOK.(BookId)
CASCADE on both DELETE or UPDATE (if a BOOK is deleted, or the value of its BookId is
updated (changed), the deletion or change is automatically propagated to the referencing
Loading page 17...
Chapter 4: Basic SQL4
BOOK_COPIES tuples)
BOOK_LOANS.(CardNo) --> BORROWER.(CardNo)
CASCADE on both DELETE or UPDATE (if a BORROWER tuple is deleted, or the value of
its CardNo is updated (changed), the deletion or change is automatically propagated to the
referencing BOOK_LOANS tuples) (Note: One could also choose REJECT on DELETE, with
the idea that if a BORROWER is deleted, it is necessary first to make a printout of all
BOOK_LOANS outstanding before deleting the BORROWER; in this case, the tuples in
BOOK_LOANS that reference the BORROWER being deleted would first be explicitly
deleted after making the printout, and before the BORROWER is deleted)
BOOK_COPIES.(BranchId) --> LIBRARY_BRANCH.(BranchId)
CASCADE on both DELETE or UPDATE (if a LIBRARY_BRANCH is deleted, or the value of
its BranchId is updated (changed), the deletion or change is automatically propagated to
the referencing BOOK_COPIES tuples) (Note: One could also choose REJECT on DELETE)
BOOK_LOANS.(BranchId) --> LIBRARY_BRANCH.(BranchId)
CASCADE on both DELETE or UPDATE (if a LIBRARY_BRANCH is deleted, or the value of
its BranchId is updated (changed), the deletion or change is automatically
propagated to the referencing BOOK_LOANS tuples) (Note: One could also choose
REJECT on DELETE)
4.8 - Write appropriate SQL DDL statements for declaring the LIBRARY relational database
schema of Figure 4.6. Specify the keys and referential triggered actions.
Answer:
One possible set of CREATE TABLE statements is given below:
CREATE TABLE BOOK ( BookId CHAR(20) NOT NULL,
Title VARCHAR(30) NOT NULL,
PublisherName VARCHAR(20),
PRIMARY KEY (BookId),
FOREIGN KEY (PublisherName) REFERENCES PUBLISHER (Name) ON UPDATE
CASCADE );
CREATE TABLE BOOK_AUTHORS ( BookId CHAR(20) NOT NULL,
AuthorName VARCHAR(30) NOT NULL,
PRIMARY KEY (BookId, AuthorName),
FOREIGN KEY (BookId) REFERENCES BOOK (BookId)
ON DELETE CASCADE ON UPDATE CASCADE );
CREATE TABLE PUBLISHER ( Name VARCHAR(20) NOT NULL,
Address VARCHAR(40) NOT NULL,
Phone CHAR(12),
PRIMARY KEY (Name) );
CREATE TABLE BOOK_COPIES ( BookId CHAR(20) NOT NULL,
BranchId INTEGER NOT NULL,
No_Of_Copies INTEGER NOT NULL,
PRIMARY KEY (BookId, BranchId),
FOREIGN KEY (BookId) REFERENCES BOOK (BookId)
ON DELETE CASCADE ON UPDATE CASCADE,
FOREIGN KEY (BranchId) REFERENCES BRANCH (BranchId)
ON DELETE CASCADE ON UPDATE CASCADE );
CREATE TABLE BORROWER ( CardNo INTEGER NOT NULL,
Name VARCHAR(30) NOT NULL,
Address VARCHAR(40) NOT NULL,
Phone CHAR(12),
PRIMARY KEY (CardNo) );
CREATE TABLE BOOK_LOANS ( CardNo INTEGER NOT NULL,
BOOK_COPIES tuples)
BOOK_LOANS.(CardNo) --> BORROWER.(CardNo)
CASCADE on both DELETE or UPDATE (if a BORROWER tuple is deleted, or the value of
its CardNo is updated (changed), the deletion or change is automatically propagated to the
referencing BOOK_LOANS tuples) (Note: One could also choose REJECT on DELETE, with
the idea that if a BORROWER is deleted, it is necessary first to make a printout of all
BOOK_LOANS outstanding before deleting the BORROWER; in this case, the tuples in
BOOK_LOANS that reference the BORROWER being deleted would first be explicitly
deleted after making the printout, and before the BORROWER is deleted)
BOOK_COPIES.(BranchId) --> LIBRARY_BRANCH.(BranchId)
CASCADE on both DELETE or UPDATE (if a LIBRARY_BRANCH is deleted, or the value of
its BranchId is updated (changed), the deletion or change is automatically propagated to
the referencing BOOK_COPIES tuples) (Note: One could also choose REJECT on DELETE)
BOOK_LOANS.(BranchId) --> LIBRARY_BRANCH.(BranchId)
CASCADE on both DELETE or UPDATE (if a LIBRARY_BRANCH is deleted, or the value of
its BranchId is updated (changed), the deletion or change is automatically
propagated to the referencing BOOK_LOANS tuples) (Note: One could also choose
REJECT on DELETE)
4.8 - Write appropriate SQL DDL statements for declaring the LIBRARY relational database
schema of Figure 4.6. Specify the keys and referential triggered actions.
Answer:
One possible set of CREATE TABLE statements is given below:
CREATE TABLE BOOK ( BookId CHAR(20) NOT NULL,
Title VARCHAR(30) NOT NULL,
PublisherName VARCHAR(20),
PRIMARY KEY (BookId),
FOREIGN KEY (PublisherName) REFERENCES PUBLISHER (Name) ON UPDATE
CASCADE );
CREATE TABLE BOOK_AUTHORS ( BookId CHAR(20) NOT NULL,
AuthorName VARCHAR(30) NOT NULL,
PRIMARY KEY (BookId, AuthorName),
FOREIGN KEY (BookId) REFERENCES BOOK (BookId)
ON DELETE CASCADE ON UPDATE CASCADE );
CREATE TABLE PUBLISHER ( Name VARCHAR(20) NOT NULL,
Address VARCHAR(40) NOT NULL,
Phone CHAR(12),
PRIMARY KEY (Name) );
CREATE TABLE BOOK_COPIES ( BookId CHAR(20) NOT NULL,
BranchId INTEGER NOT NULL,
No_Of_Copies INTEGER NOT NULL,
PRIMARY KEY (BookId, BranchId),
FOREIGN KEY (BookId) REFERENCES BOOK (BookId)
ON DELETE CASCADE ON UPDATE CASCADE,
FOREIGN KEY (BranchId) REFERENCES BRANCH (BranchId)
ON DELETE CASCADE ON UPDATE CASCADE );
CREATE TABLE BORROWER ( CardNo INTEGER NOT NULL,
Name VARCHAR(30) NOT NULL,
Address VARCHAR(40) NOT NULL,
Phone CHAR(12),
PRIMARY KEY (CardNo) );
CREATE TABLE BOOK_LOANS ( CardNo INTEGER NOT NULL,
Loading page 18...
Chapter 4: Basic SQL 5
BookId CHAR(20) NOT NULL,
BranchId INTEGER NOT NULL,
DateOut DATE NOT NULL,
DueDate DATE NOT NULL,
PRIMARY KEY (CardNo, BookId, BranchId),
FOREIGN KEY (CardNo) REFERENCES BORROWER (CardNo)
ON DELETE CASCADE ON UPDATE CASCADE,
FOREIGN KEY (BranchId) REFERENCES LIBRARY_BRANCH (BranchId)
ON DELETE CASCADE ON UPDATE CASCADE,
FOREIGN KEY (BookId) REFERENCES BOOK (BookId)
ON DELETE CASCADE ON UPDATE CASCADE );
CREATE TABLE LIBRARY_BRANCH ( BranchId INTEGER NOT NULL,
BranchName VARCHAR(20) NOT NULL,
Address VARCHAR(40) NOT NULL,
PRIMARY KEY (BranchId) );
4.9 - How can the key and foreign key constraints be enforced by the DBMS? Is the
enforcement technique you suggest difficult to implement? Can the constraint checks be
executed in an efficient manner when updates are applied to the database?
Answer:
One possible technique that is often used to check efficiently for the key constraint
is to create an index on the combination of attributes that form each key (primary or
secondary). Before inserting a new record (tuple), each index is searched to check that
no value currently exists in the index that matches the key value in the new record. If
this is the case, the record is inserted successfully.
For checking the foreign key constraint, an index on the primary key of each
referenced relation will make this check relatively efficient. Whenever a new record is
inserted in a referencing relation , its foreign key value is used to search the index for
the primary key of the referenced relation, and if the referenced record exists, then the
new record can be successfully inserted in the referencing relation.
For deletion of a referenced record, it is useful to have an index on the foreign key
of each referencing relation so as to be able to determine efficiently whether any records
reference the record being deleted.
If the indexes described above do not exist, and no alternative access structure (for
example, hashing) is used in their place, then it is necessary to do linear searches to
check for any of the above constraints, making the checks quite inefficient.
4.10 – No solution provided.
4.11 - Specify the updates of Exercise 3.11 using the SQL update commands.
Answers:
Below, we show how each of the updates may be specified in SQL. Notice that some of
these updates violate integrity constraints as discussed in the solution to Exercise 5.10,
and hence should be rejected if executed on the database of Figure 5.6.
(a) Insert < 'Robert', 'F', 'Scott', '943775543', '21-JUN-42', '2365 Newcastle Rd,
Bellaire, TX', M, 58000, '888665555', 1 > into EMPLOYEE.
INSERT INTO EMPLOYEE
VALUES ('Robert', 'F', 'Scott', '943775543', '21-JUN-42', '2365 Newcastle Rd, Bellaire, TX',
M, 58000, '888665555', 1)
BookId CHAR(20) NOT NULL,
BranchId INTEGER NOT NULL,
DateOut DATE NOT NULL,
DueDate DATE NOT NULL,
PRIMARY KEY (CardNo, BookId, BranchId),
FOREIGN KEY (CardNo) REFERENCES BORROWER (CardNo)
ON DELETE CASCADE ON UPDATE CASCADE,
FOREIGN KEY (BranchId) REFERENCES LIBRARY_BRANCH (BranchId)
ON DELETE CASCADE ON UPDATE CASCADE,
FOREIGN KEY (BookId) REFERENCES BOOK (BookId)
ON DELETE CASCADE ON UPDATE CASCADE );
CREATE TABLE LIBRARY_BRANCH ( BranchId INTEGER NOT NULL,
BranchName VARCHAR(20) NOT NULL,
Address VARCHAR(40) NOT NULL,
PRIMARY KEY (BranchId) );
4.9 - How can the key and foreign key constraints be enforced by the DBMS? Is the
enforcement technique you suggest difficult to implement? Can the constraint checks be
executed in an efficient manner when updates are applied to the database?
Answer:
One possible technique that is often used to check efficiently for the key constraint
is to create an index on the combination of attributes that form each key (primary or
secondary). Before inserting a new record (tuple), each index is searched to check that
no value currently exists in the index that matches the key value in the new record. If
this is the case, the record is inserted successfully.
For checking the foreign key constraint, an index on the primary key of each
referenced relation will make this check relatively efficient. Whenever a new record is
inserted in a referencing relation , its foreign key value is used to search the index for
the primary key of the referenced relation, and if the referenced record exists, then the
new record can be successfully inserted in the referencing relation.
For deletion of a referenced record, it is useful to have an index on the foreign key
of each referencing relation so as to be able to determine efficiently whether any records
reference the record being deleted.
If the indexes described above do not exist, and no alternative access structure (for
example, hashing) is used in their place, then it is necessary to do linear searches to
check for any of the above constraints, making the checks quite inefficient.
4.10 – No solution provided.
4.11 - Specify the updates of Exercise 3.11 using the SQL update commands.
Answers:
Below, we show how each of the updates may be specified in SQL. Notice that some of
these updates violate integrity constraints as discussed in the solution to Exercise 5.10,
and hence should be rejected if executed on the database of Figure 5.6.
(a) Insert < 'Robert', 'F', 'Scott', '943775543', '21-JUN-42', '2365 Newcastle Rd,
Bellaire, TX', M, 58000, '888665555', 1 > into EMPLOYEE.
INSERT INTO EMPLOYEE
VALUES ('Robert', 'F', 'Scott', '943775543', '21-JUN-42', '2365 Newcastle Rd, Bellaire, TX',
M, 58000, '888665555', 1)
Loading page 19...
Chapter 4: Basic SQL6
(b) Insert < 'ProductA', 4, 'Bellaire', 2 > into PROJECT.
INSERT INTO PROJECT
VALUES ('ProductA', 4, 'Bellaire', 2)
(c) Insert < 'Production', 4, '943775543', '01-OCT-88' > into DEPARTMENT.
INSERT INTO DEPARTMENT
VALUES ('Production', 4, '943775543', '01-OCT-88')
(d) Insert < '677678989', null, '40.0' > into WORKS_ON.
INSERT INTO WORKS_ON
VALUES ('677678989', NULL, '40.0')
(e) Insert < '453453453', 'John', M, '12-DEC-60', 'SPOUSE' > into DEPENDENT.
INSERT INTO DEPENDENT
VALUES ('453453453', 'John', M, '12-DEC-60', 'SPOUSE')
(f) Delete the WORKS_ON tuples with ESSN= '333445555'.
DELETE FROM WORKS_ON
WHERE ESSN= '333445555'
(g) Delete the EMPLOYEE tuple with SSN= '987654321'.
DELETE FROM EMPLOYEE
WHERE SSN= '987654321'
(h) Delete the PROJECT tuple with PNAME= 'ProductX'.
DELETE FROM PROJECT
WHERE PNAME= 'ProductX'
(i) Modify the MGRSSN and MGRSTARTDATE of the DEPARTMENT tuple with
DNUMBER=
5 to '123456789' and '01-OCT-88', respectively.
UPDATE DEPARTMENT
SET MGRSSN = '123456789', MGRSTARTDATE = '01-OCT-88'
WHERE DNUMBER= 5
(j) Modify the SUPERSSN attribute of the EMPLOYEE tuple with SSN= '999887777' to
'943775543'.
UPDATE EMPLOYEE
SET SUPERSSN = '943775543'
WHERE SSN= '999887777'
(k) Modify the HOURS attribute of the WORKS_ON tuple with ESSN= '999887777' and
PNO= 10 to '5.0'.
UPDATE WORKS_ON
SET HOURS = '5.0'
WHERE ESSN= '999887777' AND PNO= 10
4.12 - Specify the following queries in SQL on the database schema of Figure 1.2.
(a) Retrieve the names of all senior students majoring in 'COSC' (computer science).
(b) Retrieve the names of all courses taught by professor King in 85 and 86.
(c) For each section taught by professor King, retrieve the course number, semester,
year, and number of students who took the section.
(b) Insert < 'ProductA', 4, 'Bellaire', 2 > into PROJECT.
INSERT INTO PROJECT
VALUES ('ProductA', 4, 'Bellaire', 2)
(c) Insert < 'Production', 4, '943775543', '01-OCT-88' > into DEPARTMENT.
INSERT INTO DEPARTMENT
VALUES ('Production', 4, '943775543', '01-OCT-88')
(d) Insert < '677678989', null, '40.0' > into WORKS_ON.
INSERT INTO WORKS_ON
VALUES ('677678989', NULL, '40.0')
(e) Insert < '453453453', 'John', M, '12-DEC-60', 'SPOUSE' > into DEPENDENT.
INSERT INTO DEPENDENT
VALUES ('453453453', 'John', M, '12-DEC-60', 'SPOUSE')
(f) Delete the WORKS_ON tuples with ESSN= '333445555'.
DELETE FROM WORKS_ON
WHERE ESSN= '333445555'
(g) Delete the EMPLOYEE tuple with SSN= '987654321'.
DELETE FROM EMPLOYEE
WHERE SSN= '987654321'
(h) Delete the PROJECT tuple with PNAME= 'ProductX'.
DELETE FROM PROJECT
WHERE PNAME= 'ProductX'
(i) Modify the MGRSSN and MGRSTARTDATE of the DEPARTMENT tuple with
DNUMBER=
5 to '123456789' and '01-OCT-88', respectively.
UPDATE DEPARTMENT
SET MGRSSN = '123456789', MGRSTARTDATE = '01-OCT-88'
WHERE DNUMBER= 5
(j) Modify the SUPERSSN attribute of the EMPLOYEE tuple with SSN= '999887777' to
'943775543'.
UPDATE EMPLOYEE
SET SUPERSSN = '943775543'
WHERE SSN= '999887777'
(k) Modify the HOURS attribute of the WORKS_ON tuple with ESSN= '999887777' and
PNO= 10 to '5.0'.
UPDATE WORKS_ON
SET HOURS = '5.0'
WHERE ESSN= '999887777' AND PNO= 10
4.12 - Specify the following queries in SQL on the database schema of Figure 1.2.
(a) Retrieve the names of all senior students majoring in 'COSC' (computer science).
(b) Retrieve the names of all courses taught by professor King in 85 and 86.
(c) For each section taught by professor King, retrieve the course number, semester,
year, and number of students who took the section.
Loading page 20...
Chapter 4: Basic SQL 7
(d) Retrieve the name and transcript of each senior student (Class=5) majoring in
COSC. Transcript includes course name, course number, credit hours, semester, year,
and grade for each course completed by the student.
(e) Retrieve the names and major departments of all straight A students (students who
have a grade of A in all their courses).
(f) Retrieve the names and major departments of all students who do not have any grade
of A in any of their courses.
Answers:
(a) SELECT Name
FROM STUDENT
WHERE Major='COSC'
(b) SELECT CourseName
FROM COURSE, SECTION
WHERE COURSE.CourseNumber=SECTION.CourseNumber AND Instructor='King'
AND (Year='85' OR Year='86')
Another possible SQL query uses nesting as follows:
SELECT CourseName
FROM COURSE
WHERE CourseNumber IN ( SELECT CourseNumber
FROM SECTION
WHERE Instructor='King' AND (Year='85' OR Year='86') )
(c) SELECT CourseNumber, Semester, Year, COUNT(*)
FROM SECTION, GRADE_REPORT
WHERE Instructor='King' AND SECTION.SectionIdentifier=GRADE_REPORT.SectionIdentifier
GROUP BY CourseNumber, Semester, Year
(d) SELECT Name, CourseName, C.CourseNumber, CreditHours, Semester, Year, Grade
FROM STUDENT ST, COURSE C, SECTION S, GRADE_REPORT G
WHERE Class=5 AND Major='COSC' AND ST.StudentNumber=G.StudentNumber AND
G.SectionIdentifier=S.SectionIdentifier AND S.CourseNumber=C.CourseNumber
(e) SELECT Name, Major
FROM STUDENT
WHERE NOT EXISTS ( SELECT *
FROM GRADE_REPORT
WHERE StudentNumber= STUDENT.StudentNumber AND NOT(Grade='A'))
(f) SELECT Name, Major
FROM STUDENT
WHERE NOT EXISTS ( SELECT *
FROM GRADE_REPORT
WHERE StudentNumber= STUDENT.StudentNumber AND Grade='A' )
4.13 - Write SQL update statements to do the following on the database schema shown in
Figure 1.2.
(a) Insert a new student <'Johnson', 25, 1, 'MATH'> in the database.
(b) Change the class of student 'Smith' to 2.
(d) Retrieve the name and transcript of each senior student (Class=5) majoring in
COSC. Transcript includes course name, course number, credit hours, semester, year,
and grade for each course completed by the student.
(e) Retrieve the names and major departments of all straight A students (students who
have a grade of A in all their courses).
(f) Retrieve the names and major departments of all students who do not have any grade
of A in any of their courses.
Answers:
(a) SELECT Name
FROM STUDENT
WHERE Major='COSC'
(b) SELECT CourseName
FROM COURSE, SECTION
WHERE COURSE.CourseNumber=SECTION.CourseNumber AND Instructor='King'
AND (Year='85' OR Year='86')
Another possible SQL query uses nesting as follows:
SELECT CourseName
FROM COURSE
WHERE CourseNumber IN ( SELECT CourseNumber
FROM SECTION
WHERE Instructor='King' AND (Year='85' OR Year='86') )
(c) SELECT CourseNumber, Semester, Year, COUNT(*)
FROM SECTION, GRADE_REPORT
WHERE Instructor='King' AND SECTION.SectionIdentifier=GRADE_REPORT.SectionIdentifier
GROUP BY CourseNumber, Semester, Year
(d) SELECT Name, CourseName, C.CourseNumber, CreditHours, Semester, Year, Grade
FROM STUDENT ST, COURSE C, SECTION S, GRADE_REPORT G
WHERE Class=5 AND Major='COSC' AND ST.StudentNumber=G.StudentNumber AND
G.SectionIdentifier=S.SectionIdentifier AND S.CourseNumber=C.CourseNumber
(e) SELECT Name, Major
FROM STUDENT
WHERE NOT EXISTS ( SELECT *
FROM GRADE_REPORT
WHERE StudentNumber= STUDENT.StudentNumber AND NOT(Grade='A'))
(f) SELECT Name, Major
FROM STUDENT
WHERE NOT EXISTS ( SELECT *
FROM GRADE_REPORT
WHERE StudentNumber= STUDENT.StudentNumber AND Grade='A' )
4.13 - Write SQL update statements to do the following on the database schema shown in
Figure 1.2.
(a) Insert a new student <'Johnson', 25, 1, 'MATH'> in the database.
(b) Change the class of student 'Smith' to 2.
Loading page 21...
Chapter 4: Basic SQL8
(c) Insert a new course <'Knowledge Engineering','COSC4390', 3,'COSC'>.
(d) Delete the record for the student whose name is 'Smith' and student number is 17.
Answers:
(a) INSERT INTO STUDENT
VALUES ('Johnson', 25, 1, 'MATH')
(b) UPDATE STUDENT
SET CLASS = 2
WHERE Name='Smith'
(c) INSERT INTO COURSE
VALUES ('Knowledge Engineering','COSC4390', 3,'COSC')
(d) DELETE FROM STUDENT
WHERE Name='Smith' AND StudentNumber=17
4.14 – No solution provided
4.15 - Consider the EMPLOYEE table’s constraint EMPSUPERFK as specified in Figure 4.2
is changed to read as follows:
CONSTRAINT EMPSUPERFK
FOREIGN KEY (SUPERSSN) REFERNCES EMPLOYEE(SSN)
ON DELETE CASCADE ON UPDATE CASCADE,
Answer the following questions:
a.a. What happens when the following command is run on the database state shown in
Figure 5.6?
DELETE EMPLOYEE WHERE LNAME = ‘Borg’
b.b. Is it better to CASCADE or SET NULL in case of EMPSUPERFK constraint ON
DELETE?
Answers:
a) The James E. Borg entry is deleted from the table, and each employee with him as a
supervisor is also (and their supervisees, and so on). In total, 8 rows are deleted and the
table is empty.
b) It is better to SET NULL, since an employee is not fired (DELETED) when their
supervisor is deleted. Instead, their SUPERSSN should be SET NULL so that they can later
get a new supervisor.
4.16 - Write SQL statements to create a table EMPLOYEE_BACKUP backup of EMPLOYEE
table shown in Figure 3.6.
Answer:
INSERT INTO EMPLOYEE_BACKUP VALUES ( SELECT * FROM EMPLOYEE )
Formatted: Bullets and Numbering
Formatted: Bullets and Numbering
(c) Insert a new course <'Knowledge Engineering','COSC4390', 3,'COSC'>.
(d) Delete the record for the student whose name is 'Smith' and student number is 17.
Answers:
(a) INSERT INTO STUDENT
VALUES ('Johnson', 25, 1, 'MATH')
(b) UPDATE STUDENT
SET CLASS = 2
WHERE Name='Smith'
(c) INSERT INTO COURSE
VALUES ('Knowledge Engineering','COSC4390', 3,'COSC')
(d) DELETE FROM STUDENT
WHERE Name='Smith' AND StudentNumber=17
4.14 – No solution provided
4.15 - Consider the EMPLOYEE table’s constraint EMPSUPERFK as specified in Figure 4.2
is changed to read as follows:
CONSTRAINT EMPSUPERFK
FOREIGN KEY (SUPERSSN) REFERNCES EMPLOYEE(SSN)
ON DELETE CASCADE ON UPDATE CASCADE,
Answer the following questions:
a.a. What happens when the following command is run on the database state shown in
Figure 5.6?
DELETE EMPLOYEE WHERE LNAME = ‘Borg’
b.b. Is it better to CASCADE or SET NULL in case of EMPSUPERFK constraint ON
DELETE?
Answers:
a) The James E. Borg entry is deleted from the table, and each employee with him as a
supervisor is also (and their supervisees, and so on). In total, 8 rows are deleted and the
table is empty.
b) It is better to SET NULL, since an employee is not fired (DELETED) when their
supervisor is deleted. Instead, their SUPERSSN should be SET NULL so that they can later
get a new supervisor.
4.16 - Write SQL statements to create a table EMPLOYEE_BACKUP backup of EMPLOYEE
table shown in Figure 3.6.
Answer:
INSERT INTO EMPLOYEE_BACKUP VALUES ( SELECT * FROM EMPLOYEE )
Formatted: Bullets and Numbering
Formatted: Bullets and Numbering
Loading page 22...
Chapter 5: More SQL: Complex Queries, Triggers, Views, and Schema Modification 1
CHAPTER 5: More SQL: Complex Queries, Triggers, Views, and Schema
Modification
Answers to Selected Exercises
5.5 - Specify the following additional queries on the database of Figure 3.5 in SQL. Show
the query results if applied to the database of Figure 3.6.
(a) For each department whose average employee salary is more than $30,000, retrieve
the department name and the number of employees working for that department.
(b) Suppose we want the number of male employees in each department rather than all
employees (as in Exercise 5.4a). Can we specify this query in SQL? Why or why not?
Answers:
(a) SELECT DNAME, COUNT (*)
FROM DEPARTMENT, EMPLOYEE
WHERE DNUMBER=DNO
GROUP BY DNAME
HAVING AVG (SALARY) > 30000
Result:
DNAME DNUMBER COUNT(*)
Research 5 4
Administration 4 3
Headquarters 1 1
(b) The query may still be specified in SQL by using a nested query as follows (not all
implementations may support this type of query):
SELECT DNAME, COUNT (*)
FROM DEPARTMENT, EMPLOYEE
WHERE DNUMBER=DNO AND SEX='M' AND DNO IN ( SELECT DNO
FROM EMPLOYEE
GROUP BY DNO
HAVING AVG (SALARY) > 30000 )
GROUP BY DNAME
Result:
DNAME DNUMBER COUNT(*)
Research 5 3
Administration 4 1
Headquarters 1 1
5.6 - Specify the following queries in SQL on the database schema of Figure 1.2.
(a) Retrieve the names and major departments of all straight-A students (students who
have a grade of A in all their courses).
(b) Retrieve the names and major departments of all students who do not have any
grade of A in any of their courses.
CHAPTER 5: More SQL: Complex Queries, Triggers, Views, and Schema
Modification
Answers to Selected Exercises
5.5 - Specify the following additional queries on the database of Figure 3.5 in SQL. Show
the query results if applied to the database of Figure 3.6.
(a) For each department whose average employee salary is more than $30,000, retrieve
the department name and the number of employees working for that department.
(b) Suppose we want the number of male employees in each department rather than all
employees (as in Exercise 5.4a). Can we specify this query in SQL? Why or why not?
Answers:
(a) SELECT DNAME, COUNT (*)
FROM DEPARTMENT, EMPLOYEE
WHERE DNUMBER=DNO
GROUP BY DNAME
HAVING AVG (SALARY) > 30000
Result:
DNAME DNUMBER COUNT(*)
Research 5 4
Administration 4 3
Headquarters 1 1
(b) The query may still be specified in SQL by using a nested query as follows (not all
implementations may support this type of query):
SELECT DNAME, COUNT (*)
FROM DEPARTMENT, EMPLOYEE
WHERE DNUMBER=DNO AND SEX='M' AND DNO IN ( SELECT DNO
FROM EMPLOYEE
GROUP BY DNO
HAVING AVG (SALARY) > 30000 )
GROUP BY DNAME
Result:
DNAME DNUMBER COUNT(*)
Research 5 3
Administration 4 1
Headquarters 1 1
5.6 - Specify the following queries in SQL on the database schema of Figure 1.2.
(a) Retrieve the names and major departments of all straight-A students (students who
have a grade of A in all their courses).
(b) Retrieve the names and major departments of all students who do not have any
grade of A in any of their courses.
Loading page 23...
Chapter 5: More SQL: Complex Queries, Triggers, Views, and Schema Modification2
Answers:
(a) SELECT Name, Major
FROM STUDENT
WHERE NOT EXISTS ( SELECT *
FROM GRADE_REPORT
WHERE StudentNumber= STUDENT.StudentNumber AND NOT(Grade='A'))
(b) SELECT Name, Major
FROM STUDENT
WHERE NOT EXISTS ( SELECT *
FROM GRADE_REPORT
WHERE StudentNumber= STUDENT.StudentNumber AND Grade='A' )
5.7 - In SQL, specify the following queries on the database specified in Figure 3.5 using the
concept of nested queries and the concepts described in this chapter.
a. Retrieve the names of all employees who work in the department that has the
employee with the highest salary among all employees.
b. Retrieve the names of all employees whose supervisor’s supervisor has '888665555'
for Ssn.
c. Retrieve the names of employees who make at least $10,000 more than the employee
who is paid the least in the company.
Answers:
a) SELECT LNAME FROM EMPLOYEE WHERE DNO =
( SELECT DNO FROM EMPLOYEE WHERE SALARY =
( SELECT MAX(SALARY) FROM EMPLOYEE) )
b) SELECT LNAME FROM EMPLOYEE WHERE SUPERSSN IN
( SELECT SSN FROM EMPLOYEE WHERE SUPERSSN = ‘888665555’ )
c) SELECT LNAME FROM EMPLOYEE WHERE SALARY >= 10000 +
( SELECT MIN(SALARY) FROM EMPLOYEE)
5.8 – No solution provided.
5.9 – No solution provided.
Answers:
(a) SELECT Name, Major
FROM STUDENT
WHERE NOT EXISTS ( SELECT *
FROM GRADE_REPORT
WHERE StudentNumber= STUDENT.StudentNumber AND NOT(Grade='A'))
(b) SELECT Name, Major
FROM STUDENT
WHERE NOT EXISTS ( SELECT *
FROM GRADE_REPORT
WHERE StudentNumber= STUDENT.StudentNumber AND Grade='A' )
5.7 - In SQL, specify the following queries on the database specified in Figure 3.5 using the
concept of nested queries and the concepts described in this chapter.
a. Retrieve the names of all employees who work in the department that has the
employee with the highest salary among all employees.
b. Retrieve the names of all employees whose supervisor’s supervisor has '888665555'
for Ssn.
c. Retrieve the names of employees who make at least $10,000 more than the employee
who is paid the least in the company.
Answers:
a) SELECT LNAME FROM EMPLOYEE WHERE DNO =
( SELECT DNO FROM EMPLOYEE WHERE SALARY =
( SELECT MAX(SALARY) FROM EMPLOYEE) )
b) SELECT LNAME FROM EMPLOYEE WHERE SUPERSSN IN
( SELECT SSN FROM EMPLOYEE WHERE SUPERSSN = ‘888665555’ )
c) SELECT LNAME FROM EMPLOYEE WHERE SALARY >= 10000 +
( SELECT MIN(SALARY) FROM EMPLOYEE)
5.8 – No solution provided.
5.9 – No solution provided.
Loading page 24...
Chapter 6: The Relational Algebra and Relational Calculus 1
CHAPTER 6: THE RELATIONAL ALGEBRA AND RELATIONAL CALCULUS
Answers to Selected Exercises
6.15 - Show the result of each of the sample queries in Section 6.5 as it would apply to the
database state in Figure 3.6.
Answer:
(QUERY 1) Find the name and address of all employees who work for the 'Research'
department.
Result: FNAME LNAME ADDRESS
John Smith 731 Fondren, Houston, TX
Franklin Wong 638 Voss, Houston, TX
Ramesh Narayan 975 Fire Oak, Humble, TX
Joyce English 5631 Rice, Houston, TX
(QUERY 2) For every project located in 'Stafford', list the project number, the
controlling department number, and the department manager's last name, address, and
birth date.
Result:
PNUMBER DNUM LNAME ADDRESS BDATE
10 4 Wallace 291 Berry, Bellaire, TX 20-JUN-31
30 4 Wallace 291 Berry, Bellaire, TX 20-JUN-31
(QUERY 3) Find the names of all employees who work on all the projects controlled by
department number 5.
Result: (empty because no tuples satisfy the result).
LNAME FNAME
(QUERY 4) Make a list of project numbers for projects that involve an employee whose
last name is 'Smith' as a worker or as a manager of the department that controls the
project.
Result: PNO
1
2
(QUERY 5) List the names of all employees with two or more dependents.
Result: LNAME FNAME
Smith John
Wong Franklin
(QUERY 6) List the names of employees who have no dependents.
Result: LNAME FNAME
Zelaya Alicia
Narayan Ramesh
English Joyce
Jabbar Ahmad
Borg James
(QUERY 7) List the names of managers who have at least one dependent.
Result: LNAME FNAME
CHAPTER 6: THE RELATIONAL ALGEBRA AND RELATIONAL CALCULUS
Answers to Selected Exercises
6.15 - Show the result of each of the sample queries in Section 6.5 as it would apply to the
database state in Figure 3.6.
Answer:
(QUERY 1) Find the name and address of all employees who work for the 'Research'
department.
Result: FNAME LNAME ADDRESS
John Smith 731 Fondren, Houston, TX
Franklin Wong 638 Voss, Houston, TX
Ramesh Narayan 975 Fire Oak, Humble, TX
Joyce English 5631 Rice, Houston, TX
(QUERY 2) For every project located in 'Stafford', list the project number, the
controlling department number, and the department manager's last name, address, and
birth date.
Result:
PNUMBER DNUM LNAME ADDRESS BDATE
10 4 Wallace 291 Berry, Bellaire, TX 20-JUN-31
30 4 Wallace 291 Berry, Bellaire, TX 20-JUN-31
(QUERY 3) Find the names of all employees who work on all the projects controlled by
department number 5.
Result: (empty because no tuples satisfy the result).
LNAME FNAME
(QUERY 4) Make a list of project numbers for projects that involve an employee whose
last name is 'Smith' as a worker or as a manager of the department that controls the
project.
Result: PNO
1
2
(QUERY 5) List the names of all employees with two or more dependents.
Result: LNAME FNAME
Smith John
Wong Franklin
(QUERY 6) List the names of employees who have no dependents.
Result: LNAME FNAME
Zelaya Alicia
Narayan Ramesh
English Joyce
Jabbar Ahmad
Borg James
(QUERY 7) List the names of managers who have at least one dependent.
Result: LNAME FNAME
Loading page 25...
Chapter 6: The Relational Algebra and Relational Calculus2
Wallace Jennifer
Wong Franklin
6.16 - Specify the following queries on the COMPANY relational database schema shown in
Figure 3.5, using the relational operators discussed in this chapter. Also show the result of
each query as it would apply to the database state of Figure 3.6.
(a) Retrieve the names of employees in department 5 who work more than 10 hours per
week on the 'ProductX' project.
(b) List the names of employees who have a dependent with the same first name as
themselves.
(c) Find the names of employees that are directly supervised by 'Franklin Wong'.
(d) For each project, list the project name and the total hours per week (by all
employees) spent on that project.
(e) Retrieve the names of employees who work on every project.
(f) Retrieve the names of employees who do not work on any project.
(g) For each department, retrieve the department name, and the average salary of
employees working in that department.
(h) Retrieve the average salary of all female employees.
(i) Find the names and addresses of employees who work on at least one project located
in Houston but whose department has no location in Houston.
(j) List the last names of department managers who have no dependents.
Answers:
In the relational algebra, as in other languages, it is possible to specify the same query
in multiple ways. We give one possible solution for each query. We use the symbol s for
SELECT, P for PROJECT, J for EQUIJOIN, * for NATURAL JOIN, and f for FUNCTION.
(a) EMP_W_X <-- ( s PNAME='ProductX' (PROJECT)) J (PNUMBER),(PNO)
(WORKS_ON)
EMP_WORK_10 <-- (EMPLOYEE) J (SSN),(ESSN) ( s HOURS>10
(EMP_W_X))
RESULT <-- P LNAME,FNAME ( s DNO=5 (EMP_WORK_10))
Result:
LNAME FNAME
Smith John
English Joyce
(b) E <-- (EMPLOYEE) J (SSN,FNAME),(ESSN,DEPENDENT_NAME)
(DEPENDENT)
Wallace Jennifer
Wong Franklin
6.16 - Specify the following queries on the COMPANY relational database schema shown in
Figure 3.5, using the relational operators discussed in this chapter. Also show the result of
each query as it would apply to the database state of Figure 3.6.
(a) Retrieve the names of employees in department 5 who work more than 10 hours per
week on the 'ProductX' project.
(b) List the names of employees who have a dependent with the same first name as
themselves.
(c) Find the names of employees that are directly supervised by 'Franklin Wong'.
(d) For each project, list the project name and the total hours per week (by all
employees) spent on that project.
(e) Retrieve the names of employees who work on every project.
(f) Retrieve the names of employees who do not work on any project.
(g) For each department, retrieve the department name, and the average salary of
employees working in that department.
(h) Retrieve the average salary of all female employees.
(i) Find the names and addresses of employees who work on at least one project located
in Houston but whose department has no location in Houston.
(j) List the last names of department managers who have no dependents.
Answers:
In the relational algebra, as in other languages, it is possible to specify the same query
in multiple ways. We give one possible solution for each query. We use the symbol s for
SELECT, P for PROJECT, J for EQUIJOIN, * for NATURAL JOIN, and f for FUNCTION.
(a) EMP_W_X <-- ( s PNAME='ProductX' (PROJECT)) J (PNUMBER),(PNO)
(WORKS_ON)
EMP_WORK_10 <-- (EMPLOYEE) J (SSN),(ESSN) ( s HOURS>10
(EMP_W_X))
RESULT <-- P LNAME,FNAME ( s DNO=5 (EMP_WORK_10))
Result:
LNAME FNAME
Smith John
English Joyce
(b) E <-- (EMPLOYEE) J (SSN,FNAME),(ESSN,DEPENDENT_NAME)
(DEPENDENT)
Loading page 26...
Chapter 6: The Relational Algebra and Relational Calculus 3
R <-- P LNAME,FNAME (E)
Result (empty):
LNAME FNAME
(c) WONG_SSN <-- P SSN ( s FNAME='Franklin' AND
LNAME='Wong' (EMPLOYEE))
WONG_EMPS <-- (EMPLOYEE) J (SUPERSSN),(SSN) (WONG_SSN)
RESULT <-- P LNAME,FNAME (WONG_EMPS)
Result:
LNAME FNAME
Smith John
Narayan Ramesh
English Joyce
(d) PROJ_HOURS(PNO,TOT_HRS) <-- PNO f SUM HOURS (WORKS_ON)
RESULT <-- P PNAME,TOT_HRS ( (PROJ_HOURS) J (PNO),(PNUMBER)
(PROJECT) )
Result:
PNAME TOT_HRS
ProductX 52.5
ProductY 37.5
ProductZ 50.0
Computerization 55.0
Reorganization 25.0
Newbenefits 55.0
(e) PROJ_EMPS(PNO,SSN) <-- P PNO,ESSN (WORKS_ON)
ALL_PROJS(PNO) <-- P PNUMBER (PROJECT)
EMPS_ALL_PROJS <-- PROJ_EMPS -:- ALLPROJS (* DIVISION operation *)
RESULT <-- P LNAME,FNAME (EMPLOYEE * EMP_ALL_PROJS)
Result (empty):
LNAME FNAME
(f) ALL_EMPS <-- P SSN (EMPLOYEE)
WORKING_EMPS(SSN) <-- P ESSN (WORKS_ON)
NON_WORKING_EMPS <-- ALL_EMPS - WORKING_EMPS (* DIFFERENCE
*)
RESULT <-- P LNAME,FNAME (EMPLOYEE * NON_WORKING_EMPS)
Result (empty):
LNAME FNAME
(g) DEPT_AVG_SALS(DNUMBER,AVG_SAL) <-- DNO f AVG SALARY
(EMPLOYEE)
RESULT <-- P DNUMBER,AVG_SAL ( DEPT_AVG_SALS * DEPARTMENT )
R <-- P LNAME,FNAME (E)
Result (empty):
LNAME FNAME
(c) WONG_SSN <-- P SSN ( s FNAME='Franklin' AND
LNAME='Wong' (EMPLOYEE))
WONG_EMPS <-- (EMPLOYEE) J (SUPERSSN),(SSN) (WONG_SSN)
RESULT <-- P LNAME,FNAME (WONG_EMPS)
Result:
LNAME FNAME
Smith John
Narayan Ramesh
English Joyce
(d) PROJ_HOURS(PNO,TOT_HRS) <-- PNO f SUM HOURS (WORKS_ON)
RESULT <-- P PNAME,TOT_HRS ( (PROJ_HOURS) J (PNO),(PNUMBER)
(PROJECT) )
Result:
PNAME TOT_HRS
ProductX 52.5
ProductY 37.5
ProductZ 50.0
Computerization 55.0
Reorganization 25.0
Newbenefits 55.0
(e) PROJ_EMPS(PNO,SSN) <-- P PNO,ESSN (WORKS_ON)
ALL_PROJS(PNO) <-- P PNUMBER (PROJECT)
EMPS_ALL_PROJS <-- PROJ_EMPS -:- ALLPROJS (* DIVISION operation *)
RESULT <-- P LNAME,FNAME (EMPLOYEE * EMP_ALL_PROJS)
Result (empty):
LNAME FNAME
(f) ALL_EMPS <-- P SSN (EMPLOYEE)
WORKING_EMPS(SSN) <-- P ESSN (WORKS_ON)
NON_WORKING_EMPS <-- ALL_EMPS - WORKING_EMPS (* DIFFERENCE
*)
RESULT <-- P LNAME,FNAME (EMPLOYEE * NON_WORKING_EMPS)
Result (empty):
LNAME FNAME
(g) DEPT_AVG_SALS(DNUMBER,AVG_SAL) <-- DNO f AVG SALARY
(EMPLOYEE)
RESULT <-- P DNUMBER,AVG_SAL ( DEPT_AVG_SALS * DEPARTMENT )
Loading page 27...
Chapter 6: The Relational Algebra and Relational Calculus4
Result:
DNUMBER AVG_SAL
Research 33250
Administration 31000
Headquarters 55000
(h) RESULT(AVG_F_SAL) <-- f AVG SALARY ( s SEX='F' (EMPLOYEE) )
Result:
AVG_F_SAL
31000
(i) E_P_HOU(SSN) <--
P ESSN (WORKS_ON J(PNO),(PNUMBER) ( s PLOCATION='Houston'
(PROJECT)))
D_NO_HOU <--
P DNUMBER (DEPARTMENT) - P DNUMBER ( s
DLOCATION='Houston' (DEPARTMENT))
E_D_NO_HOU <-- P SSN (EMPLOYEE J(PNO),(DNUMBER) (D_NO_HOU))
RESULT_EMPS <-- E_P_HOU - E_D_NO_HOU (* this is set DIFFERENCE *)
RESULT <-- P LNAME,FNAME,ADDRESS (EMPLOYEE * RESULT_EMPS)
Result:
LNAME FNAME ADDRESS
Wallace Jennifer 291 Berry, Bellaire, TX
(j) DEPT_MANAGERS(SSN)<-- P MGRSSN (DEPARTMENT)
EMPS_WITH_DEPENDENTS(SSN) <-- P ESSN (DEPENDENT)
RESULT_EMPS <-- DEPT_MANAGERS - EMPS_WITH_DEPENDENTS
RESULT <-- P LNAME,FNAME (EMPLOYEE * RESULT_EMPS)
Result:
LNAME FNAME
Borg James
6.17 – No solution provided.
6.18 - Consider the LIBRARY relational schema shown in Figure 6.14, which is used to keep
track of books, borrowers, and book loans. Referential integrity constraints are shown as
directed arcs in Figure 6.14, as in the notation of Figure 3.7. Write down relational
expressions for the following queries on the LIBRARY database:
(a) How many copies of the book titled The Lost Tribe are owned by the library branch
whose name is "Sharpstown"?
(b) How many copies of the book titled The Lost Tribe are owned by each library branch?
Result:
DNUMBER AVG_SAL
Research 33250
Administration 31000
Headquarters 55000
(h) RESULT(AVG_F_SAL) <-- f AVG SALARY ( s SEX='F' (EMPLOYEE) )
Result:
AVG_F_SAL
31000
(i) E_P_HOU(SSN) <--
P ESSN (WORKS_ON J(PNO),(PNUMBER) ( s PLOCATION='Houston'
(PROJECT)))
D_NO_HOU <--
P DNUMBER (DEPARTMENT) - P DNUMBER ( s
DLOCATION='Houston' (DEPARTMENT))
E_D_NO_HOU <-- P SSN (EMPLOYEE J(PNO),(DNUMBER) (D_NO_HOU))
RESULT_EMPS <-- E_P_HOU - E_D_NO_HOU (* this is set DIFFERENCE *)
RESULT <-- P LNAME,FNAME,ADDRESS (EMPLOYEE * RESULT_EMPS)
Result:
LNAME FNAME ADDRESS
Wallace Jennifer 291 Berry, Bellaire, TX
(j) DEPT_MANAGERS(SSN)<-- P MGRSSN (DEPARTMENT)
EMPS_WITH_DEPENDENTS(SSN) <-- P ESSN (DEPENDENT)
RESULT_EMPS <-- DEPT_MANAGERS - EMPS_WITH_DEPENDENTS
RESULT <-- P LNAME,FNAME (EMPLOYEE * RESULT_EMPS)
Result:
LNAME FNAME
Borg James
6.17 – No solution provided.
6.18 - Consider the LIBRARY relational schema shown in Figure 6.14, which is used to keep
track of books, borrowers, and book loans. Referential integrity constraints are shown as
directed arcs in Figure 6.14, as in the notation of Figure 3.7. Write down relational
expressions for the following queries on the LIBRARY database:
(a) How many copies of the book titled The Lost Tribe are owned by the library branch
whose name is "Sharpstown"?
(b) How many copies of the book titled The Lost Tribe are owned by each library branch?
Loading page 28...
Chapter 6: The Relational Algebra and Relational Calculus 5
(c) Retrieve the names of all borrowers who do not have any books checked out.
(d) For each book that is loaned out from the "Sharpstown" branch and whose DueDate
is today, retrieve the book title, the borrower's name, and the borrower's address.
(e) For each library branch, retrieve the branch name and the total number of books
loaned out from that branch.
(f) Retrieve the names, addresses, and number of books checked out for all borrowers
who have more than five books checked out.
(g) For each book authored (or co-authored) by "Stephen King", retrieve the title and
the number of copies owned by the library branch whose name is "Central".
Answer:
(Note: We will use S for SELECT, P for PROJECT, * for NATURAL JOIN, - for
SET DIFFERENCE, F for AGGREGATE FUNCTION)
(a) A <-- BOOKCOPIES * LIBRARY-BRANCH * BOOK
RESULT <-- P No_Of_Copies ( S BranchName='Sharpstown' and Title='The Lost
Tribe'
(A) )
Note: A better query would be to do the SELECTs before the JOIN as follows:
A <-- P No_Of_Copies ( ( S BranchName='Sharpstown' (LIBRARY-BRANCH) ) *
(BOOKCOPIES * ( S Title='The Lost Tribe'
(BOOK) ) ) )
(b) P BranchID,No_Of_Copies ( ( S Title='The Lost Tribe' (BOOK)) * BOOKCOPIES )
(c) NO_CHECKOUT_B <-- P CardNo (BORROWER) - P CardNo (BOOK_LOANS)
RESULT <-- P Name (BORROWER * NO_CHECKOUT_B)
(d) S <-- P BranchId ( S BranchName='Sharpstown' (LIBRARY-BRANCH) )
B_FROM_S <-- P BookId,CardNo ( ( S DueDate='today' (BOOKLOANS) ) * S )
RESULT <-- P Title,Name,Address ( BOOK * BORROWER * B_FROM_S )
(e) R(BranchId,Total) <-- BranchId FCOUNT(BookId,CardNo) (BOOK_LOANS)
RESULT <-- P BranchName,Total (R * LIBRARY_BRANCH)
(f) B(CardNo,TotalCheckout) <-- CardNo F COUNT(BookId) (BOOK_LOANS)
B5 <-- S TotalCheckout > 5 (B)
RESULT <-- P Name,Address,TotalCheckout ( B5 * BORROWER)
(g) SK(BookId,Title) <-- ( sAuthorName='Stephen King' ( BOOK_AUTHORS)) * BOOK
CENTRAL(BranchId) <-- sBranchName='Central' ( LIBRARY_BRANCH )
RESULT <-- P Title,NoOfCopies ( SK * BOOKCOPIES * CENTRAL )
6.19 – 6.21: No solutions provided.
(c) Retrieve the names of all borrowers who do not have any books checked out.
(d) For each book that is loaned out from the "Sharpstown" branch and whose DueDate
is today, retrieve the book title, the borrower's name, and the borrower's address.
(e) For each library branch, retrieve the branch name and the total number of books
loaned out from that branch.
(f) Retrieve the names, addresses, and number of books checked out for all borrowers
who have more than five books checked out.
(g) For each book authored (or co-authored) by "Stephen King", retrieve the title and
the number of copies owned by the library branch whose name is "Central".
Answer:
(Note: We will use S for SELECT, P for PROJECT, * for NATURAL JOIN, - for
SET DIFFERENCE, F for AGGREGATE FUNCTION)
(a) A <-- BOOKCOPIES * LIBRARY-BRANCH * BOOK
RESULT <-- P No_Of_Copies ( S BranchName='Sharpstown' and Title='The Lost
Tribe'
(A) )
Note: A better query would be to do the SELECTs before the JOIN as follows:
A <-- P No_Of_Copies ( ( S BranchName='Sharpstown' (LIBRARY-BRANCH) ) *
(BOOKCOPIES * ( S Title='The Lost Tribe'
(BOOK) ) ) )
(b) P BranchID,No_Of_Copies ( ( S Title='The Lost Tribe' (BOOK)) * BOOKCOPIES )
(c) NO_CHECKOUT_B <-- P CardNo (BORROWER) - P CardNo (BOOK_LOANS)
RESULT <-- P Name (BORROWER * NO_CHECKOUT_B)
(d) S <-- P BranchId ( S BranchName='Sharpstown' (LIBRARY-BRANCH) )
B_FROM_S <-- P BookId,CardNo ( ( S DueDate='today' (BOOKLOANS) ) * S )
RESULT <-- P Title,Name,Address ( BOOK * BORROWER * B_FROM_S )
(e) R(BranchId,Total) <-- BranchId FCOUNT(BookId,CardNo) (BOOK_LOANS)
RESULT <-- P BranchName,Total (R * LIBRARY_BRANCH)
(f) B(CardNo,TotalCheckout) <-- CardNo F COUNT(BookId) (BOOK_LOANS)
B5 <-- S TotalCheckout > 5 (B)
RESULT <-- P Name,Address,TotalCheckout ( B5 * BORROWER)
(g) SK(BookId,Title) <-- ( sAuthorName='Stephen King' ( BOOK_AUTHORS)) * BOOK
CENTRAL(BranchId) <-- sBranchName='Central' ( LIBRARY_BRANCH )
RESULT <-- P Title,NoOfCopies ( SK * BOOKCOPIES * CENTRAL )
6.19 – 6.21: No solutions provided.
Loading page 29...
Chapter 6: The Relational Algebra and Relational Calculus6
6.22 – Consider the two tables T1 and T2 shown in Figure 6.15. Show the results of the
following operations:
Answers:
(a)
P Q R A B C
10 a 5 10 b 6
10 a 5 10 b 5
25 a 6 25 c 3
(b)
P Q R A B C
15 b 8 10 b 6
15 b 8 10 b 5
(c)
P Q R A B C
10 a 5 10 b 6
10 a 5 10 b 5
15 b 8 null null null
25 a 6 25 c 3
(d)
P Q R A B C
15 b 8 10 b 6
null null null 25 c 3
15 b 8 10 b 5
(e)
P Q R
10a 5
15 b 8
25 a 6
10b 6
25 c 3
10b 5
(f)
P Q R A B C
10 a 5 10 b 5
6.23 – No solution provided.
6.24 - Specify queries (a), (b), (c), (e), (f), (i), and (j) of Exercise 6.16 in both tuple and
domain relational calculus.
Answer:
(a) Retrieve the names of employees in department 5 who work more than 10 hours per
week on the 'ProductX' project.
Tuple relational Calculus:
{ e.LNAME, e.FNAME | EMPLOYEE(e) AND e.DNO=5 AND (EXISTS p)
(EXISTS w)
(
WORKS_ON(w) AND PROJECT(p) AND e.SSN=w.ESSN AND
w.PNO=p.PNUMBER AND
p.PNAME='ProductX' AND w.HOURS>10 ) }
6.22 – Consider the two tables T1 and T2 shown in Figure 6.15. Show the results of the
following operations:
Answers:
(a)
P Q R A B C
10 a 5 10 b 6
10 a 5 10 b 5
25 a 6 25 c 3
(b)
P Q R A B C
15 b 8 10 b 6
15 b 8 10 b 5
(c)
P Q R A B C
10 a 5 10 b 6
10 a 5 10 b 5
15 b 8 null null null
25 a 6 25 c 3
(d)
P Q R A B C
15 b 8 10 b 6
null null null 25 c 3
15 b 8 10 b 5
(e)
P Q R
10a 5
15 b 8
25 a 6
10b 6
25 c 3
10b 5
(f)
P Q R A B C
10 a 5 10 b 5
6.23 – No solution provided.
6.24 - Specify queries (a), (b), (c), (e), (f), (i), and (j) of Exercise 6.16 in both tuple and
domain relational calculus.
Answer:
(a) Retrieve the names of employees in department 5 who work more than 10 hours per
week on the 'ProductX' project.
Tuple relational Calculus:
{ e.LNAME, e.FNAME | EMPLOYEE(e) AND e.DNO=5 AND (EXISTS p)
(EXISTS w)
(
WORKS_ON(w) AND PROJECT(p) AND e.SSN=w.ESSN AND
w.PNO=p.PNUMBER AND
p.PNAME='ProductX' AND w.HOURS>10 ) }
Loading page 30...
Chapter 6: The Relational Algebra and Relational Calculus 7
Domain relational Calculus:
{ qs | EMPLOYEE(qrstuvwxyz) AND z=5 AND (EXISTS a) (EXISTS b) (EXISTS
e)
(EXISTS f)
(EXISTS g) ( WORKS_ON(efg) AND PROJECT(abcd) AND t=e AND f=b AND
a='ProductX' AND
g>10 ) }
(b) List the names of employees who have a dependent with the same first name as
themselves.
Tuple relational Calculus:
{ e.LNAME, e.FNAME | EMPLOYEE(e) AND (EXISTS d) ( DEPENDENT(d) AND
e.SSN=d.ESSN
AND e.FNAME=d.DEPENDENT_NAME ) }
Domain relational Calculus:
{ qs | (EXISTS t) (EXISTS a) (EXISTS b) ( EMPLOYEE(qrstuvwxyz) AND
DEPENDENT(abcde)
AND a=t AND b=q ) }
(c) Find the names of employees that are directly supervised by 'Franklin Wong'.
Tuple relational Calculus:
{ e.LNAME, e.FNAME | EMPLOYEE(e) AND (EXISTS s) ( EMPLOYEE(s) AND
s.FNAME='Franklin' AND s.LNAME='Wong' AND e.SUPERSSN=s.SSN ) }
Domain relational Calculus:
{ qs | (EXISTS y) (EXISTS a) (EXISTS c) (EXISTS d) ( EMPLOYEE(qrstuvwxyz)
AND
EMPLOYEE(abcdefghij) AND a='Franklin' AND c='Wong' AND y=d ) }
(e) Retrieve the names of employees who work on every project.
Tuple relational Calculus:
{ e.LNAME, e.FNAME | EMPLOYEE(e) AND (FORALL p) ( NOT(PROJECT(p))
OR
(EXISTS w) (
WORKS_ON(w) AND p.PNUMBER=w.PNO AND w.ESSN=e.SSN ) ) }
Domain relational Calculus:
{ qs | (EXISTS t) ( EMPLOYEE(qrstuvwxyz) AND (FORALL b) (
NOT(PROJECT(abcd)) OR
(EXISTS e) (EXISTS f) (WORKS_ON(efg) AND e=t AND f=b) ) }
(f) Retrieve the names of employees who do not work on any project.
Tuple relational Calculus:
{ e.LNAME, e.FNAME | EMPLOYEE(e) AND NOT(EXISTS w) ( WORKS_ON(w)
AND
w.ESSN=e.SSN ) }
Domain relational Calculus:
{ qs | (EXISTS t) ( EMPLOYEE(qrstuvwxyz) AND NOT(EXISTS a) (
WORKS_ON(abc) AND a=t )
) }
Domain relational Calculus:
{ qs | EMPLOYEE(qrstuvwxyz) AND z=5 AND (EXISTS a) (EXISTS b) (EXISTS
e)
(EXISTS f)
(EXISTS g) ( WORKS_ON(efg) AND PROJECT(abcd) AND t=e AND f=b AND
a='ProductX' AND
g>10 ) }
(b) List the names of employees who have a dependent with the same first name as
themselves.
Tuple relational Calculus:
{ e.LNAME, e.FNAME | EMPLOYEE(e) AND (EXISTS d) ( DEPENDENT(d) AND
e.SSN=d.ESSN
AND e.FNAME=d.DEPENDENT_NAME ) }
Domain relational Calculus:
{ qs | (EXISTS t) (EXISTS a) (EXISTS b) ( EMPLOYEE(qrstuvwxyz) AND
DEPENDENT(abcde)
AND a=t AND b=q ) }
(c) Find the names of employees that are directly supervised by 'Franklin Wong'.
Tuple relational Calculus:
{ e.LNAME, e.FNAME | EMPLOYEE(e) AND (EXISTS s) ( EMPLOYEE(s) AND
s.FNAME='Franklin' AND s.LNAME='Wong' AND e.SUPERSSN=s.SSN ) }
Domain relational Calculus:
{ qs | (EXISTS y) (EXISTS a) (EXISTS c) (EXISTS d) ( EMPLOYEE(qrstuvwxyz)
AND
EMPLOYEE(abcdefghij) AND a='Franklin' AND c='Wong' AND y=d ) }
(e) Retrieve the names of employees who work on every project.
Tuple relational Calculus:
{ e.LNAME, e.FNAME | EMPLOYEE(e) AND (FORALL p) ( NOT(PROJECT(p))
OR
(EXISTS w) (
WORKS_ON(w) AND p.PNUMBER=w.PNO AND w.ESSN=e.SSN ) ) }
Domain relational Calculus:
{ qs | (EXISTS t) ( EMPLOYEE(qrstuvwxyz) AND (FORALL b) (
NOT(PROJECT(abcd)) OR
(EXISTS e) (EXISTS f) (WORKS_ON(efg) AND e=t AND f=b) ) }
(f) Retrieve the names of employees who do not work on any project.
Tuple relational Calculus:
{ e.LNAME, e.FNAME | EMPLOYEE(e) AND NOT(EXISTS w) ( WORKS_ON(w)
AND
w.ESSN=e.SSN ) }
Domain relational Calculus:
{ qs | (EXISTS t) ( EMPLOYEE(qrstuvwxyz) AND NOT(EXISTS a) (
WORKS_ON(abc) AND a=t )
) }
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