-- On the fly Code aus der Übung vom 20.05.2010

DROP TABLE Intoniert;
DROP TABLE Enthaelt;
DROP TABLE Song;
DROP TABLE Komponist;
DROP TABLE Album;
DROP TABLE Interpret;

CREATE TABLE Song(
  sid INTEGER PRIMARY KEY NOT NULL
, stitel VARCHAR NOT NULL
, laenge INTEGER
, skomponist INTEGER NOT NULL
);

CREATE TABLE Komponist(
  kid INTEGER PRIMARY KEY NOT NULL
, kvorname VARCHAR
, knachname VARCHAR NOT NULL
);

ALTER TABLE Song
  ADD CONSTRAINT s_fk FOREIGN KEY(skomponist)
                        REFERENCES Komponist(kid);

CREATE TABLE Album(
  aid INTEGER PRIMARY KEY NOT NULL
, atititel VARCHAR NOT NULL
);

CREATE TABLE Interpret(
  iid INTEGER PRIMARY KEY NOT NULL
, ikuenstlername VARCHAR NOT NULL
);


CREATE TABLE Intoniert(
  sid INTEGER NOT NULL
       REFERENCES Song
, iid INTEGER NOT NULL
       REFERENCES Interpret
, PRIMARY KEY (sid, iid)
);
       
CREATE TABLE Enthaelt(
  sid INTEGER NOT NULL
       REFERENCES Song
, aid INTEGER NOT NULL
       REFERENCES Album 
, PRIMARY KEY (sid, aid)
);