Untitled

IF OBJECT_ID('adres', 'U') IS NOT NULL
  ALTER TABLE adres
      DROP CONSTRAINT adres_klient_FK
  GO
  DROP TABLE adres;

IF OBJECT_ID('egzemplarz', 'U') IS NOT NULL
  ALTER TABLE egzemplarz
      DROP CONSTRAINT egzemplarz_produkt_FK
  GO
  DROP TABLE egzemplarz;

IF OBJECT_ID('faktury', 'U') IS NOT NULL
  ALTER TABLE faktury
      DROP CONSTRAINT faktury_klient_FK
  GO
  ALTER TABLE faktury
      DROP CONSTRAINT faktury_zamowienie_FK
  GO
  DROP TABLE faktury;

IF OBJECT_ID('kontakt', 'U') IS NOT NULL
  ALTER TABLE kontakt
      DROP CONSTRAINT kontakt_klient_FK
  GO
  DROP TABLE kontakt;

IF OBJECT_ID('produktFaktura', 'U') IS NOT NULL
  ALTER TABLE produktFaktura
      DROP CONSTRAINT produktFaktura_faktury_FK
  GO

  ALTER TABLE produktFaktura
      DROP CONSTRAINT produktFaktura_produkt_FK
  GO
  DROP TABLE produktFaktura;

IF OBJECT_ID('produktZamowienie', 'U') IS NOT NULL
  ALTER TABLE produktZamowienie
      DROP CONSTRAINT produktZamowienie_produkt_FK
  GO

  ALTER TABLE produktZamowienie
      DROP CONSTRAINT produktZamowienie_zamowienie_FK
  GO
  DROP TABLE produktZamowienie;

IF OBJECT_ID('zamowienie', 'U') IS NOT NULL
  ALTER TABLE zamowienie
      DROP CONSTRAINT zamowienie_faktury_FK
  GO

  ALTER TABLE zamowienie
      DROP CONSTRAINT zamowienie_klient_FK
  GO
  DROP TABLE zamowienie;

IF OBJECT_ID('klient', 'U') IS NOT NULL
  DROP TABLE klient;

IF OBJECT_ID('produkt', 'U') IS NOT NULL
  DROP TABLE produkt;


CREATE TABLE adres
    (
     klient_login VARCHAR (64) NOT NULL ,
     ulica VARCHAR (64) NOT NULL ,
     nr_domu INTEGER NOT NULL ,
     nr_lokalu INTEGER ,
     kod_pocztowy VARCHAR (64) NOT NULL ,
     miasto VARCHAR (64) NOT NULL
    )
    ON "default"
GO

ALTER TABLE adres ADD CONSTRAINT adres_PK PRIMARY KEY CLUSTERED (klient_login)
     WITH (
     ALLOW_PAGE_LOCKS = ON ,
     ALLOW_ROW_LOCKS = ON )
     ON "default"
    GO

CREATE TABLE egzemplarz
    (
     nr_seryjny INTEGER NOT NULL ,
     produkt_kod_produktu VARCHAR (64) NOT NULL ,
     nazwa VARCHAR (64) NOT NULL ,
     data_zakupu DATETIME NOT NULL ,
     data_sprzedazy DATETIME NOT NULL ,
     czy_sprzedano INTEGER NOT NULL
    )
    ON "default"
GO

ALTER TABLE egzemplarz ADD CONSTRAINT egzemplarz_PK PRIMARY KEY CLUSTERED (nr_seryjny)
     WITH (
     ALLOW_PAGE_LOCKS = ON ,
     ALLOW_ROW_LOCKS = ON )
     ON "default"
    GO

CREATE TABLE faktury
    (
     nr_faktury VARCHAR (64) NOT NULL ,
     zamowienie_nr_zamowienia INTEGER NOT NULL ,
     klient_login VARCHAR (64) NOT NULL ,
     data_sprzedazy DATETIME NOT NULL ,
     wartosc_netto MONEY NOT NULL ,
     wartosc_brutto MONEY NOT NULL ,
     forma_platnosci VARCHAR (64) NOT NULL
    )
    ON "default"
GO

ALTER TABLE faktury ADD CONSTRAINT faktury_PK PRIMARY KEY CLUSTERED (nr_faktury)
     WITH (
     ALLOW_PAGE_LOCKS = ON ,
     ALLOW_ROW_LOCKS = ON )
     ON "default"
    GO

CREATE TABLE klient
    (
     login VARCHAR (64) NOT NULL ,
     haslo VARCHAR (64) NOT NULL ,
     imie VARCHAR (64) NOT NULL ,
     nazwisko VARCHAR (64) NOT NULL ,
     nip VARCHAR (64) ,
     nazwa_firmy VARCHAR (64)
    )
    ON "default"
GO

ALTER TABLE klient ADD CONSTRAINT klient_PK PRIMARY KEY CLUSTERED (login)
     WITH (
     ALLOW_PAGE_LOCKS = ON ,
     ALLOW_ROW_LOCKS = ON )
     ON "default"
    GO

CREATE TABLE kontakt
    (
     klient_login VARCHAR (64) NOT NULL ,
     nr_tel INTEGER NOT NULL CONSTRAINT ck_kont_tel CHECK(nr_tel LIKE '[0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9]'),
     fax INTEGER NOT NULL CONSTRAINT ck_kont_fax CHECK(fax LIKE '[0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9]'),
     email VARCHAR (64) NOT NULL CONSTRAINT ck_email_mal CHECK (email LIKE '%@%'),
    )
    ON "default"
GO

ALTER TABLE kontakt ADD CONSTRAINT kontakt_PK PRIMARY KEY CLUSTERED (klient_login)
     WITH (
     ALLOW_PAGE_LOCKS = ON ,
     ALLOW_ROW_LOCKS = ON )
     ON "default"
    GO

CREATE TABLE produkt
    (
     kod_produktu VARCHAR (64) NOT NULL ,
     nazwa_produktu VARCHAR (64) NOT NULL ,
     producent VARCHAR (64) NOT NULL ,
     cena_netto MONEY NOT NULL ,
     cena_brutto MONEY NOT NULL ,
     kolor VARCHAR (64) NOT NULL ,
     kategoria VARCHAR (64) NOT NULL
    )
    ON "default"
GO

ALTER TABLE produkt ADD CONSTRAINT produkt_PK PRIMARY KEY CLUSTERED (kod_produktu)
     WITH (
     ALLOW_PAGE_LOCKS = ON ,
     ALLOW_ROW_LOCKS = ON )
     ON "default"
    GO

CREATE TABLE produktFaktura
    (
     produkt_kod_produktu VARCHAR (64) NOT NULL ,
     faktury_nr_faktury VARCHAR (64) NOT NULL
    )
    ON "default"
GO

ALTER TABLE produktFaktura ADD CONSTRAINT produktFaktura_PK PRIMARY KEY CLUSTERED (produkt_kod_produktu, faktury_nr_faktury)
     WITH (
     ALLOW_PAGE_LOCKS = ON ,
     ALLOW_ROW_LOCKS = ON )
     ON "default"
    GO

CREATE TABLE produktZamowienie
    (
     produkt_kod_produktu VARCHAR (64) NOT NULL ,
     zamowienie_nr_zamowienia INTEGER NOT NULL
    )
    ON "default"
GO

ALTER TABLE produktZamowienie ADD CONSTRAINT produktZamowienie_PK PRIMARY KEY CLUSTERED (produkt_kod_produktu, zamowienie_nr_zamowienia)
     WITH (
     ALLOW_PAGE_LOCKS = ON ,
     ALLOW_ROW_LOCKS = ON )
     ON "default"
    GO

CREATE TABLE zamowienie
    (
     nr_zamowienia INTEGER NOT NULL ,
     klient_login VARCHAR (64) NOT NULL ,
     faktury_nr_faktury VARCHAR (64) NOT NULL ,
     data_zlozenia DATETIME NOT NULL ,
     data_realizacji DATETIME NOT NULL ,
     data_wysylki DATETIME NOT NULL ,
     forma_dostawy VARCHAR (64) NOT NULL
    )
    ON "default"
GO

ALTER TABLE zamowienie ADD CONSTRAINT zamowienie_PK PRIMARY KEY CLUSTERED (nr_zamowienia)
     WITH (
     ALLOW_PAGE_LOCKS = ON ,
     ALLOW_ROW_LOCKS = ON )
     ON "default"
    GO

ALTER TABLE adres
    ADD CONSTRAINT adres_klient_FK FOREIGN KEY
    (
     klient_login
    )
    REFERENCES klient
    (
     login
    )
    ON DELETE NO ACTION
    ON UPDATE NO ACTION
GO

ALTER TABLE egzemplarz
    ADD CONSTRAINT egzemplarz_produkt_FK FOREIGN KEY
    (
     produkt_kod_produktu
    )
    REFERENCES produkt
    (
     kod_produktu
    )
    ON DELETE NO ACTION
    ON UPDATE NO ACTION
GO

ALTER TABLE faktury
    ADD CONSTRAINT faktury_klient_FK FOREIGN KEY
    (
     klient_login
    )
    REFERENCES klient
    (
     login
    )
    ON DELETE NO ACTION
    ON UPDATE NO ACTION
GO

ALTER TABLE faktury
    ADD CONSTRAINT faktury_zamowienie_FK FOREIGN KEY
    (
     zamowienie_nr_zamowienia
    )
    REFERENCES zamowienie
    (
     nr_zamowienia
    )
    ON DELETE NO ACTION
    ON UPDATE NO ACTION
GO

ALTER TABLE kontakt
    ADD CONSTRAINT kontakt_klient_FK FOREIGN KEY
    (
     klient_login
    )
    REFERENCES klient
    (
     login
    )
    ON DELETE NO ACTION
    ON UPDATE NO ACTION
GO

ALTER TABLE produktFaktura
    ADD CONSTRAINT produktFaktura_faktury_FK FOREIGN KEY
    (
     faktury_nr_faktury
    )
    REFERENCES faktury
    (
     nr_faktury
    )
    ON DELETE NO ACTION
    ON UPDATE NO ACTION
GO

ALTER TABLE produktFaktura
    ADD CONSTRAINT produktFaktura_produkt_FK FOREIGN KEY
    (
     produkt_kod_produktu
    )
    REFERENCES produkt
    (
     kod_produktu
    )
    ON DELETE NO ACTION
    ON UPDATE NO ACTION
GO

ALTER TABLE produktZamowienie
    ADD CONSTRAINT produktZamowienie_produkt_FK FOREIGN KEY
    (
     produkt_kod_produktu
    )
    REFERENCES produkt
    (
     kod_produktu
    )
    ON DELETE NO ACTION
    ON UPDATE NO ACTION
GO

ALTER TABLE produktZamowienie
    ADD CONSTRAINT produktZamowienie_zamowienie_FK FOREIGN KEY
    (
     zamowienie_nr_zamowienia
    )
    REFERENCES zamowienie
    (
     nr_zamowienia
    )
    ON DELETE NO ACTION
    ON UPDATE NO ACTION
GO

ALTER TABLE zamowienie
    ADD CONSTRAINT zamowienie_faktury_FK FOREIGN KEY
    (
     faktury_nr_faktury
    )
    REFERENCES faktury
    (
     nr_faktury
    )
    ON DELETE NO ACTION
    ON UPDATE NO ACTION
GO

ALTER TABLE zamowienie
    ADD CONSTRAINT zamowienie_klient_FK FOREIGN KEY
    (
     klient_login
    )
    REFERENCES klient
    (
     login
    )
    ON DELETE NO ACTION
    ON UPDATE NO ACTION
GO

INSERT INTO klient VALUES
( 'Nazar03' , 'xppew%^85' , 'Nazar' ,'Staron' ,'68572088910' ,'Bosch Sp.z o.o.' ),
( 'Przemko27' , 'ogioe(!54' , 'Przemko' ,'Rozek' ,'37382448272' ,'Okno Company' ),
( 'Wili65' , 'lmedn*!59' , 'Wili' ,'Galkowski' ,'76895392878' ,'Ryba S.A.' ),
( 'Hartmund24' , 'mfmer^%45' , 'Hartmund' ,'Jurewicz' ,'13436056105' ,'Canon Company' ),
( 'Aurela54' , 'hqaug@$03' , 'Aurela' ,'Biesiada' ,'21858979550' ,'Olympus Company' ),
( 'Mierycława42' , 'amxqz%^09' , 'Mierycława' ,'Szumski' ,'69967491286' ,'Ryba Sp.z o.o.' ),
( 'Lilla82' , 'pclqn&%40' , 'Lilla' ,'Budnik' ,'17392732311' ,'Okno Company' ),
( 'Felicytes30' , 'zfmyc%#76' , 'Felicytes' ,'Kijak' ,'64545923690' ,'Olympus Industries' ),
( 'Rodolf72' , 'loxdw@)47' , 'Rodolf' ,'Banka' ,'24446607675' ,'Ryba Company' ),
( 'Hamid56' , 'tttkl%&20' , 'Hamid' ,'Wesoloski' ,'45448760871' ,'Ryba Industries' ),
( 'Wasilika11' , 'kfpis(&74' , 'Wasilika' ,'Brozek' ,'47721984190' ,'Canon Sp.z o.o.' ),
( 'Edda16' , 'zxkli#%49' , 'Edda' ,'Chudzik' ,'17971493081' ,'Okno Company' ),
( 'Kiura33' , 'srlcz##69' , 'Kiura' ,'Marecki' ,'72654823686' ,'Ryba S.A.' ),
( 'Druzjanna54' , 'tbrda$)11' , 'Druzjanna' ,'Golombek' ,'34456709178' ,'Olympus Sp.z o.o.' ),
( 'Praskowia44' , 'pcltx^)98' , 'Praskowia' ,'Goscinski' ,'21404671882' ,'Olympus Company' ),
( 'Przemko63' , 'jlvpm^^94' , 'Przemko' ,'Glogowski' ,'96834940813' ,'Olympus Industries' ),
( 'Margaryta24' , 'zmjsb*#80' , 'Margaryta' ,'Gawrych' ,'77978697139' ,'Okno S.A.' ),
( 'Szymeon06' , 'smucj&(28' , 'Szymeon' ,'Wielgus' ,'94876010755' ,'Sony Company' ),
( 'Tomomasa41' , 'kmtvm**58' , 'Tomomasa' ,'Lipa' ,'63627356983' ,'Okno Company' ),
( 'Bob44' , 'ozmpv$(44' , 'Bob' ,'Sitkowski' ,'71836201010' ,'Sony Sp.z o.o.' ),
( 'Malik48' , 'sogun^&08' , 'Malik' ,'Slomkowski' ,'92821518933' ,'Olympus Company' ),
( 'Simion81' , 'bwlpz#$79' , 'Simion' ,'Mlynarski' ,'53936755672' ,'Ryba Company' ),
( 'Joachem45' , 'wnoem&@37' , 'Joachem' ,'Lata' ,'69398435112' ,'Canon Industries' ),
( 'Otilda81' , 'ivvjm%(68' , 'Otilda' ,'Bienkowski' ,'44274915128' ,'Ryba S.A.' ),
( 'Jacenty74' , 'rlosk&%45' , 'Jacenty' ,'Stepnowski' ,'47648789276' ,'Okno S.A.' ),
( 'Hellena74' , 'mmuxd*!08' , 'Hellena' ,'Karwowski' ,'18048541365' ,'Bosch Sp.z o.o.' ),
( 'Stamisław14' , 'bxnfs^(77' , 'Stamisław' ,'Filipkowski' ,'20766512746' ,'Okno Sp.z o.o.' ),
( 'Bogodar74' , 'lqiin#^61' , 'Bogodar' ,'' ,'76475325916' ,'Sony Industries' ),
( 'Melcyda51' , 'ezisd%!66' , 'Melcyda' ,'Bonar' ,'11313531310' ,'Bosch S.A.' ),
( 'Sawery85' , 'mxuos#*10' , 'Sawery' ,'Siuda' ,'61353501288' ,'Olympus S.A.' ),
( 'Więczesława25' , 'yvxep@@97' , 'Więczesława' ,'Wajda' ,'66461047543' ,'Olympus Sp.z o.o.' ),
( 'Imogena62' , 'gakvl^&10' , 'Imogena' ,'Ogonowski' ,'68877343760' ,'Ryba S.A.' ),
( 'Golfrida87' , 'jyydg%(61' , 'Golfrida' ,'Kaczmarczyk' ,'77075101450' ,'Ryba Company' ),
( 'Serwiliusz01' , 'aqfwf!!36' , 'Serwiliusz' ,'Zuraw' ,'83463405113' ,'Bosch Industries' ),
( 'Boghdan50' , 'mgohy(%76' , 'Boghdan' ,'Blaszkiewicz' ,'88262370905' ,'Sony Company' ),
( 'Alim32' , 'nfeuz^@57' , 'Alim' ,'Banka' ,'72727889390' ,'Olympus Sp.z o.o.' ),
( 'Margo61' , 'sjgeu$!15' , 'Margo' ,'Niemczyk' ,'56223173401' ,'Sony Industries' ),
( 'Hersz03' , 'isswi#^70' , 'Hersz' ,'Terlecki' ,'44180387880' ,'Okno Company' ),
( 'Azacja62' , 'gerrd^(80' , 'Azacja' ,'Piechowski' ,'20796217132' ,'Bosch S.A.' ),
( 'Reinert88' , 'uwkfu*%34' , 'Reinert' ,'Chromy' ,'72037990952' ,'Bosch Company' ),
( 'Zhanna20' , 'hljzz%)72' , 'Zhanna' ,'Gracyalny' ,'37113056472' ,'Ryba Sp.z o.o.' ),
( 'Klementyna03' , 'calhd))95' , 'Klementyna' ,'Nycz' ,'53323024333' ,'Canon Sp.z o.o.' ),
( 'Bibian45' , 'ywpfh*&78' , 'Bibian' ,'Gardocki' ,'67525238067' ,'Okno S.A.' ),
( 'Frida31' , 'lmwrf!#04' , 'Frida' ,'Andrzejewski' ,'40297098423' ,'Ryba Sp.z o.o.' ),
( 'Ignaca73' , 'zjhzm&!79' , 'Ignaca' ,'Paczkowski' ,'71180829079' ,'Olympus Company' ),
( 'Lubomierz36' , 'kfzkx#@22' , 'Lubomierz' ,'Kolinski' ,'90632904638' ,'Olympus Company' ),
( 'Annelisa78' , 'mtgwb)$18' , 'Annelisa' ,'Stodola' ,'66241811186' ,'Okno S.A.' ),
( 'Ermelinda06' , 'tyeee$%03' , 'Ermelinda' ,'Golab' ,'65846945156' ,'Sony Industries' ),
( 'Ruggero43' , 'aizkw)*40' , 'Ruggero' ,'Ciesielski' ,'37559841258' ,'Okno S.A.' ),
( 'Mahmet74' , 'hhcxs*@99' , 'Mahmet' ,'' ,'42112595635' ,'Ryba Sp.z o.o.' );

INSERT INTO kontakt VALUES
( 'Nazar03' , '217078011' , '516172366' ,'Nazar03@o2.pl' ),
( 'Przemko27' , '411777637' , '219058621' ,'Przemko27@gmail.com' ),
( 'Wili65' , '781391787' , '256418182' ,'Wili65@onet.pl' ),
( 'Hartmund24' , '822558684' , '683649848' ,'Hartmund24@gmail.com' ),
( 'Aurela54' , '576621471' , '916369357' ,'Aurela54@amu.edu.pl' ),
( 'Mierycława42' , '769405553' , '996205813' ,'Mierycława42@hotmail.com' ),
( 'Lilla82' , '822362708' , '634034785' ,'Lilla82@interia.pl' ),
( 'Felicytes30' , '196210713' , '646557368' ,'Felicytes30@gmail.com' ),
( 'Rodolf72' , '253028845' , '498853233' ,'Rodolf72@onet.pl' ),
( 'Hamid56' , '979088186' , '907462531' ,'Hamid56@o2.pl' ),
( 'Wasilika11' , '922198975' , '693700054' ,'Wasilika11@o2.pl' ),
( 'Edda16' , '270137615' , '113791829' ,'Edda16@o2.pl' ),
( 'Kiura33' , '190081701' , '385008848' ,'Kiura33@o2.pl' ),
( 'Druzjanna54' , '788812333' , '923114398' ,'Druzjanna54@outlook.com' ),
( 'Praskowia44' , '195815206' , '318835709' ,'Praskowia44@gmail.com' ),
( 'Przemko63' , '468238041' , '739369137' ,'Przemko63@o2.pl' ),
( 'Margaryta24' , '604158330' , '937922048' ,'Margaryta24@gmail.com' ),
( 'Szymeon06' , '120090493' , '483229873' ,'Szymeon06@outlook.com' ),
( 'Tomomasa41' , '420057888' , '359373112' ,'Tomomasa41@outlook.com' ),
( 'Bob44' , '689449596' , '774906732' ,'Bob44@gmail.com' ),
( 'Malik48' , '812744586' , '965949274' ,'Malik48@hotmail.com' ),
( 'Simion81' , '295622091' , '917165061' ,'Simion81@o2.pl' ),
( 'Joachem45' , '154792124' , '118289922' ,'Joachem45@outlook.com' ),
( 'Otilda81' , '947683230' , '482387153' ,'Otilda81@outlook.com' ),
( 'Jacenty74' , '577067797' , '698050635' ,'Jacenty74@hotmail.com' ),
( 'Hellena74' , '298748841' , '890713430' ,'Hellena74@amu.edu.pl' ),
( 'Stamisław14' , '584852539' , '234881590' ,'Stamisław14@o2.pl' ),
( 'Bogodar74' , '934741147' , '698066224' ,'Bogodar74@hotmail.com' ),
( 'Melcyda51' , '611850752' , '592355365' ,'Melcyda51@hotmail.com' ),
( 'Sawery85' , '304646903' , '583211171' ,'Sawery85@amu.edu.pl' ),
( 'Więczesława25' , '402925486' , '439718388' ,'Więczesława25@hotmail.com' ),
( 'Imogena62' , '148421476' , '825075528' ,'Imogena62@outlook.com' ),
( 'Golfrida87' , '413625018' , '199570957' ,'Golfrida87@o2.pl' ),
( 'Serwiliusz01' , '355139605' , '640210323' ,'Serwiliusz01@gmail.com' ),
( 'Boghdan50' , '119861794' , '949389182' ,'Boghdan50@gmail.com' ),
( 'Alim32' , '647941620' , '606999480' ,'Alim32@outlook.com' ),
( 'Margo61' , '301811617' , '393941484' ,'Margo61@interia.pl' ),
( 'Hersz03' , '188682851' , '249733373' ,'Hersz03@outlook.com' ),
( 'Azacja62' , '162922949' , '711270738' ,'Azacja62@onet.pl' ),
( 'Reinert88' , '449941163' , '883746246' ,'Reinert88@gmail.com' ),
( 'Zhanna20' , '125125082' , '601273280' ,'Zhanna20@hotmail.com' ),
( 'Klementyna03' , '791545573' , '178465274' ,'Klementyna03@amu.edu.pl' ),
( 'Bibian45' , '345330582' , '206249262' ,'Bibian45@amu.edu.pl' ),
( 'Frida31' , '280291887' , '116129855' ,'Frida31@o2.pl' ),
( 'Ignaca73' , '482777637' , '927324866' ,'Ignaca73@interia.pl' ),
( 'Lubomierz36' , '114201562' , '977936182' ,'Lubomierz36@interia.pl' ),
( 'Annelisa78' , '312548890' , '882604626' ,'Annelisa78@onet.pl' ),
( 'Ermelinda06' , '211843326' , '370486855' ,'Ermelinda06@onet.pl' ),
( 'Ruggero43' , '874586057' , '174244335' ,'Ruggero43@outlook.com' ),
( 'Mahmet74' , '484017589' , '356249959' ,'Mahmet74@hotmail.com' );