Solution Manual for Database Systems: A Practical Approach to Design, Implementation, and Management, 6th Edition
Ace your coursework with Solution Manual for Database Systems: A Practical Approach to Design, Implementation, and Management, 6th Edition, designed to simplify complex topics.
Alexander Wilson
Contributor
4.5
88
about 2 months ago
Preview (31 of 470)
Sign in to access the full document!
SOLUTIONS TO REVIEW QUESTIONS
AND EXERCISES
FOR PART 1 – BACKGROUND (CHAPTERS 1 – 3)
AND EXERCISES
FOR PART 1 – BACKGROUND (CHAPTERS 1 – 3)
Database Systems: Instructor’s Guide - Part III
2
Solutions to Review Questions and Exercises
Chapter 1 Introduction to Databases ................................................................................................................ 3
Chapter 2 Database Environment..................................................................................................................... 7
Chapter 3 Database Architectures and the Web............................................................................................. 11
2
Solutions to Review Questions and Exercises
Chapter 1 Introduction to Databases ................................................................................................................ 3
Chapter 2 Database Environment..................................................................................................................... 7
Chapter 3 Database Architectures and the Web............................................................................................. 11
Database Systems: Instructor’s Guide - Part III
3
Chapter 1 Introduction to Databases
Review Questions
1.1 List four examples of database systems other than those listed in Section 1.1.
Some examples could be:
• A system that maintains component part details for a car manufacturer;
• An advertising company keeping details of all clients and adverts placed with them;
• A training company keeping course information and participants’ details;
• An organization maintaining all sales order information.
1.2 Discuss each of the following terms:
Data For end users, this constitutes all the different values connected with the
various objects/entities that are of concern to them. See also Section 1.3.4.
Database See Section 1.3.1
Database Management System See Section 1.3.2
Database Application Program See Section 1.3.3
Data Independence This is essentially the separation of underlying file structures from the
programs that operate on them, also called program-data independence. See
also Sections 1.2.2 and 1.3.1.
Security The protection of the database from unauthorized users, which may involve
passwords and access restrictions. See also Section 1.6.
Integrity The maintenance of the validity and consistency of the database by use of
particular constraints that are applied to the data. See also Section 1.6.
Views These present only a subset of the database that is of particular interest to a
user. Views can be customized, for example, field names may change, and
they also provide a level of security preventing users from seeing certain
data. See also Section 1.3.3.
1.3 Describe the approach taken to the handling of data in the early file-based systems. Discuss the
disadvantages of this approach.
Focus was on applications for which programs would be written, and all the data required would
be stored in a file or files owned by the programs. See also Section 1.2.
Clearly, each program was responsible for only its own data, which could be repeated in other
program’s data files. Different programs could be written in different languages, and would not be
able to access another program’s files. This would be true even for those programs written in the
same language, because a program needs to know the file structure before it can access it. See also
Section 1.2.2.
1.4 Describe the main characteristics of the database approach and contrast it with the file-based
approach.
3
Chapter 1 Introduction to Databases
Review Questions
1.1 List four examples of database systems other than those listed in Section 1.1.
Some examples could be:
• A system that maintains component part details for a car manufacturer;
• An advertising company keeping details of all clients and adverts placed with them;
• A training company keeping course information and participants’ details;
• An organization maintaining all sales order information.
1.2 Discuss each of the following terms:
Data For end users, this constitutes all the different values connected with the
various objects/entities that are of concern to them. See also Section 1.3.4.
Database See Section 1.3.1
Database Management System See Section 1.3.2
Database Application Program See Section 1.3.3
Data Independence This is essentially the separation of underlying file structures from the
programs that operate on them, also called program-data independence. See
also Sections 1.2.2 and 1.3.1.
Security The protection of the database from unauthorized users, which may involve
passwords and access restrictions. See also Section 1.6.
Integrity The maintenance of the validity and consistency of the database by use of
particular constraints that are applied to the data. See also Section 1.6.
Views These present only a subset of the database that is of particular interest to a
user. Views can be customized, for example, field names may change, and
they also provide a level of security preventing users from seeing certain
data. See also Section 1.3.3.
1.3 Describe the approach taken to the handling of data in the early file-based systems. Discuss the
disadvantages of this approach.
Focus was on applications for which programs would be written, and all the data required would
be stored in a file or files owned by the programs. See also Section 1.2.
Clearly, each program was responsible for only its own data, which could be repeated in other
program’s data files. Different programs could be written in different languages, and would not be
able to access another program’s files. This would be true even for those programs written in the
same language, because a program needs to know the file structure before it can access it. See also
Section 1.2.2.
1.4 Describe the main characteristics of the database approach and contrast it with the file-based
approach.
Loading page 4...
Database Systems: Instructor’s Guide - Part III
4
Focus is now on the data first, and then the applications. The structure of the data is now kept
separate from the programs that operate on the data. This is held in the system catalog or data
dictionary. Programs can now share data, which is no longer fragmented. There is also a reduction
in redundancy, and achievement of program-data independence. See also Section 1.3.
1.5 Describe the five components of the DBMS environment and discuss how they relate to each
other.
See Section 1.3.3.
1.6 Discuss the roles of the following personnel in the database environment:
Data Administrator See Section 1.4.1
Database Administrator See Section 1.4.1
Logical Database Designer See Section 1.4.2
Physical Database Designer See Section 1.4.2
Application Developer See Section 1.4.3
End-Users See Section 1.4.4
1.7 Discuss the three generations of DBMSs.
The CODASYL and hierarchical approaches represented the first generation of DBMSs. They
were based on the concept that smaller components come together as parts of larger
components, and so on, until the final product is assembled. This structure, which conforms to
an upside down tree, is also known as a hierarchical structure.
Relational DBMSs are referred to as second-generation DBMSs. In 1970, E. F. Codd of the
IBM Research Laboratory produced his highly influential paper on the relational data model
(“A relational model of data for large shared data banks,” Codd, 1970). This paper was very
timely and addressed the disadvantages of the former approaches. Many experimental
relational DBMSs were implemented thereafter.
In response to the increasing complexity of database applications, two “new” systems have
emerged: the object-oriented DBMS (OODBMS) and the object-relational DBMS
(ORDBMS). However, unlike previous models, the actual composition of these models is not
clear. This evolution represents third generation DBMSs.
1.8 Discuss the advantages and disadvantages of database management systems.
See Section 1.6
4
Focus is now on the data first, and then the applications. The structure of the data is now kept
separate from the programs that operate on the data. This is held in the system catalog or data
dictionary. Programs can now share data, which is no longer fragmented. There is also a reduction
in redundancy, and achievement of program-data independence. See also Section 1.3.
1.5 Describe the five components of the DBMS environment and discuss how they relate to each
other.
See Section 1.3.3.
1.6 Discuss the roles of the following personnel in the database environment:
Data Administrator See Section 1.4.1
Database Administrator See Section 1.4.1
Logical Database Designer See Section 1.4.2
Physical Database Designer See Section 1.4.2
Application Developer See Section 1.4.3
End-Users See Section 1.4.4
1.7 Discuss the three generations of DBMSs.
The CODASYL and hierarchical approaches represented the first generation of DBMSs. They
were based on the concept that smaller components come together as parts of larger
components, and so on, until the final product is assembled. This structure, which conforms to
an upside down tree, is also known as a hierarchical structure.
Relational DBMSs are referred to as second-generation DBMSs. In 1970, E. F. Codd of the
IBM Research Laboratory produced his highly influential paper on the relational data model
(“A relational model of data for large shared data banks,” Codd, 1970). This paper was very
timely and addressed the disadvantages of the former approaches. Many experimental
relational DBMSs were implemented thereafter.
In response to the increasing complexity of database applications, two “new” systems have
emerged: the object-oriented DBMS (OODBMS) and the object-relational DBMS
(ORDBMS). However, unlike previous models, the actual composition of these models is not
clear. This evolution represents third generation DBMSs.
1.8 Discuss the advantages and disadvantages of database management systems.
See Section 1.6
Loading page 5...
Database Systems: Instructor’s Guide - Part III
5
Exercises
1.9 Interview some users of database systems. Which DBMS features do they find most useful and
why? Which DBMS facilities do they find least useful and why? What do these users perceive to
be the advantages and disadvantages of the DBMS?
Select a variety of users for a particular DBMS. If the users are using different DBMSs, group the
answers for the different systems, which will give an overall picture of specific systems.
1.10 Write a small program (using pseudocode, if necessary) that allows entry and display of client
details including a client number, name, address, telephone number, preferred number of rooms,
and maximum rent. The details should be stored in a file. Enter a few records and display the
details. Now repeat this process but rather than writing a special program, use any DBMS that
you have access to. What can you conclude from these two approaches?
The program can be written in any appropriate programming language, such as Pascal,
FORTRAN, C. It should adhere to basic software engineering principles including being well-
structured, modular, and suitably commented. It is important to appreciate the process involved
even in developing a small program such as this. The DBMS facilities to structure, store, and
retrieve data are used to the same effect. The differences in the approaches, such as the effort
involved, potential for extension, ability to share the data should be noted.
1.11 Study the DreamHome case study presented in Section 10.4 and Appendix A. In what ways would
a DBMS help this organization? What data can you identify that needs to be represented in the
database? What relationships exist between the data? What queries do you think are required?
It may be useful to review the file-based approach and the database approach here before tackling
the first part of the exercise. Careful reading and thinking about how people might use the
applications should help in carrying out the rest of the exercise.
1.12 Study the Wellmeadows Hospital case study presented in Appendix B.3. In what ways would a
DBMS help this organization? What data can you identify that needs to be represented in the
database? What relationships exist between the data?
The approach used for Exercise 1.10 should be used for this exercise also.
1.13 Discuss what you consider to be the three most important advantages for the use of a DBMS for a
company like DreamHome and provide a justification for your selection. Discuss what you
consider to be the three most important disadvantages for the use of a DBMS for a company like
DreamHome and provide a justification for your selection.
Students should review section 1.6 to make a list of advantages and disadvantages of using a
DBMS.
1.14 Using any Web browser, look at some of the following Web pages and discover the wealth of
information available there:
5
Exercises
1.9 Interview some users of database systems. Which DBMS features do they find most useful and
why? Which DBMS facilities do they find least useful and why? What do these users perceive to
be the advantages and disadvantages of the DBMS?
Select a variety of users for a particular DBMS. If the users are using different DBMSs, group the
answers for the different systems, which will give an overall picture of specific systems.
1.10 Write a small program (using pseudocode, if necessary) that allows entry and display of client
details including a client number, name, address, telephone number, preferred number of rooms,
and maximum rent. The details should be stored in a file. Enter a few records and display the
details. Now repeat this process but rather than writing a special program, use any DBMS that
you have access to. What can you conclude from these two approaches?
The program can be written in any appropriate programming language, such as Pascal,
FORTRAN, C. It should adhere to basic software engineering principles including being well-
structured, modular, and suitably commented. It is important to appreciate the process involved
even in developing a small program such as this. The DBMS facilities to structure, store, and
retrieve data are used to the same effect. The differences in the approaches, such as the effort
involved, potential for extension, ability to share the data should be noted.
1.11 Study the DreamHome case study presented in Section 10.4 and Appendix A. In what ways would
a DBMS help this organization? What data can you identify that needs to be represented in the
database? What relationships exist between the data? What queries do you think are required?
It may be useful to review the file-based approach and the database approach here before tackling
the first part of the exercise. Careful reading and thinking about how people might use the
applications should help in carrying out the rest of the exercise.
1.12 Study the Wellmeadows Hospital case study presented in Appendix B.3. In what ways would a
DBMS help this organization? What data can you identify that needs to be represented in the
database? What relationships exist between the data?
The approach used for Exercise 1.10 should be used for this exercise also.
1.13 Discuss what you consider to be the three most important advantages for the use of a DBMS for a
company like DreamHome and provide a justification for your selection. Discuss what you
consider to be the three most important disadvantages for the use of a DBMS for a company like
DreamHome and provide a justification for your selection.
Students should review section 1.6 to make a list of advantages and disadvantages of using a
DBMS.
1.14 Using any Web browser, look at some of the following Web pages and discover the wealth of
information available there:
Loading page 6...
Database Systems: Instructor’s Guide - Part III
6
(a) http://www.oracle.com
(b) http://www.microsoft.com/sql and http://www.microsoft.com/access
(c) http://www.ibm.com/db2
(d) http://www.mysql.com
(e) http://en.wikipedia.org/wiki/database and http://en.wikipedia.org/wiki/DBMS
Students should visit the Web pages listed above to gain understanding about the type of
information covered on each Web site. Pages are the major database vendor Web sites while the
final two links provide information on databases and DBMS.
6
(a) http://www.oracle.com
(b) http://www.microsoft.com/sql and http://www.microsoft.com/access
(c) http://www.ibm.com/db2
(d) http://www.mysql.com
(e) http://en.wikipedia.org/wiki/database and http://en.wikipedia.org/wiki/DBMS
Students should visit the Web pages listed above to gain understanding about the type of
information covered on each Web site. Pages are the major database vendor Web sites while the
final two links provide information on databases and DBMS.
Loading page 7...
Database Systems: Instructor’s Guide - Part III
7
Chapter 2 Database Environment
Review Questions
2.1 Discuss the concept of data independence and explain its importance in a database environment.
See Section 2.1.5
2.2 To address the issue of data independence, the ANSI-SPARC three-level architecture was
proposed. Compare and contrast the three levels of this model.
See Section 2.1
2.3 What is a data model? Discuss the main types of data models.
An integrated collection of concepts for describing and manipulating data, relationships between
data, and constraints on the data in an organization. See Section 2.3.
Object-based data models such as the Entity-Relationship model (see Section 2.3.1). Record-
based data models such as the relational data model, network data model, and hierarchical data
model (see Section 2.3.2). Physical data models describe how data is stored in the computer (see
Section 2.3.3).
2.4 Discuss the function and importance of conceptual modeling.
See Section 2.3.4.
2.5 Describe the types of facility you would expect to be provided in a multi-user DBMS.
Data Storage, Retrieval and Update Authorization Services
A User-Accessible Catalog Support for Data Communication
Transaction Support Integrity Services
Concurrency Control Services Services to Promote Data Independence
Recovery Services Utility Services
See also Section 2.4
2.6 Of the facilities described in your answer to Question 2.5, which ones do you think would not be
needed in a standalone PC DBMS? Provide justification for your answer.
Concurrency Control Services - only single user.
Authorization Services - only single user, but may be needed if different individuals are to use the
DBMS at different times.
Utility Services - limited in scope.
Support for Data Communication - only standalone system.
7
Chapter 2 Database Environment
Review Questions
2.1 Discuss the concept of data independence and explain its importance in a database environment.
See Section 2.1.5
2.2 To address the issue of data independence, the ANSI-SPARC three-level architecture was
proposed. Compare and contrast the three levels of this model.
See Section 2.1
2.3 What is a data model? Discuss the main types of data models.
An integrated collection of concepts for describing and manipulating data, relationships between
data, and constraints on the data in an organization. See Section 2.3.
Object-based data models such as the Entity-Relationship model (see Section 2.3.1). Record-
based data models such as the relational data model, network data model, and hierarchical data
model (see Section 2.3.2). Physical data models describe how data is stored in the computer (see
Section 2.3.3).
2.4 Discuss the function and importance of conceptual modeling.
See Section 2.3.4.
2.5 Describe the types of facility you would expect to be provided in a multi-user DBMS.
Data Storage, Retrieval and Update Authorization Services
A User-Accessible Catalog Support for Data Communication
Transaction Support Integrity Services
Concurrency Control Services Services to Promote Data Independence
Recovery Services Utility Services
See also Section 2.4
2.6 Of the facilities described in your answer to Question 2.5, which ones do you think would not be
needed in a standalone PC DBMS? Provide justification for your answer.
Concurrency Control Services - only single user.
Authorization Services - only single user, but may be needed if different individuals are to use the
DBMS at different times.
Utility Services - limited in scope.
Support for Data Communication - only standalone system.
Loading page 8...
Database Systems: Instructor’s Guide - Part III
8
2.7 Discuss the function and importance of the system catalog.
See Section 2.4, Service (2) – User-accessible catalog.
2.8 Discuss the differences between DDL and DML? What operations would you typically expect
to be available in each language?
DDL - A language that allows the DBA or user to describe and name the entities, attributes,
and relationships required for the application, together with any associated integrity and
security constraints.
DML - A language that provides a set of operations to support the basic data manipulation
operations on the data held in the database.
See Section 2.2.2.
2.9 Discuss the differences between procedural DMLs and nonprocedural DMLs?
Procedural DML - A language that allows the user to tell the system what data is needed and
exactly how to retrieve the data.
Nonprocedural DML - A language that allows the user to state what data is needed rather than
how it is to be retrieved.
See Section 2.2.2.
2.10 Name four object-based data models.
• Entity-Relationship (ER)
• Semantic
• Functional
• Object-oriented.
See Section 2.3.1.
2.11 Name three record-based data models. Discuss the main differences between these data
models.
• relational data model - data and relationships are represented as tables, each of which
has a number of columns with a unique name
• network data model - data is represented as collections of records, and relationships are
represented by sets. Compared with the relational model, relationships are explicitly
modeled by the sets, which become pointers in the implementation. The records are
organized as generalized graph structures with records appearing as nodes (also called
segments) and sets as edges in the graph
8
2.7 Discuss the function and importance of the system catalog.
See Section 2.4, Service (2) – User-accessible catalog.
2.8 Discuss the differences between DDL and DML? What operations would you typically expect
to be available in each language?
DDL - A language that allows the DBA or user to describe and name the entities, attributes,
and relationships required for the application, together with any associated integrity and
security constraints.
DML - A language that provides a set of operations to support the basic data manipulation
operations on the data held in the database.
See Section 2.2.2.
2.9 Discuss the differences between procedural DMLs and nonprocedural DMLs?
Procedural DML - A language that allows the user to tell the system what data is needed and
exactly how to retrieve the data.
Nonprocedural DML - A language that allows the user to state what data is needed rather than
how it is to be retrieved.
See Section 2.2.2.
2.10 Name four object-based data models.
• Entity-Relationship (ER)
• Semantic
• Functional
• Object-oriented.
See Section 2.3.1.
2.11 Name three record-based data models. Discuss the main differences between these data
models.
• relational data model - data and relationships are represented as tables, each of which
has a number of columns with a unique name
• network data model - data is represented as collections of records, and relationships are
represented by sets. Compared with the relational model, relationships are explicitly
modeled by the sets, which become pointers in the implementation. The records are
organized as generalized graph structures with records appearing as nodes (also called
segments) and sets as edges in the graph
Loading page 9...
Database Systems: Instructor’s Guide - Part III
9
• hierarchical data model - restricted type of network model. Again, data is represented as
collections of records and relationships are represented by sets. However, the hierarchical
model allows a node to have only one parent. A hierarchical model can be represented as
a tree graph, with records appearing as nodes (also called segments) and sets as edges.
See Section 2.3.2.
2.12 What is a transaction. Give an example of a transaction.
A transaction is a series of actions, carried out by a single user or application program, which
accesses or changes the contents of the database.
See Section 2.4.
2.13 What is concurrency control and why does a DBMS need a concurrency control facility?
A mechanism to ensure that the database is updated correctly when multiple users are
updating the database concurrently.
This avoids inconsistencies from arising when two or more transactions are executing and at
least one is updating the database.
See Section 2.4.
2.14 Define the term "database integrity". How does database integrity differ from database
security?
“Database integrity” refers to the correctness and consistency of stored data: it can be
considered as another type of database protection. Although integrity is related to security, it
has wider implications: integrity is concerned with the quality of data itself. Integrity is
usually expressed in terms of constraints, which are consistency rules that the database is not
permitted to violate.
See Section 2.4.
Exercises
2.15 Analyze the DBMSs that you are currently using. Determine each system’s compliance with the
functions that we would expect to be provided by a DBMS. What type of language does each
system provide? What type of architecture does each DBMS use? Check the accessibility and
extensibility of the system catalog. Is it possible to export the system catalog to another system?
To do this you will need to obtain appropriate information about each system. There should be
manuals available or possibly someone in charge of each system who could supply the necessary
information.
9
• hierarchical data model - restricted type of network model. Again, data is represented as
collections of records and relationships are represented by sets. However, the hierarchical
model allows a node to have only one parent. A hierarchical model can be represented as
a tree graph, with records appearing as nodes (also called segments) and sets as edges.
See Section 2.3.2.
2.12 What is a transaction. Give an example of a transaction.
A transaction is a series of actions, carried out by a single user or application program, which
accesses or changes the contents of the database.
See Section 2.4.
2.13 What is concurrency control and why does a DBMS need a concurrency control facility?
A mechanism to ensure that the database is updated correctly when multiple users are
updating the database concurrently.
This avoids inconsistencies from arising when two or more transactions are executing and at
least one is updating the database.
See Section 2.4.
2.14 Define the term "database integrity". How does database integrity differ from database
security?
“Database integrity” refers to the correctness and consistency of stored data: it can be
considered as another type of database protection. Although integrity is related to security, it
has wider implications: integrity is concerned with the quality of data itself. Integrity is
usually expressed in terms of constraints, which are consistency rules that the database is not
permitted to violate.
See Section 2.4.
Exercises
2.15 Analyze the DBMSs that you are currently using. Determine each system’s compliance with the
functions that we would expect to be provided by a DBMS. What type of language does each
system provide? What type of architecture does each DBMS use? Check the accessibility and
extensibility of the system catalog. Is it possible to export the system catalog to another system?
To do this you will need to obtain appropriate information about each system. There should be
manuals available or possibly someone in charge of each system who could supply the necessary
information.
Loading page 10...
Database Systems: Instructor’s Guide - Part III
10
2.16 Write a program that stores names and telephone numbers in a database. Write another program
that stores names and addresses in a database. Modify the programs to use external, conceptual,
and internal schemas. What are the advantages and disadvantages of this modification?
The programs can be written in any suitable language and should be well structured and
appropriately commented. Two distinct files result. The structures can be combined into one
containing name, address, and telNo, which can be the representation of both the internal and
conceptual schemas. The conceptual schema should be created separately with a routine to map
the conceptual to the internal schema. The two external schemas also must be created separately
with routines to map the data between the external and the conceptual schema. The two programs
should then use the appropriate external schema and routines.
2.17 Write a program that stores names and dates of birth in a database. Extend the program so that it
stores the format of the data in the database; in other words, create a system catalog. Provide an
interface that makes this system catalog accessible to external users.
Again, the program can be written in any suitable language. It should then be modified to add the
data format to the original file. This should not be difficult, if the original program is well
structured. The interface for other users operates on the data dictionary and is separate from the
original program. A menu-based interface is adequate.
10
2.16 Write a program that stores names and telephone numbers in a database. Write another program
that stores names and addresses in a database. Modify the programs to use external, conceptual,
and internal schemas. What are the advantages and disadvantages of this modification?
The programs can be written in any suitable language and should be well structured and
appropriately commented. Two distinct files result. The structures can be combined into one
containing name, address, and telNo, which can be the representation of both the internal and
conceptual schemas. The conceptual schema should be created separately with a routine to map
the conceptual to the internal schema. The two external schemas also must be created separately
with routines to map the data between the external and the conceptual schema. The two programs
should then use the appropriate external schema and routines.
2.17 Write a program that stores names and dates of birth in a database. Extend the program so that it
stores the format of the data in the database; in other words, create a system catalog. Provide an
interface that makes this system catalog accessible to external users.
Again, the program can be written in any suitable language. It should then be modified to add the
data format to the original file. This should not be difficult, if the original program is well
structured. The interface for other users operates on the data dictionary and is separate from the
original program. A menu-based interface is adequate.
Loading page 11...
Database Systems: Instructor’s Guide - Part III
11
Chapter 3 Database Architectures and the Web
Review Questions
3.1 What is meant by the term ‘client-server architecture’ and what are the advantages of this
approach? Compare the client-server architecture with two other architectures.
The client is a process that requires some resource, and the server provides the resource. Neither
need reside on the same machine. Advantages include:
• Better performance
• Likely reduction in hardware costs
• Reduction in communication costs
• Better consistency
See also Section 3.1.
3.2 Compare and contrast the two-tier client-server architecture for traditional DBMSs with the
three-tier client-server architecture. Why is the latter architecture more appropriate for the Web?
See Figures 3.5 and 3.6. Architecture maps quite naturally to the Web with a Web browser acting
as ‘thin’ client and Web server acting as an application server (with database server as third layer).
3.3 What is an n-tier architecture?
The three-tier architecture can be expanded to n tiers, with additional tiers providing more
flexibility and scalability.
3.4 What is middleware? Provide a classification service for middleware.
Middleware is a generic term used to describe software that mediates with other software and
allows for communication between disparate applications in a heterogeneous system. The need
for middleware arises when distributed systems become too complex to manage efficiently
without a common interface. The need to make heterogeneous systems work efficiently across a
network and be flexible enough to incorporate frequent modifications led to the development of
middleware, which hides the underlying complexity of distributed systems.
3.5 What is a TP Monitor? What advantages does a TP Monitor bring to an OLTP environment?
A TP Monitor forms the middle tier of a three-tier architecture. TP Monitors provide significant
advantages, including:
• Transaction routing: The TP Monitor can increase scalability by directing transactions to
specific DBMSs.
• Managing distributed transactions: The TP Monitor can manage transactions that require
access to data held in multiple, possibly heterogeneous, DBMSs. For example, a transaction
11
Chapter 3 Database Architectures and the Web
Review Questions
3.1 What is meant by the term ‘client-server architecture’ and what are the advantages of this
approach? Compare the client-server architecture with two other architectures.
The client is a process that requires some resource, and the server provides the resource. Neither
need reside on the same machine. Advantages include:
• Better performance
• Likely reduction in hardware costs
• Reduction in communication costs
• Better consistency
See also Section 3.1.
3.2 Compare and contrast the two-tier client-server architecture for traditional DBMSs with the
three-tier client-server architecture. Why is the latter architecture more appropriate for the Web?
See Figures 3.5 and 3.6. Architecture maps quite naturally to the Web with a Web browser acting
as ‘thin’ client and Web server acting as an application server (with database server as third layer).
3.3 What is an n-tier architecture?
The three-tier architecture can be expanded to n tiers, with additional tiers providing more
flexibility and scalability.
3.4 What is middleware? Provide a classification service for middleware.
Middleware is a generic term used to describe software that mediates with other software and
allows for communication between disparate applications in a heterogeneous system. The need
for middleware arises when distributed systems become too complex to manage efficiently
without a common interface. The need to make heterogeneous systems work efficiently across a
network and be flexible enough to incorporate frequent modifications led to the development of
middleware, which hides the underlying complexity of distributed systems.
3.5 What is a TP Monitor? What advantages does a TP Monitor bring to an OLTP environment?
A TP Monitor forms the middle tier of a three-tier architecture. TP Monitors provide significant
advantages, including:
• Transaction routing: The TP Monitor can increase scalability by directing transactions to
specific DBMSs.
• Managing distributed transactions: The TP Monitor can manage transactions that require
access to data held in multiple, possibly heterogeneous, DBMSs. For example, a transaction
Loading page 12...
Database Systems: Instructor’s Guide - Part III
12
may require to update data items held in an Oracle DBMS at site 1, an Informix DBMS at
site 2, and an IMS DBMS as site 3. TP Monitors normally control transactions using the
X/Open Distributed Transaction Processing (DTP) standard. A DBMS that supports this
standard can function as a resource manager under the control of a TP Monitor acting as a
transaction manager.
• Load balancing: The TP Monitor can balance client requests across multiple DBMSs on one
or more computers by directing client service calls to the least loaded server. In addition, it
can dynamically bring in additional DBMSs as required to provide the necessary
performance.
• Funneling: In environments with a large number of users, it may sometimes be difficult for
all users to be logged on simultaneously to the DBMS. In many cases, we would find that
users generally do not need continuous access to the DBMS. Instead of each user connecting
to the DBMS, the TP Monitor can establish connections with the DBMSs as and when
required, and can funnel user requests through these connections. This allows a larger
number of users to access the available DBMSs with a potentially much smaller number of
connections, which in turn would mean less resource usage.
• Increased reliability: The TP Monitor acts as a transaction manager, performing the
necessary actions to maintain the consistency of the database, with the DBMS acting as a
resource manager. If the DBMS fails, the TP Monitor may be able to resubmit the
transaction to another DBMS or can hold the transaction until the DBMS becomes available
again.
3.6 What is a Web service?
Web services allow applications to integrate with other applications across the Internet and may
be a key technology that supports B2B (Business to Business) interaction. Unlike other Web-
based applications, Web services have no user interface and are not aimed at Web browsers. Web
services instead share business logic, data, and processes through a programmatic interface across
a network. In this way, it is the applications that interface and not the users. Developers can then
add the Web service to a Web page (or an executable program) to offer specific functionality to
users.
3.7 What technologies and standards are used to develop Web services and how do they relate to
each other?
Key to the Web services approach is the use of widely accepted technologies and standards, such
as:
• XML (extensible Markup Language).
• SOAP (Simple Object Access Protocol) is a communication protocol for exchanging
structured information over the Internet and uses a message format based on XML. It is both
platform- and language-independent.
• WSDL (Web Services Description Language) protocol, again based on XML, is used to
describe and locate a Web service.
• UDDI (Universal Discovery, Description, and Integration) protocol is a platform-
independent, XML-based registry for businesses to list themselves on the Internet. It was
designed to be interrogated by SOAP messages and to provide access to WSDL documents
12
may require to update data items held in an Oracle DBMS at site 1, an Informix DBMS at
site 2, and an IMS DBMS as site 3. TP Monitors normally control transactions using the
X/Open Distributed Transaction Processing (DTP) standard. A DBMS that supports this
standard can function as a resource manager under the control of a TP Monitor acting as a
transaction manager.
• Load balancing: The TP Monitor can balance client requests across multiple DBMSs on one
or more computers by directing client service calls to the least loaded server. In addition, it
can dynamically bring in additional DBMSs as required to provide the necessary
performance.
• Funneling: In environments with a large number of users, it may sometimes be difficult for
all users to be logged on simultaneously to the DBMS. In many cases, we would find that
users generally do not need continuous access to the DBMS. Instead of each user connecting
to the DBMS, the TP Monitor can establish connections with the DBMSs as and when
required, and can funnel user requests through these connections. This allows a larger
number of users to access the available DBMSs with a potentially much smaller number of
connections, which in turn would mean less resource usage.
• Increased reliability: The TP Monitor acts as a transaction manager, performing the
necessary actions to maintain the consistency of the database, with the DBMS acting as a
resource manager. If the DBMS fails, the TP Monitor may be able to resubmit the
transaction to another DBMS or can hold the transaction until the DBMS becomes available
again.
3.6 What is a Web service?
Web services allow applications to integrate with other applications across the Internet and may
be a key technology that supports B2B (Business to Business) interaction. Unlike other Web-
based applications, Web services have no user interface and are not aimed at Web browsers. Web
services instead share business logic, data, and processes through a programmatic interface across
a network. In this way, it is the applications that interface and not the users. Developers can then
add the Web service to a Web page (or an executable program) to offer specific functionality to
users.
3.7 What technologies and standards are used to develop Web services and how do they relate to
each other?
Key to the Web services approach is the use of widely accepted technologies and standards, such
as:
• XML (extensible Markup Language).
• SOAP (Simple Object Access Protocol) is a communication protocol for exchanging
structured information over the Internet and uses a message format based on XML. It is both
platform- and language-independent.
• WSDL (Web Services Description Language) protocol, again based on XML, is used to
describe and locate a Web service.
• UDDI (Universal Discovery, Description, and Integration) protocol is a platform-
independent, XML-based registry for businesses to list themselves on the Internet. It was
designed to be interrogated by SOAP messages and to provide access to WSDL documents
Loading page 13...
Database Systems: Instructor’s Guide - Part III
13
describing the protocol bindings and message formats required to interact with the Web
services listed in its directory.
Figure 3.9 illustrates the relationship between these technologies. From the database perspective,
Web services can be used both from within the database (to invoke an external Web service as a
consumer) and the Web service itself can access its own database (as a provider) to maintain the
data required to provide the requested service.
3.8 What is a service-oriented architecture?
The SOA approach attempts to design loosely coupled and autonomous services that can be
combined to provide flexible composite business processes and applications. SOA principles
provide a unique design approach for building Web services for SOA:
• loose coupling: services must be designed to interact on a loosely coupled basis;
• reusability: logic that can potentially be reused is designed as a separate service;
• contract: services adhere to a communications contract that defines the information exchange
and any additional service description information, specified by one or more service
description documents;
• abstraction: beyond what is described in the service contract, services hide logic from the
outside world;
• composability: services may compose other services, so that logic can be represented at
different levels of granularity thereby promoting reusability and the creation of abstraction
layers;
• autonomy: services have control over the logic they encapsulate and are not dependent upon
other services to execute this governance;
• stateless: services should not be required to manage state information, as this can affect their
ability to remain loosely-coupled;
• discoverability: services are designed to be outwardly descriptive so that they can be found
and assessed via available discovery mechanisms.
3.9 Provide an example of a service-oriented architecture.
An example of SOA is shown in Figure 3.10(b).
3.10 What is Cloud computing?
Cloud computing: A model for enabling ubiquitous, convenient, on-demand network access
to a shared pool of configurable computing resources (e.g., networks, servers, storage,
applications, and services) that can be rapidly provisioned and released with minimal
management effort or service provider interaction.
See Section 3.5.
3.11 Discuss the five essential characteristics of cloud computing.
The essential characteristics are:
13
describing the protocol bindings and message formats required to interact with the Web
services listed in its directory.
Figure 3.9 illustrates the relationship between these technologies. From the database perspective,
Web services can be used both from within the database (to invoke an external Web service as a
consumer) and the Web service itself can access its own database (as a provider) to maintain the
data required to provide the requested service.
3.8 What is a service-oriented architecture?
The SOA approach attempts to design loosely coupled and autonomous services that can be
combined to provide flexible composite business processes and applications. SOA principles
provide a unique design approach for building Web services for SOA:
• loose coupling: services must be designed to interact on a loosely coupled basis;
• reusability: logic that can potentially be reused is designed as a separate service;
• contract: services adhere to a communications contract that defines the information exchange
and any additional service description information, specified by one or more service
description documents;
• abstraction: beyond what is described in the service contract, services hide logic from the
outside world;
• composability: services may compose other services, so that logic can be represented at
different levels of granularity thereby promoting reusability and the creation of abstraction
layers;
• autonomy: services have control over the logic they encapsulate and are not dependent upon
other services to execute this governance;
• stateless: services should not be required to manage state information, as this can affect their
ability to remain loosely-coupled;
• discoverability: services are designed to be outwardly descriptive so that they can be found
and assessed via available discovery mechanisms.
3.9 Provide an example of a service-oriented architecture.
An example of SOA is shown in Figure 3.10(b).
3.10 What is Cloud computing?
Cloud computing: A model for enabling ubiquitous, convenient, on-demand network access
to a shared pool of configurable computing resources (e.g., networks, servers, storage,
applications, and services) that can be rapidly provisioned and released with minimal
management effort or service provider interaction.
See Section 3.5.
3.11 Discuss the five essential characteristics of cloud computing.
The essential characteristics are:
Loading page 14...
Database Systems: Instructor’s Guide - Part III
14
• On-demand self-service: consumers can obtain, configure and deploy cloud services
themselves using cloud service catalogues, without requiring the assistance of anyone
from the cloud provider.
• Broad network access: it is network based, and accessible from anywhere, from any
standardized platform (e.g. desktop computers, laptops, mobile devices).
• Resource pooling. The cloud provider’s computing resources are pooled to serve multiple
consumers, with different physical and virtual resources dynamically assigned and
reassigned according to consumer demand. Examples of resources include storage,
processing, memory, and network bandwidth.
• Rapid elasticity. Resource pooling avoids the capital expenditure required for the
establishment of network and computing infrastructure. By outsourcing to a cloud,
consumers can cater for the spikes in demand for their services by using the cloud
provider’s computing capacity and the risk of outages and service interruptions are
significantly reduced. Moreover, capabilities can be elastically provisioned and released,
in some cases automatically, to scale rapidly based on demand. To the consumer, the
capabilities available for provisioning often appear to be unlimited and can be called on in
any quantity at any time.
• Measured service. Cloud systems automatically control and optimize resource use by
leveraging a metering capability at some level of abstraction appropriate to the type of
service (e.g., storage, processing, bandwidth, and active user accounts). Resource usage
can be monitored, controlled, and charged for.
See Section 3.5.
3.12 Discuss the three main service models of cloud computing.
• Software as a Service (SaaS): software and associated data are centrally hosted on the
cloud.
• Platform as a Service (PaaS): a computing platform that allows the creation of web
applications quickly and easily and without the complexity of buying and maintaining the
software and infrastructure underneath it. Sometimes, PaaS is used to extend the
capabilities of applications developed as SaaS. While earlier application development
required hardware, an operating system, a database, middleware, Web servers, and other
software, with the PaaS model only the knowledge to integrate them is required. The rest
is taken care of by the PaaS provider.
• Infrastructure as a Service (IaaS): delivers servers, storage, network and operating
systems – typically a platform virtualization environment – to consumers as an on-
demand service, in a single bundle and billed according to usage. A popular use of IaaS is
in hosting websites, where the in-house infrastructure is not burdened with this task but
left free to manage the business.
3.13 Compare and contrast the four main deployment models for the cloud.
The four main deployment models for the cloud are:
14
• On-demand self-service: consumers can obtain, configure and deploy cloud services
themselves using cloud service catalogues, without requiring the assistance of anyone
from the cloud provider.
• Broad network access: it is network based, and accessible from anywhere, from any
standardized platform (e.g. desktop computers, laptops, mobile devices).
• Resource pooling. The cloud provider’s computing resources are pooled to serve multiple
consumers, with different physical and virtual resources dynamically assigned and
reassigned according to consumer demand. Examples of resources include storage,
processing, memory, and network bandwidth.
• Rapid elasticity. Resource pooling avoids the capital expenditure required for the
establishment of network and computing infrastructure. By outsourcing to a cloud,
consumers can cater for the spikes in demand for their services by using the cloud
provider’s computing capacity and the risk of outages and service interruptions are
significantly reduced. Moreover, capabilities can be elastically provisioned and released,
in some cases automatically, to scale rapidly based on demand. To the consumer, the
capabilities available for provisioning often appear to be unlimited and can be called on in
any quantity at any time.
• Measured service. Cloud systems automatically control and optimize resource use by
leveraging a metering capability at some level of abstraction appropriate to the type of
service (e.g., storage, processing, bandwidth, and active user accounts). Resource usage
can be monitored, controlled, and charged for.
See Section 3.5.
3.12 Discuss the three main service models of cloud computing.
• Software as a Service (SaaS): software and associated data are centrally hosted on the
cloud.
• Platform as a Service (PaaS): a computing platform that allows the creation of web
applications quickly and easily and without the complexity of buying and maintaining the
software and infrastructure underneath it. Sometimes, PaaS is used to extend the
capabilities of applications developed as SaaS. While earlier application development
required hardware, an operating system, a database, middleware, Web servers, and other
software, with the PaaS model only the knowledge to integrate them is required. The rest
is taken care of by the PaaS provider.
• Infrastructure as a Service (IaaS): delivers servers, storage, network and operating
systems – typically a platform virtualization environment – to consumers as an on-
demand service, in a single bundle and billed according to usage. A popular use of IaaS is
in hosting websites, where the in-house infrastructure is not burdened with this task but
left free to manage the business.
3.13 Compare and contrast the four main deployment models for the cloud.
The four main deployment models for the cloud are:
Loading page 15...
Database Systems: Instructor’s Guide - Part III
15
• Private cloud: cloud infrastructure operated solely for a single organization, whether
managed internally by the organization, a third party, or some combination of them, and it
may be hosted internally or externally.
• Community cloud: cloud infrastructure is shared for exclusive use by a specific
community of organizations that have common concerns (e.g., security requirements,
compliance, jurisdiction). It may be owned, managed, and operated by one or more of the
organizations in the community, a third party, or some combination of them, and it may
be hosted internally or externally.
• Public cloud: cloud infrastructure is made available to the general public by a service
provider. These services are free or offered on a pay-per-use model. It may be owned,
managed, and operated by a business, academic, or government organization, or some
combination of these. It exists on the premises of the cloud provider.
• Hybrid cloud: cloud infrastructure is a composition of two or more distinct cloud
infrastructures (private, community, or public) that remain unique entities, but are bound
together by standardized or proprietary technology, offering the benefits of multiple
deployment models.
3.14 What are the difference between Data as a service (DaaS) and Database as a service
(DBaaS)?
• DaaS: offers the ability to define data in the cloud and subsequently query that data on
demand. Unlike traditional database solutions, DaaS does not implement typical DBMS
interfaces such as SQL (see Chapter 6). Instead, the data is accessed via a common set of
APIs. DaaS enables any organization with valuable data to create new revenue lines based
on data they already own. Examples of DaaS are Urban Mapping, a geography data
service, provides data for customers to embed into their own websites and applications;
Xignite makes financial data available to customers; and Hoovers, a Dun & Bradstreet
company, provides business data on various organizations.
• DBaaS: offers full database functionality to application developers. In DBaaS, a
management layer is responsible for the continuous monitoring and configuring of the
database to achieve optimized scaling, high availability, multi-tenancy (that is, serving
multiple client organizations), and effective resource allocation in the cloud, thereby
sparing the developer from ongoing database administration tasks.
See Section 3.5.2.
3.15 Discuss the different architectural models for Database-as-a-service.
• Separate servers
• Shared Server, Separate Database Server Process
• Shared Database Server, Separate Databases
• Shared Database, Separate Schema
• Shared Database, Shared Schema
See Section 3.5.2.
15
• Private cloud: cloud infrastructure operated solely for a single organization, whether
managed internally by the organization, a third party, or some combination of them, and it
may be hosted internally or externally.
• Community cloud: cloud infrastructure is shared for exclusive use by a specific
community of organizations that have common concerns (e.g., security requirements,
compliance, jurisdiction). It may be owned, managed, and operated by one or more of the
organizations in the community, a third party, or some combination of them, and it may
be hosted internally or externally.
• Public cloud: cloud infrastructure is made available to the general public by a service
provider. These services are free or offered on a pay-per-use model. It may be owned,
managed, and operated by a business, academic, or government organization, or some
combination of these. It exists on the premises of the cloud provider.
• Hybrid cloud: cloud infrastructure is a composition of two or more distinct cloud
infrastructures (private, community, or public) that remain unique entities, but are bound
together by standardized or proprietary technology, offering the benefits of multiple
deployment models.
3.14 What are the difference between Data as a service (DaaS) and Database as a service
(DBaaS)?
• DaaS: offers the ability to define data in the cloud and subsequently query that data on
demand. Unlike traditional database solutions, DaaS does not implement typical DBMS
interfaces such as SQL (see Chapter 6). Instead, the data is accessed via a common set of
APIs. DaaS enables any organization with valuable data to create new revenue lines based
on data they already own. Examples of DaaS are Urban Mapping, a geography data
service, provides data for customers to embed into their own websites and applications;
Xignite makes financial data available to customers; and Hoovers, a Dun & Bradstreet
company, provides business data on various organizations.
• DBaaS: offers full database functionality to application developers. In DBaaS, a
management layer is responsible for the continuous monitoring and configuring of the
database to achieve optimized scaling, high availability, multi-tenancy (that is, serving
multiple client organizations), and effective resource allocation in the cloud, thereby
sparing the developer from ongoing database administration tasks.
See Section 3.5.2.
3.15 Discuss the different architectural models for Database-as-a-service.
• Separate servers
• Shared Server, Separate Database Server Process
• Shared Database Server, Separate Databases
• Shared Database, Separate Schema
• Shared Database, Shared Schema
See Section 3.5.2.
Loading page 16...
Database Systems: Instructor’s Guide - Part III
16
3.16 Describe the main components in a DBMS.
The major software components in a DBMS environment are depicted in Figure 3.20. The main
components in a DBMS are:
• Query processor. This is a major DBMS component that transforms queries into a series of
low-level instructions directed to the database manager.
• Database manager (DM). The DM interfaces with user-submitted application programs and
queries. The DM accepts queries and examines the external and conceptual schemas to
determine what conceptual records are required to satisfy the request. The DM then places a
call to the file manager to perform the request.
• File manager. The file manager manipulates the underlying storage files and manages the
allocation of storage space on disk. It establishes and maintains the list of structures and
indexes defined in the internal schema. If hashed files are used, it calls on the hashing
functions to generate record addresses. However, the file manager does not directly manage
the physical input and output of data. Rather, it passes the requests on to the appropriate
access methods, which either read data from or write data into the system buffer (or cache).
• DML preprocessor. This module converts DML statements embedded in an application
program into standard function calls in the host language. The DML preprocessor must
interact with the query processor to generate the appropriate code.
• DDL compiler. The DDL compiler converts DDL statements into a set of tables containing
metadata. These tables are then stored in the system catalog while control information is
stored in data file headers.
• Catalog manager. The catalog manager manages access to and maintains the system catalog.
The system catalog is accessed by most DBMS components.
3.17 Describe the internal architecture of Oracle.
Oracle is based on the client–server architecture. The Oracle server consists of the database (the
raw data, including log and control files) and the instance (the processes and system memory on
the server that provide access to the database). An instance can connect to only one database. The
database consists of a logical structure, such as the database schema, and a physical structure,
containing the files that make up an Oracle database.
Exercises
3.18 Examine the documentation sets of Microsoft SQL Server, Oracle, and IBM’s DB2 system to
identify their support for the following:
(a) client–server architecture
(b) Web services
(c) service-oriented architecture
Microsoft SQL Server
Microsoft SQL Server is designed to work effectively in a number of environments:
• As a two-tier or multitier client/server database system
• As a desktop database system
16
3.16 Describe the main components in a DBMS.
The major software components in a DBMS environment are depicted in Figure 3.20. The main
components in a DBMS are:
• Query processor. This is a major DBMS component that transforms queries into a series of
low-level instructions directed to the database manager.
• Database manager (DM). The DM interfaces with user-submitted application programs and
queries. The DM accepts queries and examines the external and conceptual schemas to
determine what conceptual records are required to satisfy the request. The DM then places a
call to the file manager to perform the request.
• File manager. The file manager manipulates the underlying storage files and manages the
allocation of storage space on disk. It establishes and maintains the list of structures and
indexes defined in the internal schema. If hashed files are used, it calls on the hashing
functions to generate record addresses. However, the file manager does not directly manage
the physical input and output of data. Rather, it passes the requests on to the appropriate
access methods, which either read data from or write data into the system buffer (or cache).
• DML preprocessor. This module converts DML statements embedded in an application
program into standard function calls in the host language. The DML preprocessor must
interact with the query processor to generate the appropriate code.
• DDL compiler. The DDL compiler converts DDL statements into a set of tables containing
metadata. These tables are then stored in the system catalog while control information is
stored in data file headers.
• Catalog manager. The catalog manager manages access to and maintains the system catalog.
The system catalog is accessed by most DBMS components.
3.17 Describe the internal architecture of Oracle.
Oracle is based on the client–server architecture. The Oracle server consists of the database (the
raw data, including log and control files) and the instance (the processes and system memory on
the server that provide access to the database). An instance can connect to only one database. The
database consists of a logical structure, such as the database schema, and a physical structure,
containing the files that make up an Oracle database.
Exercises
3.18 Examine the documentation sets of Microsoft SQL Server, Oracle, and IBM’s DB2 system to
identify their support for the following:
(a) client–server architecture
(b) Web services
(c) service-oriented architecture
Microsoft SQL Server
Microsoft SQL Server is designed to work effectively in a number of environments:
• As a two-tier or multitier client/server database system
• As a desktop database system
Loading page 17...
Database Systems: Instructor’s Guide - Part III
17
In a two-tier client/server system, users run an application on their local computer, known as a
client, which connects over a network to the server running SQL Server. The client application
runs both business logic and the code to display output to the user, and is also known as a
thick client.
SQL Server 2008 enables data to be consumed from custom applications developed using
Microsoft .NET and Visual Studio and from within service-oriented architectures (SOA) and
business processes through Microsoft BizTalk Server.
Web services extend the World Wide Web infrastructure to provide the means for software to
connect to other software applications. Applications access Web services via ubiquitous Web
protocols and data formats such as HTTP, XML, and SOAP, with no need to worry about how
each Web service is implemented. Web services combine the best aspects of component-based
development and the Web, and are a cornerstone of the Microsoft .NET programming model.
Microsoft System Center plays a central role in Microsoft's vision to help IT organizations
benefit from self-managing, dynamic systems. System Center solutions are tuned to simplify
management of the systems and applications your company already has implemented,
including Microsoft SQL Server, Microsoft Exchange Server, Microsoft Biztalk Server,
Internet Information Services and the Microsoft .NET Framework.
Oracle
In the Oracle database system environment, the database application and the database are
separated into two parts: a front-end or client portion, and a back-end or server portion--hence
the term client/server architecture. The client runs the database application that accesses
database information and interacts with a user through the keyboard, screen, and pointing
device, such as a mouse. The server runs the Oracle software and handles the functions
required for concurrent, shared data access to an Oracle database.
Although the client application and Oracle can be run on the same computer, greater
efficiency can often be achieved when the client portions and server portion are run by
different computers connected through a network. The following sections discuss possible
variations in the Oracle client/server architecture.
Most organizations implement service-oriented architecture (SOA) with the hope of gaining
more business agility through reuse of shared services. As reuse begins to take hold within the
organization, however, it becomes critical to manage consumption of services or your SOA
can quickly spin out of control. Oracle Web Services Manager provides a solution for
governing the interactions with shared services through security and operational policy
management and enforcement to ensure service reuse remains under control.
Leading companies are gaining operational efficiencies and business agility through adaptable,
re-usable business processes and services built on a truly flexible Service-Oriented
Architecture (SOA) foundation. Oracle SOA products allow you to build, deploy, and manage
SOA with integrated, best-in-class technology that provides:
17
In a two-tier client/server system, users run an application on their local computer, known as a
client, which connects over a network to the server running SQL Server. The client application
runs both business logic and the code to display output to the user, and is also known as a
thick client.
SQL Server 2008 enables data to be consumed from custom applications developed using
Microsoft .NET and Visual Studio and from within service-oriented architectures (SOA) and
business processes through Microsoft BizTalk Server.
Web services extend the World Wide Web infrastructure to provide the means for software to
connect to other software applications. Applications access Web services via ubiquitous Web
protocols and data formats such as HTTP, XML, and SOAP, with no need to worry about how
each Web service is implemented. Web services combine the best aspects of component-based
development and the Web, and are a cornerstone of the Microsoft .NET programming model.
Microsoft System Center plays a central role in Microsoft's vision to help IT organizations
benefit from self-managing, dynamic systems. System Center solutions are tuned to simplify
management of the systems and applications your company already has implemented,
including Microsoft SQL Server, Microsoft Exchange Server, Microsoft Biztalk Server,
Internet Information Services and the Microsoft .NET Framework.
Oracle
In the Oracle database system environment, the database application and the database are
separated into two parts: a front-end or client portion, and a back-end or server portion--hence
the term client/server architecture. The client runs the database application that accesses
database information and interacts with a user through the keyboard, screen, and pointing
device, such as a mouse. The server runs the Oracle software and handles the functions
required for concurrent, shared data access to an Oracle database.
Although the client application and Oracle can be run on the same computer, greater
efficiency can often be achieved when the client portions and server portion are run by
different computers connected through a network. The following sections discuss possible
variations in the Oracle client/server architecture.
Most organizations implement service-oriented architecture (SOA) with the hope of gaining
more business agility through reuse of shared services. As reuse begins to take hold within the
organization, however, it becomes critical to manage consumption of services or your SOA
can quickly spin out of control. Oracle Web Services Manager provides a solution for
governing the interactions with shared services through security and operational policy
management and enforcement to ensure service reuse remains under control.
Leading companies are gaining operational efficiencies and business agility through adaptable,
re-usable business processes and services built on a truly flexible Service-Oriented
Architecture (SOA) foundation. Oracle SOA products allow you to build, deploy, and manage
SOA with integrated, best-in-class technology that provides:
Loading page 18...
Database Systems: Instructor’s Guide - Part III
18
• Comprehensive and Pre-Integrated SOA Platform—Complete set of service and
process infrastructure components for building, deploying, and managing SOAs
• Closed-Loop Governance—Comprehensive, end-to-end lifecycle governance of
services
• Extreme performance and scalability—In-memory transactions, real-time event
processing, and high-volume data transfer on top of a highly scalable application
server
• Integrated Security—Centralized policy management, enterprise-grade, end-to-end
security
Oracle SOA Suite, which now includes the former BEA AquaLogic Service Bus, is Oracle
Fusion Middleware's strategic product for SOA. Oracle plans to continue to develop and
support Oracle WebLogic Integration, and expects to converge this product with Oracle's
strategic solutions over time. Existing deployments of this product will benefit from
complementary products such as Oracle SOA Suite.
DB2
Local and remote application processes can work with the same database. A remote
application is one that initiates a database action from a machine that is remote from the
database machine. Local applications are directly attached to the database at the server
machine.
Web service providers are described by Web Services Description Language (WSDL)
documents. You can use the Web services wrapper to access Web service providers.
A Web services client application can obtain access to a DB2® Version 9 database with a
Web services description language (WSDL) interface. You can create a WSDL interface to
DB2® Version 9 data by using the Web services Object Runtime Framework (WORF), also
known as Document Access Definition Extension (DADX) files. After you define the
operations to access DB2 data with the DADX file, then you deploy the DADX file and its
runtime environment (IBM® Web Service SOAP provider or Apache Axis version 1.2) to a
supported Java™ Web application server environment (Apache Jakarta Tomcat or IBM®
WebSphere® Application Server). After you have the DB2 Web service tested and deployed,
any Web services client can start using the DB2 Web service.
Web services consumer - the user-defined functions
When DB2 Version 9 becomes the consumer, Web services can take advantage of the
optimization that is built within the database. By using SQL statements, you can consume and
integrate Web services data. By using SQL to access Web services data, you can reduce some
application programming efforts because the data can be manipulated within the context of an
SQL statement before that data is returned to the client application. You can convert an
existing WSDL interface into a DB2 table or scalar function by using tools that are provided
in WebSphere® Studio version 5 and later. During the execution of an SQL statement, you
establish a connection with the Web service provider, and then you receive a response
document as a relational table or a scalar value.
18
• Comprehensive and Pre-Integrated SOA Platform—Complete set of service and
process infrastructure components for building, deploying, and managing SOAs
• Closed-Loop Governance—Comprehensive, end-to-end lifecycle governance of
services
• Extreme performance and scalability—In-memory transactions, real-time event
processing, and high-volume data transfer on top of a highly scalable application
server
• Integrated Security—Centralized policy management, enterprise-grade, end-to-end
security
Oracle SOA Suite, which now includes the former BEA AquaLogic Service Bus, is Oracle
Fusion Middleware's strategic product for SOA. Oracle plans to continue to develop and
support Oracle WebLogic Integration, and expects to converge this product with Oracle's
strategic solutions over time. Existing deployments of this product will benefit from
complementary products such as Oracle SOA Suite.
DB2
Local and remote application processes can work with the same database. A remote
application is one that initiates a database action from a machine that is remote from the
database machine. Local applications are directly attached to the database at the server
machine.
Web service providers are described by Web Services Description Language (WSDL)
documents. You can use the Web services wrapper to access Web service providers.
A Web services client application can obtain access to a DB2® Version 9 database with a
Web services description language (WSDL) interface. You can create a WSDL interface to
DB2® Version 9 data by using the Web services Object Runtime Framework (WORF), also
known as Document Access Definition Extension (DADX) files. After you define the
operations to access DB2 data with the DADX file, then you deploy the DADX file and its
runtime environment (IBM® Web Service SOAP provider or Apache Axis version 1.2) to a
supported Java™ Web application server environment (Apache Jakarta Tomcat or IBM®
WebSphere® Application Server). After you have the DB2 Web service tested and deployed,
any Web services client can start using the DB2 Web service.
Web services consumer - the user-defined functions
When DB2 Version 9 becomes the consumer, Web services can take advantage of the
optimization that is built within the database. By using SQL statements, you can consume and
integrate Web services data. By using SQL to access Web services data, you can reduce some
application programming efforts because the data can be manipulated within the context of an
SQL statement before that data is returned to the client application. You can convert an
existing WSDL interface into a DB2 table or scalar function by using tools that are provided
in WebSphere® Studio version 5 and later. During the execution of an SQL statement, you
establish a connection with the Web service provider, and then you receive a response
document as a relational table or a scalar value.
Loading page 19...
Database Systems: Instructor’s Guide - Part III
19
Web service providers are described by Web Services Description Language (WSDL)
documents. You can use the Web services wrapper to access Web service providers. Figure 1
shows the architecture of Web services.
1. A Web service provider implements a service and publishes the WSDL information to a
service broker, such as UDDI.
2. The service consumer can then use the service broker to find a Web service provider.
3. When the service consumer finds a Web service provider, the service consumer binds to
the service provider so that the consumer can use the Web service.
4. The consumer invokes the service by exchanging SOAP (simple object access protocol)
messages between the requester and provider.
Figure 1. Web services: a service-oriented architecture
The SOAP specification defines the layout of an XML-based message. A SOAP message is
contained in a SOAP envelope. The envelope consists of an optional SOAP header and a
mandatory SOAP body. The SOAP header can contain information about the message, such as
encryption information or authentication information. The SOAP body contains the message.
The SOAP specification also defines a default encoding for programming language bindings,
which is called the SOAP encoding.
3.19 Search the Web for a number of Web services other than the ones discussed in Section 3.2.
What do these services have in common? Identify whether the services access a database.
The Google Maps API provides Web services to make use of the Google Maps application.
The following Web site contains a number of examples: http://maps.forum.nu/ The following
site contains information on using a database with Google maps:
http://groups.google.com/group/Google-Maps-API/web/using-databases-with-gmaps-apps
Amazon provides a number of Web services discussed on the following Web site:
http://aws.amazon.com/
19
Web service providers are described by Web Services Description Language (WSDL)
documents. You can use the Web services wrapper to access Web service providers. Figure 1
shows the architecture of Web services.
1. A Web service provider implements a service and publishes the WSDL information to a
service broker, such as UDDI.
2. The service consumer can then use the service broker to find a Web service provider.
3. When the service consumer finds a Web service provider, the service consumer binds to
the service provider so that the consumer can use the Web service.
4. The consumer invokes the service by exchanging SOAP (simple object access protocol)
messages between the requester and provider.
Figure 1. Web services: a service-oriented architecture
The SOAP specification defines the layout of an XML-based message. A SOAP message is
contained in a SOAP envelope. The envelope consists of an optional SOAP header and a
mandatory SOAP body. The SOAP header can contain information about the message, such as
encryption information or authentication information. The SOAP body contains the message.
The SOAP specification also defines a default encoding for programming language bindings,
which is called the SOAP encoding.
3.19 Search the Web for a number of Web services other than the ones discussed in Section 3.2.
What do these services have in common? Identify whether the services access a database.
The Google Maps API provides Web services to make use of the Google Maps application.
The following Web site contains a number of examples: http://maps.forum.nu/ The following
site contains information on using a database with Google maps:
http://groups.google.com/group/Google-Maps-API/web/using-databases-with-gmaps-apps
Amazon provides a number of Web services discussed on the following Web site:
http://aws.amazon.com/
Loading page 20...
SOLUTIONS TO REVIEW QUESTIONS
AND EXERCISES
FOR PART 2 – THE RELATIONAL MODEL AND LANGUAGES
(CHAPTERS 4 – 9)
AND EXERCISES
FOR PART 2 – THE RELATIONAL MODEL AND LANGUAGES
(CHAPTERS 4 – 9)
Loading page 21...
Database Systems: Instructor’s Guide - Part III
2
Solutions to Review Questions and Exercises
Chapter 4 The Relational Model ...................................................................................................................... 3
Chapter 5 Relational Algebra and Relational Calculus.................................................................................... 6
Chapter 6 SQL: Data Manipulation ............................................................................................................... 22
Chapter 7 SQL: Data Definition..................................................................................................................... 32
Chapter 8 Advanced SQL .............................................................................................................................. 42
Chapter 9 Object-Relational DBMSs ............................................................................................................. 52
2
Solutions to Review Questions and Exercises
Chapter 4 The Relational Model ...................................................................................................................... 3
Chapter 5 Relational Algebra and Relational Calculus.................................................................................... 6
Chapter 6 SQL: Data Manipulation ............................................................................................................... 22
Chapter 7 SQL: Data Definition..................................................................................................................... 32
Chapter 8 Advanced SQL .............................................................................................................................. 42
Chapter 9 Object-Relational DBMSs ............................................................................................................. 52
Loading page 22...
Database Systems: Instructor’s Guide - Part III
3
Chapter 4 The Relational Model
Review Questions
4.1 Discuss each of the following concepts in the context of the relational data model:
(a) Relation A table with columns and rows.
(b) Attribute A named column of a relation.
(c) Domain The set of allowable values for one or more attributes.
(d) Tuple A row of a relation.
(e) Intension The structure of a relation together with a specification of the domains
and any other restrictions on possible values.
Extension An instance of the tuples of a relation.
(f) Degree The number of attributes in a relation.
Cardinality The number of tuples in a relation.
Each term defined in Section 4.2.1.
4.2 Describe the relationship between mathematical relations and relations in the relational data
model?
Let D1, D2, . . . , Dn be n sets. Their Cartesian product is defined as:
D1 D2 . . . Dn = {(d1, d2, . . . , dn) | d1 D1, d2 D2, . . . , dn Dn}
Any set of n-tuples from this Cartesian product is a relation on the n sets. Now let A1, A2, . . .,
An be attributes with domains D1, D2, . . . , Dn. Then the set {A1:D1, A2:D2, . . . , An:Dn} is
a relation schema. A relation R defined by a relation schema S is a set of mappings from the
attribute names to their corresponding domains. Thus, relation R is a set of n-tuples:
(A1:d1, A2:d2, . . . , An:dn) such that d1 D1, d2 D2, . . . , dn Dn
Each element in the n-tuple consists of an attribute and a value for that attribute.
Discussed fully in Sections 4.2.2 and 4.2.3.
4.3 Describe the differences between a relation and a relation schema. What is a relational
database schema?
A relation schema is a named relation defined by a set of attribute and domain name pairs. A
relational database schema is a set of relation schemas, each with a distinct name. Discussed in
Section 4.2.3.
4.4 Discuss the properties of a relation.
A relation has the following properties:
• has a name that is distinct from all other relation names in the relational schema;
• each cell contains exactly one atomic (single) value;
3
Chapter 4 The Relational Model
Review Questions
4.1 Discuss each of the following concepts in the context of the relational data model:
(a) Relation A table with columns and rows.
(b) Attribute A named column of a relation.
(c) Domain The set of allowable values for one or more attributes.
(d) Tuple A row of a relation.
(e) Intension The structure of a relation together with a specification of the domains
and any other restrictions on possible values.
Extension An instance of the tuples of a relation.
(f) Degree The number of attributes in a relation.
Cardinality The number of tuples in a relation.
Each term defined in Section 4.2.1.
4.2 Describe the relationship between mathematical relations and relations in the relational data
model?
Let D1, D2, . . . , Dn be n sets. Their Cartesian product is defined as:
D1 D2 . . . Dn = {(d1, d2, . . . , dn) | d1 D1, d2 D2, . . . , dn Dn}
Any set of n-tuples from this Cartesian product is a relation on the n sets. Now let A1, A2, . . .,
An be attributes with domains D1, D2, . . . , Dn. Then the set {A1:D1, A2:D2, . . . , An:Dn} is
a relation schema. A relation R defined by a relation schema S is a set of mappings from the
attribute names to their corresponding domains. Thus, relation R is a set of n-tuples:
(A1:d1, A2:d2, . . . , An:dn) such that d1 D1, d2 D2, . . . , dn Dn
Each element in the n-tuple consists of an attribute and a value for that attribute.
Discussed fully in Sections 4.2.2 and 4.2.3.
4.3 Describe the differences between a relation and a relation schema. What is a relational
database schema?
A relation schema is a named relation defined by a set of attribute and domain name pairs. A
relational database schema is a set of relation schemas, each with a distinct name. Discussed in
Section 4.2.3.
4.4 Discuss the properties of a relation.
A relation has the following properties:
• has a name that is distinct from all other relation names in the relational schema;
• each cell contains exactly one atomic (single) value;
Loading page 23...
Database Systems: Instructor’s Guide - Part III
4
• each attribute has a distinct name;
• the values of an attribute are all from the same domain;
• each tuple is distinct; there are no duplicate tuples;
• the order of attributes has no significance;
• the order of tuples has no significance, theoretically. (However, in practice, the order may
affect the efficiency of accessing tuples.)
Discussed fully in Section 4.2.4.
4.5 Discuss the differences between the candidate keys and the primary key of a relation. Explain
what is meant by a foreign key. How do foreign keys of relations relate to candidate keys? Give
examples to illustrate your answer.
The primary key is the candidate key that is selected to identify tuples uniquely within a relation.
A foreign key is an attribute or set of attributes within one relation that matches the candidate key
of some (possibly the same) relation. Discussed in Section 4.2.5.
4.6 Define the two principal integrity rules for the relational model. Discuss why it is desirable to
enforce these rules.
Two rules are Entity Integrity (Section 4.3.2) and Referential Integrity (Section 4.3.3).
4.7 What is a view? Discuss the difference between a view and a base relation.
View is the dynamic result of one or more relational operations operating on the base relations to
produce another relation. Base relation exists as a set of data in the database. A view does not
contain any data, rather a view is defined as a query on one or more base relations and a query on
the view is translated into a query on the associated base relations. See Section 4.4.
Exercises
The following tables form part of a database held in a relational DBMS:-
Hotel (hotelNo, hotelName, city)
Room (roomNo, hotelNo, type, price)
Booking (hotelNo, guestNo, dateFrom, dateTo, roomNo)
Guest (guestNo, guestName, guestAddress)
where Hotel contains hotel details and hotelNo is the primary key;
Room contains room details for each hotel and (roomNo, hotelNo) forms the primary key;
Booking contains details of the bookings and (hotelNo, guestNo, dateFrom) forms the primary
key;
and Guest contains guest details and guestNo is the primary key.
4
• each attribute has a distinct name;
• the values of an attribute are all from the same domain;
• each tuple is distinct; there are no duplicate tuples;
• the order of attributes has no significance;
• the order of tuples has no significance, theoretically. (However, in practice, the order may
affect the efficiency of accessing tuples.)
Discussed fully in Section 4.2.4.
4.5 Discuss the differences between the candidate keys and the primary key of a relation. Explain
what is meant by a foreign key. How do foreign keys of relations relate to candidate keys? Give
examples to illustrate your answer.
The primary key is the candidate key that is selected to identify tuples uniquely within a relation.
A foreign key is an attribute or set of attributes within one relation that matches the candidate key
of some (possibly the same) relation. Discussed in Section 4.2.5.
4.6 Define the two principal integrity rules for the relational model. Discuss why it is desirable to
enforce these rules.
Two rules are Entity Integrity (Section 4.3.2) and Referential Integrity (Section 4.3.3).
4.7 What is a view? Discuss the difference between a view and a base relation.
View is the dynamic result of one or more relational operations operating on the base relations to
produce another relation. Base relation exists as a set of data in the database. A view does not
contain any data, rather a view is defined as a query on one or more base relations and a query on
the view is translated into a query on the associated base relations. See Section 4.4.
Exercises
The following tables form part of a database held in a relational DBMS:-
Hotel (hotelNo, hotelName, city)
Room (roomNo, hotelNo, type, price)
Booking (hotelNo, guestNo, dateFrom, dateTo, roomNo)
Guest (guestNo, guestName, guestAddress)
where Hotel contains hotel details and hotelNo is the primary key;
Room contains room details for each hotel and (roomNo, hotelNo) forms the primary key;
Booking contains details of the bookings and (hotelNo, guestNo, dateFrom) forms the primary
key;
and Guest contains guest details and guestNo is the primary key.
Loading page 24...
Database Systems: Instructor’s Guide - Part III
5
4.8 Identify the foreign keys in this schema. Explain how the entity and referential integrity rules
apply to these relations.
For each relation, the primary key must not contain any nulls.
Room is related to Hotel through the attribute hotelNo. Therefore, the hotelNo in Room should
either be null or contain the number of an existing hotel in the Hotel relation. In this case study, it
would probably be unacceptable to have a hotelNo in Room with a null value.
Booking is related to Hotel through the attribute hotelNo. Therefore, the hotelNo in Booking
should either be null or contain the number of an existing hotel in the Hotel relation. However,
because hotelNo is also part of the primary key, a null value for this attribute would be
unacceptable. Similarly for guestNo. Booking is also related to Room through the attribute
roomNo.
4.9 Produce some sample tables for these relations that observe the relational integrity rules.
Suggest some general constraints that would be appropriate for this schema.
Student should provide some sample tables, observing entity and referential integrity. In
particular, ensure the uniqueness for the composite primary keys of the Room and Booking tables.
Some general constraints may be:
• There can be no two bookings for the same room in the same hotel on the same day.
• For the Booking relation, dateFrom must be before dateTo.
• Room price must be greater than 0 and less than £200.
4.10 Analyze the RDBMSs that you are currently using. Determine the support the system provides for
primary keys, alternate keys, foreign keys, relational integrity, and views.
This is a small student project, the result of which is dependent on the system analyzed.
4.11 Implement the above schema in one of the RDBMSs you currently use. Implement, where
possible, the primary, alternate, and foreign keys, and appropriate relational integrity
constraints.
This is a small student project, the result of which is dependent on the RDBMS used. Ensure that
keys have been implemented, and that relationships have been implemented if the RDBMS
supports this.
5
4.8 Identify the foreign keys in this schema. Explain how the entity and referential integrity rules
apply to these relations.
For each relation, the primary key must not contain any nulls.
Room is related to Hotel through the attribute hotelNo. Therefore, the hotelNo in Room should
either be null or contain the number of an existing hotel in the Hotel relation. In this case study, it
would probably be unacceptable to have a hotelNo in Room with a null value.
Booking is related to Hotel through the attribute hotelNo. Therefore, the hotelNo in Booking
should either be null or contain the number of an existing hotel in the Hotel relation. However,
because hotelNo is also part of the primary key, a null value for this attribute would be
unacceptable. Similarly for guestNo. Booking is also related to Room through the attribute
roomNo.
4.9 Produce some sample tables for these relations that observe the relational integrity rules.
Suggest some general constraints that would be appropriate for this schema.
Student should provide some sample tables, observing entity and referential integrity. In
particular, ensure the uniqueness for the composite primary keys of the Room and Booking tables.
Some general constraints may be:
• There can be no two bookings for the same room in the same hotel on the same day.
• For the Booking relation, dateFrom must be before dateTo.
• Room price must be greater than 0 and less than £200.
4.10 Analyze the RDBMSs that you are currently using. Determine the support the system provides for
primary keys, alternate keys, foreign keys, relational integrity, and views.
This is a small student project, the result of which is dependent on the system analyzed.
4.11 Implement the above schema in one of the RDBMSs you currently use. Implement, where
possible, the primary, alternate, and foreign keys, and appropriate relational integrity
constraints.
This is a small student project, the result of which is dependent on the RDBMS used. Ensure that
keys have been implemented, and that relationships have been implemented if the RDBMS
supports this.
Loading page 25...
Database Systems: Instructor’s Guide - Part III
6
Chapter 5 Relational Algebra and Relational Calculus
Review Questions
5.1 What is the difference between a procedural and non-procedural language? How would you
classify the relational algebra and relational calculus?
Procedural language: a language that allows user to tell the system what data is needed and
exactly how to retrieve the data.
Non-procedural language: a language that allows user to state what data is needed rather
than how it is to be retrieved.
Informally, we may describe the relational algebra as a (high-level) procedural language: it
can be used to tell the DBMS how to build a new relation from one or more relations in the
database. Again, informally, we may describe the relational calculus as a non-procedural
language: it can be used to formulate the definition of a relation in terms of one or more
database relations.
5.2 Explain the following terms:
• relationally complete;
A language that can be used to produce any relation that can be derived using the
relational calculus is said to be relationally complete.
• closure of relational operations.
The relational algebra is a theoretical language with operations that work on one or more
relations to define another relation without changing the original relation(s). Thus, both
the operands and the results are relations, and so the output from one operation can
become the input to another operation. This allows expressions to be nested in the
relational algebra, just as we can nest arithmetic operations. This property is called
closure: relations are closed under the algebra, just as numbers are closed under
arithmetic operations.
5.3 Define the five basic relational algebra operations. Define the Join, Intersection, and Division
operations in terms of these five basic operations.
Five basic operations are:
• Selection and Projection (Unary)
• Cartesian Product, Union, and Set Difference (Binary).
There is also the Join, Intersection, and Division operations:
• Can rewrite -Join in terms of the basic selection and Cartesian product operations:
6
Chapter 5 Relational Algebra and Relational Calculus
Review Questions
5.1 What is the difference between a procedural and non-procedural language? How would you
classify the relational algebra and relational calculus?
Procedural language: a language that allows user to tell the system what data is needed and
exactly how to retrieve the data.
Non-procedural language: a language that allows user to state what data is needed rather
than how it is to be retrieved.
Informally, we may describe the relational algebra as a (high-level) procedural language: it
can be used to tell the DBMS how to build a new relation from one or more relations in the
database. Again, informally, we may describe the relational calculus as a non-procedural
language: it can be used to formulate the definition of a relation in terms of one or more
database relations.
5.2 Explain the following terms:
• relationally complete;
A language that can be used to produce any relation that can be derived using the
relational calculus is said to be relationally complete.
• closure of relational operations.
The relational algebra is a theoretical language with operations that work on one or more
relations to define another relation without changing the original relation(s). Thus, both
the operands and the results are relations, and so the output from one operation can
become the input to another operation. This allows expressions to be nested in the
relational algebra, just as we can nest arithmetic operations. This property is called
closure: relations are closed under the algebra, just as numbers are closed under
arithmetic operations.
5.3 Define the five basic relational algebra operations. Define the Join, Intersection, and Division
operations in terms of these five basic operations.
Five basic operations are:
• Selection and Projection (Unary)
• Cartesian Product, Union, and Set Difference (Binary).
There is also the Join, Intersection, and Division operations:
• Can rewrite -Join in terms of the basic selection and Cartesian product operations:
Loading page 26...
Database Systems: Instructor’s Guide - Part III
7
R 3 F S = F (R S)
• Can express the intersection operation in terms of the set difference operation:
R S = R - (R - S)
• Can express the division operation in terms of the basic operations:
T1 = C(R)
T2 = C( (S x T1) - R)
T = T1 - T2
5.4 Discuss the differences between the five Join operations: Theta join, Equijoin, Natural join,
Outer join, and Semijoin. Give examples to illustrate your answer.
Theta join R F S Produces a relation that contains tuples satisfying the predicate F from
the Cartesian product of R and S.
Equijoin R F S Produces a relation that contains tuples satisfying the predicate F
(which only contains equality comparisons) from the Cartesian product
of R and S.
Natural join R S An Equijoin of the two relations R and S over all common attributes
x. One occurrence of each common attribute is eliminated.
(Left) Outer join R S A join in which tuples from R that do not have matching values in the
common attributes of S are also included in the result relation.
Semijoin R F S Produces a relation that contains the tuples of R that participate in the
join of R with S.
5.5 Compare and contrast the tuple relational calculus with domain relational calculus. In
particular, discuss the distinction between tuple and domain variables.
In the tuple relational calculus, we use variables that range over tuples in a relation. In the
domain relational calculus, we also use variables but in this case the variables take their values
from domains of attributes rather than tuples of relations.
5.6 Define the structure of a (well-formed) formula in both the tuple relational calculus and
domain relational calculus.
Tuple relational calculus
A (well-formed) formula is made out of one or more atoms, where an atom has one of the
following forms:
• R(Si), where Si is a tuple variable and R is a relation.
• Si.a1
Sj.a2, where Si and Sj are tuple variables, a1 is an attribute of the relation over
which Si ranges, a2 is an attribute of the relation over which Sj ranges, and is one of the
comparison operators (, , , , =, ); the attributes a1 and a2 must have domains
whose members can be compared by
7
R 3 F S = F (R S)
• Can express the intersection operation in terms of the set difference operation:
R S = R - (R - S)
• Can express the division operation in terms of the basic operations:
T1 = C(R)
T2 = C( (S x T1) - R)
T = T1 - T2
5.4 Discuss the differences between the five Join operations: Theta join, Equijoin, Natural join,
Outer join, and Semijoin. Give examples to illustrate your answer.
Theta join R F S Produces a relation that contains tuples satisfying the predicate F from
the Cartesian product of R and S.
Equijoin R F S Produces a relation that contains tuples satisfying the predicate F
(which only contains equality comparisons) from the Cartesian product
of R and S.
Natural join R S An Equijoin of the two relations R and S over all common attributes
x. One occurrence of each common attribute is eliminated.
(Left) Outer join R S A join in which tuples from R that do not have matching values in the
common attributes of S are also included in the result relation.
Semijoin R F S Produces a relation that contains the tuples of R that participate in the
join of R with S.
5.5 Compare and contrast the tuple relational calculus with domain relational calculus. In
particular, discuss the distinction between tuple and domain variables.
In the tuple relational calculus, we use variables that range over tuples in a relation. In the
domain relational calculus, we also use variables but in this case the variables take their values
from domains of attributes rather than tuples of relations.
5.6 Define the structure of a (well-formed) formula in both the tuple relational calculus and
domain relational calculus.
Tuple relational calculus
A (well-formed) formula is made out of one or more atoms, where an atom has one of the
following forms:
• R(Si), where Si is a tuple variable and R is a relation.
• Si.a1
Sj.a2, where Si and Sj are tuple variables, a1 is an attribute of the relation over
which Si ranges, a2 is an attribute of the relation over which Sj ranges, and is one of the
comparison operators (, , , , =, ); the attributes a1 and a2 must have domains
whose members can be compared by
Loading page 27...
Database Systems: Instructor’s Guide - Part III
8
• Si.a1
c, where Si is a tuple variable, a1 is an attribute of the relation over which Si
ranges, c is a constant from the domain of attribute a1, and is one of the comparison
operators
We recursively build up formulae from atoms using the following rules:
• an atom is a formula;
• if F1 and F2 are formulae, so are their conjunction F1 F2, their disjunction F1 F2, and
the negation F1;
• if F is a formula with free variable X, then (X)(F) and (X)(F) are also formulae.
Domain relational calculus
A (well-formed) formula is made out of one or more atoms, where an atom has one of the
following forms:
• R(d1, d2,…, dn), where R is a relation of degree n and each di is a domain variable.
• di
dj, where di and dj are domain variables and is one of the comparison operators (,
, , , =, ); the domains di and dj must have members that can be compared by
• di
c, where di is a domain variable, c is a constant from the domain of di, and is one of
the comparison operators
5.7 Explain how a relational calculus expression can be unsafe? Illustrate your answer with an
example. Discuss how to ensure that a relational calculus expression is safe.
See end of Section 5.2.1.
Exercises
For the following exercises, use the Hotel schema defined at the start of the Exercises at the end of
Chapter 4.
5.8 Describe the relations that would be produced by the following relational algebra operations:
a) hotelNo (price 50 (Room) )
This will produce a relation with a single attribute (hotelNo) giving the number of those
hotels with a room price greater than £50.
b) Hotel.hotelNo = Room.hotelNo(Hotel Room)
This will produce a join of the Hotel and Room relations containing all the attributes of
both Hotel and Room (there will be two copies of the hotelNo attribute). Essentially this
will produce a relation containing all rooms at all hotels.
8
• Si.a1
c, where Si is a tuple variable, a1 is an attribute of the relation over which Si
ranges, c is a constant from the domain of attribute a1, and is one of the comparison
operators
We recursively build up formulae from atoms using the following rules:
• an atom is a formula;
• if F1 and F2 are formulae, so are their conjunction F1 F2, their disjunction F1 F2, and
the negation F1;
• if F is a formula with free variable X, then (X)(F) and (X)(F) are also formulae.
Domain relational calculus
A (well-formed) formula is made out of one or more atoms, where an atom has one of the
following forms:
• R(d1, d2,…, dn), where R is a relation of degree n and each di is a domain variable.
• di
dj, where di and dj are domain variables and is one of the comparison operators (,
, , , =, ); the domains di and dj must have members that can be compared by
• di
c, where di is a domain variable, c is a constant from the domain of di, and is one of
the comparison operators
5.7 Explain how a relational calculus expression can be unsafe? Illustrate your answer with an
example. Discuss how to ensure that a relational calculus expression is safe.
See end of Section 5.2.1.
Exercises
For the following exercises, use the Hotel schema defined at the start of the Exercises at the end of
Chapter 4.
5.8 Describe the relations that would be produced by the following relational algebra operations:
a) hotelNo (price 50 (Room) )
This will produce a relation with a single attribute (hotelNo) giving the number of those
hotels with a room price greater than £50.
b) Hotel.hotelNo = Room.hotelNo(Hotel Room)
This will produce a join of the Hotel and Room relations containing all the attributes of
both Hotel and Room (there will be two copies of the hotelNo attribute). Essentially this
will produce a relation containing all rooms at all hotels.
Loading page 28...
Database Systems: Instructor’s Guide - Part III
9
c) hotelName (Hotel Hotel.hotelNo = Room.hotelNo (price 50 (Room)) )
This will produce a join of Hotel and those tuples of Room with a price greater than £50.
Essentially this will produce a relation containing all hotel names with a room price above
£50.
d) Guest (dateTo ‘1-Jan-2007’ (Booking))
This will produce a (left outer) join of Guest and those tuples of Booking with an end date
(dateTo) greater than or equal to 1-Jan-2007. All guests who don’t have a booking with
such a date will still be included in the join. Essentially this will produce a relation
containing all guests and show the details of any bookings they have beyond 1-Jan-2002.
e) Hotel Hotel.hotelNo = Room.hotelNo (price 50 (Room)) )
This will produce a (semi) join of Hotel and those tuples of Room with a price greater
than £50. Only those Hotel attributes will be listed. Essentially this will produce a relation
containing all the details of all hotels with a room price above £50.
f) guestName, hotelNo (Booking Booking.guestNo = Guest.guestNo Guest)
hotelNo (city = ’London’(Hotel))
This will produce a relation containing the names of all guests who have booked all hotels
in London.
5.9 Provide the equivalent tuple relational calculus and domain relational calculus expressions
for each of the relational algebra queries given in Exercise 4.8.
a) hotelNo (price 50 (Room) )
TRC: {R.hotelNo | Room(R) R.price 50}
DRC: {hotelNo | (rNo, typ, prce) (Room (rNo, hotelNo, typ, prce) prce 50)}
b) Hotel.hotelNo = Room.hotelNo(Hotel Room)
TRC: {H, R | Hotel(H) (R) (Room(R) (H.hotelNo = R.hotelNo))}
DRC: {hNo, hName, cty, rNo, hNo1, typ, prce | (Hotel(hNo, hName, cty)
Room(rNo, hNo1, typ, prce) (hNo = hNo1))}
9
c) hotelName (Hotel Hotel.hotelNo = Room.hotelNo (price 50 (Room)) )
This will produce a join of Hotel and those tuples of Room with a price greater than £50.
Essentially this will produce a relation containing all hotel names with a room price above
£50.
d) Guest (dateTo ‘1-Jan-2007’ (Booking))
This will produce a (left outer) join of Guest and those tuples of Booking with an end date
(dateTo) greater than or equal to 1-Jan-2007. All guests who don’t have a booking with
such a date will still be included in the join. Essentially this will produce a relation
containing all guests and show the details of any bookings they have beyond 1-Jan-2002.
e) Hotel Hotel.hotelNo = Room.hotelNo (price 50 (Room)) )
This will produce a (semi) join of Hotel and those tuples of Room with a price greater
than £50. Only those Hotel attributes will be listed. Essentially this will produce a relation
containing all the details of all hotels with a room price above £50.
f) guestName, hotelNo (Booking Booking.guestNo = Guest.guestNo Guest)
hotelNo (city = ’London’(Hotel))
This will produce a relation containing the names of all guests who have booked all hotels
in London.
5.9 Provide the equivalent tuple relational calculus and domain relational calculus expressions
for each of the relational algebra queries given in Exercise 4.8.
a) hotelNo (price 50 (Room) )
TRC: {R.hotelNo | Room(R) R.price 50}
DRC: {hotelNo | (rNo, typ, prce) (Room (rNo, hotelNo, typ, prce) prce 50)}
b) Hotel.hotelNo = Room.hotelNo(Hotel Room)
TRC: {H, R | Hotel(H) (R) (Room(R) (H.hotelNo = R.hotelNo))}
DRC: {hNo, hName, cty, rNo, hNo1, typ, prce | (Hotel(hNo, hName, cty)
Room(rNo, hNo1, typ, prce) (hNo = hNo1))}
Loading page 29...
Database Systems: Instructor’s Guide - Part III
10
c) hotelName (Hotel Hotel.hotelNo = Room.hotelNo (price 50 (Room)) )
TRC: {H.hotelName | Hotel(H) (R) (Room(R) (H.hotelNo = R.hotelNo)
(R.price > 50))}
DRC: {hotelName | (hNo, cty, rNo, hNo1, typ, prce)
(Hotel(hNo, hotelName, cty) Room(rNo, hNo1, typ, prce) (hNo = hNo1)
(prce > 50))}
d) Guest (dateTo ‘1-Jan-2007’ (Booking))
TRC: {G.guestNo, G.guestName, G.guestAddress, B.hotelNo, B.dateFrom,
B.dateTo, B.roomNo | Guest(G) (B) (Booking(B)
(G.guestNo = B.guestNo) (B.dateTo > ‘1-Jan-2007’))}
DRC: {guestNo, guestName, guestAddress, hotelNo, dateFrom, dateTo, roomNo |
(gNo1) (Guest(guestNo, guestName, guestAddress)
(Booking(hotelNo, gNo1, dateFrom, dateTo, roomNo)
(guestNo = gNo1) (dateTo ‘1-Jan-2007’)))}
e) Hotel Hotel.hotelNo = Room.hotelNo (price 50 (Room)) )
TRC: {H.hotelNo, H.hotelName, H.city | Hotel(H) (R) (Room(R)
(H.hotelNo = R.hotelNo) (R.price > 50))}
DRC: {hotelNo, hotelName, city | (rNo, hNo1, typ, prce)
(Hotel(hotelNo, hotelName, city) Room(rNo, hNo1, typ, prce)
(hotelNo = hNo1) (prce > 50))}
f) guestName, hotelNo (Booking Booking.guestNo = Guest.guestNo Guest)
hotelNo (city = ’London’(Hotel))
TRC: {G.guestName | Guest(G) ( (H) (Hotel(H)
(H.city = ‘London’) ((B) (Booking(B)
G.guestNo = B.guestNo H.hotelNo = B.hotelNo))))}
DRC: {guestName | (gNo, gName, gAddress, hNo, gNo1, dFrom, dTo, rNo,
hName, cty, hNo1, typ, prce) ((Hotel(hNo, hName, cty)
(cty = ‘London’) Guest(gNo, gName, gAddress)
Booking(hNo1, gNo1, dFrom, dTo, rNo)
(gNo = gNo1) (hNo = hNo1)))}
10
c) hotelName (Hotel Hotel.hotelNo = Room.hotelNo (price 50 (Room)) )
TRC: {H.hotelName | Hotel(H) (R) (Room(R) (H.hotelNo = R.hotelNo)
(R.price > 50))}
DRC: {hotelName | (hNo, cty, rNo, hNo1, typ, prce)
(Hotel(hNo, hotelName, cty) Room(rNo, hNo1, typ, prce) (hNo = hNo1)
(prce > 50))}
d) Guest (dateTo ‘1-Jan-2007’ (Booking))
TRC: {G.guestNo, G.guestName, G.guestAddress, B.hotelNo, B.dateFrom,
B.dateTo, B.roomNo | Guest(G) (B) (Booking(B)
(G.guestNo = B.guestNo) (B.dateTo > ‘1-Jan-2007’))}
DRC: {guestNo, guestName, guestAddress, hotelNo, dateFrom, dateTo, roomNo |
(gNo1) (Guest(guestNo, guestName, guestAddress)
(Booking(hotelNo, gNo1, dateFrom, dateTo, roomNo)
(guestNo = gNo1) (dateTo ‘1-Jan-2007’)))}
e) Hotel Hotel.hotelNo = Room.hotelNo (price 50 (Room)) )
TRC: {H.hotelNo, H.hotelName, H.city | Hotel(H) (R) (Room(R)
(H.hotelNo = R.hotelNo) (R.price > 50))}
DRC: {hotelNo, hotelName, city | (rNo, hNo1, typ, prce)
(Hotel(hotelNo, hotelName, city) Room(rNo, hNo1, typ, prce)
(hotelNo = hNo1) (prce > 50))}
f) guestName, hotelNo (Booking Booking.guestNo = Guest.guestNo Guest)
hotelNo (city = ’London’(Hotel))
TRC: {G.guestName | Guest(G) ( (H) (Hotel(H)
(H.city = ‘London’) ((B) (Booking(B)
G.guestNo = B.guestNo H.hotelNo = B.hotelNo))))}
DRC: {guestName | (gNo, gName, gAddress, hNo, gNo1, dFrom, dTo, rNo,
hName, cty, hNo1, typ, prce) ((Hotel(hNo, hName, cty)
(cty = ‘London’) Guest(gNo, gName, gAddress)
Booking(hNo1, gNo1, dFrom, dTo, rNo)
(gNo = gNo1) (hNo = hNo1)))}
Loading page 30...
Database Systems: Instructor’s Guide - Part III
11
5.10 Describe the relations that would be produced by the following tuple relational calculus
expressions:
(a) {H.hotelName | Hotel(H) H.city = ‘London’}
This will produce a relation containing the names of all hotels in London.
(b) {H.hotelName | Hotel(H) (R) (Room(R) H.hotelNo = R.hotelNo R.price
50)}
This will produce a relation containing the names of all hotels that have a room price
above £50.
(c) {H.hotelName | Hotel(H) (B) (G) (Booking(B) Guest(G) H.hotelNo =
B.hotelNo B.guestNo = G.guestNo G.guestName = ‘John Smith’)}
This will produce a relation containing the names of all hotels that have a booking for
a guest called John Smith.
(d) {H.hotelName, G.guestName, B1.dateFrom, B2.dateFrom | Hotel(H) Guest(G)
Booking(B1) Booking(B2) H.hotelNo = B1.hotelNo
G.guestNo = B1.guestNo B2.hotelNo = B1.hotelNo
B2.guestNo = B1.guestNo B2.dateFrom B1.dateFrom}
This will produce a relation containing the names of guests who have more than one
booking at the same hotel, along with the hotel number and the dates of the bookings.
5.11 Provide the equivalent domain relational calculus and relational algebra expressions for each
of the tuple relational calculus expressions given in Exercise 4.10.
(a) {H.hotelName | Hotel(H) H.city = ‘London’}
DRC: {hotelName | (hNo, cty) (Hotel(hNo, hotelName, cty) cty = ‘London’)}
RA: hotelName (city = ‘London’ (Hotel) )
(b) {H.hotelName | Hotel(H) (R) (Room(R) H.hotelNo = R.hotelNo R.price
50)}
DRC: {hotelName | (hNo, cty, rNo, hNo1, typ, prce) (Hotel(hNo, hotelName, cty)
Room(rNo, hNo1, typ, prce) (hNo = hNo1) (prce > 50)) }
11
5.10 Describe the relations that would be produced by the following tuple relational calculus
expressions:
(a) {H.hotelName | Hotel(H) H.city = ‘London’}
This will produce a relation containing the names of all hotels in London.
(b) {H.hotelName | Hotel(H) (R) (Room(R) H.hotelNo = R.hotelNo R.price
50)}
This will produce a relation containing the names of all hotels that have a room price
above £50.
(c) {H.hotelName | Hotel(H) (B) (G) (Booking(B) Guest(G) H.hotelNo =
B.hotelNo B.guestNo = G.guestNo G.guestName = ‘John Smith’)}
This will produce a relation containing the names of all hotels that have a booking for
a guest called John Smith.
(d) {H.hotelName, G.guestName, B1.dateFrom, B2.dateFrom | Hotel(H) Guest(G)
Booking(B1) Booking(B2) H.hotelNo = B1.hotelNo
G.guestNo = B1.guestNo B2.hotelNo = B1.hotelNo
B2.guestNo = B1.guestNo B2.dateFrom B1.dateFrom}
This will produce a relation containing the names of guests who have more than one
booking at the same hotel, along with the hotel number and the dates of the bookings.
5.11 Provide the equivalent domain relational calculus and relational algebra expressions for each
of the tuple relational calculus expressions given in Exercise 4.10.
(a) {H.hotelName | Hotel(H) H.city = ‘London’}
DRC: {hotelName | (hNo, cty) (Hotel(hNo, hotelName, cty) cty = ‘London’)}
RA: hotelName (city = ‘London’ (Hotel) )
(b) {H.hotelName | Hotel(H) (R) (Room(R) H.hotelNo = R.hotelNo R.price
50)}
DRC: {hotelName | (hNo, cty, rNo, hNo1, typ, prce) (Hotel(hNo, hotelName, cty)
Room(rNo, hNo1, typ, prce) (hNo = hNo1) (prce > 50)) }
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
Mathematics