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

Tentamensuppgifterna finns här.

Problem 1

(a)

Vi ville ha något i stil med:

problem-1-er.svg

När vi implementerar denna modell kan vi antingen använda de naturliga nycklar som fanns i modellen, eller använda 'invented keys' – här är ett exempel på hur vi kan använda 'invented keys' (jag har samtidigt uppdaterat några av attributnamnen från modellen, för att göra några queries enklare): 

CREATE TABLE tavlor (
  tavel_id       INTEGER,
  titel          TEXT,
  år             INT NOT NULL,
  konstnärs_id   INTEGER,
  PRIMARY KEY    (tavel_id),
  FOREIGN KEY    (konstnärs_id) REFERENCES konstnärer(konstnärs_id)
  CONSTRAINT
    unique_name  UNIQUE (konstnärs_id, titel, år) ON CONFLICT ROLLBACK
);

CREATE TABLE konstnärer (
  konstnärs_id   INTEGER,
  konstnärsnamn  TEXT,
  födelseår      INT NOT NULL,
  dödsår         INT,
  telefon        TEXT,
  PRIMARY KEY    (konstnärs_id),
  CONSTRAINT
    unique_name  UNIQUE (konstnärsnamn)
);

CREATE TABLE kategorier (
  kategori_id   INTEGER,
  kategorinamn  TEXT,
  beskrivning   TEXT,
  PRIMARY KEY   (kategori_id),
  CONSTRAINT
    unique_name UNIQUE (kategorinamn)
);

CREATE TABLE kategoriseringar (
  tavel_id      INTEGER,
  kategori_id   INTEGER
);

CREATE TABLE kunder (
  kund_id      INTEGER,
  kundnamn     TEXT,
  kundemail    TEXT,
  PRIMARY KEY  (kund_id)
  CONSTRAINT
    unique_email UNIQUE (kundemail)
);

CREATE TABLE auktioner (
  auktions_id  INTEGER,
  tavel_id     INTEGER,
  minimipris   INT,
  tidpunkt     DATETIME,
  PRIMARY KEY  (auktions_id),
  FOREIGN KEY  (tavel_id) REFERENCES tavlor(tavel_id)
);

CREATE TABLE bud (
  bud_id        INTEGER,
  auktions_id   INTEGER,
  kund_id       INTEGER,
  belopp        INT,
  budtidpunkt   DATETIME,
  PRIMARY KEY   (bud_id),
  FOREIGN KEY   (auktions_id) REFERENCES auktioner(auktions_id),
  FOREIGN KEY   (kund_id) REFERENCES kunder(kund_id)
);

På svenska borde vi egentligen inte särskriva suffixen id, så det borde kanske heta tavelid snarare än tavel_id, men jag har behållit _id eftersom det gör nycklarna lite tydligare.

(b)

I och med att jag använde invented keys när jag implementerade min modell, så kommer jag att behöva join-a mer i denna uppgift: 

SELECT   kundnamn
FROM     kunder
         JOIN bud USING (kund_id)
         JOIN auktioner USING (auktions_id)
         JOIN tavlor USING (tavel_id)
         JOIN konstnärer USING (konstnärs_id)
WHERE    konstnärsnamn = 'Oddput Clementin';

Problem 2

(a)

Ett sätt att rita detta ER-diagram är:

problem-2-er.svg

Här har jag inte ritat ut våra 'invented keys', eftersom de är en implementationsdetalj (det är helt OK om ni ritat ut dem i era lösningar, det ger inget avdrag). Vi har heller inte skrivit ut några främmande nycklar, eftersom de redan är utritade som associationer i diagrammet.

(b)

Skriv ut platsnamn och datum för alla observationer av ’bofink’, sorterade i första hand efter platsnamn, i andra hand efter datum. 

SELECT    site_name, observation_date
FROM      observations
          JOIN sites USING (site_id)
          JOIN species USING (species_id)
WHERE     swedish_name = 'bofink'
ORDER BY  site_name, observation_date;

(c)

Skriv ut de latinska namnen på de arter i databasen som inte observerats någon gång under 2024 – du kan använda funktionen year för att få året för ett datum. 

SELECT    latin_name
FROM      species
WHERE     species_id NOT IN (
          SELECT   species_id
          FROM     observations
          WHERE    year(observation_date) = 2024
);

(d)

Skriv ut hur många observationer av de olika arterna som medlemmen ’Ulrika Vogel’ har gjort – du kan förutsätta att det bara finns bara en Ulrika Vogel i föreningen. Listan skall sorteras efter antal observationer, och bara innehålla arter som hon har observerat minst 10 gånger. 

SELECT     swedish_name, count()
FROM       observations
           JOIN species USING (species_id)
           JOIN members USING (member_id)
WHERE      member_name = 'Ulrika Vogel'
GROUP BY   species_id
HAVING     count() >= 10
ORDER BY   count() DESC, swedish_name;

(e)

För varje art som har observerats, skriv ut följande information om den/de senaste observationen/erna (alla observationer där datum är lika med datum för den senaste observationen av arten skall skrivas ut): svenskt artnamn, platsnamn, observatörsnamn och datum (för den senaste observationen). Ordna utskriften efter de svenska artnamnen, arter som inte har observerats skall inte tas med.

Vi kan använda en WITH-sats för att hålla reda på datum för senaste observationen av varje art, och jämföra datum i varje observation med detta datum: 

WITH most_recent_observations(species_id, most_recent_observation) AS (
  SELECT    species_id, max(observation_date)
  FROM      observations
  GROUP BY  species_id
)
SELECT   swedish_name, site_name, member_name, observation_date
FROM     observations
         JOIN most_recent_observations USING (species_id)
         JOIN species USING (species_id)
         JOIN sites USING (site_id)
         JOIN members USING (member_id)
WHERE    observation_date = most_recent_observation
ORDER BY swedish_name;

Vi skulle även kunna använda en subquery: 

SELECT   swedish_name, site_name, member_name, observation_date
FROM     observations AS obs
         JOIN species USING (species_id)
         JOIN sites USING (site_id)
         JOIN members USING (member_id)
WHERE    observation_date =
            (SELECT max(observation_date)
             FROM   observations
             WHERE  species_id = obs.species_id)
ORDER BY swedish_name;

Problem 3

(a)

Vi hade:

  • stil_id
  • ssn
  • student_name
  • course_code
  • course_name
  • grade
  • exam_date

och får följande funktionella beroenden:

  • ssn -> stil_id student_name
  • stil_id -> ssn
  • course_code -> course_name
  • ssn course_code -> grade exam_date
  • stil_id course_code -> grade exam_date

Man behöver inte komma på alla dessa för full poäng, man får 1 poäng för varje, och maximalt fyra poäng på uppgiften.

(b)

Vi har alltså:

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

och:

  • FD1: CE -> DF
  • FD2: AC -> B

Vi kan börja med att ta det transitiva höljet av vart och ett av dessa beroenden (det kan underlätta när vi skall beräkna nycklarna), men vi kommer inte längre för något av våra beroenden.

Vi har varken A, C eller E i något högerled, så de måste alla ingå i samtliga nycklar. Det transitiva höljet av ACE är ABCDEF, så vi har en nyckel, och eftersom ACE alla måste ingå, så är det den enda nyckel vi kan hitta (alla andra möjliga nycklar skulle vara superkey med minst ett 'onödigt' attribut).

Detta innebär att ACE är den enda möjliga nyckeln.

(c)

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

(d)

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 utgår alltså från R(A,B,C,D,E,F) och de funktionella beroenden som vi såg ovan:

  • FD1: CE -> DF
  • FD2: AC -> B

Vår enda nyckel är ACE, och vi kan sammanställa det som: 

R(A,B,C,D,E,F)
--------------
fds: CE -> DF, AC -> B
keys: ACE

Vi skall bryta upp på ett funktionellt beroende som bryter mot BCNF – vi gör det för att dess vänsterled kan förekomma flera gånger i tabellen, och att dess högerled kommer att upprepas varje gång (att bryta upp på ett beroende som är i BCNF, dvs har ett vänsterled som är en supernyckel, visar att man inte har förstått idén bakom normaliseringen – vi vill undvika redundans, men för ett beroende med en supernyckel i vänsterledet kan varje vänsterled bara förekomma en gång, och då får vi ingen redundans).

Här kan vi göra uppdelningen på två olika sätt, vi kan välja att bryta upp på FD1:

  • FD1 ger R1(C,E,D,F)
  • Återstoden av R innehåller alla attribut utom D och F (som vi nu kan hämta från R1): R2(A,B,C,E)

Här får vi: 

R1(C,E,D,F)
--------------
fds: CE -> DF
keys: CE

och 

R2(A,B,C,E)
--------------
fds: AC -> B
keys: ACE

I R1 är vi i BCNF, eftersom dess enda beroende har ett vänsterled som är en superkey, men för R2 har vi beroendet AC -> B som är problematiskt (vi behöver E i vår nyckel, så AC är ingen superkey, och kan alltså upprepas flera gånger – varje gång får vi samma redundanta värde på B).

Vi bryter därför upp en gång till: 

R2a(A,C,B)
--------------
fds: AC -> B
keys: AC

och 

R2b(A,C,E)
--------------
fds: {}
keys: ACE

och nu har båda våra relationer bara supernycklar i vänsterleden i sina beroenden.

Vi får alltså:

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

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

(e)

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

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

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

SELECT    *
FROM      R2b
          JOIN R2a USING (A,C)
          JOIN R1 USING (C,E)

Problem 4

Skriv en trigger som ser till att vi i databasen i uppgift 2 inte kan lägga in mer än tre observationer av en given art på en given plats en given dag, oavsett vem som gör observationerna.

Om vi testar innan vi gör insättningen, så får inte antalet observationer av den aktuella arten på den givna platsen den givna dagen vara mer än högst 3: 

CREATE TRIGGER observation_count_limit
BEFORE INSERT ON observations
WHEN
  (SELECT  count()
   FROM    observations
   WHERE   species_id = NEW.species_id
           AND site_id = NEW.site_id
           AND observation_date = NEW.observation_date) >= 3
BEGIN
  SELECT RAISE (ROLLBACK, 'too many observations');
END;

Problem 5

Vi vill läsa in data för en observation, och lägga in den i databasen. För att göra uppgiften lite enklare antar vi att både member_id och site_id är heltal, och att vi läser in nycklarna direkt i programmet. För arten läser vi in det latinska namnet som en sträng.

Lägg därefter in motsvarande observation i databasen – nyckeln (observation_id) och datumet (observation_date) får båda automatiskt startvärden när vi lägger in en rad i tabellen observations, datumet sätts till dagens datum.

Skriv sedan ut namn och telelfonnummer för samtliga medlemmar som observerat samma art på samma ställe (alltså även den nya observationen), skriv även ut antalet gånger de gjort observationen, och datum för den senaste av dessa observationer. Sortera medlemmarna efter när de gjorde sin senaste observation (den med mest aktuell observation skall komma först).

Om något går fel, exempelvis att platsen eller arten inte finns, eller att vi redan har tre observationer av arten på platsen denna dag (se uppgift 4), så skall vi skriva ut ett lämpligt felmeddelande, och avbryta (vi behöver då inte skriva ut listan med observationer).

En möjlighet är att börja med att leta upp species_id med en separat query innan vi gör insättningen, men om vi använder en INSERT INTO SELECT så slipper vi det: 

import sqlite3


db = sqlite3.connect("birds.sqlite")


def main():
    try:
        member_id = int(input('Member id: '))
        site_id = int(input('Site id: '))
        species_name = input('Bird name in latin: ')
        c = db.cursor()
        c.execute(
            '''
            INSERT OR IGNORE
            INTO     observations(member_id, site_id, species_id)
              SELECT   ?, ?, species_id
              FROM     species
              WHERE    latin_name = ?
            ''', [member_id, site_id, species_name]
        )
        db.commit()

        c.execute(
            '''
            SELECT    member_name,
                      member_phone,
                      count(observation_date),
                      max(observation_date) AS most_recent_date
            FROM      members
                      JOIN observations USING (member_id)
                      JOIN species USING (species_id)
            WHERE     species_name = ? AND site_id = ?
            GROUP BY  member_id
            ORDER BY  most_recent_date DESC
            ''', [species_name, site_id]
        )
        for name, phone, count, recent_obs in c:
            print(f'')
    except Exception as e:
        print(f'Woops...{str(e)}')


main()

Om vi även hade läst medlemsnamn och platsnamn, istället för de påhittade nycklarna (då under förutsättning att de var unika), så skulle vi kunnat 'trixa' lite genom att skriva något i stil med: 

member_name = input('Member name: ')
species_name = input('Bird name in latin: ')
site_name = input('Site name: ')
c = db.cursor()
c.execute(
    '''
    INSERT OR IGNORE
    INTO     observations(member_id, site_id, species_id)
      SELECT   member_id, site_id, species_id
      FROM     members
               CROSS JOIN species
               CROSS JOIN sites
      WHERE    member_name = ?
               AND latin_name = ?
               AND site_name = ?
    ''', [member_name, species_name, site_name]
)
db.commit()

...

Problem 6

Se kurshemsidorna.