--- /dev/null
+
+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 )
+);