show_area_manager decomp

--ALTER TABLE show_areas ADD managerID INT

BEGIN TRANSACTION bdb_20111209

------------------------------------------------------------------------------
 -- NULL all blank emails, names, and phone numbers.
------------------------------------------------------------------------------
UPDATE show_areas SET
ownerName = NULL
WHERE ownerName = ''

UPDATE show_areas SET
ownerEmail = NULL
WHERE ownerEmail = ''

UPDATE show_areas SET
ownerPhone = NULL
WHERE ownerPhone = ''

------------------------------------------------------------------------------
 -- TRIM all emails, names, and phone numbers.
------------------------------------------------------------------------------
UPDATE show_areas SET
ownerName  = RTRIM(LTRIM(ownerName)),
ownerEmail = RTRIM(LTRIM(ownerEmail)),
ownerPhone = RTRIM(LTRIM(ownerPhone))

------------------------------------------------------------------------------
 -- Create the new am table.
------------------------------------------------------------------------------
CREATE TABLE show_area_managers
(
  amID INT NOT NULL PRIMARY KEY IDENTITY,
  amName VARCHAR(255),
  amEmail VARCHAR(255),
  amPhone VARCHAR(100)
)

------------------------------------------------------------------------------
 -- Populate the table.
------------------------------------------------------------------------------
INSERT INTO show_area_managers (amName, amEmail, amPhone)
SELECT DISTINCT ownerName, ownerEmail, ownerPhone
FROM   show_areas
WHERE  ownerName  IS NOT NULL
  OR   ownerEmail IS NOT NULL
  OR   ownerPhone IS NOT NULL
ORDER BY ownerName

------------------------------------------------------------------------------
 -- Add the references for to the managers.
------------------------------------------------------------------------------
DECLARE @amID INT
DECLARE @amName VARCHAR(255)
DECLARE @amEmail VARCHAR(255)
DECLARE @amPhone VARCHAR(100)

DECLARE samCursor CURSOR FOR
SELECT  amID, amName, amEmail, amPhone
FROM    show_area_managers
OPEN samCursor
FETCH NEXT FROM samCursor INTO @amID, @amName, @amEmail, @amPhone

WHILE @@FETCH_STATUS = 0
BEGIN
  FETCH NEXT FROM samCursor INTO @amID, @amName, @amEmail, @amPhone
  UPDATE show_areas SET
  managerID = @amID
  WHERE ownerName  = @amName
    AND ownerPhone = @amPhone
    AND ownerEmail = @amEmail
END

CLOSE samCursor
DEALLOCATE samCursor

------------------------------------------------------------------------------
 -- Make the managerID reference the amID.
------------------------------------------------------------------------------
ALTER TABLE show_areas ADD FOREIGN KEY (managerID) REFERENCES show_area_managers(amID)

------------------------------------------------------------------------------
 -- Ensure the decomposition is lossless.
------------------------------------------------------------------------------
SELECT sa.showAreaID, sa.areaName, ownerName, ownerEmail, ownerPhone,
       amName, amEmail, amPhone
FROM   show_areas sa
LEFT OUTER JOIN show_area_managers sam ON sa.managerID = sam.amID

------------------------------------------------------------------------------
 -- Remove the unused columns.
------------------------------------------------------------------------------
ALTER TABLE show_areas DROP COLUMN ownerName
ALTER TABLE show_areas DROP COLUMN ownerEmail
ALTER TABLE show_areas DROP COLUMN ownerPhone

ROLLBACK TRANSACTION bdb_20111209