CREATE SCHEMA sch$\_$rps;

SET SEARCH$\_$PATH TO sch$\_$rps;

$/*$

To support our discussion of Database Administration, we will develop

a simple DB application that implements basic data security. It's a

trivially simple game called "Rock, Paper, Scissors" $($RPS$).$ It has many

variations; however, in its simplest form, valid input consists of the

characters $\text{'}$R$\text{'},\text{'}$P$\text{'},$ and $\text{'}$S$\text{'}.\text{'}$R$\text{'}$ beats $\text{'}$S$\text{'}$; $\text{'}$S$\text{'}$ beats $\text{'}$P$\text{'}$; and $\text{'}$P$\text{'}$ beats

$\text{'}$R$\text{'}.($This exercise is found in $*$many$*$ elementary programming textbooks.$)$

To implement this game securely$,$ we will use four tables... OK$,$ three related

tables and an errata log$.$ The three tables will represent "players", "games",

and "rounds".

A player will have a user ID that shall be required and unique. $($Hint: that's

a sure bet for a PRIMARY KEY!$)$ It also has a date of creation $($doc$)$ that

defaults to NOW$().$ We might add some fields as we develop the application;

however, these two will get us started... keep it simple for now.

A game has a required and unique integer $($let$\text{'}$s make it be a BIGINT$),$ a doc,

$($DEFAULT NOW$())$ and two different user ids from the players table $($foreign

keys$).$

The pair $($I$\text{'}$ll call mine P$1$ and P$2)$ must be unique irrespective of order. This

means that, if we have the pair: $[\text{'}$Al$\text{'},$ 'Bob'$],$ we cannot allow $[\text{'}$Bob$\text{'},\text{'}$Al$\text{'}]$

and visa versa. The best way to do this is with two constraints on the table:

$-->$ UNIQUE$($p$1,$ p$2)$

$-->$ CHECK$($p$1<$ p$2)$

I see four fields and five constraints: one PK$,$ two FK$,$ and the two above.

A round will have a unique & required BIGINT $($PK$,$ obviously$),$ a required

game ID referencing the games table, a doc, and two CHAR$(1)$ game tokens

that must be $\text{'}$R$\text{'},\text{'}$P$\text{'},$ or $\text{'}$S$\text{'}$ exclusively... $($means NOT NULL$).$

$*/$

$--$ The errata table is simple. It's just a log table.

$--$ Use this one. It will work for our needs.

DROP TABLE IF EXISTS tbl$\_$errata

$($

fld$\_$time TIMESTAMP,

fld$\_$SQLSTATE CHAR$(5),$

fld$\_$SQLERRM TEXT

$)$;

$/*$

Follow the naming conventions given below:

$--------------------------------------------------------$

Discussing naming convention and format: VERY IMPORTANT!

Consider a table representing parents. A parent has a unique and required

name, a date of creation, and an optional integer "age" $($for demo, only.

No database person would store a person's age! It would be calculated