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 the number of records that the relation will have was to
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 constraints that should be present in the first- and
fourth-norma
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 option, when a tuple is deleted from s.
Answer: We define t
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 definition of 2NF.)
Answer: The three design goals of rela
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, 3NF) in order to make checking dependencies
easier during
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 minimization of repetition of information. They
44 Cha
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 that
A,B,C (r) _ C,D,E (r) _= r
Answer: Following the hin
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 that the only dependencies
satisfying result are B B a
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
_ reflexivity rule
transitivity rule
reflexive rule
transiti
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: Consider the following rule: if A B and C B, then A C.
Th
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 definition of functional dependency is: holds on R if i
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 all relations.
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 of information is a condition in a relational database w
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 an entity in the entity set. A superkey may contain
extr
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 _ s
c. r _ s
Answer:
a. cfw_t | r R s S (r[A] = s[A] t[A
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 > r b = 17)
b. cfw_< a, b, c > | < a,b > r < a,c > s
c.
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 who work for First Bank Corporation.
b. Find the names an
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 functions are primarily a mechanism for extending the power
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 companies whose
employees earn a higher salary, on average
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)
hourly-worker (name, hourly-wage)
address (name, stree
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 domain company names char(20)
create domain city names cha
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 nested query in the from clauser.
b. Using a nested quer
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 system should not allow updates to be
expressed in terms
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 <> all S) then for
all elements y of S x1 _= y. Thus x1 is
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)
Answer:
a. r1 r2
(select *
from r1)
union
(select *
from r2
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 queries.
a. A(r)
b. B =17 (r)
c. r s
d. A,F (C =D(r s)
Figure 3.11. Insurance database.
employee (employee name, street, city)
works (employee name, company name, salary)
company (company name, city)
manages (employee name, manager name)
3.10 Consider the relational database of Figure 3.12. Give an expression
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 of all employees who work for First Bank Corporation.
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 the cars belonging to John Smith
were involved.
b. Update
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, an employee whose address
has changed and whose new a