USE [SDABE]
GO
/****** Object: Table [dbo].[tblBookableEquipment] Script Date: 28/12/2015 21:06:50 ******/
--create new table if it doesn't exist
IF NOT EXISTS (
SELECT * FROM sys.objects
WHERE object_id = OBJECT_ID(N'[dbo].[tblBookableEquipment]') AND type in (N'U')
)
BEGIN
SET ANSI_NULLS ON
SET QUOTED_IDENTIFIER ON
SET ANSI_PADDING ON
CREATE TABLE [dbo].[tblBookableEquipment](
[ItemID] [int] IDENTITY(1,1) NOT NULL,
[ItemName] [varchar](20) NULL,
[Description] [varchar](30) NULL,
[Bookable] [bit] NOT NULL,
[Info] [text] NULL,
CONSTRAINT [PK_tblBookableEquipment] PRIMARY KEY CLUSTERED
(
[ItemID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
SET ANSI_PADDING OFF
ALTER TABLE [dbo].[tblBookableEquipment] ADD CONSTRAINT [DF_tblBookableEquipment_Bookable] DEFAULT ((0)) FOR [Bookable]
END
GO
/****** Object: Table [dbo].[tblEquipmentBookings] Script Date: 28/12/2015 21:06:50 ******/
--create new table if it doesn't exist
IF NOT EXISTS (
SELECT * FROM sys.objects
WHERE object_id = OBJECT_ID(N'[dbo].[tblEquipmentBookings]') AND type in (N'U')
)
BEGIN
SET ANSI_NULLS ON
SET QUOTED_IDENTIFIER ON
SET ANSI_PADDING ON
CREATE TABLE [dbo].[tblEquipmentBookings](
[BookingID] [int] IDENTITY(1,1) NOT NULL,
[ItemID] [int] NOT NULL,
[ItemName] [varchar](20) NULL,
[BookingDate] [datetime] NULL,
[LessonID] [int] NULL,
[Period] [int] NULL,
[StartTime] [datetime] NULL,
[EndTime] [datetime] NULL,
[AllDay] [bit] NOT NULL,
[TeacherID] [varchar](5) NULL,
[ClassID] [varchar](10) NULL,
[RoomID] [varchar](10) NULL,
[SubjShort] [varchar](10) NULL,
[Activity] [varchar](30) NULL,
CONSTRAINT [PK_tblEquipmentBookings] PRIMARY KEY CLUSTERED
(
[BookingID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
SET ANSI_PADDING OFF
--add referential integrity
ALTER TABLE [dbo].[tblEquipmentBookings] ADD CONSTRAINT [DF_tblEquipmentBookings_AllDay] DEFAULT ((0)) FOR [AllDay]
ALTER TABLE [dbo].[tblEquipmentBookings] WITH CHECK ADD CONSTRAINT [FK_tblEquipmentBookings_tblBookableEquipment] FOREIGN KEY([ItemID])
REFERENCES [dbo].[tblBookableEquipment] ([ItemID])
ON UPDATE CASCADE
ON DELETE CASCADE
ALTER TABLE [dbo].[tblEquipmentBookings] CHECK CONSTRAINT [FK_tblEquipmentBookings_tblBookableEquipment]
END
GO
/****** Object: Table [dbo].[tblBookableRooms] Script Date: 28/12/2015 21:32:27 ******/
--create new table if it doesn't exist
IF NOT EXISTS (
SELECT * FROM sys.objects
WHERE object_id = OBJECT_ID(N'[dbo].[tblBookableRooms]') AND type in (N'U')
)
BEGIN
SET ANSI_NULLS ON
SET QUOTED_IDENTIFIER ON
SET ANSI_PADDING ON
CREATE TABLE [dbo].[tblBookableRooms](
[RoomID] [varchar](10) NOT NULL,
[RoomDescription] [varchar](50) NULL,
[ITRoom] [bit] NOT NULL,
[TimetabledRoom] [bit] NOT NULL,
[Bookable] [bit] NOT NULL,
[RoomInfo] [text] NULL,
CONSTRAINT [PK_tblBookableRooms] PRIMARY KEY CLUSTERED
(
[RoomID] 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]
SET ANSI_PADDING OFF
ALTER TABLE [dbo].[tblBookableRooms] ADD CONSTRAINT [DF_tblBookableRooms_ITRoom] DEFAULT ((0)) FOR [ITRoom]
ALTER TABLE [dbo].[tblBookableRooms] ADD CONSTRAINT [DF_tblBookableRooms_TimetabledRoom] DEFAULT ((0)) FOR [TimetabledRoom]
ALTER TABLE [dbo].[tblBookableRooms] ADD CONSTRAINT [DF_tblBookableRooms_Bookable] DEFAULT ((0)) FOR [Bookable]
END
GO
/****** Object: Table [dbo].[tblRoomBookings] Script Date: 28/12/2015 21:40:09 ******/
--create new table if it doesn't exist
IF NOT EXISTS (
SELECT * FROM sys.objects
WHERE object_id = OBJECT_ID(N'[dbo].[tblRoomBookings]') AND type in (N'U')
)
BEGIN
SET ANSI_NULLS ON
SET QUOTED_IDENTIFIER ON
SET ANSI_PADDING ON
CREATE TABLE [dbo].[tblRoomBookings](
[ID] [int] IDENTITY(1,1) NOT NULL,
[RoomID] [varchar](10) NOT NULL,
[Timetabledroom] [bit] NOT NULL,
[BookingDate] [datetime] NULL,
[LessonID] [int] NULL,
[Period] [int] NULL,
[StartTime] [datetime] NULL,
[EndTime] [datetime] NULL,
[AllDay] [bit] NOT NULL,
[TeacherID] [varchar](5) NULL,
[ClassID] [varchar](10) NULL,
[SubjShort] [varchar](10) NULL,
[Activity] [varchar](30) NULL,
CONSTRAINT [PK_tblRoomBookings] 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]
SET ANSI_PADDING OFF
ALTER TABLE [dbo].[tblRoomBookings] ADD CONSTRAINT [DF_tblRoomBookings_AllDay1] DEFAULT ((0)) FOR [Timetabledroom]
ALTER TABLE [dbo].[tblRoomBookings] ADD CONSTRAINT [DF_tblRoomBookings_AllDay] DEFAULT ((0)) FOR [AllDay]
--add referential integrity
ALTER TABLE [dbo].[tblRoomBookings] WITH CHECK ADD CONSTRAINT [FK_tblRoomBookings_tblBookableRooms] FOREIGN KEY([RoomID])
REFERENCES [dbo].[tblBookableRooms] ([RoomID])
ON UPDATE CASCADE
ON DELETE CASCADE
ALTER TABLE [dbo].[tblRoomBookings] CHECK CONSTRAINT [FK_tblRoomBookings_tblBookableRooms]
END
GO