ÿþSET ANSI_NULLS ON
SET DATEFORMAT ymd
GO
SET QUOTED_IDENTIFIER ON
GO

USE [master];
GO

IF EXISTS (SELECT * FROM sys.databases WHERE name = 'Ecole')
	DROP DATABASE Ecole;
GO
CREATE DATABASE Ecole;
GO

/****** Object:  ForeignKey [FK_BureauAffecte_Personne]    Script Date: 01/09/2012 16:58:41 ******/
IF  EXISTS (SELECT * FROM sys.foreign_keys WHERE object_id = OBJECT_ID(N'[dbo].[FK_BureauAffecte_Personne]') AND parent_object_id = OBJECT_ID(N'[dbo].[BureauAffecte]'))
ALTER TABLE [dbo].[BureauAffecte] DROP CONSTRAINT [FK_BureauAffecte_Personne]
GO
/****** Object:  ForeignKey [FK_Cours_Departement]    Script Date: 01/09/2012 16:58:41 ******/
IF  EXISTS (SELECT * FROM sys.foreign_keys WHERE object_id = OBJECT_ID(N'[dbo].[FK_Cours_Departement]') AND parent_object_id = OBJECT_ID(N'[dbo].[Cours]'))
ALTER TABLE [dbo].[Cours] DROP CONSTRAINT [FK_Cours_Departement]
GO
/****** Object:  ForeignKey [FK_CoursEnClasse_Cours]    Script Date: 01/09/2012 16:58:41 ******/
IF  EXISTS (SELECT * FROM sys.foreign_keys WHERE object_id = OBJECT_ID(N'[dbo].[FK_CoursEnClasse_Cours]') AND parent_object_id = OBJECT_ID(N'[dbo].[CoursEnClasse]'))
ALTER TABLE [dbo].[CoursEnClasse] DROP CONSTRAINT [FK_CoursEnClasse_Cours]
GO
/****** Object:  ForeignKey [FK_CoursEnLigne_Cours]    Script Date: 01/09/2012 16:58:41 ******/
IF  EXISTS (SELECT * FROM sys.foreign_keys WHERE object_id = OBJECT_ID(N'[dbo].[FK_CoursEnLigne_Cours]') AND parent_object_id = OBJECT_ID(N'[dbo].[CoursEnLigne]'))
ALTER TABLE [dbo].[CoursEnLigne] DROP CONSTRAINT [FK_CoursEnLigne_Cours]
GO
/****** Object:  ForeignKey [FK_CoursEnseignant_Cours]    Script Date: 01/09/2012 16:58:41 ******/
IF  EXISTS (SELECT * FROM sys.foreign_keys WHERE object_id = OBJECT_ID(N'[dbo].[FK_CoursEnseignant_Cours]') AND parent_object_id = OBJECT_ID(N'[dbo].[CoursEnseignant]'))
ALTER TABLE [dbo].[CoursEnseignant] DROP CONSTRAINT [FK_CoursEnseignant_Cours]
GO
/****** Object:  ForeignKey [FK_CoursEnseignant_Personne]    Script Date: 01/09/2012 16:58:41 ******/
IF  EXISTS (SELECT * FROM sys.foreign_keys WHERE object_id = OBJECT_ID(N'[dbo].[FK_CoursEnseignant_Personne]') AND parent_object_id = OBJECT_ID(N'[dbo].[CoursEnseignant]'))
ALTER TABLE [dbo].[CoursEnseignant] DROP CONSTRAINT [FK_CoursEnseignant_Personne]
GO
/****** Object:  ForeignKey [FK_CoursGrade_Cours]    Script Date: 01/09/2012 16:58:41 ******/
IF  EXISTS (SELECT * FROM sys.foreign_keys WHERE object_id = OBJECT_ID(N'[dbo].[FK_CoursGrade_Cours]') AND parent_object_id = OBJECT_ID(N'[dbo].[CoursGrade]'))
ALTER TABLE [dbo].[CoursGrade] DROP CONSTRAINT [FK_CoursGrade_Cours]
GO
/****** Object:  ForeignKey [FK_CoursGrade_Etudiant]    Script Date: 01/09/2012 16:58:41 ******/
IF  EXISTS (SELECT * FROM sys.foreign_keys WHERE object_id = OBJECT_ID(N'[dbo].[FK_CoursGrade_Etudiant]') AND parent_object_id = OBJECT_ID(N'[dbo].[CoursGrade]'))
ALTER TABLE [dbo].[CoursGrade] DROP CONSTRAINT [FK_CoursGrade_Etudiant]
GO
/****** Object:  StoredProcedure [dbo].[GetEtudiantGrades]    Script Date: 01/09/2012 16:58:41 ******/
IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[GetEtudiantGrades]') AND type in (N'P', N'PC'))
DROP PROCEDURE [dbo].[GetEtudiantGrades]
GO
/****** Object:  StoredProcedure [dbo].[InsertBureauAffecte]    Script Date: 01/09/2012 16:58:41 ******/
IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[InsertBureauAffecte]') AND type in (N'P', N'PC'))
DROP PROCEDURE [dbo].[InsertBureauAffecte]
GO
/****** Object:  StoredProcedure [dbo].[UpdateBureauAffecte]    Script Date: 01/09/2012 16:58:41 ******/
IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[UpdateBureauAffecte]') AND type in (N'P', N'PC'))
DROP PROCEDURE [dbo].[UpdateBureauAffecte]
GO
/****** Object:  Table [dbo].[CoursEnClasse]    Script Date: 01/09/2012 16:58:41 ******/
IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[CoursEnClasse]') AND type in (N'U'))
DROP TABLE [dbo].[CoursEnClasse]
GO
/****** Object:  Table [dbo].[CoursEnLigne]    Script Date: 01/09/2012 16:58:41 ******/
IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[CoursEnLigne]') AND type in (N'U'))
DROP TABLE [dbo].[CoursEnLigne]
GO
/****** Object:  Table [dbo].[CoursEnseignant]    Script Date: 01/09/2012 16:58:41 ******/
IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[CoursEnseignant]') AND type in (N'U'))
DROP TABLE [dbo].[CoursEnseignant]
GO
/****** Object:  Table [dbo].[CoursGrade]    Script Date: 01/09/2012 16:58:41 ******/
IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[CoursGrade]') AND type in (N'U'))
DROP TABLE [dbo].[CoursGrade]
GO
/****** Object:  StoredProcedure [dbo].[DeleteBureauAffecte]    Script Date: 01/09/2012 16:58:41 ******/
IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[DeleteBureauAffecte]') AND type in (N'P', N'PC'))
DROP PROCEDURE [dbo].[DeleteBureauAffecte]
GO
/****** Object:  StoredProcedure [dbo].[DeletePersonne]    Script Date: 01/09/2012 16:58:41 ******/
IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[DeletePersonne]') AND type in (N'P', N'PC'))
DROP PROCEDURE [dbo].[DeletePersonne]
GO
/****** Object:  Table [dbo].[BureauAffecte]    Script Date: 01/09/2012 16:58:41 ******/
IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[BureauAffecte]') AND type in (N'U'))
DROP TABLE [dbo].[BureauAffecte]
GO
/****** Object:  Table [dbo].[Cours]    Script Date: 01/09/2012 16:58:41 ******/
IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[Cours]') AND type in (N'U'))
DROP TABLE [dbo].[Cours]
GO
/****** Object:  StoredProcedure [dbo].[UpdatePersonne]    Script Date: 01/09/2012 16:58:41 ******/
IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[UpdatePersonne]') AND type in (N'P', N'PC'))
DROP PROCEDURE [dbo].[UpdatePersonne]
GO
/****** Object:  StoredProcedure [dbo].[InsertPersonne]    Script Date: 01/09/2012 16:58:41 ******/
IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[InsertPersonne]') AND type in (N'P', N'PC'))
DROP PROCEDURE [dbo].[InsertPersonne]
GO
/****** Object:  Table [dbo].[Personne]    Script Date: 01/09/2012 16:58:41 ******/
IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[Personne]') AND type in (N'U'))
DROP TABLE [dbo].[Personne]
GO
/****** Object:  Table [dbo].[Departement]    Script Date: 01/09/2012 16:58:41 ******/
IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[Departement]') AND type in (N'U'))
DROP TABLE [dbo].[Departement]
GO
/****** Object:  Table [dbo].[Departement]    Script Date: 01/09/2012 16:58:41 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[Departement]') AND type in (N'U'))
BEGIN
CREATE TABLE [dbo].[Departement](
	[DepartementID] [int] NOT NULL,
	[Nom] [nvarchar](50) COLLATE French_CI_AS NOT NULL,
	[Budget] [money] NOT NULL,
	[DateDebut] [datetime] NOT NULL,
	[Administrateur] [int] NULL,
 CONSTRAINT [PK_Departement] PRIMARY KEY CLUSTERED 
(
	[DepartementID] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON)
)
END
GO
INSERT [dbo].[Departement] ([DepartementID], [Nom], [Budget], [DateDebut], [Administrateur]) VALUES (1, N'Engineering', 350000.0000, CAST(0x0000999C00000000 AS DateTime), 2)
INSERT [dbo].[Departement] ([DepartementID], [Nom], [Budget], [DateDebut], [Administrateur]) VALUES (2, N'English', 120000.0000, CAST(0x0000999C00000000 AS DateTime), 6)
INSERT [dbo].[Departement] ([DepartementID], [Nom], [Budget], [DateDebut], [Administrateur]) VALUES (4, N'Economics', 200000.0000, CAST(0x0000999C00000000 AS DateTime), 4)
INSERT [dbo].[Departement] ([DepartementID], [Nom], [Budget], [DateDebut], [Administrateur]) VALUES (7, N'Mathematics', 250000.0000, CAST(0x0000999C00000000 AS DateTime), 3)
/****** Object:  Table [dbo].[Personne]    Script Date: 01/09/2012 16:58:41 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[Personne]') AND type in (N'U'))
BEGIN
CREATE TABLE [dbo].[Personne](
	[PersonneID] [int] IDENTITY(1,1) NOT NULL,
	[Nom] [nvarchar](50) COLLATE French_CI_AS NOT NULL,
	[Prenom] [nvarchar](50) COLLATE French_CI_AS NOT NULL,
	[DateEmbauche] [datetime] NULL,
	[DateInscription] [datetime] NULL,
 CONSTRAINT [PK_Ecole.Etudiant] PRIMARY KEY CLUSTERED 
(
	[PersonneID] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON)
)
END
GO
SET IDENTITY_INSERT [dbo].[Personne] ON
INSERT [dbo].[Personne] ([PersonneID], [Nom], [Prenom], [DateEmbauche], [DateInscription]) VALUES (1, N'Abercrombie', N'Kim', CAST(0x000087CF00000000 AS DateTime), NULL)
INSERT [dbo].[Personne] ([PersonneID], [Nom], [Prenom], [DateEmbauche], [DateInscription]) VALUES (2, N'Barzdukas', N'Gytis', NULL, CAST(0x000096C200000000 AS DateTime))
INSERT [dbo].[Personne] ([PersonneID], [Nom], [Prenom], [DateEmbauche], [DateInscription]) VALUES (3, N'Justice', N'Peggy', NULL, CAST(0x0000910D00000000 AS DateTime))
INSERT [dbo].[Personne] ([PersonneID], [Nom], [Prenom], [DateEmbauche], [DateInscription]) VALUES (4, N'Fakhouri', N'Fadi', CAST(0x0000926000000000 AS DateTime), NULL)
INSERT [dbo].[Personne] ([PersonneID], [Nom], [Prenom], [DateEmbauche], [DateInscription]) VALUES (5, N'Harui', N'Roger', CAST(0x00008C8700000000 AS DateTime), NULL)
INSERT [dbo].[Personne] ([PersonneID], [Nom], [Prenom], [DateEmbauche], [DateInscription]) VALUES (6, N'Li', N'Yan', NULL, CAST(0x0000927A00000000 AS DateTime))
INSERT [dbo].[Personne] ([PersonneID], [Nom], [Prenom], [DateEmbauche], [DateInscription]) VALUES (7, N'Norman', N'Laura', NULL, CAST(0x000093E700000000 AS DateTime))
INSERT [dbo].[Personne] ([PersonneID], [Nom], [Prenom], [DateEmbauche], [DateInscription]) VALUES (8, N'Olivotto', N'Nino', NULL, CAST(0x000096C200000000 AS DateTime))
INSERT [dbo].[Personne] ([PersonneID], [Nom], [Prenom], [DateEmbauche], [DateInscription]) VALUES (9, N'Tang', N'Wayne', NULL, CAST(0x000096C200000000 AS DateTime))
INSERT [dbo].[Personne] ([PersonneID], [Nom], [Prenom], [DateEmbauche], [DateInscription]) VALUES (10, N'Alonso', N'Meredith', NULL, CAST(0x0000927A00000000 AS DateTime))
INSERT [dbo].[Personne] ([PersonneID], [Nom], [Prenom], [DateEmbauche], [DateInscription]) VALUES (11, N'Lopez', N'Sophia', NULL, CAST(0x0000955500000000 AS DateTime))
INSERT [dbo].[Personne] ([PersonneID], [Nom], [Prenom], [DateEmbauche], [DateInscription]) VALUES (12, N'Browning', N'Meredith', NULL, CAST(0x00008FA000000000 AS DateTime))
INSERT [dbo].[Personne] ([PersonneID], [Nom], [Prenom], [DateEmbauche], [DateInscription]) VALUES (13, N'Anand', N'Arturo', NULL, CAST(0x000093E700000000 AS DateTime))
INSERT [dbo].[Personne] ([PersonneID], [Nom], [Prenom], [DateEmbauche], [DateInscription]) VALUES (14, N'Walker', N'Alexandra', NULL, CAST(0x00008FA000000000 AS DateTime))
INSERT [dbo].[Personne] ([PersonneID], [Nom], [Prenom], [DateEmbauche], [DateInscription]) VALUES (15, N'Powell', N'Carson', NULL, CAST(0x0000955500000000 AS DateTime))
INSERT [dbo].[Personne] ([PersonneID], [Nom], [Prenom], [DateEmbauche], [DateInscription]) VALUES (16, N'Jai', N'Damien', NULL, CAST(0x0000910D00000000 AS DateTime))
INSERT [dbo].[Personne] ([PersonneID], [Nom], [Prenom], [DateEmbauche], [DateInscription]) VALUES (17, N'Carlson', N'Robyn', NULL, CAST(0x000096C200000000 AS DateTime))
INSERT [dbo].[Personne] ([PersonneID], [Nom], [Prenom], [DateEmbauche], [DateInscription]) VALUES (18, N'Zheng', N'Roger', CAST(0x0000948B00000000 AS DateTime), NULL)
INSERT [dbo].[Personne] ([PersonneID], [Nom], [Prenom], [DateEmbauche], [DateInscription]) VALUES (19, N'Bryant', N'Carson', NULL, CAST(0x0000910D00000000 AS DateTime))
INSERT [dbo].[Personne] ([PersonneID], [Nom], [Prenom], [DateEmbauche], [DateInscription]) VALUES (20, N'Suarez', N'Robyn', NULL, CAST(0x0000955500000000 AS DateTime))
INSERT [dbo].[Personne] ([PersonneID], [Nom], [Prenom], [DateEmbauche], [DateInscription]) VALUES (21, N'Holt', N'Roger', NULL, CAST(0x0000955500000000 AS DateTime))
INSERT [dbo].[Personne] ([PersonneID], [Nom], [Prenom], [DateEmbauche], [DateInscription]) VALUES (22, N'Alexander', N'Carson', NULL, CAST(0x000096C200000000 AS DateTime))
INSERT [dbo].[Personne] ([PersonneID], [Nom], [Prenom], [DateEmbauche], [DateInscription]) VALUES (23, N'Morgan', N'Isaiah', NULL, CAST(0x0000910D00000000 AS DateTime))
INSERT [dbo].[Personne] ([PersonneID], [Nom], [Prenom], [DateEmbauche], [DateInscription]) VALUES (24, N'Martin', N'Randall', NULL, CAST(0x000096C200000000 AS DateTime))
INSERT [dbo].[Personne] ([PersonneID], [Nom], [Prenom], [DateEmbauche], [DateInscription]) VALUES (25, N'Kapoor', N'Candace', CAST(0x0000902800000000 AS DateTime), NULL)
INSERT [dbo].[Personne] ([PersonneID], [Nom], [Prenom], [DateEmbauche], [DateInscription]) VALUES (26, N'Rogers', N'Cody', NULL, CAST(0x0000927A00000000 AS DateTime))
INSERT [dbo].[Personne] ([PersonneID], [Nom], [Prenom], [DateEmbauche], [DateInscription]) VALUES (27, N'Serrano', N'Stacy', CAST(0x00008DD600000000 AS DateTime), NULL)
INSERT [dbo].[Personne] ([PersonneID], [Nom], [Prenom], [DateEmbauche], [DateInscription]) VALUES (28, N'White', N'Anthony', NULL, CAST(0x0000910D00000000 AS DateTime))
INSERT [dbo].[Personne] ([PersonneID], [Nom], [Prenom], [DateEmbauche], [DateInscription]) VALUES (29, N'Griffin', N'Rachel', NULL, CAST(0x0000955500000000 AS DateTime))
INSERT [dbo].[Personne] ([PersonneID], [Nom], [Prenom], [DateEmbauche], [DateInscription]) VALUES (30, N'Shan', N'Alicia', NULL, CAST(0x000093E700000000 AS DateTime))
INSERT [dbo].[Personne] ([PersonneID], [Nom], [Prenom], [DateEmbauche], [DateInscription]) VALUES (31, N'Stewart', N'Jasmine', CAST(0x00008B8100000000 AS DateTime), NULL)
INSERT [dbo].[Personne] ([PersonneID], [Nom], [Prenom], [DateEmbauche], [DateInscription]) VALUES (32, N'Xu', N'Kristen', CAST(0x000090E500000000 AS DateTime), NULL)
INSERT [dbo].[Personne] ([PersonneID], [Nom], [Prenom], [DateEmbauche], [DateInscription]) VALUES (33, N'Gao', N'Erica', NULL, CAST(0x0000931100000000 AS DateTime))
INSERT [dbo].[Personne] ([PersonneID], [Nom], [Prenom], [DateEmbauche], [DateInscription]) VALUES (34, N'Van Houten', N'Roger', CAST(0x0000900100000000 AS DateTime), NULL)
SET IDENTITY_INSERT [dbo].[Personne] OFF
/****** Object:  StoredProcedure [dbo].[InsertPersonne]    Script Date: 01/09/2012 16:58:41 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[InsertPersonne]') AND type in (N'P', N'PC'))
BEGIN
EXEC dbo.sp_executesql @statement = N'
CREATE PROCEDURE [dbo].[InsertPersonne]
		@Nom nvarchar(50),
		@Prenom nvarchar(50),
		@DateEmbauche datetime,
		@DateInscription datetime
		AS
		INSERT INTO dbo.Personne (Nom, 
					Prenom, 
					DateEmbauche, 
					DateInscription)
		VALUES (@Nom, 
			@Prenom, 
			@DateEmbauche, 
			@DateInscription);
		SELECT SCOPE_IDENTITY() as NewPersonneID;
' 
END
GO
/****** Object:  StoredProcedure [dbo].[UpdatePersonne]    Script Date: 01/09/2012 16:58:41 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[UpdatePersonne]') AND type in (N'P', N'PC'))
BEGIN
EXEC dbo.sp_executesql @statement = N'
CREATE PROCEDURE [dbo].[UpdatePersonne]
		@PersonneID int,
		@Nom nvarchar(50),
		@Prenom nvarchar(50),
		@DateEmbauche datetime,
		@DateInscription datetime
		AS
		UPDATE Personne SET Nom=@Nom, 
				Prenom=@Prenom,
				DateEmbauche=@DateEmbauche,
				DateInscription=@DateInscription
		WHERE PersonneID=@PersonneID;
' 
END
GO
/****** Object:  Table [dbo].[Cours]    Script Date: 01/09/2012 16:58:41 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[Cours]') AND type in (N'U'))
BEGIN
CREATE TABLE [dbo].[Cours](
	[CoursID] [int] NOT NULL,
	[Titre] [nvarchar](100) COLLATE French_CI_AS NOT NULL,
	[Credits] [int] NOT NULL,
	[DepartementID] [int] NOT NULL,
 CONSTRAINT [PK_Ecole.Cours] PRIMARY KEY CLUSTERED 
(
	[CoursID] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON)
)
END
GO
INSERT [dbo].[Cours] ([CoursID], [Titre], [Credits], [DepartementID]) VALUES (1045, N'Calculus', 4, 7)
INSERT [dbo].[Cours] ([CoursID], [Titre], [Credits], [DepartementID]) VALUES (1050, N'Chemistry', 4, 1)
INSERT [dbo].[Cours] ([CoursID], [Titre], [Credits], [DepartementID]) VALUES (1061, N'Physics', 4, 1)
INSERT [dbo].[Cours] ([CoursID], [Titre], [Credits], [DepartementID]) VALUES (2021, N'Composition', 3, 2)
INSERT [dbo].[Cours] ([CoursID], [Titre], [Credits], [DepartementID]) VALUES (2030, N'Poetry', 2, 2)
INSERT [dbo].[Cours] ([CoursID], [Titre], [Credits], [DepartementID]) VALUES (2042, N'Literature', 4, 2)
INSERT [dbo].[Cours] ([CoursID], [Titre], [Credits], [DepartementID]) VALUES (3141, N'Trigonometry', 4, 7)
INSERT [dbo].[Cours] ([CoursID], [Titre], [Credits], [DepartementID]) VALUES (4022, N'Microeconomics', 3, 4)
INSERT [dbo].[Cours] ([CoursID], [Titre], [Credits], [DepartementID]) VALUES (4041, N'Macroeconomics', 3, 4)
INSERT [dbo].[Cours] ([CoursID], [Titre], [Credits], [DepartementID]) VALUES (4061, N'Quantitative', 2, 4)
/****** Object:  Table [dbo].[BureauAffecte]    Script Date: 01/09/2012 16:58:41 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[BureauAffecte]') AND type in (N'U'))
BEGIN
CREATE TABLE [dbo].[BureauAffecte](
	[EnseignantID] [int] NOT NULL,
	[Emplacement] [nvarchar](50) COLLATE French_CI_AS NOT NULL,
 CONSTRAINT [PK_BureauAffecte] PRIMARY KEY CLUSTERED 
(
	[EnseignantID] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON)
)
END
GO
INSERT [dbo].[BureauAffecte] ([EnseignantID], [Emplacement]) VALUES (1, N'17 Smith')
INSERT [dbo].[BureauAffecte] ([EnseignantID], [Emplacement]) VALUES (4, N'29 Adams')
INSERT [dbo].[BureauAffecte] ([EnseignantID], [Emplacement]) VALUES (5, N'37 Williams')
INSERT [dbo].[BureauAffecte] ([EnseignantID], [Emplacement]) VALUES (18, N'143 Smith')
INSERT [dbo].[BureauAffecte] ([EnseignantID], [Emplacement]) VALUES (25, N'57 Adams')
INSERT [dbo].[BureauAffecte] ([EnseignantID], [Emplacement]) VALUES (27, N'271 Williams')
INSERT [dbo].[BureauAffecte] ([EnseignantID], [Emplacement]) VALUES (31, N'131 Smith')
INSERT [dbo].[BureauAffecte] ([EnseignantID], [Emplacement]) VALUES (32, N'203 Williams')
INSERT [dbo].[BureauAffecte] ([EnseignantID], [Emplacement]) VALUES (34, N'213 Smith')
/****** Object:  StoredProcedure [dbo].[DeletePersonne]    Script Date: 01/09/2012 16:58:41 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[DeletePersonne]') AND type in (N'P', N'PC'))
BEGIN
EXEC dbo.sp_executesql @statement = N'
CREATE PROCEDURE [dbo].[DeletePersonne]
		@PersonneID int
		AS
		DELETE FROM Personne WHERE PersonneID = @PersonneID;
' 
END
GO
/****** Object:  StoredProcedure [dbo].[DeleteBureauAffecte]    Script Date: 01/09/2012 16:58:41 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[DeleteBureauAffecte]') AND type in (N'P', N'PC'))
BEGIN
EXEC dbo.sp_executesql @statement = N'
CREATE PROCEDURE [dbo].[DeleteBureauAffecte]
		@EnseignantID int
		AS
		DELETE FROM BureauAffecte
		WHERE EnseignantID=@EnseignantID;
' 
END
GO
/****** Object:  Table [dbo].[CoursGrade]    Script Date: 01/09/2012 16:58:41 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[CoursGrade]') AND type in (N'U'))
BEGIN
CREATE TABLE [dbo].[CoursGrade](
	[InscriptionID] [int] IDENTITY(1,1) NOT NULL,
	[CoursID] [int] NOT NULL,
	[EtudiantID] [int] NOT NULL,
	[Grade] [decimal](3, 2) NULL,
 CONSTRAINT [PK_CoursGrade] PRIMARY KEY CLUSTERED 
(
	[InscriptionID] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON)
)
END
GO
SET IDENTITY_INSERT [dbo].[CoursGrade] ON
INSERT [dbo].[CoursGrade] ([InscriptionID], [CoursID], [EtudiantID], [Grade]) VALUES (1, 2021, 2, CAST(4.00 AS Decimal(3, 2)))
INSERT [dbo].[CoursGrade] ([InscriptionID], [CoursID], [EtudiantID], [Grade]) VALUES (2, 2030, 2, CAST(3.50 AS Decimal(3, 2)))
INSERT [dbo].[CoursGrade] ([InscriptionID], [CoursID], [EtudiantID], [Grade]) VALUES (3, 2021, 3, CAST(3.00 AS Decimal(3, 2)))
INSERT [dbo].[CoursGrade] ([InscriptionID], [CoursID], [EtudiantID], [Grade]) VALUES (4, 2030, 3, CAST(4.00 AS Decimal(3, 2)))
INSERT [dbo].[CoursGrade] ([InscriptionID], [CoursID], [EtudiantID], [Grade]) VALUES (5, 2021, 6, CAST(2.50 AS Decimal(3, 2)))
INSERT [dbo].[CoursGrade] ([InscriptionID], [CoursID], [EtudiantID], [Grade]) VALUES (6, 2042, 6, CAST(3.50 AS Decimal(3, 2)))
INSERT [dbo].[CoursGrade] ([InscriptionID], [CoursID], [EtudiantID], [Grade]) VALUES (7, 2021, 7, CAST(3.50 AS Decimal(3, 2)))
INSERT [dbo].[CoursGrade] ([InscriptionID], [CoursID], [EtudiantID], [Grade]) VALUES (8, 2042, 7, CAST(4.00 AS Decimal(3, 2)))
INSERT [dbo].[CoursGrade] ([InscriptionID], [CoursID], [EtudiantID], [Grade]) VALUES (9, 2021, 8, CAST(3.00 AS Decimal(3, 2)))
INSERT [dbo].[CoursGrade] ([InscriptionID], [CoursID], [EtudiantID], [Grade]) VALUES (10, 2042, 8, CAST(3.00 AS Decimal(3, 2)))
INSERT [dbo].[CoursGrade] ([InscriptionID], [CoursID], [EtudiantID], [Grade]) VALUES (11, 4041, 9, CAST(3.50 AS Decimal(3, 2)))
INSERT [dbo].[CoursGrade] ([InscriptionID], [CoursID], [EtudiantID], [Grade]) VALUES (12, 4041, 10, NULL)
INSERT [dbo].[CoursGrade] ([InscriptionID], [CoursID], [EtudiantID], [Grade]) VALUES (13, 4041, 11, CAST(2.50 AS Decimal(3, 2)))
INSERT [dbo].[CoursGrade] ([InscriptionID], [CoursID], [EtudiantID], [Grade]) VALUES (14, 4041, 12, NULL)
INSERT [dbo].[CoursGrade] ([InscriptionID], [CoursID], [EtudiantID], [Grade]) VALUES (15, 4061, 12, NULL)
INSERT [dbo].[CoursGrade] ([InscriptionID], [CoursID], [EtudiantID], [Grade]) VALUES (16, 4022, 14, CAST(3.00 AS Decimal(3, 2)))
INSERT [dbo].[CoursGrade] ([InscriptionID], [CoursID], [EtudiantID], [Grade]) VALUES (17, 4022, 13, CAST(4.00 AS Decimal(3, 2)))
INSERT [dbo].[CoursGrade] ([InscriptionID], [CoursID], [EtudiantID], [Grade]) VALUES (18, 4061, 13, CAST(4.00 AS Decimal(3, 2)))
INSERT [dbo].[CoursGrade] ([InscriptionID], [CoursID], [EtudiantID], [Grade]) VALUES (19, 4041, 14, CAST(3.00 AS Decimal(3, 2)))
INSERT [dbo].[CoursGrade] ([InscriptionID], [CoursID], [EtudiantID], [Grade]) VALUES (20, 4022, 15, CAST(2.50 AS Decimal(3, 2)))
INSERT [dbo].[CoursGrade] ([InscriptionID], [CoursID], [EtudiantID], [Grade]) VALUES (21, 4022, 16, CAST(2.00 AS Decimal(3, 2)))
INSERT [dbo].[CoursGrade] ([InscriptionID], [CoursID], [EtudiantID], [Grade]) VALUES (22, 4022, 17, NULL)
INSERT [dbo].[CoursGrade] ([InscriptionID], [CoursID], [EtudiantID], [Grade]) VALUES (23, 4022, 19, CAST(3.50 AS Decimal(3, 2)))
INSERT [dbo].[CoursGrade] ([InscriptionID], [CoursID], [EtudiantID], [Grade]) VALUES (24, 4061, 20, CAST(4.00 AS Decimal(3, 2)))
INSERT [dbo].[CoursGrade] ([InscriptionID], [CoursID], [EtudiantID], [Grade]) VALUES (25, 4061, 21, CAST(2.00 AS Decimal(3, 2)))
INSERT [dbo].[CoursGrade] ([InscriptionID], [CoursID], [EtudiantID], [Grade]) VALUES (26, 4022, 22, CAST(3.00 AS Decimal(3, 2)))
INSERT [dbo].[CoursGrade] ([InscriptionID], [CoursID], [EtudiantID], [Grade]) VALUES (27, 4041, 22, CAST(3.50 AS Decimal(3, 2)))
INSERT [dbo].[CoursGrade] ([InscriptionID], [CoursID], [EtudiantID], [Grade]) VALUES (28, 4061, 22, CAST(2.50 AS Decimal(3, 2)))
INSERT [dbo].[CoursGrade] ([InscriptionID], [CoursID], [EtudiantID], [Grade]) VALUES (29, 4022, 23, CAST(3.00 AS Decimal(3, 2)))
INSERT [dbo].[CoursGrade] ([InscriptionID], [CoursID], [EtudiantID], [Grade]) VALUES (30, 1045, 23, CAST(1.50 AS Decimal(3, 2)))
INSERT [dbo].[CoursGrade] ([InscriptionID], [CoursID], [EtudiantID], [Grade]) VALUES (31, 1061, 24, CAST(4.00 AS Decimal(3, 2)))
INSERT [dbo].[CoursGrade] ([InscriptionID], [CoursID], [EtudiantID], [Grade]) VALUES (32, 1061, 25, CAST(3.00 AS Decimal(3, 2)))
INSERT [dbo].[CoursGrade] ([InscriptionID], [CoursID], [EtudiantID], [Grade]) VALUES (33, 1050, 26, CAST(3.50 AS Decimal(3, 2)))
INSERT [dbo].[CoursGrade] ([InscriptionID], [CoursID], [EtudiantID], [Grade]) VALUES (34, 1061, 26, CAST(3.00 AS Decimal(3, 2)))
INSERT [dbo].[CoursGrade] ([InscriptionID], [CoursID], [EtudiantID], [Grade]) VALUES (35, 1061, 27, CAST(3.00 AS Decimal(3, 2)))
INSERT [dbo].[CoursGrade] ([InscriptionID], [CoursID], [EtudiantID], [Grade]) VALUES (36, 1045, 28, CAST(2.50 AS Decimal(3, 2)))
INSERT [dbo].[CoursGrade] ([InscriptionID], [CoursID], [EtudiantID], [Grade]) VALUES (37, 1050, 28, CAST(3.50 AS Decimal(3, 2)))
INSERT [dbo].[CoursGrade] ([InscriptionID], [CoursID], [EtudiantID], [Grade]) VALUES (38, 1061, 29, CAST(4.00 AS Decimal(3, 2)))
INSERT [dbo].[CoursGrade] ([InscriptionID], [CoursID], [EtudiantID], [Grade]) VALUES (39, 1050, 30, CAST(3.50 AS Decimal(3, 2)))
INSERT [dbo].[CoursGrade] ([InscriptionID], [CoursID], [EtudiantID], [Grade]) VALUES (40, 1061, 30, CAST(4.00 AS Decimal(3, 2)))
SET IDENTITY_INSERT [dbo].[CoursGrade] OFF
/****** Object:  Table [dbo].[CoursEnseignant]    Script Date: 01/09/2012 16:58:41 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[CoursEnseignant]') AND type in (N'U'))
BEGIN
CREATE TABLE [dbo].[CoursEnseignant](
	[CoursID] [int] NOT NULL,
	[PersonneID] [int] NOT NULL,
 CONSTRAINT [PK_CoursEnseignant] PRIMARY KEY CLUSTERED 
(
	[CoursID] ASC,
	[PersonneID] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON)
)
END
GO
INSERT [dbo].[CoursEnseignant] ([CoursID], [PersonneID]) VALUES (1045, 5)
INSERT [dbo].[CoursEnseignant] ([CoursID], [PersonneID]) VALUES (1050, 1)
INSERT [dbo].[CoursEnseignant] ([CoursID], [PersonneID]) VALUES (1061, 31)
INSERT [dbo].[CoursEnseignant] ([CoursID], [PersonneID]) VALUES (2021, 27)
INSERT [dbo].[CoursEnseignant] ([CoursID], [PersonneID]) VALUES (2030, 4)
INSERT [dbo].[CoursEnseignant] ([CoursID], [PersonneID]) VALUES (2042, 25)
INSERT [dbo].[CoursEnseignant] ([CoursID], [PersonneID]) VALUES (4022, 18)
INSERT [dbo].[CoursEnseignant] ([CoursID], [PersonneID]) VALUES (4041, 32)
INSERT [dbo].[CoursEnseignant] ([CoursID], [PersonneID]) VALUES (4061, 34)
/****** Object:  Table [dbo].[CoursEnLigne]    Script Date: 01/09/2012 16:58:41 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[CoursEnLigne]') AND type in (N'U'))
BEGIN
CREATE TABLE [dbo].[CoursEnLigne](
	[CoursID] [int] NOT NULL,
	[URL] [nvarchar](100) COLLATE French_CI_AS NOT NULL,
 CONSTRAINT [PK_CoursEnLigne] PRIMARY KEY CLUSTERED 
(
	[CoursID] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON)
)
END
GO
INSERT [dbo].[CoursEnLigne] ([CoursID], [URL]) VALUES (2021, N'http://www.fineartschool.net/Composition')
INSERT [dbo].[CoursEnLigne] ([CoursID], [URL]) VALUES (2030, N'http://www.fineartschool.net/Poetry')
INSERT [dbo].[CoursEnLigne] ([CoursID], [URL]) VALUES (3141, N'http://www.fineartschool.net/Trigonometry')
INSERT [dbo].[CoursEnLigne] ([CoursID], [URL]) VALUES (4041, N'http://www.fineartschool.net/Macroeconomics')
/****** Object:  Table [dbo].[CoursEnClasse]    Script Date: 01/09/2012 16:58:41 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[CoursEnClasse]') AND type in (N'U'))
BEGIN
CREATE TABLE [dbo].[CoursEnClasse](
	[CoursID] [int] NOT NULL,
	[Emplacement] [nvarchar](50) COLLATE French_CI_AS NOT NULL,
	[Jours] [nvarchar](50) COLLATE French_CI_AS NOT NULL,
	[Heure] [smalldatetime] NOT NULL,
 CONSTRAINT [PK_CoursEnClasse] PRIMARY KEY CLUSTERED 
(
	[CoursID] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON)
)
END
GO
INSERT [dbo].[CoursEnClasse] ([CoursID], [Emplacement], [Jours], [Heure]) VALUES (1045, N'121 Smith', N'MWHF', CAST(0x000003A2 AS SmallDateTime))
INSERT [dbo].[CoursEnClasse] ([CoursID], [Emplacement], [Jours], [Heure]) VALUES (1050, N'123 Smith', N'MTWH', CAST(0x000002B2 AS SmallDateTime))
INSERT [dbo].[CoursEnClasse] ([CoursID], [Emplacement], [Jours], [Heure]) VALUES (1061, N'234 Smith', N'TWHF', CAST(0x0000031B AS SmallDateTime))
INSERT [dbo].[CoursEnClasse] ([CoursID], [Emplacement], [Jours], [Heure]) VALUES (2042, N'225 Adams', N'MTWH', CAST(0x00000294 AS SmallDateTime))
INSERT [dbo].[CoursEnClasse] ([CoursID], [Emplacement], [Jours], [Heure]) VALUES (4022, N'23 Williams', N'MWF', CAST(0x0000021C AS SmallDateTime))
INSERT [dbo].[CoursEnClasse] ([CoursID], [Emplacement], [Jours], [Heure]) VALUES (4061, N'22 Williams', N'TH', CAST(0x000002A3 AS SmallDateTime))
/****** Object:  StoredProcedure [dbo].[UpdateBureauAffecte]    Script Date: 01/09/2012 16:58:41 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[UpdateBureauAffecte]') AND type in (N'P', N'PC'))
BEGIN
EXEC dbo.sp_executesql @statement = N'
CREATE PROCEDURE [dbo].[UpdateBureauAffecte]
		@EnseignantID int,
		@Emplacement nvarchar(50),
		@OrigTimestamp timestamp
		AS
		UPDATE BureauAffecte SET Emplacement=@Emplacement 
		WHERE EnseignantID=@EnseignantID AND [Timestamp]=@OrigTimestamp;
		IF @@ROWCOUNT > 0
		BEGIN
			SELECT [Timestamp] FROM BureauAffecte 
				WHERE EnseignantID=@EnseignantID;
		END
' 
END
GO
/****** Object:  StoredProcedure [dbo].[InsertBureauAffecte]    Script Date: 01/09/2012 16:58:41 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[InsertBureauAffecte]') AND type in (N'P', N'PC'))
BEGIN
EXEC dbo.sp_executesql @statement = N'
CREATE PROCEDURE [dbo].[InsertBureauAffecte]
		@EnseignantID int,
		@Emplacement nvarchar(50)
		AS
		INSERT INTO dbo.BureauAffecte (EnseignantID, Emplacement)
		VALUES (@EnseignantID, @Emplacement);
		IF @@ROWCOUNT > 0
		BEGIN
			SELECT [Timestamp] FROM BureauAffecte 
				WHERE EnseignantID=@EnseignantID;
		END
' 
END
GO
/****** Object:  StoredProcedure [dbo].[GetEtudiantGrades]    Script Date: 01/09/2012 16:58:41 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[GetEtudiantGrades]') AND type in (N'P', N'PC'))
BEGIN
EXEC dbo.sp_executesql @statement = N'
CREATE PROCEDURE [dbo].[GetEtudiantGrades]
            @EtudiantID int
            AS
            SELECT InscriptionID, Grade FROM dbo.CoursGrade
            WHERE EtudiantID = @EtudiantID
' 
END
GO
/****** Object:  ForeignKey [FK_BureauAffecte_Personne]    Script Date: 01/09/2012 16:58:41 ******/
IF NOT EXISTS (SELECT * FROM sys.foreign_keys WHERE object_id = OBJECT_ID(N'[dbo].[FK_BureauAffecte_Personne]') AND parent_object_id = OBJECT_ID(N'[dbo].[BureauAffecte]'))
ALTER TABLE [dbo].[BureauAffecte]  WITH CHECK ADD  CONSTRAINT [FK_BureauAffecte_Personne] FOREIGN KEY([EnseignantID])
REFERENCES [dbo].[Personne] ([PersonneID])
GO
IF  EXISTS (SELECT * FROM sys.foreign_keys WHERE object_id = OBJECT_ID(N'[dbo].[FK_BureauAffecte_Personne]') AND parent_object_id = OBJECT_ID(N'[dbo].[BureauAffecte]'))
ALTER TABLE [dbo].[BureauAffecte] CHECK CONSTRAINT [FK_BureauAffecte_Personne]
GO
/****** Object:  ForeignKey [FK_Cours_Departement]    Script Date: 01/09/2012 16:58:41 ******/
IF NOT EXISTS (SELECT * FROM sys.foreign_keys WHERE object_id = OBJECT_ID(N'[dbo].[FK_Cours_Departement]') AND parent_object_id = OBJECT_ID(N'[dbo].[Cours]'))
ALTER TABLE [dbo].[Cours]  WITH CHECK ADD  CONSTRAINT [FK_Cours_Departement] FOREIGN KEY([DepartementID])
REFERENCES [dbo].[Departement] ([DepartementID])
GO
IF  EXISTS (SELECT * FROM sys.foreign_keys WHERE object_id = OBJECT_ID(N'[dbo].[FK_Cours_Departement]') AND parent_object_id = OBJECT_ID(N'[dbo].[Cours]'))
ALTER TABLE [dbo].[Cours] CHECK CONSTRAINT [FK_Cours_Departement]
GO
/****** Object:  ForeignKey [FK_CoursEnClasse_Cours]    Script Date: 01/09/2012 16:58:41 ******/
IF NOT EXISTS (SELECT * FROM sys.foreign_keys WHERE object_id = OBJECT_ID(N'[dbo].[FK_CoursEnClasse_Cours]') AND parent_object_id = OBJECT_ID(N'[dbo].[CoursEnClasse]'))
ALTER TABLE [dbo].[CoursEnClasse]  WITH CHECK ADD  CONSTRAINT [FK_CoursEnClasse_Cours] FOREIGN KEY([CoursID])
REFERENCES [dbo].[Cours] ([CoursID])
GO
IF  EXISTS (SELECT * FROM sys.foreign_keys WHERE object_id = OBJECT_ID(N'[dbo].[FK_CoursEnClasse_Cours]') AND parent_object_id = OBJECT_ID(N'[dbo].[CoursEnClasse]'))
ALTER TABLE [dbo].[CoursEnClasse] CHECK CONSTRAINT [FK_CoursEnClasse_Cours]
GO
/****** Object:  ForeignKey [FK_CoursEnLigne_Cours]    Script Date: 01/09/2012 16:58:41 ******/
IF NOT EXISTS (SELECT * FROM sys.foreign_keys WHERE object_id = OBJECT_ID(N'[dbo].[FK_CoursEnLigne_Cours]') AND parent_object_id = OBJECT_ID(N'[dbo].[CoursEnLigne]'))
ALTER TABLE [dbo].[CoursEnLigne]  WITH CHECK ADD  CONSTRAINT [FK_CoursEnLigne_Cours] FOREIGN KEY([CoursID])
REFERENCES [dbo].[Cours] ([CoursID])
GO
IF  EXISTS (SELECT * FROM sys.foreign_keys WHERE object_id = OBJECT_ID(N'[dbo].[FK_CoursEnLigne_Cours]') AND parent_object_id = OBJECT_ID(N'[dbo].[CoursEnLigne]'))
ALTER TABLE [dbo].[CoursEnLigne] CHECK CONSTRAINT [FK_CoursEnLigne_Cours]
GO
/****** Object:  ForeignKey [FK_CoursEnseignant_Cours]    Script Date: 01/09/2012 16:58:41 ******/
IF NOT EXISTS (SELECT * FROM sys.foreign_keys WHERE object_id = OBJECT_ID(N'[dbo].[FK_CoursEnseignant_Cours]') AND parent_object_id = OBJECT_ID(N'[dbo].[CoursEnseignant]'))
ALTER TABLE [dbo].[CoursEnseignant]  WITH CHECK ADD  CONSTRAINT [FK_CoursEnseignant_Cours] FOREIGN KEY([CoursID])
REFERENCES [dbo].[Cours] ([CoursID])
GO
IF  EXISTS (SELECT * FROM sys.foreign_keys WHERE object_id = OBJECT_ID(N'[dbo].[FK_CoursEnseignant_Cours]') AND parent_object_id = OBJECT_ID(N'[dbo].[CoursEnseignant]'))
ALTER TABLE [dbo].[CoursEnseignant] CHECK CONSTRAINT [FK_CoursEnseignant_Cours]
GO
/****** Object:  ForeignKey [FK_CoursEnseignant_Personne]    Script Date: 01/09/2012 16:58:41 ******/
IF NOT EXISTS (SELECT * FROM sys.foreign_keys WHERE object_id = OBJECT_ID(N'[dbo].[FK_CoursEnseignant_Personne]') AND parent_object_id = OBJECT_ID(N'[dbo].[CoursEnseignant]'))
ALTER TABLE [dbo].[CoursEnseignant]  WITH CHECK ADD  CONSTRAINT [FK_CoursEnseignant_Personne] FOREIGN KEY([PersonneID])
REFERENCES [dbo].[Personne] ([PersonneID])
GO
IF  EXISTS (SELECT * FROM sys.foreign_keys WHERE object_id = OBJECT_ID(N'[dbo].[FK_CoursEnseignant_Personne]') AND parent_object_id = OBJECT_ID(N'[dbo].[CoursEnseignant]'))
ALTER TABLE [dbo].[CoursEnseignant] CHECK CONSTRAINT [FK_CoursEnseignant_Personne]
GO
/****** Object:  ForeignKey [FK_CoursGrade_Cours]    Script Date: 01/09/2012 16:58:41 ******/
IF NOT EXISTS (SELECT * FROM sys.foreign_keys WHERE object_id = OBJECT_ID(N'[dbo].[FK_CoursGrade_Cours]') AND parent_object_id = OBJECT_ID(N'[dbo].[CoursGrade]'))
ALTER TABLE [dbo].[CoursGrade]  WITH CHECK ADD  CONSTRAINT [FK_CoursGrade_Cours] FOREIGN KEY([CoursID])
REFERENCES [dbo].[Cours] ([CoursID])
GO
IF  EXISTS (SELECT * FROM sys.foreign_keys WHERE object_id = OBJECT_ID(N'[dbo].[FK_CoursGrade_Cours]') AND parent_object_id = OBJECT_ID(N'[dbo].[CoursGrade]'))
ALTER TABLE [dbo].[CoursGrade] CHECK CONSTRAINT [FK_CoursGrade_Cours]
GO
/****** Object:  ForeignKey [FK_CoursGrade_Etudiant]    Script Date: 01/09/2012 16:58:41 ******/
IF NOT EXISTS (SELECT * FROM sys.foreign_keys WHERE object_id = OBJECT_ID(N'[dbo].[FK_CoursGrade_Etudiant]') AND parent_object_id = OBJECT_ID(N'[dbo].[CoursGrade]'))
ALTER TABLE [dbo].[CoursGrade]  WITH CHECK ADD  CONSTRAINT [FK_CoursGrade_Etudiant] FOREIGN KEY([EtudiantID])
REFERENCES [dbo].[Personne] ([PersonneID])
GO
IF  EXISTS (SELECT * FROM sys.foreign_keys WHERE object_id = OBJECT_ID(N'[dbo].[FK_CoursGrade_Etudiant]') AND parent_object_id = OBJECT_ID(N'[dbo].[CoursGrade]'))
ALTER TABLE [dbo].[CoursGrade] CHECK CONSTRAINT [FK_CoursGrade_Etudiant]
GO