L?sningsforslag til ?vingsoppgaver INF1300 uke 15.-19. oktober
--------------------------------------------------------------

Kap. 4, kap 5 fram til s. 120 og kap. 8 i PostgreSQL-boka er fin
st?ttelitteratur til det som gjennomg?s denne uken.


F?rst litt praktisk om postgres:
--------------------------------

* Logge inn:

psql -h kurspg -U brukernavn -d databasenavn

der kurspg angir maskinen du kobler opp mot,

Kommandoen kj?res fra en unix-maskin. 


* Skift passord!

alter role brukernavn with encrypted password 'nyttpassord';

Av sikkerhetsmessige grunner skal du ikke sette postgres-passordet til
det samme som ditt vanlige UiO-passord.


* Nyttige kommandoer: 

\?		- for hjelp rundt psql-komandoer

\h		- for hjelp til sql-kommandoer: 
		  f.eks. \h CREATE TABLE

\i filnavn.sql	- kj?rer fila filnavn.sql som m? inneholde
		  sql-kommandoer. Enkel m?te ? kj?re mange kommandoer
		  samtidig, f.eks. n?r du oppretter flere tabeller,
		  legger inn mye data, osv.

\d Hotell	- viser informasjon om tabellen Hotell

\c fdb		- kobler til databasen fdb

\q		- avslutt


Kommentarer i en .sql-fil omsluttes av /* og */.


Oppgave 1
----------

* hotellnr i Rom er fremmedn?kkel til Hotell
* gjestnr i Bestilling er fremmedn?kkel til Gjest
* (hotellnr, romnr) i Bestilling er fremmedn?kkel til Rom



Oppgave 2
----------

create table Hotell (
hotellnr serial primary key,
hotellnavn varchar (100) not null,
by varchar (50) not null,
unique(hotellnavn, by)
);


Merk at det er brukt SERIAL for hotellnr. SERIAL er en int datatype
som ?kes automatisk (av dmbs) ved innlegging av en ny forekomst (se
s. 207 PostgreSQL-boka). Dette betyr at ved hver nye registrering av
et hotell ?kes hotellnr med 1. SERIAL b?r brukes med forsiktighet, det
vil skape kaos ? blande manuell registrering av nytt hotellnr med det
dmbs-genererte. Samtidig er det en veldig grei funksjon i tabeller
hvor man har l?penr el. som ikke har annen mening enn ? v?re
identifiserende.


create table Rom (
romnr int,
hotellnr int references Hotell (hotellnr),
type varchar(100) not null,
pris real not null,
primary key (romnr, hotellnr)
);


create table Gjest (
gjestnr serial primary key,
gjestnavn varchar(200) not null,
gjestadresse varchar(300) not null
);	


create table Bestilling (
hotellnr int not null,
romnr int not null,
fradato date not null check(fradato >= current_date),
tildato date not null check(tildato > fradato),
gjestnr int references Gjest (gjestnr) not null,
primary key (hotellnr, romnr, fraDato),
foreign key (hotellnr, romnr) references Rom (hotellnr, romnr)
);

current_date er en innebygget variabel i Postgres som gir dagens dato
(se s. 215 i PostgreSQl-boka).



Tenk litt over rekkef?lgen tabellene opprettes i. Kan man opprette en
tabell der det er definert en fremmedn?kkel til en annen tabell f?r
tabellen det refereres til er definert?


Kan ogs? legge til fremmedn?kler (og andre constrainter) etter at en
tabell er opprettet:

alter table Rom 
add constraint FK_Rom_Hotellnr foreign key (hotellnr) 
references Hotell (hotellnr);

FK_Rom_Hotellnr er rett og slett navnet som er gitt denne spesielle
constrainten.


Test gjerne ? slette tabeller med DROP TABLE ogs?. Spiller rekkef?lgen
tabellene slettes i noen rolle? Finn ut hva 'DROP TABLE .... CASCADE'
gj?r.



Oppgave 3
----------

/* Legge inn hotell-data */

insert into Hotell(hotellnavn, by) 
values ('Oslo Plaza', 'Oslo'); 

insert into Hotell(hotellnavn, by) 
values ('SAS-hotellet', 'Oslo');

insert into Hotell(hotellnavn, by) 
values ('Hotell Opera', 'Oslo');

insert into Hotell(hotellnavn, by) 
values ('Norlandia', 'Bergen');

insert into Hotell(hotellnavn, by) 
values ('Hotel Norge', 'Bergen');

insert into Hotell(hotellnavn, by) 
values ('Radisson SAS Royal Garden', 'Trondheim');

insert into Hotell(hotellnavn, by) 
values ('Britannia Hotel', 'Trondheim');


/* Legge inn Rom-data */

insert into Rom(romnr, hotellnr, type, pris)
values (100, 1, 'dobbel', 499.00);

insert into Rom(romnr, hotellnr, type, pris)
values (101, 1, 'dobbel', 499.00);

insert into Rom(romnr, hotellnr, type, pris)
values (102, 1, 'familie', 699.00);

insert into Rom(romnr, hotellnr, type, pris)
values (302, 1, 'enkel', 699.00);

insert into Rom(romnr, hotellnr, type, pris)
values (146, 6, 'dobbel', 499.00);

insert into Rom(romnr, hotellnr, type, pris)
values (148, 6, 'enkel', 699.00);


/* Legge inn data om gjester */

INSERT INTO Gjest VALUES (default, 'Hans Hansen', 'Kroken 4, 0481 Oslo');

INSERT INTO Gjest VALUES (default, 'Anne Hansen', 'Kroken 4, 0481 Oslo');

INSERT INTO Gjest VALUES (default, 'Ole Olsen', 'Nyveien 67, 7012 Trondheim');


/* Legge inn data om bestillinger */

INSERT INTO Bestilling VALUES (1, 101, '2007-10-17', '2007-10-19', 1);

INSERT INTO Bestilling VALUES (1, 102, '2007-10-17', '2007-10-19', 2);

INSERT INTO Bestilling VALUES (1, 302, '2007-12-12', '2007-12-13', 3);

INSERT INTO Bestilling VALUES (6, 148, '2007-10-28', '2007-10-30', 1);


Tillegg: Fors?k ? legge inn et par ulovlige forekomster.

Gj?r et par endringer p? dataene, f.eks.:

update Hotell set hotellnavn='Norlandia' 
where hotellnr='2';

delete from Hotell
where hotellnavn='Norlandia';



Oppgave 4
----------

a)

select * from Hotell;


b)  

select * from Hotell where by like 'Oslo';
    

c) 

I denne oppgaven m? vi bruke pattern matching. Eks:

select * from hotell where by like 'B%';

finner informasjon om alle hoteller som ligger i byer som begynner med B.

Dette finner alle gjester som har Oslo i sitt adressefelt:

select gjestnavn, gjestadresse from Gjest
where gjestadresse like '%Oslo%'
order by gjestnavn asc;


d)  

select * from Rom 
where (type like 'dobbel' or type like 'familie') and pris < 800 
order by pris asc;


OR har presedens foran AND og blir utf?rt f?rst, men jeg har likevel
satt parantes rundt OR-uttrykket for ? markere hva som h?rer sammen.

Se s. 269 og noen sider framover i PostgreSQL-boka for en full
oversikt over presendens og assosiativitet for de ulike operatorene i
Postgres.



Oppgave 5
----------

a)

select r.romnr, r.type, r.pris from Rom r, Hotell h
where r.hotellnr = h.hotellnr 
and h.hotellnavn like 'Oslo Plaza';


b)

select b.romnr from Hotell h, Bestilling b
where h.hotellnavn like 'Oslo Plaza'
and h.hotellnr = b.hotellnr
and (b.fradato <= current_date and b.tildato > current_date);


c)

Denne oppgave var dessverre litt forut for sin tid,
SQL-funksjonaliteten vi trenger for ? finne det vi er ute etter
blir ikke forelest f?r 22/10. Jeg gir likevel to ulike forslag til
l?sning:

Med differanse (EXCEPT):

(select r.romnr from Rom r, Hotell h
where h.hotellnavn like 'Oslo Plaza'
and h.hotellnr = r.hotellnr)
except all
(select b.romnr from Hotell h, Bestilling b
where h.hotellnavn like 'Oslo Plaza'
and h.hotellnr = b.hotellnr
and (b.fradato <= current_date and b.tildato > current_date));

I denne queryen finner vi i den f?rste select-setningen alle
forekomster av rom p? Oslo Plaza. Deretter fjerner vi - med except all
- alle rom som det i ?yeblikket bor noen p?. Da sitter vi igjen med
alle rom det i ?yeblikket ikke bor noen p?.


S? en l?sning med NOT IN (foreleses f?rst 5/11):

select r.romnr from Rom r, Hotell h
where h.hotellnavn like 'Oslo Plaza'
and h.hotellnr = r.hotellnr
and r.romnr not in
    (select b.romnr from Hotell h, Bestilling b
    where h.hotellnavn like 'Oslo Plaza'
    and h.hotellnr = b.hotellnr
    and (b.fradato <= current_date and b.tildato > current_date));

I denne queryen finner vi alle rom p? Oslo Plaza, i tillegg har vi en
klausul (romnr NOT IN) som sier at et romnr ikke kan forekomme i
mengden av alle romnr det i ?yeblikket bor noen p? (som vi finner i
neste select).


d)

select g.gjestnavn, g.gjestadresse from Gjest g, Bestilling b, Hotell h
where h.hotellnavn like 'Oslo Plaza'
and b.hotellnr = h. hotellnr
and g.gjestnr = b.gjestnr
and b.fradato <= current_date and b.tildato > current_date;