ÿþUSE [master]

GO



EXEC msdb.dbo.sp_delete_database_backuphistory @database_name = N'adr;'

GO



IF  EXISTS (SELECT name FROM sys.databases WHERE name = N'adr')

DROP DATABASE [adr]

GO



IF  EXISTS (SELECT * FROM sys.server_principals WHERE name = N'adr')

DROP LOGIN [adr]

GO



CREATE DATABASE [adr] ON  PRIMARY 

( NAME = N'adr', FILENAME = N'D:\DB\ADR\DATA\adr.mdf' , SIZE = 3072KB , MAXSIZE = UNLIMITED, FILEGROWTH = 1024KB )

 LOG ON 

( NAME = N'adr_log', FILENAME = N'D:\DB\ADR\DATA\adr_log.ldf' , SIZE = 1280KB , MAXSIZE = 2048GB , FILEGROWTH = 10%)

GO



IF  EXISTS (SELECT * FROM sys.server_principals WHERE name = N'adr')

DROP LOGIN [adr]

GO



CREATE LOGIN [adr] WITH PASSWORD=N'ZMIEN_HASLO', DEFAULT_DATABASE=[adr], DEFAULT_LANGUAGE=[us_english], CHECK_EXPIRATION=OFF, CHECK_POLICY=OFF

GO







USE [adr]

GO





USE [master]

GO



/****** Object:  Login [adr]    Script Date: 02/07/2012 12:09:48 ******/

DROP LOGIN [adr]

GO































































































































































/****** Object:  Login [adr]    Script Date: 02/07/2012 12:09:48 ******/



CREATE LOGIN [adr] WITH PASSWORD=N'ZMIEN HASA
O', DEFAULT_DATABASE=[adr], DEFAULT_LANGUAGE=[us_english], CHECK_EXPIRATION=OFF, CHECK_POLICY=OFF

GO





USE [adr]

GO

/****** Object:  User [adr]    Script Date: 02/07/2012 12:09:47 ******/

CREATE USER [adr] FOR LOGIN [adr] WITH DEFAULT_SCHEMA=[dbo]

GO

/****** Object:  Table [dbo].[Osoba]    Script Date: 02/07/2012 12:09:46 ******/

SET ANSI_NULLS ON

GO

SET QUOTED_IDENTIFIER ON

GO

CREATE TABLE [dbo].[Osoba](

	[Id] [int] IDENTITY(1,1) NOT NULL,

	[str2_nazwisko] [nvarchar](50) NULL,

	[str2_imiona] [nvarchar](50) NOT NULL,

	[str2_data_urodzenia] [datetime] NOT NULL,

	[pesel] [nvarchar](11) NOT NULL,

	[adres_zamieszkannia] [nvarchar](500) NULL,

	[str2_narodowosc] [nvarchar](50) NULL,

	[typ_dokumentu] [nvarchar](50) NULL,

	[zrodlo_danych] [nvarchar](500) NULL DEFAULT (N'Od osoby, której dane dotycz
'),

	[zgoda_na_przetwarzanie] [bit] NOT NULL DEFAULT ('true'),

 CONSTRAINT [PK__Egzamin] PRIMARY KEY CLUSTERED 

(

	[Id] ASC

)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON, FILLFACTOR = 100) ON [PRIMARY]

) ON [PRIMARY]

GO



/****** Object:  Table [dbo].[Import]    Script Date: 02/07/2012 12:09:46 ******/

SET ANSI_NULLS ON

GO

SET QUOTED_IDENTIFIER ON

GO

SET ANSI_PADDING ON

GO

CREATE TABLE [dbo].[Import](

	[Id] [int] IDENTITY(1,1) NOT NULL,

	[NazwaPliku] [nvarchar](300) NOT NULL,

	[Skrot] [varbinary](max) NOT NULL,

	[DataImportu] [datetime] NOT NULL,

 CONSTRAINT [PK_Import] 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]

GO

SET ANSI_PADDING OFF

GO

CREATE NONCLUSTERED INDEX [IX_Import_NazwaPliku] ON [dbo].[Import] 

(

	[NazwaPliku] ASC

)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]

GO

/****** Object:  Table [dbo].[Status]    Script Date: 02/07/2012 12:09:47 ******/

SET ANSI_NULLS ON

GO

SET QUOTED_IDENTIFIER ON

GO

CREATE TABLE [dbo].[Status](

	[Id] [int] NOT NULL,

	[Nazwa] [nvarchar](50) NOT NULL,

	[Pozycja] [int] NULL,

 CONSTRAINT [PK_StatusWniosku] 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]

GO

INSERT [dbo].[Status] ([Id], [Nazwa], [Pozycja]) VALUES (1, N'Wprowadzone', 1)

INSERT [dbo].[Status] ([Id], [Nazwa], [Pozycja]) VALUES (2, N'Wydrukowane', 2)

/****** Object:  Table [dbo].[Sequence]    Script Date: 02/07/2012 12:09:46 ******/

SET ANSI_NULLS ON

GO

SET QUOTED_IDENTIFIER ON

GO

CREATE TABLE [dbo].[Sequence](

	[Name] [nvarchar](50) NOT NULL,

	[Value] [int] NOT NULL,

 CONSTRAINT [PK_dbo_Sequence] PRIMARY KEY CLUSTERED 

(

	[Name] ASC

)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON, FILLFACTOR = 100) ON [PRIMARY]

) ON [PRIMARY]

GO

INSERT [dbo].[Sequence] ([Name], [Value]) VALUES (N'NumerZaswiadczenia', 5)

/****** Object:  Table [dbo].[Rola]    Script Date: 02/07/2012 12:09:46 ******/

SET ANSI_NULLS ON

GO

SET QUOTED_IDENTIFIER ON

GO

CREATE TABLE [dbo].[Rola](

	[Id] [int] IDENTITY(1,1) NOT NULL,

	[Nazwa_roli] [nvarchar](50) NOT NULL,

	[Nazwa_kontrolki] [nvarchar](50) NOT NULL,

 CONSTRAINT [PK_Role] 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]

GO

SET IDENTITY_INSERT [dbo].[Rola] ON

INSERT [dbo].[Rola] ([Id], [Nazwa_roli], [Nazwa_kontrolki]) VALUES (1, N'Edycja za[
wiadczenia', N'edytujWnosekToolStripMenuItem')

INSERT [dbo].[Rola] ([Id], [Nazwa_roli], [Nazwa_kontrolki]) VALUES (2, N'Dodawanie za[
wiadczenia', N'dodajNowyToolStripMenuItem')

INSERT [dbo].[Rola] ([Id], [Nazwa_roli], [Nazwa_kontrolki]) VALUES (3, N'Wydruk upowaznieD
', N'drukujWybraneToolStripMenuItem')

INSERT [dbo].[Rola] ([Id], [Nazwa_roli], [Nazwa_kontrolki]) VALUES (5, N'Wydruk próbny', N'wydrukPróbnyToolStripMenuItem')

INSERT [dbo].[Rola] ([Id], [Nazwa_roli], [Nazwa_kontrolki]) VALUES (7, N'Ustawianie przesuni
cia', N'ustawieniePrzesuni
ciaToolStripMenuItem')

INSERT [dbo].[Rola] ([Id], [Nazwa_roli], [Nazwa_kontrolki]) VALUES (10, N'Wyszukiwanie', N'buttonSearch')

INSERT [dbo].[Rola] ([Id], [Nazwa_roli], [Nazwa_kontrolki]) VALUES (13, N'Dodawanie u|
ytkowników', N'dodajToolStripMenuItem')

INSERT [dbo].[Rola] ([Id], [Nazwa_roli], [Nazwa_kontrolki]) VALUES (14, N'Edytowanie u|
ytkowników', N'edytujToolStripMenuItem')

INSERT [dbo].[Rola] ([Id], [Nazwa_roli], [Nazwa_kontrolki]) VALUES (15, N'Konfiguracja programu', N'konfiguracjaToolStripMenuItem')

INSERT [dbo].[Rola] ([Id], [Nazwa_roli], [Nazwa_kontrolki]) VALUES (17, N'Rejestracja udost
pnienia danych', N'buttonUdostepDanych')

INSERT [dbo].[Rola] ([Id], [Nazwa_roli], [Nazwa_kontrolki]) VALUES (18, N'Raport dot. osoby', N'raportZUzytkownikow')

INSERT [dbo].[Rola] ([Id], [Nazwa_roli], [Nazwa_kontrolki]) VALUES (19, N'Raport zbiorczy o udost
pnieniu danych', N'raportZbiorczyUdostepToolStripMenuItem')

INSERT [dbo].[Rola] ([Id], [Nazwa_roli], [Nazwa_kontrolki]) VALUES (20, N'Raport o u|
ytkownikach', N'historiaU|
ytkownikaToolStripMenuItem')

SET IDENTITY_INSERT [dbo].[Rola] OFF

/****** Object:  Table [dbo].[Uzytkownik]    Script Date: 02/07/2012 12:09:47 ******/

SET ANSI_NULLS ON

GO

SET QUOTED_IDENTIFIER ON

GO

SET ANSI_PADDING ON

GO

CREATE TABLE [dbo].[Uzytkownik](

	[Id] [int] IDENTITY(1,1) NOT NULL,

	[Nazwisko] [nvarchar](100) NOT NULL,

	[Imie] [nvarchar](100) NOT NULL,

	[Login] [nvarchar](50) NOT NULL,

	[Haslo] [varbinary](50) NOT NULL,

	[Data_utworzenia] [datetime] NOT NULL CONSTRAINT [DF_Uzytkownik_Data_utworzenia]  DEFAULT (getdate()),

	[Data_wygasniecia] [datetime] NULL,

	[Zablokowany] [bit] NOT NULL CONSTRAINT [DF_Uzytkownik_Usuniete]  DEFAULT ((0)),

	[WymuszenieZmianyHasla] [bit] NOT NULL CONSTRAINT [DF_Uzytkownik_WymyszenieZmianyHasla]  DEFAULT ((0)),

 CONSTRAINT [PK_uzytkownik] 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],

 CONSTRAINT [IX_Uzytkownik] UNIQUE NONCLUSTERED 

(

	[Login] 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

SET IDENTITY_INSERT [dbo].[Uzytkownik] ON

INSERT [dbo].[Uzytkownik] ([Id], [Nazwisko], [Imie], [Login], [Haslo], [Data_utworzenia], [Data_wygasniecia], [Zablokowany], [WymuszenieZmianyHasla]) VALUES (2, N'Admin', N'Admin', N'admin', 0x86F7E437FAA5A7FCE15D1DDCB9EAEAEA377667B8, CAST(0x00009F0100941545 AS DateTime), CAST(0x0000A00B01057E0D AS DateTime), 0, 0)

SET IDENTITY_INSERT [dbo].[Uzytkownik] OFF

/****** Object:  Table [dbo].[Zaswiadczenie]    Script Date: 02/07/2012 12:09:47 ******/

SET ANSI_NULLS ON

GO

SET QUOTED_IDENTIFIER ON

GO

CREATE TABLE [dbo].[Zaswiadczenie](

	[Id] [int] IDENTITY(1,1) NOT NULL,

	[str2_wydany_przez] [nvarchar](100) NOT NULL,

	[str2_data_wydania] [datetime] NOT NULL,

	[str2_podpis] [nvarchar](100) NULL,

	[str1_do_dnia] [datetime] NOT NULL,

	[str1_intanks_strike] [bit] NOT NULL DEFAULT ((0)),

	[str1_inothers_strike] [bit] NOT NULL DEFAULT ((0)),

	[str1_certificateNb] [nvarchar](50) NULL,

	[str1_intanks_1] [bit] NOT NULL DEFAULT ((0)),

	[str1_intanks_2] [bit] NOT NULL DEFAULT ((0)),

	[str1_intanks_3] [bit] NOT NULL DEFAULT ((0)),

	[str1_intanks_4_1] [bit] NOT NULL DEFAULT ((0)),

	[str1_intanks_4_2] [bit] NOT NULL DEFAULT ((0)),

	[str1_intanks_4_3] [bit] NOT NULL DEFAULT ((0)),

	[str1_intanks_5_1] [bit] NOT NULL DEFAULT ((0)),

	[str1_intanks_5_2] [bit] NOT NULL DEFAULT ((0)),

	[str1_intanks_6_1] [bit] NOT NULL DEFAULT ((0)),

	[str1_intanks_6_2] [bit] NOT NULL DEFAULT ((0)),

	[str1_intanks_7] [bit] NOT NULL DEFAULT ((0)),

	[str1_intanks_8] [bit] NOT NULL DEFAULT ((0)),

	[str1_intanks_9] [bit] NOT NULL DEFAULT ((0)),

	[str1_inother_1] [bit] NOT NULL DEFAULT ((0)),

	[str1_inother_2] [bit] NOT NULL DEFAULT ((0)),

	[str1_inother_3] [bit] NOT NULL DEFAULT ((0)),

	[str1_inother_4_1] [bit] NOT NULL DEFAULT ((0)),

	[str1_inother_4_2] [bit] NOT NULL DEFAULT ((0)),

	[str1_inother_4_3] [bit] NOT NULL DEFAULT ((0)),

	[str1_inother_5_1] [bit] NOT NULL DEFAULT ((0)),

	[str1_inother_5_2] [bit] NOT NULL DEFAULT ((0)),

	[str1_inother_6_1] [bit] NOT NULL DEFAULT ((0)),

	[str1_inother_6_2] [bit] NOT NULL DEFAULT ((0)),

	[str1_inother_7] [bit] NOT NULL DEFAULT ((0)),

	[str1_inother_8] [bit] NOT NULL DEFAULT ((0)),

	[str1_inother_9] [bit] NOT NULL DEFAULT ((0)),

	[str3_intanks_1] [bit] NOT NULL DEFAULT ((0)),

	[str3_intanks_2] [bit] NOT NULL DEFAULT ((0)),

	[str3_intanks_3] [bit] NOT NULL DEFAULT ((0)),

	[str3_intanks_4_1] [bit] NOT NULL DEFAULT ((0)),

	[str3_intanks_4_2] [bit] NOT NULL DEFAULT ((0)),

	[str3_intanks_4_3] [bit] NOT NULL DEFAULT ((0)),

	[str3_intanks_5_1] [bit] NOT NULL DEFAULT ((0)),

	[str3_intanks_5_2] [bit] NOT NULL DEFAULT ((0)),

	[str3_intanks_6_1] [bit] NOT NULL DEFAULT ((0)),

	[str3_intanks_6_2] [bit] NOT NULL DEFAULT ((0)),

	[str3_intanks_7] [bit] NOT NULL DEFAULT ((0)),

	[str3_intanks_8] [bit] NOT NULL DEFAULT ((0)),

	[str3_intanks_9] [bit] NOT NULL DEFAULT ((0)),

	[str3_inother_1] [bit] NOT NULL DEFAULT ((0)),

	[str3_inother_2] [bit] NOT NULL DEFAULT ((0)),

	[str3_inother_3] [bit] NOT NULL DEFAULT ((0)),

	[str3_inother_4_1] [bit] NOT NULL DEFAULT ((0)),

	[str3_inother_4_2] [bit] NOT NULL DEFAULT ((0)),

	[str3_inother_4_3] [bit] NOT NULL DEFAULT ((0)),

	[str3_inother_5_1] [bit] NOT NULL DEFAULT ((0)),

	[str3_inother_5_2] [bit] NOT NULL DEFAULT ((0)),

	[str3_inother_6_1] [bit] NOT NULL DEFAULT ((0)),

	[str3_inother_6_2] [bit] NOT NULL DEFAULT ((0)),

	[str3_inother_7] [bit] NOT NULL DEFAULT ((0)),

	[str3_inother_8] [bit] NOT NULL DEFAULT ((0)),

	[str3_inother_9] [bit] NOT NULL DEFAULT ((0)),

	[str3_intanks_data] [datetime] NULL,

	[str3_inothers_data] [datetime] NULL,

	[Data_utworzenia] [datetime] NOT NULL DEFAULT (getdate()),

	[Status_id] [int] NOT NULL,

	[Data_wplywu] [date] NULL,

	[Uwagi] [nvarchar](max) NULL,

	[wtornik] [bit] NULL DEFAULT ('false'),

	[data_egzaminu] [date] NULL,

	[zakres_uprawnien] [nvarchar](50) NULL,

	[data_waznosci_uprawnien] [datetime] NOT NULL,

	[osobaId] [int] NULL,

	[blankiet] [nvarchar](50) NULL,

	[przedluzenie] [bit] NOT NULL DEFAULT ('false'),

	[przedluzenieZaswId] [int] NULL,

 CONSTRAINT [PK_Zaswiadczenie] PRIMARY KEY CLUSTERED 

(

	[Id] ASC

)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON, FILLFACTOR = 100) ON [PRIMARY]

) ON [PRIMARY]

GO



/****** Object:  Table [dbo].[UzytkownikRola]    Script Date: 02/07/2012 12:09:47 ******/

SET ANSI_NULLS ON

GO

SET QUOTED_IDENTIFIER ON

GO

CREATE TABLE [dbo].[UzytkownikRola](

	[Uzytkownik_id] [int] NOT NULL,

	[Rola_id] [int] NOT NULL,

 CONSTRAINT [PK_UzytkownikRola] PRIMARY KEY CLUSTERED 

(

	[Uzytkownik_id] ASC,

	[Rola_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]

GO

INSERT [dbo].[UzytkownikRola] ([Uzytkownik_id], [Rola_id]) VALUES (2, 1)

INSERT [dbo].[UzytkownikRola] ([Uzytkownik_id], [Rola_id]) VALUES (2, 2)

INSERT [dbo].[UzytkownikRola] ([Uzytkownik_id], [Rola_id]) VALUES (2, 3)

INSERT [dbo].[UzytkownikRola] ([Uzytkownik_id], [Rola_id]) VALUES (2, 5)

INSERT [dbo].[UzytkownikRola] ([Uzytkownik_id], [Rola_id]) VALUES (2, 7)

INSERT [dbo].[UzytkownikRola] ([Uzytkownik_id], [Rola_id]) VALUES (2, 10)

INSERT [dbo].[UzytkownikRola] ([Uzytkownik_id], [Rola_id]) VALUES (2, 13)

INSERT [dbo].[UzytkownikRola] ([Uzytkownik_id], [Rola_id]) VALUES (2, 14)

INSERT [dbo].[UzytkownikRola] ([Uzytkownik_id], [Rola_id]) VALUES (2, 15)

INSERT [dbo].[UzytkownikRola] ([Uzytkownik_id], [Rola_id]) VALUES (2, 17)

INSERT [dbo].[UzytkownikRola] ([Uzytkownik_id], [Rola_id]) VALUES (2, 18)

INSERT [dbo].[UzytkownikRola] ([Uzytkownik_id], [Rola_id]) VALUES (2, 19)

INSERT [dbo].[UzytkownikRola] ([Uzytkownik_id], [Rola_id]) VALUES (2, 20)

/****** Object:  Table [dbo].[Udostepnienie]    Script Date: 02/07/2012 12:09:47 ******/

SET ANSI_NULLS ON

GO

SET QUOTED_IDENTIFIER ON

GO

CREATE TABLE [dbo].[Udostepnienie](

	[Id] [int] IDENTITY(1,1) NOT NULL,

	[osobaId] [int] NOT NULL,

	[UdostepniajacyUserId] [int] NOT NULL,

	[DataUtworzenia] [datetime] NOT NULL,

	[DataUdostepnienia] [datetime] NOT NULL,

	[Zakres] [nvarchar](max) NOT NULL,

	[Instytucja] [nvarchar](250) NULL,

 CONSTRAINT [PK__Udostepn] PRIMARY KEY CLUSTERED 

(

	[Id] ASC

)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON, FILLFACTOR = 100) ON [PRIMARY]

) ON [PRIMARY]

GO

/****** Object:  Table [dbo].[Zdarzenie]    Script Date: 02/07/2012 12:09:47 ******/

SET ANSI_NULLS ON

GO

SET QUOTED_IDENTIFIER ON

GO

CREATE TABLE [dbo].[Zdarzenie](

	[Id] [int] IDENTITY(1,1) NOT NULL,

	[Uzytkownik_id] [int] NULL,

	[Zaswiadczenie_id] [int] NULL,

	[Osoba_id] [int] NULL,

	[Informacja] [nvarchar](max) NOT NULL,

	[Data_utworzenia] [datetime] NOT NULL CONSTRAINT [DF_Zdarzenie_Data_utworzenia]  DEFAULT (getdate()),

	[Typ] [nvarchar](50) NOT NULL,

	[Status_id] [int] NULL,

 CONSTRAINT [PK_zdarzenie] 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]

GO

CREATE NONCLUSTERED INDEX [IX_Zdarzenie_DataZdarzenia] ON [dbo].[Zdarzenie] 

(

	[Data_utworzenia] ASC

)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]

GO

CREATE NONCLUSTERED INDEX [IX_Zdarzenie_Legitymacja] ON [dbo].[Zdarzenie] 

(

	[Zaswiadczenie_id] ASC

)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]

GO

CREATE NONCLUSTERED INDEX [IX_Zdarzenie_Uzytkownik] ON [dbo].[Zdarzenie] 

(

	[Uzytkownik_id] ASC

)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]

GO

CREATE NONCLUSTERED INDEX [IX_Zdarzenie_Wniosek] ON [dbo].[Zdarzenie] 

(

	[Osoba_id] ASC

)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]

GO

/****** Object:  View [dbo].[UdostepnienieRaport]    Script Date: 02/07/2012 12:09:48 ******/

SET ANSI_NULLS ON

GO

SET QUOTED_IDENTIFIER ON

GO

CREATE VIEW [dbo].[UdostepnienieRaport]

AS

SELECT        dbo.Osoba.str2_nazwisko, dbo.Osoba.str2_imiona, dbo.Osoba.pesel, dbo.Uzytkownik.Nazwisko AS NazwiskoUdo, dbo.Uzytkownik.Imie AS ImieUdo, 

                         dbo.Udostepnienie.DataUdostepnienia, dbo.Udostepnienie.Zakres, dbo.Udostepnienie.Instytucja, dbo.Osoba.Id AS OsobaId, 

                         dbo.Udostepnienie.Id AS UdostepnienieId

FROM            dbo.Osoba INNER JOIN

                         dbo.Udostepnienie ON dbo.Osoba.Id = dbo.Udostepnienie.osobaId INNER JOIN

                         dbo.Uzytkownik ON dbo.Udostepnienie.UdostepniajacyUserId = dbo.Uzytkownik.Id

GO

/****** Object:  View [dbo].[OsobaZaswiadczenie]    Script Date: 02/07/2012 12:09:48 ******/

SET ANSI_NULLS ON

GO

SET QUOTED_IDENTIFIER ON

GO

CREATE VIEW [dbo].[OsobaZaswiadczenie]

AS

SELECT     dbo.Zaswiadczenie.Id, dbo.Osoba.str2_nazwisko, dbo.Osoba.str2_imiona, dbo.Osoba.pesel, dbo.Zaswiadczenie.str2_wydany_przez, dbo.Zaswiadczenie.str2_data_wydania, 

                      dbo.Zaswiadczenie.data_egzaminu, dbo.Zaswiadczenie.data_waznosci_uprawnien, dbo.Zaswiadczenie.zakres_uprawnien, dbo.Zaswiadczenie.str1_certificateNb, 

                      dbo.Zaswiadczenie.Status_id, dbo.Status.Nazwa, dbo.Zaswiadczenie.wtornik, dbo.Osoba.Id AS osobaId

FROM         dbo.Status INNER JOIN

                      dbo.Zaswiadczenie ON dbo.Status.Id = dbo.Zaswiadczenie.Status_id RIGHT OUTER JOIN

                      dbo.Osoba ON dbo.Zaswiadczenie.osobaId = dbo.Osoba.Id

GO

/****** Object:  ForeignKey [FK_Udostep_Osoba]    Script Date: 02/07/2012 12:09:47 ******/

ALTER TABLE [dbo].[Udostepnienie]  WITH CHECK ADD  CONSTRAINT [FK_Udostep_Osoba] FOREIGN KEY([osobaId])

REFERENCES [dbo].[Osoba] ([Id])

ON UPDATE CASCADE

ON DELETE CASCADE

GO

ALTER TABLE [dbo].[Udostepnienie] CHECK CONSTRAINT [FK_Udostep_Osoba]

GO

/****** Object:  ForeignKey [FK_Udostep_Uzytkownik]    Script Date: 02/07/2012 12:09:47 ******/

ALTER TABLE [dbo].[Udostepnienie]  WITH CHECK ADD  CONSTRAINT [FK_Udostep_Uzytkownik] FOREIGN KEY([UdostepniajacyUserId])

REFERENCES [dbo].[Uzytkownik] ([Id])

GO

ALTER TABLE [dbo].[Udostepnienie] CHECK CONSTRAINT [FK_Udostep_Uzytkownik]

GO

/****** Object:  ForeignKey [FK_UzytkownikRola_Rola]    Script Date: 02/07/2012 12:09:47 ******/

ALTER TABLE [dbo].[UzytkownikRola]  WITH CHECK ADD  CONSTRAINT [FK_UzytkownikRola_Rola] FOREIGN KEY([Rola_id])

REFERENCES [dbo].[Rola] ([Id])

ON UPDATE CASCADE

ON DELETE CASCADE

GO

ALTER TABLE [dbo].[UzytkownikRola] CHECK CONSTRAINT [FK_UzytkownikRola_Rola]

GO

/****** Object:  ForeignKey [FK_UzytkownikRola_Uzytkownik]    Script Date: 02/07/2012 12:09:47 ******/

ALTER TABLE [dbo].[UzytkownikRola]  WITH CHECK ADD  CONSTRAINT [FK_UzytkownikRola_Uzytkownik] FOREIGN KEY([Uzytkownik_id])

REFERENCES [dbo].[Uzytkownik] ([Id])

ON UPDATE CASCADE

ON DELETE CASCADE

GO

ALTER TABLE [dbo].[UzytkownikRola] CHECK CONSTRAINT [FK_UzytkownikRola_Uzytkownik]

GO

/****** Object:  ForeignKey [FK_Zasw_Osoba]    Script Date: 02/07/2012 12:09:47 ******/

ALTER TABLE [dbo].[Zaswiadczenie]  WITH CHECK ADD  CONSTRAINT [FK_Zasw_Osoba] FOREIGN KEY([osobaId])

REFERENCES [dbo].[Osoba] ([Id])

GO

ALTER TABLE [dbo].[Zaswiadczenie] CHECK CONSTRAINT [FK_Zasw_Osoba]

GO

/****** Object:  ForeignKey [FK_Zaswiadcz_Status]    Script Date: 02/07/2012 12:09:47 ******/

ALTER TABLE [dbo].[Zaswiadczenie]  WITH CHECK ADD  CONSTRAINT [FK_Zaswiadcz_Status] FOREIGN KEY([Status_id])

REFERENCES [dbo].[Status] ([Id])

ON UPDATE CASCADE

ON DELETE CASCADE

GO

ALTER TABLE [dbo].[Zaswiadczenie] CHECK CONSTRAINT [FK_Zaswiadcz_Status]

GO

/****** Object:  ForeignKey [FK_Zdarzenie_Uzytkownik]    Script Date: 02/07/2012 12:09:47 ******/

ALTER TABLE [dbo].[Zdarzenie]  WITH CHECK ADD  CONSTRAINT [FK_Zdarzenie_Uzytkownik] FOREIGN KEY([Uzytkownik_id])

REFERENCES [dbo].[Uzytkownik] ([Id])

ON UPDATE CASCADE

ON DELETE CASCADE

GO

ALTER TABLE [dbo].[Zdarzenie] CHECK CONSTRAINT [FK_Zdarzenie_Uzytkownik]

GO

/****** Object:  ForeignKey [FK_Zdarzenie_Zaswiadczenie]    Script Date: 02/07/2012 12:09:47 ******/

ALTER TABLE [dbo].[Zdarzenie]  WITH CHECK ADD  CONSTRAINT [FK_Zdarzenie_Zaswiadczenie] FOREIGN KEY([Zaswiadczenie_id])

REFERENCES [dbo].[Zaswiadczenie] ([Id])

ON UPDATE CASCADE

ON DELETE CASCADE

GO

ALTER TABLE [dbo].[Zdarzenie] CHECK CONSTRAINT [FK_Zdarzenie_Zaswiadczenie]

GO