9.   Assume that all of the above tables are in place. Dr. J. Kervorkian (physid ‘555443333’, who lives at 203 N. Cooper St., Arlington, TX, 76011, and whose specialty is Surgery (NOTE: THE SAME AS DR. SMITH’S), and who is 45 Years old) is going to take over all of Dr. M. Smith’s (physid ‘123456789’) patients. Her name will then be removed from the physician table. Show all of the necessary SQL commands (be careful of the order of commands issued).

 

// We can NOT violate our UNIQUE constraint, so …

 

UPDATE physician

  SET specialty = ‘XXX’

  WHERE physid = ‘123456798’;

 

// NOW, we can insert a new record.

 

INSERT INTO physician

  VALUES (‘555443333’, ‘Kervorkian, J.’,

          ‘203 N. Cooper St.’, ‘Arlington’,

          ‘TX’, ‘76011’, ‘Surgery’,45);

 

// NOW, we can update our patient table

 

UPDATE patient

  SET physid = ‘555443333’

  WHERE physid = ‘123456798’;

 

// NOW, we can get rid of Dr. Smith

 

DELETE FROM physician

  WHERE physid = ‘123456789’;

 

// FINALLY, let’s make the changes permanent

 

COMMIT;