USE [ELEVE_SECONDAIRE]
GO

CREATE USER [ese_user] FOR LOGIN [ese_user] WITH DEFAULT_SCHEMA=[dbo]
GO
ALTER ROLE [db_owner] ADD MEMBER [ese_user]
GO
ALTER ROLE [db_datawriter] ADD MEMBER [ese_user]
GO

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[Acces](
	[IDAcces] [int] IDENTITY(1,1) NOT NULL,
	[IDUsager] [int] NOT NULL,
	[IDRole] [int] NOT NULL,
	[NomUsager] [varchar](300) NULL,
	[Courriel] [varchar](300) NULL,
	[Etat] [bit] NULL,
	[DateHeureCreation] [datetime] NULL,
	[DateDerniereModif] [datetime] NULL,
	[IDUserCreation] [int] NOT NULL,
	[IDUserDerniereModif] [int] NULL,
 CONSTRAINT [PK_Acces] PRIMARY KEY CLUSTERED 
(
	[IDAcces] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[Disponibilite](
	[IDDisponibilite] [int] IDENTITY(1,1) NOT NULL,
	[IDProgramme] [int] NOT NULL,
	[IDEvenementTemps] [int] NOT NULL,
	[NombrePlaceDisponible] [int] NULL,
	[NombrePlaceDisponibleRestante] [int] NULL,
	[Etat] [bit] NOT NULL,
	[DateHeureCreation] [datetime] NOT NULL,
	[DateDerniereModif] [datetime] NULL,
	[IDUserCreation] [int] NULL,
	[IDUserDerniereModif] [int] NULL,
 CONSTRAINT [PK_Disponibilite] PRIMARY KEY CLUSTERED 
(
	[IDDisponibilite] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[Eleve](
	[IDEleve] [int] IDENTITY(1,1) NOT NULL,
	[Nom] [nvarchar](50) NOT NULL,
	[Prenom] [nvarchar](50) NOT NULL,
	[Courriel] [nvarchar](50) NOT NULL,
	[NoTelephone] [nchar](10) NULL,
	[NomEcoleSecondaire] [nchar](10) NULL,
	[Etat] [bit] NOT NULL,
	[DateHeureCreation] [datetime] NOT NULL,
	[DateHeureModification] [datetime] NULL,
 CONSTRAINT [PK_Eleve] PRIMARY KEY CLUSTERED 
(
	[IDEleve] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[Evenement](
	[IDEvenement] [int] IDENTITY(1,1) NOT NULL,
	[DateEvenement] [date] NOT NULL,
	[Heure] [int] NOT NULL,
	[HeureOfficielle] [int] NOT NULL,
	[HeureDiner] [int] NOT NULL,
	[Local] [nvarchar](50) NOT NULL,
	[Etat] [bit] NOT NULL,
	[DateDebutInscription] [date] NOT NULL,
	[DateFinInscription] [date] NOT NULL,
	[DateHeureCreation] [datetime] NOT NULL,
	[DateDerniereModif] [datetime] NULL,
	[IDUserCreation] [int] NULL,
	[IDUserDerniereModif] [int] NULL,
 CONSTRAINT [PK_Evenement] PRIMARY KEY CLUSTERED 
(
	[IDEvenement] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[EvenementTemps](
	[IDEvenementTemps] [int] IDENTITY(1,1) NOT NULL,
	[IDEvenement] [int] NOT NULL,
	[HeureDebut] [smallint] NOT NULL,
	[HeureFin] [smallint] NOT NULL,
	[Periode] [nvarchar](50) NOT NULL,
	[Etat] [bit] NOT NULL,
	[DateHeureCreation] [datetime] NOT NULL,
	[DateDerniereModif] [datetime] NULL,
	[IDUserCreation] [int] NULL,
	[IDUserDerniereModif] [int] NULL,
 CONSTRAINT [PK_EvenementTemps] PRIMARY KEY CLUSTERED 
(
	[IDEvenementTemps] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[Inscription](
	[IDInscription] [int] IDENTITY(1,1) NOT NULL,
	[IDDisponibilite] [int] NOT NULL,
	[IDEleve] [int] NOT NULL,
	[Commentaire] [nvarchar](max) NULL,
	[DateHeureCreation] [datetime] NOT NULL,
	[DateDerniereModif] [datetime] NULL,
	[IDUserCreation] [int] NULL,
	[IDUserDerniereModif] [int] NULL,
 CONSTRAINT [PK_Rencontre] PRIMARY KEY CLUSTERED 
(
	[IDInscription] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
GO

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[Inscription_Backup](
	[IDInscription] [int] IDENTITY(1,1) NOT NULL,
	[IDDisponibilite] [int] NOT NULL,
	[IDEleve] [int] NOT NULL,
	[Commentaire] [nvarchar](max) NULL,
	[DateHeureCreation] [datetime] NOT NULL,
	[DateDerniereModif] [datetime] NULL,
	[IDUserCreation] [int] NULL,
	[IDUserDerniereModif] [int] NULL
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
GO

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[Notification](
	[IDNotification] [int] IDENTITY(1,1) NOT NULL,
	[IDEvenement] [int] NOT NULL,
	[DateEnvoie] [datetime] NOT NULL,
	[IDUserEnvoie] [int] NOT NULL,
 CONSTRAINT [PK_Notification] PRIMARY KEY CLUSTERED 
(
	[IDNotification] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[NotificationEleve](
	[IDNotificationEleve] [int] IDENTITY(1,1) NOT NULL,
	[IDNotification] [int] NOT NULL,
	[IDEleve] [int] NOT NULL,
	[Envoye] [bit] NOT NULL,
	[DateHeureCreation] [datetime] NOT NULL,
 CONSTRAINT [PK_NotificationEleve] PRIMARY KEY CLUSTERED 
(
	[IDNotificationEleve] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[Programme](
	[IDProgramme] [int] IDENTITY(1,1) NOT NULL,
	[Numero] [nvarchar](5) NULL,
	[Titre] [nvarchar](500) NOT NULL,
	[Description] [nvarchar](max) NULL,
	[IDTypeFormation] [int] NOT NULL,
	[Etat] [bit] NOT NULL,
	[DateHeureCreation] [datetime] NOT NULL,
	[DateDerniereModif] [datetime] NULL,
	[IDUserCreation] [int] NULL,
	[IDUserDerniereModif] [int] NULL,
 CONSTRAINT [PK_Programme] PRIMARY KEY CLUSTERED 
(
	[IDProgramme] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
GO

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[Role](
	[IDRole] [int] IDENTITY(1,1) NOT NULL,
	[Type] [nvarchar](300) NULL,
	[Description] [nvarchar](500) NULL,
	[Tache] [nvarchar](1000) NULL,
 CONSTRAINT [PK_Role] PRIMARY KEY CLUSTERED 
(
	[IDRole] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[TypeFormation](
	[IDTypeProgramme] [int] IDENTITY(1,1) NOT NULL,
	[Numero] [nvarchar](2) NOT NULL,
	[Titre] [nvarchar](30) NOT NULL,
 CONSTRAINT [PK_TypeFormation] PRIMARY KEY CLUSTERED 
(
	[IDTypeProgramme] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[Usager](
	[IDUsager] [int] IDENTITY(1,1) NOT NULL,
	[Nom] [varchar](300) NULL,
	[Prenom] [varchar](300) NULL,
	[Fonction] [varchar](500) NULL,
 CONSTRAINT [PK_Usager] PRIMARY KEY CLUSTERED 
(
	[IDUsager] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
ALTER TABLE [dbo].[Acces]  WITH CHECK ADD  CONSTRAINT [FK_Acces_Role] FOREIGN KEY([IDRole])
REFERENCES [dbo].[Role] ([IDRole])
GO
ALTER TABLE [dbo].[Acces] CHECK CONSTRAINT [FK_Acces_Role]
GO
ALTER TABLE [dbo].[Acces]  WITH CHECK ADD  CONSTRAINT [FK_Acces_Usager] FOREIGN KEY([IDUsager])
REFERENCES [dbo].[Usager] ([IDUsager])
GO
ALTER TABLE [dbo].[Acces] CHECK CONSTRAINT [FK_Acces_Usager]
GO
ALTER TABLE [dbo].[Disponibilite]  WITH CHECK ADD  CONSTRAINT [FK_Disponibilite_EvenementTemps] FOREIGN KEY([IDEvenementTemps])
REFERENCES [dbo].[EvenementTemps] ([IDEvenementTemps])
GO
ALTER TABLE [dbo].[Disponibilite] CHECK CONSTRAINT [FK_Disponibilite_EvenementTemps]
GO
ALTER TABLE [dbo].[Disponibilite]  WITH CHECK ADD  CONSTRAINT [FK_Disponibilite_Programme] FOREIGN KEY([IDProgramme])
REFERENCES [dbo].[Programme] ([IDProgramme])
GO
ALTER TABLE [dbo].[Disponibilite] CHECK CONSTRAINT [FK_Disponibilite_Programme]
GO
ALTER TABLE [dbo].[EvenementTemps]  WITH CHECK ADD  CONSTRAINT [FK_EvenementTemps_Evenement] FOREIGN KEY([IDEvenement])
REFERENCES [dbo].[Evenement] ([IDEvenement])
GO
ALTER TABLE [dbo].[EvenementTemps] CHECK CONSTRAINT [FK_EvenementTemps_Evenement]
GO
ALTER TABLE [dbo].[Inscription]  WITH CHECK ADD  CONSTRAINT [FK_Rencontre_Disponibilite] FOREIGN KEY([IDDisponibilite])
REFERENCES [dbo].[Disponibilite] ([IDDisponibilite])
GO
ALTER TABLE [dbo].[Inscription] CHECK CONSTRAINT [FK_Rencontre_Disponibilite]
GO
ALTER TABLE [dbo].[Inscription]  WITH CHECK ADD  CONSTRAINT [FK_Rencontre_Eleve] FOREIGN KEY([IDEleve])
REFERENCES [dbo].[Eleve] ([IDEleve])
GO
ALTER TABLE [dbo].[Inscription] CHECK CONSTRAINT [FK_Rencontre_Eleve]
GO
ALTER TABLE [dbo].[Notification]  WITH CHECK ADD  CONSTRAINT [FK_Notification_Evenement] FOREIGN KEY([IDEvenement])
REFERENCES [dbo].[Evenement] ([IDEvenement])
GO
ALTER TABLE [dbo].[Notification] CHECK CONSTRAINT [FK_Notification_Evenement]
GO
ALTER TABLE [dbo].[Notification]  WITH CHECK ADD  CONSTRAINT [FK_Notification_Usager] FOREIGN KEY([IDUserEnvoie])
REFERENCES [dbo].[Usager] ([IDUsager])
GO
ALTER TABLE [dbo].[Notification] CHECK CONSTRAINT [FK_Notification_Usager]
GO
ALTER TABLE [dbo].[NotificationEleve]  WITH CHECK ADD  CONSTRAINT [FK_NotificationEleve_Notification] FOREIGN KEY([IDEleve])
REFERENCES [dbo].[Eleve] ([IDEleve])
GO
ALTER TABLE [dbo].[NotificationEleve] CHECK CONSTRAINT [FK_NotificationEleve_Notification]
GO
ALTER TABLE [dbo].[NotificationEleve]  WITH CHECK ADD  CONSTRAINT [FK_NotificationEleve_Notification1] FOREIGN KEY([IDNotification])
REFERENCES [dbo].[Notification] ([IDNotification])
GO
ALTER TABLE [dbo].[NotificationEleve] CHECK CONSTRAINT [FK_NotificationEleve_Notification1]
GO
ALTER TABLE [dbo].[Programme]  WITH CHECK ADD  CONSTRAINT [FK_Programme_TypeFormation] FOREIGN KEY([IDTypeFormation])
REFERENCES [dbo].[TypeFormation] ([IDTypeProgramme])
GO
ALTER TABLE [dbo].[Programme] CHECK CONSTRAINT [FK_Programme_TypeFormation]
GO
