--         File: queens_2_tables_pg.sql
--      Version: 1.2
-- Last Changed: 2016-12-24
--           by: Damir;  https://www.damirsystems.com
--      Project: Eight Queens
--  Description: Part 2; Tables
--           DB: PostgreSQL


-- [p_01] The column labelled (CL) with
-- the assigned column number (CL_NO) exists.
--
CREATE TABLE a_col (
  CL      col_lbl      NOT NULL
, CL_NO   ordinal_num  NOT NULL

, CONSTRAINT  pk_acol PRIMARY KEY (CL)
, CONSTRAINT ak1_acol UNIQUE      (CL_NO)
);

COMMENT ON TABLE a_col IS '
[p_01] The column labelled (CL) with 
the assigned column number (CL_NO) exists.
';

COMMENT ON CONSTRAINT pk_acol ON a_col IS '
(c1.1) Each column is assigned exactly one column number.
';

COMMENT ON CONSTRAINT ak1_acol ON a_col IS '
(c1.2) Each column number is assigned to exactly one column.
';



-- [p_02] The row labelled (RW) with
-- the assigned row number (RW_NO) exists.
--
CREATE TABLE a_row (
  RW      row_lbl      NOT NULL
, RW_NO   ordinal_num  NOT NULL

, CONSTRAINT  pk_arow PRIMARY KEY (RW)
, CONSTRAINT ak1_arow UNIQUE      (RW_NO)
);

COMMENT ON TABLE a_row IS '
[p_02] The row labelled (RW) with
the assigned row number (RW_NO) exists.
';

COMMENT ON CONSTRAINT pk_arow ON a_row IS '
(c2.1) Each row is assigned exactly one row number.
';

COMMENT ON CONSTRAINT ak1_arow ON a_row IS '
(c2.2) Each row number is assigned to exactly one row.
';



-- [p_03] The board square named (SQUARE) is located
-- in the row labelled (RW) and the column labelled (CL).
--
CREATE TABLE board (
  SQUARE  square_name  NOT NULL
, CL      col_lbl      NOT NULL
, RW      row_lbl      NOT NULL

, CONSTRAINT  pk_board PRIMARY KEY (SQUARE)
, CONSTRAINT ak1_board UNIQUE      (CL, RW)

, CONSTRAINT fk1_board FOREIGN KEY (RW) 
                  REFERENCES a_row (RW)

, CONSTRAINT fk2_board FOREIGN KEY (CL) 
                  REFERENCES a_col (CL)
);

COMMENT ON TABLE board IS '
[p_03] The board square named (SQUARE) is located
in the row labelled (RW) and the column labelled (CL).
' ;

COMMENT ON CONSTRAINT pk_board ON board IS '
(c3.1) Each square is located in exactly one row;
for each row it is possible that more than one square
is located in that row.

(c3.2) Each square is located in exactly one column;
for each column it is possible that more than one square
is located in that column.
' ;

COMMENT ON CONSTRAINT ak1_board ON board IS '
(c3.3) For each row and column, exactly one square
is located in both, that row and that column.
' ;

COMMENT ON CONSTRAINT fk1_board ON board IS '
(c3.4) If a square is located in a row 
then that row must exist.
' ;

COMMENT ON CONSTRAINT fk2_board ON board IS '
(c3.5) If a square is located in a column
then that column must exist.
' ;



-- [p_04] The line identified by the starting square (ANCHOR)
-- and the direction of movement (MOV_DIR) exists.
--
CREATE TABLE line (
  ANCHOR   square_name  NOT NULL
, MOV_DIR  mov_dir      NOT NULL

, CONSTRAINT  pk_line PRIMARY KEY (ANCHOR, MOV_DIR)

, CONSTRAINT fk1_line FOREIGN KEY (ANCHOR) 
                 REFERENCES board (SQUARE)
);

COMMENT ON TABLE line IS '
[p_04] The line identified by the starting square (ANCHOR)
and the direction of movement (MOV_DIR) exists.
' ;

COMMENT ON CONSTRAINT pk_line ON line IS '
(c4.1) Each line is identified by exactly one 
starting square and direction of movement combination.

(c4.2) For each starting square and direction of movement combination,
exactly one line is identified by that combination.
' ;

COMMENT ON CONSTRAINT fk1_line ON line IS '
(c4.3) If a starting square is part of a line identifier,
then that square must exist.
' ;



-- [p_05] The square named (SQUARE) is located on the line
-- identified by the starting square (ANCHOR) and 
-- the direction of movement (MOV_DIR).
--
CREATE TABLE square_line (
  SQUARE   square_name  NOT NULL
, ANCHOR   square_name  NOT NULL
, MOV_DIR  mov_dir      NOT NULL

, CONSTRAINT  pk_square_line PRIMARY KEY (SQUARE, ANCHOR, MOV_DIR)

, CONSTRAINT fk1_square_line FOREIGN KEY (SQUARE)
                        REFERENCES board (SQUARE)

, CONSTRAINT fk2_square_line FOREIGN KEY (ANCHOR, MOV_DIR) 
                         REFERENCES line (ANCHOR, MOV_DIR)
);

COMMENT ON TABLE square_line IS '
[p_05] The square named (SQUARE) is located on the line
identified by the starting square (ANCHOR) and 
the direction of movement (MOV_DIR).
' ;

COMMENT ON CONSTRAINT pk_square_line ON square_line IS '
(c5.3) For each square and line, 
that square and that line combination occurs at most once.

(c5.2) For each line that line may contain more than one square.

(c5.1) For each square, that square
may be located on more than one line.
' ; 


COMMENT ON CONSTRAINT fk1_square_line ON square_line IS '
(c5.4) If a square is located on a line
then that square must exist.
' ;

COMMENT ON CONSTRAINT fk2_square_line ON square_line IS '
(c5.5) If a square is located on a line
then that line must exist.
' ;



-- [p_06] The board square named (SQUARE) is located
-- in the row labelled (RW), row number (RW_NO); 
-- and the column labelled (CL), column number (CL_NO).
--
CREATE VIEW board1 AS
SELECT SQUARE
     , CL
     , RW
     , CL_NO
     , RW_NO
FROM board
JOIN a_col USING (CL)
JOIN a_row USING (RW)
;

COMMENT ON VIEW board1 IS '
[p_06] Board square named (SQUARE) is located in row labelled (RW),
row number (RW_NO); and column labelled (CL), column number (CL_NO).

KEY {SQUARE}
KEY {CL, RW}
KEY {CL_NO, RW_NO}.
' ;



-- [p_07] Placing queens on squares
-- (S1), (S2), (S3), (S4), (S5), (S6), (S7), and (S8)
-- is a solution to the puzzle.
CREATE VIEW solution AS
SELECT SQUARE as S1
     , SQUARE as S2
     , SQUARE as S3
     , SQUARE as S4
     , SQUARE as S5
     , SQUARE as S6
     , SQUARE as S7
     , SQUARE as S8
--
-- At this point we know only the schema of the solution,
-- but not the algorithm. The query implementing the
-- algorithm will be place here; see part 4 of the series.
--
FROM board1
WHERE FALSE
;

COMMENT ON VIEW solution IS '
At this point we know only the schema of the solution,
but not the algorithm. The query implementing the
algorithm will be placed here; see part 4 of the series.

[p_07] Placing queens on squares (S1), (S2), (S3), (S4), (S5), 
(S6), (S7), and (S8) is a solution to the puzzle.

KEY {S1, S2, S3, S4, S5, S6, S7, S8}
';