Tietokannan hallinta, syksy 2004, Harjoitus 5 (29.11. - 3.12.2004)

1 Tutkitaan liitosta R JOIN _R.a=S.b S. Kustannusmittana käytetään levysivujen luku-/kirjoitusmäärää. Tuloksen kirjoitukseen vaadittavaa kustannusta ei oteta huomioon, jos erikseen ei ole mainittu. Sivunkoko on 4KB. Taulu R sisältää 10,000 tietuetta ja jokaisella sivulla on 10 tietuetta. Taulu S sisältää 2000 tietuetta ja jokaisella sivulla on 10 tietuetta. Sarake b on taulun S pääavain. Molemmat taulut on talletettu kasarakenteina, eikä niihin liity hakemistoja. Puskurisivuja on käytössä 52 ja puskurisivun koko on 4KB.
(a) Mikä on liitoksen kustannus käytettäessä jaksottaisten sisäkkäisten silmukoiden liitosmenetelmää? Mitä puskureiden määrän vähentäminen vaikuttaa kustannuksiin?
(b) Mikä on liitoksen kustannus käytettäessä lomitusliitosta. Miten paljon puskureiden määrää voitaisiin vähentää kustannuksia lisäämättä?
(c) Mikä on liitoksen kustannus käytettäessä hajautusliitosta? Mitä on puskureiden määrän vähentäminen vaikuttaisi tässä menetelmässä?

2 Tarkastellaan tehtävän 1 liitosta.
(a) Mikä olisi levyoperaatioiltaan tehokkain mahdollinen liitostapa ja kuinka paljon puskuritilaa sen käyttö vaatisi ?
(b) Kuinka monta tulostietuetta liitos tuottaa vähintään ja enintään ja kuinka monta sivua pitää kirjoittaa levylle?
(c) Muuttuisiko minkään edellisen kohdan vastauksessasi, jos tietäisit, että R:n sarake a on viiteavain, jolle ei sallita tyhjäarvoja ja jonka kohteena on S.b?

3. Seuraavan kaavion mukaisessa tietokannassa on informaatiota työntekijöistä, osastoista ja osastojen finanssitiedoista.

Emp(eid: integer,did: integer, sal: integer, hobby: char(2));
Dept(did: integer, dname: char(20), floor: integer, phone: char(10));
Finance(did: integer, budget: real, sales: real, expenses: real);

Tarkastellaan kyselyä:

SELECT D.dname, F.budget
FROM Emp E, Dept D, Finance F
WHERE E.did = D.did AND D.did = F.did AND D.floor = 1
AND E.sal >= 59000 AND E.hobby = 'yodeling';

(a) Anna kyselyä vastaava relaatioalgebran lauseke ja piirrä lauseketta vastaava kyselypuu.
(b) Optimoi kyselyä käyttäen luennoilla esiteltyjä sääntöjä ja piirrä näkyviin optimoidun lausekkeen kyselypuu.
(c) Mitkä hakemistot tehostaisivat kyselyn suoritusta? Selvitä lyhyesti.

4. Tietokannasaa on tietoa tavaroista ja niiden toimittajista:

Suppliers(sid: integer, sname: char(20), city: char(20));
Supply(sid: integer ->Supplier, pid: integer->Parts);
Parts(pid: integer, pname: char(20), price: real);

Tarkastellaan kyselyä:

SELECT S.sname, P.pname
FROM Suppliers S, Parts P, Supply Y
WHERE S.sid = Y.sid AND Y.pid = P.pid AND
S.city = 'Madison' AND P.price <= 1,000;

(a) Anna kyselyä vastaava relaatioalgebramuotoinen lauseke ja piirrä lauseketta vastaava kyselypuu.
(b) Mitä tietoja tauluista optimoija tarvitsisi hyvän kyselysuunnitelman valintaan?
(c) Optimoi kyselyä tehden oletuksia tarpeellisista tilastotiedoista ja piirrä kyselypuu ja arvioi oletuksiisi perustuen kyselyn kustannus (olkoon osia noin 2000)
.

5. Tarkastellaan tietokantakaaviota

Course(courseid: integer, name: char(40));
Material(courseid: integer->Course, bookid: integer ->Books);
Books(bookid: integer, name: char(40));

ja kyselyä SQL-kyselyä:

SELECT b.bookid,b.name
FROM Books b, Material m, Course c
WHERE c.courseid = m.courseid AND c.name = 'Database Management' AND
m.bookid = b.bookid;

Oletetaan, että taulussa Course on 6000 riviä. Material-taulussa on 12000 riviä ja Books-taulussa 24000 riviä. Sivukoko on 4KB. Oletetaan että käytössä on 20 sivua puskureita.
(a) Kuinka monta riviä keskimäärin kysely palauttaa?
(b) Anna kyselyä vastaava relaatioalgebran lauseke ja piirrä kyselypuu.
(c) Optimoi kysely ja piirrä optimoitu kyselypuu.
(d) Valitse operaatioille toteutustapa, kun taulut on tallennettu kasarakenteina ja niihin liittyvät tiheät B+ -puu hakemistot sarakkeille Course.name ja Course.courseid, Material.courseid ja Book.bookid. Montako datasivuihin kohdistuvaa levyhakua tarvitaan?