Okosotthon
Kategória: SQL emelt szint
Feladat leírása
Okosotthon Rendszer Adatbázis
A modern lakberendezés és épületgépészet népszerű megoldásai az okosotthon-rendszerek, amelyek kényelmesebbé és energiatakarékosabbá teszik mindennapjainkat. Egy családi ház komplett felügyeleti rendszerének adatbázisát kell kezelned, amely tárolja a ház helyiségeit, a beépített eszközöket, a jogosult felhasználókat, valamint a szenzorok és vezérlők által rögzített eseménynaplót.
Az adatbázisban nyilvántartjuk, hogy melyik eszköz hol található, ki használhatja a rendszert, és részletes naplót vezetünk minden aktivitásról. A rendszer célja, hogy visszakövethető legyen a hőmérséklet változása, a világítás használata, vagy éppen az, hogy ki és mikor nyitotta ki a garázskaput.
Adattáblák szerkezete
helyisegek (id, nev, emelet, alapterulet)
id: A helyiség egyedi azonosítója (szám), ez az elsődleges kulcs.nev: A szoba vagy terület elnevezése (szöveg, pl. Nappali, Konyha).emelet: Az emelet száma, ahol a helyiség található (szám). A 0 a földszintet jelöli, a negatív érték a pincét.alapterulet: A helyiség mérete négyzetméterben (szám).
felhasznalok (id, teljes_nev, szuletesi_ev, jogkor)
id: A felhasználó egyedi azonosítója (szám), ez az elsődleges kulcs.teljes_nev: A rendszert használó személy neve (szöveg).szuletesi_ev: A felhasználó születési éve (szám).jogkor: A felhasználó jogosultsági szintje (szöveg, pl. admin, csaladtag, vendeg).
eszkozok (id, nev, tipus, helyiseg_id, gyarto)
id: Az eszköz egyedi azonosítója (szám), ez az elsődleges kulcs.nev: Az eszköz pontos megnevezése (szöveg).tipus: Az eszköz funkcionális kategóriája (szöveg, pl. világítás, termosztát, zár).helyiseg_id: Annak a helyiségnek az azonosítója, ahol az eszköz fel van szerelve (szám), idegen kulcs ahelyisegektáblára.gyarto: Az eszközt gyártó cég neve (szöveg).
naplo (id, eszkoz_id, felhasznalo_id, ertek, rogzites_ideje)
id: A bejegyzés egyedi sorszáma (szám), ez az elsődleges kulcs.eszkoz_id: Az érintett eszköz azonosítója (szám), idegen kulcs azeszkozoktáblára.felhasznalo_id: Az eseményt kezdeményező személy azonosítója (szám), idegen kulcs afelhasznaloktáblára. Ha az esemény automatikus (pl. hőmérő mérés), akkor ez a mezőNULLértékű.ertek: A rögzített adat vagy állapot (lebegőpontos szám). Ez lehet hőmérséklet (°C) vagy logikai érték (pl. 1 = bekapcsolva/nyitva, 0 = kikapcsolva/zárva).rogzites_ideje: Az esemény pontos dátuma és időpontja (datetime).
Példa: A naplo tábla egy rekordja például: (3, 19, 100, 1, '2023-12-24 14:00:00').
Ez azt jelenti, hogy a 3-as sorszámú eseménykor a 19-es azonosítójú eszközt (az eszkozok tábla alapján: Garázskapu) a 100-as kódú felhasználó (a felhasznalok tábla alapján: Kovács Péter) aktiválta (kinyitotta) 2023. december 24-én délután 2 órakor.
Technikai elvárások
A következő feladatokat megoldó SQL-parancsokat rögzítsd a feladatok végén zárójelben megadott nevű és .sql kiterjesztésű szöveges állományban! (Például az 1. feladat megoldását az 1eszkozok.sql nevű állományban).
A javítás során csak ezeknek az állományoknak a tartalma lesz értékelve! Ügyelj arra, hogy a lekérdezésekben pontosan a kívánt mezők szerepeljenek, felesleges mezőt ne jeleníts meg!
Részfeladatok
1. Listázd ki azokat az eszközöket (név, gyártó), amelyek 'Xiaomi' gyártmányúak VAGY a típusuk 'zár'! (1eszkozok.sql)
2. Mely helyiségek vannak az emeleten (emelet > 0)? Listázd a nevüket, és rendezd őket alapterület szerint csökkenő sorrendbe! (2emelet.sql)
3. Hány különböző típusú eszköz található a rendszerben? Jelenítsd meg a darabszámot! (3tipusok.sql)
4. Írasd ki a naplóból azoknak a bejegyzéseknek minden adatát (összes oszlop), ahol a mért érték 20 és 25 közé esik (határokat is beleértve), és az esemény 2024-ben történt. (4naplo.sql)
5. Listázd ki a 'Kovács' nevű felhasználók teljes nevét és születési évét! (A név bárhol tartalmazhatja a Kovács szót). (5kovacs.sql)
6. Melyek azok a naplóbejegyzések (csak az 'id' és 'rogzites_ideje' oszlopok kellenek), amelyeket NEM felhasználó kezdeményezett (automata/szenzor adat)? (6automata.sql)
7. Listázd ki a 'Nappali' nevű helyiségben található összes eszköz nevét és típusát! (7nappali.sql)
8. Számold ki az eszközök átlagos értékét típusonként csoportosítva! Az oszlop neve legyen `atlag_ertek`. (8atlag.sql)
9. Keresd meg a legutolsó naplóbejegyzés pontos dátumát és időpontját a rendszerben! (9utolso.sql)
10. Mely napokon történt aktivitás? Listázd a dátumokat (csak a nap sorszámát) úgy, hogy minden nap csak egyszer szerepeljen, és csak a januári hónap (hó=1) napjait mutassa! (10januar.sql)
11. Melyek azok az eszközök (elég a nevüket kiírni), amelyeknél a naplóban rögzített értékek maximuma meghaladja a 30-at? (11maxertek.sql)
12. Listázd ki azoknak a felhasználóknak a teljes nevét, akiknek az utolsó aktivitása óta már eltelt több mint 30 nap (a referencia dátum legyen '2025-02-01'). (12inaktiv.sql)
13. Mely helyiségekben (csak a nevük kell) NINCS egyáltalán felszerelve semmilyen eszköz? (13ures.sql)
14. Keress olyan eseteket, amikor ugyanabban a helyiségben két különböző típusú eszköz is található! Listázd ki a helyiség nevét és a két eszköz típusát páronként. (Ügyelj rá, hogy egy pár csak egyszer szerepeljen). (14kulonbozo.sql)
15. Melyek azok a helyiségek (nevük), amelyek alapterülete nagyobb, mint az összes helyiség átlagos alapterülete? (15atlagonfeluli.sql)
16. Készíts listát a felhasználók aktivitásáról! Jelenítsd meg a teljes nevüket és egy `aktivitas` nevű új oszlopot: ha a felhasználónak több mint 10 bejegyzése van a naplóban, írd ki: 'Aktív', egyébként 'Átlagos'. (16minosites.sql)
17. Listázd ki azon felhasználók teljes nevét, akiknek már volt dolguk 'Xiaomi' gyártmányú eszközzel! (17xiaomihasznalok.sql)
18. Melyek azok az eszközök (nevük), amelyek átlagos mért értéke magasabb, mint az összes naplóbejegyzés globális átlaga? (18atlagfelett.sql)
19. Listázd azokat a helyiségeket (nevüket), ahol legalább 3 darab 'Xiaomi' gyártmányú eszköz van felszerelve. (19xiaomi.sql)
20. Számold ki, hogy átlagosan hány bejegyzés tartozik egy eszközhöz! (20eszkozatlag.sql)
