Det skulle kunna vara så att bara en mask kan bo i varje äpple, eller att det måste bo minst en mask i varje äpple, eller att det bara kan växa ett äpple på varje träd, eller att det måste växa minst ett äpple på varje träd. Det finns inget i scenariot som motsäger dessa ytterligare villkor, men eftersom de lägger mer bergränsningar på databasen än vad som uttryckligen står i scenariot, har jag i alla fallen valt den mindre restriktiva tolkningen.
Trad(ID, Hojd)
Applen(ID, Farg, VaxerPa)
Maskar(ID, Namn, Langd, BorI)
Tradgardsmastare(ID, Namn)
SkoterOm(ID, Tradgardsmastare, Trad)
Applen.VaxerPa refererar till Trad.ID
Maskar.BorI refererar till Apple.ID
SkoterOm.Tradgardsmastare refererar till Tradgardsmastare.ID
SkoterOm.Trad refererar till Trad.ID
I tabellen SkoterOm bilder Tradgardsmastare och Trad en kandidatnyckel
SQL-kommandon:
create table Trad (ID integer not null primary key, Hojd integer not null); create table Applen (ID integer not null primary key, Farg varchar(10) not null, VaxerPa integer not null references Trad(ID)); create table Maskar (ID integer not null primary key, Namn varchar(10) not null, Langd integer not null, BorI integer not null references Applen(ID)); create table Tradgardsmastare (ID integer not null primary key, Namn varchar(10) not null); create table SkoterOm (ID integer not null primary key, Tradgardsmastare integer not null references Tradgardsmastare(ID), Trad integer not null references Trad(ID), unique(Tradgardsmastare, Trad));
Lite exempeldata:
insert into Trad values (1, 11); insert into Trad values (2, 12); insert into Trad values (3, 13); insert into Trad values (4, 14); insert into Applen values (1, 'Rött', 1); insert into Applen values (2, 'Rött', 2); insert into Applen values (3, 'Grönt', 1); insert into Applen values (4, 'Grönt', 2); insert into Maskar values (1, 'Max', 11, 1); insert into Maskar values (2, 'Bengt', 12, 1); insert into Maskar values (3, 'Kurt', 13, 2); insert into Maskar values (4, 'Alpha', 13, 2); insert into Maskar values (5, 'Bravo', 13, 3); insert into Maskar values (6, 'Charlie', 13, 3); insert into Maskar values (7, 'Delta', 13, 3); insert into Tradgardsmastare values (1, 'Göran Grön'); insert into Tradgardsmastare values (2, 'Guran'); insert into Tradgardsmastare values (3, 'Zlatan Ib'); insert into SkoterOm values (1, 1, 1); insert into SkoterOm values (2, 1, 2); insert into SkoterOm values (3, 2, 2);
select ID, Langd from Maskar where Namn = 'Max';
b) (1p) Hur långa är maskarna som heter Bengt och Kurt?
select Langd from Maskar where Namn = 'Bengt' or Namn = 'Kurt';
c) (1p) Vilka maskar bor i röda äpplen? Vi vill veta dessa maskars ID-nummer och namn.
Alternativ 2:select Maskar.ID, Maskar.Namn from Maskar, Applen where Maskar.BorI = Applen.ID and Applen.Farg = 'Rött';
select ID, Namn
from Maskar
where BorI in (select ID
from Applen
where Farg = 'Rött');
Alternativ 3:
select Maskar.ID, Namn from Maskar join Applen on BorI = Applen.ID where Farg = 'Rött';
d) (2p) Hur många maskar bor i äpplen som växer på träd som sköts om av en trädgårdsmästare som heter Göran Grön?
Alternativ:select count(*) from Maskar, Applen, Trad, SkoterOm, Tradgardsmastare where Maskar.BorI = Applen.ID and VaxerPa = Trad.ID and Trad.ID = SkoterOm.Trad and SkoterOm.Tradgardsmastare = Tradgardsmastare.ID and Tradgardsmastare.Namn = 'Göran Grön';
select count(*)
from Maskar
where BorI in (select ID
from Applen
where VaxerPa in (select Trad
from SkoterOm
where Tradgardsmastare in (select ID
from Tradgardsmastare
where Namn = 'Göran Grön')));
e) (3p) Vilket äpple bor det flest maskar i? Vi vill veta det äpplets ID-nummer och färg.
create view MaskarPerApple as
select BorI as Apple, count(*) as AntalMaskar
from Maskar
group by BorI;
select ID, Farg from Applen
where ID = (select Apple from MaskarPerApple
where AntalMaskar = (select max(AntalMaskar) from MaskarPerApple));
Man bör endast skapa index på kolumnen Maskar.Namn. Det är den enda som används för att söka fram rader i dessa frågor.
b)
Några möjliga exempel på integritetsvillkor som databasen kan behöva:
select t1.* from Tidrapport as t1, Tidrapport as t2 where t1.Timmar = t2.Timmar and t1.Manad = t2.Manad and t1.ID <> t2.ID;
Ingen av de tre nya raderna kommer att finnas i tabellen. (Egenskapen "A" i "ACID", atomicitet eller odelbarhet.)
b) (1p)
Alla de tre nya raderna kommer att finnas i tabellen. (Egenskapen "D" i "ACID", "durability" eller hållbarhet.)
c) (1p)
Ingen av de nya raderna finns i tabellen.
d) (1p)
Vi har alltså två transaktioner som försöker lägga in två olika rader med samma värde på primärnyckeln ID.
Exakt vad som händer beror på hur databashanteraren implementerar isolering mellan transaktioner (egenskapen "I" i "ACID"), men på något sätt kommer konflikten mellan de två transaktionerna att upptäckas.
Mimer använder "optimistisk" transaktionshantering. Det betyder att databashanteraren kör transaktionen (men typiskt görs ändringar bara i en transaktionslokal kopia av data), och först vid commit kontrolleras om det fanns några konflikter. Transaktion nummer 2 (den från d-uppgiften, som bara lägger in en enda rad) kommer att lyckas, och alltså genomföra sin ändring av tabellen. När transaktion nummer 1 (den som lägger in tre rader) gör commit, kommer konflikten mellan dem att upptäckas, och transaktion 1 avbryts och dess ändringar rullas tillbaka:
SQL>start transaction;
SQL>insert into Tidrapport values (12, 7, 101, 'oktober');
SQL>insert into Tidrapport values (13, 7, 122, 'november');
SQL>insert into Tidrapport values (14, 7, 200, 'december');
SQL>commit;
MIMER/DB warning -10001 in function EXECUTE
Transaction aborted due to conflict with other transaction
SQL>
I Mimer kommer tabellen till slut alltså att innehålla raden från transaktion 2, men ingen av raderna från transaktion 1.
I MySQL, som använder traditionella lås för transaktionshantering, kommer transaktion nummer 2 att få vänta på att transaktion 1 låser upp sina lås. Transaktion 2 står alltså still tills transaktion nummer 1 gör commit, och då kommer transaktion 2 att försöka lägga in sin rad. Men eftersom det redan finns en rad med ID 12 i tabellen, avbryts transaktion 2 med ett fel:
(Egenskapen "C" i "ACID", "consistency preserving" eller "konsistensbevarande", innebär att om alla integritetsvillkor var uppfyllda före transaktionen, ska de också vara uppfyllda efter transaktionen.)mysql> insert into Tidrapport values (12, 8, 34, 'november'); ERROR 1062 (00000): Duplicate entry '12' for key 1 mysql>
I MySQL kommer tabellen till slut alltså att innehålla de tre raderna från transaktion 1, men inte raden från transaktion 2.
Det är tabellerna som är "relationerna" i en relationsdatabas. Det är inte kopplingarna mellan tabellerna!
(I svenskspråkiga produkter från Microsoft, till exempel Microsoft Access, kallas kopplingarna för "relationer", men det är en felaktig eller åtminstone vilseledande översättning. I de engelskspråkiga produkterna står det "relationships", inte "relations".)
b) (2p)
XML (Extensible Markup Language) är ett "uppmärkningsspråk", eller snarare ett sätt att definiera sådana språk, för att märka upp data med "taggar" (etiketter), för lagring och öveföring av data.
c) (2p)
Några olika databashanterare: