Vissa lösningsförslag till databastentan 2015-08-19 =================================================== Uppgift 1 (5 p) --------------- ER-diagrammet bör innehålla fyra entitetstyper: SPÖKE, SPÖKHUS, VITTNE och PARAPSYKOLOG, samt tre sambandstyper: N:1-sambandet BOR_I mellan SPÖKE och SPÖKHUS, N:M-sambandet SETT mellan VITTNE och SPÖKE, N:M-sambandet UNDERSÖKT mellan PARAPSYKOLOG och SPÖKHUS. Uppgift 2 (5 p) --------------- drop table Spoken; drop table Spokhus; drop table Vittnen; drop table Parapsykologer; drop table Sett; drop table Undersokt; create table Spokhus (ID integer not null primary key, Adress nvarchar(10) not null unique, Namn nvarchar(10)); create table Spoken (Nummer integer not null primary key, Namn nvarchar(10), Beskrivning nvarchar(10), Bor_i integer not null references Spokhus(ID)); create table Vittnen (Personnummer integer not null primary key, Namn nvarchar(10) not null); -- Vi antar att varje vittne bara kan se samma spöke en gång create table Sett (Vittne integer not null references Vittnen(Personnummer), Spoke integer not null references Spoken(Nummer), Datum Date not null, primary key(Vittne, Spoke)); create table Parapsykologer (Nummer integer not null primary key, Namn nvarchar(10) not null); -- Vi antar att varje parapsykolog bara kan undersöka samma spökhus en gång create table Undersokt (Parapsykolog integer not null references Parapsykologer(Nummer), Spokhus integer not null references Spokhus(ID), Datum Date not null, primary key(Parapsykolog, Spokhus)); insert into Spokhus values (1, 'Gatan 3', 'Ruskhuset'); insert into Spokhus values (2, 'Torget 2', null); insert into Spokhus values (3, 'Paraden 2', 'Ruinen'); insert into Spokhus values (4, 'Slottet', 'Slottet'); insert into Spoken values (1, 'Laban', 'Litet', 4); insert into Spoken values (2, 'Labolina', 'Litet', 4); insert into Spoken values (3, 'Mamma', 'Stor', 4); insert into Spoken values (4, 'Pappa', 'Stor', 4); insert into Spoken values (5, 'Vita frun', null, 3); insert into Spoken values (6, null, null, 3); insert into Spoken values (17, null, null, 4); insert into Spoken values (18, null, null, 4); insert into Vittnen values (1, 'Anna'); insert into Vittnen values (2, 'Bo'); insert into Vittnen values (3, 'Charlie'); insert into Vittnen values (4, 'Dagny'); insert into Sett values (1, 1, DATE'2012-03-22'); insert into Sett values (1, 2, DATE'2012-03-22'); insert into Sett values (1, 3, DATE'2012-03-22'); insert into Sett values (1, 4, DATE'2012-03-22'); insert into Sett values (2, 1, DATE'2012-03-23'); insert into Sett values (2, 2, DATE'2012-03-23'); insert into Parapsykologer values (1, 'Adolf'); insert into Parapsykologer values (2, 'Benito'); insert into Parapsykologer values (3, 'Caligula'); insert into Undersokt values (1, 1, DATE'2015-08-15'); insert into Undersokt values (1, 2, DATE'2015-08-16'); insert into Undersokt values (2, 1, DATE'2015-08-16'); insert into Undersokt values (2, 2, DATE'2015-08-17'); insert into Undersokt values (3, 1, DATE'2015-08-18'); insert into Undersokt values (3, 2, DATE'2015-08-19'); select * from Spoken; select * from Spokhus; select * from Vittnen; select * from Parapsykologer; select * from Sett; select * from Undersokt; Uppgift 3 (10 p) ---------------- a) Här är spöke nummer 17. Vilken adress bor det spöket på? select Spokhus.Adress from Spoken, Spokhus where Spoken.Nummer = 17 and Spoken.Bor_i = Spokhus.ID; select Adress from Spoken, Spokhus where Nummer = 17 and Bor_i = ID; select Spokhus.Adress from Spoken join Spokhus on Spoken.Bor_i = Spokhus.ID where Spoken.Nummer = 17; select Adress from Spoken join Spokhus on Bor_i = ID where Nummer = 17; select Adress from Spokhus where ID in (select Bor_i from Spoken where Nummer = 17); b) Här är spöke nummer 18. Hur många spöken bor i spökhuset som det spöket bor i? select count(*) from Spoken as Arton, Spoken as AllaBoende where Arton.Nummer = 18 and Arton.Bor_i = AllaBoende.Bor_i; select count(*) from Spoken where Bor_i in (select Bor_i from Spoken where Nummer = 18); c) Vi vill ha adresserna till alla de spökhus där någon har sett minst ett spöke. select distinct Spokhus.Adress from Sett, Spoken, Spokhus where Sett.Spoke = Spoken.Nummer and Spoken.Bor_i = Spokhus.ID; select Adress from Spokhus where ID in (select Bor_i from Spoken where Nummer in (select Spoke from Sett)); d) Finns det några spökhus där ingen har sett något spöke? Vi vill ha adresserna till de spökhusen. select Adress from Spokhus where ID not in (select Spoken.Bor_i from Sett, Spoken where Sett.Spoke = Spoken.Nummer); select Adress from Spokhus where ID not in (select Bor_i from Spoken where Nummer in (select Spoke from Sett)); select Adress from Spokhus left join (Sett join Spoken on Spoke = Nummer) on ID = Bor_i where Vittne is null; e) Vad heter det vittne som har sett flest olika spöken? create view AntalSeddaSpoken as select Vittne, count(*) as Antal from Sett group by Vittne; select Vittne from AntalSeddaSpoken where Antal = (select max(Antal) from AntalSeddaSpoken); select Vittnen.Namn from Vittnen, AntalSeddaSpoken where Vittnen.Personnummer = AntalSeddaSpoken.Vittne and Antal = (select max(Antal) from AntalSeddaSpoken); Uppgift 4 (3 p) --------------- Spokhus.ID -- JA! Spokhus.Adress -- Nej, bara i resultatet! (Men kan faktiskt snabba upp ändå!) Spokhus.Namn -- Nej, inte med alls! Spoken.Nummer -- JA! Spoken.Namn -- Nej, inte med alls! Spoken.Beskrivning -- Nej, inte med alls! Spoken.Bor_i -- JA! Vittnen.Personnummer -- Nej, inte med alls! Vittnen.Namn -- Nej, inte med alls! Sett.Vittne -- Nej, inte med alls! Sett.Spoke -- JA! Sett.Datum -- Nej, inte med alls! Parapsykologer.Nummer -- Nej, inte med alls! Parapsykologer.Namn -- Nej, inte med alls! Undersokt.Parapsykolog -- Nej, inte med alls! Undersokt.Spokhus -- Nej, inte med alls! Undersokt.Datum -- Nej, inte med alls! Uppgift 7 (5 p) --------------- a) inre join och yttre join Bägge slår ihop två tabeller genom att para ihop rader som hör ihop, men inre join kastar bort rader som inte går att para ihop med någon rad från den andra tabellen, och yttre join behåller dem. b) WHERE och HAVING Två delar som kan finnas i en SQL-fråga. Bägge väljer ut vilka rader som ska behållas i ett svar, men WHERE görs före en eventuell aggregatfunktion med GROUP BY, och HAVING görs på resultatet av aggregatfunktionen och GROUP BY. c) UPDATE och ALTER Två SQL-kommandon som bägge ändrar på innehållet i databasen, men UPDATE ändrar på de data som finns på raderna i tabellerna, medan ALTER ändrar på själva tabellen (eller andra databasobjekt, som vyer), till exempel genom att ta bort eller lägga till en hel kolumn. d) DROP och DELETE Två SQL-kommandon som bägge tar bort saker ur databasen, men DELETE tar bort rader ur en tabell, och DROP tar bort hela tabeller (eller andra databasobjekt, som vyer). e) en tabell och en relation Det är samma sak. "Relation" brukar användas om den teoretiska, matematiska företeelsen, och "tabeller" om de praktiskt använda tabellerna i en databas.