--name: Rick van der Zwet
--sid:  0433373
--file: generate.sql
--vak/opdracht: Databases/ opdracht 1

CREATE TABLE Coach(
  ID integer,
  Age integer,
  Name char(20),
  PRIMARY KEY( ID )
);


CREATE TABLE Sportsman(
   ID integer,
   Age integer,
   Name char(20),
   Country char(20),
   Coach_id integer NOT NULL,
   PRIMARY KEY( ID ),
   FOREIGN KEY( Coach_id ) REFERENCES Coach( ID )
);


CREATE TABLE Sponsor(
  ID integer,
  Name char(20),
  SupportSportsman_id integer,
  Budget integer,
  PRIMARY KEY( ID ),
  FOREIGN KEY( SupportSportsman_id ) REFERENCES Sportsman( ID )
);


CREATE TABLE Sports_Branch(
  Name char(20),
  PRIMARY KEY( Name )
);


CREATE TABLE plays(
  Sportsman_id integer,
  Sports_Branch char(20),
  PRIMARY KEY( Sportsman_id, Sports_Branch ),
  FOREIGN KEY( Sportsman_id ) REFERENCES Sportsman( ID ) ON DELETE CASCADE,
  FOREIGN KEY( Sports_Branch ) REFERENCES Sports_Branch( Name ) ON DELETE CASCADE
);


CREATE TABLE Sports_event(
  ID integer,
  Datum date,
  Name char(20),
  Location char(20),
  PRIMARY KEY( ID )
);


CREATE TABLE participate(
  Sportsman_id integer,
  SportsEvent_id integer,
  PRIMARY KEY( Sportsman_id, SportsEvent_id ),
  FOREIGN KEY( Sportsman_id ) REFERENCES Sportsman( ID ) ON DELETE CASCADE,
  FOREIGN KEY( SportsEvent_id ) REFERENCES Sports_event ( ID ) ON DELETE CASCADE
);

CREATE TABLE won( 
  Sportsman_id integer,
  Sports_event integer,
  Sports_Branch char(20),
  Metal char(20),
  PRIMARY KEY( Sportsman_id, Sports_event, Sports_Branch ),
  FOREIGN KEY( Sportsman_id ) REFERENCES Sportsman( ID ) ON DELETE CASCADE,
  FOREIGN KEY( Sports_event ) REFERENCES Sports_event( ID ) ON DELETE CASCADE,
  FOREIGN KEY( Sports_Branch ) REFERENCES Sports_Branch( Name ) ON DELETE CASCADE
);
  

--
--budget VIEW is een beetje in elkaar gehacked
--niet geheel duidelijk wat bedoeld werd.
--de view lijkt me wat overbodig hier, 
--dit kan beter extern geregeld worden. 
--

CREATE TABLE Sponsor_login(
  Name char(20), 
  Passwd char(20),
  Sponsor_id integer,
  PRIMARY KEY ( Name ),
  FOREIGN KEY( Sponsor_id ) REFERENCES Sponsor( ID ) ON DELETE CASCADE
);

CREATE VIEW budget( Name, Support_Sportsman_id, Budget ) 
  AS SELECT S.Name, S.SupportSportsman_id, S.Budget
  FROM Sponsor S, Sponsor_login L 
  WHERE L.sponsor_id = S.ID AND L.Passwd = 'externe_variable'
;

DROP VIEW budget;
DROP TABLE sponsor_login;

--
--END of HACK
--


DROP TABLE won;
DROP TABLE participate;
DROP TABLE Sports_event;
DROP TABLE plays;
DROP TABLE Sports_Branch;
DROP TABLE Sponsor;
DROP TABLE Sportsman;
DROP TABLE Coach;