/****** Object:  Database istec    Script Date: 08-04-2005 20:14:01 ******/
IF EXISTS (SELECT name FROM master.dbo.sysdatabases WHERE name = N'istec')
	DROP DATABASE [istec]
GO

CREATE DATABASE [istec]  ON (NAME = N'istec_Data', FILENAME = N'C:\Program Files\Microsoft SQL Server\MSSQL\Data\istec_Data.MDF' , SIZE = 2, FILEGROWTH = 10%) LOG ON (NAME = N'istec_Log', FILENAME = N'C:\Program Files\Microsoft SQL Server\MSSQL\Data\istec_Log.LDF' , SIZE = 1, FILEGROWTH = 10%)
 COLLATE Latin1_General_CI_AS
GO

exec sp_dboption N'istec', N'autoclose', N'true'
GO

exec sp_dboption N'istec', N'bulkcopy', N'false'
GO

exec sp_dboption N'istec', N'trunc. log', N'true'
GO

exec sp_dboption N'istec', N'torn page detection', N'true'
GO

exec sp_dboption N'istec', N'read only', N'false'
GO

exec sp_dboption N'istec', N'dbo use', N'false'
GO

exec sp_dboption N'istec', N'single', N'false'
GO

exec sp_dboption N'istec', N'autoshrink', N'true'
GO

exec sp_dboption N'istec', N'ANSI null default', N'false'
GO

exec sp_dboption N'istec', N'recursive triggers', N'false'
GO

exec sp_dboption N'istec', N'ANSI nulls', N'false'
GO

exec sp_dboption N'istec', N'concat null yields null', N'false'
GO

exec sp_dboption N'istec', N'cursor close on commit', N'false'
GO

exec sp_dboption N'istec', N'default to local cursor', N'false'
GO

exec sp_dboption N'istec', N'quoted identifier', N'false'
GO

exec sp_dboption N'istec', N'ANSI warnings', N'false'
GO

exec sp_dboption N'istec', N'auto create statistics', N'true'
GO

exec sp_dboption N'istec', N'auto update statistics', N'true'
GO

if( ( (@@microsoftversion / power(2, 24) = 8) and (@@microsoftversion & 0xffff >= 724) ) or ( (@@microsoftversion / power(2, 24) = 7) and (@@microsoftversion & 0xffff >= 1082) ) )
	exec sp_dboption N'istec', N'db chaining', N'false'
GO

use [istec]
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[FK_Exames_Alunos1]') and OBJECTPROPERTY(id, N'IsForeignKey') = 1)
ALTER TABLE [dbo].[Exames] DROP CONSTRAINT FK_Exames_Alunos1
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[FK_Docentes_Disciplinas_Disciplinas]') and OBJECTPROPERTY(id, N'IsForeignKey') = 1)
ALTER TABLE [dbo].[Docentes_Disciplinas] DROP CONSTRAINT FK_Docentes_Disciplinas_Disciplinas
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[FK_Exames_Disciplinas1]') and OBJECTPROPERTY(id, N'IsForeignKey') = 1)
ALTER TABLE [dbo].[Exames] DROP CONSTRAINT FK_Exames_Disciplinas1
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[FK_Docentes_Disciplinas_Docentes]') and OBJECTPROPERTY(id, N'IsForeignKey') = 1)
ALTER TABLE [dbo].[Docentes_Disciplinas] DROP CONSTRAINT FK_Docentes_Disciplinas_Docentes
GO

/****** Object:  Table [dbo].[Docentes_Disciplinas]    Script Date: 08-04-2005 20:14:03 ******/
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[Docentes_Disciplinas]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[Docentes_Disciplinas]
GO

/****** Object:  Table [dbo].[Exames]    Script Date: 08-04-2005 20:14:03 ******/
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[Exames]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[Exames]
GO

/****** Object:  Table [dbo].[Alunos]    Script Date: 08-04-2005 20:14:03 ******/
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[Alunos]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[Alunos]
GO

/****** Object:  Table [dbo].[Cursos]    Script Date: 08-04-2005 20:14:03 ******/
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[Cursos]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[Cursos]
GO

/****** Object:  Table [dbo].[Disciplinas]    Script Date: 08-04-2005 20:14:03 ******/
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[Disciplinas]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[Disciplinas]
GO

/****** Object:  Table [dbo].[Docentes]    Script Date: 08-04-2005 20:14:03 ******/
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[Docentes]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[Docentes]
GO

/****** Object:  Table [dbo].[Funcionarios]    Script Date: 08-04-2005 20:14:03 ******/
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[Funcionarios]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[Funcionarios]
GO

/****** Object:  Table [dbo].[Alunos]    Script Date: 08-04-2005 20:14:03 ******/
CREATE TABLE [dbo].[Alunos] (
	[idAluno] [int] IDENTITY (1, 1) NOT NULL ,
	[nome] [varchar] (50) COLLATE Latin1_General_CI_AS NULL ,
	[dataNasc] [datetime] NULL ,
	[sexo] [char] (1) COLLATE Latin1_General_CI_AS NULL ,
	[naturalidade] [varchar] (50) COLLATE Latin1_General_CI_AS NULL ,
	[peso] [int] NULL ,
	[clube] [char] (20) COLLATE Latin1_General_CI_AS NOT NULL 
) ON [PRIMARY]
GO

/****** Object:  Table [dbo].[Cursos]    Script Date: 08-04-2005 20:14:04 ******/
CREATE TABLE [dbo].[Cursos] (
	[idCurso] [int] IDENTITY (1, 1) NOT NULL ,
	[nome] [varchar] (100) COLLATE Latin1_General_CI_AS NULL ,
	[anos] [tinyint] NULL 
) ON [PRIMARY]
GO

/****** Object:  Table [dbo].[Disciplinas]    Script Date: 08-04-2005 20:14:04 ******/
CREATE TABLE [dbo].[Disciplinas] (
	[idDisciplina] [int] IDENTITY (1, 1) NOT NULL ,
	[Nome] [varchar] (50) COLLATE Latin1_General_CI_AS NULL ,
	[carga] [int] NULL ,
	[descricao] [text] COLLATE Latin1_General_CI_AS NULL 
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
GO

/****** Object:  Table [dbo].[Docentes]    Script Date: 08-04-2005 20:14:04 ******/
CREATE TABLE [dbo].[Docentes] (
	[idDocente] [int] IDENTITY (1, 1) NOT NULL ,
	[nome] [varchar] (50) COLLATE Latin1_General_CI_AS NULL ,
	[grau] [varchar] (50) COLLATE Latin1_General_CI_AS NULL ,
	[dataNasc] [datetime] NULL 
) ON [PRIMARY]
GO

/****** Object:  Table [dbo].[Funcionarios]    Script Date: 08-04-2005 20:14:04 ******/
CREATE TABLE [dbo].[Funcionarios] (
	[idFunc] [int] IDENTITY (1, 1) NOT NULL ,
	[nome] [char] (10) COLLATE Latin1_General_CI_AS NULL ,
	[cargo] [varchar] (50) COLLATE Latin1_General_CI_AS NULL ,
	[salario] [money] NULL 
) ON [PRIMARY]
GO

/****** Object:  Table [dbo].[Docentes_Disciplinas]    Script Date: 08-04-2005 20:14:04 ******/
CREATE TABLE [dbo].[Docentes_Disciplinas] (
	[idDocente] [int] NOT NULL ,
	[idDisciplina] [int] NOT NULL ,
	[anoLectivo] [int] NOT NULL 
) ON [PRIMARY]
GO

/****** Object:  Table [dbo].[Exames]    Script Date: 08-04-2005 20:14:04 ******/
CREATE TABLE [dbo].[Exames] (
	[idAluno] [int] NOT NULL ,
	[idDisciplina] [int] NOT NULL ,
	[data] [datetime] NOT NULL ,
	[nota] [int] NULL 
) ON [PRIMARY]
GO

ALTER TABLE [dbo].[Alunos] ADD 
	CONSTRAINT [DF__alunos__clube__2A4B4B5E] DEFAULT ('Benfica') FOR [clube],
	CONSTRAINT [PK_Alunos] PRIMARY KEY  CLUSTERED 
	(
		[idAluno]
	)  ON [PRIMARY] 
GO

ALTER TABLE [dbo].[Cursos] ADD 
	CONSTRAINT [PK_Cursos] PRIMARY KEY  CLUSTERED 
	(
		[idCurso]
	)  ON [PRIMARY] 
GO

ALTER TABLE [dbo].[Disciplinas] ADD 
	CONSTRAINT [PK_Disciplinas] PRIMARY KEY  CLUSTERED 
	(
		[idDisciplina]
	)  ON [PRIMARY] 
GO

ALTER TABLE [dbo].[Docentes] ADD 
	 PRIMARY KEY  CLUSTERED 
	(
		[idDocente]
	)  ON [PRIMARY] 
GO

ALTER TABLE [dbo].[Funcionarios] ADD 
	CONSTRAINT [PK_Funcionarios] PRIMARY KEY  CLUSTERED 
	(
		[idFunc]
	)  ON [PRIMARY] 
GO

ALTER TABLE [dbo].[Docentes_Disciplinas] ADD 
	CONSTRAINT [PK_Docentes_Disciplinas] PRIMARY KEY  CLUSTERED 
	(
		[idDocente],
		[idDisciplina],
		[anoLectivo]
	)  ON [PRIMARY] 
GO

ALTER TABLE [dbo].[Exames] ADD 
	CONSTRAINT [PK_Exames] PRIMARY KEY  CLUSTERED 
	(
		[idAluno],
		[idDisciplina],
		[data]
	)  ON [PRIMARY] 
GO

ALTER TABLE [dbo].[Docentes_Disciplinas] ADD 
	CONSTRAINT [FK_Docentes_Disciplinas_Disciplinas] FOREIGN KEY 
	(
		[idDisciplina]
	) REFERENCES [dbo].[Disciplinas] (
		[idDisciplina]
	) ON DELETE CASCADE  ON UPDATE CASCADE ,
	CONSTRAINT [FK_Docentes_Disciplinas_Docentes] FOREIGN KEY 
	(
		[idDocente]
	) REFERENCES [dbo].[Docentes] (
		[idDocente]
	) ON DELETE CASCADE  ON UPDATE CASCADE 
GO

ALTER TABLE [dbo].[Exames] ADD 
	CONSTRAINT [FK_Exames_Alunos1] FOREIGN KEY 
	(
		[idAluno]
	) REFERENCES [dbo].[Alunos] (
		[idAluno]
	) ON DELETE CASCADE  ON UPDATE CASCADE ,
	CONSTRAINT [FK_Exames_Disciplinas1] FOREIGN KEY 
	(
		[idDisciplina]
	) REFERENCES [dbo].[Disciplinas] (
		[idDisciplina]
	) ON DELETE CASCADE  ON UPDATE CASCADE 
GO