732G16 Databaser: design och programmering

Projekt, del C - Bearbeta data och analysera med R

Motivation & Syfte

SQL är bra när man vill välja ut, sammanställa eller manipulera stora datamängder. Ett sätt att hantera stora datamängder är att använda databasens vyer för att komprimera informationen. Du ska nu förbereda din analys genom att skapa vyer som analysen ska utgå från.

När man har skapat vyer i databsen för den data man vill arbeta med så behöver man hämta ut denna data till någon miljö där man kan göra analyser. Detta skulle kunna vara SAS, SPSS, STATA, eller som i detta fall R. Mängden data är nu mer begränsad tack vare att vi bearbetat data i databasen, och kan nu hanteras med R.

I R kan man sedan göra de statistiska analyser som behövs för att testa de hypoteser man ställt.

När man gör komplexa utsökningar som i de skapade vyerna kan man studera hur söktider påverkas av indexering. Du ska också undersöka tidsåtgången med och utan index vid sökning i din databas.

Förberedelser

Fundera på vilka vyer som behöver skapas givet att vi vill göra linjär regression för att ta reda på om det finns ett samband mellan medelbetyg (meritpoäng) och tagna kurspoäng för olika urval av studenter och typ av kurs. Vi vill i slutänden ha en observation för varje students poäng på en kurs (precis som i ursprungsdata, men utan all onödig information). Vi vill också kunna skilja ut studenter på olika program. Det enklaste är att skapa tre vyer (en för varje urvalskriterium - obligatoriska kurser, valbara respektive alla), men det är ok att göra en enda.

Genomförande

Skapa vyerna i SQL

Skapa en ny fil som ni kallar CreateViewsGRUPPNR.sql där ni ersätter GRUPPNR med ert gruppnummer iWebreg. I denna fil skriver ni all SQL kod som ni behöver för att skapa vyerna. Det kan vara praktiskt att längst upp i filen ha SQL kod som tar bort vyerna man skapat, så att man kan köra filen flera gånger om man gör ändringar.

När ni vill köra SQL-kommandona i filen använder ni source precis som tidigare.

Hämta och analysera data i R

Skapa en ny fil som heter RegressionAnalysis.R. Det är i denna fil som ni skall hämta data från er databas (från era vyer) och genomföra de linjära regressionerna. Tänk på att vi vill ha reda på hur det ser ut för programmen generellt, men också med avseende på obligatoriska och icke obligatoriska kurser.

För varje modell sparar ni undan ett spridningsdiagram med regressionslinjen i form av en bild. Dessa bilder för ni sedan in i er rapport på valfritt sätt. Se Redovisning nedan för vilka data som ska in i rapporten.

Undersök index

Syftet med index i en databas är att de gör utsökningar snabbare. Här ska vi undersöka hur mycket snabbare det faktiskt går. MariaDB lägger automatiskt upp index på alla nycklar till alla tabeller (både primärnycklar och främmande nycklar) under antgagandet att nycklar ofta används för utsökningar. Därför finns redan ett antal index i er databas. Därför börjar vi med att ta reda på hur lång tid en utsökning tar med index och måste sedan lura MariaDB för att bli av med dem så att vi kan mäta på samma sökning utan index. Efter att alla tabeller och diagram till analysrapporten är skapade kan ni mixtra med databasen.

Börja med att mäta tiden för en eller ett par utsökningar. Detta görs med SELECT-kommando direkt i mysql-klienten. Minst en utsökning ska vara en beräkning av medelpoängsumman för alla studenter på obligatoriska kurser. Anteckna vilka kommandon som användes och tiden för varje kommendo, som anges från mysql (tiden det tar för kommandot att exekveras).

Inspektera sedan vilka index som finns på de tabeller som ingår i vyn (det är minst 4 tabeller). Kommandot heter Show indexes from tabellnamn; (Om du undrar vad BTree betyder läs i boken under B+-träd-index, avsnitt 23.13, 23.19-22).

Nu ska vi undersöka hur lång tid det tar att göra detta utan index. Tyvärr går det inte att ta bort indexen ur de befintliga tabellerna, utan vi måste göra nya tabeller utan nycklar för detta syfte. Det enklaste sättet är att använda kommandot Create table x as Select * from y (där y är en av tabellerna i databasen). Som ni kanske såg i SQL-labben kommer tabell x då att skapas utan de nycklar som finns i tabell y men ha samma innehåll. Skapa kopior av era tabeller på detta sätt.

Kontrollera att det inte finns några index till dessa nya tabeller. Gör sedan en kopia av er vy över tagna obligatoriska poäng som är precis likadan utom att den använder de nya tabellerna istället för de gamla. Gör sedan motsvarande utsökningar som tidigare på de nya tabellerna (om ni gjorde utsäkningar med de andra vyerna behöver ni göra samma förändring i dem, annars används de gamla indexerade tabellerna). Notera tiden.

Blev det skillnad i tid? Hur stor? Varierar skillbaden? Skriv ett kort stycke sist i rapporten som beskrivs nedan och berätta om skillnaden och vad ni lärde er av övningen.

Rapport

Skriv en kort rapport som innehåller en summering av alla modeller. Denna summering skall innehålla spridningsdiagram med regressionslinjen, visa min/max/medelvärde för tagna poäng samt visa modellen på formen y = kx + m. Jämför också modellerna med varandra och peka ut vilka modeller som verkar vara signifikanta.

Exempel på rapportering av data:

Prog	Alla kurser				Obligatoriska				Icke obligatoriska
	min	max	mean	regr	min	max	mean	regr	min	max	mean	regr
LIU50007	0	100	49.2	y(x) = 3.42x - 32.11	0	100	49.2	y(x) = kx + m	0	100	49.2	y(x) = kx + m
LIU50008	0	100	49.2	y(x) = 8.12x - 45.11	0	100	49.2	y(x) = kx + m	0	100	49.2	y(x) = kx + m
etc...	0	100	49.2	y(x) = 0.42x - 12.11	0	100	49.2	y(x) = kx + m	0	100	49.2	y(x) = kx + m
Alla program	...	...	...	y(x) = ...	...	...	...	y(x) =...	...	...	...	y(x) = ...

Skriv sedan kort om indexens påverkan på komplex utsökning.

Rapporten avslutas med ett avsnitt som behandlar möjliga felkällor som kan ha introducerats i projektdelar B och C. Att skriva "Inga felkällor introducerades i projektdel B" är inte ett godkänt svar på denna del av rapporten. Notera att felkällor från datainsamling inte ingår här, datat var redan insamlat.

Spara rapporten som en PDF med namnet 732G16-GRUPPNR-rapport.pdf, där ni ersätter GRUPPNR med ert gruppnummer i Webreg.

Redovisning

Denna del redovisas muntligt och skriftligt.

Den muntliga redovisningen görs på labbtillfälle genom att inför labbassistenten gå igenom hur ni arbetat med CreateTables från Del B, CreateViews, RegressionAnalysis och rapporten, inklusive diagrammen. Var redo att besvara frågor kring hur er kod fungerar.

Skriftlig redovisning görs genom att packa rapporten och de nya programfilerna för analys och databashantering (CreateViews.sql, RegressionAnalysis.R och 732G16-GRUPPNR-rapport.pdf) i ett zip-arkiv och lämna in enligt instruktionerna på översiktssidan.

Deadline

Se schemat.

Sidansvarig: Eva Ragnemalm
Senast uppdaterad: 2024-03-28