EDAF75 - lösningsförslag till tentamen i mars 2026

Vi ville ha något i stil med:

Det finns flera alternativa lösningar, exempelvis kan vi byta ut vår 1 - 0..1 mellan Exemplar och Köp mot ett antal attribut i Exemplar, detta innebär att vi kan ta bort tabellen Köp (men vi får i så fall hålla koll på flera NULL-värden i Exemplar).

Ett exempel på implementation av modellen ovan:

  DROP TABLE IF EXISTS böcker;
  CREATE TABLE böcker (
    isbn         TEXT,
    titel        TEXT,
    rek_pris     INT,
    PRIMARY KEY  (isbn)
  );

  DROP TABLE IF EXISTS exemplar;
  CREATE TABLE exemplar (
    qr_kod       BLOB,
    isbn         TEXT,
    status       INT,
    säljar_id    TEXT,
    PRIMARY KEY  (qr_code),
    FOREIGN KEY  (isbn) REFERENCES böcker(isbn),
    FOREIGN KEY  (säljar_id) REFERENCES studenter(stil_id)
  );

  DROP TABLE IF EXISTS studenter;
  CREATE TABLE studenter (
    stil_id      TEXT,
    namn         TEXT,
    adress       TEXT,
    PRIMARY KEY  (stil_id)
  );

  DROP TABLE IF EXISTS köp;
  CREATE TABLE köp (
    qr_kod       BLOB,
    köpar_id     TEXT,
    pris         INT,
    datum        DATE,
    PRIMARY KEY  (qr_kod),
    FOREIGN KEY  (qr_kod) REFERENCES exemplar(qr_kod),
    FOREIGN KEY  (köpar_id) REFERENCES studenter(köpar_id)
  );

  DROP TABLE IF EXISTS kurser;
  CREATE TABLE kurser (
    kurskod      TEXT,
    kursnamn     TEXT,
    PRIMARY KEY  (kurskod)
  );

  DROP TABLE IF EXISTS kursböcker;
  CREATE TABLE kursböcker (
    kurskod      TEXT,
    isbn         TEXT,
    obligatorisk BOOLEAN,
    PRIMARY KEY  (kurskod, isbn),
    FOREIGN KEY  (kurskod) REFERENCES kurser(kurskod),
    FOREIGN KEY  (isbn) REFERENCES böcker(isbn)
  );

Utgående från de tabeller du skapar i uppgift (a), skriv SQL-satser som skriver information om varje köp under 2026 som gäller böcker som används som ’bredvidläsning’ i kursen EDAF99. Utskriften skall innehålla köparnas namn, titlarna på böckerna, och de priser de betalade (du kan använda funktionen year() för att få året för ett datum).

  SELECT     namn, titel, pris
  FROM       köp
             JOIN studenter ON köpar_id = stil_id
             JOIN exemplar USING (qr_kod)
             JOIN böcker USING (isbn)
  WHERE      year(datum) = 2026 AND
             isbn IN (
                 SELECT    isbn
                 FROM      kursböcker
                 WHERE     kurskod = 'EDAF99'
                           AND NOT obligatorisk
             );

Rita ett ER-diagram i UML-format för att beskriva databasen, rita diagrammet enligt anvisningarna i problem 1a.

Vi hade inte markerat främmande nycklar i uppgiften, men det är ganska uppenbart vilka attribut som är det (och det finns i uppgiftstexten en kommentar om att employee_id i TestLeader pekar på motsvarande attribut i test_leaders.

Ett sätt att rita detta ER-diagram är (här har vi ritat ut invented keys, vi skulle även kunna utelämna dem):

Skriv ut första behandlingsdatum, namn och behandlande läkare (alltså namnet på den som är test-ansvarig) för alla de patienter som behandlas med ’antibiotikum-5’ mot ’bakterie-C’ – ordna efter i första hand första behandlingsdatum, och i andra hand efter patientens namn.

SELECT    initial_date, patient_name, employee_name
FROM      treatments
          JOIN patients USING (patient_id)
          JOIN test_leaders USING (employee_id)
WHERE     bacterium_name = 'bakterie-C' AND
          antibiotic_name = 'antibiotikum-5'
ORDER BY  initial_date, patient_name;

Skriv ut namn och tillverkare för de antibiotika som inte har använts i någon behandling.

  SELECT    antibiotic_name, maker
  FROM      antibiotics
  WHERE     antibiotic_name NOT IN (
      SELECT    antibiotic_name
      FROM      treatments
  );

Skriv ut id-nummer, namn och adress för de patienter som fått mer än en behandling.

  SELECT    patient_id, patient_name, address
  FROM      patients
            JOIN treatments USING (patient_id)
  GROUP BY  patient_id
  HAVING    count() > 1;

För varje bakterie och varje antibiotikum som använts mot bakterien: skriv ut antalet behandlingar som gjorts, och den sämsta och bästa effekt man har uppmätt. För de bakterier som inte har behandlats alls vill vi ha texten ’ej behandlad’, och värdet 0 på både bästa och sämsta effekt i utskriften. Ordna i första hand på bakteriens namn, och i andra hand på den bästa sämsta-effekt man uppnått med det givna antibiotikat.

SELECT    bacterium_name AS 'Bakterie',
          coalesce(antibiotic_name, 'ej behandlad') AS 'Antibiotika',
          count() AS 'Antal behandlingar',
          coalesce(min(effect), 0) AS 'Sämsta effekt',
          coalesce(max(effect), 0) AS 'Bästa effekt'
FROM      bacteria
          LEFT JOIN treatments USING (bacterium_name)
GROUP BY  bacterium_name, antibiotic_name
ORDER BY  bacterium_name, 'Sämsta effekt' DESC;

Vi hade attributen

• spelår
• serie
• lag
• spelare
• tröjnummer

och får följande funktionella beroenden:

1. lag spelår -> serie
2. spelare spelår -> lag
3. lag spelår spelare -> tröjnummer
4. lag spelår tröjnummer -> spelare

Vi har

R(A,B,C,D,E,F)

och

• FD1: AB -> C
• FD2: BD -> E
• FD3: AD -> BF
• FD4: CD -> A

Attributet D måste ingå i varje nyckel, eftersom det inte finns i något högerled. När vi utvecklar det transitiva höljet av D så får vi inget nytt:

• D -> D

Så D är själv ingen nyckel.

Vi testar därefter alla tvåattributsmängder med D:

• AD -> ADBFEC
• BD -> BDE
• CD -> CDABFE
• DE -> DE
• DF -> DF

Så, AD och CD är nycklar, men vi måste även pröva möjliga nycklar med fler attribut (dvs D men vare sig A eller C):

• BDE -> BDE
• BDF -> BDEF

Vi får inga nycklar här, men måste även testa eventuella fyr-attributnycklar:

• BDEF -> BDEF

Så, AD och CD är våra enda nycklar.

Vi är inte i BCNF, eftersom FD1 och FD2 har vänsterled som inte är supernycklar.

Hela poängen med normaliseringen är att vi får ett antal tabeller som vi kan join-a ihop igen, till precis samma rader som vi hade från början. Och det är det som är poängen med den metod att normalisera som vi diskuterade under föreläsning 7-8 – att bara skriva upp ett antal relationer som var och en är i BCNF visar inte att vi kan återskapa ursprungstabellen utan förvrängning, så man får inga poäng om man inte följer ’receptet’.

Vi börjar alltså med:

  R(A,B,C,D,E,F)
  --------------
  fd: AB -> C, BD -> E, AD -> BF, CD -> A
  keys: AD, CD

Vi kan bryta upp R på FD1 (AB -> C), och får då:

  R1(A,B,C)
  --------------
  fd: AB -> C
  keys: AB

och

  R2(A,B,D,E,F)
  --------------
  fd: BD -> E, AD -> BF
  keys: AD

Här är R1 i BCNF eftersom vårt enda funktionella beroende (AB -> C) har ett vänsterled som är en nyckel.

Men R2 har två beroenden, varav bara det ena (AD -> BF) har ett vänsterled som är en superkey, det andra beroendet (BD -> E) har ett vänsterled som kan förekomma på många rader i R2, så det bryter mot BCNF. Vi bryter därför upp R2 på BD -> E, och får:

  R2a(B,D,E)
  --------------
  fd: BD -> E
  keys: BD

och

  R2b(A,B,D,F)
  --------------
  fd: AD -> BF
  keys: AD

Här har både R2a och R2b bara beroenden vars vänsterled är supernycklar, så vi är i BCNF.

Våra relationer blir med denna normalisering:

• R1(A,B,C)
• R2a(B,D,E)
• R2b(A,B,D,F)

(men det viktigaste i uppgiften är alltså inte slutrelationerna, utan stegen dit).

Vi hade även kunnat välja att bryta upp på FD2 i första steget, och det finns ingen garanti för att vi får samma slutgiltiga relationer, men vi vet med säkerhet att dessa relationer kan JOIN-as ihop så att vi får tillbaka den ursprungliga relationen, utan att vare sig få nya rader, eller att tappa gamla rader.

Vi kan enkelt bygga tillbaka ursprungsrelationen R om vi använder uppdelningen ovan:

• R1(A,B,C)
• R2a(B,D,E)
• R2b(A,B,D,F)

Vi kan börja att join-a ihop R2b(A,B,D,F) och R2a(B,D,E) med (B,D) som join-nyckel – det ger oss tabellen R2(A,B,D,E,F), och med hjälp av (A,B) kan vi joina in E från R1. Så vi får:

  SELECT    *
  FROM      R2b
            JOIN R2a USING (B,D)
            JOIN R1 USING (A,B);