/*
ISC 561
ERD, 
Transforming data models into database design,
Database implementation (SQL-DDL/DML)

In-class demo: AMCIS database (see handout)

To execute this script use the AMCIS database and click the run button

Candidate keys pp. 104-105, 243(UNIQUE constraint for AK)
Null status pp. 195
Default values pp. 197
Data constraints pp. 194
   Domain
   Range
   IntraRelation constraint (between columns in same table)
   InterRelation constraint (between columns in different tables)
Verify normalization pp. 197
   Multivalued attributes pp. 204
Create relationships (1:1), (1:M), (M:N), recursive (1:M), (M:N)
   1. Insert foreign key into child relation 
   2. create referential integrity constraint 
   3. enforce minimum cardinality (this is trickier...) pp. 214 - 222

Note that I have sample code for most types of transformations at the bottom of this file

*/      
SET NOCOUNT ON

/*
Create FKs and enforce constraints

--Add a foreign key attribute
ALTER TABLE tblChildTableName
ADD ForeignKeyID INT NOT NULL REFERENCES tblParentTableName(PrimaryKeyID)

*/

--sp_help tblParticipant

/*
tblInstitution |O ----- O< tblParticipant
*/

ALTER TABLE tblParticipant
ADD InstitutionID INT NULL --By making the FK NULLable, an Institution is optional (O)
GO

ALTER TABLE tblParticipant
ADD CONSTRAINT Institution_Participant_fk FOREIGN KEY (InstitutionID)
REFERENCES tblInstitution(InstitutionID)
GO

--ALTER TABLE tblParticipant
--DROP CONSTRAINT Institution_Participant_fk

/*
Add an IS-A foreign key in a SubType relations
The UNIQUE constraint enforces the 1:1 relationship

If you add the 1:1 FK later, it is a two step process: 
   1. Add the FK reference
   2. Enforce that the FK values be UNIQUE (IS A)

--Add a foreign key attribute
ALTER TABLE tblChildTableName
ADD ForeignKeyID INT NOT NULL REFERENCES tblParentTableName(PrimaryKeyID)

--Add an IS-A constraint in a SubType relation
ALTER TABLE tblTableName
ADD CONSTRAINT ConstraintName_Unique UNIQUE(attributelist) -- makes this an alternate key

*/

/*
tblParticipant ||--------O| tblEditor
*/

ALTER TABLE tblEditor
ADD ParticipantID INT NULL
GO

ALTER TABLE tblEditor
ALTER COLUMN ParticipantID INT NOT NULL
GO

ALTER TABLE tblEditor
ADD CONSTRAINT Participant_Editor_IS_A FOREIGN KEY (ParticipantID)
REFERENCES tblParticipant(ParticipantID)
GO

ALTER TABLE tblEditor
ADD CONSTRAINT Participant_Editor_IS_A_UNIQUE UNIQUE(ParticipantID)
GO

/*
tblParticipant ||--------O| tblTrackChair
*/

--Add the new FK (which in this case is really a copy of the PK in the supertype
--Adds the copy of the PK
ALTER TABLE tblTrackChair
ADD ParticipantID INT NULL
GO

--Implements the hash mark
ALTER TABLE tblTrackChair
ALTER COLUMN ParticipantID INT NOT NULL
GO

--Creates the relationship (1:M)
ALTER TABLE tblTrackChair
ADD CONSTRAINT Participant_TrackChair_IS_A FOREIGN KEY (ParticipantID)
REFERENCES tblParticipant(ParticipantID)
GO

--Forces the relationship to be (1:1), that is, FK must be unique like PK
ALTER TABLE tblTrackChair
ADD CONSTRAINT Participant_TrackChair_IS_A_UNIQUE UNIQUE(ParticipantID)
GO

/*
tblParticipant ||--------O| tblAuthor
*/
ALTER TABLE tblAuthor
ADD ParticipantID INT NULL
GO

ALTER TABLE tblAuthor
ALTER COLUMN ParticipantID INT NOT NULL
GO

ALTER TABLE tblAuthor
ADD CONSTRAINT Participant_Author_IS_A FOREIGN KEY (ParticipantID)
REFERENCES tblParticipant(ParticipantID)
GO

ALTER TABLE tblAuthor
ADD CONSTRAINT Participant_Author_IS_A_UNIQUE UNIQUE(ParticipantID)
GO

/*
tblEditor ||------|< tblSession
*/
--Why "EditorID" rather than "ParticipantID"?
--For one thing,you can't have participantID twice in tblSession
--It makes it clear which subtype is being referenced
--The name of the FK does not have to match the name of the PK
ALTER TABLE tblSession
ADD EditorID INT NOT NULL --Note: I am naming ParticipantID "EditorID"
GO

ALTER TABLE tblSession
ADD CONSTRAINT Editor_Session_fk FOREIGN KEY (EditorID)
REFERENCES tblEditor(ParticipantID)
GO

/*
tblTrackChair ||------|< tblSession
*/
ALTER TABLE tblSession
ADD TrackChairID INT NOT NULL --Note: I am naming ParticipantID "TrackChairID"
GO

ALTER TABLE tblSession
ADD CONSTRAINT TrackChair_Session_fk FOREIGN KEY (TrackChairID)
REFERENCES tblTrackChair(ParticipantID)
GO

/*
tblSession ||------|< tblPresentation
*/
ALTER TABLE tblPresentation
ADD SessionID INT NOT NULL 
GO

ALTER TABLE tblPresentation
ADD CONSTRAINT Session_Presentation_fk FOREIGN KEY (SessionID)
REFERENCES tblSession(SessionID)
GO

/*
tblRoom ||------O< tblSession
*/
ALTER TABLE tblSession
ADD RoomID INT NOT NULL 
GO

ALTER TABLE tblSession
ADD CONSTRAINT Room_Session_fk FOREIGN KEY (RoomID)
REFERENCES tblRoom(RoomID)
GO

/*
tblTimeSlot ||------O< tblSession
*/
ALTER TABLE tblSession
ADD TimeSlotID INT NOT NULL 
GO

ALTER TABLE tblSession
ADD CONSTRAINT TimeSlot_Session_fk FOREIGN KEY (TimeSlotID)
REFERENCES tblTimeSlot(TimeSlotID)
GO

/*
tblPresentation |O--------|| tblPaper
*/
--This is an erro, PaperID needs to be NOT NULL

ALTER TABLE tblPresentation
ADD PaperID INT NULL --NULL because a paper does not have to be a presentation
GO

ALTER TABLE tblPresentation
ADD CONSTRAINT Presentation_IS_A_Paper FOREIGN KEY (PaperID)
REFERENCES tblPaper(PaperID)
GO

ALTER TABLE tblPresentation
ADD CONSTRAINT Presenentation_Paper_IS_A_UNIQUE UNIQUE(PaperID)
GO

/*
tblTrack ||------O< tblPaper
*/
ALTER TABLE tblPaper
ADD TrackID INT NOT NULL 
GO

ALTER TABLE tblPaper
ADD CONSTRAINT Track_Paper_fk FOREIGN KEY (TrackID)
REFERENCES tblTrack(TrackID)
GO

/*
tblTrack ||-----O< tblTrack
*/

--EXEC sp_rename 'tblTrack.SubTrack', 'SubTrackID', 'COLUMN';
--GO

ALTER TABLE tblTrack
ADD SubTrackID INT
GO

ALTER TABLE tblTrack
ADD CONSTRAINT Track_SubTrack_FK FOREIGN KEY (SubTrackID)
REFERENCES tblTrack(TrackID)
GO

/*
tblAuthor >|------|< tblPaper

Add the intersection table to resolve the M:N
*/

CREATE TABLE tblAuthorPaper
(
AuthorPaperID INT IDENTITY(1,1) PRIMARY KEY,
AuthorID INT NOT NULL REFERENCES tblAuthor(ParticipantID),
PaperID INT NOT NULL REFERENCES tblPaper(PaperID)
)

PRINT 'Foreign Key Constraints successfully applied...'


/*
Enforce any NOT NULL constraints

--Make an existing attribute required (NOT NULL)
ALTER TABLE tblTableName
ALTER COLUMN ColumnName INT NOT NULL


Note, this step will not be necessary for attributes
requiring a UNIQUE constraint
*/
ALTER TABLE tblInstitution
ALTER COLUMN ShortName VARCHAR(3) NOT NULL
GO
ALTER TABLE tblInstitution
ALTER COLUMN LongName NVARCHAR(50) NOT NULL
GO
ALTER TABLE tblPresentation
ALTER COLUMN SessionID INT NOT NULL
GO

PRINT 'NOT NULL Constraints successfully applied...'

/*
Enforce DEFAULT constraints

--Add a DEFAULT constraint to an existing attribute
ALTER TABLE tblTableName
ADD CONSTRAINT ConstraintName_Default
DEFAULT 'some value' FOR ColumnName

Why assign default values?
1. Save input for user (common or most likely value)
2. Reduce input errors by putting most common valid value
3. System generated timestamp on a transaction
*/

ALTER TABLE tblInstitution
ADD CONSTRAINT Academic_Yes_Default
DEFAULT 1 FOR Academic
GO

--I mis-typed the attribute LCDProjectorRequired in the CREATE TABLE statement
--EXEC sp_rename 'tblSession.LCDProjectedRequired', 'LCDProjectorRequired', 'COLUMN';
GO

ALTER TABLE tblSession
ADD CONSTRAINT ProjectorRequired_Default
DEFAULT 0 FOR LCDProjectorRequired 
GO

PRINT 'DEFAULT Constraints successfully applied...'

--SELECT * FROM tblSession

/*
Enforce UNIQUE constraints on identifiers

--Add an alternate key constraint to an existing attribute
ALTER TABLE tblTableName
ADD CONSTRAINT ConstraintName_Unique UNIQUE(attributelist) -- makes this an alternate key
*/

ALTER TABLE tblInstitution
ADD CONSTRAINT ShortName_Unique UNIQUE(ShortName) -- makes this an alternate key
GO

ALTER TABLE tblInstitution
ADD CONSTRAINT LongName_Unique UNIQUE(LongName) -- makes this an alternate key
GO

ALTER TABLE tblParticipant
ADD CONSTRAINT First_And_LastName_Unique UNIQUE(FirstName, LastName) -- makes this an alternate key
GO

PRINT 'UNQUE Constraints successfully applied...'


/*
Enforce domain and range constraints

--Add a CHECK constraint to an existing attribute
ALTER TABLE tblTableName
ADD CONSTRAINT ConstraintName_Check
CHECK (tblTableName.ColumnName IN('value1', 'value2', 'value3', 'etc...')) 

ALTER TABLE tblTableName
ADD CONSTRAINT ConstraintName_Check
CHECK (attribute <, >, =, >=, <= some_value)

*/

ALTER TABLE tblEditor
ADD CONSTRAINT Valid_Editor_Title_Check
CHECK (tblEditor.Title IN('Editor','Executive Editor','Associate Editor', 'Co-Chair', 'Chair')) 
GO

ALTER TABLE tblRoom
ADD CONSTRAINT MaxOccupancy_Range_Check
CHECK (MaxOccupancy <500)
GO

PRINT 'CHECK Constraints successfully applied...'


--SELECT * FROM tblPresentation
/*
I was experimenting with something here based on a student question...

ALTER TABLE tblPresentation
ADD PresentationCounter INT NOT NULL

INSERT INTO tblPresentation (PresentationOrder, SessionID, PaperID) values (2, 9, 3)

ALTER TABLE tblPresentation
ADD CONSTRAINT PresentationOrder_Range_check
CHECK (PresentationOrder IN(1,2,3,4,5))
GO
ALTER TABLE tblPresentation
ADD CONSTRAINT SessionID_PresentationOrder_Unique UNIQUE(SessionID, PresentationOrder)
GO

UPDATE tblPresentation SET PresentationCounter = 2 WHERE PresentationID = 11
UPDATE tblPresentation SET PresentationCounter = 1 WHERE PresentationID = 12

UPDATE tblPresentation SET PresentationOrder = PresentationCounter
WHERE SessionID = 9

BEGIN TRY
  BEGIN TRANSACTION

UPDATE tblPresentation SET PresentationOrder = 2 WHERE PresentationID = 11
UPDATE tblPresentation SET PresentationOrder = 1 WHERE PresentationID = 12

  COMMIT TRANSACTION

  PRINT 'Order swapped successfully...'

END TRY
BEGIN CATCH
  DECLARE @ErrorMessage VARCHAR(500)
  SET @ErrorMessage = ERROR_MESSAGE() + ' Rolledback transaction: Order swap.'
  ROLLBACK TRANSACTION
  RAISERROR (@ErrorMessage, 16,1)
END CATCH
GO

*/


/*
ALTER TABLE tblRoom
DROP CONSTRAINT MaxOccupancy_Range_Check
*/

/*
Test cases

INSERT INTO tblRoom (RoomName, MaxOccupancy, ConferenceYear)
   VALUES ('Salon1', 546, GETDATE())

UPDATE tblRoom SET MaxOccupancy = 400 WHERE RoomID = 3
*/


SET NOCOUNT OFF


/*
Sample SQL DDL statements for enforcing constraints
*/

/*
--Add a primary key attribute
ALTER TABLE tblTableName
ADD PrimaryKeyID INT IDENTITY(1,1) PRIMARY KEY

--Add a primary key constraint to an existing attribute
ALTER TABLE tblTableName
ADD CONSTRAINT ConstraintName_PK PRIMARY KEY (PrimaryKeyID)

--Add an IS-A foreign key in a SubType relation
ALTER TABLE tblSubTypeTableName
ADD SuperTypePrimaryKeyID INT NULL UNIQUE --makes this an alternate key

--Add an alternate key attribute
ALTER TABLE tblTableName
ADD AlternateKeyID INT NOT NULL UNIQUE

--Add an alternate key constraint to an existing attribute
ALTER TABLE tblTableName
ADD CONSTRAINT ConstraintName_Unique UNIQUE(attributelist) -- makes this an alternate key

--Add a foreign key attribute
ALTER TABLE tblChildTableName
ADD ForeignKeyID INT NULL REFERENCES tblParentTableName(PrimaryKeyID)

--Add a foreign key to an existing attribute
ALTER TABLE tblChildTableName
ADD CONSTRAINT ConstraintName_fk FOREIGN KEY (ForeignKeyID)
REFERENCES tblParentTableName(ForeignKeyID)

--Make an existing attribute required (NOT NULL)
ALTER TABLE tblTableName
ALTER COLUMN ColumnName INT NOT NULL

--Add a DEFAULT constraint to an existing attribute
ALTER TABLE tblTableName
ADD CONSTRAINT ConstraintName_Default
DEFAULT 'some value' FOR ColumnName

--Add a CHECK constraint to an existing attribute
ALTER TABLE tblTableName
ADD CONSTRAINT ConstraintName_Check
CHECK (tblTableName.ColumnName IN('value1', 'value2', 'value3', 'etc...')) 

ALTER TABLE tblTableName
ADD CONSTRAINT ConstraintName_Check
CHECK (attribute <, >, =, >=, <= some_value)

--Drop a constraint
ALTER TABLE tblTableName
DROP CONSTRAINT ConstraintName_Check

--Drop a column
ALTER TABLE tblTableName
DROP COLUMN ColumnName

*/