Půjčovna lodí - úkoly pro cvičení
Popis databáze:
Schémata tabulek jsou dána následovně:
B_TYPE(TYPE : VARCHAR2(20), C_CAPACITY : NUMBER(4), CAPACITY :
NUMBER(4), M_SPEED(4))
BOAT (BID : NUMBER(3), BNAME : VARCHAR2(20), BTYPE :
VARCHAR2(20), COLOR
: VARCHAR2(20))
SAILOR (SID : NUMBER(3), SNAME : VARCHAR2(20), AGE : NUMBER(3), RATE :
NUMBER(1))
CUSTOMER (CID : NUMBER(3), CNAME : VARCHAR2(20))
REG_LINE (LID : NUMBER(3), START : VARCHAR2(20), DESTINATION :
VARCHAR2(20))
REG_LINE_REL (LID : NUMBER(3) , RID : NUMBER(3), LTIME : DATE)
REG_LINE_REL_COVER (LID : NUMBER(3), RID : NUMBER(3), COV_DATE : DATE,
BID : NUMBER(3), SID : Ingeter)
RESERVATION (BID : NUMBER(3), SID : NUMBER(3), RES_DATE : DATE)
SAIL_WITH_GUIDE (CID : NUMBER(3), BID : NUMBER(3), SID : NUMBER(3),
SAIL_DATE : DATE)
Vysvětlení a integritní omezení:
Půjčovna nabízí čluny pro plavby s průvodcem a také jezdí na
pravidelních linkách.
Čluny jsou registrovány v tabulce BOAT a identifikovány jsou atributem
BID.
Každký člun má jméno, dále můžeme evidovat jeho barvu a typ. Jméno
člunu je unikátní. Barva člunu může nabývat pouze těchto hodnot:
"yellow, blue, ping, brown, black, geen" (jiné barvy neumí místní
výrobce, který je shodou okolností též akcionářem půjčovny, vyrábět).
Lodě jsou různých typů. Typ lodě je dán číselníkem typů lodí -
tabulka B_TYPE. Zde je uložena též informace o nosnosti tohot typu
lodi, počtu pasažérů a nejvyšší dosažené rychlosti.
Každý námořník je identifikován atributem SID v tabulce SAILOR. Jméno
námořníka musí být v databází unikátní.
Majitel půjčovny je pod neustálým tlakem odborů, takže musel přijmout
opatření, že žádný z jeho regestrovaných námořníků nebude mladší než 14
let a také nebude starší než 700 let.
Někteří zákazníci jsou registrováni v tabulce CUSTOMER a identifikováni
atributem CID.
Pravidelná linka má počátek a konec (atributy) a je identifikována
atributem LID.
Pravidelná linka má jizdní řád (jet se může víckrát za den). Každá
jízda je rigistrována v tabulce REG_LINE_REL a je identifikována
dvojicí LID, RID.
Pokrytí každé položky v jízdním řádu je registrováno v tabulce
REG_LINE_REL_COVER. Je to realizace položky v jízdním řádu v konkrétní
den konkrétním člunem a konkrétním námořníkem.
Námořníci si mohou na dané datum rezervovat člun -
tabulka RESERVATION.
Registrovaní zákazníci si mohou objednat soukromou plavbu s průvodcem -
tabulka
SAIL_WITH_GUIDE.
Úkoly:
- deklarativní integritní omezení a DDL
Skript pro vytvoření databáze bez integritních omezení je k
dispozici zde.
- Skript si stáhněte, prostudujte a pomocí iSQL*Plus spusťte,
abyste si ověřili, že se tabulky vyrobí. (Při prvním spuštění skriptu
dostanete sadu chybových hlášení "Table or view does not exists.". To
proto, že skript počítá s tím, že bude spouštěn víckrát za sebou, tudíž
se snaží vytvářené tabulky nejprve zrušit).
- Upravte skript tak, abyste co nejvíce výše uvedených integritních
omezení ošetřili pomocí deklarativních integritních omezení.
Skript odlaďte.
- Svou práci porovnejte se vzorovým řešením, které získáte zde.
- deklarativní integritní omezení a DML
- Skript pro vložení dat je k dispozici zde.
Skript si stáhněte, prostudujte a pomocí iSQL*Plus spusťte. Ověřte si,
že data se do tabulek skutečně vložila.
- Formulujte následující příkazy a ověte si, že systém skutečně
kontroluje integritní omezení:
- Pokuste se vložit nového námořníka jménem Yarwen, 900
let, rating 8.
- Pokuste se vložit nového námořníka jménem Dustin, 56 let,
rating 9.
-
Zkuste z databáze vymazat loď jménem Pooh.
- Zkuste z databáze vymazat loď jménem Jane.
-
Změnte barvu lodi Yenifer z black na silver.
-
Zkuste změnit BID člunu 5 na hodnotu 13.
- Zkuste vložit loď jménem Charybda. Je to loď typu
floating_rock.
- Zkuste některý cizí klíč deklarovat s klauzulí ON DELETE
CASCADE a ověřte si funkčnost.
- deklarativní integritní omezení a DataDictionary
- Podívejte se na strukturu těchto pohledů: USER_DATA,
USER_CONSTRAINTS, USER_CONS_COLUMNS.
- Dotazy nad těmito pohledy si prohlédněte, jak jsou jednotlivá
integritní omezení dokumentovaná. Všimňte si sloupce STATUS v pohledu
USER_CONSTRAINTS.
- Zkuste některá integritní omezení dočasně vypnout příkazem alter
table (např: ALTER TABLE SAILOR DISABLE CONSTRAINT SAILOR_CK;)
- Zkontrolujte status příslušných integritních omezeni.
- Zkuste vložit data, která vypnuté IO porušují (například: insert
into SAILOR values (1,'Yarwin', 900, 7);)
- Zkuste příslušné integritní omezení zapnout.
- Takto si vyzkoušejte i další integritní omezení - budete-li
vypínat například B_TYPE_PK, musíte k příkazu přidat klauzuli CASCADE,
která vypne též (kaskádně) příslušné cizí klíče (BOAT_FK). Zapnutí
B_TYPE_PK už nezapne BOAT_FK (ověřte si).
- DCL
- Pracujte po dvojicích - například uživatelé U1 a U2.
- Pomocí příkazu GRANT SELECT, INSERT, UPDATE, DELETE ON BOAT TO
U2; dá uživatel U1 (vlastník tabulky BOAT) uživateli U2 práva
pracovat příslušným způsobem s tabulkou v jeho schématu.
- U2 si v pohledech ALL_TABLES a ALL_TAB_PRIVS může ověřit, že
příslušná práva nad tabulkou má.
- U2 si nyní může vyzkoušet několik DML operací nad tabulkou BOAT
ve schématu uživatele U1 (SELECT * FROM U1.BOAT; ...).
- U1 může privilegia odebrat příkazem REVOKE INSERT FROM U2;
- U2 může opět nahlédnout do data dictionary.
- DML - vlastnosti transakcí
- Každý uživatel si otevře dvě samostná okna iSQL*Plus - tím
vytvoří dvě session (označíme je A a B).
- Vyzkoušejte si následující scénáře:
- Read consistency:
- V session A proveďte změny v tabulce (např. UPDATE SAILOR
SET RATING = RATING + 2;). Transakci nepotvrzujte a ověřte si, že v
session A vidíte změněná data.
- V session B se podívejte na data, vidíte staré hodnoty.
- V session A potvrďte transakci příkazem COMMIT
- V session B se opět podívejte na data.
- Zamykání na úrovni řádku - neblokující:
- V session A změňte jeden řádek v tabulce (např UPDATE SAILOR
SET AGE = 345 WHERE SNAME = 'Charon';). Nepotvrzujte.
- V session B změňte jiný řádek v tabulce (např UPDATE
SAILOR SET AGE = 36 WHERE SNAME = 'Dustin';).
- Zamykání na úrovni řádků - blokující:
- V session A změňte jeden řádek v tabulce (např UPDATE SAILOR
SET AGE = 345 WHERE SNAME = 'Charon';). Nepotvrzujte.
- V session A změňte jeden řádek v tabulce (např UPDATE SAILOR
SET RATING=2 WHERE SNAME = 'Charon';).
- Session B čeká na uvolnění řádku, který je zamknutý v session A.
- Potvrďte transakci v session A příkazem COMMIT.
- Podívejte se na změněný řádek v session B.
- SQL dotazy a pohledy
Máte-li ještě čas a chuť zkuste vyřešit následující dotazy:
- Seznam člunů (BID, BNAME), které nikdy nejely na pravidelné lince
(nejsou v tabulce pokrytí).
- Seznam námořníků (SNAME, AGE), kteří jeli jako průvodci na žluté
lodi typu "SUBMARINE".
- Kolik zelených členů je registrováno v půjčovně?
- Jaký je průměrný věk námořníků?
- Najděte námořníky (SID, SNAME), kteří jeli alespoň třikrát
pravidelnou linku.
- Jména námořníků a počet jejich plaveb coby průvodce,
výstup seřaďte podle vzestupně podle počtu plaveb.
- Jména námořníků a počet jejich plaveb coby průvodece, vypusťte
námořníky, kteří jeli méně než třikrát, výstup seřaďte sestupně podle
počtu plaveb.
- Seznam námořníků a počet jejich plaveb (coby průvodce i na
linkách).
Výstup seřaďte podle počtu plaveb.
- Vytvořte pohle Favorit_Boats (BID, BNAME, COLOR, TYPE). Jsou zde
čluny, které byly pronajaty k soukromé plavbě více než dvakrát.
- Vytvořte pohled Popular_Sailor (SID, SNAME, AGE). Jsou zde
námořníci, kteří jeli více jak 5x (jako průvodci nebo na pravidelné
lince)
Řešení některých výše uvedených příkladů najdete zde.