/*
created		4. 3. 2006
modified		4. 3. 2006
project		
model			
company		
author		
version		
database		postgresql 8 
*/


/* drop referential integrity triggers */





/* drop indexes */



/* drop tables */
drop table "person" restrict;
drop table "attended_courses" restrict;
drop table "teacher" restrict;
drop table "branch" restrict;
drop table "course" restrict;
drop table "student" restrict;



/* create tables */


create table "student"
(
	"student_id" bigint not null,
	"bornnumber" varchar(11) not null,
	"branch" integer not null,
	"specialization" integer not null,
	"year" smallint,
	"studygroup" varchar(5) not null,
	"started" date,
	"finished" date,
	"status" char(1),
 primary key ("student_id")
) with oids;

create table "course"
(
	"coursecode" varchar(4) not null,
	"gestor" varchar(5) not null,
	"name" varchar(50),
	"credits" smallint,
 primary key ("coursecode")
) with oids;

create table "branch"
(
	"branch" integer not null,
	"specialization" integer not null,
	"branchdescription" varchar(200) not null,
	"specdescription" varchar(200),
 primary key ("branch","specialization")
) with oids;

create table "teacher"
(
	"personal_id" varchar(5) not null,
	"name" varchar(50),
	"familyname" varchar(50) not null,
	"department" varchar(50),
 primary key ("personal_id")
) with oids;

create table "attended_courses"
(
	"student_id" bigint not null,
	"coursecode" varchar(4) not null,
	"schoolyear" integer not null,
	"teacher" varchar(5) not null,
	"result" varchar(20),
	"examdate" date,
	"try" smallint,
	"assignment" date,
	"credits" integer,
 primary key ("student_id","coursecode","schoolyear")
) with oids;

create table "person"
(
	"bornnumber" varchar(11) not null,
	"name" varchar(50) not null,
	"familyname" varchar(50) not null,
	"street" varchar(50),
	"town" varchar(50),
	"zipcode" varchar(6),
 primary key ("bornnumber")
) with oids;


/* create indexes */



/* create foreign keys */

alter table "attended_courses" add  foreign key ("student_id") references "student" ("student_id") on update restrict on delete restrict;

alter table "attended_courses" add  foreign key ("coursecode") references "course" ("coursecode") on update restrict on delete restrict;

alter table "student" add  foreign key ("branch","specialization") references "branch" ("branch","specialization") on update restrict on delete restrict;

alter table "course" add  foreign key ("gestor") references "teacher" ("personal_id") on update restrict on delete restrict;

alter table "attended_courses" add  foreign key ("teacher") references "teacher" ("personal_id") on update restrict on delete restrict;

alter table "student" add  foreign key ("bornnumber") references "person" ("bornnumber") on update restrict on delete restrict;



/* create referential integrity triggers */







