USE [iDoc]
GO
/****** Object:  Table [dbo].[Employees]    Script Date: 11/11/2010 19:03:59 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[Employees](
    [UserID] [int] IDENTITY(1,1) NOT NULL,
    [Username] [char](25) NOT NULL,
    [Password] [char](25) NOT NULL,
 CONSTRAINT [PK_User] PRIMARY KEY CLUSTERED
(
    [UserID] 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].[Departments]    Script Date: 11/11/2010 19:03:59 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[Departments](
    [DepartmentID] [smallint] IDENTITY(1,1) NOT NULL,
    [Name] [char](25) NOT NULL,
 CONSTRAINT [PK_Groups] PRIMARY KEY CLUSTERED
(
    [DepartmentID] 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].[Positions]    Script Date: 11/11/2010 19:03:59 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[Positions](
    [PositionID] [smallint] IDENTITY(1,1) NOT NULL,
    [Name] [char](25) NOT NULL,
 CONSTRAINT [PK_Positions] PRIMARY KEY CLUSTERED
(
    [PositionID] 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].[Path]    Script Date: 11/11/2010 19:03:59 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[Path](
    [PathID] [int] IDENTITY(1,1) NOT NULL,
    [Name] [char](256) NOT NULL,
    [Created] [datetime] NOT NULL,
 CONSTRAINT [PK_Path] PRIMARY KEY CLUSTERED
(
    [PathID] 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].[Steps]    Script Date: 11/11/2010 19:03:59 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[Steps](
    [PathID] [int] IDENTITY(1,1) NOT NULL,
    [Step] [smallint] NOT NULL,
    [Rights] [smallint] NOT NULL,
    [Department] [smallint] NOT NULL
) ON [PRIMARY]
GO
/****** Object:  StoredProcedure [dbo].[getID]    Script Date: 11/11/2010 19:04:04 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[getID]

@Username char(25),
@Password char(25)

AS
BEGIN

    SET NOCOUNT ON;


    SELECT UserID FROM Employees u where ((u.Username=@Username) and (u.Password=@Password))
END
GO
/****** Object:  Table [dbo].[Employees_Details]    Script Date: 11/11/2010 19:04:04 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[Employees_Details](
    [UserID] [int] NOT NULL,
    [Name] [char](25) NOT NULL,
    [Surname] [char](25) NOT NULL,
    [PositionID] [smallint] NOT NULL,
    [DepartmentID] [smallint] NOT NULL,
 CONSTRAINT [PK_User_Details] PRIMARY KEY CLUSTERED
(
    [UserID] 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].[Documents]    Script Date: 11/11/2010 19:04:04 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING OFF
GO
CREATE TABLE [dbo].[Documents](
    [DocumentID] [int] IDENTITY(1,1) NOT NULL,
    [AuthorID] [int] NOT NULL,
    [DestinationID] [int] NOT NULL,
    [Submitted] [datetime] NOT NULL,
    [Attachment] [varbinary](max) NOT NULL,
 CONSTRAINT [PK_Documents] PRIMARY KEY CLUSTERED
(
    [DocumentID] 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].[Documents_ Histories]    Script Date: 11/11/2010 19:04:04 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[Documents_ Histories](
    [DocumentID] [int] NOT NULL,
    [Step] [smallint] NOT NULL,
    [Decision] [smallint] NOT NULL,
    [UserID] [int] NOT NULL,
    [Decision_date] [datetime] NOT NULL,
    [Notice] [char](256) NULL
) ON [PRIMARY]
GO
SET ANSI_PADDING OFF
GO
/****** Object:  StoredProcedure [dbo].[Add_Document]    Script Date: 11/11/2010 19:04:04 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[Add_Document]
@AuthorID int,
@Username char(25),
@Password char(25),
@PathID int,
@Attachment varbinary(max)



AS


BEGIN

INSERT INTO Documents
(AuthorID,
PathID,
Attachment)

VALUES
(@AuthorID,
@PathID,
@Attachment)

END;
GO
/****** Object:  ForeignKey [FK_Steps_Path]    Script Date: 11/11/2010 19:03:59 ******/
ALTER TABLE [dbo].[Steps]  WITH CHECK ADD  CONSTRAINT [FK_Steps_Path] FOREIGN KEY([PathID])
REFERENCES [dbo].[Path] ([PathID])
GO
ALTER TABLE [dbo].[Steps] CHECK CONSTRAINT [FK_Steps_Path]
GO
/****** Object:  ForeignKey [FK_Employees_Details_Employees]    Script Date: 11/11/2010 19:04:04 ******/
ALTER TABLE [dbo].[Employees_Details]  WITH CHECK ADD  CONSTRAINT [FK_Employees_Details_Employees] FOREIGN KEY([UserID])
REFERENCES [dbo].[Employees] ([UserID])
GO
ALTER TABLE [dbo].[Employees_Details] CHECK CONSTRAINT [FK_Employees_Details_Employees]
GO
/****** Object:  ForeignKey [FK_Employees_Details_Groups]    Script Date: 11/11/2010 19:04:04 ******/
ALTER TABLE [dbo].[Employees_Details]  WITH CHECK ADD  CONSTRAINT [FK_Employees_Details_Groups] FOREIGN KEY([DepartmentID])
REFERENCES [dbo].[Departments] ([DepartmentID])
GO
ALTER TABLE [dbo].[Employees_Details] CHECK CONSTRAINT [FK_Employees_Details_Groups]
GO
/****** Object:  ForeignKey [FK_Employees_Details_Positions]    Script Date: 11/11/2010 19:04:04 ******/
ALTER TABLE [dbo].[Employees_Details]  WITH CHECK ADD  CONSTRAINT [FK_Employees_Details_Positions] FOREIGN KEY([PositionID])
REFERENCES [dbo].[Positions] ([PositionID])
GO
ALTER TABLE [dbo].[Employees_Details] CHECK CONSTRAINT [FK_Employees_Details_Positions]
GO
/****** Object:  ForeignKey [FK_Documents_Employees]    Script Date: 11/11/2010 19:04:04 ******/
ALTER TABLE [dbo].[Documents]  WITH CHECK ADD  CONSTRAINT [FK_Documents_Employees] FOREIGN KEY([AuthorID])
REFERENCES [dbo].[Employees] ([UserID])
GO
ALTER TABLE [dbo].[Documents] CHECK CONSTRAINT [FK_Documents_Employees]
GO
/****** Object:  ForeignKey [FK_Documents_Path]    Script Date: 11/11/2010 19:04:04 ******/
ALTER TABLE [dbo].[Documents]  WITH CHECK ADD  CONSTRAINT [FK_Documents_Path] FOREIGN KEY([DestinationID])
REFERENCES [dbo].[Path] ([PathID])
GO
ALTER TABLE [dbo].[Documents] CHECK CONSTRAINT [FK_Documents_Path]
GO
/****** Object:  ForeignKey [FK_Documents_ Histories_Documents]    Script Date: 11/11/2010 19:04:04 ******/
ALTER TABLE [dbo].[Documents_ Histories]  WITH CHECK ADD  CONSTRAINT [FK_Documents_ Histories_Documents] FOREIGN KEY([DocumentID])
REFERENCES [dbo].[Documents] ([DocumentID])
GO
ALTER TABLE [dbo].[Documents_ Histories] CHECK CONSTRAINT [FK_Documents_ Histories_Documents]
GO