CREATE TABLE "Clubs" (
	"short_club_name"	TEXT,
	"club_code"	TEXT NOT NULL UNIQUE,
	"long_club_name"	TEXT NOT NULL UNIQUE,
	"club_rep_name"	TEXT,
	"email_address"	TEXT,
	PRIMARY KEY("short_club_name")
);

CREATE TABLE "Events" (
	"event_number"	INTEGER,
	"distance"	INTEGER NOT NULL,
	"stroke"	TEXT NOT NULL,
	"sex"	TEXT NOT NULL,
	"no_of_swimmers"	INTEGER NOT NULL,
	"event_type"	TEXT NOT NULL,
	"no_of_lanes"	INTEGER NOT NULL,
	"seeding_method"	TEXT NOT NULL,
	"heat_events_number"	INTEGER,
	"entry_fee"	REAL NOT NULL,
	"time_interval"	INTEGER NOT NULL,
	"session_number"	INTEGER NOT NULL,
	PRIMARY KEY("event_number")
	FOREIGN KEY("heat_events_number") REFERENCES Events("event_number")
	FOREIGN KEY("session_number") REFERENCES Sessions("session_number")
);

CREATE TABLE "Meet" (
	"licence_number"	TEXT,
	"meet_name"	TEXT NOT NULL,
	"meet_type"	TEXT NOT NULL,
	"start_date"	TEXT NOT NULL,
	"end_date"	TEXT NOT NULL,
	"location"	TEXT NOT NULL,
	"course"	INTEGER NOT NULL,
	PRIMARY KEY("licence_number")
);

CREATE TABLE "Qualifying_Times" (
	"event_number"	INTEGER,
	"min_age"	INTEGER,
	"max_age"	INTEGER,
	"lower_time"	INTEGER,
	"upper_time"	INTEGER,
	PRIMARY KEY("event_number","min_age","max_age")
	FOREIGN KEY("event_number") REFERENCES Events("event_number")
);

CREATE TABLE "Sessions" (
	"session_number"	INTEGER,
	"date"	TEXT NOT NULL,
	"start_time"	INTEGER,
	PRIMARY KEY("session_number")
);

CREATE TABLE "Swimmers" (
	"asa_number"	INTEGER,
	"forename"	TEXT NOT NULL,
	"surname"	TEXT NOT NULL,
	"nickname"	TEXT,
	"dob"	TEXT,
	"age"	INTEGER NOT NULL,
	"sex"	TEXT NOT NULL,
	"short_club_name"	TEXT NOT NULL,
	"disability_status"	TEXT,
	PRIMARY KEY("asa_number")
	FOREIGN KEY("short_club_name") REFERENCES Clubs("short_club_name")
);