DATABASE SOLUTIONS (2nd Edition)
THOMAS M CONNOLLY & CAROLYN E BEGG
SOLUTIONS TO REVIEW QUESTIONS
Chapter 1 Introduction- Review questions
1.1 List four examples of database systems other than those listed in Section 1.1.
Some examples could be:
1.2 Discuss the meaning of each of the following terms:
For end users, this constitutes all the different values connected with the various objects/entities that are of concern to them.
A shared collection of logically related data (and a description of this data), designed to meet the information needs of an organization.
(c) database management system
A software system that: enables users to define, create, and maintain the database and provides controlled access to this database.
(d) application program
A computer program that interacts with the database by issuing an appropriate request (typically an SQL statement) to the DBMS.
(e) data independence
This is essentially the separation of underlying file structures from the programs that operate on them, also called program-data independence.
A virtual table that does not necessarily exist in the database but is generated by the DBMS from the underlying base tables whenever it’s accessed. 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.
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.
1.5 Describe the problems with the traditional two-tier client-server architecture and discuss how these problems were overcome with the three-tier client-server architecture.
In the mid-1990s, as applications became more complex and potentially could be deployed to hundreds or thousands of end-users, the client side of this architecture gave rise to two problems:
By 1995, a new variation of the traditional two-tier client-server model appeared to solve these problems called the three-tier client-server architecture. This new architecture proposed three layers, each potentially running on a different platform:
The three-tier design has many advantages over the traditional two-tier design, such as:
An additional advantage is that the three-tier architecture maps quite naturally to the Web environment, with a Web browser acting as the ‘thin’ client, and a Web server acting as the application server. The three-tier client server architecture is illustrated in Figure 1.4.
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
1.7 Of the functions described in your answer to Question 1.6, 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.
Some advantages of the database approach include control of data redundancy, data consistency, sharing of data, and improved security and integrity. Some disadvantages include complexity, cost, reduced performance, and higher impact of a failure.
Chapter 2 The Relational Model - Review questions
A table with columns and rows.
A named column of a relation.
The set of allowable values for one or more attributes.
A record of a relation.
(e) relational database.
A collection of normalized tables.
2.2 Discuss the properties of a relational table.
A relational table has the following properties:
2.3 Discuss the differences between the candidate keys and the primary key of a table. Explain what is meant by a foreign key. How do foreign keys of tables 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.
Represents a value for a column that is currently unknown or is not applicable for this record.
Entity integrity In a base table, no column of a primary key can be null.
Referential integrity If a foreign key exists in a table, either the foreign key value must match a candidate key value of some record in its home table or the foreign key value must be wholly null.
Chapter 3 SQL and QBE - Review questions
A data definition language (DDL) for defining the database structure.
A data manipulation language (DML) for retrieving and updating data.
3.2 Explain the function of each of the clauses in the SELECT statement. What restrictions are imposed on these clauses?
FROM specifies the table or tables to be used;
^ filters the rows subject to some condition;
GROUP BY forms groups of rows with the same column value;
HAVING filters the groups subject to some condition;
SELECT specifies which columns are to appear in the output;
ORDER BY specifies the order of the output.
An aggregate function can be used only in the SELECT list and in the HAVING clause.
Apart from COUNT(*), each function eliminates nulls first and operates only on the remaining non-null values. COUNT(*) counts all the rows of a table, regardless of whether nulls or duplicate values occur.
SQL first applies the WHERE clause. Then it conceptually arranges the table based on the grouping column(s). Next, applies the HAVING clause and finally orders the result according to the ORDER BY clause.
WHERE filters rows subject to some condition; HAVING filters groups subject to some condition.
With a subquery, the columns specified in the SELECT list are restricted to one table. Thus, cannot use a subquery if the SELECT list contains columns from more than one table.
QBE is an alternative, graphical-based, ‘point-and-click’ way of querying the database, which is particularly suited for queries that are not too complex, and can be expressed in terms of a few tables. QBE has acquired the reputation of being one of the easiest ways for non-technical users to obtain information from the database.
QBE queries are converted into their equivalent SQL statements before transmission to the DBMS server.
The past few decades has witnessed the dramatic rise in the number of software applications. Many of these applications proved to be demanding, requiring constant maintenance. This maintenance involved correcting faults, implementing new user requirements, and modifying the software to run on new or upgraded platforms. With so much software around to support, the effort spent on maintenance began to absorb resources at an alarming rate. As a result, many major software projects were late, over budget, and the software produced was unreliable, difficult to maintain, and performed poorly. This led to what has become known as the ‘software crisis’. Although this term was first used in the late 1960s, more than 30 years later, the crisis is still with us. As a result, some people now refer to the software crisis as the ‘software depression’.
An information system is the resources that enable the collection, management, control, and dissemination of data/information throughout a company. The database is a fundamental component of an information system. The lifecycle of an information system is inherently linked to the lifecycle of the database that supports it.
Typically, the stages of the information systems lifecycle include: planning, requirements collection and analysis, design (including database design), prototyping, implementation, testing, conversion, and operational maintenance. As a database is a fundamental component of the larger company-wide information system, the database system development lifecycle is inherently linked with the information systems lifecycle.
See Figure 4.1 Stages of the database system development lifecycle.
Database planning is the management activities that allow the stages of the database system development lifecycle to be realized as efficiently and effectively as possible.
^ involves identifying the scope and boundaries of the database system including its major user views. A user view can represent a job role or business application area.
Requirements collection and analysis is the process of collecting and analyzing information about the company that is to be supported by the database system, and using this information to identify the requirements for the new system.
There are three approaches to dealing with multiple user views, namely the centralized approach, the view integration approach, and a combination of both. The centralized approach involves collating the users’ requirements for different user views into a single list of requirements. A data model representing all the user views is created during the database design stage. The view integration approach involves leaving the users’ requirements for each user view as separate lists of requirements. Data models representing each user view are created and then merged at a later stage of database design.
^ is the process of creating a design that will support the company’s mission statement and mission objectives for the required database. This stage includes the logical and physical design of the database.
The aim of DBMS selection is to select a system that meets the current and future requirements of the company, balanced against costs that include the purchase of the DBMS product and any additional software/hardware, and the costs associated with changeover and training.
^ involves designing the user interface and the application programs that use and process the database. This stage involves two main activities: transaction design and user interface design.
Prototyping involves building a working model of the database system, which allows the designers or users to visualize and evaluate the system.
Implementation is the physical realization of the database and application designs.
^ involves transferring any existing data into the new database and converting any existing applications to run on the new database.
Testing is the process of running the database system with the intent of finding errors.
Operational maintenance is the process of monitoring and maintaining the system following installation.
The mission statement defines the major aims of the database system, while each mission objective identifies a particular task that the database must support.
A user view defines what is required of a database system from the perspective of a particular job (such as Manager or Supervisor) or business application area (such as marketing, personnel, or stock control).
An important activity of the requirements collection and analysis stage is deciding how to deal with the situation where there is more than one user view. There are three approaches to dealing with multiple user views:
Requirements for each user view are merged into a single list of requirements for the new database system. A logical data model representing all user views is created during the database design stage.
The centralized approach involves collating the requirements for different user views into a single list of requirements. A data model representing all user views is created in the database design stage. A diagram representing the management of user views 1 to 3 using the centralized approach is shown in Figure 4.4. Generally, this approach is preferred when there is a significant overlap in requirements for each user view and the database system is not overly complex.
See Figure 4.4 The centralized approach to managing multiple user views 1 to 3.