Adatbázis-tervezés¶

DBML alapok¶

Az adatbázisok tervezését DBML nyelven végezzük, on-line tesztelésük a dbdiagram.io oldalon tehetjük meg.

Példák:

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ű:

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

Első terv:

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)

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:

Pendulum|Elemental|2002|1|Driver
Pendulum|Elemental|2002|2|Nothing for Free
Pendulum|Elemental|2002|3|Louder Than Words by Pendulum & Hybrid Minds
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:

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.

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
}

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

Tervezési feladat: mini Neptun¶

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

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]
}

mySql:

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¶

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

mySql:

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¶

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

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
}

mySQL

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ő:¶

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.

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.