LÖSNINGSFÖRSLAG till DBTEK-tentan 18 oktober 1997 (TDDB38) ---------------------------------------------------------- Det här är FÖRSLAG på lösningar. Det kan finnas andra svar som också är riktiga. Dessutom kan en del av de här svaren vara utförligare än vad som behövs för full poäng på tentan. 1. In Swedish: trädgårdsmästare(pnr, namn) -- primärnyckel: pnr äppelsort(sortnr, sortnamn) -- primärnyckel: sortnr äppelträd(trädnr, sortnr, x, y) -- primärnyckel: trädnr äpple(trädnr, äppelnr, plockdatum, vikt, mätare, mätdatum) -- primärnyckel: trädnr, äppelnr ansvarar(trädgårdsmästare, äppelträd) -- primärnyckel: trädgårdsmästare, äppelträd In English: gardener(pnr, name) -- primary key: pnr applesort(sortnr, sortname) -- primary key: sortnr appletree(treenr, sortnr, x, y) -- primary key: treenr apple(treenr, applenr, pickdate, weight, measurer, measurement_date) -- primary key: treenr, applenr responsible(gardener, appletree) -- primary key: gardener, appletree 2. (no solution yet) 3. a) select sortnr, sortname from appletree, applesort where appletree.sortnr = applesort.sortnr and x = 98 and y = 47 b) select distinct sortnr, sortname from appletree, applesort, responsible, gardener where gardener.name = 'Sven Svensson' and gardener.pnr = responsible.gardener and responsible.appletree = appletree.treenr and appletree.sortnr = applesort.sortnr c) select avg(weight) from apple, appletree, applesort where apple.treenr = appletree.treenr and appletree.sortnr = applesort.sortnr and applesort.sortname = 'Åkerö' d) create view number_measured as select pnr, count(*) as number from gardener, apple where pnr = measurer group by pnr select * from number_measured where number = (select max(number) from number_measured) 4. In a somewhat strange syntax: PROJECT sortname ((((SELECT name='Sven Svensson' GARDENER) JOIN pnr=gardener RESPONSIBLE) JOIN appletree=treenr APPLETREE) JOIN appletree.sortnr=applesort.sortnr APPLESORT) 5. /* InterBase 4.2 solution by Martin Sköld */ CREATE TABLE seatbookings /* primary key (fno, fdate, seat) */ (fno CHAR(10) NOT NULL, fdate DATE NOT NULL, seat INTEGER NOT NULL, smoking CHAR(1) NOT NULL, class INTEGER NOT NULL, passanger VARCHAR(56)); CREATE TABLE waiting /* primary key (fno, fdate, passanger) */ (fno CHAR(10) NOT NULL, fdate DATE NOT NULL, smoking CHAR(1) NOT NULL, class INTEGER NOT NULL, passanger VARCHAR(56), seat INTEGER); SET TERM ^ ; CREATE EXCEPTION no_flight "No available flight at chosen date"^ CREATE EXCEPTION illegal_section "Smoking can only be Y or N"^ CREATE EXCEPTION illegal_class "Class can only be 1 or 2"^ CREATE PROCEDURE book(passanger CHAR(56), fdate DATE, smoking CHAR(1), class INTEGER) RETURNS (free_fno CHAR(10), free_seat INTEGER) AS BEGIN IF ((:smoking <> 'Y') AND (:smoking <> 'N')) THEN EXCEPTION illegal_section; IF ((:class <> 1) AND (:class <> 2)) THEN EXCEPTION illegal_class; /* Choose flight */ SELECT DISTINCT sb.fno FROM seatbookings sb WHERE sb.fdate = :fdate AND sb.smoking = :smoking AND sb.class = :class INTO :free_fno; /* Here interaction with the passanger should really determine a unique flight! */ /* Choose free seat with the lowest number */ SELECT MIN(sb.seat) FROM seatbookings sb WHERE sb.passanger IS NULL AND sb.fdate = :fdate AND sb.smoking = :smoking AND sb.class = :class AND sb.fno = :free_fno GROUP BY sb.fno INTO :free_seat; IF (:free_seat IS NOT NULL) THEN UPDATE seatbookings sb SET sb.passanger = :passanger WHERE sb.fno = :free_fno AND sb.seat = :free_seat; ELSE IF (:free_fno IS NOT NULL) THEN /* no free seat in fno */ INSERT INTO waiting(fno, fdate, smoking, class, passanger, seat) VALUES (:free_fno, :fdate, :smoking, :class, :passanger, NULL); ELSE EXCEPTION no_flight; END^ /* PROCEDURE book */ CREATE PROCEDURE unbook(fno CHAR(10), fdate DATE, passanger VARCHAR(56)) AS BEGIN UPDATE seatbookings sb SET sb.passanger = NULL WHERE fno = sb.fno AND sb.fdate = :fdate AND sb.passanger = :passanger; END^ /* PROCEDURE unbook_proc */ CREATE TRIGGER monitor_unbook FOR seatbookings AFTER UPDATE AS DECLARE VARIABLE waiting_passanger VARCHAR(56); BEGIN IF (new.passanger IS NULL) THEN BEGIN SELECT w.passanger FROM waiting w WHERE w.fno = new.fno AND w.fdate = new.fdate AND w.smoking = new.smoking AND w.class = new.class INTO :waiting_passanger; IF (:waiting_passanger IS NOT NULL) THEN BEGIN UPDATE seatbookings sb SET sb.passanger = :waiting_passanger WHERE sb.fno = new.fno AND sb.fdate = new.fdate AND sb.seat = new.seat; UPDATE waiting w SET w.seat = new.seat WHERE w.passanger = :waiting_passanger AND w.fno = new.fno AND w.fdate = new.fdate; END END END^ /* TRIGGER monitor_unbook */ SET TERM ;^ /* Test data */ INSERT INTO seatbookings (fno, fdate, seat, smoking, class, passanger) VALUES ("SK496", "1-JAN-2000", 1, 'N', 1, NULL); INSERT INTO seatbookings (fno, fdate, seat, smoking, class, passanger) VALUES ("SK496", "1-JAN-2000", 2, 'N', 1, NULL); INSERT INTO seatbookings (fno, fdate, seat, smoking, class, passanger) VALUES ("SK496", "1-JAN-2000", 3, 'N', 1, NULL); INSERT INTO seatbookings (fno, fdate, seat, smoking, class, passanger) VALUES ("SK496", "1-JAN-2000", 4, 'N', 1, NULL); INSERT INTO seatbookings (fno, fdate, seat, smoking, class, passanger) VALUES ("SK496", "1-JAN-2000", 5, 'N', 1, NULL); INSERT INTO seatbookings (fno, fdate, seat, smoking, class, passanger) VALUES ("SK496", "1-JAN-2000", 6, 'N', 1, NULL); INSERT INTO seatbookings (fno, fdate, seat, smoking, class, passanger) VALUES ("SK496", "1-JAN-2000", 7, 'Y', 1, NULL); INSERT INTO seatbookings (fno, fdate, seat, smoking, class, passanger) VALUES ("SK496", "1-JAN-2000", 8, 'Y', 1, NULL); INSERT INTO seatbookings (fno, fdate, seat, smoking, class, passanger) VALUES ("SK496", "1-JAN-2000", 9, 'N', 2, NULL); INSERT INTO seatbookings (fno, fdate, seat, smoking, class, passanger) VALUES ("SK496", "1-JAN-2000", 10, 'N', 2, NULL); INSERT INTO seatbookings (fno, fdate, seat, smoking, class, passanger) VALUES ("SK496", "1-JAN-2000", 11, 'N', 2, NULL); INSERT INTO seatbookings (fno, fdate, seat, smoking, class, passanger) VALUES ("SK496", "1-JAN-2000", 12, 'N', 2, NULL); INSERT INTO seatbookings (fno, fdate, seat, smoking, class, passanger) VALUES ("SK496", "1-JAN-2000", 13, 'N', 2, NULL); INSERT INTO seatbookings (fno, fdate, seat, smoking, class, passanger) VALUES ("SK496", "1-JAN-2000", 14, 'N', 2, NULL); EXECUTE PROCEDURE book("Joe Smith", "1-JAN-2000", 'N', 1); FREE_FNO FREE_SEAT ========== =========== SK496 1 EXECUTE PROCEDURE book("Charles Smith", "1-JAN-2000", 'Y', 1); FREE_FNO FREE_SEAT ========== =========== SK496 7 EXECUTE PROCEDURE book("Lucille Smith", "1-JAN-2000", 'Y', 1); FREE_FNO FREE_SEAT ========== =========== SK496 8 EXECUTE PROCEDURE book("Edward Smith", "1-JAN-2000", 'N', 2); FREE_FNO FREE_SEAT ========== =========== SK496 9 EXECUTE PROCEDURE book("Unlucky Smith", "1-JAN-2000", 'Y', 1); FREE_FNO FREE_SEAT ========== =========== SK496 SELECT * FROM seatbookings; FNO FDATE SEAT SMOKING CLASS PASSANGER ========== =========== =========== ======= =========== ============= SK496 1-JAN-2000 1 N 1 Joe Smith SK496 1-JAN-2000 2 N 1 SK496 1-JAN-2000 3 N 1 SK496 1-JAN-2000 4 N 1 SK496 1-JAN-2000 5 N 1 SK496 1-JAN-2000 6 N 1 SK496 1-JAN-2000 7 Y 1 Charles Smith SK496 1-JAN-2000 8 Y 1 Lucille Smith SK496 1-JAN-2000 9 N 2 Edward Smith SK496 1-JAN-2000 10 N 2 SK496 1-JAN-2000 11 N 2 SK496 1-JAN-2000 12 N 2 SK496 1-JAN-2000 13 N 2 SK496 1-JAN-2000 14 N 2 SELECT * FROM waiting; FNO FDATE SMOKING CLASS PASSANGER SEAT ========== =========== ======= =========== ============= =========== SK496 1-JAN-2000 Y 1 Unlucky Smith EXECUTE PROCEDURE unbook("SK496", "1-JAN-2000", "Charles Smith"); SELECT * FROM seatbookings; FNO FDATE SEAT SMOKING CLASS PASSANGER ========== =========== =========== ======= =========== =============== SK496 1-JAN-2000 1 N 1 Joe Smith SK496 1-JAN-2000 2 N 1 SK496 1-JAN-2000 3 N 1 SK496 1-JAN-2000 4 N 1 SK496 1-JAN-2000 5 N 1 SK496 1-JAN-2000 6 N 1 SK496 1-JAN-2000 7 Y 1 Unlucky Smith SK496 1-JAN-2000 8 Y 1 Lucille Smith SK496 1-JAN-2000 9 N 2 Edward Smith SK496 1-JAN-2000 10 N 2 SK496 1-JAN-2000 11 N 2 SK496 1-JAN-2000 12 N 2 SK496 1-JAN-2000 13 N 2 SK496 1-JAN-2000 14 N 2 SELECT * FROM waiting; FNO FDATE SMOKING CLASS PASSANGER SEAT ========== =========== ======= =========== ============= =========== SK496 1-JAN-2000 Y 1 Unlucky Smith 7 6. - Replication: The same data on more than one node (server). Usually entire relations are replicated. When data is updated the DBMS guarantees that the replicas are up-to-date. In some systems the freshness of the replicas is not guaranteed and it is up to the user to deal with this inconsistency. - Fragmentation: Different pieces of a relation on several nodes. The fragmentation is transparent so that the DBMS guarantees that the correct fragemnts are modifies - Replication is favorable when there are few updates, many reads, and few replicas. The more updates and the more nodes the cost of maintaining consistencies of the replicas increases rapidly. Also unreliable nodes and networks increases the cost of maintaining consistency of replicas. The load can be balanced with replicas on different nodes since any replica can be used in accessing the data. The availability of data is higher, since another replica can be used when some node with a replica crashes. - Fragmentation is favorable when there are many accesses and updates and when these access patterns can be rather evenly spread over the fragments.(E.g. when the access pattern reflect different global locations). Fragmentation also improves query execution by allowing more parallelism. It also improves the availability since other parts of a relation are still available if one node with its fragments crashes. - Since every node needs access to schema information, and, since schema information does not change rapidly, schema information is replicated. 7. (E var) in the formulae below stands for (EXISTS var). It is still OK if you omitted the (E var). - Tuple calculus: {S.NAME | (E D)(E E): EMPLOYEE(E) AND DEPARTENT(D) AND EMPLOYEE(S) AND E.NAME = "Kalle Karlsson" AND D.DNO = E.DNO AND D.SECR = S.SSN} - Domain calculus: {NAM | (E SSN)(E SAL)(E DNO)(E ADD)(E PHO)(E DNAM)(E MGR)(E SEC) (E LOC)(E SAL1)(E DNO1)(E ADD1)(E PHO1) EMPLOYEE(SSN,"Kalle Karlsson",SAL,DNO,ADD,PHO) AND DEPARTMENT(DNO,DNAM,MGR,SEC,LOC) AND EMPLOYEE(SEC,NAM,SAL1,DNO1,ADD1,PHO1)} Alternative solution where _ denote anonymous existentially quantified variables. {NAM | (E SSN)(E DNO)(E SEC) EMPLOYEE(SSN,"Kalle Karlsson",_,DNO,_,_) AND DEPARTMENT(DNO,_,_,SEC,_) AND EMPLOYEE(SEC,NAM,_,_,_)} 8. - Create ordered seconday index (B-tree) on SALES. - Make the following SQL in your application: SELECT NAME, SALES FROM SALESMEN ORDER BY SALES - Read only the 10 first tuples from the result. - The method works since the query optimizer estimates the time to sort the result, and, in this case, it can avoid sorting by traversing the already sorted index on SALES. (The sort order does not matter; the index can be searched backwards too.) Furthermore, since the result of a query is always delivered as a stream (scan, rowset) the DBMS will deliver only as many tuples as are fetched by the application.