Läsnäolo: 3 tehtävää
Huom. lisäys paperiversioon tehtävässä 4!
1. Tarkastellaan seuraavaa yritystietokantaan määriteltyä näkymää.
create view DEPT_SUMMARY (Dept, EmpCount, TotalSalary, AverageSalary) as
select DNO, COUNT(*), SUM(Salary), AVG(Salary) from EMPLOYEE
group by DNO.
a) Lavenna seuraava näkymää käyttävä kysely suorituskelpoiseen muotoon,
so. muotoon, jossa argumentteina on ainoastaan perustauluja.
Mitkä optimoinnit näet mahdollisiksi?
select Dept, AverageSalary from DEPT_SUMMARY
where EmpCount > (select EmpCount from DEPT_SUMMARY where Dept = 4).
b) Onko seuraava operaatio sallittu?
update DEPT_SUMMARY set Dept = 3 where Dept = 4.
2. Mikä on alla määriteltyjen herättimien R1 ja R2 aikaansaama toiminta?
R1: create trigger varaston_muutos
after insert on muutostapahtuma
for each row
update varasto set maara = ...;
R2: create trigger muutos_talteen
after update of maara on varasto
for each row
insert into muutostapahtuma values (...);
3. Anna herättimet, joilla valvotaan yritystietokannan relaatioiden
EMPLOYEE ja DEPARTMENT välisiä viite-eheyksiä. Viite-eheyttä rikkonut
transaktio peruutetaan. Millainen suoritussemantiikka herättimille
pitäisi valita, so. missä vaiheessa ja minkä transaktion osana
herättimien toiminto-osat pitäisi suorittaa?
4. Toteuta edellisen tehtävän viite-eheyksien valvonta herättimillä
siten, että ratkaisu vastaa luentojen sivun 58 määrityksiä. Millainen
suoritussemantiikka herättimille pitäisi valita?
(Sivun 58 määritykset: taulun employee sarakkeen dno default-arvo = 1,
viiteavainmäärittelyt seuraavat:
CONSTRAINT empsuperfk FOREIGN KEY (superssn) REFERENCES employee(ssn)
ON DELETE set null ON UPDATE cascade,
CONSTRAINT empdeptfk FOREIGN KEY (dno) REFERENCES department (dnumber)
ON DELETE set default ON UPDATE cascade.)
Lisäys: Luentojen sivulla 58 esitetyn lisäksi tarvitaan taustatiedoksi
taulun department määrityksiä (E&N2, s. 189, E&N3, s. 249):
- sarakkeen mgrssn oletusarvo = 888665555,
- viiteavainmäärittely
CONSTRAINT deptmgrfk FOREIGN KEY (mgrssn) REFERENCES (employee(ssn)
ON DELETE set default ON UPDATE cascade;
5. Lisätään relaatioon DEPARTMENT attribuutti NumberOfEmployees, joka
sisältää osaston työntekijöiden lukumäärän. ('NumberofEmployees'
esiintyi aikanaan yritystietokannan ER-kaaviossa, vrt. luennot, s. 32.)
Anna herätin/herättimet, jolla tämän attribuutin arvo pysytetään
ajantasaisena. Millainen suoritussemantiikka herättimille pitäisi
valita?
6. Anna Pascal-, C- tai Java-kieleen sulautetulla SQL:llä kirjoitettu
ohjelman osa, joka tulostaa päätteelle yksitellen Research-osaston
kunkin työntekijän nimen, kysyy kullekin myönnetyn mahdollisen
palkankorotuksen ja toteuttaa korotuksen tietokantaan.