Uppgifter

Uppgift 1 - Charterflyg

Relationen AVGANG innehåller uppgifter om bl.a. antalet platser (PLATS) på respektive plan (FLYG), priset per plats (PRIS) samt antalet lediga platser (KVAR) vid detta tillfälle.

Relationen BOKNING innehåller uppgifter om bl.a. antalet platser (ANTAL) som bokats av kunder (PERSNR) hos respektive resebyrå (BYRA), datum då bokningen gjordes (BOKAD) samt datum då flygbolaget erhållit betalt (BETALT).

1. När en kund hos en resebyrå bokar ett antal platser på ett visst plan (FLYG) utförs en transaktion som ;
a) minskar antalet lediga platser (KVAR) med gällande antal,
b) infogar en lämplig tippel i relationen BOKNING, och
c) debiterar attribut SUMMA i relationen SKULDER med priset för dessa platser (ANTAL*PRIS).

Samma kund skall ej kunna boka samma resa mer än en gång.

2. När flygbolaget erhållit betalt för bokningen utförs en transaktion som;
a) inför datum för detta vid gällande tippel i relationen BOKNING, och
b) krediterar attribut SUMMA i relationen SKULDER med gällande belopp (ANTAL*PRIS).

Samma kund skall ej kunna betala för samma resa mer än en gång.

3. Självklart får ingen bokning göras om inte det finns tillräklig med platser (KVAR får ej vara mindre än 0). Även om det finns tillräkligt med platser får ingen bokning göras om resebyrån kommer att överskrida sin kreditgräns (SUMMA får ej vara större än KREDIT). Det antalet platser (ANTAL) som bokningen avser skall vara större än noll. Betalningsdatum (BETALT) skall antingen saknas (null) eller vara större än bokningsdatum (BOKAD).

4. Databasen skall vara korrekt på följande sätt;
a) de enda tillåtna namn på resebyråer är 'ALWAYS', 'ATLAS' och 'ROYAL',
b) varje rad i AVGANG skall vara unik på FLYG,
c) varje rad i BOKNING skall vara unik på FLYG, PERSNR och BYRA,
d) varje rad i SKULDER skall vara unik på BYRA,
e) varje plan (FLYG) i relationen BOKNING måste referera till någon plan (FLYG) i relationen AVGANG.

5. För att möjliggör snabb acess till databasen används
a) hashing på attribut FLYG i relationen AVGANG,
b) sekundär index på attribut TILL i relationen AVGANG,
c) btree på attribut BYRA, FLYG och PERSNR i relationen BOKNING.

6. Visa hur flygbolaget kan utföra följande sökningar;
a) ta fram i bokstavsording utan duplikat uppgifter om vilka orter respektive resebyrå har bokningar till (SELECT ... BYRA, TILL ... WHERE ...)
b) ta fram uppgifter om kunder som ännu inte betalt för flygen till Paris den 13/6 (SELECT PERSNR, BYRA, PRIS*ANTAL ... WHERE ... IS NULL)
c) ta fram uppgifter om vilka plan för vilka det saknas uppgifter om kunder (SELECT FLYG, TILL, FRAN, DATUM ... WHERE ... NOT IN ...)

Uppgiften innebär att varje grupp skall
a) skapa databasen, deklarera lämpliga access metoder (deluppgift 5) och ladda tabellerna med data enligt ovan.

När ni deklarerar era relationer var snälla och använd små bokstäver och gör inga förkortningar. Detta för att göra det enklare för vi andra som ska komma åt er databas. Saknade värde är NULL. Primära nycklar är understrukna.

b) bevisa att databasen är korrekt (deluppgift 3 och 4) genom att köra sql satserna i filen ~john/b.3/nogoflyg. Ingen sql sats skall lyckas!

c) köra deluppgift 6.

Redovisning sker genom att varje grupp skall logga in med egen användaridentitet och utför punkt b, och c ovan. Lyckas någon sql sats i nogoflyg måste databasen korrigeras!

Uppgift 2 - Anrop av SQL via Java

Denna uppgift handlar om att skriva program i Java som bearbetar databasen ovan. Att utföra transaktioner mot en databas är alltid riskabel eftersom det är lätt att ange felvärde. SQL satser som ändrar innehållet i en databas (INSERT, UPDATE, DELETE) måste kontrolleras nogranna innan de utförs. När en kund bokar en resa skall inte personalen i en resebyrå behöver räkna ut hur många platser blir kvar på planet och vilket belopp det gäller. När flygbolaget får betalt för en resa skall personalen inte behöva ange beloppet. Alla sådana räkningar skall göras i program som eventuellt hämta gällande uppgifter från databasen (SELECT).

Två program skall skrivas motsvarande deluppgift 1 och 2 ovan. Det ena programmet skall användas av resebyråer och möjliggöra bokning av resor. Det andra programmet skall användas av flygbolaget för att registrera betalning av resor. Programmen skall efterfråga lämpliga uppgifter för att kunna utföra transaktioner.

Uppgift 3 - Butikskedja

Jompa äger en butikskedja av radio & TV-affärer. Det finns filialer i de flesta städer i Norrland. I affärerna säljer man radio, TV, stereo, video, TV-spel samt olika typer av tillbehör. Alla produkter i sortimentet är indelade i olika produktgrupper, t.ex. TV, video, spel, skivor. Jompa önskar skaffa sig ett orderhanteringssystem. Systemet skall lagra uppgifter om kunder, filialer, artiklar, försäljning samt vilka artiklar och antalet artiklar som finns i de olika filialernas lager.

Efter diskussioner med Jompa har det framkommit att i databasen ska följande information finnas lagrad: (visas som ett 'rotten record')

Värdet Nej i kolumnen Null anger att det inte får förekomma null-värden, utan värde måste alltid anges.

När en kund placerar en order hämtas artiklarna från olika filialer. Hos varje filial finns ett artikellager. En och samma artikel kan finnas vid flera filialer. Varje order omfattar uppgifter om bl.a. från vilken filial respektive artikeln har hämtats. Ett exempel på en order som en viss kund placerat visas nedan;

Ordern omfattar artiklar från olika filialer till ett totalt värde av 31086.00 kronor.

1. Använd Entity-Relationship modellering för att skapa en konceptuell modell över datan som skall finnas i databasen. Ge attributen exakt de namn som anges. Rita diagrammet på en A4 sida med hjälp av ett lämpligt ritverktyg (t.ex. Power Point).

2. Normalisera den konceptuella modellen till ett antal tabeller i tredje normalformen. Skriv gällande SQL' create table' satser för dessa tabeller.