Løsningsforslag til kapittel 15

25.02.2020: Selv om noen løsningsforslag nå er på plass også for dette kapitlet, gjenstår det fortsatt litt arbeid. Arbeidet fortsetter utover våren og nye løsningsforslag legges ut etter hvert som de blir klare.

Se også skript for flytting/migrering av Hobbyhuset til Neo4j

Oppgave 1

Objektrelasjonelle PostgreSQL-datatyper i Hobbyhuset.

Både Kunde og Ansatt inneholder kolonner Fornavn og Etternavn. Disse kan representeres som et sammensatt navn ved følgende brukerdefinerte datatype:


CREATE TYPE NavnType AS
(
  Fornavn    VARCHAR(50) NOT NULL,
  Etternavn  VARCHAR(50) NOT NULL,
);

En skisse av hvordan denne datatypen kan brukes i definisjonen av tabellen Kunde:


CREATE TABLE Kunde
(
  ...
  Navn  NavnType,
  ...
);

I løsningsforslag til oppgave 1e blir fornavn og etternavn til hver kunde vist. SELECT-delen må da skrives om som følger:


SELECT K.KNr, (Navn).Fornavn, (Navn).Etternavn, ...

Ved å innføre en brukerdefinert datatype for detaljene på en ordre, kan vi samle alle opplysninger om én ordre på én rad.


CREATE TYPE OrdrelinjeType AS
(
  VNr         CHAR(5),
  PrisPrEnhet DECIMAL(8,2) NOT NULL,
  Antall      INTEGER NOT NULL,
)

Tabellen Ordre kan deretter bygges ut med en array-kolonne Linjer:


CREATE TABLE Ordre
(
  OrdreNr    INTEGER,
  OrdreDato  DATE NOT NULL,
  SendtDato  DATE,
  BetaltDato DATE,
  KNr        INTEGER NOT NULL,
  Linjer[]   OrdrelinjeType[],
  CONSTRAINT OrdrePN PRIMARY KEY (OrdreNr),
  CONSTRAINT OrdreKundeFN FOREIGN KEY (KNr) REFERENCES Kunde (KNr)
);

Det gjenstår å omprogrammere noen av spørringene i kapittel 4. Her er først et enkelt eksempel på hvordan man kan hente ut ordrelinjer – spørringen viser første ordrelinje på hver ordre:


SELECT OrdreNr, OrdreDato, KNr, Linjer[0]
FROM Ordre

PostgreSQL-funksjonen UNNEST kan brukes for å "ekspandere" en array. Neste spørring leverer det samme som en indre likekobling mellom Ordre og Ordrelinje i den "vanlige" utgaven av Hobbyhuset.


SELECT OrdreNr, OrdreDato, KNr, UNNEST(Linjer)
FROM Ordre;

Kolonnen Kunde.Kjønn kan håndteres som en oppramstype:


CREATE TYPE KjønnType AS ENUM ('mann', 'kvinne');

Oppgave 2

Fjelltopper, elver og kommuner.

Vi kan her lage en tabell for fjelltopper, en for elver og en for kommuner. Tabellene kan inneholde "vanlige" egenskapsdata, som f.eks. navn, og dessuten geometridata (koordinater) i en egen kolonne Geometri med datatype Geography – dette forutsetter PostgreSQL-utvidelsen PostGIS. For en fjelltopp vil Geometri-kolonnen være et punkt, for en elv vil kolonnen være en polylinje og for en kommune vil kolonnen være et polygon. Verdier i datatypen Geometry knyttes for øvrig til et såkalt referansesystem, les mer om dette i dokumentasjonen for PostGIS.


CREATE TABLE Fjelltopp
(
  Id INTEGER,
  HøydeOverHavet INTEGER,
  Geometri GEOMETRY,
  PRIMARY KEY (Id)
);

CREATE TABLE Elv
(
  Id INTEGER,
  Navn VARCHAR(100),
  Geometri GEOMETRY,
  PRIMARY KEY (Id)
);

CREATE TABLE Kommune
(
  Id INTEGER,
  KommuneNr VARCHAR(4),
  Navn VARCHAR(100),
  Geometri GEOMETRY,
  PRIMARY KEY (Id)
);

Oppgave 3

Studieomtaler i MongoDB.

Innholdet på XML-filen laget til oppgave 1 i kapittel 14 lar seg oversette relativt direkte til JSON. XML-elementer med flere forskjellige "barn" blir til JSON-objekter, mens XML-elementer med et antall barn av samme "type" blir håndtert ved JSON-arrays. BSON er en "binærversjon" av JSON og det finnes verktøy som konverterer fra JSON til BSON. Metodene i JSON-API'et til MongoDB kan kalles med JSON-verdier, men data blir lagret på BSON-format.

Et forslag til JSON-fil: studiehandbok.json

Oppgave 4

MongoDB dokumentdatabase for golfapplikasjon.

Det virker naturlig å lage følgende dokumentsamlinger:

Bane med opplysninger om BaneNr, Navn og Adresse samt en array av Hull-objekter, der vi om hvert hull tar vare på HullNr, Par og Avstand.
Spiller med opplysninger om SpillerNr, Fornavn, Etternavn, Tlf og BaneNr.
Runde med opplysninger om RundeNr, Dato og BaneNr samt en array av Resultat-objekter, der hver resultat består av et SpillerNr, et HullNr og AntallSlag.

Utdrag av eksempeldata for en golfrunde:


{
  "RundeNr": 173,
  "Dato": "2020-05-16",
  "BaneNr": 23,
  "Resultater":
  [
    {
      "SpillerNr": 562,
      "HullNr": 1,
      "AntallSlag": 5
    },
    {
      "SpillerNr": 287,
      "HullNr": 1,
      "AntallSlag": 4
    }
  ]
}

Oppgave 5

JavaScript-spørringer mot golfdatabasen i oppgave 4.

Løsningsforslag er ikke laget ennå.

5a

5b

5c

5d

5e

Oppgave 6

Sykkelutleie som Neo4j grafdatabase.

En relasjonsdatabase kan importeres i Neo4j på flere måter. Les mer i denne innføringen.

Steg 1. Eksporterer til CSV-filer fra MySQL-tabeller.

Dette steget gjøres fra MySQL.


(SELECT 'KNr', 'Fornavn', 'Etternavn', 'Mobil')
UNION 
SELECT * FROM Kunde
INTO OUTFILE 'C:/tmp/Kunde.csv' 
FIELDS ENCLOSED BY '' 
TERMINATED BY ';' 
ESCAPED BY '"' 
LINES TERMINATED BY '\r\n';

(SELECT 'MNr', 'Fabrikk', 'Betegnelse', 'Kategori', 'Dagpris')
UNION 
SELECT * FROM Modell
INTO OUTFILE 'C:/tmp/Modell.csv' 
FIELDS ENCLOSED BY '' 
TERMINATED BY ';' 
ESCAPED BY '"' 
LINES TERMINATED BY '\r\n';

(SELECT 'MNr', 'KopiNr', 'Ramme', 'Farge')
UNION 
SELECT * FROM Sykkel
INTO OUTFILE 'C:/tmp/Sykkel.csv' 
FIELDS ENCLOSED BY '' 
TERMINATED BY ';' 
ESCAPED BY '"' 
LINES TERMINATED BY '\r\n';

(SELECT 'KNr', 'MNr', 'KopiNr', 'TidUt', 'TidInn')
UNION 
SELECT * FROM Utleie
INTO OUTFILE 'C:/tmp/Utleie.csv' 
FIELDS ENCLOSED BY '' 
TERMINATED BY ';' 
ESCAPED BY '"' 
LINES TERMINATED BY '\r\n';

Steg 2. Importerer CSV-filer til Neo4j.

Kommandoene herfra og ut kjøres fra Neo4j.

Start med å flytte CSV-filene fra C:/tmp (se forrige steg) til en web-tjener. Erstatt URL-en www.xyz.no med URL-en til CSV-filene i følgende LOAD-kommandoer:


USING PERIODIC COMMIT
LOAD CSV WITH HEADERS FROM "https://www.xyz.no/Kunde.csv" AS row
FIELDTERMINATOR ';'
CREATE (:Kunde {kNr: row.KNr, fornavn: row.Fornavn, etternavn: row.Etternavn, mobil: row.Mobil});

USING PERIODIC COMMIT
LOAD CSV WITH HEADERS FROM "https://www.xyz.no/Modell.csv" AS row
FIELDTERMINATOR ';'
CREATE (:Modell {mNr: row.MNr, fabrikk: row.Fabrikk, betegnelse: row.Betegnelse, kategori: row.Kategori, dagpris: row.Dagpris});

USING PERIODIC COMMIT
LOAD CSV WITH HEADERS FROM "https://www.xyz.no/Sykkel.csv" AS row
FIELDTERMINATOR ';'
CREATE (:Sykkel {mNr: row.MNr, kopiNr: row.KopiNr, ramme: row.Ramme, farge: row.Farge});

USING PERIODIC COMMIT
LOAD CSV WITH HEADERS FROM "https://www.xyz.no/Utleie.csv" AS row
FIELDTERMINATOR ';'
CREATE (:Utleie {kNr: row.KNr, mNr: row.MNr, kopiNr: row.KopiNr, tidUt: row.TidUt, tidInn: row.TidInn});

Steg 3. Oppretter nodenøkler.


CREATE CONSTRAINT ON (k:Kunde)
ASSERT (k.kNr) IS NODE KEY;

CREATE CONSTRAINT ON (m:Modell)
ASSERT (m.mNr) IS NODE KEY;

Sykkel har et identifiserende forhold til Modell i MySQL, og har en sammensatt primærnøkkel, delvis arvet fra Modell. Vi kunne laget en sammensatt nodenøkkel slik:


CREATE CONSTRAINT ON (s:Sykkel)
ASSERT (s.mNr, s.kopiNr) IS NODE KEY;

Men mNr vil etter hvert bli erstattet av en kant – og det ser ikke ut til at man kan opprette sammensatte nodenøkler der en av "komponentene" er en kant.

Vi lager heller ikke nodenøkkel for Utleie, av samme grunn. Både kNr og (mNr, kopiNr) vil bli erstattet av kanter. En direkte oversatt nodenøkkel (fra tilsvarende primærnøkkel i MySQL) ville ha sett slik ut:


CREATE CONSTRAINT ON (u:Utleie)
ASSERT (u.kNr, u.mNr, u.kopiNr, u.tidUt) IS NODE KEY;

Steg 4. Oppretter indekser for fremmednøkler.

Hvis vi erstatter attributtene som tilsvarer fremmednøklene med kanter (se lenger ned), så vil disse indeksene også bli fjernet og man kan diskutere om dette steget er hensiktsmessig.


CREATE INDEX ON :Sykkel(mNr);
CREATE INDEX ON :Utleie(kNr);
CREATE INDEX ON :Utleie(mNr, kopiNr);

Steg 5. Oppretter kanter ("forhold") for å erstatte en-til-mange forhold.


MATCH (s:Sykkel),(m:Modell)
WHERE s.mNr = m.mNr
CREATE (s)-[:HAR]->(m);

MATCH (u:Utleie),(k:Kunde)
WHERE u.kNr = k.kNr
CREATE (u)-[:TILHØRER]->(k);

MATCH (u:Utleie),(s:Sykkel)
WHERE u.mNr = s.mNr AND u.kopiNr = s.kopiNr 
CREATE (u)-[:AV]->(s);

Steg 6. Fjerner "fremmednøkler" for en-til-mange forhold.

Først indeksene:


DROP INDEX ON :Sykkel(mNr);
DROP INDEX ON :Utleie(kNr);
DROP INDEX ON :Utleie(mNr, kopiNr);

Så selve "fremmednøklene" (dette er altså attributter som er erstattet av kanter):


MATCH (s:Sykkel)
REMOVE s.mNr;

MATCH (u:Utleie)
REMOVE u.kNr;

MATCH (u:Utleie)
REMOVE u.mNr;

MATCH (u:Utleie)
REMOVE u.kopiNr;

Oppgave 7

T-banenett som Neo4j grafdatabase.

Forklarer tankegangen bak datamodellen med tekst, se også CREATE-kommandoer under oppgave 8a.

Vi kan få til dette ved å innføre nodetyper Stasjon, Stoppested og Rute. Stoppestedene langs ei rute organiseres som ei lenka liste, der ei Rute "peker på" første Stoppested, og der hvert Stoppested peker på neste Stoppested. Flere ruter kan være innom samme stasjon, så det virker fornuftig å lagre navn og koordinater i en egen nodetype Stasjon, og legge til en kant fra Stoppested til Stasjon.

For enkel "tilgang" på opplysninger om ei rute fra hvert stoppested, virker det naturlig å leggee til en kant fra Stoppested til Rute. Stoppested-nodene danner altså ei lenka liste, der hver node også peker tilbake på Rute-noden. Denne ekstrapekeren utnyttes blant annet i oppgave 8b.

Ei Rute blir kjørt flere ganger i løpet av en dag, kanskje med litt forskjellige "etappetider". Avgangsklokkesletter fra hvert stoppested skal lagres. Dette håndteres ved en array av klokkesletter i hver Stoppested-node.

Oppgave 8

Cypher-spørringer mot T-banedatabasen i oppgave 7.

Merk: Arbeid pågår med løsningene til denne oppgaven, de er ikke testet ennå – og må foreløpig betraktes som skisser!

8a

T-banenett med tre stasjoner og ei rute som stopper på disse tre stasjonene.


CREATE (:Rute {navn: 'Ringruta'};

CREATE (:Stoppested {id:1, avganger:[10:30, 14:45]})
  -[:VED]->(:Stasjon {navn:'Tonsen', x:23.47, y:52.77 });
CREATE (:Stoppested {id:2, avganger:[10:42, 14:59]})
  -[:VED]->(:Stasjon {navn:'Midtstua', x:24.14, y:52.06});
CREATE (:Stoppested {id:3, avganger:[10:57, 15:13]})
  -[:VED]->(:Stasjon {navn:'Allerød', x:24.35, y:52.29});

MATCH (r:Rute) (ss:Stoppested)
WHERE r.navn = 'Ringruta' AND ss.id = 1
CREATE (r)-[:STARTER]->(s);

MATCH (r:Rute) (ss:Stoppested)
WHERE r.navn = 'Ringruta' AND ss.id = 1
CREATE (s)-[:TILHØRER]->(r);

MATCH (ss1:Stoppested),(ss2:Stoppested)
WHERE ss1.id = 1 AND ss2.id = 2
CREATE (ss1)-[:NESTE]->(ss2);

MATCH (r:Rute) (ss:Stoppested)
WHERE r.navn = 'Ringruta' AND ss.id = 2
CREATE (s)-[:TILHØRER]->(r);

MATCH (ss2:Stoppested),(ss3:Stoppested)
WHERE ss2.id = 2 AND ss3.id = 3
CREATE (ss2)-[:NESTE]->(ss3);

MATCH (r:Rute) (ss:Stoppested)
WHERE r.navn = 'Ringruta' AND ss.id = 3
CREATE (s)-[:TILHØRER]->(r);

8b

Alle ruter som går mellom to bestemte nabostasjoner.

Velger stasjonene Tonsen og Midtstua, som vi vet er naboer på Ringruta. Skisse til løsning – som med eksempeldata fra oppgave 8a skal levere "Ringruta":


MATCH (ss1:Stoppested)-[:VED]->(s1:Stasjon {navn:'Tonsen'}),
  (ss2:Stoppested)-[:VED]->(s2:Stasjon {navn:'Midtstua'}),
  (ss1)-[:NESTE]->(ss2),
  (ss1)-[:TILHØRER]->(r),
  (ss2)-[:TILHØRER]->(r),
RETURN r.navn;

8c

Fjerner en gitt stasjon fra samtlige ruter.

En gitt stasjon kan være stoppested på mange ruter. For hvert stoppested må vi først finne forrige stoppested og neste stoppested. La oss kalle disse tre stoppestedene for ss1, ss2 og ss3, der ss2 er stoppestedet som skal slettes. Det betyr at ss1 har en NESTE-peker til ss2 og ss2 har en NESTE-peker til ss3. Disse to pekerne må erstattes av en ny NESTE-peker fra ss1 til ss3. Da er ikke lenger ss2 med på ruta. Vi velger å slette stasjon Midtstua og erstatter først NESTE-pekerne som nettopp forklart:


MATCH (ss1:Stoppested)-[n1:NESTE]->
  (ss2:Stoppested)-[:VED]->(s2:Stasjon {navn:'Midtstua'}),
  (ss2)-[n2:NESTE]->(ss3:Stoppested)
CREATE (ss1)-[n:NESTE]->(ss3)
DELETE n1, n2

Når vi har sørget for at alle rutene nå går "forbi" stoppested Midtstua, kan både Stasjon-noden, alle Stoppested-nodene og alle relasjoner til disse nodene fjernes med DETACH DELETE:


MATCH (ss:Stoppested)-[:VED]->(s:Stasjon {navn:'Midtstua'})
DETACH DELETE ss, s;

8d

Flytter alle avgangstider for en gitt rute 10 minutter fram.

Løsningsforslag er ikke laget ennå.

8e

Finner den raskeste reiseruten mellom to T-banestasjoner på et gitt klokkeslett.

Løsningsforslag er ikke laget ennå.