Adatbázisok gyakorlat 2008

Tekintsük a következő táblákat:

aruk (akod, nev)
forgalom (akod, menny)

Listázzuk ki

1. az összes árut és az összes forgalmat
2. az összes árut és a hozzá tartozó összes forgalmat

SELECT nev, menny 
FROM aruk a, forgalom f 
WHERE a.akod=f.akod

3. név szerint rendezve!
4. Kód szerint csoportosítva és a forgalmat összegezve

SELECT nev, SUM(menny) 
FROM aruk, forgalom 
WHERE aruk.akod=forgalom.akod
GROUP BY aruk.akod

5. Mint az elobb, de csak a 10-nél nagyobb összegekre vagyunk kíváncsiak

SELECT nev, SUM(menny) 
FROM aruk, forgalom WHERE aruk.akod=forgalom.akod
GROUP BY aruk.akod, nev 
HAVING SUM(menny)>10

Tekintsük a következő táblákat:

create table rates (currency varchar(5), date datetime, rate	float )
create table currency( currency varchar(5), name varchar(50) )

Vegyünk fel a táblákba adatokat:

insert into currency values ('HUF', 'Magyar Forint' )
insert into rates values ( 'HUF', '2001-01-02', 169 )

Listázzuk ki az

1. összes sort a currency táblából, ahol 'CHF' a pénznem!
2. összes sort ahol a dátum 2001-01-02!
3. összes sort ahol a pénznem 'CHF' vagy 'USD'!
4. Adjunk listát, hogy mely pénznemek szerepelnek az árfolyamok táblában!
5. Adjuk meg az átlagos árfolyamot pénznemenként
6. Adjuk meg hogy mi volt az átlagos árfolyam 2006 januárjában pénznemenkénti bontásban
7. Adjuk meg a 2001 januári árfolyamok közül a legkisebb és a legnagyobb értékeket pénznemenként, kiírva a pénznem nevét is!

select r.currency, name, min(rate) LOW, max(rate) HIGH
FROM rates r left outer join currency c on r.currency = c.currency
WHERE month(date) = 1 and year(date)=2001
GROUP BY r.currency, name
ORDER BY r.currency

A HSQLDB egy java alapú adatbáziskezelő. Maga az adatbázis kezelő, a jdbc driver és az SQL kliens is benne van a hsqldb.jar fájlban. A kliens oldalon a org.hsqldb.jdbcDriver a driver osztály, ezt kell betölteni Javaban a használat előtt:

Class.forName("org.hsqldb.jdbcDriver" );

Elindítható In-Process módban, ekkor azonos JVM-ben fut a programmal, viszont kívülről nem lesz elérhető:

Connection c = DriverManager.getConnection("jdbc:hsqldb:file:dbname", "sa", "");

Önálló Hsqldb szerver indítása, ahol az adatokat tartalmazó fájlok dbfilename néven jönnek létre, és az adatbázis neve dbname:

java -cp hsqldb.jar org.hsqldb.Server -database.0 file:dbfilename -dbname.0 dbname

Az így elindított adatbázishoz való kapcsolódás url-je: jdbc:hsqldb:hsql://localhost/dbname

SQL kliens indítása mysql driverrel:

java -cp hsqldb.jar;mysql-connector-java-5.1.6-bin.jar org.hsqldb.util.DatabaseManagerSwing

Kapcsolódási URL a kabinetes MySQL adatbázishoz:

jdbc:mysql://home.cab.u-szeged.hu/test

CREATE TABLE táblanév
( oszlopnév adattípus [feltétel],
... ...,
oszlopnév adattípus [feltétel]
[, táblaFeltételek]
);

Ahol

• az adattípusok (rendszerenként eltérők lehetnek): CHAR(n) n hosszúságú karaktersorozat, VARCHAR(n) legfeljebb n hosszúságú karaktersorozat, INTEGER egész szám (röviden INT), REAL valós (lebegőpontos) szám, másnéven FLOAT, DECIMAL(n[,d]) n jegyű decimális, ebből d tizedes jegy, DATE dátum (év, hó, nap) TIME idő (óra, perc, másodperc)
• Feltételek (egy adott oszlopra vonatkoznak): PRIMARY KEY: elsődleges kulcs, UNIQUE: kulcs, REFERENCES tábla(oszlop) [ON-feltételek]: külső kulcs
• Táblafeltételek (az egész táblára vonatkoznak): PRIMARY KEY (oszloplista): elsődleges kulcs, UNIQUE (oszloplista): kulcs, FOREIGN KEY (oszloplista) REFERENCES tábla(oszloplista) [ON-feltételek]: külső kulcs

INSERT INTO táblanév [(oszloplista)] 
VALUES (értéklista);

UPDATE táblanév 
SET oszlop = kifejezés, ..., oszlop = kifejezés
[ WHERE feltétel ];

DELETE FROM táblanév [ WHERE feltétel ];

• Hozzuk létre a következő táblát: Tábla: nevek Oszlopok: id INT(6) PRIMARY KEY, NOT NULL, auto_increment, vezeteknev VARCHAR(20) NOT NULL, keresztnev VARCHAR(20) NOT NULL, szulev INT(4) NOT NULL
• Tábla: reszleg, Oszlopok: kod INT(6) PRIMARY KEY, NOT NULL, auto_increment, nev VARCHAR(20) NOT NULL, fonokid INT(6) NOT NULL, REFERENCES nevek(id)
• Vidd be a következő adatokat! Nagy István 1967, Kiss Ilona 1973, Frank Pál 1978, Molnár Dávid 1956, Szögi Viola 1962, Kovács József 1956, Kovács István 1959.
• Gazdasági Szögi Viola, Műszaki Kovács József, Fejlesztés Nagy István

Készítsük el egy iskolai könyvtár adatbázisát! Egyedek: Olvasó, könyv, példány, szerző, kiadó. Kapcsolatok: 1 olvasó több könyvet is kölcsönözhet, a könyvekre több olvasó is adhat le előjegyzést, egy könyvnek több szerzője is lehet, 1 könyvnek csak egy kiadója van, de több példánya is lehet. ER-diagram.

Könyvek előjegyzése, azaz mely könyvekre kik vannak előjegyezve: isbn, cím, kiad_azon, kiad_nev, varos, kiad_dat, o_azon, vnev, unev, okod, eloj_dat

Ismerkedjünk meg a Sulikönyvtár Access fájlal. Klikkeljük össze az alábbi lekérdezéseket:

• 2005-ben beíratkozott olvasók
• Átlagos könyvár
• Az átlagosnál drágább könyvek címe és szerzője.
• Hány előjegyzés van az egyes könyvekre.
• Melyik kiadótól van a legrégebbi könyvünk
• Melyik szerzőnek hány könyve van? A nulla könyvű szerők is jelenjenek meg / ne jelenjenek meg.
• Kiadónkénti könyvek száma
• Kiadónkénti átlagos könyvár
• A Gondolat kiadó átlagánál drágább könyvek listája.
• Olvasók, akik még soha nem kölcsönöztek könyvet.
• Kik milyen könyvet kölcsönöztek ki: olvasó neve, könyv szerzője és címe.
• Melyik kiadótól van a legrégebbi könyvünk?

október 20: 1. ZH. Téma: adatmodellezés, normalizálás.
november 24: 2 ZH. Téma: SQL.

A korább feladat ER diagramja.

TÁNCCSOP(cskód, csnév, pontsz, átlagéletkor, nkód)
NEMZETISÉG(nkód, nnév)
TÁNC(tkód, tnév)
ZENE(zcím, zelőadó, zműfaj, tkód)
MIT_TÁNCOL(cskód, tkód)

Ez az adatbázis háziasszonyoknak készül. Megtalálhatók benne tetszőleges ételek, azok elkészítési ideje, és egy főre jutó elkészítési költsége. Ezenkívül az egy-egy meghatározott alkalomra (pl. ebéd, desszert, uzsonna, elegáns vacsora) ajánlott ételekből is lehet válogatni. Az adatbázis tartalmazza még, hogy melyik szakácskönyvben, hányadik oldalon található meg a recept. Feltételezzük, hogy: 1) egy recept több könyvben is megtalálható, 2) egy könyvnek több szerzője is lehet, 3) az elkészítési időt a receptek közti különbségek nem befolyásolják, 4) egy ételt több alkalomra is lehet ajánlani. Az elkészítési időt, a könyv és az alkalom megnevezését kódolt formában is tároljuk. ER-diagram.

ÉTEL(név, ár_fő, elkkód)
IDŐ(elkkód, elkidő)
KÖNYV(kkód, cím)
ALKALOM(alkkód, alknév)
SZERZŐK(kkód, szerzők)
MILYEN_ALKALOM(név, alkkód)
MELYIK_SZAKÁCSKÖNYV(név, kkód, old)

• 1. normálforma: a cellákban csak egyszerű adatok lehetnek (nincsenek értékhalmazok, érték n-esek)
• 2. normálforma: minden másodlagos attribútum teljesen függ a kulcstól, azaz a kulcsból bármely attribútumot elhagyva már nem áll fenn függőség
• 3. normálforma: nincs kulcstól való tranzitív függés

Hozzuk a következő adatbázissémát 1NF-re, 2NF-re, 3NF-re: számla (számlaszám, dátum, vevőkód, vevőnév, vevőcím, árukód, árunév, egységár, mennyiség) ahol árukód, árunév, egységár, mennyiség sorokból több is lehet.
1NF: 1) válasszuk le az összetett cellát az eredeti sémából 2) vegyünk fel egy új sémát, melynek mezői a leválasztott cella mezői valamint az eredeti séma kulcsa (’új séma kulcsa’ = ‘erdeti séma kulcsa’ + ‘leválasztott cella kulcsa’)

számla (számlaszám, dátum, vevőkód, vevőnév, vevőcím)
sorok (számlaszám, árukód, árunév, egységár, mennyiség)

2NF: A számla sémában a kulcs egyelemű, így igaz, hogy a kulcsból bármely attribútumot elhagyva már nem áll fenn függőség (hiszen az üres halmaz nem határozhat meg semmit), tehát a számla séma már 2 NF-ben van. A sorok séma azonban nincs 2NF-ben, mert az árukód egyedül is meghatározza, az árunév mezőt (az egységár mezőt nem, mert az függ attól, mikor adták ki a számlát, tehát a számlaszám mezőtől is). 1) válasszuk le azokat a cellákat az eredeti sémából, melyeket a kulcs része egyértelműen meghatároz (jelen esetben az árunév mezőt) 2) vegyünk fel egy új sémát, melynek mezői a leválasztott mezők és az őket meghatározó kulcsrészlet (jelen esetben az árukód mező) (‘új séma kulcsa’ = ‘leválasztott mezőket meghatározó kulcsrészlet’)

számla (számlaszám, dátum, vevőkód, vevőnév, vevőcím)
sorok (számlaszám, árukód, egységár, mennyiség)
áruk (árukód, árunév)

3 NF: A sorok illetve az áruk sémában nincs tranzitív (többlépéses) függés, így azok már 3NFben vannak. A számla sémában a következő tranzitív függés van: a számlaszám meghatározza a vevőkód mezőt, az pedig a vevőnév illetve a vevőcím mezőket. Megoldás: 1) az eredeti sémában csak a középső (csatoló) mezőt hagyjuk meg (jelenleg: vevőkód) 2) vegyünk fel egy új sémát, melynek kulcsa a csatolómező lesz, mezői pedig a tranzitív függés második felét alkotó mezők.

számla (számlaszám, dátum, vevőkód)
vevők (vevőkód, vevőnév, vevőcím)
sorok (számlaszám, árukód, egységár, mennyiség)
áruk (árukód, árunév)

Az iskolában olvasójegyen rögzítik, hogy ki mikor mit kölcsonzött. Olvasójegy: o_azon, vnev, unev, lakcim, beir_dat, lelt_szam, kolcs_e, isbn, cím, szerzo, ar, kolcs_dat. Nurmalizáljuk!
1NF:

olvasó ( o_azon, vnév, unév, lakcím, beír_dat)
kölcsönzés (o_azon, lelt_szám, kölcs_e, isbn, cim, szerző, ar, kölcs_dat)

2NF:

olvasó ( o_azon, vnév, unév, lakcím, beír_dat)
kölcsönzés (o_azon, lelt_szám, kölcs_dat)
példány (lelt_szám, kölcs_e, isbn, cím, szerző, ár )

3NF:

olvasó ( o_azon, vnév, unév, lakcím, beír_dat)
kölcsönzés (o_azon, lelt_szám, kölcs_dat)
példány (lelt_szám, kölcs_e, ár )
könyv (isbn, cím, szerző )

Üzenőfal: A PHP forrást kell uzenofal.php néven elmenteni a szerveren. A rekordok módosítása házi feladat.

Üzenőfal program Java/Swing használatával. A rekordok módosítása házi feladat.

Egyedek átírása: az EK-modell minden egyedéhez felírunk egy relációs adatbázissémát, melynek attribútumai az egyed attribútumai, kulcsa az egyed kulcsattributumaiból áll.
Kapcsolatok átírása:

• 1:1 kapcsolat esetén kiválasztjuk a kapcsolatban résztvevő egyedek egyikét (bármelyiket) és annak relációs sémájába felvesszük új attribútumként a másik egyed kulcsattribútumait, valamint a kapcsolat attribútumait.
• 1:N kapcsolat esetén az N oldali egyed relációs sémáját bővítjük úgy, mint 1:1 kapcsolat esetén
• N:M kapcsolat esetén új relációs sémát veszünk fel, melynek attribútumai a kapcsolódó egyedek kulcsattribútumai valamint a kapcsolat saját attribútumai
• kettőnél több egyed közötti kapcsolat esetén is úgy járunk el, mint N:M kapcsolat esetén

A korábbi feladat ER diagramja. Az indulóállomásra vonatkozó 1:N kapcsolat esetén a vonat sémát bővítenünk kell. Majd tovább bővítjük a végállomásra vonatkozó 1:N kapcsolat miatt (az állomáskód mező kétszer szerepel a sémában, ezért meg kell különböztetnünk őket névlegesen).

állomás (állomáskód, állomásnév)
közbülsőállomás (állomáskód, vonatkód, indulás, érkezés)
vonat (vonatkód, indulóállomáskód, indulóidő, végállomáskód, érkezésidő)

A korább feladat ER diagramja.

áru (cikkszám, ánév)
raktár (rkód, rnév)
szállító (szkód, sznév)
vevő (vkód, vnév)
készlet (cikkszám, rkód, menny, ár)  // N:M kapcsolat átírása
eladás (cikkszám, rkód, vkód, sorszám, menny, ár)  
vásárlás (cikkszám, rkód, szkód, sorszám, menny, ár)

A korább feladat ER diagramja.

PÁCIENS(pid, név,cím, tel)
GYÓGYSZER(gynév)
BETEGSÉG(bnév)
VIZSGÁLAT(vid, dátum, pid)
MIRE_ÉRZÉKENY(pid, gynév)
MIT_KAP(bnév, gynév)
DIAGNÓZIS(vid, dátum, bnév) 

• PHP referencia, és dokumentáció
• HTML tutorial

Kapcsolódás MySQL adatbázishoz és egy tábla tartalmának kiírása:

<?php 
  $conn = mysql_connect(host, username, password); //Establish connection
  $db = mysql_select_db('test', $conn) or die ( "Unable to select database!" );

  $sql = "select * from uzenofal";
  $rs = mysql_query($sql, $conn);

  while($row = mysql_fetch_array($rs)) {
    echo $row[1]. ": ". $row[0] . "\n";
  }
?>

A MySQL adatbázis eléréséhez a Connector/J driverek szükségesek (letöltés a mysql oldaláról vagy tárolt változat az egyetemi oldalról). A drivert tartalmazó jar fájlt el kell helyezni a CLASSPATH-ban. A MySql adatbázishoz kapcsolódás URL-je: jdbc:mysql://hostname/dbname A Uzenofal.java program felépít egy kapcsolatot az adatbázissal és kiítja az uzenofal tabla tartalmat.

September 15-én kell kötelező programot választani.

Készítsünk EK-diagrammot áruk, és raktárak modellezéséhez. A cégnek sok raktára van, melyekben különböző árukat tart. Ezekről szeretne egy készletnyilvántartást. Az árukat a cikkszámmal azonosítjuk, ill. az áru nevét kell még tárolni. Tárolni kell a készleten lévő áru mennyiségét és árát. Egy féle áruból több raktárban is lehet készlet. Egy raktárban többféle áru is lehet.
Egészítsük ki a rajzot a vevők és az eladások nyilvántartásával. Rögzítsük, hogy kinek, milyen áron, mennyit, melyik raktárból, mit adtunk el.
Egészítsük ki a rajzot a beszállítók és a vásárlások nyilvántartásával. Rögzítsük, hogy kitől, milyen áron, mennyit, melyik raktárba, mit szállíttattunk be. ER-diagram.

Rajzoljuk EK diagrammot, amely a vonatok induló-, közbülső- és végállomásait modellezi (egy vonatnak csak egy indulóállomása van, de erről az állomásról több vonat is indulhat, ezért ez 1:N kapcsolat). ER-diagram.

Készítsünk EK-diagramot egy háziorvosi betegnyilvántartó redszerhez! Az orvosok elvárásainak megfelelően az adatbázisnak tartalmaznia kell a betegek személyi adatait, gyógyszerérzékenységüket, az egyes vizsgálatok időpontjait és a felírt gyógyszereket. Feltételezzük, hogy: 1) egy vizsgálaton több betegség is diagnosztizálható, 2) egy betegségre több gyógyszer is felírható, 3) egy-egy páciens több gyógyszerre is lehet érzékeny, 4) egy vizsgálatot a dátum és a vizsgálat kódja határoz meg egyértelműen. ER-diagram.

Feladat: Olyan relációs adatbázist szeretnénk létrehozni, amely egy nemzetközi tánversenyen résztvevő csoportok adatait tartalmazza. Tároljuk a csoport nevét, nemzetiségét, a csoport átlagéletkorát és a verseny folyamán elért pontszámot. E mellett tároljuk a bemutatott tánc nevét, valamint a zenére vonatkozó adatokat, azaz a zene műfaját, a szám címét és előadóját. Feltételezzük, hogy: 1) egyféle táncot több csoport is táncolhat, 2) egy csoport több táncot is előadhat, 3) a versenyen nincs kikötés a táncra vonatkozóan, így bármilyen táncot be lehet mutatni, 4) a zenét egyértelműen azonosítja a címe, azaz nincs két azonos című szám, 5) egy csoportban csak azonos nemzetiségűek táncolnak A tánc műfaja, valamint a csoportok nemzetisége csak meghatározott értékeket vehet fel. ER-diagram

Legalább 4 összefüggő táblából álló adatbázis-alkalmazás készítendő Access, MySQL, ODBC, JDBC vagy más (a gyakorlatvezetővel előre egyeztetett) rendszer segítségével. Az adatbázist fel kell tölteni demó adatokkal (összesen legalább 50 rekord). Az alkalmazás az alábbi szolgáltatásokat kell, hogy nyújtsa:

• Adatok aktualizálása (új felvitel, módosítás, törlés) űrlap segítségével.
• Legalább három nemtriviális lekérdezés. (Access esetén nemtriviális egy lekérdezés, ha kattintgatással nem, csak SQL-ben állítható elő.)
• Integritás ellenőrzés (kulcs feltételek)

Az alkalmazást a hallgató személyesen mutatja be a gyakorlatvezetőnek, aki annak eredetiségét is ellenőrzi (szakmai részletekre való rákérdezéssel).

Az adatbázis-alkalmazásról 2-5 oldalas írott anyagot kell készíteni. A dokumentáció tartalmazza: - Egyed-kapcsolat modell, - relációs modell, - az alkalmazás szolgáltatásai, - megvalósítás (fejlesztő eszköz, nemtriviális megoldások stb.). A dokumentáció számítógépen szerkesztett legyen (E-K diagram is), kézzel írott dokumentáció nem fogadható el.

• Moodle (felhasználók, kérdések, válaszlehetőségek, válaszok)
• családfa (személyek, gyermekek, események, házasságok)
• sporteredmények (csapatok, mérkőzések, eredmények)
• publikációk (Közlemények, szerzők, folyóiratok)
• egyetemi oktatás (kurzusok, szakok, termek, hallgatók, oktatók, órarend)
• vállalat (főosztályok, osztályok, dolgozók, fizetések)
• raktár (áruk, készlet, mozgások)
• könyvtár (olvasók, könyvek, kiadók, szerzők)
• lakás (helyiségek, bútorok, elektromos szerelvények)
• munkahely (helyiségek, számítógépek, telefonok, dolgozók)
• városi tömegközlekedés (buszjáratok, megállók, menetrend)
• pizzeria (pizzák, ügyfelek, rendelések)
• bank (folyószámlák, ügyfelek, átutalások)
• ingatlan (telkek, épületek, tulajdonosok)
• vasúti, légi helyfoglalás (járatok, helyek, ügyfelek)