Gamle obliger laget av meg og Tom Heine Nätt

Endel kommentarer, innleveringsfrister og noen oppgaver er tatt bort

2005

Oblig 1

1. Fila coi_freelance.sql inneholder kode for å opprette og legge inn data i tre tabeller.
   Tabellene inneholder informasjon om freelance-journalister(freelancere), hvilke aviser de jobber for og hva slags spesialiteter de har.
   Legg inn tabellene i databasen deres på SQL Server eller Postgres, og skriv spørringene som gjør følgende:
   1. List opp all informasjon om alle freelancere
   2. List opp pers_id på alle freelancere
   3. List opp navn på alle aviser. Hver avis skal bare vises en gang
   4. List opp navnene på alle journalistene i VG
      Skriv tre spørringer; en som bruker vanlig kobling, en som bruker WHERE ... IN og en som bruker WHERE EXISTS
   5. List opp fornavn og etternavn på alle freelancere som ikke jobber i VG
   6. List opp alle freelancere som ikke har noe etternavn
   7. List opp alle freelancere som har Olsen i etternavnet sitt
   8. List opp alle freelancere som har akkurat Olsen som etternavn
   9. List opp alle freelancere med fornavn som begynner på 'An'
   10. List opp alle freelancere med etternavn som inneholder 'ls'
   11. List opp alle freelancere med både har fornavn som begynner på 'An' og etternavn som slutter på 'sen'
   12. Hvilke(n) avis mangler freelancere som jobber med sport
       Skriv to spørringer; en som bruker WHERE ... IN og en som bruker WHERE EXISTS
   13. Skriv ut freelancers fornavn, etternavn, avisnavn og spesialitet for alle freelancere
       
2. Lag deg en database med minst to tabeller i relasjon til hverandre, for eksempel

   • cd-register
     
     Bør minst ha en tabell for generell cd-info og en for spor. Kan utvides med tabeller for artist og sjanger.
   • Gartneri med blomster
   • ferie- og fraværsregister
     
     1 tabell over ansatte, 1 over "events" og 1 som linker ansatte til et event i en viss periode
   • Idrettslag med utøvere og trenere
   • Idrettslag med grener(forball,håndball,...) og ledere
   • nettbutikk med leverandører og varer
     En tabell for leverandører, en for vare og en som knytter varer til leverandører.
     Hvis man vil kan man ta med kunde og bestilling også.

   Fyll databasen med litt data så du har noe å spørre på
   Lag 20 forskjellige queries/spørringer som bruker det du har lært hittil.
   Ta med type spørring, hva du skal gjøre, selve spørringen og resultatet.
3. Finn 14 feil i dataene nedenfor. Forklar hva som er feil og hva som må gjøres for å rette opp feilen(NB! det er ingen feil i sql-en)

           CREATE TABLE inngjerding(
             inngjerding_id INTEGER NOT NULL,
             areal_kvm INTEGER,
             info VARCHAR(75),
             PRIMARY KEY(inngjerding_id)
           );
   
           CREATE TABLE dyr(
             dyr_id INTEGER NOT NULL,
             navn VARCHAR(25),
             sikkerhetsgrad INTEGER NOT NULL,
             type VARCHAR(15),
             fodt DATE,
             kjonn VARCHAR(4),
             vekt_kg FLOAT,
             inngjerding_id INTEGER NOT NULL,
             info VARCHAR(40),
             PRIMARY KEY(dyr_id),
             FOREIGN KEY(inngjerding_id) REFERENCES inngjerding,
             CONSTRAINT sjekkSikkerhetsgrad
                   CHECK(sikkerhetsgrad BETWEEN 1 AND 10),
             CONSTRAINT sjekkKjonn
                   CHECK(kjonn = 'Hann' OR kjonn = 'Hunn')
           );
   
      NB: I MS SQL Server heter dato-datatypen datetime, og inneholder både dato og tidspunkt
   	EB  
       

   inngjerding
   inngjerding_id	areal_kvm	info
   1	2500000	'Ypperlig grøntområde for tiger, apekatt osv. Noe lite'
   2	'300 000 kvm'	' Tørt område, ørkenlikt '
   3	10	'kun for undulater og papagøyer. sikret med bur'
   2	2	'edderkopper, taranteller, sommerfugler, termitter osv og planter som disse liker kan stues inn her. bur sikret med glass. '
   5	10000000	'For ulv og vilt. naturlig villskog'
   6	5	'bur for smågnagere

   dyr
   dyr_id	navn	sikkerhetsgrad	type	fodt	kjonn	vekt_kg	inngjerding_id	info
   1	'Salto'	1	'Tiger'	'1988-01-01'	'Hann'	230.0	1	'Agressiv på gamle dager'
   2	'Pippi'	10	Undulat		'Hunn'	0.5	3	'Veldig lystig. plystrer la det swinge'
   3	'Kaptein Krok'		'Krokodille'	'November 1, 2000'	'Hann'	150.3	4	'Må ha daglig tilsyn pga et skadet bein'
   4	'Skalle'	5	'Skilpadde'	'1975-10-10'	'Male'	30,7	5	'Kan bite hvis han blir sliten'
   5	'Baltazar'	0	'Tiger'	'2003-04-15'	'Hann'	10.0	1	'Følger alltid sin mor, litt utrygg ennå'
   6	'Timbala'	1	'Tiger'	'juli 27, 1992'	'Hunn'	200.2	1	'Må vokte hennes bevegelser nøye når ungen skal vaksineres ol.'
   7	'Hilde'	7	'Ulv'	'1997-12-01'	Hunn	ukjent	5	'Trenger en make'

4. Finn eksempler på nettsteder og programvare som bruker databaser.
   Forklar enkelt hva nettestedene eller programvaren bruker databaser til og hvordan de gjør det.

Oblig 2

Opprett følgende tabeller med gitte primær- og fremmednøkler

1. Diverse SQL

   1. Hent ut all informasjon om alle barnehager
   2. Hent ut personnr og navn på alle ansatte
   3. Hent ut navn på barnehage med organisasjonsnr lik 967534543
   4. Hent ut navn på alle ansatte uten utdannelse
   5. Hent ut personnr for Inge Trulsen
   6. Hent ut navn og intern_tlf for avdelinger med navn som inneholder 'Fiol' eller 'fiol'
   7. Hent ut informasjon om alle barnehager sortert på navn
   8. Hent ut informasjon om alle ansatte sortert på barnehage og deretter avdeling
   9. Hent ut antallet ansatte for barnehagen som har færrest ansatte
   10. Hent ut navnet på de(n) avdeling(-ene/-en) som har flest ansatte
   11. Hent navn og stilling for alle ansatte i 'Solgløttheimen barnehage'
   12. Hent ut navnet på alle ansatte som ikke jobber i Solgløttheimen barnehage
   13. Hent ut gjennomsnittlig antall plasser i avdelinger(ikke pr. barnehage, men totalt)
       Endre overskriften til Gjennomsnittlig antall plasser
   14. Hent ut navn og stilling for alle ansatte som jobber på samme avdeling i en barnehage.
       Sorter på barnehage, deretter avdeling
   15. Hent ut navnet på den barnehagen som har flest plasser, og antallet plasser den har
   16. Hent ut id, navn og str_kvm for den avdelingen i hver barnehage som har størst plass pr. barn
   17. Hent ut navn og antall plasser for avdelinger i Solgløttheimen barnehage som har flere plasser enn hvertfall en annen avdeling innenfor den samme barnehagen

2. Svar på følgende spørsmål om SQL

   • Fortell kort om SQLs historie
   • Hvorfor er syntaxen i SQL så likt vanlig språk?
   • Nevn noen av de vanligste tingene en SQL query/spørring kan gjøre.
     Hvorfor er det en fordel at databasen gjør disse tingene istedenfor at man får overført alle rådata til sitt eget program og gjør bearbeidingen der?
     
     Hint: tenkt prosesseringstid, transmisjonstid, datakorrekthet og spesialisering på enkelte oppgaver
   • Hvorfor har vi kun ett spørrespråk som er en slags de facto-standard, når det finnes så mange forskjellige DBMS som kunne hatt sitt eget optimaliserte spørrespråk?
   • Hva menes med dialektforskjeller i SQL?

Oblig 3

1. Mer SQL: subqueries, gruppering, union, intersection, difference, alias

   Viktig: siden difference og intersection ikke er implementert i SQL Server, må denne bare skrives utifra boka og kan ikke testes
   Siden Postgres har implementert difference og intersection, kan de som bruker denne databasen få testet

   1. Skriv sql og resultat for oppgavene under.

      1. Skriv ut en liste med barnehagenavn, avdelings_id, navn og intern_tlf på alle avdelinger i alle barnehager
         Kall kolonnene navn i barnehage for 'Barnehage' og navn i avdeling for 'Avdeling'
      2. Skriv ut en liste med ansattnavn, barnehagenavn, avdelingsnavn og stilling på alle avdelinger i alle barnehager
         Kall kolonnen navn i ansatt for 'Ansatt sitt navn' og slå sammen navn i barnehage og navn i avdeling så det blir på formen 'Barnehage: Avdeling'
      3. Skriv ut en liste med hvor mange plasser hver barnehage har totalt
      4. Skriv ut navn, barnehage, avdeling, stilling og timelønn for de som har høyere eller lik timelønn enn gjennomsnittet på sin avdeling
         Sorter på barnehage, deretter avdeling.
         NB! Det er ikke lov å bruke et view for å løse oppgaven!

   2. Skriv sql og resultat for oppgavene under. Hvorfor blir resultatene som de blir?

      1. Skriv en union mellom to instanser av samme tabell.
         Kolonnen som skal hentes ut er navn, tabellen er barnehage
         (betyr at du skal: barnehage(navn) union barnehage(navn))
      2. Skriv en intersection mellom to instanser av samme tabell.
         Kolonnen som skal hentes ut er navn, tabellen er barnehage
      3. Skriv en difference mellom to instanser av samme tabell.
         Kolonnen som skal hentes ut er navn, tabellen er barnehage
      4. Bruk union til å finne de postnr som finnes enten i ansatt eller barnehage.
         
      5. Bruk intersection til å finne de postnr som finnes både i ansatt og barnehage.
         
      6. Bruk difference til å finne de postnr som finnes i ansatt, men ikke i barnehage.
         

2. Oppdatering og sletting

   1. Endre etternavnet til 'Solveig Kringvo' til 'Solmo'
   2. Endre åpningstid for alle barnehager så de er åpne 30 minutter lenger
   3. Slett alle ansatte i 'Dyreskogen barnehage'
   4. Slett alle data i alle tabeller
   5. Slett alle tabeller
   6. Opprett alle tabeller og data igjen for å fortsette på oppgave 3

3. Views

   1. Løs oppgave 1.1.d ved hjelp av et view
   2. 1. Lag et view som inneholder navn på barnehage, avdeling og gjennomsnittslønn for ansatte pr. avdeling
      2. Skriv ut alle data fra viewet
      3. Finn den avdelingen med høyest gjennomsnittslønn. skriv ut barnehagens navn, avdeling og gjennomsnittslønna
      4. Finn den barnehagen med høyest summert gjennomsnittslønn(sum alle avdelinger pr. barnehage)

   3. Dette skal alle gjøre(men les alternativene videre først!):

      1. Lag et view for lønns- og utdanningsinformasjon om barnehageansatte.
         Viewet skal inneholde organisasjonsnr, navn på barnehage, avdelings_id, personnr, navn på ansatt, stilling, ansatt_siden, utdannelse og timelønn
      2. Hent ut all informasjon fra viewet
      3. Hent ut gjennomsnittlig lønn for ansatte i 'Solgløttheimen barnehage'
      4. Endre avdelings_id for 'Eivind Gru' til 3
      5. Endre avdelings_id for 'Roberta Hugu' til 2
      6. Øk lønnen til alle i 'Dyreskogen barnehage' som har jobbet der mer enn 5 år(fra 01.01.2000) med 2 kroner 40 øre
      7. Prøv å legge inn disse dataene:
         

         967534543

         'Veivalget barnehage'

         2

         '140580 10305'

         'Eva Olsen'

         'Assistent'

         '2005-01-01'

         'Barne- og ungdomsarbeider'

         100.40

      For deloppgave 7:
      - Hva skjer når du prøver å legge inn dataene?
      - Hvorfor skjer dette?

      Slett og gjenopprett alle tabeller og data igjen for å fortsette på oppgave 4

4. Relasjonsalgebra - teori

   Lag en oversikt over sammenhengen mellom SQL og relasjonsalgebra

5. Relasjonsalgebra - praksis

   1. 
   2. 
   3. 

   For hver av uttrykkene nedenfor, svar på følgende:

   1. Hva gjør uttrykket?
   2. Skriv tilsvarende uttrykk som SQL
   3. Skriv ut resultatet

6. Systemtabeller

   1. Lag et view som viser en oversikt over alle tabeller og kolonner i din database
   2. Lag et select-setning som skriver ut alt fra utsnittet.
      Resultatet skal leveres inn
   3. (ekstra)Gjør oppg. b, men skriv i tillegg ut en (P) bak de kolonnene som er primærnøkler
      (Hint: ligger i sysindexes og sysindexkeys)
   4. Hva skjer hvis du skriver delete from sysobjects; (eller tilsvarende i andre systemer)?
7. (ekstra) Når vil du bruke en database istedenfor en fil(tekst, xml, binærfil), og når vil du velge motsatt?
   Begrunn svaret
   
   Hint: Tenk på hastighet, flerbrukere, recovery, transaksjoner, backup osv

Oblig 4

1. 1. Tegn datamodell for barnehagedatabasen
   2. Hvis en ansatt skal kunne jobbe på flere avdelinger i samme barnehage, hvordan vil det påvirke datamodellen og databasedefinisjonen?
      Tegn ny modell.
   3. Hvis en ansatt skal kunne jobbe i flere barnehager, hvordan vil det påvirke datamodellen og databasedefinisjonen?
      Tegn ny modell.
2. 1. Slett og gjenopprett alle data i barnehage-databasen
   2. Endre tabelldefinisjonen i barnehage(med ALTER TABLE) ved å legge til kolonnen pris_pr_plass. Verdien 0 skal legges inn for de gamle dataene(dvs skal være default verdi og settes til dette)
   3. Hent ut alle data fra tabellen barnehage
   4. Oppdater prisene: Solgløttheimen(2500), Dyreskogen(2750), Veivalget(3000), Blomsterenga(2850)
   5. Hent ut alle data fra tabellen barnehage
   6. Slett og gjenopprett alle data i barnehage-databasen
3. Lag et view som viser personnr, navn, barnehage, avdeling og ansatt_siden for alle ansatte
   
   1. Hent ut alle data fra viewet
   2. Prøv å legge inn følgende data, og forklar hva som skjer:

      personnr	navn	barnehage	avdeling	ansatt_siden
      '070178 74281'	'Heivor Hald'	963545782	3	'2000-05-01'
      '090569 19745'	'Kristoffer Skogen'	965647842	1	'2001-09-01'
      '020176 56321'	'Even Bentersen'			'2002-10-01'
      '090777 65346'	'Jens Ovesen'	967845325	2	'2002-07-04'
      	'Anne Pettersen'	967845325	2	'2003-01-01'

   3. Hent ut alle data fra viewet og tabellen ansatt(2 spørringer)
   4. Sett navn for 'Hanne Jansen' til 'Hanne J. Hansger'
   5. Sett barnehage lik NULL for personnr '16047862304'
   6. Sett avdeling lik 5 for 'Inge Trulsen'
   7. Hent ut alle data fra viewet og tabellen ansatt(2 spørringer)
4. 1. Tegn ditt eget slektstre.
      
   2. Lag CREATE TABLE og INSERT INTO for treet(1 tabell, husk at folk kan ha like både fornavn og etternavn!, kjønn skal ikke lagres)
      Hint: Sirkulære referanser(=foreign key til egen tabell)
   3. Lag følgende spørringer
      1. Hent ut alle data fra tabellen
      2. Finn hvem som er foreldre til hvem. Kolonnenavn skal bli Person, Mor og Far og inneholde fullt navn på hver av de
      3. Finn hvem som er besteforeldre til hvem. Kolonnenavn skal bli Person, Mormor, Morfar, Farmor og Farfar og inneholde fullt navn på hver av de(litt vanskelig, men prøv hvertfall og forklar hva dere har tenkt hvis dere ikke får den til)
      4. Finn røttene i treet(deg selv+evt. barnløse personer)
      5. Finn alle blader i treet(de som mangler mor eller far) og hvem de mangler(Begge, Mor eller Far) TIPS: CASE
      6. Finn antall barn hver har
      7. Lag et view som viser navn og kjønn for alle personer. Kjønn kan være enten Mann, Kvinne eller Ukjent TIPS: CASE
5. Lag en datamodell for et bibliotek.
   Biblioteket skal ha flere avdelinger(med id, navn og info), bøker(med bibliotekid, isbn, tittel, forfatter(flere), utgitt_år og forlag), lånere(med id, fødselsdato, navn, adresse, postnr og sted), sjanger(med id og navn), reservasjon(med id, bok, låner, reservasjonsdato), utlån(med uttid, innfrist, inndato og purring(flere)) og ansatte(med id og navn)
   Ansatte og bøker skal være koblet til en avdeling, mens lånere er tilknyttet hele biblioteket
   Legg inn egne entiteter og attributter etterhvert som du får behov for dem, og beskriv de forutsetninger du gjør underveis (linja over er et eksempel på en forutsetning).

Oblig 5 og 6

Del 1

1. 1. Bilregister.

      Vi har et register over biler, eiere m.m.

      Chassisnr	Kjennetegn	Merke	Type	Årsmodell	Eiers_personnr	Eiers_navn
      787827342	AR-12345	Ford	Escort	1994	12345678901	Jan Ek
      787923423	AD-98765	Opel	Corsa	1998	23456789012	Ole Ek
      899231234	JU-78834	Volvo	242	1979	34567890123	Tor Ek

      1. Vi antar at eier gjelder nåværende eier, og at en bil ikke kan eies av mer enn en person. Hva kan/bør du velge som primærnøkkel her? Finnes det flere alternativer?
      2. Finn ut hvilken normalform som tabellen er på. Ta forutsetning om en gitt biltype bare tilhører ett merke (f.eks. kan ikke Escorter bli produsert av både Ford og Opel).
      3. Gjør om strukturen slik at den tilfredsstiller alle normalformer.
      4. En forenklet måte for å sjekke normalisering på, er å bruke kriteriet for BCNF direkte. Gjør dette i stedet for å drøfte hver normalform.
      5. Ta utgangspunkt i den opprinnelige tabellen. Anta i stedet at du systemet skal inneholde alle eierne som bilen har hatt. Analyser normalform på nytt, konkluder med normalform og gjør eventuelle splittinger som er nødvendig for at strukturen skal være godt normalisert.

   2. Vedlikehold av maskindeler.

      Et første forsøk på stuktur er dette:

      Delnr

      Delnavn

      Maskinnr

      Siste_vedlikeholdsdato

      Vedlikeholdsintervall

      Et tilleggsproblem er også at det kan finnes flere like deler i samme maskin, men som vedlikeholdes til forskjellige tider.

      1. Avgjør hvilken normalform den gitte stukturen er på.
      2. Normaliser denne strukturen mest mulig.
      3. Legg inn tilleggsproblemet som nevnt over, foreslå på nytt en struktur.

2. Hvorfor denormaliserer man innimellom?

3. Hvorfor er det en fordel å modellere før man lager databasen?

Del 2

1. Datamodell

   a) Lag en normalisert datamodell for systemet du vi arbeide med.
   • For enpersonsgrupper skal modellen inneholde minimum 6 entitetstyper
   • For flerpersongrupper skal modellen inneholde ca. 4 entitetstyper pr. gruppemedlem.
   • Minst en av entitetstypene skal ha en sammensatt identifikator

   b) Forklar (min 1/2 - 1 side, gjerne mer) forutsetninger som du har lagt til grunn og/eller deler hvor du så ulike muligheter for design

   c) Gjør følgende med datamodellen deres:

   • Slå sammen 2 entitetstyper slik at du bryter med 1. normalform.
     Forklar problemet med å ha modellen slik.
   • Slå sammen 2 entitetstyper slik at du bryter med 2. normalform.
     Forklar problemet med å ha modellen slik. Hint: en av de opprinnelige entitetene må da ha en sammensatt primærnøkkel.
   • Slå sammen 2 entitetstyper slik at du bryter med 3. normalform.
     Forklar problemet med å ha modellen slik.
   • (ekstra)Finner du noen kombinasjonsmuligheter i din modell som kan bryte med BCNF?

   (Disse sammenslåingene skal ikke brukes senere i oppgaven.)

2. Opprett alle tabeller og legg inn data

   Tenk på hva du skal bruke databasen til(php, javaprogram, ...) når du velger testdata. Lag noen spørringer som er logiske mhp bruksområder, og lag evt. view(s) ved behov.
   

3. Lag et www-grensesnitt eller et program som bruker databasen fra oppgave 1 (leveres uke 6).
   Minimumskravet er at programmet/grensesnittet skal kunne koble seg til databasen og hente ut data, men det bør også kunne legge til og slette elementer, vise sorterte lister med forskjellige sorteringsvalg og foreta søk med begrensning. Dere trenger ikke legge mye vekt på design/utseende.

Oblig 6

1. Lag et www-grensesnitt eller et program som bruker databasen i oblig 5.
   Minimumskravet er at programmet/grensesnittet skal kunne koble seg til databasen og hente ut data, men det bør også kunne legge til og slette elementer, vise sorterte lister med forskjellige sorteringsvalg og foreta søk med begrensning. Dere trenger ikke legge mye vekt på design/utseende
2. For de som har valgt minimumsløsninger, og ellers de som har lyst:
   Hvorfor er det laget noen generelle kommunikasjonsmetoder mot dbms(for eksempel jdbc, odbc osv)?
   Hint: tenk på bl.a. programmering mot database og skifte av database