USE [Shire]
GO
/****** Object:  StoredProcedure [dbo].[CreateTables]    Script Date: 02/02/2008 23:08:33 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author:		<Author,,Name>
-- Create date: <Create Date,,>
-- Description:	<Description,,>
-- =============================================
ALTER PROCEDURE [dbo].[CreateTables]
	-- Add the parameters for the stored procedure here
	@database_name varchar(MAX)
AS
BEGIN
	DECLARE @name varchar(MAX)
	DECLARE @courses_prefix varchar(100)
	DECLARE @subjects_prefix varchar(100)
	DECLARE @tutors_prefix varchar(100)
	DECLARE @students_prefix varchar(100)
	DECLARE @tutorsubjects_prefix varchar(100)
	DECLARE @studentsubjects_prefix varchar(100)
	
	SET @name=(SELECT DepartmentGUID from edu_departments where DepartmentId=@database_name);
	SET @courses_prefix='['+@name + ']'+'.[dbo].[courses]'
	SET @subjects_prefix='['+@name + ']'+'.[dbo].[subjects]'
	SET @tutors_prefix='['+@name + ']'+'.[dbo].[tutors]'
	SET @students_prefix='['+@name + ']'+'.[dbo].[students]'
	SET @tutorsubjects_prefix='['+@name + ']'+'.[dbo].[tutorsubjects]'
	SET @studentsubjects_prefix='['+@name + ']'+'.[dbo].[studentsubjects]'

	--Courses
	exec('CREATE TABLE '+@courses_prefix+'(
		[Id] [varchar](100) NOT NULL,
		[Name] [varchar](300) NOT NULL,
		[Contact] [varchar](100) NULL,
		[Timestamp] [datetime] NOT NULL CONSTRAINT [DF_courses_Timestamp]  DEFAULT (getdate()),
	 CONSTRAINT [PK_courses_1] PRIMARY KEY CLUSTERED 
	(
		[Id] ASC
	)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
	) ON [PRIMARY]')

	--Subjects
	exec('CREATE TABLE '+@subjects_prefix+'(
		[Id] [varchar](100) NOT NULL,
		[Name] [varchar](300) NOT NULL,
		[Contact] [varchar](100) NULL,
		[Timestamp] [datetime] NOT NULL CONSTRAINT [DF_subjects_Timestamp]  DEFAULT (getdate()),
	 CONSTRAINT [PK_subjects_1] PRIMARY KEY CLUSTERED 
	(
		[Id] ASC
	)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]	
	) ON [PRIMARY]')

	--Tutors
	exec('CREATE TABLE '+@tutors_prefix+'(
	[UserName] [varchar](100) NOT NULL,
	[Name] [varchar](300) NOT NULL,
	[Contact] [varchar](100) NULL,
	[Photo] [image] NULL,
	[Timestamp] [datetime] NOT NULL CONSTRAINT [DF_tutors_Timestamp]  DEFAULT (getdate()),
	 CONSTRAINT [PK_tutors_1] PRIMARY KEY CLUSTERED 
	(
		[UserName] 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]')

	--Students
	exec('CREATE TABLE '+@students_prefix+'(
	[UserName] [varchar](100) NOT NULL,
	[Name] [varchar](300) NOT NULL,
	[Course] [varchar](100) NOT NULL,
	[Contact] [varchar](100) NULL,
	[Photo] [image] NULL,
	[Timestamp] [datetime] NOT NULL CONSTRAINT [DF_students_Timestamp]  DEFAULT (getdate()),
	 CONSTRAINT [PK_students_1] PRIMARY KEY CLUSTERED 
	(
		[UserName] 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]')

	--Tutorsubjects
	exec('CREATE TABLE '+@tutorsubjects_prefix+'(
		[Key] [bigint] IDENTITY(1,1) NOT NULL,
		[Tutor] [varchar](100) NOT NULL,
		[Subject] [varchar](100) NOT NULL,
		[Course] [varchar](100) NOT NULL,
		[SessionName] [varchar](300) NOT NULL,
		[SessionDate] [varchar](100) NOT NULL,
		[SessionTime] [varchar](100) NOT NULL,
		[SessionVenue] [varchar](300) NOT NULL,
		[Timestamp] [datetime] NOT NULL CONSTRAINT [DF_tutorsubjects_Timestamp]  DEFAULT (getdate()),
	 CONSTRAINT [PK_tutorsubjects] PRIMARY KEY CLUSTERED 
	(
		[Key] ASC
	)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY],
	CONSTRAINT [IX_tutorsubjects] UNIQUE NONCLUSTERED 
	(
		[Tutor] ASC,
		[Subject] ASC,
		[SessionName] ASC
	)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
	) ON [PRIMARY]')

	--Studentsubjects
	exec('CREATE TABLE '+@studentsubjects_prefix+'(
		[Key] [bigint] IDENTITY(1,1) NOT NULL,
		[Student] [varchar](100) NOT NULL,
		[Session] [bigint] NOT NULL,
		[Timestamp] [datetime] NOT NULL CONSTRAINT [DF_studentsubjects_Timestamp]  DEFAULT (getdate()),
	 CONSTRAINT [PK_studentsubjects] PRIMARY KEY CLUSTERED 
	(
		[Key] ASC
	)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
	) ON [PRIMARY]')
END