drop database Northwind;
create database Northwind;

use Northwind;

CREATE TABLE Zakaznici (KodZakaznika CHAR(5) PRIMARY KEY,Firma CHAR(40) NOT NULL,INDEX(Firma),KontaktniOsoba CHAR(30),Funkce CHAR(30),Adresa CHAR(60),Mesto CHAR(15),INDEX(Mesto),Region CHAR(15),INDEX(Region),PSC CHAR(10),INDEX(PSC),Zeme CHAR(15),Telefon CHAR(24),Fax CHAR(24));

CREATE TABLE Zamestnanci (CisloZamestnance INT UNSIGNED PRIMARY KEY, Prijmeni CHAR(20) NOT NULL,INDEX(Prijmeni),Jmeno CHAR(10) NOT NULL,Funkce CHAR(30),FormalniOsloveni CHAR(25),DatumNarozeni DATE, ZamestnanOd DATE, Adresa CHAR(60),Mesto CHAR(15),Region CHAR(15),PSC CHAR(10),INDEX(PSC),Zeme CHAR(15),TelefonDomu CHAR(24),Linka CHAR(4),Poznamky TEXT,Nadrizeny INT UNSIGNED);

CREATE TABLE Kategorie (CisloKategorie INT UNSIGNED PRIMARY KEY,NazevKategorie CHAR(15),INDEX(NazevKategorie),Popis TEXT);

CREATE TABLE Dodavatele (CisloDodavatele INT UNSIGNED PRIMARY KEY,Firma CHAR(40) NOT NULL,INDEX (Firma),KontaktniOsoba CHAR(30),Funkce CHAR(30),Adresa CHAR(60),Mesto CHAR(15),Region CHAR(15),PSC CHAR(10),INDEX (PSC),Zeme CHAR(15),Telefon CHAR(24),Fax CHAR(24));

CREATE TABLE Vyrobky (CisloVyrobku INT UNSIGNED PRIMARY KEY,NazevVyrobku CHAR(40) NOT NULL,INDEX(NazevVyrobku),CisloDodavatele INT REFERENCES Dodavatele,INDEX(CisloDodavatele),CisloKategorie INT UNSIGNED REFERENCES Kategorie,INDEX(CisloKategorie),MnozstviVJednotce CHAR(20),JednotkovaCena DEC(17,2) UNSIGNED,JednotkyNaSklade SMALLINT UNSIGNED ,ObjednanoJednotek SMALLINT UNSIGNED,MinimalniUroven SMALLINT UNSIGNED,NakupUkoncen BIT DEFAULT 0);

CREATE TABLE Prepravci (CisloPrepravce INT UNSIGNED PRIMARY KEY, Firma CHAR(40)NOT NULL,Telefon CHAR(24));

CREATE TABLE Objednavky (CisloObjednavky INT UNSIGNED PRIMARY KEY,KodZakaznika CHAR(5) REFERENCES Zakaznici ON DELETE SET NULL ON UPDATE CASCADE,INDEX(KodZakaznika),CisloZamestnance INT REFERENCES Zamestnanci,INDEX(CisloZamestnance),DatumObjednavky DATE,INDEX(DatumObjednavky),DodatDne DATE,DatumOdeslani DATE,INDEX(DatumOdeslani),Preprava INT REFERENCES Prepravci,Dopravne DEC(17,2),JmenoPrijemce CHAR(40),AdresaPrijemce CHAR(60),MestoPrijemce CHAR(15),RegionPrijemce CHAR(15),PSCPrijemce CHAR(10),INDEX(PSCPrijemce),ZemePrijemce CHAR(15));

CREATE TABLE RozpisObjednavek (CisloObjednavky INT UNSIGNED NOT NULL REFERENCES Objednavky ON DELETE CASCADE,CisloVyrobku INT UNSIGNED NOT NULL REFERENCES Vyrobky,JednotkovaCena DEC(17,2) UNSIGNED,Mnozstvi INT UNSIGNED,Sleva FLOAT UNSIGNED,PRIMARY KEY (CisloObjednavky,CisloVyrobku));


load data infile "disk:/cesta/Zam.txt" into table Zamestnanci FIELDS OPTIONALLY ENCLOSED BY  '"';
load data infile "disk:/cesta/Zak.txt" into table Zakaznici FIELDS OPTIONALLY ENCLOSED BY  '"';
load data infile "disk:/cesta/Kat.txt" into table Kategorie FIELDS OPTIONALLY ENCLOSED BY  '"';
load data infile "disk:/cesta/Dod.txt" into table Dodavatele FIELDS OPTIONALLY ENCLOSED BY  '"';
load data infile "disk:/cesta/Pre.txt" into table Prepravci FIELDS OPTIONALLY ENCLOSED BY  '"';
load data infile "disk:/cesta/Vyr.txt" into table Vyrobky FIELDS OPTIONALLY ENCLOSED BY  '"';
load data infile "disk:/cesta/Obj.txt" into table Objednavky FIELDS OPTIONALLY ENCLOSED BY  '"';
load data infile "disk:/cesta/Roz.txt" into table RozpisObjednavek FIELDS OPTIONALLY ENCLOSED BY  '"';

ALTER TABLE RozpisObjednavek ADD COLUMN VyslednaCena DEC(17,2) UNSIGNED;
UPDATE RozpisObjednavek SET VyslednaCena=JednotkovaCena*Mnozstvi*(1-Sleva);

CREATE TABLE Pomoc SELECT O.*, sum(VyslednaCena) AS Mezisoucet FROM Objednavky O, RozpisObjednavek R WHERE O.CisloObjednavky=R.CisloObjednavky GROUP BY R.CisloObjednavky;

DROP TABLE Objednavky;

ALTER TABLE Pomoc RENAME Objednavky;