22RRm8VW

1\c pg;
2
3DROP TABLE IF EXISTS ships CASCADE;
4DROP TABLE IF EXISTS sailors CASCADE;
5DROP TABLE IF EXISTS assignments CASCADE;
6DROP TABLE IF EXISTS ranks CASCADE;
7DROP TABLE IF EXISTS fleets CASCADE;
8
9CREATE TABLE fleets (
10  id SERIAL PRIMARY KEY NOT NULL,
11  name VARCHAR(50) NOT NULL
12);
13
14CREATE TABLE ranks (
15  id SERIAL PRIMARY KEY NOT NULL,
16  name VARCHAR(50) NOT NULL
17);
18
19CREATE TABLE ships (
20  id SERIAL PRIMARY KEY NOT NULL,
21  name VARCHAR(50) NOT NULL,
22  date_built DATE NOT NULL,
23  fleet_id INTEGER NOT NULL REFERENCES fleets(id) ON DELETE CASCADE
24);
25
26CREATE TABLE sailors (
27  id SERIAL PRIMARY KEY NOT NULL,
28  name VARCHAR(50) NOT NULL,
29  dob DATE NOT NULL 
30);
31
32CREATE TABLE assignments (
33  id SERIAL PRIMARY KEY NOT NULL,
34  name VARCHAR(50) NOT NULL,
35  start_date DATE NOT NULL,
36  stop_date DATE NOT NULL,
37  rank_id INTEGER NOT NULL REFERENCES ranks(id) ON DELETE CASCADE,
38  sailor_id INTEGER NOT NULL REFERENCES sailors(id) ON DELETE CASCADE,
39  ship_id INTEGER NOT NULL REFERENCES ships(id) ON DELETE CASCADE
40);