KCNYReF8

1--CREATE DATABASE bradgardendb
2
3CREATE TABLE IF NOT EXISTS member
4(
5  member_id    SMALLSERIAL PRIMARY KEY UNIQUE NOT NULL,
6  first_name   VARCHAR(30) NOT NULL,
7  last_name    VARCHAR(30) NOT NULL
8);
9
10CREATE TABLE IF NOT EXISTS game
11(
12  game_id     SMALLSERIAL PRIMARY KEY UNIQUE NOT NULL,
13  game_name   VARCHAR(50) NOT NULL,
14  max_players INTEGER NOT NULL,
15  traitor     BOOLEAN NOT NULL,
16  co_op       BOOLEAN NOT NULL
17);
18
19CREATE TABLE IF NOT EXISTS game_session
20(
21  session_id     SMALLSERIAL UNIQUE NOT NULL,
22  game           INTEGER REFERENCES game (game_id) NOT NULL,
23  session_date   VARCHAR(25) NOT NULL,
24  PRIMARY KEY (session_id)
25);
26
27CREATE TABLE IF NOT EXISTS traitor
28(
29  game_session INTEGER REFERENCES game_session (session_id) ON DELETE CASCADE NOT NULL,
30  member    INTEGER REFERENCES member (member_id) NOT NULL,
31  PRIMARY KEY (game_session,member)
32);
33
34CREATE TABLE IF NOT EXISTS loser
35(
36  game_session  INTEGER REFERENCES game_session (session_id) ON DELETE CASCADE NOT NULL,
37  member    INTEGER REFERENCES member (member_id) NOT NULL,
38  PRIMARY KEY (game_session,member)
39);
40CREATE TABLE IF NOT EXISTS winner
41(
42  game_session  INTEGER REFERENCES game_session (session_id) ON DELETE CASCADE NOT NULL,
43  member    INTEGER REFERENCES member (member_id) NOT NULL,
44  PRIMARY KEY (game_session,member)
45);
46
47CREATE TABLE IF NOT EXISTS api_user
48(
49    name VARCHAR(50) NOT NULL,
50    secret VARCHAR(100) NOT NULL,
51    PRIMARY KEY (name)
52);