Databaskonstruktion: Tentamen 2004-01-07

Örebro universitet
Institutionen för teknik
Thomas Padron-McCarthy (Thomas.Padron-McCarthy@tech.oru.se)

Med vissa rättelser. 2004-06-05.

Tentamen i

Databaskonstruktion

för Dataingenjörsprogrammet

lördag 29 maj 2004 kl 8:00 - 13:00 i L001

Hjälpmedel:	Inga hjälpmedel.
Poängkrav:	Maximal poäng är 40. För betyget 3 krävs 20 poäng. För betyget 4 krävs 27 poäng. För betyget 5 krävs 33 poäng.
Resultat och lösningar:	Meddelas på kursens hemsida senast lördag 12 juni 2004.
Visning:	Ingen visning. Tentorna kan studeras på expeditionen.
Examinator och jourhavande:	Thomas Padron-McCarthy, telefon 070-7347013.

Skriv tydligt och klart. Lösningar som inte går att läsa kan naturligtvis inte ge några poäng. Oklara formuleringar kommer att misstolkas.
Skriv den personliga tentamenskoden på varje inlämnat blad. Skriv inte namn eller personnummer på bladen.
Lös bara en uppgift per blad. Skriv bara på en sida av papperet. Använd inte röd skrift.
Antaganden utöver de som står i uppgifterna måste anges. Gjorda antaganden får inte förändra den givna uppgiften.

LYCKA TILL!

Scenario till uppgifterna

Företaget Örebro Trädgårdsservice grundades 1892, och sköter trädgårdar mot betalning. Deras skickliga trädgårdsmästare åker ut i sina små bilar och klipper gräs, rensar rabatter och planterar blommor. Kunderna är främst företag, men även en del privatpesoner.

Under senare år har företagets verksamhet växt kraftigt. Eftersom man öppnat kontor i andra städer än Örebro har man bytt namn till Svensk Trädgårdsservice, och planer finns på en börsintroduktion.

Hittills har företaget skött sin administration på papper som samlas i pärmar, men (enligt känt mönster) har det nu blivit så mycket papper att företaget oroar sig för kontorsgolvets hållfasthet. Därför vill man förstås överföra administrationens data till en databas.

Saker som ska lagras i databasen:

Kunder. En del av kunderna är företag, och en del är privatpersoner. Alla företagskunder har en kontaktperson, så att trädgårdsmästaren vet vem på företaget hon ska ringa till om grinden till trädgården är låst, men det har inte privatpersonerna.
Lokalkontor. Det finns ett kontor i varje stad, utom i Stockholm där det finns tre.
Trädgårdsmästare. De anställda trädgårdsmästarna. Varje trädgårdsmästare är knuten till ett visst lokalkontor.
Arbeten som utförts. Det kan till exempel vara gräsklippning, rabattrensning eller trädbeskärning. Varje arbete utförs åt en viss kund, och görs av en viss trädgårdsmästare.

Ytterligare ledtrådar om vad som behöver lagras i databasen får man av SQL-frågorna i uppgift 4, som ska gå att besvara med hjälp av de data som finns i databasen.

Uppgift 1 (7 p)

Rita ett ER-diagram för Svensk Trädgårdsservice' databas. Använd informationen i scenariot ovan, och tänk också på att det ska gå att svara på frågorna i uppgift 4.

Det här är en ganska fri uppgift, så flera olika lösningar är möjliga. Om det är något du är osäker på, eller om du behöver göra några antaganden, så förklara hur du tänkt och vilka antaganden du gjort.

Uppgift 2 (5 p)

Implementera den beskrivna databasen i relationsmodellen, dvs översätt ER-diagrammet till tabeller.

Du behöver inte skriva create table-kommandon i SQL, men du ska ange vilka relationer som finns och vilka attribut varje relation innehåller. Ange också vad som är primärnyckel, och vilka referensattribut som finns och vad de refererar till.

Implementationen ska vara bra.

Uppgift 3 (3 p)

Välj själv en av tabellerna som du gjort i uppgift 2 ovan. Ange vilken som är den högsta normalform (av 1NF, 2NF, 3NF och BCNF) som tabellen uppfyller. (Eftersom det står i uppgiften att "implementationen ska vara bra", så är det förmodligen i BCNF.)

Visa sen att tabellen uppfyller den normalformen.

Uppgift 4 (10 p)

Formulera följande frågor i SQL:

a) Vilka trädgårsmästare hör till lokalkontoret i Brunflo? Vi vill veta de trädgårdsmästarnas nummer och namn.

b) Kunden Bengt Bengtsson i Bälinge klagar på att någon av de trädgårdsmästare som jobbat i hans trädgård har rökt under arbetet, och kastat fimpar i blomrabatten. Vilka trädgårdsmästare har arbetat i Bengt Bengtssons trädgård? Vi vill veta de trädgårdsmästarnas nummer och namn.

c) Vilken trädgårsmästare har utfört flest arbeten? Vi vill veta den trädgårdsmästarens nummer och namn.

d) Finns det några lokalkontor som inte har några kunder alls? Var ligger i så fall dessa lokalkontor? (Kanske får man gå via trädgårdsmästarna för att se vilka kunder som lokalkontoren har: det är trädgårdsmästarna som jobbar åt kunderna, och trädgårdsmästarna hör sen till lokalkontoren.)

e) Statistiska Centralbyrån håller på med en undersökning av vilka namn på personer som förekommer i Sverige. Därför vill vi, ur vår databas, söka fram namnen på trädgårdsmästare och på de kunder som är privatpersoner. Svaret ska bli en enda tabell, med en enda kolumn.

Definiera gärna vyer om det underlättar.

Uppgift 5 (5 p)

Frågor av samma typ som de fem frågorna i uppgiften ovan förekommer ofta. Vilka index bör skapas för att de frågorna ska gå snabbt? Varför bör just dessa index skapas?

("Samma typ" betyder att man kan byta ut namnen i frågorna, så att man till exempel frågar efter trädgårdsmästare som hör till andra lokalkontor än just det i Brunflo.)

Uppgift 6 (5 p)

ODBC, som betyder Open Database Connectivity, har utvecklats för att skapa en standard för hur program kommunicerar med en databashanterare, där det ska vara enkelt att byta mellan olika databashanterare.

a) (3p) Beskriv hur ODBC-arkitekturen är uppbyggd. Några ord som kan vara bra att ha med är datakälla, ODBC-drivrutin, drivrutinhanterare och API. Rita gärna bilder också.

b) (2p) Jämför ODBC med de alternativ som finns för att få ett C-program att hämta data ur en databas. Vilka alternativ finns? På vilket vis är ODBC bättre eller sämre?

Uppgift 7 (5 p)

I de flesta relationsdatabashanterare kan man ange dataintegritetsvillkor, till exempel främmande nycklar, och databashanteraren kommer sen automatiskt att se till att dessa villkor inte bryts.

Om man sen har ett applikationsprogram, till exempel i form av ett ODBC- eller ESQL-program, så kan det hända att ändringar som användaren försöker göra stoppas av integritetsvillkoren i databasen. Ta till exempel att man vill ta bort en anställd, men så är denna anställde chef för en avdelning, och det finns en främmande nyckel från avdelningstabellen till anställdtabellen.

Man kan förstås hämta felmeddelandet från databashanteraren och ge det till användaren. Men det felmeddelandet är ofta ganska svårbegripligt, och en ovan användare blir inte glad av det:

MIMER/DB error -10106 in function EXECUTE
     UPDATE or DELETE operation invalid because the referenced
     table SYSADM.EMPLOYEE does not satisfy a referential constraint

Alternativt kan man skriva applikationsprogrammet så att det, innan det försöker ta bort den anställde, först kollar vad som kan gå fel, till exempel det där med avdelningschefer. Då kan programmet skriva ut ett mycket bättre felmeddelande:

Du försökte ta bort anställd nummer 8, Sten Dumling, ur databasen. Men Sten Dumling är chef för avdelning 31, Städ och konfekt. Du måste utse en ny chef för Städ och konfekt innan du kan ta bort Sten Dumling ur databasen.

Den lösningen ger alltså fördelen att det går att ge hjälpsammare felmeddelanden. Men vilka andra fördelar och nackdelar finns med att på det sättet kontrollera dataintegritetsvillkor i applikationsprogrammet, i stället för att förlita sig på databashanterarens automatiska kontroll?