Drop table Hodnoceni cascade constraints

Drop table Hodnoceni succeeded.
Drop table Zkusenost cascade constraints

Drop table Zkusenost succeeded.
Drop table Jezdec cascade constraints

Drop table Jezdec succeeded.
Drop table Zavod cascade constraints

Drop table Zavod succeeded.
Drop table Disciplina cascade constraints

Drop table Disciplina succeeded.
Drop table Lokace cascade constraints

Drop table Lokace succeeded.
Drop table Nakup cascade constraints

Drop table Nakup succeeded.
Drop table Obchod cascade constraints

Drop table Obchod succeeded.
Drop table Komponent cascade constraints

Drop table Komponent succeeded.
Drop table Vidlice cascade constraints

Drop table Vidlice succeeded.
Drop table Plast cascade constraints

Drop table Plast succeeded.
Drop table Ram cascade constraints

Drop table Ram succeeded.
Drop table pouziva cascade constraints

Drop table pouziva succeeded.
Drop table ma_rad cascade constraints

Drop table ma_rad succeeded.
Drop table se_ucastnil cascade constraints

Drop table se_ucastnil succeeded.
Create table Hodnoceni (
      Sortiment Integer Not Null,
      Ceny Integer Not Null,
      Privetivost Integer Not Null,
      Slovni_hodnoceni VarChar2(255) Null,
      Ob_Nazev VarChar2(50) Not Null,
      Je_ID_jezdce Integer Not Null,
      Constraint PK_Hodnoceni Primary Key (Ob_Nazev, Je_ID_jezdce)
)

Create table succeeded.
Create table Zkusenost (
      Slovni_hodnoceni VarChar2(255) Null,
      Hodnoceni Integer Not Null,
      Cena_vykon Integer Not Null,
      Ko_Model VarChar2(50) Not Null,
      Ko_Vyrobce VarChar2(50) Not Null,
      Ko_Rok_vyroby Number(4) Not Null,
      Je_ID_jezdce Integer Not Null,
      Constraint PK_Zkusenost Primary Key (Ko_Model, Ko_Vyrobce, Ko_Rok_vyroby, Je_ID_jezdce)
)

Create table succeeded.
Create table Jezdec (
      Jmeno VarChar2(30) Not Null,
      Prijmeni VarChar2(30) Not Null,
      E_mail VarChar2(50) Not Null,
      ID_jezdce Integer Not Null,
      Prezdivka VarChar2(50) Not Null,
      Constraint PK_Jezdec Primary Key (ID_jezdce)
)

Create table succeeded.
Create table Zavod (
      Nazev VarChar2(100) Not Null,
      Startovne Float Null,
      Datum Date Not Null,
      Di_Nazev VarChar2(30) Not Null,
      Lo_Nazev VarChar2(50) Not Null,
      Constraint PK_Zavod Primary Key (Datum, Di_Nazev, Lo_Nazev)
)

Create table succeeded.
Create table Disciplina (
      Nazev VarChar2(30) Not Null,
      Popis VarChar2(255) Null,
      Constraint PK_Disciplina Primary Key (Nazev)
)

Create table succeeded.
Create table Lokace (
      Nazev VarChar2(50) Not Null,
      Umisteni VarChar2(255) Null,
      Popis VarChar2(255) Null,
      Constraint PK_Lokace Primary Key (Nazev)
)

Create table succeeded.
Create table Nakup (
      Cena Float Not Null,
      Datum Date Not Null,
      Ob_Nazev VarChar2(50) Not Null,
      Je_ID_jezdce Integer Not Null,
      Ko_Model VarChar2(50) Not Null,
      Ko_Vyrobce VarChar2(50) Not Null,
      Ko_Rok_vyroby Number(4) Not Null,
      Constraint PK_Nakup Primary Key (Datum, Ob_Nazev, Je_ID_jezdce, Ko_Model, Ko_Vyrobce, Ko_Rok_vyroby)
)

Create table succeeded.
Create table Obchod (
      Nazev VarChar2(50) Not Null,
      Adresa VarChar2(255) Not Null,
      WWW VarChar2(100) Null,
      Constraint PK_Obchod Primary Key (Nazev)
)

Create table succeeded.
Create table Komponent (
      Model VarChar2(50) Not Null,
      Vyrobce VarChar2(50) Not Null,
      Rok_vyroby Number(4) Not Null,
      Constraint PK_Komponent Primary Key (Model, Vyrobce, Rok_vyroby)
)

Create table succeeded.
Create table Vidlice (
      Zdvih Float Not Null,
      Polomer_kola Integer Not Null,
      Ko_Model VarChar2(50) Not Null,
      Ko_Vyrobce VarChar2(50) Not Null,
      Ko_Rok_vyroby Number(4) Not Null,
      Constraint PK_Vidlice Primary Key (Ko_Model, Ko_Vyrobce, Ko_Rok_vyroby)
)

Create table succeeded.
Create table Plast (
      Sirka Float Not Null,
      Polomer Integer Not Null,
      Ko_Model VarChar2(50) Not Null,
      Ko_Vyrobce VarChar2(50) Not Null,
      Ko_Rok_vyroby Number(4) Not Null,
      Constraint PK_Plast Primary Key (Ko_Model, Ko_Vyrobce, Ko_Rok_vyroby)
)

Create table succeeded.
Create table Ram (
      Velikost Float Not Null,
      Polomer_kola Integer Not Null,
      Ko_Model VarChar2(50) Not Null,
      Ko_Vyrobce VarChar2(50) Not Null,
      Ko_Rok_vyroby Number(4) Not Null,
      Constraint PK_Ram Primary Key (Ko_Model, Ko_Vyrobce, Ko_Rok_vyroby)
)

Create table succeeded.
Create table pouziva (
      Role11_ID_jezdce Integer Not Null,
      Role23_Model VarChar2(50) Not Null,
      Role23_Vyrobce VarChar2(50) Not Null,
      Role23_Rok_vyroby Number(4) Not Null,
      Constraint UNQ_pouziva_1 Unique (Role11_ID_jezdce, Role23_Model, Role23_Vyrobce, Role23_Rok_vyroby)
)

Create table succeeded.
Create table ma_rad (
      Role6_Nazev VarChar2(30) Not Null,
      Role3_ID_jezdce Integer Not Null,
      Constraint UNQ_ma_rad_1 Unique (Role6_Nazev, Role3_ID_jezdce)
)

Create table succeeded.
Create table se_ucastnil (
      Role9_Datum Date Not Null,
      Role9_Di_Nazev VarChar2(30) Not Null,
      Role9_Lo_Nazev VarChar2(50) Not Null,
      Role10_ID_jezdce Integer Not Null,
      Constraint UNQ_se_ucastnil_1 Unique (Role9_Datum, Role9_Di_Nazev, Role9_Lo_Nazev, Role10_ID_jezdce)
)

Create table succeeded.
Alter table Hodnoceni add (
      Constraint FK_Hodnoceni_1 Foreign Key (Ob_Nazev) References Obchod(Nazev),
      Constraint FK_Hodnoceni_2 Foreign Key (Je_ID_jezdce) References Jezdec(ID_jezdce)
)

Alter table Hodnoceni succeeded.
Alter table Zkusenost add (
      Constraint FK_Zkusenost_1 Foreign Key (Ko_Model, Ko_Vyrobce, Ko_Rok_vyroby) References Komponent(Model, Vyrobce, Rok_vyroby),
      Constraint FK_Zkusenost_2 Foreign Key (Je_ID_jezdce) References Jezdec(ID_jezdce)
)

Alter table Zkusenost succeeded.
Alter table Zavod add (
      Constraint FK_Zavod_1 Foreign Key (Di_Nazev) References Disciplina(Nazev),
      Constraint FK_Zavod_2 Foreign Key (Lo_Nazev) References Lokace(Nazev)
)

Alter table Zavod succeeded.
Alter table Nakup add (
      Constraint FK_Nakup_1 Foreign Key (Ob_Nazev) References Obchod(Nazev),
      Constraint FK_Nakup_2 Foreign Key (Je_ID_jezdce) References Jezdec(ID_jezdce),
      Constraint FK_Nakup_3 Foreign Key (Ko_Model, Ko_Vyrobce, Ko_Rok_vyroby) References Komponent(Model, Vyrobce, Rok_vyroby)
)

Alter table Nakup succeeded.
Alter table Vidlice add (
      Constraint FK_Vidlice_1 Foreign Key (Ko_Model, Ko_Vyrobce, Ko_Rok_vyroby) References Komponent(Model, Vyrobce, Rok_vyroby)
)

Alter table Vidlice succeeded.
Alter table Plast add (
      Constraint FK_Plast_1 Foreign Key (Ko_Model, Ko_Vyrobce, Ko_Rok_vyroby) References Komponent(Model, Vyrobce, Rok_vyroby)
)

Alter table Plast succeeded.
Alter table Ram add (
      Constraint FK_Ram_1 Foreign Key (Ko_Model, Ko_Vyrobce, Ko_Rok_vyroby) References Komponent(Model, Vyrobce, Rok_vyroby)
)

Alter table Ram succeeded.
Alter table pouziva add (
      Constraint FK_pouziva_1 Foreign Key (Role11_ID_jezdce) References Jezdec(ID_jezdce),
      Constraint FK_pouziva_2 Foreign Key (Role23_Model, Role23_Vyrobce, Role23_Rok_vyroby) References Komponent(Model, Vyrobce, Rok_vyroby)
)

Alter table pouziva succeeded.
Alter table ma_rad add (
      Constraint FK_ma_rad_1 Foreign Key (Role6_Nazev) References Disciplina(Nazev),
      Constraint FK_ma_rad_2 Foreign Key (Role3_ID_jezdce) References Jezdec(ID_jezdce)
)

Alter table ma_rad succeeded.
Alter table se_ucastnil add (
      Constraint FK_se_ucastnil_1 Foreign Key (Role9_Datum, Role9_Di_Nazev, Role9_Lo_Nazev) References Zavod(Datum, Di_Nazev, Lo_Nazev),
      Constraint FK_se_ucastnil_2 Foreign Key (Role10_ID_jezdce) References Jezdec(ID_jezdce)
)

Alter table se_ucastnil succeeded.
Drop table Obchod_obj cascade constraints

Drop table Obchod_obj succeeded.
Drop table Nakup_obj cascade constraints

Drop table Nakup_obj succeeded.
Drop table Komponent_obj cascade constraints

Drop table Komponent_obj succeeded.
Drop table Vidlice_obj cascade constraints

Drop table Vidlice_obj succeeded.
Drop table Ram_obj cascade constraints

Drop table Ram_obj succeeded.
Drop table Plast_obj cascade constraints

Drop table Plast_obj succeeded.
Drop table Zkusenost_obj cascade constraints

Drop table Zkusenost_obj succeeded.
Drop table Zavod_obj cascade constraints

Drop table Zavod_obj succeeded.
Drop table Lokace_obj cascade constraints

Drop table Lokace_obj succeeded.
Drop table Jezdec_obj cascade constraints

Drop table Jezdec_obj succeeded.
Drop table Hodnoceni_obj cascade constraints

Drop table Hodnoceni_obj succeeded.
Drop table Disciplina_obj cascade constraints

Drop table Disciplina_obj succeeded.
Drop table pouziva_obj cascade constraints

Drop table pouziva_obj succeeded.
Drop table se_ucastnil_obj cascade constraints

Drop table se_ucastnil_obj succeeded.
Drop table ma_rad_obj cascade constraints

Drop table ma_rad_obj succeeded.
Drop type Obchod_t FORCE

Drop type Obchod_t succeeded.
Drop type Nakup_t FORCE

Drop type Nakup_t succeeded.
Drop type Komponent_t FORCE

Drop type Komponent_t succeeded.
Drop type Vidlice_t FORCE

Drop type Vidlice_t succeeded.
Drop type Ram_t FORCE

Drop type Ram_t succeeded.
Drop type Plast_t FORCE

Drop type Plast_t succeeded.
Drop type Zkusenost_t FORCE

Drop type Zkusenost_t succeeded.
Drop type Zavod_t FORCE

Drop type Zavod_t succeeded.
Drop type Lokace_t FORCE

Drop type Lokace_t succeeded.
Drop type Jezdec_t FORCE

Drop type Jezdec_t succeeded.
Drop type Hodnoceni_t FORCE

Drop type Hodnoceni_t succeeded.
Drop type Disciplina_t FORCE

Drop type Disciplina_t succeeded.
Drop type pouziva_t FORCE

Drop type pouziva_t succeeded.
Drop type se_ucastnil_t FORCE

Drop type se_ucastnil_t succeeded.
Drop type ma_rad_t FORCE

Drop type ma_rad_t succeeded.
CREATE OR REPLACE TYPE Obchod_t AS OBJECT (
      Nazev VarChar2(50),
      Adresa VarChar2(255),
      WWW VarChar2(100)
);

TYPE Obchod_t Compiled.
CREATE OR REPLACE TYPE Nakup_t AS OBJECT (
      Cena Float,
      Datum Date
);

TYPE Nakup_t Compiled.
CREATE OR REPLACE TYPE Komponent_t AS OBJECT (
      Model VarChar2(50),
      Vyrobce VarChar2(50),
      Rok_vyroby Char(4)
);

TYPE Komponent_t Compiled.
CREATE OR REPLACE TYPE Vidlice_t AS OBJECT (
      Zdvih Float,
      Polomer_kola Integer
);

TYPE Vidlice_t Compiled.
CREATE OR REPLACE TYPE Ram_t AS OBJECT (
      Polomer_kola Integer,
      Velikost Float
);

TYPE Ram_t Compiled.
CREATE OR REPLACE TYPE Plast_t AS OBJECT (
      Polomer Integer,
      Sirka Float
);

TYPE Plast_t Compiled.
CREATE OR REPLACE TYPE Zkusenost_t AS OBJECT (
      Slovni_hodnoceni VarChar2(255),
      Hodnoceni Integer,
      Cena_vykon Integer
);

TYPE Zkusenost_t Compiled.
CREATE OR REPLACE TYPE Zavod_t AS OBJECT (
      Nazev VarChar2(100),
      Startovne Float,
      Datum Date
);

TYPE Zavod_t Compiled.
CREATE OR REPLACE TYPE Lokace_t AS OBJECT (
      Nazev VarChar2(50),
      Umisteni VarChar2(255),
      Popis VarChar2(255)
);

TYPE Lokace_t Compiled.
CREATE OR REPLACE TYPE Jezdec_t AS OBJECT (
      Jmeno VarChar2(30),
      Prijmeni VarChar2(30),
      ID_jezdce Integer,
      Prezdivka VarChar2(50),
      E_mail VarChar2(50)
);

TYPE Jezdec_t Compiled.
CREATE OR REPLACE TYPE Hodnoceni_t AS OBJECT (
      Sortiment Integer,
      Ceny Integer,
      Privetivost Integer,
      Slovni_hodnoceni VarChar2(255)
);

TYPE Hodnoceni_t Compiled.
CREATE OR REPLACE TYPE Disciplina_t AS OBJECT (
      Popis VarChar2(255),
      Nazev VarChar2(30)
);

TYPE Disciplina_t Compiled.
CREATE OR REPLACE TYPE pouziva_t AS OBJECT (
      -- pri pouziti referenci je potreba jen jeden odkaz na komponent
      Role23_Komponent REF Komponent_t
      /*Role23_Model REF Komponent_t,
      Role23_Vyrobce REF Komponent_t,
      Role23_Rok_vyroby REF Komponent_t*/,
      Role11_ID_jezdce REF Jezdec_t
);

TYPE pouziva_t Compiled.
CREATE OR REPLACE TYPE se_ucastnil_t AS OBJECT (
      -- pri pouziti referenci je potreba jen jeden odkaz na zavod
      Role9_Zavod REF Zavod_t
      /*Role9_Datum REF Zavod_t,
      Role9_Di_Nazev REF Disciplina_t,
      Role9_Lo_Nazev REF Lokace_t*/,
      Role10_ID_jezdce REF Jezdec_t
);

TYPE se_ucastnil_t Compiled.
CREATE OR REPLACE TYPE ma_rad_t AS OBJECT (
      Role3_ID_jezdce REF Jezdec_t,
      Role6_Nazev REF Disciplina_t
);

TYPE ma_rad_t Compiled.
ALTER TYPE Nakup_t ADD attribute Ob_Nazev REF Obchod_t CASCADE

ALTER TYPE Nakup_t succeeded.
ALTER TYPE Nakup_t ADD attribute Je_ID_jezdce REF Jezdec_t CASCADE

ALTER TYPE Nakup_t succeeded.
-- pri pouziti referenci je potreba jen jeden odkaz na komponent

ALTER TYPE Nakup_t ADD attribute Ko_Komponent REF Komponent_t CASCADE

ALTER TYPE Nakup_t succeeded.
/*ALTER TYPE Nakup_t ADD attribute Ko_Model REF Komponent_t CASCADE;
ALTER TYPE Nakup_t ADD attribute Ko_Vyrobce REF Komponent_t CASCADE;
ALTER TYPE Nakup_t ADD attribute Ko_Rok_vyroby REF Komponent_t CASCADE;*/

ALTER TYPE Vidlice_t ADD attribute Ko_Komponent REF Komponent_t CASCADE

ALTER TYPE Vidlice_t succeeded.
/*ALTER TYPE Vidlice_t ADD attribute Ko_Model REF Komponent_t CASCADE;
ALTER TYPE Vidlice_t ADD attribute Ko_Vyrobce REF Komponent_t CASCADE;
ALTER TYPE Vidlice_t ADD attribute Ko_Rok_vyroby REF Komponent_t CASCADE;*/

ALTER TYPE Ram_t ADD attribute Ko_Komponent REF Komponent_t CASCADE

ALTER TYPE Ram_t succeeded.
/*ALTER TYPE Ram_t ADD attribute Ko_Model REF Komponent_t CASCADE;
ALTER TYPE Ram_t ADD attribute Ko_Vyrobce REF Komponent_t CASCADE;
ALTER TYPE Ram_t ADD attribute Ko_Rok_vyroby REF Komponent_t CASCADE;*/

ALTER TYPE Plast_t ADD attribute Ko_Komponent REF Komponent_t CASCADE

ALTER TYPE Plast_t succeeded.
/*ALTER TYPE Plast_t ADD attribute Ko_Model REF Komponent_t CASCADE;
ALTER TYPE Plast_t ADD attribute Ko_Vyrobce REF Komponent_t CASCADE;
ALTER TYPE Plast_t ADD attribute Ko_Rok_vyroby REF Komponent_t CASCADE;*/

ALTER TYPE Zkusenost_t ADD attribute Ko_Komponent REF Komponent_t CASCADE

ALTER TYPE Zkusenost_t succeeded.
/*ALTER TYPE Zkusenost_t ADD attribute Ko_Model REF Komponent_t CASCADE;
ALTER TYPE Zkusenost_t ADD attribute Ko_Vyrobce REF Komponent_t CASCADE;
ALTER TYPE Zkusenost_t ADD attribute Ko_Rok_vyroby REF Komponent_t CASCADE;*/

ALTER TYPE Zkusenost_t ADD attribute Je_ID_jezdce REF Jezdec_t CASCADE

ALTER TYPE Zkusenost_t succeeded.
ALTER TYPE Zavod_t ADD attribute Di_Nazev REF Disciplina_t CASCADE

ALTER TYPE Zavod_t succeeded.
ALTER TYPE Zavod_t ADD attribute Lo_Nazev REF Lokace_t CASCADE

ALTER TYPE Zavod_t succeeded.
ALTER TYPE Hodnoceni_t ADD attribute Ob_Nazev REF Obchod_t CASCADE

ALTER TYPE Hodnoceni_t succeeded.
ALTER TYPE Hodnoceni_t ADD attribute Je_ID_jezdce REF Jezdec_t CASCADE

ALTER TYPE Hodnoceni_t succeeded.
CREATE TABLE Obchod_obj OF Obchod_t (
      Nazev NOT NULL UNIQUE,
      Adresa NOT NULL
)

CREATE TABLE succeeded.
CREATE TABLE Nakup_obj OF Nakup_t (
      Cena NOT NULL,
      Datum NOT NULL,
      Ob_Nazev NOT NULL,
      Je_ID_jezdce NOT NULL,
      -- pri pouziti referenci je potreba jen jeden odkaz na komponent
      Ko_Komponent NOT NULL
      /*Ko_Model NOT NULL,
      Ko_Vyrobce NOT NULL,
      Ko_Rok_vyroby NOT NULL*/
)

CREATE TABLE succeeded.
CREATE TABLE Komponent_obj OF Komponent_t (
      Model NOT NULL,
      Vyrobce NOT NULL,
      Rok_vyroby NOT NULL,
      Unique(Model, Vyrobce, Rok_vyroby)
)

CREATE TABLE succeeded.
CREATE TABLE Vidlice_obj OF Vidlice_t (
      Zdvih NOT NULL,
      Polomer_kola NOT NULL,
      -- pri pouziti referenci je potreba jen jeden odkaz na komponent
      Ko_Komponent NOT NULL
      /*Ko_Model NOT NULL,
      Ko_Vyrobce NOT NULL,
      Ko_Rok_vyroby NOT NULL*/
)

CREATE TABLE succeeded.
CREATE TABLE Ram_obj OF Ram_t (
      Polomer_kola NOT NULL,
      Velikost NOT NULL,
      -- pri pouziti referenci je potreba jen jeden odkaz na komponent
      Ko_Komponent NOT NULL
      /*Ko_Model NOT NULL,
      Ko_Vyrobce NOT NULL,
      Ko_Rok_vyroby NOT NULL*/
)

CREATE TABLE succeeded.
CREATE TABLE Plast_obj OF Plast_t (
      Polomer NOT NULL,
      Sirka NOT NULL,
      -- pri pouziti referenci je potreba jen jeden odkaz na komponent
      Ko_Komponent NOT NULL
      /*Ko_Model NOT NULL,
      Ko_Vyrobce NOT NULL,
      Ko_Rok_vyroby NOT NULL*/
)

CREATE TABLE succeeded.
CREATE TABLE Zkusenost_obj OF Zkusenost_t (
      Hodnoceni NOT NULL,
      Cena_vykon NOT NULL,
      -- pri pouziti referenci je potreba jen jeden odkaz na komponent
      Ko_Komponent NOT NULL
      /*Ko_Model NOT NULL,
      Ko_Vyrobce NOT NULL,
      Ko_Rok_vyroby NOT NULL*/,
      Je_ID_jezdce NOT NULL
)

CREATE TABLE succeeded.
CREATE TABLE Zavod_obj OF Zavod_t (
      Nazev NOT NULL,
      Datum NOT NULL UNIQUE,
      Di_Nazev NOT NULL,
      Lo_Nazev NOT NULL
)

CREATE TABLE succeeded.
CREATE TABLE Lokace_obj OF Lokace_t (
      Nazev NOT NULL UNIQUE
)

CREATE TABLE succeeded.
CREATE TABLE Jezdec_obj OF Jezdec_t (
      Jmeno NOT NULL,
      Prijmeni NOT NULL,
      ID_jezdce NOT NULL UNIQUE,
      Prezdivka NOT NULL,
      E_mail NOT NULL
)

CREATE TABLE succeeded.
CREATE TABLE Hodnoceni_obj OF Hodnoceni_t (
      Sortiment NOT NULL,
      Ceny NOT NULL,
      Privetivost NOT NULL,
      Ob_Nazev NOT NULL,
      Je_ID_jezdce NOT NULL
)

CREATE TABLE succeeded.
CREATE TABLE Disciplina_obj OF Disciplina_t (
      Nazev NOT NULL UNIQUE
)

CREATE TABLE succeeded.
CREATE TABLE pouziva_obj OF pouziva_t (
      -- pri pouziti referenci je potreba jen jeden odkaz na komponent
      Role23_Komponent NOT NULL
      /*Role23_Model NOT NULL,
      Role23_Vyrobce NOT NULL,
      Role23_Rok_vyroby NOT NULL*/,
      Role11_ID_jezdce NOT NULL
)

CREATE TABLE succeeded.
CREATE TABLE se_ucastnil_obj OF se_ucastnil_t (
      -- pri pouziti referenci je potreba jen jeden odkaz na zavod
      Role9_Zavod NOT NULL
      /*Role9_Datum NOT NULL,
      Role9_Di_Nazev NOT NULL,
      Role9_Lo_Nazev NOT NULL*/,
      Role10_ID_jezdce NOT NULL
)

CREATE TABLE succeeded.
CREATE TABLE ma_rad_obj OF ma_rad_t (
      Role3_ID_jezdce NOT NULL,
      Role6_Nazev NOT NULL
)

CREATE TABLE succeeded.
ALTER TABLE Nakup_obj ADD FOREIGN KEY (Ob_Nazev) REFERENCES Obchod_obj

ALTER TABLE Nakup_obj succeeded.
ALTER TABLE Nakup_obj ADD FOREIGN KEY (Je_ID_jezdce) REFERENCES Jezdec_obj

ALTER TABLE Nakup_obj succeeded.
-- pri pouziti referenci je potreba jen jeden odkaz na komponent

ALTER TABLE Nakup_obj ADD FOREIGN KEY (Ko_Komponent) REFERENCES Komponent_obj

ALTER TABLE Nakup_obj succeeded.
/*ALTER TABLE Nakup_obj ADD FOREIGN KEY (Ko_Model) REFERENCES Komponent_obj;
ALTER TABLE Nakup_obj ADD FOREIGN KEY (Ko_Vyrobce) REFERENCES Komponent_obj;
ALTER TABLE Nakup_obj ADD FOREIGN KEY (Ko_Rok_vyroby) REFERENCES Komponent_obj;*/

ALTER TABLE Vidlice_obj ADD FOREIGN KEY (Ko_Komponent) REFERENCES Komponent_obj

ALTER TABLE Vidlice_obj succeeded.
/*ALTER TABLE Vidlice_obj ADD FOREIGN KEY (Ko_Model) REFERENCES Komponent_obj;
ALTER TABLE Vidlice_obj ADD FOREIGN KEY (Ko_Vyrobce) REFERENCES Komponent_obj;
ALTER TABLE Vidlice_obj ADD FOREIGN KEY (Ko_Rok_vyroby) REFERENCES Komponent_obj;*/

ALTER TABLE Ram_obj ADD FOREIGN KEY (Ko_Komponent) REFERENCES Komponent_obj

ALTER TABLE Ram_obj succeeded.
/*ALTER TABLE Ram_obj ADD FOREIGN KEY (Ko_Model) REFERENCES Komponent_obj;
ALTER TABLE Ram_obj ADD FOREIGN KEY (Ko_Vyrobce) REFERENCES Komponent_obj;
ALTER TABLE Ram_obj ADD FOREIGN KEY (Ko_Rok_vyroby) REFERENCES Komponent_obj;*/

ALTER TABLE Plast_obj ADD FOREIGN KEY (Ko_Komponent) REFERENCES Komponent_obj

ALTER TABLE Plast_obj succeeded.
/*ALTER TABLE Plast_obj ADD FOREIGN KEY (Ko_Model) REFERENCES Komponent_obj;
ALTER TABLE Plast_obj ADD FOREIGN KEY (Ko_Vyrobce) REFERENCES Komponent_obj;
ALTER TABLE Plast_obj ADD FOREIGN KEY (Ko_Rok_vyroby) REFERENCES Komponent_obj;*/

ALTER TABLE Zkusenost_obj ADD FOREIGN KEY (Ko_Komponent) REFERENCES Komponent_obj

ALTER TABLE Zkusenost_obj succeeded.
/*ALTER TABLE Zkusenost_obj ADD FOREIGN KEY (Ko_Model) REFERENCES Komponent_obj;
ALTER TABLE Zkusenost_obj ADD FOREIGN KEY (Ko_Vyrobce) REFERENCES Komponent_obj;
ALTER TABLE Zkusenost_obj ADD FOREIGN KEY (Ko_Rok_vyroby) REFERENCES Komponent_obj;*/

ALTER TABLE Zkusenost_obj ADD FOREIGN KEY (Je_ID_jezdce) REFERENCES Jezdec_obj

ALTER TABLE Zkusenost_obj succeeded.
ALTER TABLE Zavod_obj ADD FOREIGN KEY (Di_Nazev) REFERENCES Disciplina_obj

ALTER TABLE Zavod_obj succeeded.
ALTER TABLE Zavod_obj ADD FOREIGN KEY (Lo_Nazev) REFERENCES Lokace_obj

ALTER TABLE Zavod_obj succeeded.
ALTER TABLE Hodnoceni_obj ADD FOREIGN KEY (Ob_Nazev) REFERENCES Obchod_obj

ALTER TABLE Hodnoceni_obj succeeded.
ALTER TABLE Hodnoceni_obj ADD FOREIGN KEY (Je_ID_jezdce) REFERENCES Jezdec_obj

ALTER TABLE Hodnoceni_obj succeeded.
ALTER TABLE pouziva_obj ADD FOREIGN KEY (Role23_Komponent) REFERENCES Komponent_obj

ALTER TABLE pouziva_obj succeeded.
/*ALTER TABLE pouziva_obj ADD FOREIGN KEY (Role23_Model) REFERENCES Komponent_obj;
ALTER TABLE pouziva_obj ADD FOREIGN KEY (Role23_Vyrobce) REFERENCES Komponent_obj;
ALTER TABLE pouziva_obj ADD FOREIGN KEY (Role23_Rok_vyroby) REFERENCES Komponent_obj;*/

ALTER TABLE pouziva_obj ADD FOREIGN KEY (Role11_ID_jezdce) REFERENCES Jezdec_obj

ALTER TABLE pouziva_obj succeeded.
ALTER TABLE se_ucastnil_obj ADD FOREIGN KEY (Role9_Zavod) REFERENCES Zavod_obj

ALTER TABLE se_ucastnil_obj succeeded.
/*ALTER TABLE se_ucastnil_obj ADD FOREIGN KEY (Role9_Datum) REFERENCES Zavod_obj;
ALTER TABLE se_ucastnil_obj ADD FOREIGN KEY (Role9_Di_Nazev) REFERENCES Disciplina_obj;
ALTER TABLE se_ucastnil_obj ADD FOREIGN KEY (Role9_Lo_Nazev) REFERENCES Lokace_obj;*/

ALTER TABLE se_ucastnil_obj ADD FOREIGN KEY (Role10_ID_jezdce) REFERENCES Jezdec_obj

ALTER TABLE se_ucastnil_obj succeeded.
ALTER TABLE ma_rad_obj ADD FOREIGN KEY (Role3_ID_jezdce) REFERENCES Jezdec_obj

ALTER TABLE ma_rad_obj succeeded.
ALTER TABLE ma_rad_obj ADD FOREIGN KEY (Role6_Nazev) REFERENCES Disciplina_obj

ALTER TABLE ma_rad_obj succeeded.
/* ****************************************
 * NAPLNENI TABULEK
 * **************************************** */

CREATE OR REPLACE PROCEDURE napln_tabulky
AS
BEGIN
  /* vymaz�n� obsahu v�ech tabulek */
  DELETE FROM pouziva;
  DELETE FROM se_ucastnil;
  DELETE FROM ma_rad;
  DELETE FROM zkusenost;
  DELETE FROM hodnoceni;
  DELETE FROM nakup;
  DELETE FROM zavod;
  
  DELETE FROM lokace;
  DELETE FROM disciplina;
  DELETE FROM jezdec;
  DELETE FROM ram;
  DELETE FROM vidlice;
  DELETE FROM plast;
  DELETE FROM komponent;
  DELETE FROM obchod;
  COMMIT;
  
  /* tabulka LOKACE */
  INSERT INTO lokace (nazev, umisteni, popis) VALUES ('BMX Bohnice', 'Praha', '');
  INSERT INTO lokace (nazev, umisteni, popis) VALUES ('BMX �epy', 'Praha', '');
  INSERT INTO lokace (nazev, umisteni, popis) VALUES ('4X Kaplice', 'Kaplice', '');
  INSERT INTO lokace (nazev, umisteni, popis) VALUES ('4X Rokytnice', 'Rokytnice nad Jizerou', '');
  INSERT INTO lokace (nazev, umisteni, popis) VALUES ('4X Litom��ice', 'Litom��ice', '');
  INSERT INTO lokace (nazev, umisteni, popis) VALUES ('DH �pindl', '�pindler�v Ml�n', '');
  INSERT INTO lokace (nazev, umisteni, popis) VALUES ('�tvanice', 'Praha', 'Streetov� park.');
  INSERT INTO lokace (nazev, umisteni, popis) VALUES ('To�n�', 'Praha', 'Singletrack v lese.');
  INSERT INTO lokace (nazev, umisteni, popis) VALUES ('Je�t�d', 'Liberec', 'Sjezdov� tra� a dirty.');
  INSERT INTO lokace (nazev, umisteni, popis) VALUES ('Monte B�', 'Brno', 'Tra� vhodn� pro dual.');
  COMMIT;
  
  /* tabulka DISCIPLINA */
  INSERT INTO disciplina (nazev, popis) VALUES ('4X', 'FourCross - �ty�i jezdci v jedn� dr�ze na krat��m svahu, skoky, klopen� zat��ky. ');
  INSERT INTO disciplina (nazev, popis) VALUES ('DH', 'Sjezd (DownHill) - jezd� se po jednom, m��� se �as, jede se po vyzna�en� trase s dlouh�ho kopce se skoky.');
  INSERT INTO disciplina (nazev, popis) VALUES ('XC', 'CrossCountry - jezdc� se dokola na vyzna�en� trase vedouc� v�t�inou lesem.');
  INSERT INTO disciplina (nazev, popis) VALUES ('FR', 'FreeRide - voln� j�zda p��rodou se skoky a dropy.');
  INSERT INTO disciplina (nazev, popis) VALUES ('BMX', 'Bikros - a� osm jezdc� na dr�ze um�st�n� na rovin�, skoky, klopen� zat��ky.');
  INSERT INTO disciplina (nazev, popis) VALUES ('Dirt', 'N�kolik velk�ch skok� um�st�n�ch za sebou, hodnot� se kvalita triku proveden�ho ve vzduchu.');
  INSERT INTO disciplina (nazev, popis) VALUES ('Flat', 'FlatLand - jezdc� se na rovn� �tvercov� plo�e, hodnot� se kvalita p�edveden�ch trik�.');
  INSERT INTO disciplina (nazev, popis) VALUES ('Street', 'Jezd� se ve speci�ln�m street-parku se skoky, z�bradl�mi, plo�inami.');
  INSERT INTO disciplina (nazev, popis) VALUES ('Vert', 'J�zda ve vertik�ln� ramp�.');
  INSERT INTO disciplina (nazev, popis) VALUES ('MTB Freestyle', 'Um�le vytvo�en� tra� s velk�mi skoky, dropy a wallridy.');
  COMMIT;
  
  /* tabulka JEZDEC */
  INSERT INTO jezdec (ID_jezdce, jmeno, prijmeni, prezdivka, e_mail) VALUES ('1', 'Marek', 'Handlu', 'Mareen', 'mareen@imail.cz');
  INSERT INTO jezdec (ID_jezdce, jmeno, prijmeni, prezdivka, e_mail) VALUES ('2', 'Michal', 'Andy', 'Bandy', 'bandaska@imail.cz');
  INSERT INTO jezdec (ID_jezdce, jmeno, prijmeni, prezdivka, e_mail) VALUES ('3', 'Jan', 'Schusti', 'Jenda', 'schusti@imail.cz');
  INSERT INTO jezdec (ID_jezdce, jmeno, prijmeni, prezdivka, e_mail) VALUES ('4', 'Jakub', 'Anton', 'An��k', 'antak@imail.com');
  INSERT INTO jezdec (ID_jezdce, jmeno, prijmeni, prezdivka, e_mail) VALUES ('5', 'Tom�', 'Andy', 'Malej Bandy', 'tom@imail.cz');
  INSERT INTO jezdec (ID_jezdce, jmeno, prijmeni, prezdivka, e_mail) VALUES ('6', 'Jakub', 'R�a', 'Xom', 'xom@imail.cz');
  INSERT INTO jezdec (ID_jezdce, jmeno, prijmeni, prezdivka, e_mail) VALUES ('7', '�t�p�n', 'Grundl', 'GrindMaster', 'grindmaster@imail.cz');
  INSERT INTO jezdec (ID_jezdce, jmeno, prijmeni, prezdivka, e_mail) VALUES ('8', 'Pavel', 'Herink', 'Pafka', 'pafka@imail.cz');
  INSERT INTO jezdec (ID_jezdce, jmeno, prijmeni, prezdivka, e_mail) VALUES ('9', 'Michal', 'Benest', 'Mich�l', 'michal@imail.cz');
  INSERT INTO jezdec (ID_jezdce, jmeno, prijmeni, prezdivka, e_mail) VALUES ('10', 'Martin', 'Hol�', 'Hobit', 'hobit@imail.cz');
  COMMIT;
  
  /* tabulka KOMPONENT */
  INSERT INTO komponent (vyrobce, model, rok_vyroby) VALUES ('Marin', 'BearValley', 1998);
  INSERT INTO komponent (vyrobce, model, rok_vyroby) VALUES ('RaceBike', 'Bandit DS', 2002);
  INSERT INTO komponent (vyrobce, model, rok_vyroby) VALUES ('Blackhorse', 'Blackhorse', 2003);
  INSERT INTO komponent (vyrobce, model, rok_vyroby) VALUES ('LeaderFox', 'Dragstar', 2003);
  INSERT INTO komponent (vyrobce, model, rok_vyroby) VALUES ('Specialized', 'P2', 2005);
  
  INSERT INTO komponent (vyrobce, model, rok_vyroby) VALUES ('Manitou', 'X-Fly', 2002);
  INSERT INTO komponent (vyrobce, model, rok_vyroby) VALUES ('Marzocchi', 'DirtJumper III', 2004);
  INSERT INTO komponent (vyrobce, model, rok_vyroby) VALUES ('Marzocchi', 'DirtJumper II', 2005);
  INSERT INTO komponent (vyrobce, model, rok_vyroby) VALUES ('Manitou', 'Clevet', 2003);
  INSERT INTO komponent (vyrobce, model, rok_vyroby) VALUES ('RockShox', 'Judy', 2000);
  INSERT INTO komponent (vyrobce, model, rok_vyroby) VALUES ('RockShox', 'Quadra', 1998);
  
  INSERT INTO komponent (vyrobce, model, rok_vyroby) VALUES ('Maxxis', 'High Roller', 2004);
  INSERT INTO komponent (vyrobce, model, rok_vyroby) VALUES ('Maxxis', 'High Roller', 2003);
  INSERT INTO komponent (vyrobce, model, rok_vyroby) VALUES ('Maxxis', 'Minion DH', 2004);
  INSERT INTO komponent (vyrobce, model, rok_vyroby) VALUES ('Nokian', 'Rocker', 2003);
  INSERT INTO komponent (vyrobce, model, rok_vyroby) VALUES ('Maxxis', 'Bling Bling', 2003);
  INSERT INTO komponent (vyrobce, model, rok_vyroby) VALUES ('Tioga', 'Factory DH', 2000);
  COMMIT;
  
  /* tabulka RAM (podtyp entity KOMPONENT) */
  INSERT INTO ram (ko_vyrobce, ko_model, ko_rok_vyroby, velikost, polomer_kola) VALUES ('Marin', 'BearValley', 1998, 14, 26);
  INSERT INTO ram (ko_vyrobce, ko_model, ko_rok_vyroby, velikost, polomer_kola) VALUES ('RaceBike', 'Bandit DS', 2002, 14, 26);
  INSERT INTO ram (ko_vyrobce, ko_model, ko_rok_vyroby, velikost, polomer_kola) VALUES ('Blackhorse', 'Blackhorse', 2003, 13, 26);
  INSERT INTO ram (ko_vyrobce, ko_model, ko_rok_vyroby, velikost, polomer_kola) VALUES ('LeaderFox', 'Dragstar', 2003, 16, 26);
  INSERT INTO ram (ko_vyrobce, ko_model, ko_rok_vyroby, velikost, polomer_kola) VALUES ('Specialized', 'P2', 2005, 13, 26);
  COMMIT;
  
  /* tabulka VIDLICE (podtyp entity KOMPONENT) */
  INSERT INTO vidlice (ko_vyrobce, ko_model, ko_rok_vyroby, zdvih, polomer_kola) VALUES ('Manitou', 'X-Fly', 2002, 10, 26);             
  INSERT INTO vidlice (ko_vyrobce, ko_model, ko_rok_vyroby, zdvih, polomer_kola) VALUES ('Marzocchi', 'DirtJumper III', 2004, 13, 26);  
  INSERT INTO vidlice (ko_vyrobce, ko_model, ko_rok_vyroby, zdvih, polomer_kola) VALUES ('Marzocchi', 'DirtJumper II', 2005, 8, 26);   
  INSERT INTO vidlice (ko_vyrobce, ko_model, ko_rok_vyroby, zdvih, polomer_kola) VALUES ('Manitou', 'Clevet', 2003, 12, 26);            
  INSERT INTO vidlice (ko_vyrobce, ko_model, ko_rok_vyroby, zdvih, polomer_kola) VALUES ('RockShox', 'Judy', 2000, 10, 26);             
  INSERT INTO vidlice (ko_vyrobce, ko_model, ko_rok_vyroby, zdvih, polomer_kola) VALUES ('RockShox', 'Quadra', 1998, 6, 26);           
  COMMIT;
  
  /* tabulka PLAST (podtyp entity KOMPONENT) */
  INSERT INTO plast (ko_vyrobce, ko_model, ko_rok_vyroby, sirka, polomer) VALUES ('Maxxis', 'High Roller', 2004, 2.3, 26); 
  INSERT INTO plast (ko_vyrobce, ko_model, ko_rok_vyroby, sirka, polomer) VALUES ('Maxxis', 'High Roller', 2003, 2.3, 26);            
  INSERT INTO plast (ko_vyrobce, ko_model, ko_rok_vyroby, sirka, polomer) VALUES ('Maxxis', 'Minion DH', 2004, 2.5, 26);              
  INSERT INTO plast (ko_vyrobce, ko_model, ko_rok_vyroby, sirka, polomer) VALUES ('Nokian', 'Rocker', 2003, 2.5, 26);                 
  INSERT INTO plast (ko_vyrobce, ko_model, ko_rok_vyroby, sirka, polomer) VALUES ('Maxxis', 'Bling Bling', 2003, 2.3, 26);            
  INSERT INTO plast (ko_vyrobce, ko_model, ko_rok_vyroby, sirka, polomer) VALUES ('Tioga', 'Factory DH', 2000, 2.1, 26);              
  COMMIT;
  
  /* tabulka OBCHOD */
  INSERT INTO obchod (nazev, adresa, www) VALUES ('Dookie', 'Seifertova 27, Praha 2', 'www.dookie.cz');
  INSERT INTO obchod (nazev, adresa, www) VALUES ('901 Bikes', 'Lidick� 35, Praha 1', '');
  INSERT INTO obchod (nazev, adresa, www) VALUES ('Katmar', 'Vltavsk� 103, Praha 6', '');
  INSERT INTO obchod (nazev, adresa, www) VALUES ('Bike Kellys Seky', 'Pod Nuselsk�m mostem 32, Praha 4', '');
  INSERT INTO obchod (nazev, adresa, www) VALUES ('Madla Sport', 'Drahenick� 10, Blatn�', 'www.madla.cz');
  COMMIT;
  
  /* tabulka ZAVOD (identifika�n� z�vis� na LOKACE a DISCIPLINA) */
  INSERT INTO zavod (nazev, di_nazev, lo_nazev, datum, startovne) VALUES ('Elimin�tor', '4X', '4X Litom��ice', '25-05-2005', '100');
  INSERT INTO zavod (nazev, di_nazev, lo_nazev, datum, startovne) VALUES ('Czech 4X Cup Rokytnice', '4X', '4X Rokytnice', '16-07-2005', '');
  INSERT INTO zavod (nazev, di_nazev, lo_nazev, datum, startovne) VALUES ('Czech DH Cup �pindler�v Ml�n', 'DH', 'DH �pindl', '09-11-2005', '80');
  INSERT INTO zavod (nazev, di_nazev, lo_nazev, datum, startovne) VALUES ('Mystic Cup', 'Street', '�tvanice', '31-03-2005', '');
  INSERT INTO zavod (nazev, di_nazev, lo_nazev, datum, startovne) VALUES ('FreeRide na Je�t�du', 'FR', 'Je�t�d', '25-08-2005', '200');
  COMMIT;
  
  /* tabulka ZKUSENOST (identifika�n� z�vis� na JEZDEC a KOMPONENT) */
  INSERT INTO zkusenost (je_id_jezdce, ko_vyrobce, ko_model, ko_rok_vyroby, cena_vykon, hodnoceni, slovni_hodnoceni) VALUES ('1', 'Marin', 'BearValley', 1998, '2', '2', 'Na XC v�born� r�m, lze pou��t i na freestylov� za��tky.');
  INSERT INTO zkusenost (je_id_jezdce, ko_vyrobce, ko_model, ko_rok_vyroby, cena_vykon, hodnoceni, slovni_hodnoceni) VALUES ('1', 'Marzocchi', 'DirtJumper III', 2004, '1', '2', 'Vydr��, ale je t�k� a nem� moc dobr� chod.');
  INSERT INTO zkusenost (je_id_jezdce, ko_vyrobce, ko_model, ko_rok_vyroby, cena_vykon, hodnoceni, slovni_hodnoceni) VALUES ('1', 'Maxxis', 'Minion DH', 2004, '1', '1', '');
  INSERT INTO zkusenost (je_id_jezdce, ko_vyrobce, ko_model, ko_rok_vyroby, cena_vykon, hodnoceni, slovni_hodnoceni) VALUES ('1', 'Maxxis', 'High Roller', 2004, '2', '2', 'V zat��k�ch to nen� ono.');
  INSERT INTO zkusenost (je_id_jezdce, ko_vyrobce, ko_model, ko_rok_vyroby, cena_vykon, hodnoceni, slovni_hodnoceni) VALUES ('3', 'Specialized', 'P2', 2005, '2', '1', '');
  INSERT INTO zkusenost (je_id_jezdce, ko_vyrobce, ko_model, ko_rok_vyroby, cena_vykon, hodnoceni, slovni_hodnoceni) VALUES ('6', 'Nokian', 'Rocker', 2003, '4', '2', '');                 
  INSERT INTO zkusenost (je_id_jezdce, ko_vyrobce, ko_model, ko_rok_vyroby, cena_vykon, hodnoceni, slovni_hodnoceni) VALUES ('1', 'RockShox', 'Judy', 2000, '2', '2', '');
  INSERT INTO zkusenost (je_id_jezdce, ko_vyrobce, ko_model, ko_rok_vyroby, cena_vykon, hodnoceni, slovni_hodnoceni) VALUES ('3', 'Marzocchi', 'DirtJumper II', 2005, '1', '1', ''); 
  COMMIT;
  
  /* tabulka HODNOCENI (identifika�n� z�visl� na JEZDEC a OBCHOD) */
  INSERT INTO hodnoceni (je_id_jezdce, ob_nazev, sortiment, ceny, privetivost, slovni_hodnoceni) VALUES ('1', 'Dookie', '2', '2', '1', 'Pohodovej majitel.');
  INSERT INTO hodnoceni (je_id_jezdce, ob_nazev, sortiment, ceny, privetivost, slovni_hodnoceni) VALUES ('1', 'Katmar', '3', '2', '4', '');
  INSERT INTO hodnoceni (je_id_jezdce, ob_nazev, sortiment, ceny, privetivost, slovni_hodnoceni) VALUES ('3', 'Katmar', '3', '3', '4', '');
  INSERT INTO hodnoceni (je_id_jezdce, ob_nazev, sortiment, ceny, privetivost, slovni_hodnoceni) VALUES ('1', '901 Bikes', '1', '3', '2', '');
  INSERT INTO hodnoceni (je_id_jezdce, ob_nazev, sortiment, ceny, privetivost, slovni_hodnoceni) VALUES ('4', 'Dookie', '2', '1', '1', '');
  INSERT INTO hodnoceni (je_id_jezdce, ob_nazev, sortiment, ceny, privetivost, slovni_hodnoceni) VALUES ('3', 'Madla Sport', '4', '1', '3', '');
  INSERT INTO hodnoceni (je_id_jezdce, ob_nazev, sortiment, ceny, privetivost, slovni_hodnoceni) VALUES ('6', 'Dookie', '2', '2', '1', '');
  COMMIT;
  
  /* tabulka NAKUP (identifika�n� z�visl� na JEZDEC a OBCHOD a KOMPONENT) */
  INSERT INTO nakup (je_id_jezdce, ob_nazev, ko_vyrobce, ko_model, ko_rok_vyroby, datum, cena) VALUES ('1', 'Dookie', 'Marzocchi', 'DirtJumper III', 2004, '29-03-2004', '10099');
  INSERT INTO nakup (je_id_jezdce, ob_nazev, ko_vyrobce, ko_model, ko_rok_vyroby, datum, cena) VALUES ('1', 'Dookie', 'Maxxis', 'Minion DH', 2004, '29-03-2004', '450');
  INSERT INTO nakup (je_id_jezdce, ob_nazev, ko_vyrobce, ko_model, ko_rok_vyroby, datum, cena) VALUES ('1', '901 Bikes', 'Maxxis', 'Minion DH', 2004, '29-03-2004', '600');
  INSERT INTO nakup (je_id_jezdce, ob_nazev, ko_vyrobce, ko_model, ko_rok_vyroby, datum, cena) VALUES ('3', 'Madla Sport', 'Specialized', 'P2', 2005, '10-09-2005', '18000');
  INSERT INTO nakup (je_id_jezdce, ob_nazev, ko_vyrobce, ko_model, ko_rok_vyroby, datum, cena) VALUES ('3', 'Madla Sport', 'Maxxis', 'High Roller', 2004, '10-09-2005', '400');
  INSERT INTO nakup (je_id_jezdce, ob_nazev, ko_vyrobce, ko_model, ko_rok_vyroby, datum, cena) VALUES ('2', '901 Bikes', 'Blackhorse', 'Blackhorse', 2003, '03-02-2004', '6000');
  INSERT INTO nakup (je_id_jezdce, ob_nazev, ko_vyrobce, ko_model, ko_rok_vyroby, datum, cena) VALUES ('2', 'Dookie', 'LeaderFox', 'Dragstar', 2003, '10-09-2003', '4000');
  COMMIT;
  
  /* tabulka MA_RAD (vztah mezi JEZDEC a DISCIPLINA) */
  INSERT INTO ma_rad (role3_id_jezdce, role6_nazev) VALUES ('1', 'BMX');
  INSERT INTO ma_rad (role3_id_jezdce, role6_nazev) VALUES ('4', 'DH');
  COMMIT;
  
  /* tabulka SE_UCASTNIL (vztah mezi JEZDEC a ZAVOD) */
  INSERT INTO se_ucastnil (role9_di_nazev, role9_lo_nazev, role9_datum, role10_id_jezdce) VALUES ('4X', '4X Litom��ice', '25-05-2005', '1');
  INSERT INTO se_ucastnil (role9_di_nazev, role9_lo_nazev, role9_datum, role10_id_jezdce) VALUES ('4X', '4X Rokytnice', '16-07-2005', '1');
  INSERT INTO se_ucastnil (role9_di_nazev, role9_lo_nazev, role9_datum, role10_id_jezdce) VALUES ('FR', 'Je�t�d', '25-08-2005', '4');
  INSERT INTO se_ucastnil (role9_di_nazev, role9_lo_nazev, role9_datum, role10_id_jezdce) VALUES ('4X', '4X Litom��ice', '25-05-2005', '3');
  INSERT INTO se_ucastnil (role9_di_nazev, role9_lo_nazev, role9_datum, role10_id_jezdce) VALUES ('DH', 'DH �pindl', '09-11-2005', '8');
  COMMIT;
  
  /* tabulka POUZIVA (vztah mezi JEZDEC a KOMPONENT) */
  INSERT INTO pouziva (role23_vyrobce, role23_model, role23_rok_vyroby, role11_id_jezdce) VALUES ('RaceBike', 'Bandit DS', 2002, '1');
  INSERT INTO pouziva (role23_vyrobce, role23_model, role23_rok_vyroby, role11_id_jezdce) VALUES ('Marzocchi', 'DirtJumper III', 2004,'1');
  INSERT INTO pouziva (role23_vyrobce, role23_model, role23_rok_vyroby, role11_id_jezdce) VALUES ('Maxxis', 'Minion DH', 2004, '1');
  INSERT INTO pouziva (role23_vyrobce, role23_model, role23_rok_vyroby, role11_id_jezdce) VALUES ('Blackhorse', 'Blackhorse', 2003, '2');
  INSERT INTO pouziva (role23_vyrobce, role23_model, role23_rok_vyroby, role11_id_jezdce) VALUES ('Maxxis', 'Bling Bling', 2003, '2');
  INSERT INTO pouziva (role23_vyrobce, role23_model, role23_rok_vyroby, role11_id_jezdce) VALUES ('Manitou', 'X-Fly', 2002, '2');
  INSERT INTO pouziva (role23_vyrobce, role23_model, role23_rok_vyroby, role11_id_jezdce) VALUES ('Specialized', 'P2', 2005, '3');
  INSERT INTO pouziva (role23_vyrobce, role23_model, role23_rok_vyroby, role11_id_jezdce) VALUES ('Marzocchi', 'DirtJumper II', 2005, '3');
  INSERT INTO pouziva (role23_vyrobce, role23_model, role23_rok_vyroby, role11_id_jezdce) VALUES ('Maxxis', 'High Roller', 2004, '3');
  INSERT INTO pouziva (role23_vyrobce, role23_model, role23_rok_vyroby, role11_id_jezdce) VALUES ('LeaderFox', 'Dragstar', 2003, '4');
  INSERT INTO pouziva (role23_vyrobce, role23_model, role23_rok_vyroby, role11_id_jezdce) VALUES ('Manitou', 'X-Fly', 2002, '4');
  INSERT INTO pouziva (role23_vyrobce, role23_model, role23_rok_vyroby, role11_id_jezdce) VALUES ('Nokian', 'Rocker', 2003, '4');
  COMMIT;

  /* insert ... select prikazy */
  -- jezdec s id = 2 se ucastnil vsech zavodu
  INSERT INTO se_ucastnil (role9_di_nazev, role9_lo_nazev, role9_datum, role10_id_jezdce)
    SELECT Z.di_nazev, Z.lo_nazev, Z.datum, J.id_jezdce FROM zavod Z CROSS JOIN jezdec J WHERE J.id_jezdce=2;
  COMMIT;
  
  -- kazdy jezdec ma rad discipliny, ve kterych se ucastnil zavodu
  INSERT INTO ma_rad (role3_id_jezdce, role6_nazev) ( SELECT DISTINCT id_jezdce, COALESCE(role9_di_nazev, 'Dirt') FROM jezdec LEFT OUTER JOIN se_ucastnil ON(role10_id_jezdce=id_jezdce) );
  
  -- jezdec s id = 3 bude mit rad vsechny discipliny
  INSERT INTO ma_rad (role3_id_jezdce, role6_nazev)
    (SELECT 3, nazev FROM disciplina
      WHERE nazev NOT IN(SELECT M.role6_nazev FROM ma_rad M WHERE M.role3_id_jezdce=3));
  COMMIT;
  
  /* vlozeni nahodnych hodnot */
  FOR idx IN 0..10 LOOP
    INSERT INTO lokace (nazev, umisteni, popis) SELECT (idx || dbms_random.string('A', 8)), dbms_random.string('A', 20), dbms_random.string('A', 30) FROM dual;
  END LOOP;
  COMMIT;

  /* vypocteni statistik */
  EXECUTE IMMEDIATE 'ANALYZE TABLE disciplina COMPUTE STATISTICS';
  EXECUTE IMMEDIATE 'ANALYZE TABLE hodnoceni COMPUTE STATISTICS';
  EXECUTE IMMEDIATE 'ANALYZE TABLE jezdec COMPUTE STATISTICS';
  EXECUTE IMMEDIATE 'ANALYZE TABLE komponent COMPUTE STATISTICS';
  EXECUTE IMMEDIATE 'ANALYZE TABLE lokace COMPUTE STATISTICS';
  EXECUTE IMMEDIATE 'ANALYZE TABLE ma_rad COMPUTE STATISTICS';
  EXECUTE IMMEDIATE 'ANALYZE TABLE nakup COMPUTE STATISTICS';
  EXECUTE IMMEDIATE 'ANALYZE TABLE obchod COMPUTE STATISTICS';
  EXECUTE IMMEDIATE 'ANALYZE TABLE plast COMPUTE STATISTICS';
  EXECUTE IMMEDIATE 'ANALYZE TABLE pouziva COMPUTE STATISTICS';
  EXECUTE IMMEDIATE 'ANALYZE TABLE ram COMPUTE STATISTICS';
  EXECUTE IMMEDIATE 'ANALYZE TABLE se_ucastnil COMPUTE STATISTICS';
  EXECUTE IMMEDIATE 'ANALYZE TABLE vidlice COMPUTE STATISTICS';
  EXECUTE IMMEDIATE 'ANALYZE TABLE zavod COMPUTE STATISTICS';
  EXECUTE IMMEDIATE 'ANALYZE TABLE zkusenost COMPUTE STATISTICS';
    
END NAPLN_TABULKY;

PROCEDURE napln_tabulky Compiled.
EXECUTE napln_tabulky;

anonymous block completed
CREATE OR REPLACE PROCEDURE napln_tabulky_obj
AS
BEGIN
 
  /* vymaz�n� obsahu v�ech tabulek */
  DELETE FROM pouziva_obj;
  DELETE FROM se_ucastnil_obj;
  DELETE FROM ma_rad_obj;
  DELETE FROM zkusenost_obj;
  DELETE FROM hodnoceni_obj;
  DELETE FROM nakup_obj;
  DELETE FROM zavod_obj;
  
  DELETE FROM lokace_obj;
  DELETE FROM disciplina_obj;
  DELETE FROM jezdec_obj;
  DELETE FROM ram_obj;
  DELETE FROM vidlice_obj;
  DELETE FROM plast_obj;
  DELETE FROM komponent_obj;
  DELETE FROM obchod_obj;
  COMMIT;
  
  /* tabulka LOKACE_OBJ */
  INSERT INTO lokace_obj (nazev, umisteni, popis) (SELECT nazev, umisteni, popis FROM lokace);
  COMMIT;
  
  /* tabulka DISCIPLINA_OBJ */
  INSERT INTO disciplina_obj (nazev, popis) (SELECT nazev, popis FROM disciplina);
  COMMIT;
  
  /* tabulka JEZDEC_OBJ */
  INSERT INTO jezdec_obj (ID_jezdce, jmeno, prijmeni, prezdivka, e_mail)
    (SELECT id_jezdce, jmeno, prijmeni, prezdivka, e_mail FROM jezdec);
  COMMIT;
  
  /* tabulka OBCHOD_OBJ */
  INSERT INTO obchod_obj (nazev, adresa, www)
    SELECT nazev, adresa, www FROM obchod;
  COMMIT;
  
  /* tabulka KOMPONENT_OBJ */
  INSERT INTO komponent_obj (vyrobce, model, rok_vyroby)
    SELECT vyrobce, model, rok_vyroby FROM komponent;
  COMMIT;
  
  /* tabulka RAM_OBJ (podtyp entity KOMPONENT_OBJ) */
  INSERT INTO ram_obj (ko_komponent, velikost, polomer_kola)
    SELECT (SELECT REF(K) FROM komponent_obj K WHERE K.vyrobce=R.ko_vyrobce AND K.model=R.ko_model AND K.rok_vyroby=R.ko_rok_vyroby), velikost, polomer_kola FROM ram R;
  COMMIT;
  
  /* tabulka VIDLICE_OBJ (podtyp entity KOMPONENT_OBJ) */
  INSERT INTO vidlice_obj (ko_komponent, zdvih, polomer_kola)
    SELECT (SELECT REF(K) FROM komponent_obj K WHERE K.vyrobce=R.ko_vyrobce AND K.model=R.ko_model AND K.rok_vyroby=R.ko_rok_vyroby), zdvih, polomer_kola FROM vidlice R;
  COMMIT;
  
  /* tabulka PLAST_OBJ (podtyp entity KOMPONENT_OBJ) */
  INSERT INTO plast_obj (ko_komponent, sirka, polomer)
    SELECT (SELECT REF(K) FROM komponent_obj K WHERE K.vyrobce=R.ko_vyrobce AND K.model=R.ko_model AND K.rok_vyroby=R.ko_rok_vyroby), sirka, polomer FROM plast R;
  COMMIT;
  
  /* tabulka ZAVOD_OBJ (identifika�n� z�vis� na LOKACE a DISCIPLINA) */
  INSERT INTO zavod_obj (nazev, di_nazev, lo_nazev, datum, startovne)
    SELECT nazev, (SELECT REF(D) FROM disciplina_obj D WHERE D.nazev=Z.di_nazev), (SELECT REF(L) FROM lokace_obj L WHERE L.nazev=Z.lo_nazev), datum, startovne
      FROM zavod Z;
  COMMIT;


  
  /* tabulka ZKUSENOST_OBJ (identifika�n� z�vis� na JEZDEC a KOMPONENT) */
  INSERT INTO zkusenost_obj (je_id_jezdce, ko_komponent, cena_vykon, hodnoceni, slovni_hodnoceni)
    SELECT (SELECT REF(J) FROM jezdec_obj J WHERE J.id_jezdce=R.je_id_jezdce),
            (SELECT REF(K) FROM komponent_obj K WHERE K.vyrobce=R.ko_vyrobce AND K.model=R.ko_model AND K.rok_vyroby=R.ko_rok_vyroby),
            cena_vykon, hodnoceni, slovni_hodnoceni
      FROM zkusenost R; 
  COMMIT;
  
  /* tabulka HODNOCENI_OBJ (identifika�n� z�visl� na JEZDEC a OBCHOD) */
  INSERT INTO hodnoceni_obj (je_id_jezdce, ob_nazev, sortiment, ceny, privetivost, slovni_hodnoceni)
    SELECT (SELECT REF(J) FROM jezdec_obj J WHERE J.id_jezdce=R.je_id_jezdce),
            (SELECT REF(O) FROM obchod_obj O WHERE O.nazev=R.ob_nazev),
            sortiment, ceny, privetivost, slovni_hodnoceni
      FROM hodnoceni R;
  COMMIT;
  
  /* tabulka NAKUP_OBJ (identifika�n� z�visl� na JEZDEC a OBCHOD a KOMPONENT) */
  INSERT INTO nakup_obj (je_id_jezdce, ob_nazev, ko_komponent, datum, cena)
    SELECT (SELECT REF(J) FROM jezdec_obj J WHERE J.id_jezdce=R.je_id_jezdce),
            (SELECT REF(O) FROM obchod_obj O WHERE O.nazev=R.ob_nazev),
            (SELECT REF(K) FROM komponent_obj K WHERE K.vyrobce=R.ko_vyrobce AND K.model=R.ko_model AND K.rok_vyroby=R.ko_rok_vyroby),
            datum, cena
      FROM nakup R;
  COMMIT;
  
  /* tabulka MA_RAD_OBJ (vztah mezi JEZDEC a DISCIPLINA) */
  INSERT INTO ma_rad_obj (role3_id_jezdce, role6_nazev)
    SELECT (SELECT REF(J) FROM jezdec_obj J WHERE J.id_jezdce=R.role3_id_jezdce),
            (SELECT REF(D) FROM disciplina_obj D WHERE D.nazev=R.role6_nazev)
      FROM ma_rad R;
  COMMIT;
  
  /* tabulka SE_UCASTNIL_OBJ (vztah mezi JEZDEC a ZAVOD) */
  INSERT INTO se_ucastnil_obj (role9_zavod, role10_id_jezdce)
    SELECT (SELECT REF(Z) FROM zavod_obj Z WHERE (DEREF(Z.di_nazev).nazev=R.role9_di_nazev AND DEREF(Z.lo_nazev).nazev=R.role9_lo_nazev AND Z.datum=R.role9_datum)),
            (SELECT REF(J) FROM jezdec_obj J WHERE J.id_jezdce=R.role10_id_jezdce)
      FROM se_ucastnil R;
  COMMIT;
  
  /* tabulka POUZIVA_OBJ (vztah mezi JEZDEC a KOMPONENT) */
  INSERT INTO pouziva_obj (role23_komponent, role11_id_jezdce)
    SELECT (SELECT REF(K) FROM komponent_obj K WHERE K.vyrobce=R.role23_vyrobce AND K.model=R.role23_model AND K.rok_vyroby=R.role23_rok_vyroby),
            (SELECT REF(J) FROM jezdec_obj J WHERE J.id_jezdce=R.role11_id_jezdce)
      FROM pouziva R;
  COMMIT;


  /* vypocteni statistik */
  EXECUTE IMMEDIATE 'ANALYZE TABLE disciplina_obj COMPUTE STATISTICS';
  EXECUTE IMMEDIATE 'ANALYZE TABLE hodnoceni_obj COMPUTE STATISTICS';
  EXECUTE IMMEDIATE 'ANALYZE TABLE jezdec_obj COMPUTE STATISTICS';
  EXECUTE IMMEDIATE 'ANALYZE TABLE komponent_obj COMPUTE STATISTICS';
  EXECUTE IMMEDIATE 'ANALYZE TABLE lokace_obj COMPUTE STATISTICS';
  EXECUTE IMMEDIATE 'ANALYZE TABLE ma_rad_obj COMPUTE STATISTICS';
  EXECUTE IMMEDIATE 'ANALYZE TABLE nakup_obj COMPUTE STATISTICS';
  EXECUTE IMMEDIATE 'ANALYZE TABLE obchod_obj COMPUTE STATISTICS';
  EXECUTE IMMEDIATE 'ANALYZE TABLE plast_obj COMPUTE STATISTICS';
  EXECUTE IMMEDIATE 'ANALYZE TABLE pouziva_obj COMPUTE STATISTICS';
  EXECUTE IMMEDIATE 'ANALYZE TABLE ram_obj COMPUTE STATISTICS';
  EXECUTE IMMEDIATE 'ANALYZE TABLE se_ucastnil_obj COMPUTE STATISTICS';
  EXECUTE IMMEDIATE 'ANALYZE TABLE vidlice_obj COMPUTE STATISTICS';
  EXECUTE IMMEDIATE 'ANALYZE TABLE zavod_obj COMPUTE STATISTICS';
  EXECUTE IMMEDIATE 'ANALYZE TABLE zkusenost_obj COMPUTE STATISTICS';
    
END napln_tabulky_obj;

PROCEDURE napln_tabulky_obj Compiled.
EXECUTE napln_tabulky_obj;

anonymous block completed
/* *************************************************************
 * DOTAZY
 * ************************************************************* */

SET AUTOTRACE ON

Autotrace Enabled
Shows the execution plan as well as statistics of the statement.
-- Seznam n�zv� obchod� s ��stkami v nich utracen�ch za n�kupy.

   Statistics
-----------------------------------------------------------
               0  recursive calls
               0  db block gets
               0  consistent gets
               0  physical reads
               0  redo size
               0  total read fandles
               0  read fandles in use
               0  fandle allocation failures
               0  parse count (hard)
               0  parse count (failures)
SELECT nazev, COALESCE(soucet,0) castka
  FROM obchod
  LEFT OUTER JOIN 
  (SELECT ob_nazev nazev, SUM(cena) soucet FROM nakup GROUP BY(ob_nazev)) USING(nazev)

NAZEV                                              CASTKA                 
-------------------------------------------------- ---------------------- 
Madla Sport                                        18400                  
Dookie                                             14549                  
901 Bikes                                          6600                   
Bike Kellys Seky                                   0                      
Katmar                                             0                      

5 rows selected

Plan hash value: 3279846785
 
----------------------------------------------------------------------------------
| Id  | Operation            | Name      | Rows  | Bytes | Cost (%CPU)| Time     |
----------------------------------------------------------------------------------
|   0 | SELECT STATEMENT     |           |     5 |   250 |     5  (40)| 00:00:01 |
|*  1 |  HASH JOIN OUTER     |           |     5 |   250 |     5  (40)| 00:00:01 |
|   2 |   INDEX FULL SCAN    | PK_OBCHOD |     5 |    50 |     1   (0)| 00:00:01 |
|   3 |   VIEW               |           |     3 |   120 |     3  (34)| 00:00:01 |
|   4 |    HASH GROUP BY     |           |     3 |    36 |     3  (34)| 00:00:01 |
|   5 |     TABLE ACCESS FULL| NAKUP     |     7 |    84 |     2   (0)| 00:00:01 |
----------------------------------------------------------------------------------
 
Predicate Information (identified by operation id):
---------------------------------------------------
 
   1 - access("OBCHOD"."NAZEV"="from$_subquery$_002"."NAZEV"(+))

   Statistics
-----------------------------------------------------------
              24  recursive calls
               0  db block gets
               9  consistent gets
               0  physical reads
               0  redo size
               0  total read fandles
               0  read fandles in use
               0  fandle allocation failures
               1  parse count (hard)
               0  parse count (failures)
SELECT nazev, COALESCE((SELECT SUM(cena) FROM nakup N WHERE N.ob_nazev=O.nazev),0) castka FROM obchod O

NAZEV                                              CASTKA                 
-------------------------------------------------- ---------------------- 
901 Bikes                                          6600                   
Bike Kellys Seky                                   0                      
Dookie                                             14549                  
Katmar                                             0                      
Madla Sport                                        18400                  

5 rows selected

Plan hash value: 1153909728
 
--------------------------------------------------------------------------------
| Id  | Operation          | Name      | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |           |     5 |    50 |     1   (0)| 00:00:01 |
|   1 |  SORT AGGREGATE    |           |     1 |    12 |            |          |
|*  2 |   TABLE ACCESS FULL| NAKUP     |     2 |    24 |     2   (0)| 00:00:01 |
|   3 |  INDEX FULL SCAN   | PK_OBCHOD |     5 |    50 |     1   (0)| 00:00:01 |
--------------------------------------------------------------------------------
 
Predicate Information (identified by operation id):
---------------------------------------------------
 
   2 - filter("N"."OB_NAZEV"=:B1)

   Statistics
-----------------------------------------------------------
               1  recursive calls
               0  db block gets
              26  consistent gets
               0  physical reads
               0  redo size
               0  total read fandles
               0  read fandles in use
               0  fandle allocation failures
               1  parse count (hard)
               0  parse count (failures)
SELECT O.nazev, (SELECT COALESCE(SUM(N.cena), 0) FROM nakup_obj N WHERE REF(O)=N.ob_nazev) AS castka
  FROM obchod_obj O

NAZEV                                              CASTKA                 
-------------------------------------------------- ---------------------- 
Dookie                                             14549                  
901 Bikes                                          6600                   
Katmar                                             0                      
Bike Kellys Seky                                   0                      
Madla Sport                                        18400                  

5 rows selected

Plan hash value: 2248251368
 
---------------------------------------------------------------------------------
| Id  | Operation          | Name       | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |            |     5 |   160 |     2   (0)| 00:00:01 |
|   1 |  SORT AGGREGATE    |            |     1 |    19 |            |          |
|*  2 |   TABLE ACCESS FULL| NAKUP_OBJ  |     2 |    38 |     2   (0)| 00:00:01 |
|   3 |  TABLE ACCESS FULL | OBCHOD_OBJ |     5 |   160 |     2   (0)| 00:00:01 |
---------------------------------------------------------------------------------
 
Predicate Information (identified by operation id):
---------------------------------------------------
 
   2 - filter("N"."SYS_NC00008$"=:B1)

   Statistics
-----------------------------------------------------------
              22  recursive calls
               0  db block gets
              36  consistent gets
               0  physical reads
               0  redo size
               0  total read fandles
               0  read fandles in use
               0  fandle allocation failures
               1  parse count (hard)
               0  parse count (failures)
-- Zobrazit v�echna hodnocen� obchod� a n�kupy. Ke ka�d�mu n�kupu p�i�adit v�echna hodnocen� vztahuj�c� se k obchodu, kde byl n�kup proveden. Zobrazit i n�kupy v obchodech, kter� nebyly hodnoceny a hodnocen� obchod�, kde nebyl je�t� proveden n�kup.

   Statistics
-----------------------------------------------------------
               0  recursive calls
               0  db block gets
               0  consistent gets
               0  physical reads
               0  redo size
               0  total read fandles
               0  read fandles in use
               0  fandle allocation failures
               0  parse count (hard)
               0  parse count (failures)
SELECT * FROM nakup FULL OUTER JOIN hodnoceni USING(ob_nazev)

OB_NAZEV                                           CENA                   DATUM                     JE_ID_JEZDCE           KO_MODEL                                           KO_VYROBCE                                         KO_ROK_VYROBY          SORTIMENT              CENY                   PRIVETIVOST            SLOVNI_HODNOCENI                                                                                                                                                                                                                                                JE_ID_JEZDCE           
-------------------------------------------------- ---------------------- ------------------------- ---------------------- -------------------------------------------------- -------------------------------------------------- ---------------------- ---------------------- ---------------------- ---------------------- --------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- ---------------------- 
Dookie                                             4000                   10.09.03                  2                      Dragstar                                           LeaderFox                                          2003                   2                      2                      1                      Pohodovej majitel.                                                                                                                                                                                                                                              1                      
Dookie                                             450                    29.03.04                  1                      Minion DH                                          Maxxis                                             2004                   2                      2                      1                      Pohodovej majitel.                                                                                                                                                                                                                                              1                      
Dookie                                             10099                  29.03.04                  1                      DirtJumper III                                     Marzocchi                                          2004                   2                      2                      1                      Pohodovej majitel.                                                                                                                                                                                                                                              1                      
901 Bikes                                          6000                   03.02.04                  2                      Blackhorse                                         Blackhorse                                         2003                   1                      3                      2                                                                                                                                                                                                                                                                                      1                      
901 Bikes                                          600                    29.03.04                  1                      Minion DH                                          Maxxis                                             2004                   1                      3                      2                                                                                                                                                                                                                                                                                      1                      
Dookie                                             4000                   10.09.03                  2                      Dragstar                                           LeaderFox                                          2003                   2                      1                      1                                                                                                                                                                                                                                                                                      4                      
Dookie                                             450                    29.03.04                  1                      Minion DH                                          Maxxis                                             2004                   2                      1                      1                                                                                                                                                                                                                                                                                      4                      
Dookie                                             10099                  29.03.04                  1                      DirtJumper III                                     Marzocchi                                          2004                   2                      1                      1                                                                                                                                                                                                                                                                                      4                      
Madla Sport                                        400                    10.09.05                  3                      High Roller                                        Maxxis                                             2004                   4                      1                      3                                                                                                                                                                                                                                                                                      3                      
Madla Sport                                        18000                  10.09.05                  3                      P2                                                 Specialized                                        2005                   4                      1                      3                                                                                                                                                                                                                                                                                      3                      
Dookie                                             4000                   10.09.03                  2                      Dragstar                                           LeaderFox                                          2003                   2                      2                      1                                                                                                                                                                                                                                                                                      6                      
Dookie                                             450                    29.03.04                  1                      Minion DH                                          Maxxis                                             2004                   2                      2                      1                                                                                                                                                                                                                                                                                      6                      
Dookie                                             10099                  29.03.04                  1                      DirtJumper III                                     Marzocchi                                          2004                   2                      2                      1                                                                                                                                                                                                                                                                                      6                      
Katmar                                                                                                                                                                                                                                                  3                      3                      4                                                                                                                                                                                                                                                                                      3                      
Katmar                                                                                                                                                                                                                                                  3                      2                      4                                                                                                                                                                                                                                                                                      1                      

15 rows selected

Plan hash value: 1177690318
 
----------------------------------------------------------------------------------
| Id  | Operation            | Name      | Rows  | Bytes | Cost (%CPU)| Time     |
----------------------------------------------------------------------------------
|   0 | SELECT STATEMENT     |           |    14 |  4340 |     8  (13)| 00:00:01 |
|   1 |  VIEW                |           |    14 |  4340 |     8  (13)| 00:00:01 |
|   2 |   UNION-ALL          |           |       |       |            |          |
|*  3 |    HASH JOIN OUTER   |           |    12 |   840 |     5  (20)| 00:00:01 |
|   4 |     TABLE ACCESS FULL| NAKUP     |     7 |   350 |     2   (0)| 00:00:01 |
|   5 |     TABLE ACCESS FULL| HODNOCENI |     7 |   140 |     2   (0)| 00:00:01 |
|*  6 |    HASH JOIN ANTI    |           |     2 |    58 |     4  (25)| 00:00:01 |
|   7 |     TABLE ACCESS FULL| HODNOCENI |     7 |   140 |     2   (0)| 00:00:01 |
|   8 |     INDEX FULL SCAN  | PK_NAKUP  |     7 |    63 |     1   (0)| 00:00:01 |
----------------------------------------------------------------------------------
 
Predicate Information (identified by operation id):
---------------------------------------------------
 
   3 - access("NAKUP"."OB_NAZEV"="HODNOCENI"."OB_NAZEV"(+))
   6 - access("NAKUP"."OB_NAZEV"="HODNOCENI"."OB_NAZEV")

   Statistics
-----------------------------------------------------------
               1  recursive calls
               0  db block gets
              16  consistent gets
               0  physical reads
               0  redo size
               0  total read fandles
               0  read fandles in use
               0  fandle allocation failures
               1  parse count (hard)
               0  parse count (failures)
(SELECT * FROM nakup LEFT OUTER JOIN hodnoceni USING(ob_nazev))
UNION
(SELECT * FROM nakup RIGHT OUTER JOIN hodnoceni USING(ob_nazev))

OB_NAZEV                                           CENA                   DATUM                     QCSJ_C000000000300000  KO_MODEL                                           KO_VYROBCE                                         KO_ROK_VYROBY          SORTIMENT              CENY                   PRIVETIVOST            SLOVNI_HODNOCENI                                                                                                                                                                                                                                                QCSJ_C000000000300001  
-------------------------------------------------- ---------------------- ------------------------- ---------------------- -------------------------------------------------- -------------------------------------------------- ---------------------- ---------------------- ---------------------- ---------------------- --------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- ---------------------- 
901 Bikes                                          600                    29.03.04                  1                      Minion DH                                          Maxxis                                             2004                   1                      3                      2                                                                                                                                                                                                                                                                                      1                      
901 Bikes                                          6000                   03.02.04                  2                      Blackhorse                                         Blackhorse                                         2003                   1                      3                      2                                                                                                                                                                                                                                                                                      1                      
Dookie                                             450                    29.03.04                  1                      Minion DH                                          Maxxis                                             2004                   2                      1                      1                                                                                                                                                                                                                                                                                      4                      
Dookie                                             450                    29.03.04                  1                      Minion DH                                          Maxxis                                             2004                   2                      2                      1                      Pohodovej majitel.                                                                                                                                                                                                                                              1                      
Dookie                                             450                    29.03.04                  1                      Minion DH                                          Maxxis                                             2004                   2                      2                      1                                                                                                                                                                                                                                                                                      6                      
Dookie                                             4000                   10.09.03                  2                      Dragstar                                           LeaderFox                                          2003                   2                      1                      1                                                                                                                                                                                                                                                                                      4                      
Dookie                                             4000                   10.09.03                  2                      Dragstar                                           LeaderFox                                          2003                   2                      2                      1                      Pohodovej majitel.                                                                                                                                                                                                                                              1                      
Dookie                                             4000                   10.09.03                  2                      Dragstar                                           LeaderFox                                          2003                   2                      2                      1                                                                                                                                                                                                                                                                                      6                      
Dookie                                             10099                  29.03.04                  1                      DirtJumper III                                     Marzocchi                                          2004                   2                      1                      1                                                                                                                                                                                                                                                                                      4                      
Dookie                                             10099                  29.03.04                  1                      DirtJumper III                                     Marzocchi                                          2004                   2                      2                      1                      Pohodovej majitel.                                                                                                                                                                                                                                              1                      
Dookie                                             10099                  29.03.04                  1                      DirtJumper III                                     Marzocchi                                          2004                   2                      2                      1                                                                                                                                                                                                                                                                                      6                      
Katmar                                                                                                                                                                                                                                                  3                      2                      4                                                                                                                                                                                                                                                                                      1                      
Katmar                                                                                                                                                                                                                                                  3                      3                      4                                                                                                                                                                                                                                                                                      3                      
Madla Sport                                        400                    10.09.05                  3                      High Roller                                        Maxxis                                             2004                   4                      1                      3                                                                                                                                                                                                                                                                                      3                      
Madla Sport                                        18000                  10.09.05                  3                      P2                                                 Specialized                                        2005                   4                      1                      3                                                                                                                                                                                                                                                                                      3                      

15 rows selected

Plan hash value: 1670255159
 
----------------------------------------------------------------------------------
| Id  | Operation            | Name      | Rows  | Bytes | Cost (%CPU)| Time     |
----------------------------------------------------------------------------------
|   0 | SELECT STATEMENT     |           |    24 |  1488 |    11  (64)| 00:00:01 |
|   1 |  SORT UNIQUE         |           |    24 |  1488 |    11  (64)| 00:00:01 |
|   2 |   UNION-ALL          |           |       |       |            |          |
|*  3 |    HASH JOIN OUTER   |           |    12 |   744 |     5  (20)| 00:00:01 |
|   4 |     TABLE ACCESS FULL| NAKUP     |     7 |   294 |     2   (0)| 00:00:01 |
|   5 |     TABLE ACCESS FULL| HODNOCENI |     7 |   140 |     2   (0)| 00:00:01 |
|*  6 |    HASH JOIN OUTER   |           |    12 |   744 |     5  (20)| 00:00:01 |
|   7 |     TABLE ACCESS FULL| HODNOCENI |     7 |   140 |     2   (0)| 00:00:01 |
|   8 |     TABLE ACCESS FULL| NAKUP     |     7 |   294 |     2   (0)| 00:00:01 |
----------------------------------------------------------------------------------
 
Predicate Information (identified by operation id):
---------------------------------------------------
 
   3 - access("NAKUP"."OB_NAZEV"="HODNOCENI"."OB_NAZEV"(+))
   6 - access("HODNOCENI"."OB_NAZEV"="NAKUP"."OB_NAZEV"(+))

   Statistics
-----------------------------------------------------------
               1  recursive calls
               0  db block gets
              20  consistent gets
               0  physical reads
               0  redo size
               0  total read fandles
               0  read fandles in use
               0  fandle allocation failures
               1  parse count (hard)
               0  parse count (failures)
(SELECT COALESCE(DEREF(N.ob_nazev).nazev, DEREF(h.ob_nazev).nazev), cena, datum, DEREF(N.je_id_jezdce).id_jezdce, DEREF(N.ko_komponent).model, DEREF(N.ko_komponent).vyrobce, DEREF(N.ko_komponent).rok_vyroby, H.sortiment, h.ceny, h.privetivost, h.slovni_hodnoceni, DEREF(h.je_id_jezdce).id_jezdce FROM nakup_obj N
LEFT OUTER JOIN hodnoceni_obj H ON(N.ob_nazev=h.ob_nazev))
UNION
(SELECT COALESCE(DEREF(N.ob_nazev).nazev, DEREF(h.ob_nazev).nazev), cena, datum, DEREF(N.je_id_jezdce).id_jezdce, DEREF(N.ko_komponent).model, DEREF(N.ko_komponent).vyrobce, DEREF(N.ko_komponent).rok_vyroby, H.sortiment, h.ceny, h.privetivost, h.slovni_hodnoceni, DEREF(h.je_id_jezdce).id_jezdce FROM nakup_obj N
RIGHT OUTER JOIN hodnoceni_obj H ON(N.ob_nazev=h.ob_nazev))

COALESCE(DEREF(N.OB_NAZEV).NAZEV,DEREF(H.OB_NAZEV).NAZEV) CENA                   DATUM                     DEREF(N.JE_ID_JEZDCE).ID_JEZDCE DEREF(N.KO_KOMPONENT).MODEL                        DEREF(N.KO_KOMPONENT).VYROBCE                      DEREF(N.KO_KOMPONENT).ROK_VYROBY SORTIMENT              CENY                   PRIVETIVOST            SLOVNI_HODNOCENI                                                                                                                                                                                                                                                DEREF(H.JE_ID_JEZDCE).ID_JEZDCE 
--------------------------------------------------------- ---------------------- ------------------------- ------------------------------- -------------------------------------------------- -------------------------------------------------- -------------------------------- ---------------------- ---------------------- ---------------------- --------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- ------------------------------- 
901 Bikes                                                 600                    29.03.04                  1                               Minion DH                                          Maxxis                                             2004                             1                      3                      2                                                                                                                                                                                                                                                                                      1                               
901 Bikes                                                 6000                   03.02.04                  2                               Blackhorse                                         Blackhorse                                         2003                             1                      3                      2                                                                                                                                                                                                                                                                                      1                               
Dookie                                                    450                    29.03.04                  1                               Minion DH                                          Maxxis                                             2004                             2                      1                      1                                                                                                                                                                                                                                                                                      4                               
Dookie                                                    450                    29.03.04                  1                               Minion DH                                          Maxxis                                             2004                             2                      2                      1                      Pohodovej majitel.                                                                                                                                                                                                                                              1                               
Dookie                                                    450                    29.03.04                  1                               Minion DH                                          Maxxis                                             2004                             2                      2                      1                                                                                                                                                                                                                                                                                      6                               
Dookie                                                    4000                   10.09.03                  2                               Dragstar                                           LeaderFox                                          2003                             2                      1                      1                                                                                                                                                                                                                                                                                      4                               
Dookie                                                    4000                   10.09.03                  2                               Dragstar                                           LeaderFox                                          2003                             2                      2                      1                      Pohodovej majitel.                                                                                                                                                                                                                                              1                               
Dookie                                                    4000                   10.09.03                  2                               Dragstar                                           LeaderFox                                          2003                             2                      2                      1                                                                                                                                                                                                                                                                                      6                               
Dookie                                                    10099                  29.03.04                  1                               DirtJumper III                                     Marzocchi                                          2004                             2                      1                      1                                                                                                                                                                                                                                                                                      4                               
Dookie                                                    10099                  29.03.04                  1                               DirtJumper III                                     Marzocchi                                          2004                             2                      2                      1                      Pohodovej majitel.                                                                                                                                                                                                                                              1                               
Dookie                                                    10099                  29.03.04                  1                               DirtJumper III                                     Marzocchi                                          2004                             2                      2                      1                                                                                                                                                                                                                                                                                      6                               
Katmar                                                                                                                                                                                                                                                                            3                      2                      4                                                                                                                                                                                                                                                                                      1                               
Katmar                                                                                                                                                                                                                                                                            3                      3                      4                                                                                                                                                                                                                                                                                      3                               
Madla Sport                                               400                    10.09.05                  3                               High Roller                                        Maxxis                                             2004                             4                      1                      3                                                                                                                                                                                                                                                                                      3                               
Madla Sport                                               18000                  10.09.05                  3                               P2                                                 Specialized                                        2005                             4                      1                      3                                                                                                                                                                                                                                                                                      3                               

15 rows selected

Plan hash value: 800135054
 
------------------------------------------------------------------------------------------------------
| Id  | Operation                           | Name           | Rows  | Bytes | Cost (%CPU)| Time     |
------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                    |                |  1206 |   229K|    34  (59)| 00:00:01 |
|   1 |  SORT UNIQUE                        |                |  1206 |   229K|    34  (59)| 00:00:01 |
|   2 |   UNION-ALL                         |                |       |       |            |          |
|*  3 |    HASH JOIN RIGHT OUTER            |                |   603 |   114K|    16  (13)| 00:00:01 |
|   4 |     TABLE ACCESS FULL               | JEZDEC_OBJ     |    10 |   120 |     2   (0)| 00:00:01 |
|*  5 |     HASH JOIN RIGHT OUTER           |                |   241 | 44103 |    14  (15)| 00:00:01 |
|   6 |      TABLE ACCESS FULL              | OBCHOD_OBJ     |     5 |   100 |     2   (0)| 00:00:01 |
|*  7 |      HASH JOIN RIGHT OUTER          |                |   193 | 31459 |    11  (10)| 00:00:01 |
|   8 |       TABLE ACCESS FULL             | HODNOCENI_OBJ  |     7 |   294 |     2   (0)| 00:00:01 |
|*  9 |       HASH JOIN OUTER               |                |   110 | 13310 |     9  (12)| 00:00:01 |
|  10 |        NESTED LOOPS OUTER           |                |    33 |  3597 |     6   (0)| 00:00:01 |
|  11 |         NESTED LOOPS OUTER          |                |    12 |   936 |     4   (0)| 00:00:01 |
|  12 |          TABLE ACCESS FULL          | NAKUP_OBJ      |     7 |   406 |     2   (0)| 00:00:01 |
|  13 |          TABLE ACCESS BY INDEX ROWID| OBCHOD_OBJ     |     2 |    40 |     1   (0)| 00:00:01 |
|* 14 |           INDEX UNIQUE SCAN         | SYS_C001514335 |     1 |       |     0   (0)| 00:00:01 |
|  15 |         TABLE ACCESS BY INDEX ROWID | KOMPONENT_OBJ  |     3 |    93 |     1   (0)| 00:00:01 |
|* 16 |          INDEX UNIQUE SCAN          | SYS_C001514346 |     1 |       |     0   (0)| 00:00:01 |
|  17 |        TABLE ACCESS FULL            | JEZDEC_OBJ     |    10 |   120 |     2   (0)| 00:00:01 |
|* 18 |    HASH JOIN RIGHT OUTER            |                |   603 |   114K|    16  (13)| 00:00:01 |
|  19 |     TABLE ACCESS FULL               | JEZDEC_OBJ     |    10 |   120 |     2   (0)| 00:00:01 |
|* 20 |     HASH JOIN RIGHT OUTER           |                |   181 | 33123 |    14  (15)| 00:00:01 |
|  21 |      TABLE ACCESS FULL              | KOMPONENT_OBJ  |    17 |   527 |     2   (0)| 00:00:01 |
|* 22 |      HASH JOIN OUTER                |                |    64 |  9728 |    11  (10)| 00:00:01 |
|* 23 |       HASH JOIN OUTER               |                |    38 |  5016 |     9  (12)| 00:00:01 |
|  24 |        NESTED LOOPS OUTER           |                |    22 |  1628 |     6   (0)| 00:00:01 |
|  25 |         NESTED LOOPS OUTER          |                |     9 |   558 |     4   (0)| 00:00:01 |
|  26 |          TABLE ACCESS FULL          | HODNOCENI_OBJ  |     7 |   294 |     2   (0)| 00:00:01 |
|  27 |          TABLE ACCESS BY INDEX ROWID| OBCHOD_OBJ     |     1 |    20 |     1   (0)| 00:00:01 |
|* 28 |           INDEX UNIQUE SCAN         | SYS_C001514335 |     1 |       |     0   (0)| 00:00:01 |
|  29 |         TABLE ACCESS BY INDEX ROWID | JEZDEC_OBJ     |     3 |    36 |     1   (0)| 00:00:01 |
|* 30 |          INDEX UNIQUE SCAN          | SYS_C001514379 |     1 |       |     0   (0)| 00:00:01 |
|  31 |        TABLE ACCESS FULL            | NAKUP_OBJ      |     7 |   406 |     2   (0)| 00:00:01 |
|  32 |       TABLE ACCESS FULL             | OBCHOD_OBJ     |     5 |   100 |     2   (0)| 00:00:01 |
------------------------------------------------------------------------------------------------------
 
Predicate Information (identified by operation id):
---------------------------------------------------
 
   3 - access("P000018$"."SYS_NC_OID$"(+)="H"."SYS_NC00010$")
   5 - access("P000012$"."SYS_NC_OID$"(+)="H"."SYS_NC00009$")
   7 - access("N"."SYS_NC00008$"="H"."SYS_NC00009$"(+))
   9 - access("P000014$"."SYS_NC_OID$"(+)="N"."SYS_NC00009$")
  14 - access("P000010$"."SYS_NC_OID$"(+)="N"."SYS_NC00008$")
  16 - access("P000016$"."SYS_NC_OID$"(+)="N"."SYS_NC00010$")
  18 - access("P000024$"."SYS_NC_OID$"(+)="N"."SYS_NC00009$")
  20 - access("P000026$"."SYS_NC_OID$"(+)="N"."SYS_NC00010$")
  22 - access("P000020$"."SYS_NC_OID$"(+)="N"."SYS_NC00008$")
  23 - access("N"."SYS_NC00008$"(+)="H"."SYS_NC00009$")
  28 - access("P000022$"."SYS_NC_OID$"(+)="H"."SYS_NC00009$")
  30 - access("P000028$"."SYS_NC_OID$"(+)="H"."SYS_NC00010$")

   Statistics
-----------------------------------------------------------
              89  recursive calls
               0  db block gets
             112  consistent gets
               0  physical reads
               0  redo size
               0  total read fandles
               0  read fandles in use
               0  fandle allocation failures
               1  parse count (hard)
               0  parse count (failures)
-- N�zvy obchod�, kde byla provedena koup� i byl hodnocen.

   Statistics
-----------------------------------------------------------
               0  recursive calls
               0  db block gets
               0  consistent gets
               0  physical reads
               0  redo size
               0  total read fandles
               0  read fandles in use
               0  fandle allocation failures
               0  parse count (hard)
               0  parse count (failures)
SELECT ob_nazev nazev FROM nakup INTERSECT SELECT ob_nazev nazev FROM hodnoceni

NAZEV                                              
-------------------------------------------------- 
901 Bikes                                          
Dookie                                             
Madla Sport                                        

3 rows selected

Plan hash value: 620573327
 
------------------------------------------------------------------------------------
| Id  | Operation           | Name         | Rows  | Bytes | Cost (%CPU)| Time     |
------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT    |              |     7 |   119 |     4  (75)| 00:00:01 |
|   1 |  INTERSECTION       |              |       |       |            |          |
|   2 |   SORT UNIQUE       |              |     7 |    63 |     2  (50)| 00:00:01 |
|   3 |    INDEX FULL SCAN  | PK_NAKUP     |     7 |    63 |     1   (0)| 00:00:01 |
|   4 |   SORT UNIQUE NOSORT|              |     7 |    56 |     2  (50)| 00:00:01 |
|   5 |    INDEX FULL SCAN  | PK_HODNOCENI |     7 |    56 |     1   (0)| 00:00:01 |
------------------------------------------------------------------------------------

   Statistics
-----------------------------------------------------------
               1  recursive calls
               0  db block gets
               2  consistent gets
               0  physical reads
               0  redo size
               0  total read fandles
               0  read fandles in use
               0  fandle allocation failures
               1  parse count (hard)
               0  parse count (failures)
SELECT DISTINCT ob_nazev nazev FROM hodnoceni JOIN nakup USING(ob_nazev)

NAZEV                                              
-------------------------------------------------- 
Madla Sport                                        
Dookie                                             
901 Bikes                                          

3 rows selected

Plan hash value: 4006305037
 
----------------------------------------------------------------------------------
| Id  | Operation         | Name         | Rows  | Bytes | Cost (%CPU)| Time     |
----------------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |              |     3 |    51 |     4  (50)| 00:00:01 |
|   1 |  HASH UNIQUE      |              |     3 |    51 |     4  (50)| 00:00:01 |
|*  2 |   HASH JOIN       |              |    12 |   204 |     3  (34)| 00:00:01 |
|   3 |    INDEX FULL SCAN| PK_HODNOCENI |     7 |    56 |     1   (0)| 00:00:01 |
|   4 |    INDEX FULL SCAN| PK_NAKUP     |     7 |    63 |     1   (0)| 00:00:01 |
----------------------------------------------------------------------------------
 
Predicate Information (identified by operation id):
---------------------------------------------------
 
   2 - access("HODNOCENI"."OB_NAZEV"="NAKUP"."OB_NAZEV")

   Statistics
-----------------------------------------------------------
               1  recursive calls
               0  db block gets
               2  consistent gets
               0  physical reads
               0  redo size
               0  total read fandles
               0  read fandles in use
               0  fandle allocation failures
               1  parse count (hard)
               0  parse count (failures)
SELECT DISTINCT DEREF(N.ob_nazev).nazev AS nazev
  FROM nakup_obj N
  WHERE N.ob_nazev IN (SELECT ob_nazev FROM hodnoceni_obj)

NAZEV                                              
-------------------------------------------------- 
Madla Sport                                        
Dookie                                             
901 Bikes                                          

3 rows selected

Plan hash value: 411217672
 
-------------------------------------------------------------------------------------------------
| Id  | Operation                      | Name           | Rows  | Bytes | Cost (%CPU)| Time     |
-------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT               |                |     5 |   415 |     8  (25)| 00:00:01 |
|   1 |  HASH UNIQUE                   |                |     5 |   415 |     8  (25)| 00:00:01 |
|*  2 |   HASH JOIN                    |                |    20 |  1660 |     7  (15)| 00:00:01 |
|   3 |    NESTED LOOPS OUTER          |                |    12 |   804 |     4   (0)| 00:00:01 |
|   4 |     TABLE ACCESS FULL          | NAKUP_OBJ      |     7 |   112 |     2   (0)| 00:00:01 |
|   5 |     TABLE ACCESS BY INDEX ROWID| OBCHOD_OBJ     |     2 |   102 |     1   (0)| 00:00:01 |
|*  6 |      INDEX UNIQUE SCAN         | SYS_C001514335 |     1 |       |     0   (0)| 00:00:01 |
|   7 |    TABLE ACCESS FULL           | HODNOCENI_OBJ  |     7 |   112 |     2   (0)| 00:00:01 |
-------------------------------------------------------------------------------------------------
 
Predicate Information (identified by operation id):
---------------------------------------------------
 
   2 - access("N"."SYS_NC00008$"="HODNOCENI_OBJ"."SYS_NC00009$")
   6 - access("P000004$"."SYS_NC_OID$"(+)="N"."SYS_NC00008$")

   Statistics
-----------------------------------------------------------
              36  recursive calls
               0  db block gets
              29  consistent gets
               0  physical reads
               0  redo size
               0  total read fandles
               0  read fandles in use
               0  fandle allocation failures
               1  parse count (hard)
               0  parse count (failures)
-- Kdyby se ka�d� jezdec ��astnil ka�d�ho z�vodu, kolik by bylo takov�ch ��ast�.

   Statistics
-----------------------------------------------------------
               0  recursive calls
               0  db block gets
               0  consistent gets
               0  physical reads
               0  redo size
               0  total read fandles
               0  read fandles in use
               0  fandle allocation failures
               0  parse count (hard)
               0  parse count (failures)
SELECT COUNT(*) pocet FROM jezdec CROSS JOIN zavod

POCET                  
---------------------- 
50                     

1 rows selected

Plan hash value: 3490665904
 
-----------------------------------------------------------------------------
| Id  | Operation               | Name      | Rows  | Cost (%CPU)| Time     |
-----------------------------------------------------------------------------
|   0 | SELECT STATEMENT        |           |     1 |     4   (0)| 00:00:01 |
|   1 |  SORT AGGREGATE         |           |     1 |            |          |
|   2 |   MERGE JOIN CARTESIAN  |           |    50 |     4   (0)| 00:00:01 |
|   3 |    INDEX FULL SCAN      | PK_ZAVOD  |     5 |     1   (0)| 00:00:01 |
|   4 |    BUFFER SORT          |           |    10 |     3   (0)| 00:00:01 |
|   5 |     INDEX FAST FULL SCAN| PK_JEZDEC |    10 |     1   (0)| 00:00:01 |
-----------------------------------------------------------------------------

   Statistics
-----------------------------------------------------------
               1  recursive calls
               0  db block gets
               4  consistent gets
               0  physical reads
               0  redo size
               0  total read fandles
               0  read fandles in use
               0  fandle allocation failures
               1  parse count (hard)
               0  parse count (failures)
SELECT (SELECT COUNT(*) FROM jezdec) * (SELECT COUNT(*) FROM zavod) pocet FROM dual

POCET                  
---------------------- 
50                     

1 rows selected

Plan hash value: 1302054247
 
------------------------------------------------------------------------
| Id  | Operation          | Name      | Rows  | Cost (%CPU)| Time     |
------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |           |     1 |     2   (0)| 00:00:01 |
|   1 |  SORT AGGREGATE    |           |     1 |            |          |
|   2 |   INDEX FULL SCAN  | PK_JEZDEC |    10 |     1   (0)| 00:00:01 |
|   3 |    SORT AGGREGATE  |           |     1 |            |          |
|   4 |     INDEX FULL SCAN| PK_ZAVOD  |     5 |     1   (0)| 00:00:01 |
|   5 |  FAST DUAL         |           |     1 |     2   (0)| 00:00:01 |
------------------------------------------------------------------------

   Statistics
-----------------------------------------------------------
               1  recursive calls
               0  db block gets
               2  consistent gets
               0  physical reads
               0  redo size
               0  total read fandles
               0  read fandles in use
               0  fandle allocation failures
               1  parse count (hard)
               0  parse count (failures)
SELECT (SELECT COUNT(*) FROM jezdec_obj) * (SELECT COUNT(*) FROM zavod_obj) pocet FROM dual

POCET                  
---------------------- 
50                     

1 rows selected

Plan hash value: 3480248397
 
-----------------------------------------------------------------------------
| Id  | Operation          | Name           | Rows  | Cost (%CPU)| Time     |
-----------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |                |     1 |     2   (0)| 00:00:01 |
|   1 |  SORT AGGREGATE    |                |     1 |            |          |
|   2 |   INDEX FULL SCAN  | SYS_C001514380 |    10 |     1   (0)| 00:00:01 |
|   3 |    SORT AGGREGATE  |                |     1 |            |          |
|   4 |     INDEX FULL SCAN| SYS_C001514370 |     5 |     1   (0)| 00:00:01 |
|   5 |  FAST DUAL         |                |     1 |     2   (0)| 00:00:01 |
-----------------------------------------------------------------------------

   Statistics
-----------------------------------------------------------
               1  recursive calls
               0  db block gets
               2  consistent gets
               0  physical reads
               0  redo size
               0  total read fandles
               0  read fandles in use
               0  fandle allocation failures
               1  parse count (hard)
               0  parse count (failures)
-- Seznam jezdc� (jm�no, p��jmen�) s po�tem ��ast� na z�vodech. Zobrazit pouze jezdce, kte�� se ��astnili v�ce jak jednoho z�vodu.

   Statistics
-----------------------------------------------------------
               0  recursive calls
               0  db block gets
               0  consistent gets
               0  physical reads
               0  redo size
               0  total read fandles
               0  read fandles in use
               0  fandle allocation failures
               0  parse count (hard)
               0  parse count (failures)
SELECT jmeno, prijmeni, pocet
  FROM ( SELECT role10_id_jezdce id_jezdce, COUNT(*) pocet FROM se_ucastnil GROUP BY role10_id_jezdce HAVING COUNT(*)>1 )
  NATURAL JOIN jezdec

JMENO                          PRIJMENI                       POCET                  
------------------------------ ------------------------------ ---------------------- 
Marek                          Handlu                         2                      
Michal                         Andy                           5                      

2 rows selected

Plan hash value: 122544899
 
--------------------------------------------------------------------------------------------------
| Id  | Operation                    | Name              | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT             |                   |     1 |    40 |     3  (34)| 00:00:01 |
|   1 |  NESTED LOOPS                |                   |     1 |    40 |     3  (34)| 00:00:01 |
|   2 |   VIEW                       |                   |     1 |    26 |     2  (50)| 00:00:01 |
|*  3 |    FILTER                    |                   |       |       |            |          |
|   4 |     HASH GROUP BY            |                   |     1 |     2 |     2  (50)| 00:00:01 |
|   5 |      INDEX FULL SCAN         | UNQ_SE_UCASTNIL_1 |    10 |    20 |     1   (0)| 00:00:01 |
|   6 |   TABLE ACCESS BY INDEX ROWID| JEZDEC            |     1 |    14 |     1   (0)| 00:00:01 |
|*  7 |    INDEX UNIQUE SCAN         | PK_JEZDEC         |     1 |       |     0   (0)| 00:00:01 |
--------------------------------------------------------------------------------------------------
 
Predicate Information (identified by operation id):
---------------------------------------------------
 
   3 - filter(COUNT(*)>1)
   7 - access("from$_subquery$_001"."ID_JEZDCE"="JEZDEC"."ID_JEZDCE")

   Statistics
-----------------------------------------------------------
              24  recursive calls
               0  db block gets
               8  consistent gets
               0  physical reads
               0  redo size
               0  total read fandles
               0  read fandles in use
               0  fandle allocation failures
               1  parse count (hard)
               0  parse count (failures)
SELECT jmeno, prijmeni, pocet
  FROM ( SELECT role10_id_jezdce id_jezdce, COUNT(*) pocet FROM se_ucastnil GROUP BY role10_id_jezdce )
  NATURAL JOIN jezdec WHERE pocet > 1

JMENO                          PRIJMENI                       POCET                  
------------------------------ ------------------------------ ---------------------- 
Marek                          Handlu                         2                      
Michal                         Andy                           5                      

2 rows selected

Plan hash value: 122544899
 
--------------------------------------------------------------------------------------------------
| Id  | Operation                    | Name              | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT             |                   |     1 |    40 |     3  (34)| 00:00:01 |
|   1 |  NESTED LOOPS                |                   |     1 |    40 |     3  (34)| 00:00:01 |
|   2 |   VIEW                       |                   |     1 |    26 |     2  (50)| 00:00:01 |
|*  3 |    FILTER                    |                   |       |       |            |          |
|   4 |     HASH GROUP BY            |                   |     1 |     2 |     2  (50)| 00:00:01 |
|   5 |      INDEX FULL SCAN         | UNQ_SE_UCASTNIL_1 |    10 |    20 |     1   (0)| 00:00:01 |
|   6 |   TABLE ACCESS BY INDEX ROWID| JEZDEC            |     1 |    14 |     1   (0)| 00:00:01 |
|*  7 |    INDEX UNIQUE SCAN         | PK_JEZDEC         |     1 |       |     0   (0)| 00:00:01 |
--------------------------------------------------------------------------------------------------
 
Predicate Information (identified by operation id):
---------------------------------------------------
 
   3 - filter(COUNT(*)>1)
   7 - access("from$_subquery$_001"."ID_JEZDCE"="JEZDEC"."ID_JEZDCE")

   Statistics
-----------------------------------------------------------
               1  recursive calls
               0  db block gets
               5  consistent gets
               0  physical reads
               0  redo size
               0  total read fandles
               0  read fandles in use
               0  fandle allocation failures
               1  parse count (hard)
               0  parse count (failures)
SELECT S.role10_id_jezdce.jmeno AS jmeno, S.role10_id_jezdce.prijmeni AS prijmeni, COUNT(*) as pocet
  FROM se_ucastnil_obj S
  GROUP BY s.role10_id_jezdce.jmeno, s.role10_id_jezdce.prijmeni
  HAVING COUNT(*) > 1

JMENO                          PRIJMENI                       POCET                  
------------------------------ ------------------------------ ---------------------- 
Michal                         Andy                           5                      
Marek                          Handlu                         2                      

2 rows selected

Plan hash value: 857617281
 
--------------------------------------------------------------------------------------------------
| Id  | Operation                      | Name            | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT               |                 |     1 |    38 |     5  (20)| 00:00:01 |
|*  1 |  FILTER                        |                 |       |       |            |          |
|   2 |   HASH GROUP BY                |                 |     1 |    38 |     5  (20)| 00:00:01 |
|   3 |    NESTED LOOPS OUTER          |                 |    20 |   760 |     4   (0)| 00:00:01 |
|   4 |     TABLE ACCESS FULL          | SE_UCASTNIL_OBJ |    10 |   160 |     2   (0)| 00:00:01 |
|   5 |     TABLE ACCESS BY INDEX ROWID| JEZDEC_OBJ      |     2 |    44 |     1   (0)| 00:00:01 |
|*  6 |      INDEX UNIQUE SCAN         | SYS_C001514379  |     1 |       |     0   (0)| 00:00:01 |
--------------------------------------------------------------------------------------------------
 
Predicate Information (identified by operation id):
---------------------------------------------------
 
   1 - filter(COUNT(*)>1)
   6 - access("P000003$"."SYS_NC_OID$"(+)="S"."ROLE10_ID_JEZDCE")

   Statistics
-----------------------------------------------------------
              15  recursive calls
               0  db block gets
              21  consistent gets
               0  physical reads
               0  redo size
               0  total read fandles
               0  read fandles in use
               0  fandle allocation failures
               1  parse count (hard)
               0  parse count (failures)
-- alias pocet tady nefunguje

   Statistics
-----------------------------------------------------------
               0  recursive calls
               0  db block gets
               0  consistent gets
               0  physical reads
               0  redo size
               0  total read fandles
               0  read fandles in use
               0  fandle allocation failures
               0  parse count (hard)
               0  parse count (failures)
-- Jm�na jezdc�, kte�� maj� v�echny discipl�ny uveden� jako obl�ben�. Pro pot�eby SQL je nutn� p�ev�zt obecn� kvantifik�tor na existen�n�. Tedy nap�: Jm�na jezdc�, pro kter� neexistuje discipl�na, kterou by nem�li r�di. Nebo je�t� slo�it�j� d�ky v�cen�sobn� negaci v �esk�ch v�t�ch: Jm�na jezdc�, pro n� ��dn� discipl�na v syst�mu nen� neobl�ben�.

   Statistics
-----------------------------------------------------------
               0  recursive calls
               0  db block gets
               0  consistent gets
               0  physical reads
               0  redo size
               0  total read fandles
               0  read fandles in use
               0  fandle allocation failures
               0  parse count (hard)
               0  parse count (failures)
SELECT (J.jmeno || ' ' || J.prijmeni) AS cele_jmeno
  FROM jezdec J
  WHERE NOT EXISTS (SELECT nazev FROM disciplina D
                    MINUS
                    SELECT M.role6_nazev AS nazev FROM ma_rad M WHERE M.role3_id_jezdce = J.id_jezdce)

CELE_JMENO                                                    
------------------------------------------------------------- 
Jan Schusti                                                   

1 rows selected

Plan hash value: 110028514
 
--------------------------------------------------------------------------------------
| Id  | Operation            | Name          | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT     |               |     9 |   126 |    22  (46)| 00:00:01 |
|*  1 |  FILTER              |               |       |       |            |          |
|   2 |   TABLE ACCESS FULL  | JEZDEC        |    10 |   140 |     2   (0)| 00:00:01 |
|   3 |   MINUS              |               |       |       |            |          |
|   4 |    SORT UNIQUE NOSORT|               |    10 |    50 |     2  (50)| 00:00:01 |
|   5 |     INDEX FULL SCAN  | PK_DISCIPLINA |    10 |    50 |     1   (0)| 00:00:01 |
|   6 |    SORT UNIQUE NOSORT|               |     2 |    12 |     2  (50)| 00:00:01 |
|*  7 |     INDEX FULL SCAN  | UNQ_MA_RAD_1  |     2 |    12 |     1   (0)| 00:00:01 |
--------------------------------------------------------------------------------------
 
Predicate Information (identified by operation id):
---------------------------------------------------
 
   1 - filter( NOT EXISTS ( (SELECT /*+ */ "NAZEV" FROM "DISCIPLINA" 
              "D")MINUS (SELECT /*+ */ "M"."ROLE6_NAZEV" FROM "MA_RAD" "M" WHERE 
              "M"."ROLE3_ID_JEZDCE"=:B1)))
   7 - access("M"."ROLE3_ID_JEZDCE"=:B1)
       filter("M"."ROLE3_ID_JEZDCE"=:B1)

   Statistics
-----------------------------------------------------------
               1  recursive calls
               0  db block gets
              25  consistent gets
               0  physical reads
               0  redo size
               0  total read fandles
               0  read fandles in use
               0  fandle allocation failures
               1  parse count (hard)
               0  parse count (failures)
SELECT (J.jmeno || ' ' || J.prijmeni) AS cele_jmeno
  FROM jezdec J
  WHERE (SELECT COUNT(DISTINCT(M.role6_nazev)) FROM ma_rad M WHERE M.role3_id_jezdce=J.id_jezdce) =
    (SELECT COUNT(*) FROM disciplina)

CELE_JMENO                                                    
------------------------------------------------------------- 
Jan Schusti                                                   

1 rows selected

Plan hash value: 1749947326
 
-------------------------------------------------------------------------------------
| Id  | Operation           | Name          | Rows  | Bytes | Cost (%CPU)| Time     |
-------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT    |               |    10 |   140 |     3   (0)| 00:00:01 |
|*  1 |  FILTER             |               |       |       |            |          |
|   2 |   TABLE ACCESS FULL | JEZDEC        |    10 |   140 |     2   (0)| 00:00:01 |
|   3 |   SORT GROUP BY     |               |     1 |     6 |            |          |
|*  4 |    INDEX FULL SCAN  | UNQ_MA_RAD_1  |     2 |    12 |     1   (0)| 00:00:01 |
|   5 |     SORT AGGREGATE  |               |     1 |       |            |          |
|   6 |      INDEX FULL SCAN| PK_DISCIPLINA |    10 |       |     1   (0)| 00:00:01 |
-------------------------------------------------------------------------------------
 
Predicate Information (identified by operation id):
---------------------------------------------------
 
   1 - filter( (SELECT COUNT(DISTINCT "M"."ROLE6_NAZEV") FROM "MA_RAD" "M" 
              WHERE "M"."ROLE3_ID_JEZDCE"=:B1)= (SELECT COUNT(*) FROM "DISCIPLINA" 
              "DISCIPLINA"))
   4 - access("M"."ROLE3_ID_JEZDCE"=:B1)
       filter("M"."ROLE3_ID_JEZDCE"=:B1)

   Statistics
-----------------------------------------------------------
               1  recursive calls
               0  db block gets
               8  consistent gets
               0  physical reads
               0  redo size
               0  total read fandles
               0  read fandles in use
               0  fandle allocation failures
               1  parse count (hard)
               0  parse count (failures)
SELECT (J.jmeno || ' ' || J.prijmeni) AS cele_jmeno
  FROM jezdec_obj J
  WHERE (SELECT COUNT(DISTINCT(role6_nazev)) FROM ma_rad_obj M WHERE M.role3_id_jezdce=REF(J)) =
          (SELECT COUNT(*) FROM disciplina_obj)

CELE_JMENO                                                    
------------------------------------------------------------- 
Jan Schusti                                                   

1 rows selected

Plan hash value: 3289653464
 
--------------------------------------------------------------------------------------
| Id  | Operation           | Name           | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT    |                |    10 |   340 |     4   (0)| 00:00:01 |
|*  1 |  FILTER             |                |       |       |            |          |
|   2 |   TABLE ACCESS FULL | JEZDEC_OBJ     |    10 |   340 |     2   (0)| 00:00:01 |
|   3 |   SORT GROUP BY     |                |     1 |    32 |            |          |
|*  4 |    TABLE ACCESS FULL| MA_RAD_OBJ     |     2 |    64 |     2   (0)| 00:00:01 |
|   5 |     SORT AGGREGATE  |                |     1 |       |            |          |
|   6 |      INDEX FULL SCAN| SYS_C001514389 |    10 |       |     1   (0)| 00:00:01 |
--------------------------------------------------------------------------------------
 
Predicate Information (identified by operation id):
---------------------------------------------------
 
   1 - filter( (SELECT COUNT(DISTINCT "ROLE6_NAZEV") FROM "MA_RAD_OBJ" "M" 
              WHERE "M"."SYS_NC00005$"=:B1)= (SELECT COUNT(*) FROM "DISCIPLINA_OBJ" 
              "DISCIPLINA_OBJ"))
   4 - filter("M"."SYS_NC00005$"=:B1)

   Statistics
-----------------------------------------------------------
              15  recursive calls
               0  db block gets
              60  consistent gets
               0  physical reads
               0  redo size
               0  total read fandles
               0  read fandles in use
               0  fandle allocation failures
               1  parse count (hard)
               0  parse count (failures)
-- N�zvy a p��padn� www adresy obchod�, kde provedl jezdec s p�ezd�vkou Mareen alespo� jeden n�kup.

   Statistics
-----------------------------------------------------------
               0  recursive calls
               0  db block gets
               0  consistent gets
               0  physical reads
               0  redo size
               0  total read fandles
               0  read fandles in use
               0  fandle allocation failures
               0  parse count (hard)
               0  parse count (failures)
SELECT nazev, www
  FROM obchod
  WHERE EXISTS (SELECT *
                  FROM nakup
                  JOIN jezdec ON(je_id_jezdce=id_jezdce)
                  WHERE prezdivka='Mareen' AND ob_nazev=nazev)

NAZEV                                              WWW                                                                                                  
-------------------------------------------------- ---------------------------------------------------------------------------------------------------- 
901 Bikes                                                                                                                                               
Dookie                                             www.dookie.cz                                                                                        

2 rows selected

Plan hash value: 675024332
 
---------------------------------------------------------------------------------
| Id  | Operation            | Name     | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------------
|   0 | SELECT STATEMENT     |          |     2 |    84 |     6  (17)| 00:00:01 |
|*  1 |  HASH JOIN SEMI      |          |     2 |    84 |     6  (17)| 00:00:01 |
|   2 |   TABLE ACCESS FULL  | OBCHOD   |     5 |    75 |     2   (0)| 00:00:01 |
|   3 |   VIEW               | VW_SQ_1  |     2 |    54 |     3   (0)| 00:00:01 |
|   4 |    NESTED LOOPS      |          |     2 |    40 |     3   (0)| 00:00:01 |
|*  5 |     TABLE ACCESS FULL| JEZDEC   |     1 |     9 |     2   (0)| 00:00:01 |
|*  6 |     INDEX FULL SCAN  | PK_NAKUP |     2 |    22 |     1   (0)| 00:00:01 |
---------------------------------------------------------------------------------
 
Predicate Information (identified by operation id):
---------------------------------------------------
 
   1 - access("OB_NAZEV"="NAZEV")
   5 - filter("JEZDEC"."PREZDIVKA"='Mareen')
   6 - access("JE_ID_JEZDCE"="ID_JEZDCE")
       filter("JE_ID_JEZDCE"="ID_JEZDCE")

   Statistics
-----------------------------------------------------------
               1  recursive calls
               0  db block gets
              11  consistent gets
               0  physical reads
               0  redo size
               0  total read fandles
               0  read fandles in use
               0  fandle allocation failures
               1  parse count (hard)
               0  parse count (failures)
SELECT nazev, www
  FROM obchod
  WHERE nazev IN ( SELECT ob_nazev
                    FROM nakup
                    WHERE je_id_jezdce=(SELECT id_jezdce FROM jezdec WHERE prezdivka='Mareen') )

NAZEV                                              WWW                                                                                                  
-------------------------------------------------- ---------------------------------------------------------------------------------------------------- 
901 Bikes                                                                                                                                               
Dookie                                             www.dookie.cz                                                                                        

2 rows selected

Plan hash value: 3226301129
 
---------------------------------------------------------------------------------
| Id  | Operation            | Name     | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------------
|   0 | SELECT STATEMENT     |          |     2 |    84 |     6  (17)| 00:00:01 |
|*  1 |  HASH JOIN SEMI      |          |     2 |    84 |     6  (17)| 00:00:01 |
|   2 |   TABLE ACCESS FULL  | OBCHOD   |     5 |    75 |     2   (0)| 00:00:01 |
|   3 |   VIEW               | VW_NSO_1 |     2 |    54 |     3   (0)| 00:00:01 |
|*  4 |    INDEX FULL SCAN   | PK_NAKUP |     2 |    22 |     1   (0)| 00:00:01 |
|*  5 |     TABLE ACCESS FULL| JEZDEC   |     1 |     9 |     2   (0)| 00:00:01 |
---------------------------------------------------------------------------------
 
Predicate Information (identified by operation id):
---------------------------------------------------
 
   1 - access("NAZEV"="$nso_col_1")
   4 - access("JE_ID_JEZDCE"= (SELECT "ID_JEZDCE" FROM "JEZDEC" "JEZDEC" 
              WHERE "PREZDIVKA"='Mareen'))
       filter("JE_ID_JEZDCE"= (SELECT "ID_JEZDCE" FROM "JEZDEC" "JEZDEC" 
              WHERE "PREZDIVKA"='Mareen'))
   5 - filter("PREZDIVKA"='Mareen')

   Statistics
-----------------------------------------------------------
               1  recursive calls
               0  db block gets
              11  consistent gets
               0  physical reads
               0  redo size
               0  total read fandles
               0  read fandles in use
               0  fandle allocation failures
               1  parse count (hard)
               0  parse count (failures)
SELECT DISTINCT DEREF(ob_nazev).nazev AS nazev, DEREF(ob_nazev).www AS www
  FROM nakup_obj N
  WHERE DEREF(je_id_jezdce).prezdivka = 'Mareen'

NAZEV                                              WWW                                                                                                  
-------------------------------------------------- ---------------------------------------------------------------------------------------------------- 
Dookie                                             www.dookie.cz                                                                                        
901 Bikes                                                                                                                                               

2 rows selected

Plan hash value: 2361537705
 
--------------------------------------------------------------------------------------------------
| Id  | Operation                       | Name           | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                |                |     8 |   592 |     8  (25)| 00:00:01 |
|   1 |  HASH UNIQUE                    |                |     8 |   592 |     8  (25)| 00:00:01 |
|*  2 |   FILTER                        |                |       |       |            |          |
|*  3 |    HASH JOIN OUTER              |                |    39 |  2886 |     7  (15)| 00:00:01 |
|   4 |     NESTED LOOPS OUTER          |                |    12 |   684 |     4   (0)| 00:00:01 |
|   5 |      TABLE ACCESS FULL          | NAKUP_OBJ      |     7 |   224 |     2   (0)| 00:00:01 |
|   6 |      TABLE ACCESS BY INDEX ROWID| OBCHOD_OBJ     |     2 |    50 |     1   (0)| 00:00:01 |
|*  7 |       INDEX UNIQUE SCAN         | SYS_C001514335 |     1 |       |     0   (0)| 00:00:01 |
|   8 |     TABLE ACCESS FULL           | JEZDEC_OBJ     |    10 |   170 |     2   (0)| 00:00:01 |
--------------------------------------------------------------------------------------------------
 
Predicate Information (identified by operation id):
---------------------------------------------------
 
   2 - filter(DECODE("P000005$"."SYS_NC_OID$",NULL,SYS_OP_ATG(NULL,4,5,2),"P000005$"."PREZ
              DIVKA")='Mareen')
   3 - access("P000005$"."SYS_NC_OID$"(+)="N"."SYS_NC00009$")
   7 - access("P000003$"."SYS_NC_OID$"(+)="N"."SYS_NC00008$")

   Statistics
-----------------------------------------------------------
              22  recursive calls
               0  db block gets
              25  consistent gets
               0  physical reads
               0  redo size
               0  total read fandles
               0  read fandles in use
               0  fandle allocation failures
               1  parse count (hard)
               0  parse count (failures)
-- Jm�na a p��jmen� jezdc�, kte�� se je�t� nez��astnili ��dn�ho z�vodu.

   Statistics
-----------------------------------------------------------
               0  recursive calls
               0  db block gets
               0  consistent gets
               0  physical reads
               0  redo size
               0  total read fandles
               0  read fandles in use
               0  fandle allocation failures
               0  parse count (hard)
               0  parse count (failures)
SELECT jmeno, prijmeni FROM jezdec WHERE id_jezdce NOT IN (SELECT role10_id_jezdce FROM se_ucastnil)

JMENO                          PRIJMENI                       
------------------------------ ------------------------------ 
Tomá‘                          Andy                           
Martin                         Holý                           
Jakub                          Růža                           
Štěpán                         Grundl                         
Michal                         Benest                         

5 rows selected

Plan hash value: 260798367
 
----------------------------------------------------------------------------------------
| Id  | Operation          | Name              | Rows  | Bytes | Cost (%CPU)| Time     |
----------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |                   |     5 |    80 |     4  (25)| 00:00:01 |
|*  1 |  HASH JOIN ANTI    |                   |     5 |    80 |     4  (25)| 00:00:01 |
|   2 |   TABLE ACCESS FULL| JEZDEC            |    10 |   140 |     2   (0)| 00:00:01 |
|   3 |   INDEX FULL SCAN  | UNQ_SE_UCASTNIL_1 |    10 |    20 |     1   (0)| 00:00:01 |
----------------------------------------------------------------------------------------
 
Predicate Information (identified by operation id):
---------------------------------------------------
 
   1 - access("ID_JEZDCE"="ROLE10_ID_JEZDCE")

   Statistics
-----------------------------------------------------------
               1  recursive calls
               0  db block gets
               6  consistent gets
               0  physical reads
               0  redo size
               0  total read fandles
               0  read fandles in use
               0  fandle allocation failures
               1  parse count (hard)
               0  parse count (failures)
SELECT jmeno, prijmeni FROM jezdec WHERE NOT EXISTS (SELECT * FROM se_ucastnil WHERE id_jezdce= role10_id_jezdce)

JMENO                          PRIJMENI                       
------------------------------ ------------------------------ 
Tomá‘                          Andy                           
Martin                         Holý                           
Jakub                          Růža                           
Štěpán                         Grundl                         
Michal                         Benest                         

5 rows selected

Plan hash value: 260798367
 
----------------------------------------------------------------------------------------
| Id  | Operation          | Name              | Rows  | Bytes | Cost (%CPU)| Time     |
----------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |                   |     5 |    80 |     4  (25)| 00:00:01 |
|*  1 |  HASH JOIN ANTI    |                   |     5 |    80 |     4  (25)| 00:00:01 |
|   2 |   TABLE ACCESS FULL| JEZDEC            |    10 |   140 |     2   (0)| 00:00:01 |
|   3 |   INDEX FULL SCAN  | UNQ_SE_UCASTNIL_1 |    10 |    20 |     1   (0)| 00:00:01 |
----------------------------------------------------------------------------------------
 
Predicate Information (identified by operation id):
---------------------------------------------------
 
   1 - access("ID_JEZDCE"="ROLE10_ID_JEZDCE")

   Statistics
-----------------------------------------------------------
               1  recursive calls
               0  db block gets
               6  consistent gets
               0  physical reads
               0  redo size
               0  total read fandles
               0  read fandles in use
               0  fandle allocation failures
               1  parse count (hard)
               0  parse count (failures)
SELECT J.jmeno, J.prijmeni FROM jezdec_obj J WHERE REF(J) NOT IN (SELECT role10_id_jezdce FROM se_ucastnil_obj)

JMENO                          PRIJMENI                       
------------------------------ ------------------------------ 
Tomá‘                          Andy                           
Jakub                          Růža                           
Štěpán                         Grundl                         
Michal                         Benest                         
Martin                         Holý                           

5 rows selected

Plan hash value: 2351109479
 
--------------------------------------------------------------------------------------
| Id  | Operation          | Name            | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |                 |     1 |    34 |     4   (0)| 00:00:01 |
|*  1 |  FILTER            |                 |       |       |            |          |
|   2 |   TABLE ACCESS FULL| JEZDEC_OBJ      |    10 |   340 |     2   (0)| 00:00:01 |
|*  3 |   TABLE ACCESS FULL| SE_UCASTNIL_OBJ |     2 |    32 |     2   (0)| 00:00:01 |
--------------------------------------------------------------------------------------
 
Predicate Information (identified by operation id):
---------------------------------------------------
 
   1 - filter( NOT EXISTS (SELECT /*+ */ 0 FROM "SE_UCASTNIL_OBJ" 
              "SE_UCASTNIL_OBJ" WHERE LNNVL("SE_UCASTNIL_OBJ"."SYS_NC00006$"<>:B1)))
   3 - filter(LNNVL("SE_UCASTNIL_OBJ"."SYS_NC00006$"<>:B1))

   Statistics
-----------------------------------------------------------
              15  recursive calls
               0  db block gets
              49  consistent gets
               0  physical reads
               0  redo size
               0  total read fandles
               0  read fandles in use
               0  fandle allocation failures
               1  parse count (hard)
               0  parse count (failures)
-- Jm�na a p��jmen� v�ech registrovan�ch jezdc�.

   Statistics
-----------------------------------------------------------
               0  recursive calls
               0  db block gets
               0  consistent gets
               0  physical reads
               0  redo size
               0  total read fandles
               0  read fandles in use
               0  fandle allocation failures
               0  parse count (hard)
               0  parse count (failures)
SELECT jmeno, prijmeni FROM jezdec

JMENO                          PRIJMENI                       
------------------------------ ------------------------------ 
Marek                          Handlu                         
Michal                         Andy                           
Jan                            Schusti                        
Jakub                          Anton                          
Tomá‘                          Andy                           
Jakub                          Růža                           
Štěpán                         Grundl                         
Pavel                          Herink                         
Michal                         Benest                         
Martin                         Holý                           

10 rows selected

Plan hash value: 4067037959
 
----------------------------------------------------------------------------
| Id  | Operation         | Name   | Rows  | Bytes | Cost (%CPU)| Time     |
----------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |        |    10 |   120 |     2   (0)| 00:00:01 |
|   1 |  TABLE ACCESS FULL| JEZDEC |    10 |   120 |     2   (0)| 00:00:01 |
----------------------------------------------------------------------------

   Statistics
-----------------------------------------------------------
               1  recursive calls
               0  db block gets
               5  consistent gets
               0  physical reads
               0  redo size
               0  total read fandles
               0  read fandles in use
               0  fandle allocation failures
               1  parse count (hard)
               0  parse count (failures)
SELECT jmeno, prijmeni FROM jezdec ORDER BY id_jezdce

JMENO                          PRIJMENI                       
------------------------------ ------------------------------ 
Marek                          Handlu                         
Michal                         Andy                           
Jan                            Schusti                        
Jakub                          Anton                          
Tomá‘                          Andy                           
Jakub                          Růža                           
Štěpán                         Grundl                         
Pavel                          Herink                         
Michal                         Benest                         
Martin                         Holý                           

10 rows selected

Plan hash value: 2482864222
 
-----------------------------------------------------------------------------------------
| Id  | Operation                   | Name      | Rows  | Bytes | Cost (%CPU)| Time     |
-----------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT            |           |    10 |   140 |     2   (0)| 00:00:01 |
|   1 |  TABLE ACCESS BY INDEX ROWID| JEZDEC    |    10 |   140 |     2   (0)| 00:00:01 |
|   2 |   INDEX FULL SCAN           | PK_JEZDEC |    10 |       |     1   (0)| 00:00:01 |
-----------------------------------------------------------------------------------------

   Statistics
-----------------------------------------------------------
               1  recursive calls
               0  db block gets
               2  consistent gets
               0  physical reads
               0  redo size
               0  total read fandles
               0  read fandles in use
               0  fandle allocation failures
               1  parse count (hard)
               0  parse count (failures)
SELECT jmeno, prijmeni FROM jezdec_obj

JMENO                          PRIJMENI                       
------------------------------ ------------------------------ 
Marek                          Handlu                         
Michal                         Andy                           
Jan                            Schusti                        
Jakub                          Anton                          
Tomá‘                          Andy                           
Jakub                          Růža                           
Štěpán                         Grundl                         
Pavel                          Herink                         
Michal                         Benest                         
Martin                         Holý                           

10 rows selected

Plan hash value: 1770270476
 
--------------------------------------------------------------------------------
| Id  | Operation         | Name       | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |            |    10 |   120 |     2   (0)| 00:00:01 |
|   1 |  TABLE ACCESS FULL| JEZDEC_OBJ |    10 |   120 |     2   (0)| 00:00:01 |
--------------------------------------------------------------------------------

   Statistics
-----------------------------------------------------------
               1  recursive calls
               0  db block gets
               5  consistent gets
               0  physical reads
               0  redo size
               0  total read fandles
               0  read fandles in use
               0  fandle allocation failures
               1  parse count (hard)
               0  parse count (failures)
-- Jm�na a p��jmen� v�ech jezdc�, kte�� maj� jako svou obl�benou discipl�nu sjezd (DH).

   Statistics
-----------------------------------------------------------
               0  recursive calls
               0  db block gets
               0  consistent gets
               0  physical reads
               0  redo size
               0  total read fandles
               0  read fandles in use
               0  fandle allocation failures
               0  parse count (hard)
               0  parse count (failures)
SELECT jmeno, prijmeni FROM jezdec JOIN ma_rad ON(id_jezdce=role3_id_jezdce) WHERE role6_nazev='DH'

JMENO                          PRIJMENI                       
------------------------------ ------------------------------ 
Michal                         Andy                           
Jan                            Schusti                        
Jakub                          Anton                          
Pavel                          Herink                         

4 rows selected

Plan hash value: 656974699
 
-----------------------------------------------------------------------------------
| Id  | Operation          | Name         | Rows  | Bytes | Cost (%CPU)| Time     |
-----------------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |              |     2 |    40 |     2   (0)| 00:00:01 |
|   1 |  NESTED LOOPS      |              |     2 |    40 |     2   (0)| 00:00:01 |
|   2 |   TABLE ACCESS FULL| JEZDEC       |    10 |   140 |     2   (0)| 00:00:01 |
|*  3 |   INDEX UNIQUE SCAN| UNQ_MA_RAD_1 |     1 |     6 |     0   (0)| 00:00:01 |
-----------------------------------------------------------------------------------
 
Predicate Information (identified by operation id):
---------------------------------------------------
 
   3 - access("MA_RAD"."ROLE6_NAZEV"='DH' AND 
              "ROLE3_ID_JEZDCE"="ID_JEZDCE")

   Statistics
-----------------------------------------------------------
               1  recursive calls
               0  db block gets
               7  consistent gets
               0  physical reads
               0  redo size
               0  total read fandles
               0  read fandles in use
               0  fandle allocation failures
               1  parse count (hard)
               0  parse count (failures)
SELECT jmeno, prijmeni
  FROM jezdec
  WHERE EXISTS (SELECT * FROM ma_rad WHERE role6_nazev='DH' AND id_jezdce=role3_id_jezdce)

JMENO                          PRIJMENI                       
------------------------------ ------------------------------ 
Michal                         Andy                           
Jan                            Schusti                        
Jakub                          Anton                          
Pavel                          Herink                         

4 rows selected

Plan hash value: 379750019
 
-----------------------------------------------------------------------------------
| Id  | Operation          | Name         | Rows  | Bytes | Cost (%CPU)| Time     |
-----------------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |              |     3 |    60 |     2   (0)| 00:00:01 |
|   1 |  NESTED LOOPS SEMI |              |     3 |    60 |     2   (0)| 00:00:01 |
|   2 |   TABLE ACCESS FULL| JEZDEC       |    10 |   140 |     2   (0)| 00:00:01 |
|*  3 |   INDEX UNIQUE SCAN| UNQ_MA_RAD_1 |     1 |     6 |     0   (0)| 00:00:01 |
-----------------------------------------------------------------------------------
 
Predicate Information (identified by operation id):
---------------------------------------------------
 
   3 - access("ROLE6_NAZEV"='DH' AND "ID_JEZDCE"="ROLE3_ID_JEZDCE")

   Statistics
-----------------------------------------------------------
               1  recursive calls
               0  db block gets
               7  consistent gets
               0  physical reads
               0  redo size
               0  total read fandles
               0  read fandles in use
               0  fandle allocation failures
               1  parse count (hard)
               0  parse count (failures)
SELECT DEREF(role3_id_jezdce).jmeno AS jmeno, DEREF(role3_id_jezdce).prijmeni AS prijmeni
  FROM ma_rad_obj
  WHERE DEREF(role6_nazev).nazev = 'DH'

JMENO                          PRIJMENI                       
------------------------------ ------------------------------ 
Michal                         Andy                           
Jan                            Schusti                        
Jakub                          Anton                          
Pavel                          Herink                         

4 rows selected

Plan hash value: 292875401
 
-------------------------------------------------------------------------------------------------
| Id  | Operation                      | Name           | Rows  | Bytes | Cost (%CPU)| Time     |
-------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT               |                |    24 |  1656 |     7   (0)| 00:00:01 |
|   1 |  NESTED LOOPS OUTER            |                |    24 |  1656 |     7   (0)| 00:00:01 |
|*  2 |   FILTER                       |                |       |       |            |          |
|   3 |    NESTED LOOPS OUTER          |                |    24 |  1128 |     4   (0)| 00:00:01 |
|   4 |     TABLE ACCESS FULL          | MA_RAD_OBJ     |    24 |   768 |     2   (0)| 00:00:01 |
|   5 |     TABLE ACCESS BY INDEX ROWID| DISCIPLINA_OBJ |     1 |    15 |     1   (0)| 00:00:01 |
|*  6 |      INDEX UNIQUE SCAN         | SYS_C001514388 |     1 |       |     0   (0)| 00:00:01 |
|   7 |   TABLE ACCESS BY INDEX ROWID  | JEZDEC_OBJ     |     1 |    22 |     1   (0)| 00:00:01 |
|*  8 |    INDEX UNIQUE SCAN           | SYS_C001514379 |     1 |       |     0   (0)| 00:00:01 |
-------------------------------------------------------------------------------------------------
 
Predicate Information (identified by operation id):
---------------------------------------------------
 
   2 - filter(DECODE("P000005$"."SYS_NC_OID$",NULL,SYS_OP_ATG(NULL,2,3,2),"P000005$"."NAZ
              EV")='DH')
   6 - access("P000005$"."SYS_NC_OID$"(+)="MA_RAD_OBJ"."SYS_NC00006$")
   8 - access("P000003$"."SYS_NC_OID$"(+)="MA_RAD_OBJ"."SYS_NC00005$")

   Statistics
-----------------------------------------------------------
              15  recursive calls
               0  db block gets
              41  consistent gets
               0  physical reads
               0  redo size
               0  total read fandles
               0  read fandles in use
               0  fandle allocation failures
               1  parse count (hard)
               0  parse count (failures)
SET AUTOTRACE OFF

Autotrace Disabled
/* ***************************************************************************
 * TRIGGERY
 * *************************************************************************** */

/* ***************************************************************************
 * MODUL 1
 * *************************************************************************** */

create or replace
PROCEDURE
  pridejCelkovaCastka
  AS
    v_dummy INTEGER;
  BEGIN
    SELECT 1 INTO v_dummy
      FROM USER_TAB_COLUMNS
      WHERE table_name = 'jezdec' AND column_name = 'celkova_castka';
      
  EXCEPTION
    WHEN NO_DATA_FOUND THEN
      EXECUTE IMMEDIATE 'ALTER TABLE jezdec ADD celkova_castka NUMBER DEFAULT 0';
  
      -- spocti utracene castky pro vsechny jezdce
      FOR radek IN (SELECT id_jezdce FROM jezdec)
      LOOP
        -- je nutne pouzivat execute immediate, protoze v dobe kompilace sloupec jeste neexistuje
        EXECUTE IMMEDIATE
          'UPDATE jezdec
            SET celkova_castka = (SELECT COALESCE(SUM(cena),0) FROM nakup WHERE je_id_jezdce = :1)
            WHERE id_jezdce = :2'
          USING radek.id_jezdce, radek.id_jezdce; 
      END LOOP;
    
  END pridejCelkovaCastka;

PROCEDURE  Compiled.
create or replace
TRIGGER
  upravCastkuZaKomponenty
  AFTER INSERT OR UPDATE OR DELETE ON nakup
  FOR EACH ROW
  DECLARE
    v_castka NUMBER := 0;
    v_id jezdec.id_jezdce%TYPE;
  BEGIN 
    IF DELETING THEN
      v_id := :OLD.je_id_jezdce;
    ELSE
      v_id := :NEW.je_id_jezdce;
    END IF;
    
    -- je nutne pouzivat execute immediate, protoze v dobe kompilace sloupec jeste neexistuje
    EXECUTE IMMEDIATE
      'SELECT celkova_castka
        FROM jezdec
        WHERE id_jezdce = :1'
      INTO v_castka
      USING v_id;
      
      
    IF INSERTING THEN
      v_castka := v_castka + :NEW.cena;
    ELSE IF DELETING THEN
      v_castka := v_castka - :OLD.cena;
    ELSE -- IF UPDATING
      v_castka := v_castka - :OLD.cena + :NEW.cena;
    END IF;
    END IF;

    -- je nutne pouzivat execute immediate, protoze v dobe kompilace sloupec jeste neexistuje
    EXECUTE IMMEDIATE
      'UPDATE jezdec
          SET celkova_castka = :1
          WHERE id_jezdce = :2'
      USING v_castka, v_id;
        
  END upravCastkuZaKomponenty;

TRIGGER  Compiled.
-- vytvorim sloupec potrebny pro funkcionalitu

EXECUTE pridejCelkovaCastka();

anonymous block completed
-- p�id�m n�kup komponentu s nulovou cenou a zkontroluji korektn� napln�n� nov�ho sloupce

EXECUTE pridejPlast('Maxxis', 'Ignitor', 2008, 26, 2.5);

anonymous block completed
INSERT INTO nakup (cena, datum, ob_nazev, je_id_jezdce, ko_vyrobce, ko_model, ko_rok_vyroby)
  VALUES (0, '30-05-09', 'Dookie', 1, 'Maxxis', 'Ignitor', 2008)

1 rows inserted
SELECT celkova_castka FROM jezdec WHERE id_jezdce=1

CELKOVA_CASTKA         
---------------------- 
11149                  

1 rows selected

-- vyma�u p�edchoz� n�kup

DELETE FROM nakup
  WHERE datum='30-05-09' AND ob_nazev='Dookie' AND je_id_jezdce=1 AND ko_vyrobce='Maxxis' AND ko_model='Ignitor' AND ko_rok_vyroby=2008

1 rows deleted
SELECT celkova_castka FROM jezdec WHERE id_jezdce=1

CELKOVA_CASTKA         
---------------------- 
11149                  

1 rows selected

-- p�id�m n�kup komponentu s nenulovou cenou  

INSERT INTO nakup (cena, datum, ob_nazev, je_id_jezdce, ko_vyrobce, ko_model, ko_rok_vyroby)
  VALUES (100, '30-05-09', 'Dookie', 1, 'Maxxis', 'Ignitor', 2008)

1 rows inserted
SELECT celkova_castka FROM jezdec WHERE id_jezdce=1

CELKOVA_CASTKA         
---------------------- 
11249                  

1 rows selected

-- uprav�m n�kup zv�en�m ceny

UPDATE nakup
  SET cena = 200
  WHERE datum='30-05-09' AND ob_nazev='Dookie' AND je_id_jezdce=1 AND ko_vyrobce='Maxxis' AND ko_model='Ignitor' AND ko_rok_vyroby=2008

1 rows updated
SELECT celkova_castka FROM jezdec WHERE id_jezdce=1

CELKOVA_CASTKA         
---------------------- 
11349                  

1 rows selected

-- vyma�u p�edchoz� n�kup

DELETE FROM nakup
  WHERE datum='30-05-09' AND ob_nazev='Dookie' AND je_id_jezdce=1 AND ko_vyrobce='Maxxis' AND ko_model='Ignitor' AND ko_rok_vyroby=2008

1 rows deleted
SELECT celkova_castka FROM jezdec WHERE id_jezdce=1

CELKOVA_CASTKA         
---------------------- 
11149                  

1 rows selected

-- odstranim vytvoreny sloupec - rollback nestaci, protoze DDL dela autocommit

ALTER TABLE jezdec drop column celkova_castka

ALTER TABLE jezdec succeeded.
DELETE FROM nakup WHERE ko_model='Ignitor'

0 rows deleted
DELETE FROM plast WHERE ko_model='Ignitor'

1 rows deleted
DELETE FROM komponent WHERE model='Ignitor'

1 rows deleted
COMMIT

COMMIT succeeded.
ALTER TRIGGER upravCastkuZaKomponenty DISABLE

ALTER TRIGGER upravCastkuZaKomponenty succeeded.
/* ***************************************************************************
 * MODUL 2
 * *************************************************************************** */

create or replace
procedure
  pridejPlast(  p_vyrobce     IN komponent.vyrobce%TYPE,
                p_model       IN komponent.model%TYPE,
                p_rok_vyroby  IN komponent.rok_vyroby%TYPE,
                p_polomer     IN plast.polomer%TYPE,
                p_sirka       IN plast.sirka%TYPE)
  AS
  BEGIN
    INSERT INTO komponent (vyrobce, model, rok_vyroby)
      VALUES (p_vyrobce, p_model, p_rok_vyroby);
    INSERT INTO plast (ko_vyrobce, ko_model, ko_rok_vyroby, polomer, sirka)
      VALUES (p_vyrobce, p_model, p_rok_vyroby, p_polomer, p_sirka);
  END pridejPlast;

procedure  Compiled.
create or replace
trigger
  polomeryPlastu
  BEFORE INSERT OR UPDATE ON pouziva
  FOR EACH ROW
  DECLARE
    v_pasujeDoRamu NUMBER := 0;
    v_pasujeDoVidlice NUMBER := 0;
  BEGIN
    -- zjisti, jestli je mozne dany plast pouzit v nekterem z ramu, ktere jezdec pouziva (vzhledem k polomeru plaste)
    SELECT COUNT(*) INTO v_pasujeDoRamu
      FROM pouziva P JOIN ram R ON(role23_model=R.ko_model AND role23_vyrobce=R.ko_vyrobce AND role23_rok_vyroby=R.ko_rok_vyroby)
      WHERE P.role11_id_jezdce = :NEW.role11_id_jezdce AND
            R.polomer_kola=(SELECT plast.polomer FROM plast WHERE ko_vyrobce=:NEW.role23_vyrobce AND ko_model=:NEW.role23_model AND ko_rok_vyroby=:NEW.role23_rok_vyroby);
    
    -- zjisti, jestli je mozne dany plast pouzit v nektere z vidlic, ktere jezdec pouziva (vzhledem k polomeru plaste)
    SELECT COUNT(*) INTO v_pasujeDoVidlice
      FROM pouziva P JOIN vidlice R ON(role23_model=R.ko_model AND role23_vyrobce=R.ko_vyrobce AND role23_rok_vyroby=R.ko_rok_vyroby)
      WHERE P.role11_id_jezdce = :NEW.role11_id_jezdce AND
            R.polomer_kola=(SELECT plast.polomer FROM plast WHERE ko_vyrobce=:NEW.role23_vyrobce AND ko_model=:NEW.role23_model AND ko_rok_vyroby=:NEW.role23_rok_vyroby);
    
    IF (v_pasujeDoRamu=0 AND v_pasujeDoVidlice=0) THEN
      RAISE_APPLICATION_ERROR(-20208, 'Neni mozne pouzivat plast, ktery nepasuje ani do vidlice ani do ramu');
    END IF;
  END polomeryPlastu;

trigger  Compiled.
-- jezdec m� r�m pro 26" a vidlici pro 26", zkus�m mu p�idat pl᚝ 24"

EXECUTE pridejPlast('Maxxis', 'Ignitor', '2008', 24, 2.5);

anonymous block completed
INSERT INTO pouziva (role11_id_jezdce, role23_vyrobce, role23_model, role23_rok_vyroby)
  VALUES (1, 'Maxxis', 'Ignitor', '2008')


Error starting at line 1 138 in command:
INSERT INTO pouziva (role11_id_jezdce, role23_vyrobce, role23_model, role23_rok_vyroby)
  VALUES (1, 'Maxxis', 'Ignitor', '2008')
Error report:
SQL Error: ORA-20208: Neni mozne pouzivat plast, ktery nepasuje ani do vidlice ani do ramu
ORA-06512: na "HANDLM1.POLOMERYPLASTU", line 18
ORA-04088: chyba během provádění triggeru 'HANDLM1.POLOMERYPLASTU'

ROLLBACK

ROLLBACK succeeded.
-- jezdec m� r�m pro 26" a vidlici pro 26", zkus�m mu p�idat pl᚝ 26"

EXECUTE pridejPlast('Maxxis', 'Ignitor', '2008', 26, 2.5);

anonymous block completed
INSERT INTO pouziva (role11_id_jezdce, role23_vyrobce, role23_model, role23_rok_vyroby)
  VALUES (1, 'Maxxis', 'Ignitor', '2008')

1 rows inserted
ROLLBACK

ROLLBACK succeeded.
-- jezdec m� r�m pro 26" a vidlici pro 24", zkus�m mu p�idat pl᚝ 24"

UPDATE vidlice SET polomer_kola=24
  WHERE ko_vyrobce='Marzocchi' AND ko_model='DirtJumper III' AND ko_rok_vyroby=2004

1 rows updated
EXECUTE pridejPlast('Maxxis', 'Ignitor', '2008', 24, 2.5);

anonymous block completed
INSERT INTO pouziva (role11_id_jezdce, role23_vyrobce, role23_model, role23_rok_vyroby)
  VALUES (1, 'Maxxis', 'Ignitor', '2008')

1 rows inserted
ROLLBACK

ROLLBACK succeeded.
-- jezdec m� r�m pro 26" a vidlici pro 24", zkus�m mu p�idat pl᚝ 27"

UPDATE vidlice SET polomer_kola=24
  WHERE ko_vyrobce='Marzocchi' AND ko_model='DirtJumper III' AND ko_rok_vyroby=2004

1 rows updated
EXECUTE pridejPlast('Maxxis', 'Ignitor', '2008', 27, 2.5);

anonymous block completed
INSERT INTO pouziva (role11_id_jezdce, role23_vyrobce, role23_model, role23_rok_vyroby)
  VALUES (1, 'Maxxis', 'Ignitor', '2008')


Error starting at line 1 160 in command:
INSERT INTO pouziva (role11_id_jezdce, role23_vyrobce, role23_model, role23_rok_vyroby)
  VALUES (1, 'Maxxis', 'Ignitor', '2008')
Error report:
SQL Error: ORA-20208: Neni mozne pouzivat plast, ktery nepasuje ani do vidlice ani do ramu
ORA-06512: na "HANDLM1.POLOMERYPLASTU", line 18
ORA-04088: chyba během provádění triggeru 'HANDLM1.POLOMERYPLASTU'

ROLLBACK

ROLLBACK succeeded.
-- jezdec m� r�m pro 24" a vidlici pro 26", zkus�m mu p�idat pl᚝ 24"

UPDATE ram SET polomer_kola=24
  WHERE ko_vyrobce='RaceBike' AND ko_model='Bandit DS' AND ko_rok_vyroby=2002

1 rows updated
EXECUTE pridejPlast('Maxxis', 'Ignitor', '2008', 24, 2.5);

anonymous block completed
INSERT INTO pouziva (role11_id_jezdce, role23_vyrobce, role23_model, role23_rok_vyroby)
  VALUES (1, 'Maxxis', 'Ignitor', '2008')

1 rows inserted
ROLLBACK

ROLLBACK succeeded.
/* ***************************************************************************
 * MODUL 3
 * *************************************************************************** */

create or replace
TRIGGER
  zavodJenVOblibene
  BEFORE INSERT OR UPDATE ON se_ucastnil
  FOR EACH ROW
  DECLARE
    v_existuje INTEGER := 0;
  BEGIN
    SELECT COUNT(*) INTO v_existuje FROM ma_rad WHERE role3_id_jezdce=:NEW.role10_id_jezdce AND role6_nazev=:NEW.role9_di_nazev;
    IF v_existuje=0 THEN
      RAISE_APPLICATION_ERROR(-20200, 'Hr�� se m�e ��astnit pouze z�vodu v discipl�n�, kterou m� r�d!');
    END IF;
  END zavodJenVOblibene;

TRIGGER  Compiled.
-- jezdec s id=1 ma rad jen 4X a BMX, zkusime mu pridat zavod v DH

INSERT INTO se_ucastnil (role9_di_nazev, role9_lo_nazev, role9_datum, role10_id_jezdce)
  VALUES ('DH', 'DH ?pindl', '09-11-2005', 1)


Error starting at line 1 192 in command:
INSERT INTO se_ucastnil (role9_di_nazev, role9_lo_nazev, role9_datum, role10_id_jezdce)
  VALUES ('DH', 'DH ?pindl', '09-11-2005', 1)
Error report:
SQL Error: ORA-20200: Hráč se může účastnit pouze závodu v disciplíně, kterou má rád!
ORA-06512: na "HANDLM1.ZAVODJENVOBLIBENE", line 6
ORA-04088: chyba během provádění triggeru 'HANDLM1.ZAVODJENVOBLIBENE'

-- jezdec s id=3 ma rad 4X, zkusime mu pridat zavod ve 4X

INSERT INTO se_ucastnil (role9_di_nazev, role9_lo_nazev, role9_datum, role10_id_jezdce)
  VALUES ('4X', '4X Rokytnice', '16-07-2005', 3)

1 rows inserted
ROLLBACK

ROLLBACK succeeded.
/* ***************************************************************************
 * MODUL 4
 * *************************************************************************** */

create or replace
TRIGGER
  rokNakupu
  BEFORE INSERT OR UPDATE ON nakup
  FOR EACH ROW
  BEGIN
    -- rok vyroby je soucast klice komponentu, takze je mozne to kontrolovat primo bez dotazovani
    IF EXTRACT(YEAR FROM :NEW.datum) < :NEW.ko_rok_vyroby THEN
      RAISE_APPLICATION_ERROR(-20201, 'Nelze koupit komponent v d��v�j��m roce ne� je rok jeho v�roby!');
    END IF;    
  END rokNakupu;

TRIGGER  Compiled.
-- zkus?m p?idat z?znam o koupi komponentu z roku 2002 proveden? v roce 2001

INSERT INTO nakup (cena, datum, ob_nazev, je_id_jezdce, ko_model, ko_vyrobce, ko_rok_vyroby)
  VALUES (1, '01-01-01', 'Dookie', '5', 'Bandit DS', 'RaceBike', 2002)


Error starting at line 1 217 in command:
INSERT INTO nakup (cena, datum, ob_nazev, je_id_jezdce, ko_model, ko_vyrobce, ko_rok_vyroby)
  VALUES (1, '01-01-01', 'Dookie', '5', 'Bandit DS', 'RaceBike', 2002)
Error report:
SQL Error: ORA-20201: Nelze koupit komponent v dřívějším roce než je rok jeho výroby!
ORA-06512: na "HANDLM1.ROKNAKUPU", line 4
ORA-04088: chyba během provádění triggeru 'HANDLM1.ROKNAKUPU'

-- p?id?m z?znam o koupi komponentu z roku 2002 proveden? v roce 2002

INSERT INTO nakup (cena, datum, ob_nazev, je_id_jezdce, ko_model, ko_vyrobce, ko_rok_vyroby)
  VALUES (1, '01-01-02', 'Dookie', '5', 'Bandit DS', 'RaceBike', 2002)

1 rows inserted
ROLLBACK

ROLLBACK succeeded.
/* ***************************************************************************
 * MODUL 5
 * *************************************************************************** */

create or replace
trigger
  emailFormat
  BEFORE INSERT OR UPDATE ON jezdec
  FOR EACH ROW
  DECLARE
    v_regexp CONSTANT VARCHAR(100) := '^[A-Z0-9._%+-]+@[A-Z0-9.-]+[.][A-Z]{2,4}$';
    v_valid BOOLEAN := FALSE;
  BEGIN
    :NEW.e_mail := TRIM(:NEW.e_mail); -- odstran mezery pred a za emailem
    v_valid := REGEXP_LIKE(:NEW.e_mail, v_regexp, 'i');
    IF (NOT v_valid) THEN
      RAISE_APPLICATION_ERROR(-20207, 'Neplatny format emailove adresy!');
    END IF;
  END emailFormat;

trigger  Compiled.
INSERT INTO jezdec (id_jezdce, jmeno, prijmeni, prezdivka, e_mail)
  VALUES (11, 'Jezdec', 'SeSpatnym', 'Emailem', '.handlm1@fel.cvut.czasd')


Error starting at line 1 245 in command:
INSERT INTO jezdec (id_jezdce, jmeno, prijmeni, prezdivka, e_mail)
  VALUES (11, 'Jezdec', 'SeSpatnym', 'Emailem', '.handlm1@fel.cvut.czasd')
Error report:
SQL Error: ORA-20207: Neplatny format emailove adresy!
ORA-06512: na "HANDLM1.EMAILFORMAT", line 8
ORA-04088: chyba během provádění triggeru 'HANDLM1.EMAILFORMAT'

INSERT INTO jezdec (id_jezdce, jmeno, prijmeni, prezdivka, e_mail)
  VALUES (11, 'Jezdec', 'SeSpatnym', 'Emailem', 'handlm1@fel.cvut.cz')

1 rows inserted
ROLLBACK

ROLLBACK succeeded.
/* ***************************************************************************
 * MODUL 6
 * *************************************************************************** */

create or replace
TRIGGER
  hodnotyHodnoceni
  BEFORE INSERT OR UPDATE ON hodnoceni
  FOR EACH ROW
  BEGIN
    IF (:NEW.sortiment NOT BETWEEN 1 AND 10) THEN
      RAISE_APPLICATION_ERROR(-20204, 'Sortiment musi mit hodnotu z romezi 1 az 10');
    END IF;
    IF (:NEW.ceny NOT BETWEEN 1 AND 10) THEN
      RAISE_APPLICATION_ERROR(-20205, 'Ceny musi mit hodnotu z romezi 1 az 10');
    END IF;
    IF (:NEW.privetivost NOT BETWEEN 1 AND 10) THEN
      RAISE_APPLICATION_ERROR(-20206, 'Privetivost musi mit hodnotu z romezi 1 az 10');
    END IF;
  END hodnotyHodnoceni;

TRIGGER  Compiled.
create or replace TRIGGER
  hodnotyZkusenosti
  BEFORE INSERT OR UPDATE ON zkusenost
  FOR EACH ROW
  BEGIN
    IF (:NEW.cena_vykon NOT BETWEEN 1 AND 10) THEN
      RAISE_APPLICATION_ERROR(-20202, 'Cena_vykon musi byt z rozmezi 1 az 10');
    END IF;
    IF (:NEW.hodnoceni NOT BETWEEN 1 AND 10) THEN
      RAISE_APPLICATION_ERROR(-20203, 'Hodnoceni musi byt v rozmezi 1 az 10');
    END IF;
  END hodnotyZkusenosti;

TRIGGER  Compiled.
INSERT INTO zkusenost (ko_model, ko_vyrobce, ko_rok_vyroby, je_id_jezdce, cena_vykon, hodnoceni, slovni_hodnoceni)
  VALUES ('Bandit DS', 'RaceBike', 2002, 5, 0, 5, '')


Error starting at line 1 287 in command:
INSERT INTO zkusenost (ko_model, ko_vyrobce, ko_rok_vyroby, je_id_jezdce, cena_vykon, hodnoceni, slovni_hodnoceni)
  VALUES ('Bandit DS', 'RaceBike', 2002, 5, 0, 5, '')
Error report:
SQL Error: ORA-20202: Cena_vykon musi byt z rozmezi 1 az 10
ORA-06512: na "HANDLM1.HODNOTYZKUSENOSTI", line 3
ORA-04088: chyba během provádění triggeru 'HANDLM1.HODNOTYZKUSENOSTI'

INSERT INTO zkusenost (ko_model, ko_vyrobce, ko_rok_vyroby, je_id_jezdce, cena_vykon, hodnoceni, slovni_hodnoceni)
  VALUES ('Bandit DS', 'RaceBike', 2002, 5, 5, 0, '')


Error starting at line 1 289 in command:
INSERT INTO zkusenost (ko_model, ko_vyrobce, ko_rok_vyroby, je_id_jezdce, cena_vykon, hodnoceni, slovni_hodnoceni)
  VALUES ('Bandit DS', 'RaceBike', 2002, 5, 5, 0, '')
Error report:
SQL Error: ORA-20203: Hodnoceni musi byt v rozmezi 1 az 10
ORA-06512: na "HANDLM1.HODNOTYZKUSENOSTI", line 6
ORA-04088: chyba během provádění triggeru 'HANDLM1.HODNOTYZKUSENOSTI'

INSERT INTO zkusenost (ko_model, ko_vyrobce, ko_rok_vyroby, je_id_jezdce, cena_vykon, hodnoceni, slovni_hodnoceni)
  VALUES ('Bandit DS', 'RaceBike', 2002, 5, 11, 5, '')


Error starting at line 1 291 in command:
INSERT INTO zkusenost (ko_model, ko_vyrobce, ko_rok_vyroby, je_id_jezdce, cena_vykon, hodnoceni, slovni_hodnoceni)
  VALUES ('Bandit DS', 'RaceBike', 2002, 5, 11, 5, '')
Error report:
SQL Error: ORA-20202: Cena_vykon musi byt z rozmezi 1 az 10
ORA-06512: na "HANDLM1.HODNOTYZKUSENOSTI", line 3
ORA-04088: chyba během provádění triggeru 'HANDLM1.HODNOTYZKUSENOSTI'

INSERT INTO zkusenost (ko_model, ko_vyrobce, ko_rok_vyroby, je_id_jezdce, cena_vykon, hodnoceni, slovni_hodnoceni)
  VALUES ('Bandit DS', 'RaceBike', 2002, 5, 5, 11, '')


Error starting at line 1 293 in command:
INSERT INTO zkusenost (ko_model, ko_vyrobce, ko_rok_vyroby, je_id_jezdce, cena_vykon, hodnoceni, slovni_hodnoceni)
  VALUES ('Bandit DS', 'RaceBike', 2002, 5, 5, 11, '')
Error report:
SQL Error: ORA-20203: Hodnoceni musi byt v rozmezi 1 az 10
ORA-06512: na "HANDLM1.HODNOTYZKUSENOSTI", line 6
ORA-04088: chyba během provádění triggeru 'HANDLM1.HODNOTYZKUSENOSTI'

INSERT INTO zkusenost (ko_model, ko_vyrobce, ko_rok_vyroby, je_id_jezdce, cena_vykon, hodnoceni, slovni_hodnoceni)
  VALUES ('Bandit DS', 'RaceBike', 2002, 5, 10, 5, '')

1 rows inserted
INSERT INTO zkusenost (ko_model, ko_vyrobce, ko_rok_vyroby, je_id_jezdce, cena_vykon, hodnoceni, slovni_hodnoceni)
  VALUES ('Bandit DS', 'RaceBike', 2002, 7, 5, 10, '')

1 rows inserted
ROLLBACK

ROLLBACK succeeded.
INSERT INTO hodnoceni (ob_nazev, je_id_jezdce, ceny, sortiment, privetivost, slovni_hodnoceni)
  VALUES ('Dookie', 5, 5, 0, 5, '')


Error starting at line 1 301 in command:
INSERT INTO hodnoceni (ob_nazev, je_id_jezdce, ceny, sortiment, privetivost, slovni_hodnoceni)
  VALUES ('Dookie', 5, 5, 0, 5, '')
Error report:
SQL Error: ORA-20204: Sortiment musi mit hodnotu z romezi 1 az 10
ORA-06512: na "HANDLM1.HODNOTYHODNOCENI", line 3
ORA-04088: chyba během provádění triggeru 'HANDLM1.HODNOTYHODNOCENI'

INSERT INTO hodnoceni (ob_nazev, je_id_jezdce, ceny, sortiment, privetivost, slovni_hodnoceni)
  VALUES ('Dookie', 5, 0, 5, 5, '')


Error starting at line 1 303 in command:
INSERT INTO hodnoceni (ob_nazev, je_id_jezdce, ceny, sortiment, privetivost, slovni_hodnoceni)
  VALUES ('Dookie', 5, 0, 5, 5, '')
Error report:
SQL Error: ORA-20205: Ceny musi mit hodnotu z romezi 1 az 10
ORA-06512: na "HANDLM1.HODNOTYHODNOCENI", line 6
ORA-04088: chyba během provádění triggeru 'HANDLM1.HODNOTYHODNOCENI'

INSERT INTO hodnoceni (ob_nazev, je_id_jezdce, ceny, sortiment, privetivost, slovni_hodnoceni)
  VALUES ('Dookie', 5, 5, 5, 0, '')


Error starting at line 1 305 in command:
INSERT INTO hodnoceni (ob_nazev, je_id_jezdce, ceny, sortiment, privetivost, slovni_hodnoceni)
  VALUES ('Dookie', 5, 5, 5, 0, '')
Error report:
SQL Error: ORA-20206: Privetivost musi mit hodnotu z romezi 1 az 10
ORA-06512: na "HANDLM1.HODNOTYHODNOCENI", line 9
ORA-04088: chyba během provádění triggeru 'HANDLM1.HODNOTYHODNOCENI'

INSERT INTO hodnoceni (ob_nazev, je_id_jezdce, ceny, sortiment, privetivost, slovni_hodnoceni)
  VALUES ('Dookie', 5, 5, 11, 5, '')


Error starting at line 1 307 in command:
INSERT INTO hodnoceni (ob_nazev, je_id_jezdce, ceny, sortiment, privetivost, slovni_hodnoceni)
  VALUES ('Dookie', 5, 5, 11, 5, '')
Error report:
SQL Error: ORA-20204: Sortiment musi mit hodnotu z romezi 1 az 10
ORA-06512: na "HANDLM1.HODNOTYHODNOCENI", line 3
ORA-04088: chyba během provádění triggeru 'HANDLM1.HODNOTYHODNOCENI'

INSERT INTO hodnoceni (ob_nazev, je_id_jezdce, ceny, sortiment, privetivost, slovni_hodnoceni)
  VALUES ('Dookie', 5, 11, 5, 5, '')


Error starting at line 1 309 in command:
INSERT INTO hodnoceni (ob_nazev, je_id_jezdce, ceny, sortiment, privetivost, slovni_hodnoceni)
  VALUES ('Dookie', 5, 11, 5, 5, '')
Error report:
SQL Error: ORA-20205: Ceny musi mit hodnotu z romezi 1 az 10
ORA-06512: na "HANDLM1.HODNOTYHODNOCENI", line 6
ORA-04088: chyba během provádění triggeru 'HANDLM1.HODNOTYHODNOCENI'

INSERT INTO hodnoceni (ob_nazev, je_id_jezdce, ceny, sortiment, privetivost, slovni_hodnoceni)
  VALUES ('Dookie', 5, 5, 5, 11, '')


Error starting at line 1 311 in command:
INSERT INTO hodnoceni (ob_nazev, je_id_jezdce, ceny, sortiment, privetivost, slovni_hodnoceni)
  VALUES ('Dookie', 5, 5, 5, 11, '')
Error report:
SQL Error: ORA-20206: Privetivost musi mit hodnotu z romezi 1 az 10
ORA-06512: na "HANDLM1.HODNOTYHODNOCENI", line 9
ORA-04088: chyba během provádění triggeru 'HANDLM1.HODNOTYHODNOCENI'

INSERT INTO hodnoceni (ob_nazev, je_id_jezdce, ceny, sortiment, privetivost, slovni_hodnoceni)
  VALUES ('Dookie', 5, 5, 10, 5, '')

1 rows inserted
INSERT INTO hodnoceni (ob_nazev, je_id_jezdce, ceny, sortiment, privetivost, slovni_hodnoceni)
  VALUES ('Dookie', 7, 10, 5, 5, '')

1 rows inserted
INSERT INTO hodnoceni (ob_nazev, je_id_jezdce, ceny, sortiment, privetivost, slovni_hodnoceni)
  VALUES ('Dookie', 8, 5, 5, 10, '')

1 rows inserted
ROLLBACK

ROLLBACK succeeded.
/* ***************************************************************************
 * KONEC DAVKY
 * *************************************************************************** */

