Database Reference
In-Depth Information
CUSTOMER_TRANSACTION_INT
CustomerID (FK)
TransactionID (FK)
CUSTOMER
CustomerID
ARTIST
TRANS
WORK
LastName
FirstName
AreaCode
LocalNumber
Street
City
State
ZipPostalCode
Country
Email (AK1.1)
ArtistID
TransactionID
WorkID
LastName (AK1.1)
FirstName (AK1.2)
Nationality
DateOfBirth
DateDeceased
DateAcquired
AcquisitionPrice
DateSold
SalesPrice
AskingPrice
WorkID (FK)
Title
Description
Medium
Copy
ArtistID (FK)
CUSTOMER_ARTIST_INT
ArtistID (FK)
CustomerID (FK)
Figure 8-5
View Ridge Gallery Database
Design with New N:M
Relationship
Changing a 1:N relationship to an N:M relationship is surprisingly easy. 2 Just create the
new intersection table with appropriate foreign key constraints, fill it with data, and drop
the old foreign key column. Figure 8-5 shows the View Ridge database design with a new
intersection table to support the N:M relationship.
We need to create this table and then copy the values of TransactionID and CustomerID
from TRANS for rows in which CustomerID is not null. First, create the new intersection table
using the following SQL:
/* *** EXAMPLE CODE - DO NOT RUN *** */
/* *** SQL-CREATE-TABLE-CH08-01 *** */
CREATE TABLE CUSTOMER_TRANSACTION_INT(
CustomerID
Int
NOT NULL,
TransactionID Int
NOT NULL,
CONSTRAINT
CustomerTransaction_PK
PRIMARY KEY(CustomerID, TransactionID),
CONSTRAINT Customer_Transaction_Int_Trans_FK
FOREIGN KEY (TransactionID) REFERENCES TRANS(TransactionID),
CONSTRAINT Customer_Transaction_Int_Customer_FK
FOREIGN KEY (CustomerID) REFERENCES CUSTOMER(CustomerID)
);
Note that there is no cascade behavior for updates because CustomerID is a surrogate key.
There is no cascade behavior for deletions because of the business policy never to delete data
2 Making the data change is easy. Dealing with the consequences of the data change with regards to views,
triggers, stored procedures, and application code will be more difficult. All of these will need to be rewritten
to join across a new intersection table. All forms and reports also will need to be changed to portray multiple
customers for a transaction; this will mean changing text boxes to grids, for example. All of this work is time
consuming and, hence, expensive.
 
Search WWH ::




Custom Search