Database Redesign - Database Processing: Fundamentals, Design, and Implementation - page 386

difficult to change all of the views, triggers, stored proce-
dures, application programs, and forms and reports to use
the new intersection table.
Reducing cardinalities is easy, but such changes may
result in data loss. Prior to making such reductions, a policy
must be determined to decide which data to keep. Changing
N:M to 1:N involves creating a foreign key in the parent table
and moving one value from the intersection table into that
foreign key. Changing 1:N to 1:1 requires first eliminating
duplicates in the foreign key and then setting a uniqueness
constraint on that key. Adding and deleting relationships can

be accomplished by defining new foreign key constraints or
by dropping existing foreign key constraints.
Most data modeling tools have the capacity to perform
forward engineering, which is the process of applying data
model changes to an existing database. If forward engineering
is used, the results should be thoroughly tested before using it
on an operational database. Some tools will show the SQL that
they will execute during the forward-engineering process. Any
SQL generated by such tools should be carefully reviewed. All in
all, there is nothing wrong with writing database redesign SQL
statements by hand rather than using forward engineering.

Key Terms

correlated subquery
dependency graph
reverse engineered (RE) data model
SQL EXISTS keyword

SQL NOT EXISTS keyword
systems analysis and design
systems development life cycle (SDLC)
system maintenance

Review Questions

8.1 Review the three sources of database design and implementation.

8.2 Describe why database redesign is necessary.

8.3 Explain the following statement in your own words: “Information systems and organi-
zations create each other.” How does this relate to database redesign?

8.4 Suppose that a table contains two nonkey columns: AdviserName and AdviserPhone.
Further suppose that you suspect that AdviserPhone → AdviserName. Explain how to
examine the data to determine if this supposition is true.

8.5 Write a subquery, other than one in this chapter, that is not a correlated subquery.

8.6 Explain the following statement: “The processing of correlated subqueries is nested,
whereas that of regular subqueries is not.”

8.7 Write a correlated subquery, other than one in this chapter.

8.8 Explain how the query in your answer to Review Question 8.5 differs from the query in
your answer to Review Question 8.7.

8.9 Explain what is wrong with the correlated subquery on page 370.

8.10 Write a correlated subquery to determine whether the data support the supposition in
Review Question 8.4.

8.11 Explain the meaning of the SQL keyword EXISTS.

8.12 Answer Review Question 8.10, but use the SQL EXISTS keyword.