Adatbázisok gyakorlat 2009

10. gyakorlat: SQL feladatok

SQL lekérdezések

9. gyakorlat: SQL - select

Oldjuk meg az alábbi feladatokat! Ellenőrizzük a megoldásokat az sqlclient.php segítségével.
Valuták, árfolyamok
Tekintsük a következő sémákat
rates (day, currency, rate)
currency (curency, name)
  • Listázzuk ki a currency, és a rates táblákat!
  • Listázzuk ki az összes sort a currency táblából, ahol 'CHF' a pénznem!
  • Listázzuk ki az összes sort ahol a pénznem 'CHF' vagy 'USD'!
  • Listázzuk ki a 2009. jan. 2-i árfolyamokat! Listázzuk ki a 2009 jan. 2-a és 5-e közötti árfolyamokat!
  • Listázzuk ki minden hónap 15-jére eső árfolyalot! Csak a márciusi árfolyamokat! Csak a 2009-es árfolyamokat!
  • Mely pénznemek szerepelnek az rates táblában?
  • Adjuk meg az átlagos árfolyamot pénznemenként!
  • Adjuk meg a 2009 januári átlagos árfolyamot pénznemenkénti bontásban!
  • Adjuk meg a 2009 januári árfolyamok közül a legkisebb és a legnagyobb értékeket pénznemenként, kiírva a pénznem nevét is!
  • Készítsünk listát: valuta neve, kódja, hány bejegyzés szerepel róla a rates táblában.
  • Amelyek nullaszor szerepelnek, azok is kerüljenek bele az előző listába!
Oktatók és kurzusok
oktatok (okod, nev)
kurzusok (kkod, nev)
tartja( kkod, okod )
  • Listázzuk ki az oktatókat!
  • Számoljuk meg az oktatókat!
  • Listázzuk ki a 'Balázs' keresztnevű oktatókat!
  • Ki tartja az Adatbázisok kurzust?
  • Töröljük a K betűvel kezdődő nevű oktatókat!
  • Melyik oktatónak hány kurzusa van? Rendezzük kurzusszám szerint csökkenő sorredbe!
  • Melyik kurzust hány oktató oktatja?
  • Listázzuk ki azokat az oktatókat, akikhez nem tartozik kurzus az adatbázisunk szerint! Töröljük is ki őket.

8. gyakorlat: SQL - create table, insert into, update, delete

CREATE TABLE
CREATE TABLE [IF NOT EXISTS] táblanév
( oszlopnév adattípus [feltétel],
  ...,
  [, táblaFeltételek]
);
Ahol
  • az adattípusok : CHAR(n), VARCHAR(n), INTEGER, DECIMAL(n[,d]), DATE, TIME
  • Feltételek (egy adott oszlopra vonatkoznak): PRIMARY KEY, UNIQUE, REFERENCES tábla(oszlop) [ON-feltételek]
  • Táblafeltételek (az egész táblára vonatkoznak): PRIMARY KEY (oszloplista), UNIQUE (oszloplista), FOREIGN KEY (oszloplista), REFERENCES tábla(oszloplista) [ON-feltételek]
INSERT, UPDATE, DELETE
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 ];
Feladatok
  • Készítsünk felhasználói felületet, amely végrehajt egy SQL utasítást!
    Megoldás PHP-ban: sqlclient.php és forrása. Megoldás Java-ban: SqlClient.java. Módosítsuk a Java programot úgy, hogy ne csak select, hanem adatmódosító utasítást is végre tudjon hajtani.
  • Hozzuk létre az oktatok táblát a következő oszlopokkal: oid, nev, szulev, tanszek, fizetes. Vigyünk fel oktatókat! Töröljünk oktatókat!
  • Hozzuk létre a fibon táblát, két oszloppal: n integer, fib integer. Töltsük fel a fib oszlopot a Fibonacci sorozat első 40 elemével.
    Megoldás PHP-ban: fibonacci.php és forrása. Megoldás Java-ban: Fibonacci.java. Extreme edition: Számoljuk ki a Fibonacci sorozat első 50 elemét!
  • Írjunk egy programot, amely létrehoz egy táblát, és feltölti az 5000-nél kisebb prím számokkal. Megoldás Java-ban: Primes.java
Segítség a kötprogihoz
Kiindulási alap: HTML + PHP + CSS oldal menüvel és bejelentkezéssel (login.zip)

6. Gyakorlat

Palatinus Endre diái: Gyakorlás a ZH-ra.

5. Gyakorlat - Normalizálás, JDBC

Normalizálás
  • 1. normálforma: Egy reláció első normálformájú, ha az értelmezési tartományának egyetlen eleme sem reláció, azaz ha a táblázat minden cellájában csak egy attribútumérték szerepel. 1NF-re hozás: A reláció újabb relációkra bontható úgy, hogy az ismétlődő csoportot leválasztjuk az eredeti relációról, melléjük illesztve a nem ismétlődő rész kulcsát.
  • 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. 2NF-re hozás: 1) Ha 1NF, és a kulcs egyszerű, akkor a reláció egyben 2NF. 2) Egyébként az összetett kulcsú relációban meg kell vizsgálni azokat az attribútumokat, amelyek nem részei a kulcsnak. Ha ezek között az ún. másodlagos attribútumok között vannak olyanok, amelyek nem függnek teljesen funkcionálisan a kulcstól, akkor meg kell határozni, hogy ezek a tulajdonságok mely részkulcstól függnek teljesen, és a tulajdonságokat a részkulccsal együtt külön táblázatba kell tenni úgy, hogy ott a részkulcs már kulcs legyen.
  • 3. normálforma: nincs kulcstól való tranzitív függés. 3NF-re hozás: A tranzitív függőségeket úgy tüntetjük el, hogy azokat külön táblázatba vagy táblázatokba tesszük.
Számla normalizálása
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)
Suli könyvtár
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. Normalizá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, isbn, kölcs_e, ár )
könyv (isbn, cím, szerző )
JDBC - MySQL
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

4. Gyakorlat - PHP, MySQL

PHP - MySQL

3. Gyakorlat - E-K modellből relációs adatbázisséma

Relációs adatbázisséma készítése EK-modellből
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
Palatinus Endre diái
Állomás, vonatok
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ő)
Áru, raktár, stb
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)
Háziorvosi betegnyilvántartás
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) 

2. Gyakorlat

Feladat - Raktárbázis
Készítsünk el az ACME vállalat áruit, és raktárait modellező EK-diagrammot. 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.
Feladat - Vonatok
Készítsen E-K modellt, ami vonatok induló-, közbülső- és végállomásait modellezi. Egy vonatnak csak egy induló- és érkező állomása van, de erről az állomásról több vonat is indulhat vagy érkezhet.
Feladat - Háziorvosi betegnyilvántartás
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.
Feladat - Nemzetközi táncverseny
Feladat: Olyan relációs adatbázist szeretnénk létrehozni, amely a Kék Osztriga 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.
Feladat - Fuvarozás
A Tehertraktor Kft. nyilván szeretné tartani a gépkocsi-parkját, a megrendelőit, a fuvarjait, valamint a kiszállított árucikkeket. Az adatbázisban a következő információkat kell eltárolni:
Gépkocsi: típus, hengerűrtartalom, fogyasztás, raktér, rendszám.
Megrendelő: név, cím.
Szállítás: szállítás dátuma, sikeres, célcím.
Áru: áru kódja, áru megnevezése, áru méretei.
Minden esetben a megrendelő bízza meg a szállítmányozó céget egy szállítással. Egy megrendelő több árut is szállíttathat egy alkalommal. Tervezze meg az adatbázis E-K modelljét! Jelölje a kulcsokat!
Feladat - Fórum
Tervezzen adatbázist egy internetes fórumhoz! A fórumot bárki megnézheti, de csak regisztrált felhasználók írhatnak be üzenetet/véleményt. Nyilván kell tartani a látogatókat (statisztika miatt), a felhasználókat, és az üzeneteket. Statisztikai szempontból fontos lehet, hogy vannak-e visszajáró látogatók, illetve, hogy egy látogató mikor nézte meg az oldalt. A fórumra több témakörben írhatnak a felhasználók, ezért egy üzenethez el kell tárolni, hogy mely témakör(ök)be tartozik.
Látogatók: ip cím, dátum
Felhasználók: felhasználó név, jelszó
Üzenetek: üzenet tartalma, ki írta, mikor írta, milyen témakör(ök)be tartozik
Témakörök: témakörök megnevezése
Feladat - Madárlesen
Az Országos Ornitológiai Társaság figyeli a térségben elrepülő költöző madarakat. A meggyűrűzött példányokat nyilvántartják. Feljegyzik a madár fajtáját, állatrendszertani besorolását, ki gyűrűzte meg, hol és mikor, valamint, hogy ki látta, mikor és hol.
Madár: gyűrű száma, rendszertani besorolás (faj, osztály, alosztály, rend), ki gyűrűzte meg, hol történt a gyűrűzés
Szövetségi tagok: név, lakcím, tagsági kártya száma, mikor lett tag, meddig tag
Feljegyzések: melyik madarat látta, ki látta, mikor látta, hol látta
Feladat - Szakácskönyv
Online szakácskönyvhöz készíts adatbázis tervet! A szakácskönyvben el kell tárolni az ételek nevét, elkészítésének szöveges leírását, az elkészítési időt, fényképet, valamint a hozzávalókat. A szakácskönyv olyan szempontból interaktív, hogy regisztrált felhasználók is tölthetnek fel receptet. Rajzolja le a szakácskönyv E-K diagramját!
Feladat - WiWiArckönyv
Tervezzen egy (egyszerűsített) adatbázist közösségi oldalhoz! Az adatbázisban el kell tárolni a regisztrált felhasználókat, azok adatait, ismerőseit, üzeneteit, feltöltött fényképeit, belépéseinek időpontját. A felhasználók küldhetnek személyes üzenetet vagy írhatnak egy üzenőfalra is. Megjegyzéseket fűzhetnek egymás fényképeihez is. Ügyelni kell arra, hogy lehet, hogy két fényképnek ugyanaz a fájlneve, ezért el kell tárolni azt is, hogy ki töltötte fel és mikor. A képekhez a képek tulajdonosa megjegyzéseket fűzhet, ami a kép alatt jelenik meg.
Felhasználók: név, születési év, foglalkozás, elérhetőség
Fényképek: fájlnév, feltöltés időpontja, ki töltötte fel, megjegyzés a képhez
Hozzászólások: fénykép, ki írt hozzászólást
Üzenetek: üzenet szövege, címzett(ek)

1. Gyakorlat

Kurzus

Tematika
  1. Kurzusfelvétel, eligazítás, E-K modell
  2. E-K modell, kötelező feladat kiválasztása
  3. PHP alapok, PHP-MySQL, JDBC-MySQL
  4. E-K modellből relációs adatbázisséma
  5. Normalizálás
  6. Normalizálás
  7. 1. ZH: adatmodellezés, normalizálás. Első dokumentáció leadása.
  8. Őszi szünet
  9. SQL: create table, insert, update, delete
  10. SQL: select
  11. SQL gyakorlas
  12. 2. ZH: SQL. Kötelező feladatok bemutatása. Második dokumentáció leadása.
  13. Kötelező feladatok bemutatása, javító ZH
  14. Kötelező feladatok bemutatása
Gyakorlat pontozása
  • 1. Dokumentáció: 3p. Papíron kell leadni azon az órán amikor a 1. ZH van. Csak számítógéppel szerkesztett változat fogadható el. Tartalmaznia kell:
    • Röviden (pár sorban) a megvalósítani kívánt feladatot
    • EK-diagramot: egyedeket és azok tulajdonságait, jelölje a kulcsokat, kapcsolatokat, és azok tulajdonságait, a kapcsolatok típusát (szöveges indoklással!), esetleges gyenge egyedeket, meghatározó kapcsolatokat.
    • A diagramhoz tartozó relációs adatbázissémát
    • Normalizálást és az egyes lépéseit
    • Tervezett megvalósítási környezet
  • 1. ZH: 12p. Adatmodellezés, normalizálás
  • 2. Dokumentáció: 3p Papíron kell leadni azon az órán amikor a 2. ZH van. Csak számítógéppel szerkesztett változat fogadható el. Tartalmaznia kell:
    • Javítások az 1.-höz képest
    • 3 nemtriviális lekérdezés leírása
    • Az alkalmazás szolgáltatásai
    • Megvalósítási környezet leírása
  • 2. ZH: 8p. SQL
  • Adatbázis-alkalmazás: 14p. A programot személyesen kell számítógép előtt bemutatni az utolsó órák egyikén.
    • Minimum 4 tábla kapcsolatokkal (3p, Access esetén 1p) és integritásellenőrzéssel (2p, Access esetén 1p)
    • Minimum 50 rekord (1p)
    • Legalább 2 táblához megtalálható mind a 4 művelet: select, insert, update, delete (3p, Access esetén 0p)
    • Három nemtriviális lekérdezés (2p)
    • További max. 4 pont adható az alábbiakra: Nem varázslóval előállított űrlapok, Négynél több tábla, Nagyobb mennyiségű, feltöltött valós adat, Diagramok, képek használata, Visual Basic programmodulok

Kötelező feladat

Választható feladatok
Egy feladatot csak egy hallgató választhat. A felsorolt fogalmakat mindenképpen be kell építeni a programba.
  1. moodle (teszt, kérdések, válaszlehetőségek, válaszok)
  2. családfa (személyek, kapcsolatok, események, házasságok)
  3. csapatsportok (csapatok, versenyzők, mérkőzések, pontszerzők/góllövők)
  4. egyéni sportok (bajnokságok, versenyzők, résztvevők, mérkőzések)
  5. imdb (filmek, színészek, szereplések, filmstúdiók)
  6. műsorújság (tv csatornák, műsorok, szereplők, időpontok)
  7. publikációk (közlemények, szerzők, folyóiratok, kiadók)
  8. etr (oktatók, kurzusok, termek, hallgatók)
  9. bugzilla (hibajelentések, fejlesztők, szerepkörök, javítások)
  10. szavazatszámláló (szavazások/fordulók, jelöltek, szavazók, szavazatok)
  11. vállalat (részlegek, osztályok, dolgozók, fizetések)
  12. raktár (áruk, készlet, mozgások)
  13. könyvtár (olvasók, könyvek, kiadók, szerzők)
  14. órarend (osztályok, tantárgyak, termek, órarend)
  15. munkahely (helyiségek, számítógépek, telefonok, dolgozók)
  16. városi tömegközlekedés (buszjáratok, megállók, menetrend)
  17. pizzeria (pizzák, ügyfelek, rendelések, feltétek)
  18. bank (folyószámlák, ügyfelek, átutalások)
  19. ingatlan (telkek, épületek, tulajdonosok)
  20. busz, vasút, repülő helyfoglalás (járatok, városok, ügyfelek, foglalás)
  21. gyakorlatvezetővel előre egyeztetett saját téma