Před touto konzultací by jste měli odevzdat návrh modulu DB. Na této konzultaci můžete vaše vypracované modely ještě zkonzultovat a popřípadě provést finální úpravy.

1. modelování dat (pro relační databáze) osnova3
   1. základy relační algebry
   2. normální formy
2. dotazování v relačních databázích osnova4
   1. jazyk SQL
   2. základní dotazy (CRUD - create, read, update delete)

ALTER USER jmeno WITH PASSWORD 'nove_heslo'

Vytvoření sequence pro tabulku sleva.

 
 CREATE SEQUENCE seq_sleva;
 

Vytvoření tabulky sleva. Tabulka bude mít sloupce id_sleva, nazev a sleva. Primárním klíčem bude sloupec id_sleva. Tabulka má také několik dalších vlastností. Sloupec id_sleva nesmí být nulový a hodnoty v tomto sloupci musí být unikátní v rámci této tabulky (víte proč tomu tak musí být?). Hodnoty v tomto sloupci se také budou generovat ze sekvence, kterou jste vytvořili v předchozím kroku. Sloupec naze bude nenulový a unikátní napříč tabulkou. Sloupec sleva bude nenulový a jeho hodnota musí být mezi 0 a 100.

 CREATE TABLE sleva (
      id_sleva        int             NOT NULL UNIQUE DEFAULT nextval('seq_sleva'),
      nazev           varchar(64)     NOT NULL UNIQUE,
      sleva           numeric(2)      NOT NULL CHECK (sleva BETWEEN 0 AND 100),
      PRIMARY KEY (id_sleva));
      

Nyní je potřeba nagrantovat práva uživateli na tabulku a sequenci.

GRANT USAGE ON SEQUENCE seq_sleva TO db16_<vase_username>;
GRANT ALL PRIVILEGES ON TABLE sleva TO db16_<vase_username>;

Vytvoření sequence pro tabulku sleva.

 CREATE SEQUENCE seq_zbozi;
 

Vytvoření tabulky zbozi. Sloupec id_zbozi je nenulový, unikátní a jeho hodnoty se generují na základě sekvence. Sloupec nazev je nenulový a unikátní, sloupec popis je textový a může být nulový, sloupec id_sleva je cizí klíč z tabulky sleva. V případě smazání se nastaví na null. Sloupec cena je hodnota, která může být nulová a musí být větší jak 0.

 CREATE TABLE zbozi (
  id_zbozi            int             NOT NULL UNIQUE DEFAULT nextval('seq_zbozi'),
  nazev               varchar(128)    NOT NULL UNIQUE,
  popis               text,
  id_sleva            int             REFERENCES sleva ON UPDATE CASCADE ON DELETE SET NULL,
  cena                numeric(2)      CHECK (cena >0),
  PRIMARY KEY (id_zbozi));
  

Nyní je potřeba nagrantovat práva uživateli na tabulku a sequenci.

 GRANT USAGE ON SEQUENCE seq_zbozi TO db16_<vase_username>;
 GRANT ALL PRIVILEGES ON TABLE zbozi TO db16_<vase_username>;

CREATE SEQUENCE seq_faktura;
CREATE TABLE faktura (
  id_faktura          int             NOT NULL UNIQUE DEFAULT nextval('seq_faktura'),
  odberatel           varchar(128)    NOT NULL,
  prodavac            varchar(64)     NOT NULL DEFAULT (user),
  PRIMARY KEY (id_faktura));
GRANT USAGE ON SEQUENCE seq_faktura TO db16_<vase_username>;
GRANT ALL PRIVILEGES ON TABLE faktura TO db16_<vase_username>;

CREATE TABLE fakturazbozi (
  id_faktura          int             NOT NULL REFERENCES faktura ON UPDATE CASCADE ON DELETE CASCADE,
  id_zbozi            int             NOT NULL REFERENCES zbozi ON UPDATE CASCADE ON DELETE CASCADE,
  id_sleva            int             REFERENCES sleva ON UPDATE CASCADE ON DELETE SET NULL,
  cena                numeric         NOT NULL,
  sleva               numeric(2),
  ks                  numeric         NOT NULL CHECK(ks>0),
  PRIMARY KEY (id_faktura,id_zbozi));
GRANT ALL PRIVILEGES ON TABLE fakturazbozi TO db16_<vase_username>;

Vložení dat do tabulku sleva.

• První řádek vloží záznam, v kterém bude nazev = AKCE: dnes 20% a sleva = 20. id_sleva bude vyplněno na základě sekvence.
• Druhý řádek vloží záznam, v kterém bude nazev = TOTÁLNÍ VÝPRODEJ: 80% a ve sloupci sleva bude 80. id_sleva bude vyplněno na základě sekvence

INSERT INTO sleva(nazev,sleva) VALUES ('AKCE: dnes 20%',20);
INSERT INTO sleva(nazev,sleva) VALUES ('TOTALNÍ VÝPRODEJ: 80%',80);

Vložení dat do tabulky zbozi.

• První řádek vloží do tabulky nazev = ponožky modré s popis = Krásné modré ponožky, co musíte mítbez reference na slevu
• Druhý řádek vloží do tabulky naze = ponožky rudé s popis = Kdo by nechěl tyto krásně rudé ponožky a s referenci na slevu Akce: dnes 20%
• Třetí řádek skončí s chybou.

INSERT INTO zbozi(nazev,popis,id_sleva) VALUES ('ponožky modré','Krásné modré ponožky, co musíte mít',NULL);
INSERT INTO zbozi(nazev,popis,id_sleva) VALUES ('ponožky rudé','Kdo by nechěl tyto krásně rudé ponožky',1);
INSERT INTO zbozi(nazev,popis,id_sleva) VALUES ('ponožky děravé','druhá jakost, velká sleva',3);

INSERT INTO faktura (odberatel) VALUES ('Pepa Novák, Technická 8, Praha 6');
INSERT INTO faktura (odberatel) VALUES ('Pepina Nováková, Technická 18, Praha 6');
INSERT INTO faktura (odberatel) VALUES ('Pepina Nováková, Technická 18, Praha 6');

Na minulých konzultací se prezentovali základy dotazování v jazyku SQL. Nyní se podíváme na další příkazy. Zkratka v názvu cvičení CRUD vyjadřuje (create,read,update,delete). Doposud jsme se zabývali “čtením - read” dotazováním do databáze. Dnes se zaměříme na další práci s daty (INSERT, UPDATE, DELETE) i na práci s tabulkami (CREATE TABLE, ALTER, DROP).

CREATE TABLE jméno_tabulky (
  jméno_sloupce_1 datový_typ [DEFAULT výraz] [integritní omezení],
  ...
  jméno_sloupce_N datový_typ [DEFAULT výraz] [integritní omezení],
  další_integritní_omezení
)

CREATE TABLE kniha( \\
 kniha_id int, \\
 nazev varchar(100), \\
 autor varchar(100), \\
 pocet_stran int, \\
 vydani int );

Primární klíč:

CREATE TABLE kniha( \\
 kniha_id int PRIMARY KEY, \\
 nazev varchar(100), \\
 autor varchar(100), \\
 pocet_stran int, \\
 vydani int );

Přidání kontroly nevyplněné hodnoty (NOT NULL) a kontroly hodnoty (CHECK):

CREATE TABLE kniha( \\
 kniha_id int PRIMARY KEY, \\
 nazev varchar(100) NOT NULL, \\
 autor varchar(100), \\
 pocet_stran int CHECK (pocet_stran > 0), \\
 vydani int );

Kontrola více hodnot (možno kontrolovat i např. součty a rozdíly hodnot polí):

CREATE TABLE kniha( \\
 kniha_id int PRIMARY KEY, \\
 nazev varchar(100) NOT NULL, \\
 autor varchar(100), \\
 pocet_stran int, \\
 vydani int, \\
 CONSTRAINT con1 CHECK (pocet_stran > 0 AND vydani > 0) );

Unikátní hodnota:

CREATE TABLE kniha( \\
 kniha_id int PRIMARY KEY, \\
 nazev varchar(100) NOT NULL, \\
 autor varchar(100), \\
 pocet_stran int, \\
 vydani int, \\
 UNIQUE(nazev) );

Cizí klíče:

CREATE TABLE kniha( \\
 kniha_id int PRIMARY KEY, \\
 nazev varchar(100) NOT NULL );

CREATE TABLE vytisk( \\
  vytisk_id integer PRIMARY KEY, \\
  kniha_id integer, \\
  police integer, \\
  FOREIGN KEY (kniha_id) REFERENCES kniha (kniha_id) );

Další varianty a příklady lze najít v dokumentaci PostgreSQL.

ALTER TABLE jméno_tabulky (
   ADD COLUMN jméno_sloupce typ [integritní omezení],
   ALTER COLUMN jméno_sloupce typ [integritní omezení],
   DROP COLUMN jméno_sloupce,
   RENAME COLUMN jméno_sloupce TO nové_jméno_sloupce,
   RENAME TO nové_jméno_tabulky
)

ALTER TABLE kniha ADD COLUMN vydavatel varchar(50);\\
ALTER TABLE kniha DROP COLUMN vydavatel;\\
ALTER TABLE kniha RENAME COLUMN pocet_stran TO pages;\\
ALTER TABLE kniha RENAME TO books;\\
ALTER TABLE kniha ALTER COLUMN autor TYPE varchar(120);

Další varianty a příklady lze najít v dokumentaci PostgreSQL.

DROP TABLE jméno_tabulky

DROP TABLE kniha

Vkládání nových dat do tabulky

INSERT INTO jméno_tabulky (seznam sloupců) VALUES (hodnoty v pořadí uvedených sloupců) 

Seznam sloupců je nepovinný, pokud není seznam uveden, berou se všechny sloupce v pořadí, v jakém byly vytvořeny při vytváření tabulky (CREATE TABLE).

INSERT INTO kniha VALUES (1,'Myslime objektove v jazyku Java', 'Rudolf Pecinovsky', 576); \\
INSERT INTO kniha (kniha_id,nazev) VALUES (1,'Myslime objektove v jazyku Java');

Úprava dat v tabulce

UPDATE jméno_tabulky SET sloupec = nová_hodnota WHERE podmínka

UPDATE kniha SET vydani=2 where nazev='Myslime objektove v jazyku Java'

v rámci jednoho příkazu UPDATE lze upravovat i několik sloupců najednou
UPDATE kniha SET vydani=2,pocet_stran=580 where nazev='Myslime objektove v jazyku Java'

Mazání dat v tabulce

DELETE FROM jméno_tabulky WHERE podmínka

Smazání všech dat v tabulce

DELETE FROM kniha  

Smazání všech druhých vydání knih

DELETE FROM kniha WHERE vydani=2

Pokorný J., Valenta M.: Databázové systémy (kap. 4.1, 4.2)

Dokumentace k create table pro PostgreSQL

Dokumentace k insert pro PostgreSQL

Dokumentace k delete pro PostgreSQL

Dokumentace k update pro PostgreSQL

Dokumentace k select pro PostgreSQL

Dokumentace k pqadmin