Teroristická organizace

Martin Řezník, kruh 108, poslední aktualizace: 3. 1.2007
Zde předkládanou práci jsem vytvořil vytvořím samostatně s využitím informačních zdrojů uvedených v závěru práce

Obsah

• Popis aplikace
• ER schéma
  • Obrázek
  • Smyčky ve schématu
• Uživatelské role
  • Kdo je kdo
• Scénáře
• SQL dotazy
• Changelog

Popis aplikace

DB bude popisovat prostředí teroristických buňek, jejich kontaktů, obětí a prodejců zbraní nebo drog. Prostředí bude poměrně komplexní, kromě již zmiňovaných položek bude obsahovat například i policejní jednotky a jejich členy infiltrované do jednotlivých teroristických buňek, zbraně různých typů a účinností.

Pomocí DB a správně formulovaných dotazů bude možné zjistit který terorista je zodpovědný za čí smrt, jakou zbraní byla smrt způsobena a který obchodník ji teroristovi prodal, které teroristické buňky patří které organizaci, příslušné teroristy i policisty infiltrované uvnitř.
Nahoru

ER schéma

Schéma jsem vytvářel v programu DBDesigner 4 od fabFORCE. DBDesigner 4 jsem se rozhodl upřednostnit z několika důvodů - je pro mne lehčí na ovládání (pracoval jsem s ním už před tím), je přehlednější a tvoří hezčí obrázky ;).
Použití ale může mít i nevýhody. Jako hlavní je neověřená kompatibilita xml exportů s ER modelářem.
Nahoru

obrázek

XML export ER schématu
XML export ER schématu, podle MDB XML
SQL příkazy pro vytvoření
SQL příkazy pro zrušení úložiště dat
SQL příkazy pro vložení základních dat
SQL dotazy
SQL skripty byly testovány na MySQL 5.0.18 - je možné, že na Oracle z nějakého důvodu fungovat nebudou. V tom případě mne prosím kontaktujte.

Možné důvody nekompatibility na Oracle

• U SQL pro vytvoření je nastavený typ pro úložiště dat (InnoDB)
• U SQL pro vložení dat je vypnuto hlídání odkazovaných dat - aby inserty nemusely být seřazeny podle Foreign keys, což funguje u MySQL, ale Oracle to může podporovat jinak, nebo vůbec. Integrita vkládaných dat je zachována, resp. předpokládá se to - data tak byla vytvořena - proto by vypnutí hlídání integrity nemělo vadit.

Nahoru

Smyčky ve schématu

cell - country - organization - cell

Smyčka vyjadřuje dva různé vztahy. Vztah country - organization mi říká pro který stát dotyčná organizace pracuje, zatímco vztah country - cell znázorňuje ve kterém státě konkrétní buňka dané organizace pracuje.

human - agent - cell - country - human

Opět jde o různé vztahy. Country - human upřesňuje ze které země daný člověk pochází, jaké je národnosti, zatímco vztah human - agent - cell - country mi říká ve které zemi člověk pracuje.

human - human_knows_human - human

V podstatě nejde ani o zacyklení - ale o vztah člověk zná člověka

human - human_has_weapon - weapon - trader - human - murders - weapon

Vztah human - human_has_weapon - weapon vyjadřuje který člověk kdy vlastnil jakou zbraň. Entita human_has_weapon je slabým entitním typem.
Vztah weapon - trader - human nám říká který člověk prodává kterou zbraň.
Vztah human - murders - weapon umožňuje dozvědět se, kdo byl zabit kterou zbraní a pomocí vztahu human - human_has_weapon - weapon můžeme zjistit kdo danou zbraň v kritický moment vlastnil.
Nahoru

Uživatelské role

1. Pánbů
   • Přidání libovolných údajů (včetně tvorby nových osob)
   • Editace libovolných údajů
   • Mazání libovolných údajů
   • Prohlížení všech zadaných údajů
2. Šéf organizace
   • Tvorba nových buněk organizace
   • Změny vlastností buněk organizace (změny hierarchie buňek, změna šéfa buňky, ...)
   • Přijímání nových členů do organizace
   • Rušení buněk organizace
3. Šéf buňky
   • Přijímání nových členů do buňky
   • Zrušení buňky
   • Zakládání nových buněk (pod "svou" buňkou)
4. Obchodník se zbraněmi
   • Prodávání zbraní lidem
   • Kupování zbraní od lidí
   • Přidávání nových zbraní do sortimentu
5. Agent
   • Koupě zbraně od obchodníka
   • Editace svých osobních údajů
6. Člověk
   • Vstup do organizace
   • Založení nové organizace
   • Zabití jiného člověka
   • Stát se obchodníkem se zbraněmi
   • Seznámit lidi

Nahoru

Kdo je kdo

Pánbů

Všemohoucí, neboli administrátor (pochopitelně s root přístupem)

Šéf organizace

Agent šéfující buňce, která je v dané organizaci nejvýše v hierarchii. Zároveň je šéf buňky, agent a člověk a může být i obchodník se zbraněmi

Šéf buňky

Agent šéfující libovolné buňce. Zároveň je agent a člověk a může být i obchodník se zbraněmi.

Ostatní

Ostatní uživatele není IMHO třeba vysvětlovat.

Nahoru

Scénáře

1. Stvoření
   • Pánbů stvoří svět (create script)
   • Pánbů stvoří lidi a státy (naplnění databáze)
2. Vytvoření organizace
   • Člověk založí novou organizaci, vytvoří buňku a stane se jejím šéfem. Následně pod sebe může verbovat další osoby a zakládat další buňky
3. Přidání buňky
   • Šéf buňky pověří jednoho ze svých agentů vytvořením a vedením nové buňky, případně pro to naverbuje nového člověka.
4. Prodej zbraně
   • Zbraň si může koupit jen ten, kdo "zná někoho kdo zná někoho ..." kdo prodává zbraně. Buď nějakým způsobem zná dotyčného člověka, nebo ho přes někoho zkontaktuje a uskuteční s ním obchod.

Prozatím to jsou všechny scénáře, přibudou další. Současný návrh umožňuje opravdu hodně možných scénářů.
Nahoru

SQL dotazy

1. Vyber všechny státy, v nichž operuje alespoň jedna buňka organizace "K.L.M.N.".

   {country[id→country_id] * {cell[organization_id→id, country_id] * {organization[id](name = "K.L.M.N.")}}}[name]
   SELECT
      country.name
   FROM
      country, cell, organization
   WHERE
      organization.name LIKE "K.L.M.N." AND cell.organization_id = organization.id AND cell.country_id = country.id
   GROUP BY
      country.id
   ORDER BY
      country.name
   Nahoru

2. Které organizace mají více než pět členů?

   {agent[id] * {cell[id → cell_id] * {organizace[id → organization_id]}}}(count(agent[id]) >= 5)[name → "Organizace", count(agent[id]) → "Pocet agentu"]
   SELECT
      o.name AS "Organizace", count(a.id) AS "Pocet agentu"
   FROM
      cell c
   JOIN
      organization o ON o.id = c.organization_id
   JOIN
      agent a ON a.cell_id = c.id
   GROUP BY
      o.id
   HAVING
      count(a.id) > 5
   ORDER BY
      o.name
   Nahoru

3. Zbraně, které zabily alespoň jednoho člověka.

   RA
   SELECT
      w.id AS "ID zbrane", wt.name AS "Typ", count(m.victim_id) AS "Pocet zabitych"
   FROM
      murders m
   JOIN
      weapon w ON w.id = m.weapon_id
   JOIN
      weapon_types wt ON wt.id = w.type_id
   GROUP BY
      w.id
   HAVING
      count(m.victim_id) >= 1
   Nahoru

4. Osoby které vlastnily více než jednu zbraň a přitom nejsou obchodníci se zbraněmi.

   RA
   SELECT
      h.id AS "ID cloveka", concat(h.name," ",h.surename) AS "Jmeno", count(w.weapon_id) AS "Pocet zbrani"
   FROM
      human h
   JOIN
      human_has_weapon w ON w.human_id = h.id
   WHERE
      h.id NOT IN (SELECT human_id FROM trader)
   GROUP BY
      h.id
   HAVING
      count(w.weapon_id) > 1
   Nahoru

5. Člověk vlastnící nějakou zbraň v době kdy byl zabit

   {human[id → victim_id] * murders[occurs] * human_has_weapon[human_id → victim_id, since, onto](since ≤ occurs ∧ (onto ≥ occurs ∨ onto = NULL))}[name]
   SELECT DISTINCT
      h.id AS "ID cloveka", concat(h.name, " ", h.surename) AS "Jmeno"
   FROM
      human h
   JOIN
      murders m ON m.victim_id = h.id
   JOIN
      human_has_weapon w ON w.human_id = h.id AND w.since <= m.occurs AND (w.onto >= m.occurs OR w.onto IS NULL)
   ORDER BY
      h.id
   Nahoru

6. Typy zbraní vlastněných vojáky organizací s ideologií "teletubbies", seřazené podle počtu jejich výskytů.

   RA
   SELECT
     wt.name AS "Typ zbrane", count(osoby.human_id) AS "Pocet majitelu"
   FROM
     weapon_types wt
   RIGHT JOIN
     weapon w ON w.type_id = wt.id
   RIGHT JOIN
     human_has_weapon hhw ON hhw.weapon_id = w.id
   RIGHT JOIN(
     SELECT
      a.human_id
     FROM
      agent a
     RIGHT JOIN (
      SELECT
      ahf.agent_id
      FROM
      agent_has_function ahf
      RIGHT JOIN
      specification spec ON spec.id = ahf.specification_id
      WHERE
      spec.name LIKE "Vojak"
     ) vojaci ON vojaci.agent_id = a.human_id
     RIGHT JOIN
      cell c ON a.cell_id = c.id
     RIGHT JOIN
      organization o ON o.id = c.organization_id
     RIGHT JOIN
      ideology i ON i.id = o.ideology_id
     WHERE
      i.name = "teletubbies"
     ORDER BY
      human_id
   ) osoby ON osoby.human_id = hhw.human_id
   GROUP BY
     wt.id
   ORDER BY
     count(osoby.human_id)
   Malé upozornění - při tomto dotazu se může ve výpisu objevit více zbraní daného typu než kolik jich je celkem k dispozici. Je to proto, že zbraně v průběhu času mohou měnit majitele a pro ukázku jejich obliby je nejlepší vidět, kolikrát byly dané zbraně koupeny.
   Nahoru

7. Vyber všechny osoby, které nejsou agentem

   {human[id] \ human[id] * {agent[human_id → id]}}
   SELECT
     *
   FROM
     human
   WHERE
     human.id NOT IN (SELECT human.id FROM human, agent WHERE human.id = agent.human_id GROUP BY human.id)
   ORDER BY
     id
   Nahoru

8. Vyber všechny agenty organizace "K.L.M.N." kteří někdy měli v držení zbraň typu glock

   {{agent * cell[id → cell_id] * organization(name = "K.L.M.N.")[id → organization_id]}[human_id] * {human[id → human_id] * human_has_weapon * weapon[id →weapon_id] * weapon_types(name = "glock")[id → type_id]}}
   SELECT
     Q.*
   FROM (
     SELECT
       a.*
     FROM
       agent a
     JOIN
       cell c ON c.id = a.cell_id
     JOIN
       organization o ON o.id = c.organization_id WHERE o.name LIKE "K.L.M.N."
     ) Q
   JOIN (
     SELECT
       a.*
     FROM
       agent a
     JOIN
       human h ON h.id = a.human_id
     JOIN
       human_has_weapon hhw ON hhw.human_id = h.id
     JOIN
       weapon w ON w.id = hhw.weapon_id
     JOIN
       weapon_types wt ON wt.id = w.type_id WHERE wt.name LIKE "glock"
     ) g ON g.id = Q.id
   GROUP BY
     Q.id
   Nahoru

9. Všichni lidé, kteří znají nějakého alespoň dvojitého agenta, který byl (bude) 21.5.2007 ještě naživu

   RA
   SELECT
     h.*
   FROM
     human h
   JOIN
     human_knows_human hkh ON hkh.who = h.id
   JOIN (
     SELECT
       h.*
     FROM
       human h
     JOIN
       agent a ON a.human_id = h.id
     WHERE
       h.id NOT IN (SELECT m.victim_id AS id FROM murders m WHERE occurs < "2007-05-21")  
     GROUP BY
       h.id
     HAVING
       count(h.id) > 1
   ) dvojti ON dvojti.id = h.id
   GROUP BY
     h.id
   ORDER BY
     h.id
   Nahoru

10. Osoby které pracují jako agent pouze v organizaci "Oz-tubbies"

    {human[id → human_id] * agent * cell[id → cell_id] * organization(name = "Oz-tubbies")[id → organization_id]}\{human[id → human_id] * agent * cell[id → cell_id] * organization(name ≠ "Oz-tubbies")[id → organization_id]}
    SELECT
      verniAgenti.*
    FROM (
      SELECT
        h.*
      FROM
        human h
      JOIN
        agent a ON a.human_id = h.id
      GROUP BY
        a.human_id
      HAVING
        count(a.id) = 1
      ) verniAgenti
    JOIN
      agent a ON a.human_id = verniAgenti.id
    JOIN
      cell c ON c.id = a.cell_id
    JOIN
      organization o ON o.id = c.organization_id WHERE o.name LIKE "Oz-tubbies"
    Nahoru

11. Civilní jména a kódová označení hlavních bossů všech organizací

    {human[id → human_id, name, surename] * agent[id → agent_id, human_id, cell_id, codename] * agent_has_function * specification(name = "boss")[id → specification_id]}*{cell(id = superior_cell)[id → cell_id]}[name, surename, codename]
    SELECT
      concat(h.name, " ", h.surename) AS "Jmeno bosse", a.codename AS "Nickname bosse", o.name AS "Organizace"
    FROM
      human h
    JOIN
      agent a ON a.human_id = h.id
    JOIN
      agent_has_function ahf ON ahf.agent_id = a.id
    JOIN
      specification s ON s.id = ahf.specification_id
    JOIN
      cell c ON c.id = a.cell_id
    JOIN
      organization o ON o.id = c.organization_id
    WHERE
      s.name LIKE "boss" AND c.id = c.superior_cell
    Nahoru

12. Vyber všechny osoby které někdy koupily novou zbraň od člověka se jménem "Evzen Onegin".

    {{human[id → human_id] * human_has_weapon * weapon[id → weapon_id] * trader[human_id → trader_id]}[human_id, trader_id] * {human(name = "Evzen" ∧ surename = "Onegin")[id→trader_id]}[trader_id]}[human_id]
    SELECT
      h.*, count(h.id) AS "Pocet nakupu"
    FROM
      human h
    JOIN
      human_has_weapon hhw ON hhw.human_id = h.id
    JOIN
      weapon w ON hhw.weapon_id = w.id
    JOIN
      trader t ON w.trader_id = t.human_id
    JOIN
      human h2 ON h2.id = t.human_id
    WHERE
      h2.name LIKE "Evzen" AND h2.surename LIKE "Onegin" AND h.id != h2.id
    GROUP BY
      h.id
    Nahoru

13. Vypiš kdo byl kdy kým a čím zabit

    RA
    SELECT
      concat(h.name," ",h.surename) AS "Jmeno zabiteho", m.occurs AS "Kdy byl zabit", concat(h2.name," ",h2.surename) AS "Jmeno vraha", wt.name as "Zbran"
    FROM
      human h
    JOIN
      murders m ON m.victim_id = h.id
    JOIN
      weapon w ON w.id = m.weapon_id
    JOIN
      weapon_types wt ON wt.id = w.type_id
    JOIN
      human_has_weapon hhw ON hhw.weapon_id = w.id
    JOIN
      human h2 ON h2.id = hhw.human_id
    WHERE
      hhw.since <= m.occurs AND (hhw.onto >= m.occurs OR hhw.onto IS NULL)
    Nahoru

14. Všechny osoby které pracují v zemi ze které pocházejí ale pro cizí agenturu / organizaci

    RA
    SELECT
      h.*
    FROM
      human h
    JOIN
      agent a ON a.human_id = h.id
    JOIN
      cell c ON c.id = a.cell_id
    JOIN
      country cn1 ON cn1.id = c.country_id
    JOIN
      organization o ON o.id = c.organization_id
    JOIN
      country cn2 ON cn2.id = o.work_for
    JOIN
      country cn3 ON cn3.id = h.country_id
    WHERE
      cn1.id = cn3.id AND cn1.id != cn2.id
    Nahoru

15. Typy zbraní, jimiž ještě nebyla spáchána žádná vražda (žádnou zbraní toho typu).

    {{weapon_types[name, id → type_id]}\{murders * weapons[id → weapon_id] * weapon_types[name, id → type_id]}}[name]
    SELECT
      wt.name
    FROM
      weapon_types wt
    JOIN
      weapon w ON w.type_id = wt.id
    WHERE
      wt.id NOT IN (
        SELECT
          wt.id
        FROM
          weapon_types wt
        JOIN
          weapon w ON w.type_id = wt.id
        JOIN
          murders m ON m.weapon_id = w.id
        GROUP BY
          wt.id
      )
    GROUP BY
      wt.id
    ORDER BY
      wt.id
    Nahoru

16. Obchodníci se zbraněmi, kteří nejsou agenty

    {{human[id → human_id] * trader[human_id]}\{human[id →human_id] * agent[human_id]}}[human_id]
    SELECT
      h.*
    FROM
      human h
    JOIN
      trader t ON t.human_id = h.id
    WHERE
      h.id NOT IN (SELECT h.id FROM human h JOIN agent a ON a.human_id = h.id GROUP BY h.id)
    Nahoru

17. Špehové kteří někdy měli v držení zbraň typu luk

    {specification(name = "speh")[id → specification_id] * agent_has_function * agent[id → agent_id, codename] * human_has_weapon * weapon[id → weapon_id] * weapon_types(name = "luk")[id → type_id]}[codename]
    SELECT
      a.*
    FROM
      agent a
    NATURAL JOIN
      human_has_weapon hhw
    JOIN
      weapon w ON hhw.weapon_id = w.id
    JOIN
      weapon_types wt ON wt.id = w.type_id
    JOIN
      agent_has_function ahf ON ahf.agent_id = a.id
    JOIN
      specification s ON s.id = ahf.specification_id
    WHERE
      wt.name LIKE "luk" AND s.name LIKE "speh"
    Nahoru

18. Vojáci kteří znají alespoň dva obchodníky

    RA
    SELECT
      a.*, count(a.id) AS "Kolik zna obchodniku"
    FROM
      agent a
    JOIN
      agent_has_function ahf ON ahf.agent_id = a.id
    JOIN
      specification s ON s.id = ahf.specification_id
    JOIN
      human_knows_human hkh ON a.human_id = hkh.who
    JOIN
      trader t ON t.human_id = hkh.whom
    GROUP BY
      a.id
    HAVING
      count(a.id) >= 2
    Nahoru

19. Lidé kteří měli cokoli společného s "Carlem Lindtem"

    RA
    SELECT
      concat(h.name," ", h.surename) AS "Osoba", "jeho vrah" AS "Druh znamosti"
    FROM
      human h
    JOIN
      human_has_weapon hhw ON hhw.human_id = h.id
    NATURAL JOIN
      murders m
    JOIN
      human h2 ON h2.id = m.victim_id
    WHERE
      h2.name LIKE "Carl" AND h2.surename LIKE "Lindt" AND m.occurs >= hhw.since AND (m.occurs <= hhw.onto OR hhw.onto IS NULL)
    UNION
    SELECT
      concat(h.name," ",h.surename) AS "Osoba", "jeho obet" AS "Druh znamosti"
    FROM
      human h
    JOIN
      murders m ON m.victim_id = h.id
    NATURAL JOIN
      human_has_weapon hhw
    JOIN
      human h2 ON h2.id = hhw.human_id
    WHERE
      h2.name LIKE "Carl" AND h2.surename LIKE "Lindt" AND m.occurs >= hhw.since AND (m.occurs <= hhw.onto OR
    hhw.onto IS NULL)
    UNION
    SELECT DISTINCT
      concat(h.name," ",h.surename) AS "Osoba", "Carl ho znal" AS "Druh znamosti"
    FROM
      human h
    JOIN
      human_knows_human hkh ON h.id = hkh.whom
    JOIN
      human h2 ON h2.id = hkh.who
    WHERE
      h2.name LIKE "Carl" AND h2.surename LIKE "Lindt"
    UNION
    SELECT DISTINCT
      concat(h.name," ",h.surename) AS "Osoba", "znal Carla" AS "Druh znamosti"
    FROM
      human h
    JOIN
      human_knows_human hkh ON h.id = hkh.who
    JOIN
      human h2 ON h2.id = hkh.whom
    WHERE
      h2.name LIKE "Carl" AND h2.surename LIKE "Lindt"
    Nahoru

20. Obchodníci, kteří jsou zároveň dvojitými agenty

    RA
    SELECT
      t.*
    FROM
      trader t
    NATURAL JOIN
      agent a
    GROUP BY
      t.human_id
    HAVING
      count(t.human_id) >= 2
    Nahoru

Changelog

3.1.2007

• Dotazy doplněné na počet 20, částečně dokončená relační algebra.
• Přidán sql soubor s dotazy.

2.1.2007

• Přidáno prvních 14 dotazů.

28.12.2006

• V tabulce human_has_weapon byla do klíče přidána položka since, jinak by nebylo možné aby jeden člověk vlastnil jednu zbraň víckrát.
• Nový SQL insert skript.
• Nový XML soubor.
• Nový MDB XML soubor.

27.12.2006

• Změna databázového modelu - tabulka cell_has_agent nahrazena vztahem Agent is in cell a tabulkou přiřazující jednotlivé funkce jedntlivým agentům, přičemž jeden agent může zastávat v buňce více funkcí.
• Nové vygenerování SQL skriptu pro vytvoření DB.
• Nový XML soubor.
• Přidán XML soubor ve standartu MDB XML.
• Přidán SQL drop skript.
• Přidán SQL insert skript.

18.12.2006

• Změna ikonky webu ;)

6.12.2006

• Změna databázového modelu - upraven vztah cell - agent - cell a nahrazen novou tabulkou cell_has_agent určující ve které buňce který agent pracuje a jaká je jeho konkrétní pozice (specification = member | boss | ...)
• Změna (nové vygenerování) SQL skriptu pro vytvoření DB.

Nahoru