/****** Object:  Database Nowinki    Script Date: 10-Jul-02 12:41:09 PM ******/
IF EXISTS (SELECT name FROM master.dbo.sysdatabases WHERE name = N'Nowinki')
	DROP DATABASE [Nowinki]
GO

CREATE DATABASE [Nowinki]  ON (NAME = N'Nowinki_Data', FILENAME = N'c:\program files\microsoft sql server\mssql\data\Nowinki_Data.mdf' , SIZE = 3, FILEGROWTH = 10%) LOG ON (NAME = N'Nowinki_Log', FILENAME = N'c:\program files\microsoft sql server\mssql\data\Nowinki_Log.LDF' , SIZE = 3, FILEGROWTH = 10%)
 COLLATE Polish_CI_AI
GO

exec sp_dboption N'Nowinki', N'autoclose', N'false'
GO

exec sp_dboption N'Nowinki', N'bulkcopy', N'false'
GO

exec sp_dboption N'Nowinki', N'trunc. log', N'false'
GO

exec sp_dboption N'Nowinki', N'torn page detection', N'true'
GO

exec sp_dboption N'Nowinki', N'read only', N'false'
GO

exec sp_dboption N'Nowinki', N'dbo use', N'false'
GO

exec sp_dboption N'Nowinki', N'single', N'false'
GO

exec sp_dboption N'Nowinki', N'autoshrink', N'false'
GO

exec sp_dboption N'Nowinki', N'ANSI null default', N'false'
GO

exec sp_dboption N'Nowinki', N'recursive triggers', N'false'
GO

exec sp_dboption N'Nowinki', N'ANSI nulls', N'false'
GO

exec sp_dboption N'Nowinki', N'concat null yields null', N'false'
GO

exec sp_dboption N'Nowinki', N'cursor close on commit', N'false'
GO

exec sp_dboption N'Nowinki', N'default to local cursor', N'false'
GO

exec sp_dboption N'Nowinki', N'quoted identifier', N'false'
GO

exec sp_dboption N'Nowinki', N'ANSI warnings', N'false'
GO

exec sp_dboption N'Nowinki', N'auto create statistics', N'true'
GO

exec sp_dboption N'Nowinki', N'auto update statistics', N'true'
GO

use [Nowinki]
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[FK_tblZamowienia_tblKlienci]') and OBJECTPROPERTY(id, N'IsForeignKey') = 1)
ALTER TABLE [dbo].[tblZamowienia] DROP CONSTRAINT FK_tblZamowienia_tblKlienci
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[FK_tblPracownicy_tblDzialy]') and OBJECTPROPERTY(id, N'IsForeignKey') = 1)
ALTER TABLE [dbo].[tblPracownicy] DROP CONSTRAINT FK_tblPracownicy_tblDzialy
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[FK_tblPozycjeZamowienia_tblProdukty]') and OBJECTPROPERTY(id, N'IsForeignKey') = 1)
ALTER TABLE [dbo].[tblPozycjeZamowienia] DROP CONSTRAINT FK_tblPozycjeZamowienia_tblProdukty
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[FK_tblPozycjeZamowienia_tblZamowienia]') and OBJECTPROPERTY(id, N'IsForeignKey') = 1)
ALTER TABLE [dbo].[tblPozycjeZamowienia] DROP CONSTRAINT FK_tblPozycjeZamowienia_tblZamowienia
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[FK_tblKlienci_tblRegiony]') and OBJECTPROPERTY(id, N'IsForeignKey') = 1)
ALTER TABLE [dbo].[tblKlienci] DROP CONSTRAINT FK_tblKlienci_tblRegiony
GO

/****** Object:  Trigger dbo.trKlientI    Script Date: 10-Jul-02 12:41:09 PM ******/
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[trKlientI]') and OBJECTPROPERTY(id, N'IsTrigger') = 1)
drop trigger [dbo].[trKlientI]
GO

/****** Object:  Stored Procedure dbo.UsuwaniePracownika    Script Date: 10-Jul-02 12:41:09 PM ******/
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[UsuwaniePracownika]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[UsuwaniePracownika]
GO

/****** Object:  Stored Procedure dbo.KlientWedlugID    Script Date: 10-Jul-02 12:41:09 PM ******/
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[KlientWedlugID]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[KlientWedlugID]
GO

/****** Object:  Stored Procedure dbo.WstawianiePracownika    Script Date: 10-Jul-02 12:41:09 PM ******/
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[WstawianiePracownika]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[WstawianiePracownika]
GO

/****** Object:  Stored Procedure dbo.WstawianiePracownikaOrg    Script Date: 10-Jul-02 12:41:09 PM ******/
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[WstawianiePracownikaOrg]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[WstawianiePracownikaOrg]
GO

/****** Object:  Stored Procedure dbo.WyszukiwanieNazwiska    Script Date: 10-Jul-02 12:41:09 PM ******/
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[WyszukiwanieNazwiska]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[WyszukiwanieNazwiska]
GO

/****** Object:  Stored Procedure dbo.WybieraniePracownikow    Script Date: 10-Jul-02 12:41:09 PM ******/
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[WybieraniePracownikow]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[WybieraniePracownikow]
GO

/****** Object:  Stored Procedure dbo.AktualizacjaPracownika    Script Date: 10-Jul-02 12:41:09 PM ******/
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[AktualizacjaPracownika]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[AktualizacjaPracownika]
GO

/****** Object:  Stored Procedure dbo.procPracownicyPosortowani    Script Date: 10-Jul-02 12:41:09 PM ******/
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[procPracownicyPosortowani]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[procPracownicyPosortowani]
GO

/****** Object:  View dbo.PracownikDzial_widok    Script Date: 10-Jul-02 12:41:09 PM ******/
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[PracownikDzial_widok]') and OBJECTPROPERTY(id, N'IsView') = 1)
drop view [dbo].[PracownikDzial_widok]
GO

/****** Object:  View dbo.Pracownicy_widok    Script Date: 10-Jul-02 12:41:09 PM ******/
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[qryPracownik_widok]') and OBJECTPROPERTY(id, N'IsView') = 1)
drop view [dbo].[Pracownicy_widok]
GO

/****** Object:  Table [dbo].[tblKlienci]    Script Date: 10-Jul-02 12:41:09 PM ******/
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[tblKlienci]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[tblKlienci]
GO

/****** Object:  Table [dbo].[tblDzialy]    Script Date: 10-Jul-02 12:41:09 PM ******/
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[tblDzialy]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[tblDzialy]
GO

/****** Object:  Table [dbo].[tblPracownicy]    Script Date: 10-Jul-02 12:41:09 PM ******/
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[tblPracownicy]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[tblPracownicy]
GO

/****** Object:  Table [dbo].[tblProdukty]    Script Date: 10-Jul-02 12:41:09 PM ******/
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[tblProdukty]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[tblProdukty]
GO

/****** Object:  Table [dbo].[tblZamowienia]    Script Date: 10-Jul-02 12:41:09 PM ******/
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[tblZamowienia]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[tblZamowienia]
GO

/****** Object:  Table [dbo].[tblPozycjeZamowienia]    Script Date: 10-Jul-02 12:41:09 PM ******/
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[tblPozycjeZamowienia]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[tblPozycjeZamowienia]
GO

/****** Object:  Table [dbo].[tblRegiony]    Script Date: 10-Jul-02 12:41:09 PM ******/
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[tblRegiony]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[tblRegiony]
GO

/****** Object:  Table [dbo].[tblKlienci]    Script Date: 10-Jul-02 12:41:10 PM ******/
CREATE TABLE [dbo].[tblKlienci] (
	[ID] [int] IDENTITY (1, 1) NOT NULL ,
	[Imie] [varchar] (20) COLLATE Polish_CI_AI NULL ,
	[Nazwisko] [varchar] (30) COLLATE Polish_CI_AI NULL ,
	[Firma] [varchar] (50) COLLATE Polish_CI_AI NULL ,
	[Adres] [varchar] (50) COLLATE Polish_CI_AI NULL ,
	[Miasto] [varchar] (30) COLLATE Polish_CI_AI NULL ,
	[Wojewodztwo] [char] (3) COLLATE Polish_CI_AI NULL ,
	[KodPocztowy] [varchar] (6) COLLATE Polish_CI_AI NULL ,
	[Telefon] [varchar] (15) COLLATE Polish_CI_AI NULL ,
	[Faks] [varchar] (15) COLLATE Polish_CI_AI NULL ,
	[Email] [varchar] (100) COLLATE Polish_CI_AI NULL ,
	[NazwiskoSoundex] [varchar] (4) COLLATE Polish_CI_AI NULL 
) ON [PRIMARY]
GO

/****** Object:  Table [dbo].[tblDzialy]    Script Date: 10-Jul-02 12:41:11 PM ******/
CREATE TABLE [dbo].[tblDzialy] (
	[ID] [int] IDENTITY (1, 1) NOT NULL ,
	[NazwaDzialu] [varchar] (75) COLLATE Polish_CI_AI NOT NULL 
) ON [PRIMARY]
GO

/****** Object:  Table [dbo].[tblPracownicy]    Script Date: 10-Jul-02 12:41:11 PM ******/
CREATE TABLE [dbo].[tblPracownicy] (
	[ID] [int] IDENTITY (1, 1) NOT NULL ,
	[Imie] [varchar] (50) COLLATE Polish_CI_AI NOT NULL ,
	[Nazwisko] [varchar] (70) COLLATE Polish_CI_AI NOT NULL ,
	[IDDzialu] [int] NULL ,
	[Pensja] [money] NULL 
) ON [PRIMARY]
GO

/****** Object:  Table [dbo].[tblProdukty]    Script Date: 10-Jul-02 12:41:11 PM ******/
CREATE TABLE [dbo].[tblProdukty] (
	[ID] [int] IDENTITY (1, 1) NOT NULL ,
	[NazwaProduktu] [varchar] (75) COLLATE Polish_CI_AI NOT NULL ,
	[CenaHurtowa] [money] NULL ,
	[CenaDetaliczna] [money] NULL ,
	[Opis] [ntext] COLLATE Polish_CI_AI NULL 
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
GO

/****** Object:  Table [dbo].[tblZamowienia]    Script Date: 10-Jul-02 12:41:12 PM ******/
CREATE TABLE [dbo].[tblZamowienia] (
	[ID] [int] IDENTITY (1, 1) NOT NULL ,
	[IDKlienta] [int] NULL ,
	[DataZamowienia] [datetime] NULL ,
	[Kwota] [money] NULL 
) ON [PRIMARY]
GO

/****** Object:  Table [dbo].[tblPozycjeZamowienia]    Script Date: 10-Jul-02 12:41:12 PM ******/
CREATE TABLE [dbo].[tblPozycjeZamowienia] (
	[ID] [int] IDENTITY (1, 1) NOT NULL ,
	[IDZamowienia] [int] NOT NULL ,
	[IDPozycji] [int] NOT NULL ,
	[Ilosc] [int] NULL ,
	[Koszt] [money] NULL 
) ON [PRIMARY]
GO

/****** Object:  Table [dbo].[tblRegiony]    Script Date: 10-Jul-02 12:41:12 PM ******/
CREATE TABLE [dbo].[tblRegiony] (
	[ID] [int] IDENTITY (1, 1) NOT NULL ,
	[Wojewodztwo] [char] (3) COLLATE Polish_CI_AI NOT NULL ,
	[NazwaRegionu] [varchar] (25) COLLATE Polish_CI_AI NULL 
) ON [PRIMARY]
GO

ALTER TABLE [dbo].[tblKlienci] WITH NOCHECK ADD 
	CONSTRAINT [PK_tblKlienci] PRIMARY KEY  CLUSTERED 
	(
		[ID]
	)  ON [PRIMARY] 
GO

ALTER TABLE [dbo].[tblDzialy] WITH NOCHECK ADD 
	CONSTRAINT [tblDzialy_IDPK] PRIMARY KEY  CLUSTERED 
	(
		[ID]
	)  ON [PRIMARY] 
GO

ALTER TABLE [dbo].[tblPracownicy] WITH NOCHECK ADD 
	CONSTRAINT [PK_tblPracownicy] PRIMARY KEY  CLUSTERED 
	(
		[ID]
	)  ON [PRIMARY] 
GO

ALTER TABLE [dbo].[tblProdukty] WITH NOCHECK ADD 
	CONSTRAINT [PK_tblProdukty] PRIMARY KEY  CLUSTERED 
	(
		[ID]
	)  ON [PRIMARY] 
GO

ALTER TABLE [dbo].[tblZamowienia] WITH NOCHECK ADD 
	CONSTRAINT [PK_tblZamowienia] PRIMARY KEY  CLUSTERED 
	(
		[ID]
	)  ON [PRIMARY] 
GO

ALTER TABLE [dbo].[tblPozycjeZamowienia] WITH NOCHECK ADD 
	CONSTRAINT [PK_tblPozycjeZamowienia] PRIMARY KEY  CLUSTERED 
	(
		[ID]
	)  ON [PRIMARY] 
GO

ALTER TABLE [dbo].[tblRegiony] WITH NOCHECK ADD 
	CONSTRAINT [PK_tblRegiony] PRIMARY KEY  CLUSTERED 
	(
		[ID]
	)  ON [PRIMARY] 
GO

 CREATE  UNIQUE  INDEX [IX_tblRegiony] ON [dbo].[tblRegiony]([Wojewodztwo]) ON [PRIMARY]
GO

ALTER TABLE [dbo].[tblKlienci] ADD 
	CONSTRAINT [FK_tblKlienci_tblRegiony] FOREIGN KEY 
	(
		[Wojewodztwo]
	) REFERENCES [dbo].[tblRegiony] (
		[Wojewodztwo]
	) ON DELETE CASCADE  ON UPDATE CASCADE 
GO

ALTER TABLE [dbo].[tblPracownicy] ADD 
	CONSTRAINT [FK_tblPracownicy_tblDzialy] FOREIGN KEY 
	(
		[IDDzialu]
	) REFERENCES [dbo].[tblDzialy] (
		[ID]
	) ON DELETE CASCADE  ON UPDATE CASCADE 
GO

ALTER TABLE [dbo].[tblZamowienia] ADD 
	CONSTRAINT [FK_tblZamowienia_tblKlienci] FOREIGN KEY 
	(
		[IDKlienta]
	) REFERENCES [dbo].[tblKlienci] (
		[ID]
	) ON DELETE CASCADE  ON UPDATE CASCADE 
GO

ALTER TABLE [dbo].[tblPozycjeZamowienia] ADD 
	CONSTRAINT [FK_tblPozycjeZamowienia_tblProdukty] FOREIGN KEY 
	(
		[IDPozycji]
	) REFERENCES [dbo].[tblProdukty] (
		[ID]
	) ON DELETE CASCADE  ON UPDATE CASCADE ,
	CONSTRAINT [FK_tblPozycjeZamowienia_tblZamowienia] FOREIGN KEY 
	(
		[IDZamowienia]
	) REFERENCES [dbo].[tblZamowienia] (
		[ID]
	) ON DELETE CASCADE  ON UPDATE CASCADE 
GO

SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

/****** Object:  View dbo.PracownikDzial_widok    Script Date: 10-Jul-02 12:41:13 PM ******/

CREATE view PracownikDzial_widok
as
  select e.ID, Imie, Nazwisko, NazwaDzialu
  from tblPracownicy e, tblDzialy t
  where e.IDDzialu  = t.ID



GO

SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

SET QUOTED_IDENTIFIER ON 
GO
SET ANSI_NULLS ON 
GO

/****** Object:  View dbo.qryPracownik_widok    Script Date: 10-Jul-02 12:41:13 PM ******/

CREATE VIEW Pracownicy_widok AS
    SELECT ID, Imie, Nazwisko, IDDzialu
    FROM tblPracownicy

GO

SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

SET QUOTED_IDENTIFIER ON 
GO
SET ANSI_NULLS ON 
GO

/****** Object:  Stored Procedure dbo.UsuwaniePracownika    Script Date: 10-Jul-02 12:41:13 PM ******/
CREATE PROCEDURE dbo.UsuwaniePracownika
(
	@Pierwotny_ID int
)
AS
	SET NOCOUNT OFF;
DELETE FROM tblPracownicy WHERE (ID = @Pierwotny_ID)
GO

SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

SET QUOTED_IDENTIFIER ON 
GO
SET ANSI_NULLS ON 
GO

/****** Object:  Stored Procedure dbo.KlientWedlugID    Script Date: 10-Jul-02 12:41:13 PM ******/
create procedure KlientWedlugID
   @IDklt int
   as
   select * from tblKlienci
   where ID = @IDklt


GO

SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS OFF 
GO

/****** Object:  Stored Procedure dbo.WstawianiePracownika    Script Date: 10-Jul-02 12:41:13 PM ******/
CREATE PROCEDURE dbo.WstawianiePracownika
(
	@Imie varchar(50),
	@Nazwisko varchar(70),
	@IDDzialu int,
	@Pensja money
)
AS
	SET NOCOUNT OFF;


if (@Pensja = 0 or @Pensja is null)
begin
	-- Skomplikowane obliczenia pensji 
	set @Pensja = @IDDzialu * 10000
end

INSERT INTO tblPracownicy(Imie, Nazwisko, IDDzialu, Pensja) VALUES
		 (@Imie, @Nazwisko, @IDDzialu, @Pensja)
GO

SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

SET QUOTED_IDENTIFIER ON 
GO
SET ANSI_NULLS ON 
GO

/****** Object:  Stored Procedure dbo.WstawianiePracownikaOrg    Script Date: 10-Jul-02 12:41:13 PM ******/
CREATE PROCEDURE dbo.WstawianiePracownikaOrg
(
	@Imie varchar(50),
	@Nazwisko varchar(70),
	@IDDzialu int,
	@Pensja money
)
AS
	SET NOCOUNT OFF;
INSERT INTO tblPracownicy(Imie, Nazwisko, IDDzialu, Pensja) VALUES (@Imie, @Nazwisko, @IDDzialu, @Pensja)
GO

SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

SET QUOTED_IDENTIFIER ON 
GO
SET ANSI_NULLS ON 
GO

/****** Object:  Stored Procedure dbo.WyszukiwanieNazwiska    Script Date: 10-Jul-02 12:41:13 PM ******/
create proc WyszukiwanieNazwiska
  @nazwisko varchar(40)
as
select * from tblKlienci
where soundex(@nazwisko) = NazwiskoSoundex



GO

SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

SET QUOTED_IDENTIFIER ON 
GO
SET ANSI_NULLS ON 
GO

/****** Object:  Stored Procedure dbo.WybieraniePracownikow    Script Date: 10-Jul-02 12:41:13 PM ******/
CREATE PROCEDURE dbo.WybieraniePracownikow
AS
	SET NOCOUNT ON;
SELECT  Imie, Nazwisko, IDDzialu, Pensja, ID FROM tblPracownicy
GO

SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

SET QUOTED_IDENTIFIER ON 
GO
SET ANSI_NULLS ON 
GO

/****** Object:  Stored Procedure dbo.AktualizacjaPracownika    Script Date: 10-Jul-02 12:41:13 PM ******/
CREATE PROCEDURE dbo.AktualizacjaPracownika
(
	@Imie varchar(50),
	@Nazwisko varchar(70),
	@IDDzialu int,
	@Pensja money,
	@Pierwotny_ID int
)
AS
	SET NOCOUNT OFF;
UPDATE tblPracownicy SET Imie = @Imie, Nazwisko = @Nazwisko, IDDzialu = @IDDzialu, Pensja = @Pensja WHERE (ID = @Pierwotny_ID)
GO

SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS OFF 
GO

/****** Object:  Stored Procedure dbo.procPracownicyPosortowani    Script Date: 10-Jul-02 12:41:13 PM ******/
CREATE PROCEDURE procPracownicyPosortowani AS 
	select  * from tblPracownicy
	order by Nazwisko, Imie
return
GO

SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

SET QUOTED_IDENTIFIER ON 
GO
SET ANSI_NULLS ON 
GO

/****** Object:  Trigger dbo.trKlientI    Script Date: 10-Jul-02 12:41:14 PM ******/
create trigger trKlientI
on dbo.tblKlienci
for insert, update
as
   update tblKlienci
   set tblKlienci.NazwiskoSoundex = soundex(tblKlienci.Nazwisko)
   from inserted
   where tblKlienci.ID = inserted.ID

GO

SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

