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.
  1. 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).
  2. 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.
  3. Svou práci porovnejte se vzorovým řešením, které získáte zde.

- deklarativní integritní omezení a DML

  1. 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.
  2. Formulujte následující příkazy a ověte si, že systém skutečně kontroluje integritní omezení:
    1.  Pokuste se vložit nového námořníka jménem Yarwen, 900 let, rating 8.
    2.  Pokuste se vložit nového námořníka jménem Dustin, 56 let, rating 9.
    3.   Zkuste z databáze vymazat loď jménem Pooh.
    4.   Zkuste z databáze vymazat loď jménem Jane.
    5.   Změnte barvu lodi Yenifer z black na silver.
    6.   Zkuste změnit BID člunu 5 na hodnotu 13.
    7.   Zkuste vložit loď jménem Charybda. Je to loď typu floating_rock.
    8.   Zkuste některý cizí klíč deklarovat s klauzulí ON DELETE CASCADE a ověřte si funkčnost.

- deklarativní integritní omezení a DataDictionary

  1. Podívejte se na strukturu těchto pohledů: USER_DATA, USER_CONSTRAINTS, USER_CONS_COLUMNS.
  2. 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.
  3. Zkuste některá integritní omezení dočasně vypnout příkazem alter table (např: ALTER TABLE SAILOR DISABLE CONSTRAINT SAILOR_CK;)
  4. Zkontrolujte status příslušných integritních omezeni.
  5. Zkuste vložit data, která vypnuté IO porušují (například: insert into SAILOR values (1,'Yarwin', 900, 7);)
  6. Zkuste příslušné integritní omezení zapnout.
  7. 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

  1. Pracujte po dvojicích - například uživatelé U1 a U2.
  2. 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.
  3. U2 si v pohledech ALL_TABLES a ALL_TAB_PRIVS může ověřit, že příslušná práva nad tabulkou má.
  4. U2 si nyní může vyzkoušet několik DML operací nad tabulkou BOAT ve schématu uživatele U1 (SELECT * FROM U1.BOAT; ...).
  5. U1 může privilegia odebrat příkazem REVOKE INSERT FROM U2;
  6. U2 může opět nahlédnout do data dictionary.

- DML - vlastnosti transakcí

  1. Každý uživatel si otevře dvě samostná okna iSQL*Plus - tím vytvoří dvě session (označíme je A a B).
  2. Vyzkoušejte si následující scénáře:
  3. Read consistency:
    1. 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.
    2. V session B se podívejte na data, vidíte staré hodnoty.
    3. V session A potvrďte transakci  příkazem COMMIT
    4. V session B se opět podívejte na data.
  4. Zamykání na úrovni řádku - neblokující:
    1. V session A změňte jeden řádek v tabulce (např UPDATE SAILOR SET AGE = 345 WHERE SNAME = 'Charon';). Nepotvrzujte.
    2. V session B změňte jiný řádek v tabulce  (např UPDATE SAILOR SET AGE = 36 WHERE SNAME = 'Dustin';).
  5. Zamykání na úrovni řádků - blokující:
    1. V session A změňte jeden řádek v tabulce (např UPDATE SAILOR SET AGE = 345 WHERE SNAME = 'Charon';). Nepotvrzujte.
    2. V session A změňte jeden řádek v tabulce (např UPDATE SAILOR SET RATING=2 WHERE SNAME = 'Charon';).
    3. Session B čeká na uvolnění řádku, který je zamknutý v session A.
    4. Potvrďte transakci v session A příkazem COMMIT.
    5. 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:
  1. Seznam člunů (BID, BNAME), které nikdy nejely na pravidelné lince (nejsou v tabulce pokrytí).
  2. Seznam námořníků (SNAME, AGE), kteří jeli jako průvodci na žluté lodi typu "SUBMARINE".
  3. Kolik zelených členů je registrováno v půjčovně?
  4. Jaký je průměrný věk námořníků?
  5. Najděte námořníky (SID, SNAME), kteří jeli alespoň třikrát pravidelnou linku.
  6. 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.
  7. 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.
  8. 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.
  9. Vytvořte pohle Favorit_Boats (BID, BNAME, COLOR, TYPE). Jsou zde čluny, které byly pronajaty k soukromé plavbě více než dvakrát.
  10. 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.