Gagnasafnsfræði, haust 2011

[ Dagskrá  |  Námsefni  |  Verkefni  |  Dæmatímar  |  Orðalisti  |  Námsmat  |  Kennslubók ]

Verkefni 4 - Forritun á móti SQL

Lausnum skal skilað í hólf viðkomandi dæmakennara (sjá lista yfir dæmatíma).

Skiladagur: mánudaginn 26. september fyrir kl 16:00

Hugbúnaður

Tilbúnir pakkar sem innihalda Apache vefþjón, PHP og PostgreSQL:

Tengingar við PostgreSQL

Til að leyfa ytri (remote) tengingar við PostgreSQL þjóninn þarf mögulega að breyta eftirfarandi stillingarskrám.

ATH: Ef þið notið tilbúnu Bitnami pakkana hér að ofan þá þarf ekki að breyta neinu til að leyfa ytri tengingar.

pg_hba.conf

Bæta við eftirfarandi línu:
host all all 127.0.0.1/32 md5

postgresql.conf

Bæta við eftirfarandi línu til að láta PostgreSQL hlusta á öllum IP tölum sem vélin er að nota:
listen_addresses='*'

Java

JDBC fylgir með Java en það þarf að sækja forritasafn fyrir hverja tegund af gagnasafnskerfi (PostgreSQL/MySQL/o.s.frv.)

Það þarf að bæta JAR skránni í CLASSPATH til að Java keyrslu umhverfið finni hana. Ef þið eruð að nota þróunarumhverfi (t.d. Eclipse) þá er hægt að bæta JAR skránni við lista af libraries í núverandi project.

Ef þið notið skelina þá er hægt að nota java -cp stika til að stilla classpath.

Dæmi um þýðingu og keyrslu í Windows:
hhg@hhg:~/v4$ javac Execute.java
hhg@hhg:~/v4$ java -cp "postgresql-9.1-901.jdbc4.jar;." Execute
hhg@hhg:~/v4$ 
og í Linux eða MacOS X:
hhg@hhg:~/v4$ javac Execute.java
hhg@hhg:~/v4$ java -cp postgresql-9.1-901.jdbc4.jar:. Execute
hhg@hhg:~/v4$ 
Sýnidæmi: Aðrir tenglar:

Python

Sýnidæmi: Aðrir tenglar:

PHP

Sýnidæmi: Aðrir tenglar:

ATH: Ef þið fáið villu eins og:

pg_insert() Expects scaler values as field values in...
þegar þið eruð að leysa lið 2 með PHP, þá þarf að kasta XML gildunum yfir í streng:
// Breyta:
$mynt->myntnafn
// Yfir í:
(string)$mynt->myntnafn

Skiladæmi:

  1. Skilgreinið töflu til að halda utan um opinbert viðmiðunargengi íslensku krónunnar gagnvart vissum gjaldmiðlum.

    Taflan skal innihalda eftirfarandi dálka:

    1. dags: dagsetning
    2. myntnafn: þrír stafir (t.d. USD, GBP)
    3. kaupgengi: tala með tveimur aukastöfum (t.d. 135.98)
    4. midgengi: tala með tveimur aukastöfum
    5. solugengi: tala með tveimur aukastöfum

    Notið numeric tagið í PostgreSQL til að geyma gengistölurnar.

    Það er ein skráning á dag per myntnafn. Hagið aðallykli töflunnar samkvæmt því.

    Sýnið CREATE TABLE skipunina á læsilegu formi með inndrætti.

  2. Það er hægt að sækja opinbert viðmiðunargengi íslensku krónunnar í dag á XML formi frá vefþjónustu Seðlabankans á eftirfarandi slóð:
    http://www.sedlabanki.is/?PageID=289

    Einnig er hægt að sækja viðmiðunargengið ákveðinn dag með því að bæta við stikanum dagur=DD.MM.YYYY.

    Dæmi:

    http://www.sedlabanki.is/?PageID=289&dagur=25.01.2007
    Skrifið forrit í Java, Python, PHP eða öðru forritunarmáli að ykkar vali sem virkar á eftirfarandi hátt:

    1. Forritið keyrir í skipanalínu sem sjálfstætt forrit
    2. Forritið tekur eitt viðfang: dagsetningu á forminu YYYY-MM-DD
    3. Forritið sækir opinbert viðmiðunargengi frá vefþjónustu Seðlabankans
    4. Forritið þáttar XML gögnin og skráir viðmiðunargengi USD, GBP, CHF og JPY í PostgreSQL töfluna úr dæmi 1

    Dæmi um mögulega keyrslu á forritinu:

    java gengi 2011-09-12
    

    eða

    python gengi.py 2011-09-12
    

    Skilið forritstextanum. Passið að textinn sé læsilegur (notið réttan inndrátt/indent). Sýnið dæmi um keyrslu og hvað var skráð í PostgreSQL fyrir þá keyrslu.

  3. Búið til eftirfarandi töflu sem heldur utan um pantanir:
    CREATE TABLE pantanir (
        id SERIAL PRIMARY KEY,
        customer INTEGER NOT NULL,
        dags DATE NOT NULL,
        vorunumer INTEGER NOT NULL,
        mynt CHAR(3) NOT NULL,
        verd NUMERIC(8,2) NOT NULL,
        magn INTEGER NOT NULL
    );
    

    Hlaðið inn skránni pantanir.csv (sem inniheldur 10.000 skáldaðar pantanir) með eftirfarandi COPY skipun:

    COPY pantanir (dags, customer, vorunumer, mynt, verd, magn)
       FROM 'pantanir.csv'
       DELIMITER '|';
    

    ATH. Það gæti þurft að slá inn slóðina (full path) á pantanir.csv ef skráin er ekki í vinnumöppunni. Gerið þá /home/xx/.. í Linux/MacOS X eða c:/xx/.. í Windows.

  4. Sýnið SQL skipun sem sækir eftirfarandi töflu: fyrir hvert vörunúmer, mynt og verð, reiknið fjölda pantana, heildarmagn og heildarupphæð (verð*heildarmagn) í júlí (2011-07). Raðið eftir heildarupphæð í lækkandi röð.

    Útkoman ætti að vera nákvæmlega sú tafla sem sést hér fyrir neðan:

     vorunumer | mynt | verd  | fjoldi | magn  |   total    
    -----------+------+-------+--------+-------+------------
        221517 | USD  | 29.99 |    733 | 36110 | 1082938.90
        273140 | USD  |  8.99 |   1450 | 74086 |  666033.14
        732052 | GBP  | 15.99 |    679 | 34327 |  548888.73
        158173 | USD  | 17.52 |    462 | 23089 |  404519.28
        601314 | GBP  | 19.99 |    242 | 12061 |  241099.39
        770291 | CHF  |  2.99 |    961 | 49041 |  146632.59
        526734 | CHF  |  3.25 |    241 | 12249 |   39809.25
    (7 rows)
    

    Ábending: Það er hægt að nota TO_CHAR fallið í PostgreSQL til að vinna með dagsetningar.

    Dæmi:

    SELECT *, TO_CHAR(dags, 'YYYY-MM') AS month
    FROM pantanir
    LIMIT 1;
    
      id   | customer |    dags    | vorunumer | mynt | verd  | magn |  month  
    -------+----------+------------+-----------+------+-------+------+---------
     20000 |    10248 | 2011-07-01 |    158173 | USD  | 17.52 |   83 | 2011-07
    (1 row)
    
  5. Notið forritið ykkar sem sækir viðmiðunargengið fyrir gefna dagsetningu til að skrá inn gengið fyrir dagsetningar 2011-08-01, 2011-08-02 og 2011-08-03.

    Sýnið SQL skipun sem notar ytri tengingu (OUTER JOIN) til að sækja lista yfir allar dagsetningar+myntir í pöntunum sem eru ekki með neitt gengi skráð í gengistöfluna.

  6. Sýnið SQL skipun sem sækir eftirfarandi töflu: fyrir hvern mánuð, reiknið heildarupphæð í íslenskum krónum fyrir allar pantanir (notið miðgengið til að breyta yfir í krónur).

    Taflan ætti að vera ca. svona:

     month   | total
    ---------+--------
     2011-08 | XXXX
    

    ATH: Ég geri ráð fyrir að þið séuð bara með gengi skráð fyrir dagsetningar 2011-08-01, 2011-08-02 og 2011-08-03.

    Ég geri einnig ráð fyrir að þið notið venjulega innri tengingu (INNER JOIN) og þá eru pantanir án gengis ekki teknar með í útreikningi, sbr. tengireglur fyrir INNER JOIN.