Skip to content
Permalink
Branch: master
Find file Copy path
Find file Copy path
Fetching contributors…
Cannot retrieve contributors at this time
executable file 358 lines (358 sloc) 26.9 KB
USE [openrams]
GO
/****** Object: Table [dbo].[tblWorkCategories] Script Date: 04/18/2013 13:35:57 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[tblWorkCategories](
[txtWorkCategory] [varchar](20) NOT NULL,
CONSTRAINT [pkWorkCategories] PRIMARY KEY CLUSTERED
(
[txtWorkCategory] 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_PADDING OFF
GO
/****** Object: Table [dbo].[tblUsers] Script Date: 04/18/2013 13:35:57 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[tblUsers](
[txtUserName] [varchar](20) NOT NULL,
[txtPassword] [varchar](30) NULL,
[txtRoles] [varchar](64) NOT NULL,
[txtFirstName] [varchar](30) NULL,
[txtSecondName] [varchar](40) NULL,
[txtEmailAddress] [varchar](40) NOT NULL,
[txtJobTitle] [varchar](30) NULL,
[txtDepartment] [varchar](50) NULL,
[txtColourCode] [varchar](7) NULL,
[dtTimeStamp] [datetime] NOT NULL,
CONSTRAINT [pkUsers] PRIMARY KEY CLUSTERED
(
[txtUserName] 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_PADDING OFF
GO
/****** Object: Table [dbo].[tblRooms] Script Date: 04/18/2013 13:35:57 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[tblRooms](
[txtRoom] [varchar](30) NOT NULL,
[txtDescription] [varchar](150) NULL,
[bitActive] [bit] NULL,
[monResRate] [money] NULL,
[dtTimeStamp] [datetime] NOT NULL,
CONSTRAINT [pkRooms] PRIMARY KEY CLUSTERED
(
[txtRoom] 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_PADDING OFF
GO
/****** Object: Table [dbo].[tblResources] Script Date: 04/18/2013 13:35:57 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[tblResources](
[txtResource] [varchar](20) NOT NULL,
[txtDescription] [varchar](150) NULL,
[intTotalQuantity] [int] NOT NULL,
[bitActive] [bit] NULL,
[monResRate] [money] NULL,
[dtTimeStamp] [datetime] NOT NULL,
CONSTRAINT [pkResources] PRIMARY KEY CLUSTERED
(
[txtResource] 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_PADDING OFF
GO
/****** Object: Table [dbo].[tblClients] Script Date: 04/18/2013 13:35:57 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[tblClients](
[txtClientName] [varchar](50) NOT NULL,
[txtOrganisation] [varchar](30) NULL,
[txtAddressFirstLine] [varchar](30) NULL,
[txtAddressSecondLine] [varchar](30) NULL,
[txtAddressThirdLine] [varchar](30) NULL,
[txtAddressPostCode] [varchar](8) NULL,
[txtLandLinePhoneNo] [varchar](15) NULL,
[txtMobilePhoneNo] [varchar](15) NULL,
[txtEmailAddress] [varchar](40) NULL,
[txtJobTitle] [varchar](30) NULL,
[txtDepartment] [varchar](50) NULL,
[dtTimeStamp] [datetime] NOT NULL,
CONSTRAINT [pkClients] PRIMARY KEY CLUSTERED
(
[txtClientName] 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_PADDING OFF
GO
/****** Object: Table [dbo].[tblBookings] Script Date: 04/18/2013 13:35:57 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[tblBookings](
[intBookingID] [bigint] NOT NULL,
[txtAssignedUser] [varchar](20) NOT NULL,
[txtClientName] [varchar](50) NULL,
[txtUserName] [varchar](20) NOT NULL,
[dtBookingStart] [datetime] NOT NULL,
[dtBookingEnd] [datetime] NOT NULL,
[txtTitle] [varchar](20) NOT NULL,
[txtDescription] [varchar](150) NULL,
[bitApproved] [bit] NULL,
[bitActive] [bit] NULL,
[txtStatus] [varchar](20) NULL,
[bitRecurring] [bit] NULL,
[txtRecurInterval] [varchar](50) NULL,
[bitChargeable] [bit] NULL,
[monAmount] [money] NULL,
[dtTimeStamp] [datetime] NOT NULL,
CONSTRAINT [pkBookings] PRIMARY KEY CLUSTERED
(
[intBookingID] 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_PADDING OFF
GO
/****** Object: Table [dbo].[tblWorkTickets] Script Date: 04/18/2013 13:35:57 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[tblWorkTickets](
[intWorkTicketID] [bigint] NOT NULL,
[txtUsername] [varchar](20) NOT NULL,
[txtRoom] [varchar](30) NULL,
[txtAllocatedTo] [varchar](20) NULL,
[txtTitle] [varchar](20) NULL,
[txtDescription] [varchar](150) NULL,
[bitApproved] [bit] NULL,
[bitActive] [bit] NULL,
[dtTimeStamp] [datetime] NOT NULL,
CONSTRAINT [pkWorkTickets] PRIMARY KEY CLUSTERED
(
[intWorkTicketID] 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_PADDING OFF
GO
/****** Object: Table [dbo].[tblWorkTicketLog] Script Date: 04/18/2013 13:35:57 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[tblWorkTicketLog](
[intWorkTicketID] [bigint] NOT NULL,
[dtTimeStamp] [datetime] NOT NULL,
[txtWorkCategory] [varchar](20) NULL,
[txtUserName] [varchar](20) NOT NULL,
[txtTitle] [varchar](20) NULL,
[txtDescription] [varchar](150) NULL,
[intNumHours] [int] NOT NULL,
CONSTRAINT [pkWorkTicketLogs] PRIMARY KEY CLUSTERED
(
[intWorkTicketID] ASC,
[dtTimeStamp] 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_PADDING OFF
GO
/****** Object: Table [dbo].[tblResourceRequests] Script Date: 04/18/2013 13:35:57 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[tblResourceRequests](
[intBookingID] [bigint] NOT NULL,
[txtResource] [varchar](20) NOT NULL,
[intRequestQuantity] [int] NULL,
[bitApproved] [bit] NULL,
[dtTimeStamp] [datetime] NOT NULL,
CONSTRAINT [pkResourceRequests] PRIMARY KEY CLUSTERED
(
[intBookingID] ASC,
[txtResource] 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_PADDING OFF
GO
/****** Object: Table [dbo].[tblBookingNotes] Script Date: 04/18/2013 13:35:57 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[tblBookingNotes](
[intBookingID] [bigint] NOT NULL,
[txtUserName] [varchar](20) NOT NULL,
[txtBookingNote] [varchar](150) NULL,
[dtTimeStamp] [datetime] NOT NULL,
CONSTRAINT [pkBookingNotes] PRIMARY KEY CLUSTERED
(
[intBookingID] ASC,
[txtUserName] 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_PADDING OFF
GO
/****** Object: Table [dbo].[tblRoomRequests] Script Date: 04/18/2013 13:35:57 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[tblRoomRequests](
[intBookingID] [bigint] NOT NULL,
[txtRoom] [varchar](30) NOT NULL,
[txtUserName] [varchar](20) NOT NULL,
[bitApproved] [bit] NULL,
[dtTimeStamp] [datetime] NOT NULL,
CONSTRAINT [pkRoomRequests] PRIMARY KEY CLUSTERED
(
[intBookingID] ASC,
[txtRoom] 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_PADDING OFF
GO
/****** Object: ForeignKey [fkBookingNoteBookingLink] Script Date: 04/18/2013 13:35:57 ******/
ALTER TABLE [dbo].[tblBookingNotes] WITH CHECK ADD CONSTRAINT [fkBookingNoteBookingLink] FOREIGN KEY([intBookingID])
REFERENCES [dbo].[tblBookings] ([intBookingID])
GO
ALTER TABLE [dbo].[tblBookingNotes] CHECK CONSTRAINT [fkBookingNoteBookingLink]
GO
/****** Object: ForeignKey [fkBookingNoteCreatorLink] Script Date: 04/18/2013 13:35:57 ******/
ALTER TABLE [dbo].[tblBookingNotes] WITH CHECK ADD CONSTRAINT [fkBookingNoteCreatorLink] FOREIGN KEY([txtUserName])
REFERENCES [dbo].[tblUsers] ([txtUserName])
GO
ALTER TABLE [dbo].[tblBookingNotes] CHECK CONSTRAINT [fkBookingNoteCreatorLink]
GO
/****** Object: ForeignKey [fkBookingAssignedTo] Script Date: 04/18/2013 13:35:57 ******/
ALTER TABLE [dbo].[tblBookings] WITH CHECK ADD CONSTRAINT [fkBookingAssignedTo] FOREIGN KEY([txtUserName])
REFERENCES [dbo].[tblUsers] ([txtUserName])
GO
ALTER TABLE [dbo].[tblBookings] CHECK CONSTRAINT [fkBookingAssignedTo]
GO
/****** Object: ForeignKey [fkBookingClient] Script Date: 04/18/2013 13:35:57 ******/
ALTER TABLE [dbo].[tblBookings] WITH CHECK ADD CONSTRAINT [fkBookingClient] FOREIGN KEY([txtClientName])
REFERENCES [dbo].[tblClients] ([txtClientName])
GO
ALTER TABLE [dbo].[tblBookings] CHECK CONSTRAINT [fkBookingClient]
GO
/****** Object: ForeignKey [fkBookingCreator] Script Date: 04/18/2013 13:35:57 ******/
ALTER TABLE [dbo].[tblBookings] WITH CHECK ADD CONSTRAINT [fkBookingCreator] FOREIGN KEY([txtAssignedUser])
REFERENCES [dbo].[tblUsers] ([txtUserName])
GO
ALTER TABLE [dbo].[tblBookings] CHECK CONSTRAINT [fkBookingCreator]
GO
/****** Object: ForeignKey [fkResourceRequestBookingLink] Script Date: 04/18/2013 13:35:57 ******/
ALTER TABLE [dbo].[tblResourceRequests] WITH CHECK ADD CONSTRAINT [fkResourceRequestBookingLink] FOREIGN KEY([intBookingID])
REFERENCES [dbo].[tblBookings] ([intBookingID])
GO
ALTER TABLE [dbo].[tblResourceRequests] CHECK CONSTRAINT [fkResourceRequestBookingLink]
GO
/****** Object: ForeignKey [fkResourceRequestResourceLink] Script Date: 04/18/2013 13:35:57 ******/
ALTER TABLE [dbo].[tblResourceRequests] WITH CHECK ADD CONSTRAINT [fkResourceRequestResourceLink] FOREIGN KEY([txtResource])
REFERENCES [dbo].[tblResources] ([txtResource])
GO
ALTER TABLE [dbo].[tblResourceRequests] CHECK CONSTRAINT [fkResourceRequestResourceLink]
GO
/****** Object: ForeignKey [fkRoomRequestBookingLink] Script Date: 04/18/2013 13:35:57 ******/
ALTER TABLE [dbo].[tblRoomRequests] WITH CHECK ADD CONSTRAINT [fkRoomRequestBookingLink] FOREIGN KEY([intBookingID])
REFERENCES [dbo].[tblBookings] ([intBookingID])
GO
ALTER TABLE [dbo].[tblRoomRequests] CHECK CONSTRAINT [fkRoomRequestBookingLink]
GO
/****** Object: ForeignKey [fkRoomRequestCreator] Script Date: 04/18/2013 13:35:57 ******/
ALTER TABLE [dbo].[tblRoomRequests] WITH CHECK ADD CONSTRAINT [fkRoomRequestCreator] FOREIGN KEY([txtUserName])
REFERENCES [dbo].[tblUsers] ([txtUserName])
GO
ALTER TABLE [dbo].[tblRoomRequests] CHECK CONSTRAINT [fkRoomRequestCreator]
GO
/****** Object: ForeignKey [fkRoomRequestRoomLink] Script Date: 04/18/2013 13:35:57 ******/
ALTER TABLE [dbo].[tblRoomRequests] WITH CHECK ADD CONSTRAINT [fkRoomRequestRoomLink] FOREIGN KEY([txtRoom])
REFERENCES [dbo].[tblRooms] ([txtRoom])
GO
ALTER TABLE [dbo].[tblRoomRequests] CHECK CONSTRAINT [fkRoomRequestRoomLink]
GO
/****** Object: ForeignKey [fkWorkTicketLogCreator] Script Date: 04/18/2013 13:35:57 ******/
ALTER TABLE [dbo].[tblWorkTicketLog] WITH CHECK ADD CONSTRAINT [fkWorkTicketLogCreator] FOREIGN KEY([txtUserName])
REFERENCES [dbo].[tblUsers] ([txtUserName])
GO
ALTER TABLE [dbo].[tblWorkTicketLog] CHECK CONSTRAINT [fkWorkTicketLogCreator]
GO
/****** Object: ForeignKey [fkWorkTicketLogsWorkTicketLink] Script Date: 04/18/2013 13:35:57 ******/
ALTER TABLE [dbo].[tblWorkTicketLog] WITH CHECK ADD CONSTRAINT [fkWorkTicketLogsWorkTicketLink] FOREIGN KEY([intWorkTicketID])
REFERENCES [dbo].[tblWorkTickets] ([intWorkTicketID])
GO
ALTER TABLE [dbo].[tblWorkTicketLog] CHECK CONSTRAINT [fkWorkTicketLogsWorkTicketLink]
GO
/****** Object: ForeignKey [fkWorkTicketLogWorkCategory] Script Date: 04/18/2013 13:35:57 ******/
ALTER TABLE [dbo].[tblWorkTicketLog] WITH CHECK ADD CONSTRAINT [fkWorkTicketLogWorkCategory] FOREIGN KEY([txtWorkCategory])
REFERENCES [dbo].[tblWorkCategories] ([txtWorkCategory])
GO
ALTER TABLE [dbo].[tblWorkTicketLog] CHECK CONSTRAINT [fkWorkTicketLogWorkCategory]
GO
/****** Object: ForeignKey [fkWorkTicketAllocatedTo] Script Date: 04/18/2013 13:35:57 ******/
ALTER TABLE [dbo].[tblWorkTickets] WITH CHECK ADD CONSTRAINT [fkWorkTicketAllocatedTo] FOREIGN KEY([txtAllocatedTo])
REFERENCES [dbo].[tblUsers] ([txtUserName])
GO
ALTER TABLE [dbo].[tblWorkTickets] CHECK CONSTRAINT [fkWorkTicketAllocatedTo]
GO
/****** Object: ForeignKey [fkWorkTicketCreator] Script Date: 04/18/2013 13:35:57 ******/
ALTER TABLE [dbo].[tblWorkTickets] WITH CHECK ADD CONSTRAINT [fkWorkTicketCreator] FOREIGN KEY([txtUsername])
REFERENCES [dbo].[tblUsers] ([txtUserName])
GO
ALTER TABLE [dbo].[tblWorkTickets] CHECK CONSTRAINT [fkWorkTicketCreator]
GO
/****** Object: ForeignKey [fkWorkTicketLocation] Script Date: 04/18/2013 13:35:57 ******/
ALTER TABLE [dbo].[tblWorkTickets] WITH CHECK ADD CONSTRAINT [fkWorkTicketLocation] FOREIGN KEY([txtRoom])
REFERENCES [dbo].[tblRooms] ([txtRoom])
GO
ALTER TABLE [dbo].[tblWorkTickets] CHECK CONSTRAINT [fkWorkTicketLocation]
GO
You can’t perform that action at this time.