Tietokannan hallinta, syksy 2005, Harjoitus 5 (10.10. - 15.10.2005)

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. 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 levyoperaatioilla laskettuna 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) Piirrä kyselyä vastaava kyselypuu.
(b) Optimoi kyselyä käyttäen luennoilla esiteltyjä sääntöjä ja piirrä optimoitu kyselypuu.
(c) Mitkä hakemistot tehostaisivat kyselyn suoritusta?

4. Tietokannassa on tietoa tavaroista ja niiden toimittajista:

Suppliers(sid: integer, sname: char(20), city: char(20)); [300 riviä]
Supply(sid: integer ->Supplier, pid: integer->Parts); [20000 riviä]
Parts(pid: integer, pname: char(20), price: real); [2000 riviä]

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 > 2000;

Taulut on toteutettu järjestämättöminä peräkkäisrakenteina. Sivukoko on 4KB. Olkoon taululla Parts hajauttamalla toteutettu tiheä oheishakemisto sarakkeen pid perusteella ja taululla Supply hajauttamalla toteutetut tiheät oheishakemistot sarakkeiden pid ja sid perusteella. Oletetaan että toimittajia toimii 15 paikkakunnalla ja yli 2000 rahan hintaisia osia on 2% osista). Laadi suunnitelma kyselyn toteuttamiseksi.

5. Tarkastellaan tietokantakaaviota

Course(courseid: integer, name: char(40), taught_by:integer -> teacher, etc: char(200));
Material(courseid: integer->Course, bookid: integer ->Books);
Books(bookid: integer, name: char(40), etc: char(400));
Teacher(tid:integer, name:char(40),etc: char(200));

ja kyselyä SQL-kyselyä:

SELECT b.bookid,b.name, t.name
FROM Books b, Material m, Course c, teacher t
WHERE c.courseid = m.courseid AND b.name = 'Java Programming' AND
m.bookid = b.bookid and t.tid=c.taught_by;

Oletetaan, että taulussa Course on 6000 riviä. Material-taulussa on 12000 riviä ja Books-taulussa 24000 riviä. Books-taulun kirjoista on käytössä kursseilla n. 6000. Teacher taulussa on 2000 riviä. Sivukoko on 4KB.
(a) Arvioi kuinka monta riviä kysely palauttaa?
(b) Laadi kyselylle toteutussuunnitelma, kun taulut on tallennettu kasarakenteina ja niihin liittyvät tiheät B+ -puu hakemistot sarakkeille Course.name, Course.courseid, Material.courseid, Book.bookid ja Teacher.tid. Arvioi montako levyhakua tarvitaan? Voit olettaa laskelmissasi, että keskimääräinen hakemistotietueen koko kaikissa hakemistoissa on noin 20 tavua. Tee lisäoletuksia tarvittaessa.

6.Vastaa kurssipalautekyselyyn osoitteessa https://ilmo.cs.helsinki.fi/kurssit/servlet/Valinta