--------------------------------------------------------------------------------
--
--   Copyright (c) 2012 Software Gems Pty Ltd
--   Derek Asirvadem  06 Jun 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/Article/Normalisation/Bridge.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
--   (This file, the fully Relational solution, completed some months later,
--   exceeds that discussion).
--
--------------------------------------------------------------------------------

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

CREATE TABLE Person (
    -- a surrogate, to avoid carrying the otherwise wide PK in the child rows
    PersonId      PersonId    NOT NULL,    -- BIGINT
    LastName      Name        NOT NULL,    -- CHAR(32)
    FirstName     Name        NOT NULL,
    Initial       _Code1      NOT NULL,    -- CHAR(1)
    BirthDate     _Date       NOT NULL,
    BirthPlace    Town        NOT NULL,    -- CHAR(32)
    BirthCountry  CountryCode NOT NULL,    -- CHAR(2) ISO-3166
    CONSTRAINT UC_PK
        PRIMARY KEY CLUSTERED ( PersonId ),
    -- the correct, natural Key
    CONSTRAINT U__AK
        UNIQUE ( LastName, FirstName, Initial, BirthDate, BirthPlace, BirthCountry )
    )
GO

--------------
-- Composer --
--------------

CREATE TABLE Composer (
    ComposerId      PersonId    NOT NULL,
    CONSTRAINT UC_PK
        PRIMARY KEY CLUSTERED ( ComposerId ),
    CONSTRAINT Person_Is_Composer_fk
        FOREIGN KEY ( ComposerId ) REFERENCES Person( PersonId )
    )
GO

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

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

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

CREATE TABLE Composer_2 (
    ComposerId     PersonId     NOT NULL,
    ProblemNo      ProblemNo    NOT NULL,
    ComposerId_2   Personid     NOT NULL,
    CONSTRAINT UC_PK
        PRIMARY KEY CLUSTERED ( ComposerId, ProblemNo ),
    CONSTRAINT Problem_Has_Composer_2_fk
        FOREIGN KEY ( ComposerId, ProblemNo ) REFERENCES Problem( ComposerId, ProblemNo ),
    CONSTRAINT Composer_JointComposed_Composer_2_fk
        FOREIGN KEY ( ComposerId_2 ) REFERENCES Composer( ComposerId ),
    CONSTRAINT Composer2_Not_Composer1_ck
        CHECK ( ComposerId_2 != ComposerId )
    )
GO

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

CREATE TABLE Solution (
    ComposerId    PersonId     NOT NULL,
    ProblemNo     ProblemNo   NOT NULL,
    SolverId      PersonId    NOT NULL,
    CONSTRAINT UC_PK
        PRIMARY KEY CLUSTERED ( ComposerId, ProblemNo, SolverId ),
    CONSTRAINT Problem_FellTo_Solution_fk
        FOREIGN KEY ( ComposerId, ProblemNo ) REFERENCES Problem( ComposerId, ProblemNo ),
    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
--
        @ComposerId     PersonId,
        @ProblemNo      ProblemNo
        )
    RETURNS TINYINT
AS
    RETURN ( SELECT COUNT( SolverId )
        FROM  Solution
        WHERE   ComposerId = @ComposerId
            AND ProblemNo  = @ProblemNo
        )
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
        -- Note GT, not GTE, this references the existing rows
        CHECK ( dbo.Solution_Count_fn ( ComposerId, ProblemNo ) < 4 )
GO

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

INSERT Person   VALUES ( 1, "Person", "One", "", 19000101, "Limerick", "IE")  -- Composers
GO

INSERT Person   VALUES (21, "Person", "Twenty One",   19200201, "London", "GB")  -- Solvers
INSERT Person   VALUES (22, "Person", "Twenty Two",   19200202, "San Francisco", "US")
INSERT Person   VALUES (23, "Person", "Twenty Three", 19200203, "Limerick", "IE")
INSERT Person   VALUES (24, "Person", "Twenty Four",  19200204, "New Delhi", "IN")
INSERT Person   VALUES (25, "Person", "Twenty Five",  19200205, "Berlin", "DE")
GO

INSERT Problem  VALUES (1, 1, "Global Warming")
GO

INSERT Solution VALUES (1, 1, 21)
INSERT Solution VALUES (1, 1, 22)
INSERT Solution VALUES (1, 1, 23)
INSERT Solution VALUES (1, 1, 24)  -- Four Solutions
GO

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

SELECT * FROM Solution                              -- ResultSet 1, sort of
GO
SELECT [SolverCount] = dbo.Solution_Count_fn ( 1, 1 )  -- ResultSet 2
GO
SELECT  [Composer] = (
            SELECT RTRIM(LastName) + "," + RTRIM(FirstName)
                FROM Person
                WHERE P.ComposerId = PersonId
                ),
        ProblemNo,
        [Composer2] = CASE C2.ComposerId_2
        WHEN NULL THEN ""
        ELSE (
            SELECT RTRIM(LastName) + "," + RTRIM(FirstName)
                FROM Person
                WHERE C2.ComposerId_2 = PersonId
                )
            END,
        [Problem] = Name,
        [Solutions] = dbo.Solution_Count_fn ( ComposerId, ProblemNo )
    FROM  Problem P,
    LEFT JOIN Composer_2 C2 ON
        (   P.ComposerId = C2.ComposerId
        AND P.ProblemNo  = C2.ProblemNo)
GO
INSERT Solution VALUES (1, 1, 25)  -- da fith Solver, insert fails
SELECT * FROM Solution
SELECT [SolverCount] = dbo.Solution_Count_fn ( 1, 1 )
GO
-- INSERT failed with "Check constraint violation Solution_Solver_Max_4_ck"
-- ResultSet 4 is the same as ResultSet 1
-- ResultSet 5 is the same as ResultSet 2

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

ALTER TABLE Solution
    DROP CONSTRAINT Solution_Solver_Not_Composer_ck
GO
ALTER TABLE Solution
    ADD  CONSTRAINT Solution_Solver_Not_Composer_ck
        CHECK ( ComposerId  != SolverId )
GO

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

INSERT Problem  VALUES (1, 2, "Neanderthal Hunting for Beginners")
GO
INSERT Solution VALUES (1, 2, 21)  -- Solver not Composer, Succeeds
GO
INSERT Solution VALUES (1, 2, 1)   -- Attempt Solver is Composer, fails
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.ComposerId = PP.ComposerId
            AND S.ProblemNo  = PP.ProblemNo
        WHERE   S.SolverId   = P.PersonId
        )
    FROM Person P
