EDAF75 - tentamen 16 mars 2021

Vid Lunds universitet finns hundratals skrivare, och de måste ofta repareras. Varje skrivare har en unik kod, och ägs av någon av universitetets institutioner. Institutionerna har ett namn, en institutionskod, och ett telefonnummer till en kontaktperson – de vill hålla reda på inköpsdatum och placering (rumsnummer) för var och en av sina skrivare.

Varje skrivare är av en given modell, modellen ges av tillverkarnamnet och deras modellbeteckning, typ "Acme QuietPrinter". Namnen på tillverkarna är unika, men modellbeteckningarna är bara unika för varje tillverkare (flera tillverkare kan tillverka en "QuietPrinter").

Universitetet har serviceavtal med olika företag som man kan ringa till när en skrivare går sönder – varje serviceföretag kan laga flera olika modeller av skrivare, och det finns skrivarmodeller som kan lagas av flera företag. Varje företag har ett fast timpris för reparation av varje skrivarmodell som de kan laga (timpriset beror av vilken modell man skall laga).

När en skrivare går sönder skrivs en felrapport med datum och beskrivning av felet, varefter den ansvarige beställer service hos ett lämpligt företag. När reparationen är klar anges reparationsdatum i felrapporten tillsammans med en kort beskrivning av vad som reparerats, kostnad för reservdelar och den totala arbetstiden.

1. (11p) Beskriv systemet i en ER-modell som du redovisar med
   • ett ER-diagram i UML, och
   • SQL-satser (CREATE TABLE) som definierar dina tabeller – deklarera primärnycklar och främmande nycklar på lämpligt sätt.
2. (2p) Utgående från de tabeller du skapar i uppgift (a), skriv SQL-satser som visar vilka skrivare (skrivarens unika kod och namnet på den ägande institutionen) som aldrig behövt repareras.

Ett företag har ett antal anställda städare vars arbete beskrivs av följande tabeller (beteckningar: ~primärnyckel~, /främmande nyckel/):

• employees(~employee_id~, name, hourly_wage)
• areas(~area_id~, size, /area_type/, /employee_id/)
• area_types(~area_type~, work_time)

De anställda städarna har ett anställningsnummer (employee_id), ett namn, och en given timlön.

Varje yta har ett identifikationsnummer (area_id), en storlek i kvadratmeter, och har en given typ (exempelvis 'arbetsrum' eller 'korridor'). Varje yta städas av en given städare (employee_id).

Ytor av olika typ tar olika lång tid att städa; tiden anges i minuter per kvadratmeter i tabellen area_types.

Skriv SQL-satser för följande uppgifter (det är tillåtet att definiera och använda vyer eller WITH-satser):

1. (2p) Skriv SQL-kod för att skapa tabellerna employees, areas och area_types – hitta på lämpliga typer för attributen.
2. (2p) Skriv ut anställningsnummer, namn och timlön för alla städare, i alfabetisk ordning efter namn.

3. (2p) Skriv ut den totala ytan som skall städas, uppdelat efter typ av yta – exempelvis:

   arbetsrum      1200
   korridor        940
   toalett          42
   

   Du behöver inte bekymra dig för hur man får raka kolumner i utskriften.

4. (2p) Skriv ut anställningsnummer och namn på de städare som inte har några ytor att städa.
5. (2p) Skriv ut anställningsnummer och namn för de städare som har mer än 1000 m2 att städa.
6. (3p) Skriv ut den totala tid som behövs för att städa alla ytor.

För att ingen skall tappa onödiga poäng på ett eventuellt misstag i (a), så har vi helt separerat uppgift (3-1) från uppgift (3-2) till (3-5).

1. (4p) En websida har ett antal podcasts med unika namn, som "Dagens dikt" eller "Kulturnytt". En podcast har en producent med ett unikt namn ("Podd AB"), och varje producent kan producera flera olika podcasts. Varje podcast har olika avsnitt, med titlar, avsnittslängd (i minuter) och publiceringsdatum – en given podcast har aldrig två avsnitt med samma namn, men det händer att olika podcasts har avsnitt med samma namn (exempelvis kan både "Dagens dikt" och "Kulturnytt" ha avsnitt med namnet "Påsk-special 2021"). Varje podcast publicerar högst ett avsnitt per dag.

   För att lyssna på websidans podcasts måste man logga in med en unik identitet, och ett lösenord, och ladda ner det avsnitt man är intresserad av – man kan ladda ner flera avsnitt åt gången, och även ladda ner ett avsnitt flera gånger.

   Definiera de funktionella beroenden som gäller mellan nedanstående attribut (använd attributnamnen som de står nedan, inför inga nya beteckningar):

   • podcast_name: namnet på en podcast
   • podcast_producer: namnet på producenten av en podcast
   • episode_title: titeln på ett podcast-avsnitt
   • episode_length: längden på ett avsnitt (i minuter)
   • episode_date: publiceringsdatum för ett avsnitt
   • user_id: identitet för en användare
   • user_password: lösenord för en användare
   • download_time: tidpunkt då en användare laddar ner ett avsnitt

2. (2p) Observera att denna och följande deluppgifter är helt oberoende av (a): I relationen R(A,B,C,D,E,F,G,H) gäller följande beroenden:

   • H -> A B
   • E -> F G
   • D E H -> C

   Bestäm relationens nycklar – motivera ditt svar

3. (1p) Visa att relationen R i (b) inte är i BCNF

4. (4p) Bryt ner relationen R i (b) i mindre relationer som är i BCNF, och som kan rekonstrueras till den ursprungliga tabellen utan att vi förlorar eller lägger till rader. Motivera varje steg i nedbrytningen genom att skriva dina relationer på nedanstående form (där fds är relationens funktionella beroenden, och keys är dess nycklar):

   -------------------
   R1(A, B, C, D, E, ...)
   fds: A -> BC, BD -> E, ...
   keys: AC, AD, ...
   -------------------
   

   Att svara genom att bara ange några relationer utan motivering ger inga poäng.

5. (2p) Visa hur vi kan återskapa den ursprungliga relationen med hjälp av relationerna som du bröt upp den i ovan.

I uppgift 2 hade vi följande tabeller:

• employees(~employee_id~, name, hourly_wage)
• areas(~area_id~, size, /area_type/, /employee_id/)
• area_types(~area_type~, work_time)

Vi vill nu ha ytterligare två tabeller:

• worklogs(~area_id~, ~cleaned_at~): lagrar information om varje gång en area har städats.
• payments(~employee_id~, ~area_id~, ~timestamp~, amount): innehåller en rad med betalningen för varje gång någon har städat en area (att ha denna tabell är inte nödvändigtvis en 'snygg lösning', vi har den för att den ger en lagom stor uppgift). Attributet timestamp är den tidpunkt då arean städades, och amount är det belopp den anställde får för att städa arean en gång.

Skriv triggers för nedanstående uppgifter – det är OK om din syntax för triggers inte är exakt rätt, men den bör vara rimligt nära den som SQLite använder (gör så gott du kan).

1. (2p) Gör så att databasen gör en ROLLBACK om man försöker lägga in en rad i worklogs med en tidpunkt som ligger före den senast inlagda tidpunkten för den aktuella arean – vi kan anta att det finns tidigare inlagda rader för samtliga areor.
2. (2p) Gör så att payments-tabellen uppdateras varje gång vi lägger in en ny rad i worklogs – vi antar i uppgiften att denna trigger anropas efter triggern i (a).

Kommentar som kan vara relevant för uppgiften: det är OK för en främmande nyckel att ha värdet NULL (du behöver inte vara orolig om du inte förstår varför detta skulle vara relevant i uppgiften, jag vill bara inte att någon skall göra en onödigt krånglig lösning för att de trodde att en främmande nyckel aldrig kan vara NULL).

Vi vill skapa en databas för släktforskning, med data från hundratals år tillbaka i tiden.

1. (2p) Hur skulle du definiera en tabell för personer i en relationsdatabas för släktforskning, om vi bara vill hålla reda på personernas födelsedatum och släkt-relationer, på enklast möjliga sätt? Skriv en SQL-sats för att definiera tabellen.
2. (1p) Om vi i (a) har en relationsdatabas med bara SQL, vilket huvudsakligt problem har den när vi skall använda den för släktforskning?
3. (1p) På vilket sätt kan Neo4j lösa problemet i (b)? Du behöver inte redogöra för några detaljer, vi vill bara ha ett översiktligt svar i högst 50 ord.

1. (1p) Förklara vad som är skillnaden mellan serializable och read committed.
2. (1p) Ge ett rimligt skäl till varför man ibland skulle vilja använda 'read committed' istället för 'serializable'.
3. (1p) Ge ett rimligt skäl till varför man ibland skulle vilja använda 'serializable' istället för 'read committed'.