desdemona

sql

Dec 18th, 2012
433
0
Never
Not a member of Pastebin yet? Sign Up, it unlocks many cool features!
MySQL 13.52 KB | None | 0 0
  1.     --CREATE TABLE desdemona (
  2.     --klucz CHAR(10),
  3.     --pole1 VARCHAR(50),
  4.     --nazwaPromocji varchar(150),
  5.     --CONSTRAINT pk_kluczykStrasznieGlowny PRIMARY KEY (klucz, pole1),
  6.     --CONSTRAINT fk_kluczykBardzoObcy FOREIGN KEY (nazwaPromocji) REFERENCES znizki(nazwaPromocji));
  7.     --INSERT INTO desdemona ( klucz, pole1) VALUES ('marcelina' , 'marchweka');
  8.    
  9.     create table pracownicy(
  10.         PESEL varchar(11) not null,
  11.         imie varchar(200) not null,
  12.         nazwisko varchar(200) not null,
  13.         primary key(PESEL)
  14.     ); 
  15.  
  16.     create table przewodnicy(
  17.         PESEL varchar(11) not null references pracownicy(PESEL),
  18.         nrLegitymacji varchar(10) unique not null,
  19.         primary key(PESEL)
  20.     );
  21.        
  22.     create table kasjerzy(
  23.         PESEL varchar(11) not null references pracownicy(PESEL),
  24.         ID integer unique not null ,
  25.         primary key(PESEL)
  26.     );
  27.          
  28.     create table znizki(
  29.         nazwaPromocji varchar(150) primary key,
  30.         wartoscProcentowa tinyint
  31.     );
  32.    
  33.     create table ofertyZoo (
  34.         nazwaOferty varchar(150) primary key,
  35.         cena smallmoney,
  36.         iloscUlgowychWejsciowek tinyint,
  37.         iloscPelnoplatychWejsciowek tinyint
  38.     );
  39.      
  40.     create table punktySprzedazy (
  41.         numerokienka tinyint primary key
  42.     );
  43.        
  44.     create table biletyGrupowe(
  45.         dataZwiedzania date not null,
  46.         godzina time,
  47.         numerBiletu integer not null,
  48.         cenaUslugiPrzewodnika smallmoney,
  49.         iloscUlgowychWejsciowek tinyint,
  50.         iloscPelnoplatychWejsciowek tinyint,
  51.         punktSprzedarzy tinyint not null references punktySprzedazy(numerOkienka),
  52.         przewodnik varchar(11) references przewodnicy(PESEL),
  53.         kasjer varchar(11) not null references kasjerzy(PESEL),
  54.         primary key (dataZwiedzania, numerBiletu)
  55.     );
  56.    
  57.     create table rachunki (
  58.         data date,
  59.         numerWDniu integer,
  60.         cena smallmoney,
  61.         iloscUlgowychWejsciowek tinyint,
  62.         iloscPelnoplatychWejsciowek tinyint,   
  63.         znizka varchar(150) references znizki(nazwaPromocji),
  64.         punktSprzedarzy tinyint not null references punktySprzedazy(numerOkienka),
  65.         kasjer varchar(11) not null references kasjerzy(PESEL),
  66.         dataZwiedzania date,
  67.         numerBiletu integer,
  68.         foreign key (dataZwiedzania, numerBiletu) references biletyGrupowe,
  69.         primary key (numerWdniu, data)
  70.     );
  71.    
  72.    create table pozycjeNaRachunku(
  73.         iloscZakupionych tinyint,
  74.         lp tinyint, --zakladamy, ze rachunek ma mniej niz 200 pozycji
  75.         data date,
  76.         numerWdniu integer,
  77.         cena smallmoney,
  78.         iloscUlgowychWejsciowek tinyint not null,
  79.         iloscPelnoplatychWejsciowek tinyint not null,
  80.         nazwaOferty varchar(150) not null references ofertyZoo(nazwaOferty),       
  81.         foreign key (numerWdniu, data) references rachunki,
  82.         primary key (numerWdniu, data, lp)
  83.     );
  84.        
  85.     create table sponsorzyZoo(
  86.         nazwa varchar(100) primary key,
  87.         kolejnoscNaBilecie tinyint,
  88.         przekazaneFundusze money
  89.     );
  90.    
  91.     create table daneOZwiedzajacych(
  92.         plec varchar(1),
  93.         wiek tinyint,
  94.         kodPocztowy varchar(6),
  95.         lp tinyint,
  96.         dataZwiedzania date,
  97.         numerBiletu integer,
  98.         foreign key (dataZwiedzania, numerBiletu) references biletyGrupowe,
  99.         primary key (lp, numerBiletu, dataZwiedzania)
  100.     );
  101.        
  102.    create table biletDosponsorowal(
  103.         dataZwiedzania date,
  104.         numerBiletu integer,
  105.         foreign key (dataZwiedzania, numerBiletu) references biletyGrupowe,
  106.         nazwaSponsora varchar(100) references sponsorzyZoo(nazwa),
  107.         primary key(dataZwiedzania, numerBiletu, nazwaSponsora)
  108.    );
  109.    
  110.   /*CHECK;*/
  111.  
  112. insert into pracownicy(PESEL, imie, nazwisko)
  113.     values(68062304561, 'Bogdan', 'Bogdanowicz');
  114.    
  115.     insert into pracownicy(PESEL, imie, nazwisko)
  116.     values(70102100312, 'Bogdan', 'Bogdaniak');
  117.    
  118.     insert into pracownicy(PESEL, imie, nazwisko)
  119.     values(89022721456, 'Bogna', 'Bogdanowicz');
  120.    
  121.     insert into pracownicy(PESEL, imie, nazwisko)
  122.     values(87011234512, 'Marceli', 'Bogdan');
  123.    
  124.     insert into pracownicy(PESEL, imie, nazwisko)
  125.     values(50121203213, 'Aureliusz', 'Krzesiwo');
  126.    
  127.     insert into pracownicy(PESEL, imie, nazwisko)
  128.     values(86020205976, 'Adam', 'Kowalski');
  129.    
  130.     insert into pracownicy(PESEL, imie, nazwisko)
  131.     values(75042002132, 'Anna', 'Zajączek');
  132.    
  133.     insert into pracownicy(PESEL, imie, nazwisko)
  134.     values(78032106784, 'Grzegorz', 'Brzęczyszczykiewicz');
  135.    
  136.     insert into przewodnicy(PESEL, nrLegitymacji) values(78032106784, 0101234510);
  137.    
  138.     insert into przewodnicy(PESEL, nrLegitymacji) values(86020205976, 9694929641);
  139.    
  140.     insert into przewodnicy(PESEL, nrLegitymacji) values(75042002132, 9123005671);
  141.    
  142.     insert into kasjerzy(PESEL, ID) values (68062304561, 1);
  143.    
  144.     insert into kasjerzy(PESEL, ID) values (70102100312, 2);
  145.    
  146.     insert into kasjerzy(PESEL, ID) values (89022721456, 3);
  147.    
  148.     insert into kasjerzy(PESEL, ID) values (87011234512, 4);
  149.      
  150.     insert into kasjerzy(PESEL, ID) values (50121203213, 5);
  151.    
  152.     insert into znizki (nazwaPromocji, wartoscProcentowa)
  153.     values('zniżka pracownicza', 40);
  154.    
  155.     insert into znizki (nazwaPromocji, wartoscProcentowa)
  156.     values('wtorkowa', 30);
  157.    
  158.     insert into znizki (nazwaPromocji, wartoscProcentowa)
  159.     values('zasłużony gość', 50);
  160.    
  161.     insert into znizki (nazwaPromocji, wartoscProcentowa)
  162.     values ('odznaka małego zoologa',35);
  163.    
  164.     insert into znizki (nazwaPromocji, wartoscProcentowa)
  165.     values ('bon zniżkowy', 55);
  166.    
  167.     insert into ofertyZoo (nazwaOferty, cena, iloscUlgowychWejsciowek, iloscPelnoplatychWejsciowek)
  168.     values ('pełnopłatny', 15, 0, 1);
  169.    
  170.     insert into ofertyZoo (nazwaOferty, cena, iloscUlgowychWejsciowek, iloscPelnoplatychWejsciowek)
  171.     values ('ulgowy', 7.5, 1, 0);
  172.    
  173.     insert into ofertyZoo (nazwaOferty, cena, iloscUlgowychWejsciowek, iloscPelnoplatychWejsciowek)
  174.     values ('rodzinny', 35, 2,2);
  175.    
  176.     insert into ofertyZoo (nazwaOferty, cena, iloscUlgowychWejsciowek, iloscPelnoplatychWejsciowek)
  177.     values ('grupa szkolna', 130, 20, 2);
  178.    
  179.     insert into ofertyZoo (nazwaOferty, cena, iloscUlgowychWejsciowek, iloscPelnoplatychWejsciowek)
  180.     values ('walentynki 2012', 20, 0, 2);
  181.    
  182.     insert into ofertyZoo (nazwaOferty, cena, iloscUlgowychWejsciowek, iloscPelnoplatychWejsciowek)
  183.     values ('dzień ziemi 2012', 20, 1,1);
  184.    
  185.     insert into punktySprzedazy(numerokienka)
  186.     values(1);
  187.    
  188.     insert into punktySprzedazy(numerokienka)
  189.     values(3);
  190.    
  191.     insert into punktySprzedazy(numerokienka)
  192.     values(2);
  193.    
  194.     insert into punktySprzedazy(numerokienka)
  195.     values(5);
  196.    
  197.     insert into punktySprzedazy(numerokienka)
  198.     values(6);
  199.  
  200.     insert into sponsorzyZoo(nazwa, kolejnoscNaBilecie, przekazaneFundusze)
  201.     values('Pan Henio', 3, 35467973.5);
  202.    
  203.     insert into sponsorzyZoo(nazwa, kolejnoscNaBilecie, przekazaneFundusze)
  204.     values('Firma firma', 2, 99999999.9);
  205.    
  206.     insert into sponsorzyZoo(nazwa, kolejnoscNaBilecie, przekazaneFundusze)
  207.     values ('Amber Gold', 1, 111111111.9);
  208.    
  209.     insert into sponsorzyZoo(nazwa, kolejnoscNaBilecie, przekazaneFundusze)
  210.     values('Farma Hipopotamow', 4, 9876.1);
  211.    
  212.     insert into sponsorzyZoo(nazwa, kolejnoscNaBilecie, przekazaneFundusze)
  213.     values('SP nr 1 w Wąglikowicach', 5, 931.9);
  214.    
  215.    
  216. --po koleji wprowadzanie danych przy zakupie biletu
  217.     insert into rachunki(data, numerWDniu, kasjer, punktSprzedarzy)
  218.     values('2012-12-12', 1, 89022721456, 3);
  219.    
  220.     insert into pozycjeNaRachunku(data, numerWdniu, lp, nazwaOferty, iloscZakupionych, iloscPelnoplatychWejsciowek, iloscUlgowychWejsciowek, cena)
  221.     values('2012-12-12', 1, 1, 'rodzinny', 2, 4, 4, 70.0);
  222.    
  223.     insert into pozycjeNaRachunku(data, numerWdniu, lp, nazwaOferty, iloscZakupionych, iloscPelnoplatychWejsciowek, iloscUlgowychWejsciowek, cena)
  224.     values('2012-12-12', 1, 2, 'pełnopłatny', 1, 1, 0, 15.0);
  225.    
  226.     update  rachunki
  227.     set cena=85.0, iloscUlgowychWejsciowek=4, iloscPelnoplatychWejsciowek=5
  228.     where data='2012-12-12' and numerWDniu=1;
  229.     --jeden rachunek gotowy
  230.    
  231.     insert into rachunki(data, numerWDniu, kasjer, punktSprzedarzy)
  232.     values('2012-12-12', 2, '68062304561', 1);
  233.    
  234.     insert into pozycjeNaRachunku(data, numerWdniu, lp, nazwaOferty, iloscZakupionych, iloscPelnoplatychWejsciowek, iloscUlgowychWejsciowek, cena)
  235.     values('2012-12-12', 2, 1, 'ulgowy', 2, 0, 2, 15.0);
  236.    
  237.     update rachunki
  238.     set cena=15.0, iloscUlgowychWejsciowek=2, iloscPelnoplatychWejsciowek=0
  239.     where data='2012-12-12' and numerWDniu=2;
  240.    
  241.     --oba rachunki w jeden bilet
  242.     insert into biletyGrupowe(dataZwiedzania, numerBiletu, punktSprzedarzy, godzina, iloscPelnoplatychWejsciowek, iloscUlgowychWejsciowek, kasjer)
  243.     values('2012-12-12', 1, 3, '16:15:00', 5, 7, '89022721456');
  244.    
  245.     update rachunki
  246.     set dataZwiedzania='2012-12-12', numerBiletu=1
  247.     where data='2012-12-12' and numerWDniu=1;
  248.    
  249.     update rachunki
  250.     set dataZwiedzania='2012-12-12', numerBiletu=1
  251.     where data='2012-12-12' and numerWDniu=2;
  252.    
  253.     --przypisujemy rachunkom ich bilety zeby nikt nie mogl dostac biletu na podstawie raz juz zrealizowanego rachunku
  254.    
  255.     insert into biletDosponsorowal(dataZwiedzania, numerBiletu, nazwaSponsora)
  256.     values('2012-12-12', 1, 'Amber Gold');
  257.     --wyznaczamy sponsora do biletu, bilet jest drukowany.
  258.    
  259.     insert into daneOZwiedzajacych(dataZwiedzania, numerBiletu, lp, plec, wiek, kodPocztowy)
  260.     values('2012-12-12', 1, 1, 'k', 10, '20-753');
  261.    
  262.     insert into daneOZwiedzajacych(dataZwiedzania, numerBiletu, lp, plec, wiek, kodPocztowy)
  263.     values('2012-12-12', 1, 2, 'm', 20, '20-753');
  264.    
  265.     insert into daneOZwiedzajacych(dataZwiedzania, numerBiletu, lp, plec, wiek, kodPocztowy)
  266.     values('2012-12-12', 1, 3, 'k', 25, '20-753');
  267.    
  268.     insert into daneOZwiedzajacych(dataZwiedzania, numerBiletu, lp, plec, wiek, kodPocztowy)
  269.     values('2012-12-12', 1, 4, 'k', 54, '20-753');
  270.    
  271.     insert into daneOZwiedzajacych(dataZwiedzania, numerBiletu, lp, plec, wiek, kodPocztowy)
  272.     values('2012-12-12', 1, 5, 'm', 51, '20-890');
  273.    
  274.     insert into daneOZwiedzajacych(dataZwiedzania, numerBiletu, lp, plec, wiek, kodPocztowy)
  275.     values('2012-12-12', 1, 6, 'k', 40, '20-120');
  276.    
  277.     insert into daneOZwiedzajacych(dataZwiedzania, numerBiletu, lp, plec, wiek, kodPocztowy)
  278.     values('2012-12-12', 1, 7, 'm', 8, '20-120');
  279.    
  280.     insert into daneOZwiedzajacych(dataZwiedzania, numerBiletu, lp, plec, wiek, kodPocztowy)
  281.     values('2012-12-12', 1, 8, 'k', 8, '20-120');
  282.    
  283.     insert into daneOZwiedzajacych(dataZwiedzania, numerBiletu, lp, plec, wiek, kodPocztowy)
  284.     values('2012-12-12', 1, 9, 'm', 10, '20-753');
  285.    
  286.     insert into daneOZwiedzajacych(dataZwiedzania, numerBiletu, lp, plec, wiek, kodPocztowy)
  287.     values('2012-12-12', 1, 10, 'k', 3, '20-753');
  288.    
  289.     insert into daneOZwiedzajacych(dataZwiedzania, numerBiletu, lp, plec, wiek, kodPocztowy)
  290.     values('2012-12-12', 1, 11, 'm', 8, '20-890');
  291.    
  292.     insert into daneOZwiedzajacych(dataZwiedzania, numerBiletu, lp, plec, wiek, kodPocztowy)
  293.     values('2012-12-12', 1, 12, 'k', 11, '20-890');
  294.    
  295.    
  296.     --nowy rachunek
  297.     insert into rachunki(data, numerWDniu, punktSprzedarzy, kasjer, znizka)
  298.     values('2012-12-12', 3, 6, '50121203213', 'bon zniżkowy')
  299.    
  300.     insert into pozycjeNaRachunku(data, numerWdniu, lp, nazwaOferty, iloscZakupionych, iloscPelnoplatychWejsciowek, iloscUlgowychWejsciowek, cena)
  301.     values('2012-12-12', 3, 1, 'dzień ziemi 2012', 10, 10, 10, 10*20);
  302.    
  303.     update rachunki
  304.     set cena=0.45*(10*20), iloscUlgowychWejsciowek=10, iloscPelnoplatychWejsciowek=10
  305.     where data='2012-12-12' and numerWDniu=3;
  306.    
  307.     insert into biletyGrupowe(dataZwiedzania, numerBiletu, przewodnik, cenaUslugiPrzewodnika, godzina, iloscPelnoplatychWejsciowek, iloscUlgowychWejsciowek, kasjer, punktSprzedarzy)
  308.     values('2012-12-13', 1, '78032106784', 20.0, '10:00', 10, 10, '87011234512', 1);
  309.    
  310.     update rachunki
  311.     set numerBiletu=1, dataZwiedzania='2012-12-13'
  312.     where data='2012-12-12' and numerWDniu=3;
  313.    
  314.     insert biletDosponsorowal(dataZwiedzania, numerBiletu, nazwaSponsora)
  315.     values('2012-12-13', 1, 'SP nr 1 w Wąglikowicach');
  316.    
  317.     insert into daneOZwiedzajacych(dataZwiedzania, numerBiletu, lp, plec, wiek, kodPocztowy)
  318.     values('2012-12-13', 1, 1, 'm', 79, '83-406');
  319.    
  320.     insert into daneOZwiedzajacych(dataZwiedzania, numerBiletu, lp, plec, wiek, kodPocztowy)
  321.     values('2012-12-13', 1, 2, 'k', 78, '83-406');
  322.    
  323.     --nowy rachunek
  324.     insert into rachunki(data, numerWDniu, punktSprzedarzy, kasjer)
  325.     values('2012-12-12', 4, 6, '50121203213')
  326.    
  327.     insert into pozycjeNaRachunku(data, numerWdniu, lp, nazwaOferty, iloscZakupionych, iloscPelnoplatychWejsciowek, iloscUlgowychWejsciowek, cena)
  328.     values('2012-12-12', 4, 1, 'dzień ziemi 2012', 1, 1, 1, 20);
  329.    
  330.     insert into pozycjeNaRachunku(data, numerWdniu, lp, nazwaOferty, iloscZakupionych, iloscPelnoplatychWejsciowek, iloscUlgowychWejsciowek, cena)
  331.     values('2012-12-12', 4, 2, 'rodzinny', 2, 4, 4, 70);
  332.    
  333.    
  334.     update rachunki
  335.     set cena=70+20, iloscUlgowychWejsciowek=5, iloscPelnoplatychWejsciowek=5
  336.     where data='2012-12-12' and numerWDniu=4;
  337.    
  338.     insert into biletyGrupowe(dataZwiedzania, numerBiletu, przewodnik, cenaUslugiPrzewodnika, godzina, iloscPelnoplatychWejsciowek, iloscUlgowychWejsciowek, kasjer, punktSprzedarzy)
  339.     values('2012-12-12', 2, '78032106784', 10.0, '10:00', 5, 5, '87011234512', 1);
  340.    
  341.     update rachunki
  342.     set numerBiletu=2, dataZwiedzania='2012-12-12'
  343.     where data='2012-12-12' and numerWDniu=4;
  344.    
  345.     insert biletDosponsorowal(dataZwiedzania, numerBiletu, nazwaSponsora)
  346.     values('2012-12-12', 2, 'Firma firma');
  347.    
  348.     insert into daneOZwiedzajacych(dataZwiedzania, numerBiletu, lp, plec, wiek, kodPocztowy)
  349.     values('2012-12-12', 2, 1, 'm', 34, '83-400');
  350.    
  351.     insert into daneOZwiedzajacych(dataZwiedzania, numerBiletu, lp, plec, wiek, kodPocztowy)
  352.     values('2012-12-12', 2, 2, 'k', 45, '83-400');
  353.    
  354. drop table biletDosponsorowal;
  355. drop table daneoZwiedzajacych;
  356. drop table sponsorzyZoo;
  357. drop table pozycjeNaRachunku;
  358. drop table rachunki;
  359. drop table biletyGrupowe;
  360. drop table punktySprzedazy;
  361. drop table ofertyZoo;
  362. drop table znizki;
  363. drop table kasjerzy;
  364. drop table przewodnicy;
  365. drop table pracownicy;
Advertisement
Add Comment
Please, Sign In to add comment