12.18 What are the causes of bucket overflow in a hash file organization? What can
be done to reduce the occurrence of bucket overflows?
Answer: The causes of bucket overflow are :a. Our estimate of t
9.7 Redesign the database of Exercise 9.2 into first normal form and fourth normal
form. List any functional or multivalued dependencies that you assume. Also
list all referential-integrity constraint
8.13 Suppose there are two relations r and s, such that the foreign key B of r references
the primary key A of s. Describe how the trigger mechanism can be used
to implement the on delete cascade opti
7.28 Given the three goals of relational-database design, is there any reason to design
a database schema that is in 2NF, but is in no higher-order normal form? (See
Practice Exercise 7.15 for the def
7.26 In designing a relational database, why might we choose a non-BCNF design?
Answer: BCNF is not always dependency preserving. Therefore, we may want
to choose another normal form (specifically, 3N
7.24 List the three design goals for relational databases, and explain why each is desirable.
Answer: The three design goals are lossless-join decompositions, dependency
preserving decompositions, and
7.23 Show that the following decomposition of the schema R of Practice Exercise 7.1
is not a lossless-join decomposition:
(A, B, C)
(C, D, E).
Hint: Give an example of a relation r on schema R such th
7.22 Using the functional dependencies of Practice Exercise 7.6, compute B+.
Answer: Computing B+ by the algorithm in Figure 7.9 we start with result =
cfw_B. Considering FDs of the form in F, we find
7.21 Use Armstrongs axioms to prove the soundness of the decomposition rule.
Answer: The decomposition rule, and its derivation from Armstrongs axioms
are given below:
if , then and .
given
_ reflex
7.20 Consider the following proposed rule for functional dependencies: If and
, then . Prove that this rule is not sound by showing a relation r that
satisfies and , but does not satisfy .
Answer: Co
7.19 Use the definition of functional dependency to argue that each of Armstrongs
axioms (reflexivity, augmentation, and transitivity) is sound.
41
42 Chapter 7 Relational Database Design
Answer: The
7.18 Why are certain functional dependencies called trivial functional dependencies?
Answer: Certain functional dependencies are called trivial functional dependencies
because they are satisfied by al
7.17 Explain what is meant by repetition of information and inability to represent information.
Explain why each of these properties may indicate a bad relationaldatabase
design.
Answer:
Repetition o
6.14 Explain the distinctions among the terms primary key, candidate key, and superkey.
Answer: A superkey is a set of one or more attributes that, taken collectively, allows
us to identify uniquely a
5.9 Let R = (A, B) and S = (A, C), and let r(R) and s(S) be relations. Using
the special constant null, write tuple-relational-calculus expressions equivalent
to each of the following:
a. r _ s
b. r _
5.7 Let R = (A, B) and S = (A, C), and let r(R) and s(S) be relations. Write
relational-algebra expressions equivalent to the following domain-relationalcalculus
expressions:
a. cfw_< a > | b (< a,b >
5.6 Consider the employee database of Figure 5.14. Give expressions in tuple relational
calculus and domain relational calculus for each of the following queries:
a. Find the names of all employees wh
4.12 Compare the use of embedded SQL with the use in SQL of functions defined in
a general-purpose programming language. Under what circumstances would
you use each of these features?
Answer: SQL func
4.10 Consider an employee database with two relations
employee (employee-name, street, city)
works (employee-name, company-name, salary)
where the primary keys are underlined.Write a query to find com
4.7 Referential-integrity constraints as defined in this chapter involve exactly two
relations. Consider a database that includes the following relations:
salaried-worker (name, office, phone, salary)
3.18 Give an SQL schema definition for the employee database of Figure 3.12. Choose
an appropriate domain for each attribute and an appropriate primary key for
each relation schema.
Answer:
create dom
3.15 Write an SQL query, without using a with clause, to find all branches where
the total account deposit is less than the average total account deposit at all
branches,
16 Chapter 3 SQL
a. Using a n
3.14 Consider the relational database of Figure 3.12. Using SQL, define a view consisting
of manager name and the average salary of all employees who work for
that manager. Explain why the database sy
3.13 Show that, in SQL, <> all is identical to not in.
Answer: Let the set S denote the result of an SQL subquery.We compare (x <>
all S) with (x not in S). If a particular value x1 satisfies (x1 <> a
3.12 Let R = (A,B,C), and let r1 and r2 both be relations on schema R. Give an
expression in SQL that is equivalent to each of the following queries.
a. r1 r2
b. r1 r2
c. r1 r2
d. AB(r1) _ BC(r2)
Answ
3.11 Let the following relation schemas be given:
14 Chapter 3 SQL
R = (A,B,C)
S = (D,E, F)
Let relations r(R) and s(S) be given. Give an expression in SQL that is equivalent
to each of the following
Figure 3.12. Employee database.
3.9 Consider the employee database of Figure 3.12, where the primary keys are underlined.
Give an expression in SQL for each of the following queries.
a. Find the names
3.8 Consider the insurance database of Figure 3.11, where the primary keys are underlined.
Construct the following SQL queries for this relational database.
a. Find the number of accidents in which th
2.10 List two reasons why null values might be introduced into the database.
Answer: Nulls may be introduced into the database because the actual value
is either unknown or does not exist. For example