Drop table Hodnoceni cascade constraints;
Drop table Zkusenost cascade constraints;
Drop table Jezdec cascade constraints;
Drop table Zavod cascade constraints;
Drop table Disciplina cascade constraints;
Drop table Lokace cascade constraints;
Drop table Obchod cascade constraints;
Drop table Komponent cascade constraints;
Drop table Vidlice cascade constraints;
Drop table Plast cascade constraints;
Drop table Ram cascade constraints;
Drop table Nakup cascade constraints;
Drop table pouziva cascade constraints;
Drop table ma_rad cascade constraints;
Drop table se_ucastnil cascade constraints;
COMMIT;

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)
);
COMMIT;

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

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

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)
);
COMMIT;

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

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

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

Create table Komponent (
      Model VarChar2(50) Not Null,
      Vyrobce VarChar2(50) Not Null,
      Rok_vyroby Char(4) Not Null,
      Constraint PK_Komponent Primary Key (Vyrobce, Model, Rok_vyroby)
);
COMMIT;

Create table Vidlice (
      Zdvih Float Not Null,
      Ko_Vyrobce VarChar2(50) Not Null,
      Ko_Model VarChar2(50) Not Null,
      Ko_Rok_vyroby Char(4) Not Null,
      Constraint PK_Vidlice Primary Key (Ko_Vyrobce, Ko_Model, Ko_Rok_vyroby)
);
COMMIT;

Create table Plast (
      Sirka Float Not Null,
      Ko_Vyrobce VarChar2(50) Not Null,
      Ko_Model VarChar2(50) Not Null,
      Ko_Rok_vyroby Char(4) Not Null,
      Constraint PK_Plast Primary Key (Ko_Vyrobce, Ko_Model, Ko_Rok_vyroby)
);
COMMIT;

Create table Ram (
      Velikost Float Not Null,
      Ko_Vyrobce VarChar2(50) Not Null,
      Ko_Model VarChar2(50) Not Null,
      Ko_Rok_vyroby Char(4) Not Null,
      Constraint PK_Ram Primary Key (Ko_Vyrobce, Ko_Model, Ko_Rok_vyroby)
);
COMMIT;

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

Create table pouziva (
      Role11_ID_jezdce Integer Not Null,
      Role23_Vyrobce VarChar2(50) Not Null,
      Role23_Model VarChar2(50) Not Null,
      Role23_Rok_vyroby Char(4) Not Null,
      Constraint UNQ_pouziva_1 Unique (Role11_ID_jezdce, Role23_Vyrobce, Role23_Model, Role23_Rok_vyroby)
);
COMMIT;

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)
);
COMMIT;

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)
);
COMMIT;

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 Zkusenost add (
      Constraint FK_Zkusenost_1 Foreign Key (Ko_Vyrobce, Ko_Model, Ko_Rok_vyroby) References Komponent(Vyrobce, Model, Rok_vyroby),
      Constraint FK_Zkusenost_2 Foreign Key (Je_ID_jezdce) References Jezdec(ID_jezdce)
);
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 Vidlice add (
      Constraint FK_Vidlice_1 Foreign Key (Ko_Vyrobce, Ko_Model, Ko_Rok_vyroby) References Komponent(Vyrobce, Model, Rok_vyroby)
);
Alter table Plast add (
      Constraint FK_Plast_1 Foreign Key (Ko_Vyrobce, Ko_Model, Ko_Rok_vyroby) References Komponent(Vyrobce, Model, Rok_vyroby)
);
Alter table Ram add (
      Constraint FK_Ram_1 Foreign Key (Ko_Vyrobce, Ko_Model, Ko_Rok_vyroby) References Komponent(Vyrobce, Model, Rok_vyroby)
);
Alter table Nakup add (
      Constraint FK_Nakup_1 Foreign Key (Je_ID_jezdce) References Jezdec(ID_jezdce),
      Constraint FK_Nakup_2 Foreign Key (Ko_Vyrobce, Ko_Model, Ko_Rok_vyroby) References Komponent(Vyrobce, Model, Rok_vyroby),
      Constraint FK_Nakup_3 Foreign Key (Ob_Nazev) References Obchod(Nazev)
);
Alter table pouziva add (
      Constraint FK_pouziva_1 Foreign Key (Role11_ID_jezdce) References Jezdec(ID_jezdce),
      Constraint FK_pouziva_2 Foreign Key (Role23_Vyrobce, Role23_Model, Role23_Rok_vyroby) References Komponent(Vyrobce, Model, Rok_vyroby)
);
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 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)
);
COMMIT;
