Adatbázis-tervezés
==================

DBML alapok
------------
Az adatbázisok tervezését `DBML <https://www.dbml.org/home/>`_ nyelven végezzük, on-line tesztelésük a `dbdiagram.io <https://dbdiagram.io/home>`_ oldalon tehetjük meg.

Példák:

.. code-block:: console

  Table users { }
  Data types: integer, text varcharacter
  Note: 'Stores user data'

  body text [
    pk,
    unique,
    not null,
    default: 123, default: `now()`,
    ref: > table.field,
    note: 'Comment',
    delete: cascade,
    update: no action]

  indexes {
    (id, country) [pk]
    created_at [name: 'created_at_index', note: 'Date']
  }

  Ref: U.id > posts.user_id // many-to-one
  Ref: U.id < posts.user_id // one-to-many
  Ref: U.id <> posts.user_id // many-to-many
  Ref: products.merchant_id > merchants.id [delete: cascade, update: no action]

Normalizálás
------------

Feladat:
^^^^^^^^

Tervezzünk programot, mely a CD lemezeinkről az alábbi adatokat tárolja:
Lemez előadója és címe, a rajta levő számok és sorrendjük, valamint a zenekar alapítási éve. Egy rekord a normalizálás előtt az alábbi felépítésű:

.. code-block:: console

  1|Pendulum: Elemental|2002|{1 Driver|2 Nothing for Free|3 Louder Than Words by Pendulum & Hybrid Minds|4 Come Alive}


Első terv:

.. code-block:: console

  Table compactdisks {
    cd_id integer [pk]
    album text
    founded date
    tracks text
  }


Ez nincs 1-es normálformában, mert:

* Az egyes mezők tovább oszthatók: album+előadó szétszedése, így azok atomi adatok lesznek
* Nem tartalmazhatnak felsorolást (tracks), ezért ezt is szétszedjük és újabb sorokat készítünk belőlük.

Az 1-es normálformához ezeket megszüntetjük, és új oszlopokat vezetünk be: a band-et és track-et.
Sajnos így már a cd_id nem kulcs többé, mert több sorban is azonos az értéke, ezért a kulcs: (cd_id, track)

.. code-block:: console

  Table compactdisks {
    cd_id integer [pk]
    band text
    album text
    founded date
    tracknumber integer [pk]
    title text
  }

A rekordjaink most így néznének ki:

.. code-block:: console

  1|Pendulum|Elemental|2002|1|Driver
  2|Pendulum|Elemental|2002|2|Nothing for Free
  3|Pendulum|Elemental|2002|3|Louder Than Words by Pendulum & Hybrid Minds
  4|Pendulum|Elemental|2002|4|Come Alive

Ez nincs 2NF-ben, mert az album címe csak az egyik kulcstól függ, a másiktól nem. Egy adatbázis második normálformába kerül, ha
* Első normálformában van,
* Eltávolítjuk az egyes táblák esetén azokat az adatcsoportokat, melyek több sort érintenek, ezeket új táblákba helyezzük, majd a régi és az új táblák közt kulcsokon alapuló kapcsolatokat hozunk létre.

Mivel a zenekarok esetés az albumok ismétlődnek, ezért kiemeljük azokat:

.. code-block:: console

  Table compactdisks {
    cd_id integer [pk]
    band text
    album text
    founded date
  }

  Table tracks {
    cd_id integer [ref: < compactdisks.cd_id]
    tracknumber integer
    title text
  }

Ez nincs 3NF-ben, mert a compactdisks táblában a cd_id-től függ az album, attól pedig az előadó, ezért trazitív függőség áll fenn. Oldjuk fel ezt! (Azon el lehet gondolkodni, hogy egy lemez csak egy előadós-e, de ettől most eltekintünk.

.. code-block:: console

  Table compactdisks {
    cd_id integer [pk]
    album text
    band_id integer [ref: > band.band_id]
  }

  Table band {
    band_id integer [pk]
    band text
    founded date
  }

  Table tracks {
    cd_id integer [ref: < compactdisks.cd_id]
    tracknumber integer
    title text
  }

.. image:: images/cds.png
        :alt: CD tár adatbázis
        :width: 500px
        :align: center

Ez már 3NF-ben van, mert nincs tranzitív függőség.


Tervezési feladat: mini Neptun
------------------------------

.. code-block:: console

  hallgató neve|telefonszáma|évfolyam|szak|TO-s ügyintéző|tárgykód|tárgyNév|jegy
  Kiss Lajos|+36705591210,+36361221213|1|matematika|Szabó Ágnes|T-01|Analízis|5
  Kiss Lajos|+36705591210,+36361221213|1|matematika|Szabó Ágnes|T-02|Algebra|5

.. code-block:: console

  Table tanulok {
    id integer [pk]
    nev text
    evfolyam integer
  }

  Table szakok {
    id integer [pk, ref: <> tanulok.id]
    szaknev text
  }

  Table telefon {
    id integer [pk]
    telefonszam text
    tanulo_id integer
  }
  ref: telefon.tanulo_id > tanulok.id [delete: cascade]

  Table ugyintezok {
    id integer [pk]
    nev text
    tanulo_id integer [ref: < tanulok.id]
  }

  Table kurzusok {
    id integer [pk]
    kurzus text
  }

  Table jegyek {
    tanulo_id integer [ ref: > tanulok.id ]
    kurzus_id integer [ ref: > kurzusok.id ]
    jegy integer [ default: null]
  }

.. image:: images/mini-neptun.png
        :alt: Mini Neptun
        :width: 500px
        :align: center

mySql:

.. code-block:: console

  CREATE TABLE tanulo (
    id integer PRIMARY KEY,
    nev text,
    evfolyam integer
  );

  CREATE TABLE szakok (
    id integer PRIMARY KEY,
    szaknev text
  );

  CREATE TABLE telefon (
    id integer PRIMARY KEY,
    telefonszam text,
    tanulo_id integer
  );

  CREATE TABLE ugyintezok (
    id integer PRIMARY KEY,
    nev text,
    tanulo_id integer
  );

  CREATE TABLE kurzusok (
    id integer PRIMARY KEY,
    kurzus text,
    erdemjegy id,
    tanulo_id integer
  );

  CREATE TABLE tanulo_szakok (
    tanulo_id integer NOT NULL,
    szakok_id integer NOT NULL,
    PRIMARY KEY (tanulo_id, szakok_id)
  );

  ALTER TABLE tanulo_szakok ADD FOREIGN KEY (tanulo_id) REFERENCES tanulo (id);
  ALTER TABLE tanulo_szakok ADD FOREIGN KEY (szakok_id) REFERENCES szakok (id);
  ALTER TABLE telefon ADD FOREIGN KEY (tanulo_id) REFERENCES tanulo (id) ON DELETE CASCADE;
  ALTER TABLE ugyintezok ADD FOREIGN KEY (tanulo_id) REFERENCES tanulo (id);
  ALTER TABLE kurzusok ADD FOREIGN KEY (tanulo_id) REFERENCES tanulo (id);


Tervezési feladat: jogosultsági rendszer
----------------------------------------

.. code-block:: console

  Table rights {
    id INT [pk, increment]
    registered VARCHAR [not null]
    name VARCHAR [not null]
  }

  Table users {
    id INT [pk, increment]
    username VARCHAR [not null, unique]
    password VARCHAR [not null]
    realname varchar [default: null]
    email varchar [not null]
    magiclink varchar [default: null]
    indexes {
      (id)
    }
  }

  table groups {
    id int [ pk, increment ]
    name varchar [not null]
  }

  Table prevpasswords {
    id int [pk, increment]
    uid int [ref: > users.id]
    password varchar [not null]
    indexes {
      (password)
    }
  }

  Ref: rights.id <> users.id
  Ref: users.id <> groups.id
  Ref: groups.id <> rights.id

.. image:: images/permissions.png
        :alt: Jogosultsági rendszer
        :width: 500px
        :align: center


mySql:

.. code-block:: console

  CREATE TABLE rights (
    id INT PRIMARY KEY AUTO_INCREMENT,
    registered VARCHAR NOT NULL,
    name VARCHAR NOT NULL
  );

  CREATE TABLE users (
    id INT PRIMARY KEY AUTO_INCREMENT,
    username VARCHAR UNIQUE NOT NULL,
    password VARCHAR NOT NULL,
    realname varchar(255) DEFAULT null,
    email varchar(255) NOT NULL,
    magiclink varchar(255) DEFAULT null
  );

  CREATE TABLE groups (
    id int PRIMARY KEY AUTO_INCREMENT,
    name varchar(255) NOT NULL
  );

  CREATE TABLE prevpasswords (
    id int PRIMARY KEY AUTO_INCREMENT,
    uid int,
    password varchar(255) NOT NULL
  );

  CREATE INDEX users_index_0 ON users (id);
  CREATE INDEX prevpasswords_index_1 ON prevpasswords (password);
  ALTER TABLE prevpasswords ADD FOREIGN KEY (uid) REFERENCES users (id);
  CREATE TABLE rights_users (
    rights_id INT NOT NULL,
    users_id INT NOT NULL,
    PRIMARY KEY (rights_id, users_id)
  );
  ALTER TABLE rights_users ADD FOREIGN KEY (rights_id) REFERENCES rights (id);
  ALTER TABLE rights_users ADD FOREIGN KEY (users_id) REFERENCES users (id);

  CREATE TABLE users_groups (
    users_id INT NOT NULL,
    groups_id int NOT NULL,
    PRIMARY KEY (users_id, groups_id)
  );

  ALTER TABLE users_groups ADD FOREIGN KEY (users_id) REFERENCES users (id);
  ALTER TABLE users_groups ADD FOREIGN KEY (groups_id) REFERENCES groups (id);

  CREATE TABLE groups_rights (
    groups_id int NOT NULL,
    rights_id INT NOT NULL,
    PRIMARY KEY (groups_id, rights_id)
  );

  ALTER TABLE groups_rights ADD FOREIGN KEY (groups_id) REFERENCES groups (id);
  ALTER TABLE groups_rights ADD FOREIGN KEY (rights_id) REFERENCES rights (id);


Tervezési feladat: versenyek
----------------------------

.. code-block:: console

  versenyek|időpontok|versenyző|iskola|osztály|pontok
  Programozó, Alkalmazó|2022.10.10., 2021.11.09.|Nagy Anna|Neumann Általános Iskola|7a|70,80

.. code-block:: console

  Project project_name {
    database_type: 'mySql'
    Note: 'Versenyek'
  }

  Table versenyek {
    id integer [pk, increment]
    nev varcharacter
  }

  Table versenyzo {
    id integer [pk, increment, ref: <> versenyek.id]
    osztaly varchar
    iskola_id integer [ref: < iskolak.id]
  }

  Table pontszamok {
    verseny_id integer [ref: > versenyek.id]
    versenyzo_id integer [ref: > versenyzo.id]
    pontszam integer
  }

  Table iskolak {
    id integer [pk, increment]
    nev varcharacter
  }

.. image:: images/versenyek.png
        :alt: Versenyek
        :width: 500px
        :align: center

mySQL

.. code-block:: console

  CREATE TABLE versenyek (
    id integer PRIMARY KEY AUTO_INCREMENT,
    nev varcharacter
  );

  CREATE TABLE versenyzo (
    id integer PRIMARY KEY AUTO_INCREMENT,
    osztaly varchar(255),
    iskola_id integer
  );

  CREATE TABLE pontszamok (
    verseny_id integer,
    versenyzo_id integer,
    pontszam integer
  );

  CREATE TABLE iskolak (
    id integer PRIMARY KEY AUTO_INCREMENT,
    nev varcharacter
  );

  CREATE TABLE versenyek_versenyzo (
    versenyek_id integer NOT NULL,
    versenyzo_id integer NOT NULL,
    PRIMARY KEY (versenyek_id, versenyzo_id)
  );

  ALTER TABLE versenyek_versenyzo ADD FOREIGN KEY (versenyek_id) REFERENCES versenyek (id);
  ALTER TABLE versenyek_versenyzo ADD FOREIGN KEY (versenyzo_id) REFERENCES versenyzo (id);
  ALTER TABLE iskolak ADD FOREIGN KEY (id) REFERENCES versenyzo (iskola_id);
  ALTER TABLE pontszamok ADD FOREIGN KEY (verseny_id) REFERENCES versenyek (id);
  ALTER TABLE pontszamok ADD FOREIGN KEY (versenyzo_id) REFERENCES versenyzo (id);


Feladat: kazetta kölcsönző:
---------------------------

.. code-block:: console

  nev|teljes cím|filmek, amiket kölcsönzött|Mr./Mrs


Komplex feladatok
=================

Web tervezés 1
--------------

Az egyetem Élelmiszertudományi Borászati Tudásközpontja megbízza önt egy szoftver kifejlesztésével, melynek fő célja a Központ weblapjának működtetése, melyben nem csak leíró információkat, hanem különböző élelmiszervizsgálati rendeléseket is kezelni kell. A weblap fő funkciói:

#. Statikus leíró weblapok megjelenítése.
#. Megrendelői regisztráció és kapcsolódó műveletei.
#. Kliensekre és belső dolgozókra vonatkozó adminisztrátori feladatok.
#. Rendelések adminisztrációja.
#. Termékvizsgálat elvégzése.
#. Termékvizsgálat ellenőrzése.

Egy termékvizsgálathoz a megrendelőnek meg kell adni:

#. A minta nevét.
#. Bor/Must/aszú stb.
#. Vörös/fehér/stb.
#. A vizsgálat tárgyát: pl. levegő próba, szorbinsav mérés stb.

A termék vizsgálatát követően jegyzőkönyv készül, melyet a megrendelő a felületre bejelentkezve letölthet, illetve kiállításra kerül a vizsgálat számlája is, melyet a rendszerből lehet előállítani.

Készítse el a usecase diagramot, valamint az adatbázis tervet!

Mérési rendszer tervezése
-------------------------

A munkáltatója egy komplex tervezési feladattal bízza meg. Az ügyfél mellékelt egy feladat leírást, mely az alábbiakban olvasható. Tervezze meg és készítse el a szoftver usecase diagramját, és a normalizált adatbázis tervét a tanult rendszerekben.

*Több különálló vegyipari vállalat számára látunk el vízminőség ellenőrzési feladatokat, melyekhez ipari mérőműszereket alkalmazunk. A műszerek a hűtővíz különböző paramétereit mérik, melyeket jelenleg körforgásos rendszerben, műszerészeink leolvasással jegyzőkönyvezünk. Ezt a folyamatot szeretnénk automatizálni úgy, hogy a lehető legjobban támogassa a munkánkat.
Jelenleg kb. 50 mérőberendezés működik 9 mérési ponton, melyek digitális kimenettel rendelkeznek, a mért értékek abból számítógéppel kiolvashatók. Ezek jelenleg az következőket mérik: pH, zavarosság, szabad klór, polimer koncentráció. Tervezzük további műszerek beszerzését, pl. lúgosság vagy hőmérséklet műszereket.
Az elsődleges feladat a mért értékek 10 percenként történő kiolvasása és rögzítése úgy, hogy az visszamenőleg is követhető legyen. Emellett műszerész munkatársaink jelenleg műszerre lebontva havi rendszerességgel riportokat készítenek, mely kiemelten tartalmazza a rendkívüli eseményeket, melyek során a mért értékek kiléptek a megengedett tartományból.
Egyes cégek hozzáférést kérnek a mért adatokhoz, ezért a kijelölt munkatársaiknak látniuk kellene a saját egységük műszereit.
A rendszernek tartalmaznia kellene a saját működéséhez szükséges kiegészítő funkciókat, mint az adatok mentése vagy archiválása.
Előzetes képernyőkép terveket már készítettünk, amennyiben további funkciók megvalósítását szükségesnek tartja, kérem, vegye figyelembe a tervezés során.*

.. image:: images/bc-1.png
        :alt: Komplex-1
        :width: 500px
        :align: center

.. image:: images/bc-2.png
        :alt: Komplex-2
        :width: 500px
        :align: center

További feladatok
-----------------

Tervezd meg az alábbi adatbázisokat úgy, hogy azok legalább 3. normálformában legyenek! Mindegyik esetben mutasd meg, hogy teljesülnek a 3. normálforma feltételei!

#. Egy film adatbázisban a következő információkat tároljuk: film cím, rendező, főszereplők, film kategória (pl. akció, romantikus stb.), kiadási év és film leírás.
#. Egy webáruházban termékek adatait tároljuk. Minden terméknek van cikkszáma, neve, leírása, ára és készleten lévő darabszáma.
#. Egy iskolai rendszerben tanulók adatait tároljuk. Minden tanulóhoz tartozik név, osztály, születési dátum és az osztályfőnök neve.
#. Egy banki rendszerben bankszámlák adatait tároljuk. Minden bankszámlához tartozik számlaszám, egy vagy több ügyfél neve, cím, egyenleg és a tranzakciók története.
#. Egy szállodai rendszerben szobák és vendégek adatai szerepelnek. Minden szobához tartozik szobaszám, szobatípus, ár és a foglalások listája. A vendégekről a nevet, címet, foglalás kezdetét és végét rögzítjük.
#. Egy könyvtári rendszerben könyvek és a könyvtári tagok adatai szerepelnek. Minden könyvhöz tartozik cím, szerző, kiadási év és kölcsönzések listája. Minden taghoz név, cím és kölcsönzött könyvek listája tartozik.
#. Egy online fórumhoz tároljuk a felhasználók adatait és a hozzászólásaikat. Minden felhasználóhoz tartozik felhasználónév, e-mail cím és jelszó. Minden hozzászóláshoz tartozik tartalom, időbélyeg és az azt író felhasználó.
#. Egy személyzetkezelő rendszerben dolgozók és projektek adatai szerepelnek. Minden dolgozónak van azonosítója, neve, beosztása és a projektek listája, amelyben részt vesz. Minden projektnek van azonosítója, neve és az abban résztvevő dolgozók listája.
#. Egy konferencia regisztrációs rendszerben tároljuk a résztvevők és a konferencia események adatait. Minden résztvevőnek van azonosítója, neve, e-mail címe és jelentkezései a különböző regisztrált eseményekre. Minden konferenciaeseménynek van azonosítója, címe, időpontja és az azon résztvevők listája.
#. Egy zenei streaming alkalmazásban tároljuk a zenészek és az albumok adatait. Minden zenésznek van azonosítója, neve és kiadott lemezek listája. Minden albumnak van azonosítója, címe, megjelenési éve.