Quartz 4

test 11.11 varianta A

7 min read

  1. Varianta A – Stručné Odpovědi

    Integrita Databáze: Správnost a konzistence dat, zajištěná pravidly.
    EIO: Pravidlo pro relační model. Primární klíč (PK) nesmí být nikdy NULL.
    Příklad: V tabulce Zaměstnanec musí mít sloupec ID_zaměstnance vždy hodnotu.

    SQL
    SELECT LS.nazev, LS.zeme, COUNT(L.id)
    FROM Letadlo L
    JOIN Linka LK ON L.id = LK.id_letadlo
    JOIN Letecká_společnost LS ON LK.id_společnost = LS.id
    WHERE L.výrobce = ‘Airbus’
    GROUP BY LS.nazev, LS.zeme;

    Definice: Systém pro správu dat (SŘBD) a samotná data. Zajišťuje integritu a bezpečnost.
    Složení: Data, SŘBD (DBMS) (software pro správu), Hardware, Uživatelé/Aplikace.
    Současné RDBMS: MySQL, PostgreSQL, MS SQL Server, Oracle.

    SQL: Standardní jazyk pro správu relačních databází (dotazování, definice, manipulace).
    DDL (Data Definition Language): Definice struktury (tabulky, schémata). Příkazy: CREATE TABLE, DROP TABLE.
    DML (Data Manipulation Language): Manipulace s daty (řádky). Příkazy: SELECT, INSERT INTO, UPDATE, DELETE FROM.

    Relační Schéma (3NF):
    Název Tabulky Atributy (PK, FK)
    ZAKAZNIK id_zakaznik (PK), jmeno, prijmeni
    ZBOZI id_zbozi (PK), nazev_zbozi, druh
    OBJEDNAVKA id_objednavka (PK), datum, id_zakaznik (FK)
    POLOZKA_OBJEDNAVKY id_objednavka (PK, FK), id_zbozi (PK, FK), mnozstvi
    SQL Selecty:
    a) Celkový příjem Září 2020
    SQL
    SELECT SUM(celkova_cena) FROM OBJEDNAVKA
    WHERE datum_objednavky BETWEEN ‘2020-09-01’ AND ‘2020-09-30’;
    b) Příjem a počet objednávek zákazníků
    SQL
    SELECT Z.jmeno, COUNT(O.id_objednavka), SUM(O.celkova_cena)
    FROM ZAKAZNIK Z JOIN OBJEDNAVKA O ON Z.id_zakaznik = O.id_zakaznik
    GROUP BY Z.jmeno;
    c) Zboží, které NENÍ v objednávkách
    SQL
    SELECT Z.druh, Z.nazev_zbozi
    FROM ZBOZI Z LEFT JOIN POLOZKA_OBJEDNAVKY PO ON Z.id_zbozi = PO.id_zbozi
    WHERE PO.id_zbozi IS NULL ORDER BY Z.druh;Add ReactionReplyForwardMore

    1. Integrita databáze & Entitní Integritní Omezení (EIO),

    2. SQL SELECT (Letadla Airbus),

    3. DBS (Databázový systém),

    4. SQL, DDL a DML,

    5. Logický/Relační Model & SQL (Obchod),

  2. Normalizace a 3.NF (Zaměstnanci/Hotely),

Důvody Normalizace: Snížení redundance, odstranění anomálií, zlepšení integrity dat.
3.NF: Je v 2.NF a žádný neklíčový atribut netranzitivně nezávisí na PK.
Relační Schéma (3NF):
Název Tabulky Atributy (PK, FK)
HOTEL id_hotel (PK), hotel_místo
ZAMESTNANEC id_zamestnanec (PK), jmeno_zam, dat_nar, id_hotel (FK)
SMLOUVA č_pojištění (PK), smlouva, hod_týdně, id_zamestnanec (FK)

1. Integrita databáze a entitní integritní omezení 🧱

Integrita databáze (Database Integrity) znamená správnost a konzistenci dat uložených v databázi. Zajišťuje, že data splňují definovaná pravidla a omezení, a zůstávají tak spolehlivá.

Entitní integritní omezení (Entity Integrity Constraint) je základní pravidlo, které se týká primárního klíče (Primary Key) v každé tabulce:

  • Primární klíč nesmí obsahovat NULL hodnoty (musí být vždy vyplněn).

  • Hodnoty primárního klíče musí být jedinečné (unikátní) pro každý řádek (entitu) tabulky.

Příklad:

V tabulce Zaměstnanci s primárním klíčem id_zamestnance:

  • Každý zaměstnanec musí mít unikátní id_zamestnance.

  • Pole id_zamestnance nesmí být nikdy prázdné (NULL).

2. SQL dotazy (Tabulky: LeteckaSpolecnost, Letadlo, Linka) ✈️

Cílem je vypsat letecké společnosti, zemi a celkový počet letadel, která mají na svých linkách Douglas (z tabulky Letadlo by bylo třeba vyfiltrovat letadla, kde vyrobce je ‘Douglas’, nebo předpokládat, že ‘Douglas’ je v názvu modelu, jak to často bývá). Předpokládáme, že Douglas je v poli **vyrobce**.

SQL příkaz (s využitím **JOIN**, **WHERE**, **GROUP BY** a agregační funkce **SUM**):

SQL

SELECT LS.nazev, LS.zeme, SUM(L.pocet_letadel) AS Celkovy_Pocet_Douglas FROM LeteckaSpolecnost LS JOIN Linka LI ON LS.id = LI.spolecnost_id JOIN Letadlo LE ON LI.letadlo_id = LE.id WHERE LE.vyrobce = 'Douglas' -- Předpoklad: vyrobce je 'Douglas' GROUP BY LS.nazev, LS.zeme;

3. DBS (Databázový systém) 💾

DBS (Databázový systém) je systém složený z dat a software pro správu dat.

Skládá se z:

  1. Databáze (Database): Samotná strukturovaná data a jejich definice (schéma).

  2. SŘBD (Systém Řízení Báze Dat - DBMS): Software, který umožňuje uživatelům a aplikacím interakci s databází (ukládání, získávání, aktualizace dat).

  3. Aplikace/Uživatelé: Programy nebo lidé, kteří přistupují k datům.

Popis částí SŘBD a jejich činnosti:

  • Jádro (Kernel/Storage Manager): Stará se o fyzické ukládání dat, správu paměti a transakcí, indexování a integritu.

  • Procesor dotazů (Query Processor): Přijímá SQL dotazy, analyzuje je, optimalizuje a převádí na spustitelný plán pro jádro.

  • Slovník dat (Data Dictionary / Catalog): Metadata - uchovává informace o struktuře databáze (tabulky, sloupce, datové typy, klíče, uživatelská práva).

Příklady relačních databázových systémů (RDBMS):

  • MySQL, PostgreSQL, Oracle Database, Microsoft SQL Server.

4. Použití SQL, DDL, DML 📝

SQL (Structured Query Language) se používá k definováni, manipulaci a řízení přístupu k datům v relační databázi.

DDL (Data Definition Language) - Jazyk pro definici dat

Používá se k vytváření, úpravě a odstraňování databázových objektů (tabulky, indexy, pohledy atd.).

PříkazPopisSyntaxe
CREATEVytvoří nový objekt.CREATE TABLE nazev_tabulky (sloupec DATATYP, ...);
ALTERModifikuje existující objekt.ALTER TABLE nazev_tabulky ADD sloupec DATATYP;
DROPOdstraní objekt z databáze.DROP TABLE nazev_tabulky;

DML (Data Manipulation Language) - Jazyk pro manipulaci s daty

Používá se k vkládání, změně, mazání a získávání dat uvnitř tabulek.

PříkazPopisSyntaxe
SELECTZískání dat z databáze.SELECT sloupec1, ... FROM nazev_tabulky WHERE podminka;
INSERTVložení nových řádků.INSERT INTO nazev_tabulky (sloupce) VALUES (hodnoty);
UPDATEModifikace existujících dat.UPDATE nazev_tabulky SET sloupec = hodnota WHERE podminka;
DELETEOdstranění řádků.DELETE FROM nazev_tabulky WHERE podminka;

5. Logický a Relační model pro obchodní společnost 🛒

Navrhneme 3 základní tabulky pro obchodní společnost prodávající zboží:

Logický model a Relační schéma

TabulkaAtributy (Sloupce)Klíče/Omezení
Zakaznikid_zakaznika (PK), jmeno, prijmeni, adresaPK: id_zakaznika
Zboziid_zbozi (PK), druh, nazev_zbozi, cena, sklademPK: id_zbozi
Objednavkaid_objednavky (PK), datum, celkova_cena, id_zakaznika (FK), id_zbozi (FK), pocet_kusuPK: id_objednavky
FK: id_zakaznika (ref. Zakaznik)
FK: id_zbozi (ref. Zbozi)
Poznámka: Pro přesný relační model by tabulka Objednavka měla být rozdělená na Objednavka (záhlaví) a Polozka_Objednavky (položky), ale pro jednoduchost a splnění selectů použijeme zjednodušený model, kde 1 objednávka = 1 typ zboží.

SQL dotazy (SELECT)

a) Celkový příjem ze všech objednávek v měsíci září 2020

Předpokládáme, že příjem = součet celkova_cena a datum je v formátu YYYY-MM-DD.

SQL

SELECT SUM(celkova_cena) AS Celkovy_Prijem_Zari_2020 FROM Objednavka WHERE datum >= '2020-09-01' AND datum < '2020-10-01';

b) Příjmení a jméno zákazníků s počtem objednávek

SQL

SELECT Z.prijmeni, Z.jmeno, COUNT(O.id_objednavky) AS Pocet_Objednavek FROM Zakaznik Z JOIN Objednavka O ON Z.id_zakaznika = O.id_zakaznika GROUP BY Z.prijmeni, Z.jmeno ORDER BY Pocet_Objednavek DESC;

c) Druhy a název zboží, které nejsou v žádné objednávce, setříděné podle druhu a názvu zboží

Použijeme LEFT JOIN s tabulkou Objednavka a filtrujeme řádky, kde id_objednavky je NULL.

SQL

SELECT Z.druh, Z.nazev_zbozi FROM Zbozi Z LEFT JOIN Objednavka O ON Z.id_zbozi = O.id_zbozi WHERE O.id_objednavky IS NULL ORDER BY Z.druh, Z.nazev_zbozi;

6. Normalizace databáze (1. až 3. NF) 📐

Proč provádíme normalizaci?

Normalizace je proces organizace sloupců a tabulek v relační databázi s cílem:

  1. Minimalizovat redundanci dat (opakování dat), čímž se šetří místo a snižuje riziko nekonzistence.

  2. Eliminovat anomálie při vkládání, aktualizaci a mazání (např. při smazání posledního záznamu zaměstnance by se ztratila i informace o hotelu, kde pracoval).

  3. Zlepšit integritu dat a efektivitu databáze.

Cílem je dosáhnout minimálně 3. Normální Formy (3. NF).

Normalizace původní tabulky

Původní tabulka:

| č_pojištění | smlouva | hod_týdně | jméno_zam | dat_nar | id_hotelu | hotel_misto |

| :--- | :--- | :--- | :--- | :--- | :--- | :--- |

| 113WD | C1024 | 16 | Jan Novák | 1. 3. 1985 | H25 | Edinburgh |

| 234XA | C1024 | 24 | Dana Horáková | 9. 9. 1990 | H25 | Edinburgh |

| 712YD | C1025 | 28 | Karel Veselý | 2. 8. 1989 | H4 | Glasgow |

| 113WD | C1025 | 12 | Jan Novák | 1. 3. 1985 | H4 | Glasgow |

1. Normální Forma (1. NF)

Tabulka je již v 1. NF, protože neobsahuje opakující se skupiny atributů (např. více hodnot v jedné buňce) a každý atribut obsahuje atomické (nedělitelné) hodnoty.

2. Normální Forma (2. NF)

Tabulka má složený primární klíč: (č_pojištění, smlouva).

  • Závislost na části klíče?

    • jméno_zam, dat_nar závisí pouze na č_pojištění (zaměstnanec).

    • id_hotelu, hotel_misto závisí pouze na smlouva (smlouva = práce pro hotel).

    • hod_týdně závisí na celém klíči (č_pojištění + smlouva).

Rozdělení kvůli porušení 2. NF:

  • Tabulka 1: Zamestnanec

    • č_pojištění (PK)

    • jméno_zam

    • dat_nar

  • Tabulka 2: Hotelova_Smlouva

    • smlouva (PK)

    • id_hotelu (FK - odkazuje na nový Hotel)

    • hotel_misto (Redundance/Přechodná závislost, odstraníme v 3. NF)

  • Tabulka 3: Pracovni_Nasazeni (Vazební tabulka)

    • č_pojištění (PK, FK)

    • smlouva (PK, FK)

    • hod_týdně

3. Normální Forma (3. NF)

V 2. NF tabulce Hotelova_Smlouva máme přechodnou závislost (Transitive Dependency):

  • smlouva id_hotelu

  • id_hotelu hotel_misto (Hotel má vždy jen jedno místo)

Hotelové informace musí být odděleny do vlastní tabulky:

Nové Relační Schéma (ve 3. NF)

TabulkaKlíčAtributyOmezení
ZamestnanecPKč_pojištěníč_pojištění (PK)
jméno_zam
dat_nar
HotelPKid_hoteluid_hotelu (PK)
hotel_misto
SmlouvaPKsmlouvasmlouva (PK)
id_hoteluid_hotelu (FK) Hotel
Pracovni_NasazeniPKč_pojištěníč_pojištění (PK, FK) Zamestnanec
smlouvasmlouva (PK, FK) Smlouva
hod_týdně

Graph View