--------------------------------------------------------------------------------
--
--   Copyright (c) 2012 Software Gems Pty Ltd
--   Derek Asirvadem  03 Feb 12
--
--------------------------------------------------------------------------------
-- Limit Count of Child Rows via CHECK Constraint
-- Exclude Disqualified Rows via CHECK Constraint
--    Function & DDL code
--------------------------------------------------------------------------------
-- Note
--   - Declarative Referential Integrity is used throughout
--   - All Constraints are implemented Declaratively
--   - Using SQL-92
--   - Tested on Sybase ASE 15.0.2
--
--   - The CHECK Constraint in its vanilla form allows the inserted row (only)
--     to be checked
--   - When the inserted row needs to be CHECKed against other rows (in either
--     the same table, or another table), that code is placed in a Function,
--     which is called by the CHECK Constraint
--   - Although SQL is supposed to allow a subquery in the CHECK, not all
--     platforms provide it.
--------------------------------------------------------------------------------
-- Related Data Model:
--     http://www.softwaregems.com.au/Documents/The%20Third%20Manifesto/Miscellany/Problem%20Model.pdf
--------------------------------------------------------------------------------
--
-- Re: TTM Post Person/Problem/Solution
--   1. Allegation that "Count of child rows cannot be limited" is false
--   2. Allegation that "Composer cannot be excluded from Solvers" is false
--
-- Resolved via
--   a. Relational Normalisation of the Data
--      As usual, the imbecile Hugh Darwen uses un-normalised tables to propose
--      that an "insurmountable" problem exists.  Which proves that either:
--      - he is grossly dishonest, using a Straw Man argument, or
--      - he is incompetent re the Relational Model and Normalisation
--      Normalising the data deploys the attributes such that they are
--      subordinate to the relevant Relational Keys.  Refer to the Data Model.
--   b. SQL Implementation vs SQL Standard
--      As usual, the fraud Hugh Darwen alleges there is a problem with SQL that
--      prevents the facility being attempted.  No such problem or limitation
--      exists in any commercial SQL implementation.
--   c. Simple Declarative Constraints
--      A commercial implementation (the spirit & intent of SQL-92, not the
--      syntax, which is an impossible double-bind set up by the morons on the
--      SQL Committee) is used.  Feel free to test it on your choice of SQL
--      platform (the free-ware and the non-SQLs are too primitive).
--
-- TweedleDum's post re "SQL is Broken" and requirements:
--     http://www.softwaregems.com.au/Documents/The%20Third%20Manifesto/Miscellany/12_01_25%20HD.pdf
-- My detailed response "SQL Is Not 'Broken', the Detractors Are":
--     http://www.softwaregems.com.au/Documents/The%20Third%20Manifesto/Miscellany/12_02_04%20DA.pdf
--
--------------------------------------------------------------------------------
-- Not Quite Relational
--
-- The problem has been solved by Normalising *just* the tables given, in order
-- to:
-- a. avoid exceeding the scope of the stated problem, and
-- b. to demonstrate the use of a Function to CHECK rows beyond the row being
--    INSERTed.
-- However the data model shown is not completely Normalised, it does not comply
-- with Codd's Relational Model.  If it were, the model would be simpler, and the
-- solution would also be simpler.  If you are interested in the Relational
-- solution, abandon this file, and examine:
--     http://www.softwaregems.com.au/Documents/Article/Normalisation/Bridge.pdf
--     http://www.softwaregems.com.au/Documents/Tutorial/Bridge.sql
--------------------------------------------------------------------------------

------------
-- Person --
------------

CREATE TABLE Person (
    PersonId      PersonId    NOT NULL,		-- INT
    CONSTRAINT UC_PK
        PRIMARY KEY CLUSTERED ( PersonId )
    )
GO

-------------
-- Problem --
-------------

CREATE TABLE Problem (
    ProblemId      ProblemId    NOT NULL,	-- INT
    ComposerId     PersonId     NOT NULL,
    Name           _Description NOT NULL,	-- CHAR(32)
    Score          _IntTiny     NOT NULL,
    CONSTRAINT UC_PK
        PRIMARY KEY CLUSTERED ( ProblemId ),
    CONSTRAINT U__AK
        UNIQUE ( Name ),
    CONSTRAINT Problem_Valid_Score_ck
        CHECK ( Score >= 1 AND Score <= 8 ),
    CONSTRAINT Person_Composed_Problem_fk
        FOREIGN KEY ( ComposerId ) REFERENCES Person( PersonId )
    )
GO

----------------
-- Composer_2 --
----------------

CREATE TABLE Composer_2 (
    ProblemId      ProblemId    NOT NULL,
    ComposerId     Personid     NOT NULL,
    CONSTRAINT UC_PK
        PRIMARY KEY CLUSTERED ( ProblemId ),
    CONSTRAINT Problem_Has_Composer_2_fk
        FOREIGN KEY ( ProblemId ) REFERENCES Problem( ProblemId ),
    CONSTRAINT Composer_JointComposed_Composer_2_fk
        FOREIGN KEY ( ComposerId ) REFERENCES Composer( ComposerId )
    )
GO

--------------
-- Solution --
--------------

CREATE TABLE Solution (
    ProblemId     ProblemId   NOT NULL,
    SolverId      PersonId    NOT NULL,
    CONSTRAINT UC_PK
        PRIMARY KEY CLUSTERED ( ProblemId, SolverId ),
    CONSTRAINT Problem_FellTo_Solution_fk
        FOREIGN KEY ( ProblemId ) REFERENCES Problem( ProblemId ),
    CONSTRAINT Person_Solved_Solution_fk
        FOREIGN KEY ( SolverId ) REFERENCES Person( PersonId )
    )

--------------------
-- Count Function --
--------------------

DROP   FUNCTION Solution_Count_fn
GO

CREATE FUNCTION Solution_Count_fn (
--
-- Function: Return COUNT(Solver) for Problem
--
        @ProblemId      ProblemId
        )
    RETURNS TINYINT
AS
    RETURN ( SELECT COUNT( SolverId )
        FROM  Solution
        WHERE ProblemId = @ProblemId
        )
GO
--------------------------------------
-- Implement Declarative Constraint --
--------------------------------------

ALTER TABLE Solution
    DROP CONSTRAINT Solution_Solver_Max_4_ck
GO
ALTER TABLE Solution
    ADD  CONSTRAINT Solution_Solver_Max_4_ck
        -- GT, not GTE, this references the existing rows
        CHECK ( dbo.Solution_Count_fn ( ProblemId ) < 4 )
GO

-----------------------
-- Prerequisite Data --
-----------------------

INSERT Person   VALUES (1)  -- Composers
GO
INSERT Person   VALUES (21) -- Solvers
INSERT Person   VALUES (22)
INSERT Person   VALUES (23)
INSERT Person   VALUES (24)
INSERT Person   VALUES (25)
GO
INSERT Problem  VALUES (91, 1, "Global Warming")
GO
INSERT Solution VALUES (91, 21)
INSERT Solution VALUES (91, 22)
INSERT Solution VALUES (91, 23)
INSERT Solution VALUES (91, 24)
GO

---------------------------------
-- Test: Solution_Solver_Max_4 --
---------------------------------

SELECT * FROM Solution  -- ResultSet 1
GO
SELECT [SolverCount] = dbo.Solution_Count_fn ( 91 )  -- ResultSet 2
GO
INSERT Solution VALUES (91, 25)  -- da fith Solver, insert fails
SELECT * FROM Solution
SELECT [SolverCount] = dbo.Solution_Count_fn ( 91 )
GO
-- INSERT failed with "Check constraint violation Solution_Solver_Max_4_ck"
-- ResultSet 3 is the same as ResultSet 1
-- ResultSet 4 is the same as ResultSet 2


-------------------------
-- ComposerId Function --
-------------------------
DROP   FUNCTION GetComposer_fn
GO
CREATE FUNCTION GetComposer_fn (
--
-- Function: Return ComposerId for ProblemId
--
        @ProblemId      ProblemId
        )
    RETURNS INT
AS
    RETURN ( SELECT ComposerId
        FROM  Problem
        WHERE ProblemId = @ProblemId
        )
GO

--------------------------------------
-- Implement Declarative Constraint --
--------------------------------------

ALTER TABLE Solution
    DROP CONSTRAINT Solution_Solver_Not_Composer_ck
GO
ALTER TABLE Solution
    ADD  CONSTRAINT Solution_Solver_Not_Composer_ck
        CHECK ( dbo.GetComposer_fn ( ProblemId ) != SolverId )
GO

ALTER TABLE Composer_2
    DROP CONSTRAINT Solution_Solver_Not_Composer_ck
GO
ALTER TABLE Composer_2
    ADD  CONSTRAINT Composer2_Not_Composer1_ck
        CHECK ( dbo.GetComposer_fn ( ProblemId ) != ComposerId )
GO

----------------------------------------
-- Test: Solution_Solver_Not_Composer --
----------------------------------------

INSERT Problem  VALUES (93, 1, "Neanderthal Hunting for Beginners")
GO
INSERT Solution VALUES (93, 21)  -- Solver not Composer, Succeeds
GO
INSERT Solution VALUES (93, 1)   -- Attempt Solver is Composer
GO
-- INSERT failed with "Check constraint violation Solution_Solver_Not_Composer_ck"

------------------
-- Person.Score --
------------------
--
-- Is a derived value, and thus should not be stored.  If it is, it would
-- violate 3NF, and create an Update Anomaly.
--
SELECT PersonId,
       [Score] = (
    SELECT SUM( Score )
        FROM Solution S,
        JOIN Problem  PP ON S.ProblemId = PP.ProblemId
        WHERE S.SolverId = P.PersonId
        )
    FROM Person P
