-- Skript pro vytvoreni tabulek internetoveho obchodu
------------------------------------------------------
------------------------------------------------------

Drop table Pisen cascade constraints;
Drop table Zamestnanec cascade constraints;
Drop table Album cascade constraints;
Drop table Interpret cascade constraints;
Drop table Zakaznik cascade constraints;
Drop table Objednavka cascade constraints;
Drop table Faktura cascade constraints;
Drop table CD cascade constraints;
Drop table na cascade constraints;
Drop table obsazena cascade constraints;
Drop table za cascade constraints;
Drop table Upominka cascade constraints;
Drop table NovaUpominka cascade constraints;
Drop view Inter;

Create table Pisen (
      Nazev_P VarChar2(20) Not Null,
      ID_I Integer Not Null,
      Pozn VarChar2(20) Null,
      Rok Integer Null,
      Constraint PK_Pisen Primary Key (Nazev_P, ID_I)
);
Create table Zamestnanec (
      ID_Zam Integer Not Null,
      Jmeno_Zam VarChar2(20) Not Null,
      Constraint PK_Zamestnanec Primary Key (ID_Zam)
);
Create table Album (
      Nazev_A VarChar2(30) Not Null,
      Rok Integer Not Null,
      Cena Float Not Null,
      Constraint PK_Album Primary Key (Nazev_A)
);
Create table Interpret (
      ID_I Integer Not Null,
      Jmeno_I VarChar2(20) Not Null,
      Pozn VarChar2(20) Null,
      Constraint PK_Interpret Primary Key (ID_I)
);
Create table Zakaznik (
      Jmeno_Zak VarChar2(20) Not Null,
      RC_Zak Integer Not Null,
      Adresa VarChar2(100) Not Null,
      Telefon Integer Null,
      Constraint PK_Zakaznik Primary Key (RC_Zak)
);
Create table Objednavka (
      ID_Obj Integer Not Null,
      Vyrizena Char(1) Not Null,
      Pocet Integer Not Null,
      RC_Zak Integer Not Null,
      Nazev_A VarChar2(30) Not Null,
      Constraint PK_Objednavka Primary Key (ID_Obj)
);
Create table Faktura (
      Cis_F Integer Not Null,
      ID_Zam Integer Not Null,
      dat_vystav Date Not Null,
      Zaplaceno Char(1) Not Null,
      dat_splatnosti Date Not Null,
      Constraint PK_Faktura Primary Key (Cis_F)
);
Create table CD (
      ID_CD Integer Not Null,
      Nazev_A VarChar2(30) Not Null,
      Constraint PK_CD Primary Key (ID_CD)
);
Create table na (
      Cis_F Integer Not Null,
      ID_CD Integer Not Null,
      Constraint UNQ_na_1 Unique (ID_CD)
);
Create table obsazena (
      Nazev_P VarChar2(20) Not Null,
      ID_I Integer Not Null,
      Nazev_A VarChar2(30) Not Null,
      Constraint UNQ_obsazena_1 Unique (Nazev_P,ID_I,Nazev_A)
);
Create table za (
      ID_Obj Integer Not Null,
      Cis_F Integer Not Null,
      Constraint UNQ_za_1 Unique (ID_Obj)
);

Create table Upominka(
       RC_ZAK Integer Not Null,
       Pocet Integer Not Null,
       Datum Date Not Null,
       Constraint PK_Upominka Primary Key (RC_ZAK)
);

Create table NovaUpominka(
       RC_ZAK Integer Not Null,
       Constraint PK_Nova_Upominka Primary Key (RC_ZAK)
);

create view Inter
as select * from Obsazena natural join Interpret;

Alter table Pisen add (
      Constraint FK_Pisen_1 Foreign Key (ID_I) References Interpret(ID_I)
);
Alter table Objednavka add (
      Constraint FK_Objednavka_1 Foreign Key (Nazev_A) References Album(Nazev_A),
      Constraint FK_Objednavka_2 Foreign Key (RC_Zak) References Zakaznik(RC_Zak)
);
Alter table Faktura add (
      Constraint FK_Faktura_1 Foreign Key (ID_Zam) References Zamestnanec(ID_Zam)
);
Alter table CD add (
      Constraint FK_CD_1 Foreign Key (Nazev_A) References Album(Nazev_A)
);
Alter table na add (
      Constraint FK_na_1 Foreign Key (Cis_F) References Faktura(Cis_F),
      Constraint FK_na_2 Foreign Key (ID_CD) References CD(ID_CD)
);
Alter table obsazena add (
      Constraint FK_obsazena_1 Foreign Key (Nazev_P, ID_I) References Pisen(Nazev_P, ID_I),
      Constraint FK_obsazena_2 Foreign Key (Nazev_A) References Album(Nazev_A)
);
Alter table za add (
      Constraint FK_za_1 Foreign Key (ID_Obj) References Objednavka(ID_Obj),
      Constraint FK_za_2 Foreign Key (Cis_F) References Faktura(Cis_F)
);
Alter table Upominka add (
      Constraint FK_Upominka_1 Foreign Key (RC_ZAK) References Zakaznik(RC_ZAK)
);

Alter table NovaUpominka add (
      Constraint FK_Nova_Upominka_1 Foreign Key (RC_ZAK) References Zakaznik(RC_ZAK)
);