Details: perform simple database refactoring

Consider the following database schema used to track student advising. 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)

The following database refactoring will need to be done: 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:

1. 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):
   1. name of the refactoring pattern chosen
   2. motivation: why did you chose it for this refactoring
   3. schema update mechanics (if applicable): include the operations and SQL commands needed to achieve the desired objective
   4. data migration mechanics (if applicable): include the operations and SQL commands needed to achieve the desired objective
2. Show the new database schema with new referential integrity constraints after the refactoring

If a trigger is needed, you can just describe its purpose and function in plain language. No need to implement it.

Please note that the refactoring request may require the application of multiple refactoring pattern(s). You are expected to discuss the refactoring pattern(s) required to achieve each refactoring request in the proper order. Show the affected relations in the schema after each refactoring step.