Database Reference
In-Depth Information
that involve transactions. The next task is to fill the table with data from the TRANS table us-
ing the following SQL statement:
/* *** EXAMPLE CODE - DO NOT RUN *** */
/* *** SQL-INSERT-CH08-02 *** */
INSERT INTO CUSTOMER_TRANSACTION_INT (CustomerID, TransactionID)
SELECT
CustomerID, TransactionID
FROM
TRANS
WHERE
CustomerID IS NOT NULL;
Once all of these changes have been made, the CustomerID column of TRANS can be
dropped.
Reducing Cardinalities (with Data Loss)
It is easy to make the structural changes to reduce cardinalities. To reduce an N:M relationship
to 1:N, we just create a new foreign key in the relation that will be the child and fill it with data
from the intersection table. To reduce a 1:N relationship to 1:1, we just make the values of the
foreign key of the 1:N relationship unique and then define a unique constraint on the foreign
key. In either case, the most difficult problem is deciding which data to lose.
Consider the reduction of N:M to 1:N. Suppose, for example, that the View Ridge Gallery
decides to keep just one artist interest for each customer. Thus, the relationship will then be
1:N from ARTIST to CUSTOMER. Accordingly, we add a new foreign key column ArtistID to
CUSTOMER and set up a foreign key constraint to ARTIST on that customer. The following
SQL will accomplish this:
/* *** EXAMPLE CODE - DO NOT RUN *** */
/* *** SQL-ALTER-TABLE-CH08-10 *** */
ALTER TABLE CUSTOMER
ADD ArtistID Int NULL;
ALTER TABLE CUSTOMER
ADD CONSTRAINT ArtistInterestFK FOREIGN KEY (ArtistID)
REFERENCES ARTIST(ArtistID);
Updates need not cascade because of the surrogate key, and deletions cannot cascade
because the customer may have a valid transaction and ought not to be deleted just because
an artist interest goes away.
Now, which of a customer's potentially many artist interests should be preserved in the
new relationship? The answer depends on the business policy at the gallery. Here suppose we
decide simply to take the first artist interest:
/* *** EXAMPLE CODE - DO NOT RUN *** */
/* *** SQL-UPDATE-CH08-03 *** */
UPDATE
CUSTOMER
SET
ArtistID =
(SELECT
TOP 1 ArtistID
FROM
CUSTOMER_ARTIST_INT AS CAI
WHERE
CUSTOMER.CustomerID = CAI.CustomerID);
The SQL Top 1 phrase is used to return the first qualifying row.
All views, triggers, stored procedures, and application code need to be changed to account
for the new 1:N relationship. Then the constraints defined on CUSTOMER_ ARTIST_INT can
be dropped. Finally, the table CUSTOMER_ARTIST_INT can be dropped.
 
Search WWH ::




Custom Search