HWA2_P2_3.sql

/* -------------------------------------------------------------------------
   Yu Cheng
   ICS 321 Assignment 2
   October 23, 2008

   PART 2.3
   Write a test SQL script that will validate the business rule that only
   one badge number can be assigned to a person.
   ------------------------------------------------------------------------- */

USE DB9975
GO

-- I will perform two checks for the uniqueness as described in the metadata
-- dictionary.  First, it should not be possible to create two badges with
-- the same id.  If that were the case, then one person could reference
-- two badges with the same badge number but issued by different people at
-- different times.  This procedure will ensure this is not possible.

CREATE PROCEDURE EnsureNoBadgeDuplicates AS
	DECLARE @BadgeId   INT
	DECLARE @PersonId  INT
	DECLARE @IssueDate DATETIME

	-- Find an existing badge id and person id, and use the current time.
	SELECT @BadgeId   = MIN(Id) FROM BADGE
	SELECT @PersonId  = MIN(Id) FROM PERSON
	SELECT @IssueDate = GETDATE()

	-- Try to add a duplicate. This will fail.
	INSERT INTO BADGE (       Id, IssuingPerson,  IssueDate )
	VALUES            ( @BadgeId,     @PersonId, @IssueDate )
GO

-- Try to add a duplicate. This will fail.

EXECUTE EnsureNoBadgeDuplicates
GO

-- Remove the temporary procedure.

DROP PROCEDURE EnsureNoBadgeDuplicates
GO

-- Similarly, it should not be possible to assign the same badge number
-- to two people at the same time.

CREATE PROCEDURE EnsureSingleBadgeAssignments AS
	DECLARE @BadgeId   INT
	DECLARE @PersonId  INT

	-- Find the last person id and any badge id.
	SELECT @PersonId  = MAX(Id) FROM PERSON
	SELECT @BadgeId   = MIN(Id) FROM BADGE

	-- Get a unique person id.
	SET @PersonId = @PersonId + 1

	-- Try to add a non-unique field value. This will fail.
	INSERT INTO PERSON
	(
		Id,
		FirstName,
		LastName,
		MiddleName,
		DateOfBirth,
		EmploymentStatus,
		Supervisor,
		Badge
	)
	VALUES
	(
		@PersonId,
		'FirstName',
		'LastName',
		'M',
		'10/13/1984',
		'F',
		NULL,
		@BadgeId
	)
GO

-- Try to add a non-unique field value. This will fail.

EXECUTE EnsureSingleBadgeAssignments
GO

-- Remove the temporary procedure.

DROP PROCEDURE EnsureSingleBadgeAssignments
GO