--SQL skript pro vytvoreni databaze
--xsimek25,xstain00
--29.3.2015


--STRUCTURE (DDL)
DROP TABLE zakaznici CASCADE CONSTRAINTS;
DROP TABLE pracovnici CASCADE CONSTRAINTS;
DROP TABLE schuzky CASCADE CONSTRAINTS;
DROP TABLE smlouvy CASCADE CONSTRAINTS;
DROP TABLE produkty CASCADE CONSTRAINTS;
DROP TABLE znacky CASCADE CONSTRAINTS;
DROP TABLE ma_na_starost CASCADE CONSTRAINTS;
DROP TABLE patri CASCADE CONSTRAINTS;
DROP TABLE se_tyka CASCADE CONSTRAINTS;

CREATE TABLE zakaznici
{
  rodne_cislo NUMBER(10) NOT NULL CHECK(MOD(rodne_cislo,11)=0),
  jmeno VARCHAR2(50) NOT NULL,
  prijmeni VARCHAR2(50) NOT NULL,
  datum_narozeni DATE,
  pohlavi VARCHAR2(10) CHECK(pohlavi IN('muz','zena')),
  adresa VARCHAR2(255),

  CONSTRAINT zakaznici_pk PRIMARY KEY(rodne_cislo)
};

CREATE TABLE pracovnici
{
  pracovnik_id NUMBER(10) NOT NULL,
  jmeno VARCHAR2(50) NOT NULL,
  prijmeni VARCHAR2(50) NOT NULL,

  CONSTRAINT pracovnici_pk PRIMARY KEY(pracovnik_id)
};

CREATE TABLE schuzky
{
  schuzka_id NUMBER(10) NOT NULL,
  cas DATETIME NOT NULL,
  misto VARCHAR2(255) NOT NULL,
  popis VARCHAR2(255),
  zakaznik_id NUMBER(10) NOT NULL,
  pracovnik_id NUMBER(10) NOT NULL,

  CONSTRAINT pracovnici_pk PRIMARY KEY(pracovnik_id),
  CONSTRAINT schuzka_zakaznik_fk
  FOREIGN KEY(zakaznik_id)
    REFERENCES zakaznici(rodne_cislo),
  CONSTRAINT schuzka_pracovnik_fk
  FOREIGN KEY(pracovnik_id)
    REFERENCES pracovnici(pracovnik_id)
};

CREATE TABLE smlouvy
{
  smlouva_id NUMBER(10) NOT NULL,
  datum_uzavreni DATE NOT NULL,
  datum_konce DATE NOT NULL,
  odber NUMBER(10),
  interval CHECK(interval IN('denne','tydne','mesicne')),
  castka NUMBER(10),
  zakaznik_id NUMBER(10) NOT NULL,
  pracovnik_id NUMBER(10) NOT NULL,
  produkt_id NUMBER(10) NOT NULL,

  CONSTRAINT smlouvy_pk PRIMARY KEY(smlouva_id),
  CONSTRAINT smlouva_zakaznik_fk
  FOREIGN KEY(zakaznik_id)
    REFERENCES zakaznici(rodne_cislo),
  CONSTRAINT smouva_pracovnik_fk
  FOREIGN KEY(pracovnik_id)
    REFERENCES pracovnici(pracovnik_id),
  CONSTRAINT smlouva_produkt_fk
  FOREIGN KEY(produkt_id)
    REFERENCES produkty(produkt_id)
};

CREATE TABLE produkty
{
  produkt_id NUMBER(10) NOT NULL,
  nazev VARCHAR2(255) NOT NULL,
  cena NUMBER(10) NOT NULL,
  znacka_id NUMBER(10) NOT NULL,
  CONSTRAINT produkty_pk PRIMARY KEY(produkt_id),
  CONSTRAINT produkt_znacka_fk
  FOREIGN KEY(znacka_id)
    REFERENCES znacky(znacka_id)
};

CREATE TABLE znacky
{
  znacka_id NUMBER(10) NOT NULL,
  nazev VARCHAR2(255) NOT NULL,
  popis VARCHAR2(255) NOT NULL,
  CONSTRAINT znacky_pk PRIMARY KEY(znacka_id)
};
--CONNECTIONS TABLES
CREATE TABLE ma_na_starost
{
  pracovnik_id NUMBER(10) NOT NULL,
  zakaznik_id NUMBER(10) NOT NULL,
  CONSTRAINT ma_na_starost_pk PRIMARY KEY(pracovnik_id,zakaznik_id)
};

CREATE TABLE patri
{
  pracovnik_id NUMBER(10) NOT NULL,
  znacka_id NUMBER(10) NOT NULL,
  CONSTRAINT patri_pk PRIMARY KEY(pracovnik_id,znacka_id)
};

CREATE TABLE se_tyka
{
  schuzka_id NUMBER(10) NOT NULL,
  produkt_id NUMBER(10) NOT NULL,
  CONSTRAINT se_tyka_pk PRIMARY KEY(schuzka_id,produkt_id)
};

--INSERTS(DML)
INSERT INTO zakaznici VALUES(9102123044,'Martin','Šimek',TO_DATE('12.02.1991','dd.mm.yyyy'),'muz','Rudé armády 199, Jirkov');
INSERT INTO zakaznici VALUES(9102123145,'Radim','Štainer',TO_DATE('12.02.1991','dd.mm.yyyy'),'muz','Přelov,Brno');

INSERT INTO pracovnici VALUES(1,'Martin','Štainer');
INSERT INTO pracovnici VALUES(2,'Radim','Šimek');

INSERT INTO znacky VALUES(1,'BMW','Auto za 500 tisíc Euro a výše');
INSERT INTO znacky VALUES(2,'Apple','Mobily za 1 tisíc Euro a výše');
INSERT INTO znacky VALUES(3,'Louis Vuitton','Tkaničky za 500 tisíc Euro a výše');

INSERT INTO schuzky VALUES(1,TO_DATE('3.5.2015 12:00:00','dd.mm.yyyy hh24:mi:ss'),'Brno-střed','Schůzka s bohatým zákazníkem',9102123044,2);
INSERT INTO schuzky VALUES(2,TO_DATE('29.3.2015 12:00:00','dd.mm.yyyy hh24:mi:ss'),'Praha','Schůzka s chudým zákazníkem',9102123145,1);

INSERT INTO produkty VALUES(1,'BMW M5',500000,1);
INSERT INTO produkty VALUES(2,'Apple iPhone 6',50000,2);

INSERT INTO smlouvy VALUES(1,TO_DATE('1.5.2015 12:00:00','dd.mm.yyyy hh24:mi:ss'),TO_DATE('4.5.2015 12:00:00','dd.mm.yyyy hh24:mi:ss'),5,'denne',15000,9102123044,2,1);
INSERT INTO smlouvy VALUES(1,TO_DATE('5.5.2015 12:00:00','dd.mm.yyyy hh24:mi:ss'),TO_DATE('9.5.2015 12:00:00','dd.mm.yyyy hh24:mi:ss'),7,'tydne',65000,9102123145,1,2);

--INSERTS (CONNECTIONS)
INSERT INTO ma_na_starost VALUES(1,9102123145);
INSERT INTO ma_na_starost VALUES(2,9102123044);

INSERT INTO patri VALUES(1,1);
INSERT INTO patri VALUES(2,2);

INSERT INTO se_tyka VALUES(1,1);
INSERT INTO se_tyka VALUES(2,2);

COMMIT;