DORDB - zápočtový projekt

Projekt pracuje se zjednodušenou databází softwarové firmy. Databáze je od společnosti Oracle.
Vytvořil Michal Bajer.

1. ER diagram

2. Dodatečná integritní imezení

  1. Triviální omezení

    1. Division - capacity > 1
      Kapacita divize musí být větší než 1.
    2. Project - dateFrom < dateTo
      Datum začátku projektu musí být menší než datum konce projektu.
    3. UserProject - extraworkPrice > 0
      Cena vícerpací na projektu uživatele musí být větší než 0.
    4. UserProject - dateFrom < dateTo
      Datum začátku projektu musí být menší než datum konce projektu.
  2. Netriviální omezení

    1. UserTool
      V jeden okamžik může jedno zařízení (Tool) vlastnit pouze jeden uživatel (User) a také není možné vypůjčit zařízení pomocí aktualizace již vráceného (je nutné založit nový záznam).
      Konkrétněji: Ke každému objektu Tool existuje maximálně jeden objekt UserTool, který má atribut dateDeleted NULL.

3. Slovní návrh API rozhraní business logiky

  1. Přijetí zaměstnance

    + type (INT, typ oddělení)
    + name (STRING, jméno)
    + surname (STRING, příjmení)
    + email (STRING, emailová adresa)
    + phone (STRING, telefon)
    + baseExtraworkPrice (DECIMAL, základní cena víceprací)
    Procedura vybere divizi podle parametru type, která má nejméně zaměstnanců. Tato divize bude přiřazena uživateli. Zároveň bude přiřazen služební notebook (pokud bude k dispozici). Zaměstnanci bude vygenerováno náhodné heslo.
  2. Založení divize

    + name (string, název oddělení)
    + type (INT, typ oddělení)
    + capacity (INT, kapacita)
    + maxExtraworkPrice (DECIMAL, maximální cena víceprací)
    Procedura vybere volného uživatele, který bude dělat leadera. Leader bude ten s nejvíce odpracovanými hodinami a který současně nedělá leadera žádné jiné divizi. Leaderovi bude navýšena základní cena víceprací o 20% (baseExtraworkPrice), pokud bude splněna podmínka, že navýšená cena bude menší než vstupní parametr maxOverworkPrice.

4. Pět slovně formulovaných dotazů nad schématem

  1. Oddělení, které má nejmenší průměrnou základní cenu víceprací
  2. Pracovní pomůcky, které jsou využívány na konkrétním projektu.
  3. Uživatel, který strávil nejvíce hodin na projektu v základní hodinové dotaci (v rámci budgetu).
  4. Uživatelé, kteřá jsou přiřazeni na některém projektu a přitom nemají přiřazený firemní notebook.
  5. Top 5 Uživatelů, kteří vykázali vykázal nejvíce hodin víceprací a jsou současně leaderem nějaké divize a také manažerem.

5. Skript, který vytvoří databázové schéma

6. Skript, který vytvoří v databázovém schématu dodatečná integritní omezení

7. Skript, který naplní tabulky testovacími daty

8. Skript, který provede postupně všechny navržené dotazy z bodu 4

SET AUTOTRACE ON;

  1. COST = 48
    -- 1
    SELECT a.* FROM (
       SELECT d.DIVISIONID, d.NAME, avg(u.baseextraworkprice) AS DIVISIONAVG FROM
       DIVISION d
       JOIN "USER" u ON d.DIVISIONID = u.DIVISIONID
       GROUP BY d.DIVISIONID, d.NAME
       ORDER BY DIVISIONAVG) a
    WHERE rownum = 1;
    
    Dotaz se subselectem je výrazně úspornější.
    COST = 18
    -- 1 OPTIMALIZOVÁNO
    SELECT a.* FROM (
       SELECT D.DIVISIONID, D.NAME, (
          SELECT avg(u.baseextraworkprice) FROM "USER" U WHERE U.DIVISIONID = D.DIVISIONID
       ) AS DIVISIONAVG FROM
       DIVISION D
       ORDER BY DIVISIONAVG) a
    WHERE rownum = 1;
    

  2. --2
    SELECT T.* FROM "PROJECT" P
    JOIN USERPROJECT UP ON UP.PROJECTID = P.PROJECTID
    JOIN USERTOOL UT ON UT.USERID = UP.USERID
    JOIN TOOL T ON T.TOOLID = UT.TOOLID
    WHERE
    P.PROJECTID = 1 AND
    UT.DATEDELETED IS NULL;
    

  3. --3
    SELECT a.* FROM (
       SELECT U.USERID, sum(r.minutesspent) AS TOTALEXTRAWORK FROM
       REPORT R
       JOIN USERPROJECT UP ON UP.USERPROJECTID = R.USERPROJECTID
       JOIN "USER" U ON U.USERID = UP.USERID
       WHERE
          R.TYPE = 1
       GROUP BY U.USERID
       ORDER BY TOTALEXTRAWORK DESC) a
    WHERE rownum = 1;
    

  4. --4
    SELECT U.* FROM "USER" U
    WHERE
       EXISTS (SELECT 1 FROM USERPROJECT UP WHERE UP.USERID = U.USERID) AND
       NOT EXISTS (SELECT 1 FROM USERTOOL UT JOIN TOOL T ON T.TOOLID = UT.TOOLID WHERE UT.USERID = U.USERID AND T.TOOLTYPEID = 1 AND UT.DATEDELETED IS NULL);
    

  5. COST = 119
    --5
    SELECT a.* FROM (
      SELECT U.USERID, U.NAME, sum(r.minutesspent) AS TOTALEXTRAWORK FROM
      REPORT R
      JOIN USERPROJECT UP ON UP.USERPROJECTID = R.USERPROJECTID
      JOIN "USER" U ON U.USERID = UP.USERID
      WHERE
        R.TYPE = 2 AND
        EXISTS (SELECT 1 FROM DIVISION D WHERE D.LEADERID = U.USERID) AND
        EXISTS (SELECT 1 FROM PROJECT P WHERE P.MANAGERID = U.USERID)
      GROUP BY U.USERID, U.NAME
      ORDER BY TOTALEXTRAWORK DESC
    ) a WHERE rownum <= 5;
    
    Dotaz, který nahrazuje join subselectem je méně náročný.
    COST = 86
    --5 OPTIMALIZOVÁN
    SELECT a.* FROM (
      SELECT U.USERID, U.NAME, (
        SELECT Coalesce(sum(R.minutesspent), 0) FROM USERPROJECT UP
        JOIN REPORT R ON UP.USERPROJECTID = R.USERPROJECTID
        WHERE UP.USERID = U.USERID AND R.TYPE = 2
      ) AS TOTALEXTRAWORK FROM
      "USER" U
      WHERE
        EXISTS (SELECT 1 FROM DIVISION D WHERE D.LEADERID = U.USERID) AND
        EXISTS (SELECT 1 FROM PROJECT P WHERE P.MANAGERID = U.USERID)
      ORDER BY TOTALEXTRAWORK DESC
    ) a WHERE rownum <= 5;
    

9. Porušení dodatečná integritní imezení

10. Skript pro vytvoření balíku (package) a procedur „business logiky“

11. Testovací skript pro ověření procedur

12. Skript, který doplní do zvolené tabulky schématu sloupec, který bude obsahovat odvozené hodnoty – tyto hodnoty budou automaticky aktualizovány pomocí triggerů

13. Skript, který vytvoří úložiště ještě jednou v „objektové“ podobě:

  1. místo relačních tabulek budou použity uživatelsky definované typy Object a objektové tabulky (s jinými názvy než v dosavadním modelu)
  2. objektové tabulky budou naplněny importem z původních relačních tabulek
  3. datové cizí klíče budou nahrazeny referencemi na objekt

14. Skript, který provede postupně všechny navržené dotazy z bodu 4 v objektové verzi

SET AUTOTRACE ON;

  1. -- 1
    SELECT a.* FROM (
       SELECT u.division.DIVISIONID, u.division.NAME, avg(u.baseextraworkprice) AS DIVISIONAVG FROM
       ObjUser u
       GROUP BY u.DIVISION, u.division.name, u.division.DIVISIONID
       order by DIVISIONAVG
       ) a
    WHERE rownum = 1;
    

  2. --2
    SELECT T.* FROM ObjProject P
    JOIN ObjUserProject UP ON UP."PROJECT" = REF(P)
    JOIN ObjUserTool UT ON UT."USER" = UP."USER"
    JOIN ObjTool T ON REF(T) = UT.TOOL
    WHERE
    P.PROJECTID = 1 AND
    UT.DATEDELETED IS NULL;
    

  3. --3
    SELECT a.* FROM (
       SELECT U.USERID, sum(r.minutesspent) AS TOTALEXTRAWORK FROM
       ObjReport R
       JOIN ObjUserProject UP ON REF(UP) = R.USERPROJECT
       JOIN ObjUser U ON REF(U) = UP."USER"
       WHERE
          R.TYPE = 1
       GROUP BY U.USERID
       ORDER BY TOTALEXTRAWORK DESC) a
    WHERE rownum = 1;
    

  4. --4
    SELECT U.* FROM ObjUser U
    WHERE
       EXISTS (SELECT 1 FROM ObjUserProject UP WHERE UP."USER" = REF(U)) AND
       NOT EXISTS (SELECT 1 FROM ObjUserTool UT JOIN ObjTool T ON REF(T) = UT.TOOL WHERE UT."USER" = REF(U) AND T.TOOLTYPE.TOOLTYPEID = 1 AND UT.DATEDELETED IS NULL);
    

  5. --5
    SELECT a.* FROM (
      SELECT U.USERID, U.NAME, sum(r.minutesspent) AS TOTALEXTRAWORK FROM
      ObjReport R
      JOIN ObjUserProject UP ON REF(UP) = R.USERPROJECT
      JOIN ObjUser U ON REF(U) = UP."USER"
      WHERE
        R.TYPE = 2 AND
        EXISTS (SELECT 1 FROM ObjDivision D WHERE D.LEADER = REF(U)) AND
        EXISTS (SELECT 1 FROM ObjProject P WHERE P.MANAGER = REF(U))
      GROUP BY REF(U), U.NAME, U.USERID
      ORDER BY TOTALEXTRAWORK DESC
    ) a WHERE rownum <= 5;
    

15. Navrhněte slovně fakta a dimenze pro datový sklad vycházející z vaší databáze. Popište, k jakým analýzám bude možné tento sklad využít. Identifikujte tabulky/sloupce, ke kterým bude nutné v datovém skladu navíc evidovat historické hodnoty (pokud již v provozní databázi nejsou k dispozici).

  • Fakta mohou obsahovat informace o projektu: celkový vykázaný čas víceprací, vypočítané náklady a budget projektu
  • Dimenzemi mohou být upravené tabulky User, Division, Project a nově vytvořená tabulka s časy.
  • Sklad bude možné použít k analýze jednotlivých projektů, zda se firmě vyplatí - zisk vs. náklady, bude možné získat nejnáročnější projekty z pohledu víceprací.
  • V rámci navrhnovaného skladu není nutné uvažovat sloupce s tabulky či sloupce s historickými hodnotami, jelikož žádná data z databáze by se neměly nikdy mazat ani upravit - budou jen přibývat. Mimo tento sklad je možné uvést tabulku UserTool, která eviduje historické hodnoty jako vrácení firemního majetku do sloupce dateDeleted.