Informatika | Tanulmányok, esszék » Fábián Zoltán - A számítógépházak útja a gyártótól a felhasználóig

Fábián Zoltán Szoftvertechnológia II. A számítógép-házak útja a gyártótól a felhasználóig (Házi feladat) 2004. április 26 1. A Feladat szöveges specifikálása 3 2. Egyed – Kapcsolat Modell 4 3. 1 Relációs adatbázis sémák 5 3. 1 1 Táblák 5 3. 1 2 Kapcsolatok 5 3. 2 A táblákat létrehozó SQL utasítások (InterBase SQL) 6 3. 2 1 Táblák 6 3. 2 2 Kapcsolatok 7 3. 2 3 Tábla-előfordulások 8 3. 2 3 A táblák feltöltésének SQL utasításai 11 3. 2 4 A kapcsolatok feltöltésének SQL utasításai 12 4. Normalizáció 13 4. 1 A táblákban érvényes függőségek 13 5. Statisztikai jellegű SQL Lekérdezések 14 5. 1 Egytáblás lekérdezések 14 5. 1 1 Mely AlkatrészGyártók készítenek műanyagból alkatrészeket? Mennyit és milyet? 14 5. 1 2 Melyek azok az összeszerelő üzemek, amelyek összeszerelt termékmennyiség (kibocsátás) tekintetében meghaladják az átlagot? . 14 5. 1 3 Melyek azok a nyersanyag-rendelések,

amelyek 2004 februárjában voltak aktuálisak?. 14 5. 1 4 Mely tranzakció tartalmazta a legnagyobb mennyiséget az Alkatrész-rendelések közül, és mire vonatkozóan, illetve mikor? . 15 5. 1 5 Átlagosan hány terméket tárolnak jelenleg a magyarországi szervizekben (Hungary)? . 15 5. 2 Többtáblás lekérdezések 15 5. 2 1 Mennyi selejtes terméket szállított legutóbb az Újrahasznosítóba a Mátészalkai ÖÜzem? . 15 5. 2 2 Adjuk meg, hogy az adott importőr-kódokhoz mennyi hibás termék visszaszállítása tartozik összesen az Összeszerelő üzembe?. 15 5. 2 3 Melyek azok a szervizek, amelyek már szállítottak vissza nem javítható termékeket az importőrüknek? . 16 5. 2 4 Adjuk meg, hogy a Nagykereskedések mely Összeszerelő Üzemből kapják termékeiket! . 16 5. 2 5 Mely Kiskereskedések nem küldtek még egyetlen szerviznek sem rossz termékeket javításra? Adjuk meg Kódot, és rendezzük az eredményt Név szerint! . 16 1. A Feladat szöveges

specifikálása A feladat célja a számítógép-házak útjának nyomon követése a gyártótól a felhasználóig, beleértve az importálást, értékesítést, szervizelést és az újrahasznosítást is. Mivel a rendszer teljes körforgásban van így nem ragadható ki egyetlen egyértelmű kiindulópont sem, azonban a példa kedvéért ezt megkíséreljük. A számítógép-házakhoz szükséges alkatrészeket legyártják a különböző alkatrészgyártók, amelyhez a szükséges nyersanyagokat az újrahasznosító üzemektől szerzik be. A gyártási folyamat során keletkező szerelési egységeket, például takaróléceket, műanyag előlapokat, stb. Az elkészített alkatrészekből az összeszerelő üzemek adott mennyiséget rendelnek, amelyekből előáll a késztermék. A selejtaránynak megfelelően valamennyi használhatatlan termék is keletkezik, amelyet az újrahasznosító üzemekbe szállítanak, hogy ott szétszereljék, majd újra nyersanyagot állítsanak

elő belőlük. Az importőrök adott mennyiségű számítógép-házat importálnak, amelyekről az importálás pillanatában feltételezzük, hogy megfelelően működnek (ennek később lesz jelentősége). A folyamat további részében a nagykereskedések az importőrnek adják le rendeléseiket, a kiskereskedések pedig a nagykereskedésektől vásárolhatnak termékeket. A kiskereskedésekben kiderülhet, hogy egy termék rossz: ekkor elküldik valamelyik szervizbe, ahol kiderítik, hogy a termék javítható-e. Amennyiben igen, megjavítják, majd visszaküldik az adott kiskereskedésbe. Ha nem javítható, akkor visszaküldik az importőrhöz, amely egy meghatározott termékmennyiség esetén visszaszállítja azokat a megadott összeszerelő üzembe, ahonnan az esedékes selejt-szállításkor a hibás termék eljut az újrahasznosítóba, s a belőle képzett nyersanyag ismét egy új termék alapja lehet. 2. Egyed – Kapcsolat Modell NyFajta GyNév GyKód Afajta

ÚKód Rmenny Únév NyFajta Menny Dátum GyMenny SztDb Nyersanyagrendelés Alkatrészgyártók Újrahasznosítók NyFajta Rmenny SzMenny Afajta Alkatrészrendelések Selejt-szállítás RosszDb ÖÜKód JóDb ÖÜNév Kibocs Összeszerelõ üzemek Db Hibás termékek visszaszállítása Rmenny JóDb IÖNév RosszDb IÖKód Termék -Importálás Importõrök Rmenny Termékrendelések Nkód Nnév db Nagykereskedések Rmenny Vásárlások Kkód db Knév Kiskereskedések db db db Rossz termékek Megjavított termékek Nem javítható termékek Szervizek SZkód SZnév db 3. Relációs modell 3. 1 Relációs adatbázis sémák 3. 1 1 Táblák AlkatrészGyártók (GyKód, GyNév, AlkatrészFajta, GyártottMennyiség) Újrahasznosítók (Úkód, Únév, NyersanyagFajta, mennyiség, SzétszerelendőTermékDb) Összeszerelő Üzemek (ÖÜkód, ÖÜnév, Kibocsátás, Jódb, RosszDb) Importőrök (IÖkód, IÖnév, JóDb, RosszDb) Nagykereskedések

(Nkód, Nnév, db) Kiskereskedések (Kkód, Knév, JóDb, RosszDb) Szervizek (SzKód, SzNév, db) 3. 1 2 Kapcsolatok Nyersanyag-rendelések (GyKód, Úkód, NyersanyagFajta, RendelésMennyiség, Dátum) Alkatrész-rendelések (GyKód, ÖÜkód, AlkatrészFajta, RendelésMennyiség, Dátum) Selejt-szállítás (ÖÜkód, Úkód, NyersanyagFajta, SzMennyiség, Dátum) Termék-rendelések (IÖkód, Nkód, RendelésMennyiség, Dátum) Vásárlások (Nkód, Kkód, RendelésMennyiség, Dátum) Termék-importálás (IÖkód, ÖÜkód, RendelésMennyiség, Dátum) Hibás termékek visszaszállítása (IÖkód, ÖÜkód, db, Dátum) Rossz-termékek (Szkód, Kkód, db, Dátum) Nem javítható termékek (SzKód, IÖkód, db, Dátum) Megjavított Termékek (Szkód, Kkód, db, Dátum) 3. 2 A táblákat létrehozó SQL utasítások (InterBase SQL) 3. 2 1 Táblák CREATE TABLE alkgyartok ( GyKod NUMERIC(4,0) NOT NULL, GyNev CHAR(30) NOT NULL, AlkFajta CHAR(10) NOT NULL, GyMenny NUMERIC(4,0),

PRIMARY KEY (GyKod) ); CREATE TABLE uhasznositok ( UKod NUMERIC(4,0) NOT NULL, UNev CHAR(30) NOT NULL, NyFajta CHAR(10) NOT NULL, Menny NUMERIC(4,0), SztDb NUMERIC(4,0), PRIMARY KEY (UKod) ); CREATE TABLE ouzemek ( OUKod NUMERIC(4,0) NOT NULL, OUNev CHAR(30) NOT NULL, kibocsatas NUMERIC(4,0) NOT NULL, JoDb NUMERIC(4,0), RosszDb NUMERIC(4,0), PRIMARY KEY (OUKod) ); CREATE TABLE importorok ( IOKod NUMERIC(4,0) NOT NULL, IONev CHAR(30) NOT NULL, JoDb NUMERIC(4,0), RosszDb NUMERIC(4,0), PRIMARY KEY (IOKod) ); CREATE TABLE nagykerek ( NKod NUMERIC(4,0) NOT NULL, NNev CHAR(30) NOT NULL, db NUMERIC(4,0), PRIMARY KEY (NKod) ); CREATE TABLE kiskerek ( KKod NUMERIC(4,0) NOT NULL, KNev CHAR(30) NOT NULL, Jodb NUMERIC(4,0), Rosszdb NUMERIC(4,0), PRIMARY KEY (KKod) ); CREATE TABLE szervizek ( SzKod NUMERIC(4,0) NOT NULL, SzNev CHAR(30) NOT NULL, db NUMERIC(4,0), PRIMARY KEY (SzKod) ); 3. 2 2 Kapcsolatok CREATE TABLE nyarendelesek ( GyKod NUMERIC(4,0) NOT NULL, UKod NUMERIC(4,0) NOT NULL,

NyFajta CHAR(10) NOT NULL, RendMenny NUMERIC(4,0) NOT NULL, datum DATE NOT NULL, PRIMARY KEY (GyKod, Ukod, datum) ); CREATE TABLE alkrendelesek ( GyKod NUMERIC(4,0) NOT NULL, OUKod NUMERIC(4,0) NOT NULL, AlkFajta CHAR(10) NOT NULL, RendMenny NUMERIC(4,0) NOT NULL, datum DATE NOT NULL, PRIMARY KEY (GyKod,OUkod, datum) ); CREATE TABLE selejtszall ( UKod NUMERIC(4,0) NOT NULL, OUKod NUMERIC(4,0) NOT NULL, NyFajta CHAR(10) NOT NULL, SzMenny NUMERIC(4,0) NOT NULL, datum DATE NOT NULL, PRIMARY KEY (OUKod,Ukod, datum) ); CREATE TABLE termekrend ( IOKod NUMERIC(4,0) NOT NULL, NKod NUMERIC(4,0) NOT NULL, db NUMERIC(4,0) NOT NULL, datum DATE NOT NULL, PRIMARY KEY (IOKod,Nkod, datum) ); CREATE TABLE vasarlasok ( NKod NUMERIC(4,0) NOT NULL, KKod NUMERIC(4,0) NOT NULL, db NUMERIC(4,0) NOT NULL, datum DATE NOT NULL, PRIMARY KEY (NKod,Kkod, datum) ); CREATE TABLE termekimport ( IOKod NUMERIC(4,0) NOT NULL, OUKod NUMERIC(4,0) NOT NULL, db NUMERIC(4,0) NOT NULL, datum DATE NOT NULL, PRIMARY KEY

(IOKod,OUkod, datum) ); CREATE TABLE hibastermek ( IOKod NUMERIC(4,0) NOT NULL, OUKod NUMERIC(4,0) NOT NULL, db NUMERIC(4,0) NOT NULL, datum DATE NOT NULL, PRIMARY KEY (IOKod,OUkod, datum) ); CREATE TABLE rossztermekek ( SzKod NUMERIC(4,0) NOT NULL, KKod NUMERIC(4,0) NOT NULL, db NUMERIC(4,0) NOT NULL, datum DATE NOT NULL, PRIMARY KEY (SzKod,Kkod, datum) ); CREATE TABLE nemjavtermekek ( SzKod NUMERIC(4,0) NOT NULL, IOKod NUMERIC(4,0) NOT NULL, db NUMERIC(4,0) NOT NULL, datum DATE NOT NULL, PRIMARY KEY (SzKod,IOkod, datum) ); CREATE TABLE megjavitotttermekek ( SzKod NUMERIC(4,0) NOT NULL, KKod NUMERIC(4,0) NOT NULL, db NUMERIC(4,0) NOT NULL, datum DATE NOT NULL, PRIMARY KEY (SzKod,Kkod, datum) ); 3. 2 3 Tábla-előfordulások AlkatrészGyártók GyKód GyNév 1101 ElőlapGyártó 1105 TakaróLemezGyártó AlkFajta műanyag alumínium GyMenny 150 270 Újrahasznosítók Úkód Únév 2205 Műanyagozó Üzem 2207 Kohászat NyFajta műanyag alumínium Menny 110 220 SztDb

Kibocsátás 350 440 JóDb 250 180 RosszDb 20 10 15 12 Összeszerelő Üzemek ÖÜKód ÖÜNév 3304 Mátészalkai Öüzem 3306 Budapesti Öüzem Importőrök IÖKód IÖNév 4407 Juhhéj Import 4409 Kovász Import JóDb RosszDb 110 11 234 34 Nagykerek Nkód Nnév 5506 Fő Nagyker 5507 Bpi Nagyker db 230 456 Kiskerek Kkód Knév 6603 Bluefish 6605 Aqua JóDb RosszDb 430 21 320 23 Szervizek SzKód SzNév 7703 Pulsar Hungary 7704 SoCo Hungary db 12 32 Nyersanyag-rendelések GyKód 1101 1105 Úkód NyaFajta 2205 műanyag 2207 alumínium RendMenny Dátum 50 2004.0220 70 2004.0222 ÖÜKód AlkFajta 3304 műanyag 3306 alumínium Rmenny Dátum 25 2004.0315 23 2004.0422 Úkód NyaFajta 2205 műanyag 3304 műanyag 2207 alumínium SzMenny Dátum 50 2004.0123 72 2004.0430 56 2004.0419 Nkód Rmenny Dátum 50 2004.0317 32 2004.0410 Rmenny Dátum 19 2004.0512 45 2004.0429 Rmenny Dátum 14 2004.1213 55 2003.1114 Alkatrész-rendelések GyKód 1101 1105

Selejt-szállítás ÖÜKód 3304 2207 3306 Termék-rendelések IÖKód 4407 4409 5506 5507 Vásárlások Nkód Kkód 5506 5507 6603 6605 Termék-importálás IÖKód 4407 4409 ÖÜKód 3304 3306 Hibás termékek visszaszállítása IÖKód 4407 4409 ÖÜKód 3304 3306 db Dátum 22 2004.0723 34 2004.0821 Kkód db Dátum 11 2004.0821 12 2004.0222 IÖKód 4407 4409 db Dátum 15 2003.1203 23 2003.1220 Kkód db Dátum 28 2004.0101 35 2004.0112 Rossz-termékek SzKód 7703 7704 6605 6603 Nem javítható termékek SzKód 7703 7704 Megjavított termékek SzKód 7703 7704 6605 6603 3. 2 3 A táblák feltöltésének SQL utasításai Példa: két egyedhalmaz, és a köztük lévő kapcsolat táblájának feltöltése adatokkal AlkatrészGyártók INSERT INTO alkgyartok VALUES (1101,’ElőlapGyártó’,’műanyag’,150); INSERT INTO alkgyartok VALUES (1105,’TakarólemezGyártó’,’alumínium’,270); Újrahasznosítók INSERT INTO uhasznositok VALUES

(2205,‘Műanyagozó Üzem’,’műanyag’,110,150); INSERT INTO uhasznositok VALUES (2207,’Kohászat’,’alumínium’,220,12); Összeszerelő üzemek INSERT INTO ouzemek VALUES (3304,‘Mátészalkai ÖÜzem’,350,250,20); INSERT INTO ouzemek VALUES (3306,’Budapesti ÖÜzem’,440,180,10); Importőrök INSERT INTO importorok VALUES (4407,’Juhhéj Import’,110,11); INSERT INTO importorok VALUES (4409,’Kovász Import’,234,34); Nagykereskedések INSERT INTO nagykerek VALUES (5506,‘Fő Nagyker’,230); INSERT INTO nagykerek VALUES (5507,’Bpi Nagyker’,456); Kiskereskedések INSERT INTO kiskerek VALUES (6603,’Bluefish’,430,21); INSERT INTO kiskerek VALUES (6605,’Aqua’,320,23); Szervizek INSERT INTO szervizek VALUES (7703,’Pulsar Hungary’,12); INSERT INTO szervizek VALUES (7704,’SoCo Hungary’,32); 3. 2 4 A kapcsolatok feltöltésének SQL utasításai Nyersanyag-rendelések INSERT INTO nyarendelesek VALUES

(1101,2205,’műanyag’,50,’2004.0220’); INSERT INTO nyarendelesek VALUES (1105,2207,’alumínium’,70,’2004.0222’); Alkatrész-rendelések INSERT INTO alkrendelesek VALUES (1101,3304,’műanyag’,25,’2004.0315’); INSERT INTO alkrendelesek VALUES (1105,3306,’alumínium’,23,’2004.0222’); Selejt-szállítás INSERT INTO selejtszall VALUES (3304,2205,’műanyag’,50,’2004.0123’); INSERT INTO selejtszall VALUES (3306,2207,’alumínium’,56,’2004.0419’); Termék-rendelések INSERT INTO termekrend VALUES (4407,5506,50,’2004.0317’); INSERT INTO termekrend VALUES (4409,5507,32,’2004.0410’); Vásárlások INSERT INTO vasarlasok VALUES (5506,6603,19,’2004.0512’); INSERT INTO vasarlasok VALUES (5507,6605,45,’2004.0429’); Termék-importálás INSERT INTO termekimport VALUES (4407,3304,14,’2004.1213’); INSERT INTO termekimport VALUES (4409,3306,55,’2003.1114’); Hibás termékek visszaszállítása INSERT INTO hibastermek VALUES

(4407,3304,22,’2004.0723’); INSERT INTO hibastermek VALUES (4409,3306,34,’2004.0821’); Rossz termékek INSERT INTO rossztermekek VALUES (7703,6605,11,’2004.0821’); INSERT INTO rossztermekek VALUES (7704,6603,12,’2004.0222’); Nem javítható termékek INSERT INTO nemjavtermekek VALUES (7703,4407,15,’2003.1203’); INSERT INTO nemjavtermekek VALUES (7704,4409,23,’2003.1220’); Megjavított termékek INSERT INTO megjavitotttermekek VALUES (7703,6605,28,’2004.0101’); INSERT INTO megjavitotttermekek VALUES (7704,6603,35,’2004.0122’); 4. Normalizáció 4. 1 A táblákban érvényes függőségek Mivel már a táblák tervezése során elsődleges szempont volt az áttekinthetőség, és a függőségek elkerülése, egyetlen táblában sincs tranzitív függés (Harmadik NormálForma – 3NF), s az egyéb függések bal oldalán mindig szuperkulcs áll, a táblák mindegyike Boyce-Codd NormálFormában van (BCNF). A példa kedvéért tekintsük az

AlkatrészGyártók táblát, amelyben a következő függések érvényesülnek: GyKód  GyNév GyKód  AlkFajta GyKód  GyártottMennyiség A megadott táblában a GyKód a kulcs. Egy gyártó többször is szerepelhet, például más gyártási profillal (AlkFajta), de ekkor a GyKód kulcs is más lesz, így biztosított, hogy ne forduljanak elő tranzitív függések (pl. AlkFajta  GyártottMennyiség) Ugyanez érvényes a kapcsolatok tábláira is, amelyeknél minden esetben a fő táblák kulcsai, és a tranzakció dátuma alkotják a kulcsot. 5. Statisztikai jellegű SQL Lekérdezések 5. 1 Egytáblás lekérdezések 5. 1 1 Mely AlkatrészGyártók készítenek műanyagból alkatrészeket? Mennyit és milyet? SELECT GyKod, GyNev, GyMenny FROM alkgyartok WHERE AlkFajta = 'műanyag' ORDER BY GyKod 5. 1 2 Mennyi az átlagos megtermelt mennyiség és az átlagos szétszerelendő termékszám nyersanyagonként az Újrahasznosító Üzemekben? SELECT NyFajta,

AVG(menny) as Menny Atlag, AVG(SztDb) as SzTDb Atlag FROM uhasznositok GROUP BY NyFajta 5. 1 2 Melyek azok az összeszerelő üzemek, amelyek összeszerelt termékmennyiség (kibocsátás) tekintetében meghaladják az átlagot? SELECT OUNev FROM ouzemek WHERE Kibocsatas > ( SELECT AVG(kibocsatas) FROM ouzemek ); 5. 1 3 Melyek azok a nyersanyag-rendelések, amelyek 2004 februárjában voltak aktuálisak? SELECT * FROM nyarendelesek WHERE EXTRACT (YEAR FROM Datum) ='2004' AND EXTRACT (MONTH FROM Datum)='02' 5. 1 4 Mely tranzakció tartalmazta a legnagyobb mennyiséget az Alkatrész-rendelések közül, és mire vonatkozóan, illetve mikor? SELECT RendMenny, AlkFajta, Datum FROM alkrendelesek WHERE RendMenny > ANY ( SELECT RendMenny FROM Alkrendelesek ) GROUP BY AlkFajta, RendMenny, Datum 5. 1 5 Átlagosan hány terméket tárolnak jelenleg a magyarországi szervizekben (Hungary)? SELECT AVG(db) AS atlag FROM szervizek WHERE (SzNev LIKE

'%Hungary%') 5. 2 Többtáblás lekérdezések 5. 2 1 Mennyi selejtes terméket szállított legutóbb az Újrahasznosítóba a Mátészalkai ÖÜzem? SELECT SzMenny FROM selejtszall WHERE ( selejtszall.Datum = ( SELECT max(Datum) FROM selejtszall WHERE selejtszall.OUKod = ( SELECT OUkod FROM OUzemek WHERE (OUNev='Mátészalkai ÖÜzem') ) ) ) 5. 2 2 Adjuk meg, hogy az adott importőr-kódokhoz mennyi hibás termék visszaszállítása tartozik összesen az Összeszerelő üzembe? SELECT IOKod,SUM(db) AS osszeg FROM importorok RIGHT OUTER JOIN hibastermek ON importorok.IOKod=hibastermekIOKod GROUP BY IOKod 5. 2 3 Melyek azok a szervizek, amelyek már szállítottak vissza nem javítható termékeket az importőrüknek? SELECT SzNev FROM szervizek LEFT OUTER JOIN nemjavtermekek ON szervizek.SzKod=nemjavtermekekSzKod 5. 2 4 Adjuk meg, hogy a Nagykereskedések mely Összeszerelő Üzemből kapják termékeiket! SELECT OUNev FROM ouzemek WHERE OUKod= ANY ( SELECT

OUKod FROM termekimport WHERE IOKod = ANY ( SELECT IOKod FROM termekrend WHERE NKod= ( SELECT NKod FROM Nagykerek WHERE NNev='Bpi Nagyker' ) ) ) 5. 2 5 Mely Kiskereskedések nem küldtek még egyetlen szerviznek sem rossz termékeket javításra? Adjuk meg Kódot, és rendezzük az eredményt Név szerint! SELECT DISTINCT KNev,KKod FROM Kiskerek WHERE KKod NOT IN ( SELECT KKod FROM Rossztermekek ) ORDER BY KNev