PL/SQL összetett gyakorlófeladatok

A feladatokat sorrendben oldd meg, mivel egymásra épülnek.

Adatmodell és tesztadatok

• space_missions: küldetések, távolság (ezer km), üzemanyag (liter), állapot.
• astronauts: űrhajósok és szakterületük.
• mission_crew: több-több kapcsolat: egy űrhajós több küldetéshez is beosztható.

1. feladat

space_missions

• id: a küldetés azonosítója, INTEGER típusú egész szám, nem lehet NULL.
• name: a küldetés neve, maximum 100 karakteres szöveges mező, nem lehet NULL.
• distance: a küldetés távolsága ezer kilométerben, maximum 10 jegyű egész szám, nem lehet NULL.
• fuel: a rendelkezésre álló üzemanyag literben, maximum 12 számjegyű szám, ebből 2 tizedesjegy, alapértéke 0, nem lehet NULL.
• status: a küldetés állapota, maximum 20 karakteres szöveges mező, alapértéke PREPARING, nem lehet NULL.

Megszorítások:

• A tábla elsődleges kulcsa az id mező.
• A distance értéke nagyobb, mint 0.
• A fuel értéke legalább 0.
• A status értéke csak PREPARING vagy LAUNCHED lehet.

astronauts

• id: az űrhajós azonosítója, INTEGER típusú egész szám, nem lehet NULL.
• name: az űrhajós neve, maximum 100 karakteres szöveges mező, nem lehet NULL.
• specialty: az űrhajós szakterülete, maximum 20 karakteres szöveges mező, nem lehet NULL.

Megszorítások:

• A tábla elsődleges kulcsa az id mező.
• A specialty értéke csak PILOT, ENGINEER, SCIENTIST vagy MEDIC lehet.

mission_crew

• mission_id: a küldetés azonosítója, típusa a space_missions tábla elsődleges kulcsának típusával egyezik meg, nem lehet NULL.
• astronaut_id: az űrhajós azonosítója, típusa az astronauts tábla elsődleges kulcsának típusával egyezik meg, nem lehet NULL.

Megszorítások:

• A tábla összetett elsődleges kulcsa a mission_id és az astronaut_id mezőkből áll.
• A mission_id a space_missions tábla id mezőjére hivatkozó idegen kulcs.
• Az astronaut_id az astronauts tábla id mezőjére hivatkozó idegen kulcs.

Tesztadatok

A táblák létrehozása után futtasd az alábbi seed szkriptet.

Tesztadatok (10 küldetés, 12 űrhajós, 24 beosztás)

INSERT INTO space_missions VALUES (1, 'Holdposta', 400, 1000, 'PREPARING');
INSERT INTO space_missions VALUES (2, 'Vénusz-szonda', 3000, 2000, 'PREPARING');
INSERT INTO space_missions VALUES (4, 'Marskutató', 8000, 0, 'PREPARING');
INSERT INTO space_missions VALUES (5, 'Europa-jégfúró', 6000, 5000, 'PREPARING');
INSERT INTO space_missions VALUES (7, 'Szaturnusz-futár', 12000, 8000, 'LAUNCHED');
INSERT INTO space_missions VALUES (8, 'Titán-labor', 15000, 10000, 'PREPARING');
INSERT INTO space_missions VALUES (10, 'Üstökösvadász', 5000, 2800, 'PREPARING');
INSERT INTO space_missions VALUES (11, 'Aszteroida-bányász', 2000, 1500, 'PREPARING');
INSERT INTO space_missions VALUES (14, 'Neptunusz-figyelő', 20000, 0, 'PREPARING');
INSERT INTO space_missions VALUES (15, 'Orion-mentőjárat', 1000, 1000, 'PREPARING');

INSERT INTO astronauts VALUES (1, 'Varga Mira', 'PILOT');
INSERT INTO astronauts VALUES (2, 'Kovács Levente', 'ENGINEER');
INSERT INTO astronauts VALUES (3, 'Farkas Lili', 'SCIENTIST');
INSERT INTO astronauts VALUES (4, 'Tóth Noel', 'MEDIC');
INSERT INTO astronauts VALUES (5, 'Nagy Zora', 'PILOT');
INSERT INTO astronauts VALUES (6, 'Kiss Áron', 'ENGINEER');
INSERT INTO astronauts VALUES (7, 'Balogh Emma', 'SCIENTIST');
INSERT INTO astronauts VALUES (8, 'Szabó Bence', 'MEDIC');
INSERT INTO astronauts VALUES (9, 'Molnár Luca', 'PILOT');
INSERT INTO astronauts VALUES (10, 'Horváth Márk', 'ENGINEER');
INSERT INTO astronauts VALUES (11, 'Fekete Hanna', 'SCIENTIST');
INSERT INTO astronauts VALUES (12, 'Oláh Dániel', 'MEDIC');

INSERT INTO mission_crew VALUES (1, 1);
INSERT INTO mission_crew VALUES (1, 2);
INSERT INTO mission_crew VALUES (2, 3);
INSERT INTO mission_crew VALUES (2, 4);
INSERT INTO mission_crew VALUES (4, 5);
INSERT INTO mission_crew VALUES (4, 6);
INSERT INTO mission_crew VALUES (4, 7);
INSERT INTO mission_crew VALUES (4, 8);
INSERT INTO mission_crew VALUES (5, 9);
INSERT INTO mission_crew VALUES (5, 10);
INSERT INTO mission_crew VALUES (5, 11);
INSERT INTO mission_crew VALUES (7, 1);
INSERT INTO mission_crew VALUES (7, 3);
INSERT INTO mission_crew VALUES (7, 4);
INSERT INTO mission_crew VALUES (8, 5);
INSERT INTO mission_crew VALUES (8, 7);
INSERT INTO mission_crew VALUES (8, 12);
INSERT INTO mission_crew VALUES (10, 9);
INSERT INTO mission_crew VALUES (10, 10);
INSERT INTO mission_crew VALUES (10, 11);
INSERT INTO mission_crew VALUES (11, 2);
INSERT INTO mission_crew VALUES (11, 6);
INSERT INTO mission_crew VALUES (15, 5);
INSERT INTO mission_crew VALUES (15, 8);

COMMIT;

2. feladat

Írj függvényt, amely küldetésazonosító alapján kiszámítja az üzemanyagigényt: távolságegységenként fél liter, személyenként további 100 liter. 5000 egységnél hosszabb útra 20% tartalék szükséges.

Az eredmény két tizedesjegyre kerekített szám legyen. feltételezhetjük, hogy létezik a küldetés viszont a hiányzó legénységet ellenőriznünk kell hiányzó legényésg esetén NULL-al térjünk vissza.

3. feladat

Írj eljárást, amely név és távolság alapján új küldetést hoz létre. Az új azonosító automatikusan generálódjon, és OUT paraméterben térjen vissza.

Csak nem üres név és pozitív egész távolság fogadható el. A küldetés üres tankkal, legénység nélkül, PREPARING állapotban induljon.

A függvény meghívása után az OUT paraméterben visszakapott érték segítségével jelenítsük meg az új azonosítót.

4. feladat

Írj eljárást, amely egy űrhajóst egy küldetéshez rendel. Csak létező űrhajós osztható be létező, PREPARING állapotú küldetésre. A legénység legfeljebb 6 fős. Ha van már azonos specialitással rendelkező személy a legénységben, a hozzárendelést elutasítjuk.

Egy űrhajós több küldetésben is részt vehet. Sikertelen beosztásnál ne változzanak az adatok, és jelenjen meg az elutasítás oka.

5. feladat

Írj eljárást, amely elindít egy küldetést, ha az még PREPARING állapotú, 2–6 fős legénységében van pilóta, és elegendő az üzemanyaga (2. feladat függvénye).

Siker esetén az állapot legyen LAUNCHED, az üzemanyag ne változzon. Sikertelen indítás esetén jelenítse meg az elutasítás okát.