CREATE DATABASE Fakultet

GO

USE Fakultet

GO

CREATE TABLE [dbo].[ispit] (
	[brojIndeksa] [nvarchar] (10) NOT NULL ,
	[idPredmeta] [int] NOT NULL ,
	[idNastavnika] [int] NOT NULL ,
	[datum] [datetime] NOT NULL ,
	[ocena] [tinyint] NOT NULL 
)

GO

CREATE TABLE [dbo].[katedra] (
	[idKatedre] [int] NOT NULL ,
	[naziv] [nvarchar] (30) NOT NULL 
) 

GO

CREATE TABLE [dbo].[mesto] (
	[ptt] [bigint] NOT NULL ,
	[naziv] [nvarchar] (30) NOT NULL 
)

GO

CREATE TABLE [dbo].[nastavnik] (
	[idNastavnika] [int] NOT NULL ,
	[ime] [nvarchar] (30) NOT NULL ,
	[prezime] [nvarchar] (30) NOT NULL ,
	[zvanje] [nvarchar] (30) NOT NULL ,
	[plata] [money] NULL ,
	[putniTrosak] [real] NULL ,
	[ptt] [bigint] NULL 
)

GO

CREATE TABLE [dbo].[predaje] (
	[idNastavnika] [int] NOT NULL ,
	[idPredmeta] [int] NOT NULL ,
	[brojCasova] [tinyint] NOT NULL 
)

GO

CREATE TABLE [dbo].[predmet] (
	[idPredmeta] [int] NOT NULL ,
	[nazivPredmeta] [nvarchar] (50) NOT NULL ,
	[idKatedre] [int] NOT NULL 
) 

GO

CREATE TABLE [dbo].[slusa] (
	[brojIndeksa] [nvarchar] (10) NOT NULL ,
	[idPredmeta] [int] NOT NULL 
) 

GO

CREATE TABLE [dbo].[student] (
	[brojIndeksa] [nvarchar] (10) NOT NULL ,
	[ime] [nvarchar] (30) NOT NULL ,
	[prezime] [nvarchar] (30) NOT NULL ,
	[adresa] [nvarchar] (30) NOT NULL ,
	[datumRodjenja] [datetime] NOT NULL ,
	[godStudija] [tinyint] NOT NULL ,
	[ptt] [bigint] NOT NULL 
)

GO

ALTER TABLE [dbo].[ispit] ADD 
	CONSTRAINT [PK_ispit] PRIMARY KEY  CLUSTERED 
	(
		[brojIndeksa],
		[idPredmeta]
	) 
 
GO

ALTER TABLE [dbo].[katedra] ADD 
	CONSTRAINT [PK_katedra] PRIMARY KEY  CLUSTERED 
	(
		[idKatedre]
	)  

GO

ALTER TABLE [dbo].[mesto] ADD 
	CONSTRAINT [PK_mesto] PRIMARY KEY  CLUSTERED 
	(
		[ptt]
	)  

GO

ALTER TABLE [dbo].[nastavnik] ADD 
	CONSTRAINT [PK_nastavnik] PRIMARY KEY  CLUSTERED 
	(
		[idNastavnika]
	)  

GO

ALTER TABLE [dbo].[predaje] ADD 
	CONSTRAINT [PK_predaje] PRIMARY KEY  CLUSTERED 
	(
		[idNastavnika],
		[idPredmeta]
	) 

GO

ALTER TABLE [dbo].[predmet] ADD 
	CONSTRAINT [PK_predmet] PRIMARY KEY  CLUSTERED 
	(
		[idPredmeta]
	) 

GO

ALTER TABLE [dbo].[slusa] ADD 
	CONSTRAINT [PK_slusa] PRIMARY KEY  CLUSTERED 
	(
		[brojIndeksa],
		[idPredmeta]
	) 

GO

ALTER TABLE [dbo].[student] ADD 
	CONSTRAINT [PK_student] PRIMARY KEY  CLUSTERED 
	(
		[brojIndeksa]
	)

GO


ALTER TABLE [dbo].[predmet] ADD 
	CONSTRAINT [AK_predmet] UNIQUE ([nazivPredmeta])

GO

ALTER TABLE [dbo].[predaje] ADD 
	CONSTRAINT [CK_predaje] CHECK ([brojCasova]>0)

GO

ALTER TABLE [dbo].[ispit] ADD 
	CONSTRAINT [DF_ispit] DEFAULT '6' FOR [ocena]

GO

ALTER TABLE [dbo].[ispit] ADD 
	CONSTRAINT [FK_ispit_predaje] FOREIGN KEY 
	(
		[idNastavnika],
		[idPredmeta]
	) REFERENCES [dbo].[predaje] (
		[idNastavnika],
		[idPredmeta]
	)
	  ON UPDATE NO ACTION
	  ON DELETE NO ACTION,
	CONSTRAINT [FK_ispit_slusa] FOREIGN KEY 
	(
		[brojIndeksa],
		[idPredmeta]
	) REFERENCES [dbo].[slusa] (
		[brojIndeksa],
		[idPredmeta]
	)
	  ON UPDATE NO ACTION
	  ON DELETE NO ACTION

GO

ALTER TABLE [dbo].[nastavnik] ADD 
	CONSTRAINT [FK_nastavnik_mesto] FOREIGN KEY 
	(
		[ptt]
	) REFERENCES [dbo].[mesto] (
		[ptt]
	) ON UPDATE CASCADE 
	  ON DELETE NO ACTION
GO

ALTER TABLE [dbo].[predaje] ADD 
	CONSTRAINT [FK_predaje_nastavnik] FOREIGN KEY 
	(
		[idNastavnika]
	) REFERENCES [dbo].[nastavnik] (
		[idNastavnika]
	) ON UPDATE NO ACTION
	  ON DELETE NO ACTION ,
	CONSTRAINT [FK_predaje_predmet] FOREIGN KEY 
	(
		[idPredmeta]
	) REFERENCES [dbo].[predmet] (
		[idPredmeta]
	) ON UPDATE NO ACTION
	  ON DELETE NO ACTION
GO

ALTER TABLE [dbo].[predmet] ADD 
	CONSTRAINT [FK_predmet_katedra] FOREIGN KEY 
	(
		[idKatedre]
	) REFERENCES [dbo].[katedra] (
		[idKatedre]
	) ON UPDATE CASCADE 
	  ON DELETE NO ACTION
	  
GO

ALTER TABLE [dbo].[slusa] ADD 
	CONSTRAINT [FK_slusa_predmet] FOREIGN KEY 
	(
		[idPredmeta]
	) REFERENCES [dbo].[predmet] (
		[idPredmeta]
	) ON UPDATE NO ACTION
	  ON DELETE NO ACTION ,
	CONSTRAINT [FK_slusa_student] FOREIGN KEY 
	(
		[brojIndeksa]
	) REFERENCES [dbo].[student] (
		[brojIndeksa]
	) ON UPDATE NO ACTION
	  ON DELETE NO ACTION
GO

ALTER TABLE [dbo].[student] ADD 
	CONSTRAINT [FK_student_mesto] FOREIGN KEY 
	(
		[ptt]
	) REFERENCES [dbo].[mesto] (
		[ptt]
	) ON UPDATE CASCADE 
	  ON DELETE NO ACTION
GO