/*
Created		4. 3. 2007
Modified		4. 3. 2007
Project		
Model			
Company		
Author		
Version		
Database		MS SQL 2005 
*/


Drop table [person] 
go
Drop table [attended_courses] 
go
Drop table [teacher] 
go
Drop table [branch] 
go
Drop table [course] 
go
Drop table [student] 
go


Create table [student]
(
	[student_id] Bigint NOT NULL,
	[bornnumber] Varchar(10) NOT NULL,
	[branch] Integer NOT NULL,
	[specialization] Integer NOT NULL,
	[year] Smallint NULL,
	[studygroup] Varchar(5) NOT NULL,
	[started] Datetime NULL,
	[finished] Datetime NULL,
	[status] Char(1) NULL,
Primary Key ([student_id])
) 
go

Create table [course]
(
	[coursecode] Varchar(4) NOT NULL,
	[gestor] Varchar(5) NOT NULL,
	[name] Varchar(50) NULL,
	[credits] Smallint NULL,
Primary Key ([coursecode])
) 
go

Create table [branch]
(
	[branch] Integer NOT NULL,
	[specialization] Integer NOT NULL,
	[branchdescription] Varchar(200) NOT NULL,
	[specdescription] Varchar(200) NULL,
Primary Key ([branch],[specialization])
) 
go

Create table [teacher]
(
	[personal_id] Varchar(5) NOT NULL,
	[name] Varchar(50) NULL,
	[familyname] Varchar(50) NOT NULL,
	[department] Varchar(50) NULL,
Primary Key ([personal_id])
) 
go

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) NULL,
	[examdate] Datetime NULL,
	[try] Smallint NULL,
	[assignment] Datetime NULL,
	[credits] Integer NULL,
Primary Key ([student_id],[coursecode],[schoolyear])
) 
go

Create table [person]
(
	[bornnumber] Varchar(10) NOT NULL,
	[name] Varchar(50) NOT NULL,
	[familyname] Varchar(50) NOT NULL,
	[street] Varchar(50) NULL,
	[town] Varchar(50) NULL,
	[zipcode] Varchar(6) NULL,
Primary Key ([bornnumber])
) 
go


Alter table [attended_courses] add  foreign key([student_id]) references [student] ([student_id])  on update no action on delete no action 
go
Alter table [attended_courses] add  foreign key([coursecode]) references [course] ([coursecode])  on update no action on delete no action 
go
Alter table [student] add  foreign key([branch],[specialization]) references [branch] ([branch],[specialization])  on update no action on delete no action 
go
Alter table [course] add  foreign key([gestor]) references [teacher] ([personal_id])  on update no action on delete no action 
go
Alter table [attended_courses] add  foreign key([teacher]) references [teacher] ([personal_id])  on update no action on delete no action 
go
Alter table [student] add  foreign key([bornnumber]) references [person] ([bornnumber])  on update no action on delete no action 
go


Set quoted_identifier on
go


Set quoted_identifier off
go


