Problem
Consider the following database schema used to track student advising at Franklin University. The database has been in use for several years and contains thousands of entries. Primary keys are underlined. Foreign keys are indicated.
Advisor (SSN, Name, Phone, Gender, Start_date)
Student (StudentID, Name, Phone, Address, Gender, DOB, AdvisorSSN)
FOREIGN KEY (AdvisorSSN) REFERENCES Advisor (SSN)
After discussing with the development teams, the following database refactoring has been approved: Change the primary key in the Advisor relation from SSN to a composite key: {Name, Phone}.
You are asked to implement the refactoring by using the appropriate refactoring pattern(s). Make the proper changes to related table(s) as well.
You need to:
A. List every step in the order of application and describe the process of accomplishing the refactoring requests by including the following (for each used refactoring):
a. name of the refactoring pattern chosen
b. motivation: why did you chose it for this refactoring
c. schema update mechanics (if applicable): include the operations and SQL commands needed to achieve the desired objective
d. data migration mechanics (if applicable): include the operations and SQL commands needed to achieve the desired objective
B. Show the new database schema with new referential integrity constraints after the refactoring