\echo
\echo
\echo $Id: Infrastructure-VoteSchema.sql 475 2008-02-21 20:49:51Z dflater $


-- The schema for core requirements is built in five layers.
--
-- 1.  Translation of the data model.  This layer contains all of the
-- tables and data.  The other layers are comprised entirely of views.
--
-- 2.  Conveniences defined over the data model.
--
-- 3.  Adaptation layer.  This layer translates the raw voter inputs
-- per the data model into the effective voter inputs required by the
-- logic model.  This involves alias reconciliation, implementation of
-- straight party voting, and generation of default (0) values for
-- ballot positions that were not voted.
--
-- 4.  Data integrity checks.  For those integrity constraints that are
-- too complex to code directly within the tables, a series of views
-- exists to look for problems.  All of the integrity checking views
-- should always be empty.  If data appear in any of the views, the input
-- was invalid and the results of the model will be invalid.
--
-- 5.  Translation of the logic model.


----------------------------------------------------------------------------

-- Layer 1:  Translation of the data model.

-- SQL realization of Vote Data Model for Core Requirements,
-- rev. 2007-03-05.

-- The following transforms are used to render the UML model as SQL.
--
-- 1.  At the most basic level, a table represents a class, the
-- columns of the table represent the attributes of that class, and
-- the rows of the table represent the instances of that class.
--
-- 2.  Object identity (haecceity) is implemented either by using an
-- existing identifier as primary key or by using a synthetic
-- identifier of integer type, as convenient.
--
-- 3.  Associations to at most 1 instance of another class are
-- implemented using foreign keys within the relevant table with a
-- not-null constraint if the minimum multiplicity is 1.  Associations
-- of higher multiplicity are reified as separate tables.
--
-- 4.  Attributes of multiplicity greater than 1 are treated as
-- associations and reified as separate tables.
--
-- 5.  Enums are implemented using the names of the enum values as
-- identifiers.  Integrity is maintained by creating a table
-- containing the enum values and making attributes of that enum type
-- into foreign keys on that table.


-- enum
create table BallotCategory (
  Name  Text  primary key
);
insert into BallotCategory values
  ('Early'), ('Regular'), ('InPerson'), ('Absentee'), ('Provisional'),
  ('Challenged'), ('NotRegistered'), ('WrongPrecinct'), ('IneligibleVoter');


-- enum
create table ContestCountingLogic (
  Name  Text  primary key
);
insert into ContestCountingLogic values
  ('N-of-M'), ('Cumulative'), ('Ranked order'), ('Straight party selection');


-- class
create table ReportingContext (
  Name  Text  primary key
);


-- class
create table Party (
  Name  Text  primary key
);


-- class
create table Contest (
  ContestId      Integer  primary key,
  Description    Text     not null,
  CountingLogic  Text     not null references ContestCountingLogic,
  N              Integer  not null check (N > 0),
  MaxWriteIns    Integer  not null check (MaxWriteIns between 0 and N),
  Rotate         Boolean  not null,

  -- Straight party selections must be 1-of-M with no write-ins.
  check (CountingLogic <> 'Straight party selection' or
          (N = 1 and MaxWriteIns = 0))
);


-- class
create table Choice (
  ChoiceId     Integer  primary key,
  ContestId    Integer  not null references Contest,
  Name         Text     not null,
  Affiliation  Text     references Party,    -- named association
  IsWriteIn    Boolean  not null
);


-- class
create table BallotStyle (
  StyleId  Integer  primary key,
  Name     Text     not null
);


-- class
create table Ballot (
  BallotId  Integer  primary key,
  StyleId   Integer  not null references BallotStyle,
  Accepted  Boolean  not null
);


-- attribute Ballot::Categories
create table BallotCategoryAssociation (
  BallotId  Integer  references Ballot,
  Category  Text     references BallotCategory,
  primary key (BallotId, Category)
);


-- association class
create table VoterInput (
  BallotId  Integer  references Ballot,
  ChoiceId  Integer  references Choice,
  Value     Integer  not null check (Value > 0),
  primary key (BallotId, ChoiceId)
);


-- association class
create table Endorsement (
  Party     Text     references Party,
  ChoiceId  Integer  references Choice,
  Value     Integer  not null check (Value > 0),
  primary key (Party, ChoiceId)
);


-- named association
create table Alias (
  AliasId   Integer  primary key references Choice,  -- The unwanted alias
  ChoiceId  Integer  not null references Choice,     -- The canonical choice
  check (ChoiceId <> AliasId)                 -- Circular aliases are no good
);


-- unnamed association
create table BallotStyleContestAssociation (
  StyleId    Integer  references BallotStyle,
  ContestId  Integer  references Contest,
  primary key (StyleId, ContestId)
);


-- unnamed association
create table BallotStyleReportingContextAssociation (
  StyleId           Integer  references BallotStyle,
  ReportingContext  Text     references ReportingContext,
  primary key (StyleId, ReportingContext)
);


-- unnamed association
create table BallotReportingContextAssociation (
  BallotId          Integer  references Ballot,
  ReportingContext  Text     references ReportingContext,
  primary key (BallotId, ReportingContext)
);


----------------------------------------------------------------------------

-- Layer 2:  Conveniences.


-- Identify all Contests that appear on a given Ballot, excluding
-- ranked order contests.

create view FilteredBallotContestAssociation (BallotId, ContestId) as
  select BallotId, ContestId
    from Ballot
      natural join BallotStyleContestAssociation
      natural join Contest
    where CountingLogic <> 'Ranked order';


-- Merge reporting contexts inherited from ballot style with reporting
-- contexts specified on ballot instances.  Duplicates are suppressed.

create view ReportingContextAssociationMerge (BallotId, ReportingContext) as
    select BallotId, ReportingContext
      from BallotReportingContextAssociation
  union
    select BallotId, ReportingContext
      from Ballot natural join BallotStyleReportingContextAssociation;


-- Identify all canonical Choices for which a valid VoterInput could
-- exist (those contained in the applicable BallotStyles), excluding
-- aliases.

create view VotableChoices (BallotId, ChoiceId) as
  select BallotId, ChoiceId
    from Ballot
      natural join BallotStyleContestAssociation
      natural join Choice
    where ChoiceId not in
      (select AliasId from Alias);


-- Identify all Contests that are relevant in a given
-- ReportingContext.  This includes those appearing in a BallotStyle
-- associated with the context and those appearing in a Ballot
-- associated with the context.  A BallotStyle association can make a
-- Contest relevant even if there are no applicable Ballots.

create view ReportingContextContestAssociation (ReportingContext, ContestId) as
    select ReportingContext, ContestId
      from BallotStyleReportingContextAssociation
        natural join BallotStyleContestAssociation
  union
    select ReportingContext, ContestId
      from BallotReportingContextAssociation
        natural join Ballot
        natural join BallotStyleContestAssociation;


-- FilteredContextContestAssociation is
-- ReportingContextContestAssociation excluding ranked order contests.

create view FilteredContextContestAssociation (ReportingContext, ContestId) as
  select ReportingContext, ContestId
    from ReportingContextContestAssociation
      natural join Contest
    where CountingLogic <> 'Ranked order';


-- Identify all Choices that are relevant in a given ReportingContext.
-- This is derived from ReportingContextContestAssociation.
-- Not used, not needed.
/*
create view ReportingContextChoiceAssociation (ReportingContext, ChoiceId) as
  select ReportingContext, ChoiceId
    from ReportingContextContestAssociation
      natural join Choice;
*/


-- FilteredContextChoiceAssociation identifies all Choices that are
-- relevant in a given ReportingContext, excluding aliases and choices
-- from ranked order contests.

create view FilteredContextChoiceAssociation (ReportingContext, ChoiceId) as
  select ReportingContext, ChoiceId
    from FilteredContextContestAssociation
      natural join Choice
    where ChoiceId not in
      (select AliasId from Alias);


-- The views BallotCounts, BallotCountsByConfiguration,
-- BallotCountsByCategory, BallotCountsByCategoryAndConfiguration,
-- BlankBallotCounts, and BlankBallotCountsByConfiguration produce the
-- ballot counts that are required in post-voting reports.

-- BallotCounts and BlankBallotCounts report zeroes for contexts
-- having no applicable ballots.  The other views suppress rows
-- pertaining to combinations of context, category and configuration
-- that have no applicable ballots.

create view BallotCounts (ReportingContext, Read, Counted) as
  select Name, count(BallotId), count (nullif (Accepted, false))
    from Ballot
      natural join ReportingContextAssociationMerge
      right outer join ReportingContext on (Name = ReportingContext)
    group by Name;

create view BallotCountsByConfiguration (ReportingContext, StyleId,
                                         Read, Counted) as
  select ReportingContext, StyleId, count(*), count (nullif (Accepted, false))
    from Ballot natural join ReportingContextAssociationMerge
    group by ReportingContext, StyleId;

create view BallotCountsByCategory (ReportingContext, Category,
                                    Read, Counted) as
  select ReportingContext, Category, count(*), count (nullif (Accepted, false))
    from Ballot
      natural join ReportingContextAssociationMerge
      natural join BallotCategoryAssociation
    group by ReportingContext, Category;

create view BallotCountsByCategoryAndConfiguration (ReportingContext, StyleId,
                                                    Category, Read, Counted) as
  select ReportingContext, StyleId, Category, count(*),
         count (nullif (Accepted, false))
    from Ballot
      natural join ReportingContextAssociationMerge
      natural join BallotCategoryAssociation
    group by ReportingContext, StyleId, Category;

-- Original formulation did not perform well on larger scenarios:
-- create view BlankBallot (BallotId, StyleId, Accepted) as
--   select BallotId, StyleId, Accepted
--     from Ballot
--     where BallotId not in
--       (select BallotId from VoterInput);

create view BlankBallot (BallotId, StyleId, Accepted) as
  select BallotId, StyleId, Accepted
    from Ballot natural left outer join VoterInput
    where Value is null;

create view BlankBallotCounts (ReportingContext, Read, Counted) as
  select Name, count(BallotId), count (nullif (Accepted, false))
    from BlankBallot
      natural join ReportingContextAssociationMerge
      right outer join ReportingContext on (Name = ReportingContext)
    group by Name;

create view BlankBallotCountsByConfiguration (ReportingContext, StyleId,
                                              Read, Counted) as
  select ReportingContext, StyleId, count(*), count (nullif (Accepted, false))
    from BlankBallot natural join ReportingContextAssociationMerge
    group by ReportingContext, StyleId;


----------------------------------------------------------------------------

-- Layer 3:  Adaptation.


-- The VoterInput table has a primary key on (BallotId, ChoiceId), so
-- there is at most one row for any given ballot position on any given
-- ballot.  Deliberately, the anti-aliasing and straight party voting
-- views do not preserve this constraint in the event that double
-- votes occur.  Both of these cases are treated as errors for testing
-- purposes, and the errors are most easily located by looking for
-- duplicate keys.  This is done by the integrity view DoubleVotes.


-- Alias reconciliation.

-- Double votes resulting from anti-aliasing will result in multiple
-- rows with the same (BallotId, ChoiceId).

create view AntiAliasedVoterInput (BallotId, ChoiceId, Value) as
  select BallotId, coalesce (Alias.ChoiceId, VoterInput.ChoiceId), Value
    from VoterInput left outer join Alias
      on VoterInput.ChoiceId = Alias.AliasId;


-- Straight party voting.


-- Straight party contests are implicitly 1-of-M contests.  If a
-- straight party contest is overvoted, it is irrelevant.

create view ValidStraightPartyVotes (BallotId, Party) as
  select BallotId, max(Name)      -- There can be only one.  See below.
    from AntiAliasedVoterInput
      natural join Choice
      natural join Contest
    where CountingLogic = 'Straight party selection'
    group by BallotId
    having sum(Value) = 1;        -- There can be only one.


-- Generate the implied straight party votes only for contests that
-- appear in the ballot style.

create view ImpliedStraightPartyVotes (BallotId, ChoiceId, Value) as
  select BallotId, ChoiceId, Value
    from VotableChoices
      natural join Endorsement
      natural join ValidStraightPartyVotes;


-- Merge implied straight party votes with regular votes.  Double
-- votes resulting from straight party voting will result in multiple
-- rows with the same (BallotId, ChoiceId).  Double votes from anti-
-- aliasing are retained.

create view VoterInputMerge (BallotId, ChoiceId, Value) as
    select BallotId, ChoiceId, Value from AntiAliasedVoterInput
  union all
    select BallotId, ChoiceId, Value from ImpliedStraightPartyVotes;


-- Generate zeroes for ballot positions that were not voted.

create view EffectiveInput (BallotId, ChoiceId, Value) as
  select BallotId, ChoiceId, coalesce (Value, 0)
    from VotableChoices natural left outer join VoterInputMerge;


----------------------------------------------------------------------------

-- Layer 4:  Integrity checks.

-- All of these views should be empty.


-- Voter inputs may not exceed 1 for N-of-M and straight party, and
-- may not exceed N for cumulative.

-- For straight party selection, N = 1.

create view OutOfRangeVoterInputs as
  select BallotId, ChoiceId, Value
    from VoterInput
      natural join Choice
      natural join Contest
    where (CountingLogic <> 'Ranked order' and Value > N)
    or (CountingLogic = 'N-of-M' and Value > 1);


-- Straight party endorsements must meet the same constraints.

create view OutOfRangeEndorsements as
  select Party, ChoiceId, Value
    from Endorsement
      natural join Choice
      natural join Contest
    where (CountingLogic <> 'Ranked order' and Value > N)
    or (CountingLogic = 'N-of-M' and Value > 1);


-- Ballots cannot have votes in contests that do not appear in their
-- ballot styles.

create view ExtraneousInputs as
  select BallotId, ChoiceId, Value
    from VoterInput
      natural join Ballot
      natural join Choice
    where ContestId not in
      (select ContestId
         from BallotStyleContestAssociation
         where BallotStyleContestAssociation.StyleId = Ballot.StyleId);


-- Every ballot must be reported somehow.

create view UnreportedBallots as
    select BallotId from Ballot
  except
    select BallotId from ReportingContextAssociationMerge;


-- The handling of double votes for a given candidate resulting from
-- write-in reconciliation or straight party overrides is deliberately
-- unspecified in the VVSG, so for testing purposes they are
-- considered errors.

create view DoubleVotes as
  select BallotId, ChoiceId, count(*), sum(Value)
    from VoterInputMerge
    group by BallotId, ChoiceId
    having count(*) > 1;


-- You can only alias a choice in the same contest.

create view CrossContestAliases (AliasId,  AliasName, AliasContestId,
                                 ChoiceId, ChoiceName, ChoiceContestId) as
  select AliasId,        AliasChoice.Name,  AliasChoice.ContestId,
         Alias.ChoiceId, ChoiceChoice.Name, ChoiceChoice.ContestId
    from Alias
      join Choice ChoiceChoice on Alias.ChoiceId = ChoiceChoice.ChoiceId
      join Choice AliasChoice  on Alias.AliasId  = AliasChoice.ChoiceId
    where ChoiceChoice.ContestId <> AliasChoice.ContestId;


-- You cannot alias an alias.

create view DoubleIndirectAliases (AliasId, AliasedAliasId) as
  select AliasId, ChoiceId
    from Alias
    where ChoiceId in
      (select AliasId from Alias);


-- I cannot think of a use case where it makes sense for a
-- non-write-in position to be an alias, but neither is there any
-- technical reason why it must be disallowed.  This "feature" is used
-- to create an error condition in 0-integrity-DoubleIndirectAlias.sql.


-- No ballot style can contain more than one straight party contest.

create view MoreThanOneStraightPartyContest as
  select StyleId
    from BallotStyleContestAssociation natural join Contest
    where CountingLogic = 'Straight party selection'
    group by StyleId
    having count(*) > 1;


-- The names of the choices in a straight party contest must reference
-- the names of the Parties.

create view NonExistentParties (ContestId, Name) as
  select ContestId, Name
    from Choice natural join Contest
    where CountingLogic = 'Straight party selection'
    and Name not in
      (select Name from Party);


-- Straight party contests cannot be straight-party-votable.

create view CircularStraightPartyEndorsements as
  select Party, ChoiceId
    from Endorsement
      natural join Choice
      natural join Contest
    where CountingLogic = 'Straight party selection';


-- Straight party endorsements must endorse the canonical candidate,
-- not an alias.  (To allow it seems a needless complication, and in
-- this case, there *is* a technical reason to disallow it.  The logic
-- becomes circular.)

create view EndorsedAliases (Party, ChoiceId, Value) as
  select Party, Endorsement.ChoiceId, Value
    from Endorsement
      join Alias on Endorsement.ChoiceId = Alias.AliasId;


-- The treatment of straight party overrides is unspecified in the
-- VVSG, so for testing purposes they are simply prohibited.  If a
-- straight party vote is implied in a given contest, there cannot be
-- a positive voter input in that context.

-- Original formulation did not perform well on larger scenarios:
-- create view StraightPartyOverrides (BallotId, ContestId, ChoiceId, Value) as
--   select BallotId, ContestId, ChoiceId, Value
--     from (VoterInput natural join Choice) VI
--     where exists
--       (select *
--         from (ImpliedStraightPartyVotes natural join Choice) ISPV
--         where ISPV.BallotId = VI.BallotId
--         and ISPV.ContestId = VI.ContestId);

create view StraightPartyOverrides (BallotId, ContestId, ChoiceId, Value) as
  select distinct BallotId, ContestId, VoterInput.ChoiceId, VoterInput.Value
    from VoterInput
      natural join Choice Choice1
      join (ImpliedStraightPartyVotes natural join Choice Choice2)
        using (BallotId, ContestId);


-- A Ballot cannot have VoterInput for more write-in Choices in a
-- given Contest than is allowed by the MaxWriteIns attribute of the
-- Contest.

create view TooManyWriteIns (BallotId, ContestId, WriteInsCount) as
  select BallotId, ContestId, count (nullif (IsWriteIn, false))
    from VoterInput
      natural join Choice
      natural join Contest
    group by BallotId, ContestId, MaxWriteIns
    having count (nullif (IsWriteIn, false)) > MaxWriteIns;


----------------------------------------------------------------------------

-- Layer 5:  Translation of logic model (2007-03-05) into SQL.

-- The following transforms are used to render the logic model as SQL.
--
-- 1.  Each function is replaced by a view in which the parameters
-- form the primary key and the last column is the value of the
-- function.
--
-- 2.  Time parameters (t) are factored out.  All views implicitly
-- project results for the time t corresponding to the current state
-- of the database.
--
-- 3.  When a function takes both a contest and a choice as
-- parameters, the contest parameter is omitted.  With the data model
-- used here, the Contest can be inferred from the Choice.
--
-- 4.  Logic is translated into those SQL constructs that are most
-- transparently equivalent.
--
-- 5.  Ranked order contests, which are not handled by the logic
-- model, are suppressed.
--
-- 6.  Irrelevant values, such as zero tallies for choices that do not
-- appear in the applicable ballot style or contests that are not
-- relevant in the applicable reporting context, are suppressed.


-- Number of votes cast in a given contest by a given ballot.  Only
-- the contests appearing in the ballot style are listed.  (The logic
-- model defines others to be 0.)
-- Note that a contest might have no choices.

create view S (ContestId, BallotId, S_val) as
  select ContestId, BallotId, coalesce (sum (Value), 0)
    from FilteredBallotContestAssociation
      natural left outer join Choice
      natural left outer join EffectiveInput
    group by ContestId, BallotId;


-- Votes that should be added to the tally, a subset of EffectiveInput.

create view SPrime (ChoiceId, BallotId, SPrime_val) as
  select ChoiceId, BallotId,
    case
      when S_val <= N and Accepted then Value
      else 0
    end
    from EffectiveInput
      natural join Choice
      natural join Contest
      natural join Ballot
      natural join S;


-- The tally for all relevant choices and contexts.

create view T (ChoiceId, ReportingContext, T_val) as
  select ChoiceId, ReportingContext, coalesce (sum (SPrime_val), 0)
    from FilteredContextChoiceAssociation
      natural left outer join
        (SPrime natural join ReportingContextAssociationMerge)
    group by ChoiceId, ReportingContext;


-- Total tallied votes by contest (convenience).
-- Note that a contest might have no choices.

create view TSum (ContestId, ReportingContext, TSum_val) as
  select ContestId, ReportingContext, coalesce (sum (T_val), 0)
    from ReportingContextContestAssociation
      natural left outer join Choice
      natural left outer join T
    group by ContestId, ReportingContext;


-- Convenience to retrieve all of the S_val vote counts for each
-- relevant combination of context and contest.  For each relevant
-- combination of context and contest that contains no ballots, there
-- is a single row with nulls in the last three columns.

create view VotesByContestAndContext (ContestId, N, ReportingContext,
                                      BallotId, Accepted, S_val) as
  select ContestId, N, ReportingContext, BallotId, Accepted, S_val
    from FilteredContextContestAssociation
      natural join Contest
      natural left outer join (S natural join ReportingContextAssociationMerge)
      natural left outer join Ballot;


-- Overvotes.

create view O (ContestId, ReportingContext, O_val) as
  select ContestId, ReportingContext, sum (
      case
        when S_val > N and Accepted then N
        else 0
      end                                 )
    from VotesByContestAndContext
    group by ContestId, ReportingContext;


-- Undervotes.

create view U (ContestId, ReportingContext, U_val) as
  select ContestId, ReportingContext, sum (
      case
        when S_val <= N and Accepted then N - S_val
        else 0
      end                                 )
    from VotesByContestAndContext
    group by ContestId, ReportingContext;


-- Number of ballots that should have been counted.

create view K (ContestId, ReportingContext, K_val) as
  select ContestId, ReportingContext,
    (select count(*)
       from Ballot
         natural join ReportingContextAssociationMerge
         natural join BallotStyleContestAssociation
       where ReportingContextAssociationMerge.ReportingContext
         = FilteredContextContestAssociation.ReportingContext
       and BallotStyleContestAssociation.ContestId
         = FilteredContextContestAssociation.ContestId
       and Accepted)
    from FilteredContextContestAssociation;


-- "Every vote must be accounted for."

create view Balance (ContestId, ReportingContext, Discrepancy) as
  select ContestId, ReportingContext, K_val * N - (TSum_val + O_val + U_val)
    from K
      natural join TSum
      natural join O
      natural join U
      natural join Contest;
