Introduction to the Relational
Consider the relational database of Figure ?. What are the appropriate
Answer: The answer is shown in Figure 2.1, with primary keys underlined.
Consider the foreign ke
Explain why log records for transactions on the undo-list must be processed in reverse order, whereas redo is performed in a forward direction.
Answer: Within a single transaction in undo-list, suppose a
Instead of storing shared structures in shared memory, an alternative
architecture would be to store them in the local memory of a special
process, and access the shared data by interprocess
In a range selection on a range-partitioned attribute, it is possible that
only one disk may need to be accessed. Describe the benets and drawbacks of this property.
Answer: If there are few tuples in
How might a distributed database designed for a local-area network differ
from one designed for a wide-area network?
Answer: Data transfer on a local-area network (LAN) is much faster than
on a wide
Data Analysis and Mining
Describe benets and drawbacks of a source-driven architecture for
gathering of data at a data warehouse, as compared to a destinationdriven architecture.
Answer: In a destination-driven architect
Compute the relevance (using appropriate denitions of term frequency and inverse document frequency) of each of the Practice Exercises in this chapter to the query SQL relation.
Answer: We do not co
A car-rental company maintains a database for all vehicles in its current eet. For all vehicles, it includes the vehicle identication number,
license number, manufacturer, model, date of purchase,
Give an alternative representation of university information containing the same data as in Figure 23.1, but using attributes instead of
subelements. Also give the DTD or XML Schema for this representation.
Many applications need to generate sequence numbers for each transaction.
If a sequence counter is locked in two-phase manner, it can become
a concurrency bottleneck. Explain why this
Advanced Data Types and
What are the two types of time, and how are they different? Why does
it make sense to have both types of time associated with a tuple?
Answer: A temporal database models the chang
Like database systems, workow systems also require concurrency and
recovery management. List three reasons why we cannot simply apply a
relational database system using 2PL, physical undo
Answer: Suppose two-phase locking does not ensure serializability. Then
there exists a set of transactions T0 , T1 . Tn1 which obey 2PL and which
produce a nonserializable schedule. A non-serializable
Answer: Even in this case the recovery manager is needed to perform
roll-back of aborted transactions.
Answer: There are several steps in the creation of a le. A storage area
is assigned to the le in th
Introduction to SQL
Write the following queries in SQL, using the university schema. (We suggest you actually run these queries on a database, using the sample data
that we provide on the Web site of the book, db-book.com. Instruct
Write the following queries in SQL:
a. Display a list of all instructors, showing their ID, name, and the
number of sections that they have taught. Make sure to show the
number of sections as 0 for instructors who
Describe the circumstances in which you would choose to use embedded SQL rather than SQL alone or only a general-purpose programming
Answer: Writing queries in SQL is typically much easier than codin
Formal Relational Query
Write the following queries in relational algebra, using the university
Find the titles of courses in the Comp. Sci. department that have 3
Find the IDs of all stude
Database Design and the E-R
Answer: The E-R diagram is shown in Figure 7.1. Payments are modeled
as weak entities since they are related to a specic policy.
Note that the participation of accident in the relationship
Relational Database Design
Suppose that we decompose the schema R = ( A, B , C , D, E ) into
( A, B , C )
( A, D, E ).
Show that this decomposition is a lossless-join decomposition if the
following set F of functional dependencies
Application Design and
What is the main reason why servlets give better performance than programs that use the common gateway interface (CGI), even though Java
programs generally run slower than C or C+ program
Storage and File Structure
Answer: This arrangement has the problem that Pi and B4i 3 are on the
same disk. So if that disk fails, reconstruction of B4i 3 is not possible, since
data and parity are both lost.
Indexing and Hashing
Answer: Reasons for not keeping indices on every attribute include:
Every index requires additional
CPU time and disk I/O overhead
during inserts and deletions.
Indices on non-primary keys might ha
Assume (for simplicity in this exercise) that only one tuple ts in a block
and memory holds at most 3 blocks. Show the runs created on each pass
of the sort-merge algorithm, when applied to sort the foll
Show that the following equivalences hold. Explain how you can apply
them to improve the efciency of certain queries:
E 1 1 ( E 2 E 3 ) = ( E 1 1 E 2 E 1 1 E 3 ).
( AG F ( E ) =
( E1 1 E2) =
This chapter has described several major advantages of a database system.
What are two disadvantages?
Answer: Two disadvantages associated with database systems are listed
Setup of the databas