EXEC SQL: DROP TABLE Mp3;
EXEC SQL: DROP TABLE UserCat;
EXEC SQL: DROP TABLE Category;
EXEC SQL: DROP TABLE Mp3user;

EXEC SQL: CREATE TABLE Mp3user (
id              VARCHAR(20) NOT NULL,
passwd          VARCHAR(30) NOT NULL,
startdate       DATE NOT NULL,
enddate         DATE,
PRIMARY KEY (id, startdate));


EXEC SQL: CREATE TABLE Category (
id              INTEGER NOT NULL,
name            VARCHAR(32) NOT NULL,
PRIMARY KEY (id));

EXEC SQL: CREATE TABLE UserCat (
userid          VARCHAR(20) NOT NULL,
userdate        DATE NOT NULL,
catid           INTEGER NOT NULL,
PRIMARY KEY (userid, userdate, catid),
FOREIGN KEY (userid, userdate) REFERENCES Mp3user(id, startdate),
FOREIGN KEY (catid)  REFERENCES Category(id));


EXEC SQL: CREATE TABLE Mp3 (
artist          VARCHAR(64) NOT NULL,
title           VARCHAR(128) NOT NULL,
tracknr         SMALLINT,
album           VARCHAR(64),
catid           INTEGER NOT NULL,
PRIMARY KEY (artist, title),
FOREIGN KEY (catid) REFERENCES Category(id));
