drop table Erststimmen;
drop table Zweitstimmen;
drop table Direktkandidaten;
drop table Parteien;
drop table Wahlbezirke;
drop table Wahlkreise;
drop table Bundesländer;


create table Bundesländer(
	Name varchar(20) not null primary key,
	Einwohner integer check (Einwohner > 0));	

create table Wahlkreise(
	Nr integer not null primary key,	
	Bezeichnung varchar(20),	
	Bundesland varchar(20) references Bundesländer on delete cascade);

create table Wahlbezirke(
	Nr integer not null primary key,
	Wahlberechtigte integer check (Wahlberechtigte > 0),
	Wahllokal varchar(20),
	Wahlkreis integer references Wahlkreise on delete cascade);

create table Parteien(
	Name varchar(10) not null primary key,
	Mitglieder integer check (Mitglieder > 0));

create table Direktkandidaten(
	SozialVNr integer not null primary key,
	Name varchar(20),	
	-- Partei kann auch null sein (parteilose Kandidaten)
	Partei varchar(10) references Parteien on delete set null,
	Wahlkreis integer references Wahlkreise on delete cascade);

create table Erststimmen(
	Wahlbezirk integer not null references Wahlbezirke on delete cascade,
	Jahr integer not null,
	Kandidat integer not null references Direktkandidaten on delete cascade,
	Stimmen integer check (Stimmen > 0),
	primary key (Wahlbezirk, Jahr, Kandidat));


create table Zweitstimmen(
	Wahlbezirk integer not null references Wahlbezirke on delete cascade,
	Jahr integer not null,
	Partei varchar(10) not null references Parteien on delete cascade,
	Stimmen integer check (Stimmen > 0),
	primary key (Wahlbezirk, Jahr, Partei));


--Getestet unter DB2 und Oracle (für DB2 wurden die not null Bedingungen eingeführt!)
--SQL Server (2005 CTP): dazu muss bei Erststimmen entweder bei Wahlbezirke oder 
--bei Direktkandidaten "on delete cascade" entfernt werden. 
--Grund, siehe Hilfe:
--"The series of cascading referential actions triggered by a single DELETE or UPDATE must 
--form a tree that contains no circular references. No table can appear more than one time 
--in the list of all cascading referential actions that result from the DELETE or UPDATE. 
--Also, the tree of cascading referential actions must not have more than one path to any 
--specified table. Any branch of the tree is ended when it encounters a table for which NO 
--ACTION has been specified or is the default."
--Da sowohl Direktkandidaten wie auch Wahlbezirke von Wahlkreisen abhängen, wird dies hier 
--verboten. Lässt man "on delete cascade" bei Wahlbezirk weg, so wird das Löschen des 
--entsprechenden Eintrags in Wahlbezirke wegen der Fremdschlüsselbeziehung verboten:
--
--delete Wahlbezirke;
--Msg 547, Level 16, State 0, Line 16
--The DELETE statement conflicted with the REFERENCE constraint "FK__Erststimm__Wahlb__2F10007B". 
--The conflict occurred in database "wahlinformation", table "Erststimmen", column 'Wahlbezirk'.
--The statement has been terminated.