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

8.41 Describe how to change the maximum cardinality from 1:1 to 1:N. Assume that the
foreign key is on the side of the new parent in the 1:N relationship.
8.42 Assume that tables T1 and T2 have a 1:1 relationship. Assume that T2 has the foreign
key. Show the SQL statements necessary to move the foreign key to T1. Make up your
own names for the primary and foreign keys.
8.43 Explain how to transform a 1:N relationship into an N:M relationship.
8.44 Suppose that tables T1 and T2 have a 1:N relationship. Show the SQL statements nec-
essary to fill an intersection T1_T2_INT. Make up your own names for the primary and
foreign keys.
8.45 Explain how the reduction of maximum cardinalities causes data loss.
8.46 Using the tables in your answer to Review Question 8.44, show the SQL statements
necessary to change the relationship back to 1:N. Assume that the first row in the
qualifying rows of the intersection table is to provide the foreign key. Use the keys and
foreign keys from your answer to Review Question 8.44.
8.47 Using the results of your answer to Review Question 8.46, explain what must be done
to convert this relationship to 1:1. Use the keys and foreign keys from your answer to
Review Question 8.46.
8.48 In general terms, what must be done to add a new relationship?
8.49 Suppose that tables T1 and T2 have a 1:N relationship, with T2 as the child. Show the
SQL statements necessary to remove table T1. Make your own assumptions about the
names of keys and foreign keys.
8.50 What are the risks and problems of forward engineering?
Project Questions
8.51 Suppose that the table EMPLOYEE has a 1:N relationship to the table PHONE_
NUMBER. Further suppose that the primary key of EMPLOYEE is EmployeeID and
the columns of PHONE_NUMBER are PhoneNumberID (a surrogate key), AreaCode,
LocalNumber, and EmployeeID (a foreign key to EMPLOYEE). Alter this design so that
EMPLOYEE has a 1:1 relationship to PHONE_NUMBER. For employees having more
than one phone number, keep only the first one.
8.52 Suppose that the table EMPLOYEE has a 1:N relationship to the table PHONE_
NUMBER. Further suppose that the key of EMPLOYEE is EmployeeID and the columns
of PHONE_NUMBER are PhoneNumberID (a surrogate key), AreaCode, LocalNumber,
and EmployeeID (a foreign key to EMPLOYEE). Write all SQL statements necessary to
redesign this database so that it has just one table. Explain the difference between the
result of Project Question 8.51 and the result of this question.
8.53 Consider the following table:
TASK (EmployeeID, EmpLastName, EmpFirstName, Phone, OfficeNumber,
ProjectName, Sponsor, WorkDate, HoursWorked)
Also consider the following possible functional dependencies:
EmployeeID S (EmpLastName,EmpFirstName, Phone, OfficeNumber)
ProjectName S Sponsor
A. Write SQL statements to display the values of any rows that violate these functional
dependencies.