
CREATE TABLE songs (
	song SERIAL PRIMARY KEY,
	title VARCHAR NOT NULL,
	artist VARCHAR NOT NULL
);

CREATE TABLE songratings (
	song INTEGER NOT NULL REFERENCES songs,
	playmode VARCHAR NOT NULL CHECK playmode IN ('single','double'),
	difficulty VARCHAR NOT NULL CHECK difficulty IN ('beginner','standard','difficult','expert','challenge'),
	feetrating INTEGER NOT NULL CHECK (feetrating >= 0 AND feetrating <= 10)

	PRIMARY KEY (song, playmode, difficulty)
);

CREATE TABLE players (
	player SERIAL PRIMARY KEY,
	nick VARCHAR NOT NULL
);

CREATE TABLE seasons (
	season SERIAL PRIMARY KEY,
	name VARCHAR NOT NULL
);

CREATE TABLE tournament (
	tournament SERIAL PRIMARY KEY,
	season INTEGER NOT NULL REFERENCES seasons,
	name VARCHAR NOT NULL
);

CREATE TABLE rounds (
	tournament INTEGER NOT NULL REFERENCES tournaments,
	level INTEGER NOT NULL,
	parallel INTEGER NOT NULL

	PRIMARY KEY (tournament, level, parallel)
);

CREATE TABLE roundparticipation (
	tournament INTEGER NOT NULL,
	level INTEGER NOT NULL,
	parallel INTEGER NOT NULL,
	player INTEGER NOT NULL REFERENCES players,

	FOREIGN KEY ( tournament, level, parallel ) REFERENCES rounds ( tournament, level, parallel ),
	PRIMARY KEY ( tournament, level, parallel, player )
);
