rolisz's site

Subiect GTBDD

Domnul profesor Dan Suciu.

Profesor: Dan Suciu.

A. (30%) Selectați răspunsul corect pentru ur­mă­toarele întrebări (un singur răspuns este corect pentru toate în­tre­bările). Fiecare întrebare are aceeași pondere în notare.

1. Care din ur­mă­toarele atribute nu reprezintă o stare validă a unei tranzacții?

a) Active b) Suspended c) Committed d) Aborted

2. Care dintre ur­mă­toarele afirmații descrie cel mai fidel pro­pri­etatea de izolare a tran­za­cți­ilor?

a) Odată ce tranzacția s-a comis, mod­i­ficările făcute trebuie să persiste în baza de date chiar dacă sistemul eșuează. b) Toate acțiunile tran­za­cției se execută sau nu se execută nici una. c) O tranzacție lasă baza de date într-o stare con­sis­ten­tă dacă baza de date se afla într-o stare con­sis­ten­tă înainte de începerea executării tran­za­cției. d) O tranzacție se poate executa in­de­pen­dent fără a lua în con­sid­er­are efectele secundere ce le-ar putea avea asupra altor tranzacții aflate în execuție.

  1. La ce se referă anomalia de execuția concurentă Phantom Read?

a) La două citiri con­sec­u­tive ale aceluiași obeict se obțin valori diferite b) La două selecții con­sec­u­tive, numărul în­reg­istrărilor returnate diferă c) La două modificări con­sec­u­tive se păstrează prima valoare asignată d) La a doua citire a unui obiect se aruncă o excepție

4. Care din ur­mă­toarele plan­i­ficări sunt conflict echiva­lente: S1: R3(Z) R1(Z) W1(X) W2(Y) R2(X) R1(X) W2(x) W1(x) R3(Y) S2: R3(Z) R3(Y) R1(Z) W1(X) W2(Y) R1(X) R2(X) W2(x) W1(x) S3: R1(Z) W1(X) W2(Y) R1(X) R2(X) W2(x) W1(x) R3(Z) R3(Y) S4: R3(Z) R1(Z) W1(X) W2(Y) R2(X) R1(X) W1(x) W2(x) R3(Y)

a) S1 și S2, b) S2 și S4, c) S2 și S3, d) S1 și S3.

5. Inițial, uti­liza­torul A e pro­pri­etarul tabelei R, și nici un alt utilizator nu are privilegii asupra lui R. Se execută ur­mă­toarele comenzi de către uti­liza­torii A,B, C și D: A: GRANT INSERT ON R TO B WITH GRANT OPTION; B: GRANT INSERT ON R TO C WITH GRANT OPTION; C: GRANT INSERT ON R TO D WITH GRANT OPTION; D: GRANT INSERT ON R TO B WITH GRANT OPTION; B: REVOKE INSERT ON R FROM C CASCADE;

Care este mulțimea uti­liza­to­rilor care au priv­i­legiul de INSERT pe tabela R?

a) Doar A b) Doar A și B c) Doar A, B și D d) A, B, C și D

6. Care dintr ur­mă­toarele perechi de pro­pri­etăți ale unuei tranzacții e garantată prin acțiunile de recuperare a datelor?

a) Con­sis­tența și In­tegri­tatea b) Dura­bil­i­tatea și Atom­ic­i­tatea c) Concurența și Izolarea d) Com­pleti­tudinea și Dis­junc­tiv­i­tatea

7. Care este nivelul de izolare implicit în MS SQL Server?

a) Read un­com­mit­ted; b) Read committed; c) Repeatable reads; d) Se­ri­al­iz­able.

8. Fiind dată o tabelă memorată pe 10.000 pagini și că în buffer sunt 3 pagini libere, câte subșiruri sortate se vor crea la primul pas al unui algoritm general de sortare externă?

a) 3334 subșiruri (ultimul având 1 pagină) b) 1667 subșiruri (ultimul având 4 pagini) c) 3333 subșiruri (ultimul având 1 pagină) d) 3333 subșiruri (ultimul având 4 pagină)

9. Care din ur­mă­toarele afirmații nu se referă la replicarea asincronă cu site principal??

a) Orice modificare efectuată pe o copie principală este propagată imediat copiilor secundare, după care se continuă ac­tiv­i­tatea b) Dintre toate copiile, una singură este ... și aceasta este publicată c) Copiile secundare nu vor putea fi modificate direct ... d) ....

10. Care dintre ur­mă­toarele afirmații despre plan­i­fi­carea de mai jos este adevărată?

T1T2T3


Read(D)


a) este serială b) este se­ri­al­iz­abilă c) nu poate fi generată dacă se urmează un protocol de blocare 2PL d) poate fi generată dacă se urmează un protocol de blocare 2PL strict

11. Fie două plan­i­ficări distincte S și S' formate din diferite aranjări ale tran­za­cți­ilor T1,..., Tn, unde n > 1. Dacă S este se­ri­al­iz­abil și S' nu este se­ri­al­iz­abil, se poate afirma că:

a) S și S' nu sunt echiva­lente b) S și S' sunt echiva­lente c) nu este posibil s[ se determine o conexiune între ele d) condițiile din descrierea problemei nu pot exista

12. Ce înseamnă frag­mentare orizontală derivată a datelor??

a) Este frag­mentarea ce se aplică după ce tabela a mai fost frag­men­tată o dată b) Sunt fragmente ce nu conțin cheia primară a tabelei frag­men­tate c) Frag­mentarea se face pe baza legă­turilor spre fragmente ale altor tabele d) Toate frage­mentele includ un set fix de în­reg­istrări, numit set derivat

13. Fie o bază de date cu o tabelă R și uti­liza­torii Alice, Bob, Carol și Dave. Alice este pro­pri­etarul tabelei R. Este executată următoarea secvență de comenzi:

Alice: GRANT SELECT ON R TO Bob WITH GRANT OPTION Alice: GRANT SELECT ON R TO Carol WITH GRANT OPTION Carol: GRANT SELECT ON R TO Bob WITH GRANT OPTION Bob: GRANT SELECT ON R TO Dave WITH GRANT OPTION Carol: GRANT SELECT ON R TO Dave Dave: GRANT SELECT ON R TO Carol WITH GRANT OPTION Alice: REVOKE SELECT ON R FROM Bob CASCADE

Care dintre ur­mă­toarele afirmații este adevărată după execuția acestor comenzi?

a) Dave are priv­i­legiul de SELECT pe R, dar fără opțiune de GRANT. b) Dave nu are priv­i­legiul de SELECT pe R. c) Dave are priv­i­legiul de SELECT pe R cu opțiune de GRANT d) Dave are opțiune de GRANT pentru SELECT pe R, fără însă ca el să deține priv­i­legiul pe această operație.

14. De ce algoritmul System R ia în con­sid­er­are doar planurile de execuție bazați pe arbori binari construiți dinspre stânga spre dreapta (left-deep join)?

a) Aceștia include toate planurile ce suportă pipeline. b) Aceștia op­ti­mizează costul op­er­a­toru­lui produs cartezian c) Aceștia includ planul de execuție optimal d) Aceștia op­ti­mizează costul op­er­a­toru­lui de selecție

15. Care este prin­ci­palul avantaj al utilizării pipeline-ului în procesul de optimizare a in­terogărilor.

a) Re­stricționează spațiul de căutare al planurilor de execuție b) Fa­cilitează evitarea utilizării de tabele temporare c) Op­ti­mizează costul op­er­a­toru­lui join d) Op­ti­mizează costul op­er­a­toru­lui de selecție

B. (20%)

Fie următorul log al unei baze de date:

<start_transaction, T1>; <T1, A, 50, 20>; <start_transaction, T2>; <T2, B, 250, 20>; <T1, A, 40, 50>; <T2, C, 35, 20>; <T2, D, 45, 20>; <commit, T1>; <start_transaction, T3>; <T3, E, 55, 20>; <T2, D, 50, 45>; <T2, C, 65, 35>; <commit, T2>; <start_transaction, T4>; <T4, F, 100, 20>; <T4, G, 110, 20>; ; ; <T4, F, 150, 100>; ; <T5, D, 40, 300> <T6, E, 49, 140> . Pre­supunând că o intrare în log are forma \<XID, Obiect, Val­oare­Nouă, Val­oareVeche>, care vor fi valorile obiectelor A, B, C, D, E, F și G salvate pe disc după efectuarea procesului de recuperare a datelor:

a. Dacă sistemul este întrerupt chiar înainte de a scrie \<start_­trans­ac­tion, T4> în log? b. Dacă sistemul este întrerupt chiar înainte ca \<commit T4> să fie salvat în log?

C. (50%)

Fie tabelele T(A,B,C) și V(D,E,F) având ur­mă­toarele pro­pri­etăți: T are 20.000 în­reg­istrări, V are 45.000 în­reg­istrări, 25 de în­reg­istrări ale lui T intră într-o pagină și 30 în­reg­istrări ale lui V într-o pagină. Buffer-ul conține 102 pagini de memorie goale. Estimați costul evaluării folosind Simple Nested Loops Join, Page Oriented Nested Loops Join și Block Nested Loops Join pentru:

SELECT * FROM T,V WHERE T.A = V.D

Se vor considera ambele cazuri în care T joacă rol de tabelă externă, respectiv internă.

Timp de lucru 2:30h

It's that jolly time of the year, când crește numărul de vizite pe blogul meu :D/ (aka, sesiune)

Să începem cu practicul la GTBDD:

1. Fie tabelele:

Ponderi (materie, proba, pondere) Rezultate (student, materie, proba, nota)

Scrieti o procedura stocata in care se verifica existenta tabelelor (daca exista – se sterg si se re-creeaza, daca nu exista – se creeaza) si se insereaza cateva in­reg­is­trari.

Observatie: Tipurile coloanelor sunt la alegere.

2. Scrieti un trigger pentru cele trei operatii INSERT, UPDATE si DELETE pentru tabelul Rezultate, pentru oricate in­reg­is­trari afectate de operatia de­clansatoare.

Daca operatia de­clansatoare este INSERT, nota inserata trebuie sa fie in intervalul [4, 10], iar in tabelul Ponderi trebuie sa existe perechea materie + proba. De asemenea, nu se permite ca un student sa aiba mai multe note la aceeasi materie, aceeasi proba.

Operatiile UPDATE si DELETE nu sunt permise.

Daca pentru cel putin o in­reg­is­trare nu sunt verificate conditiile de mai sus, se anuleaza intreaga operatie.

Se cere ver­i­fi­carea trigger-ului.

3. Scrieti o procedura stocata care calculeaza si afiseaza urmatoarea situatie: pentru fiecare student, disciplina, media probelor la disciplina respectiva (pentru fiecare student, pentru fiecare disciplina la care are cel putin o proba data). Daca vreun student nu are nota la vreo proba a unei discipline, i se calculeaza media cu nota 0 (zero) la proba respectiva. Procedura va intoarce printr-un parametru de iesire numarul total de studenti promovati. Sa se apeleze aceasta procedura din cod Transact-SQL si sa se afiseze valoarea para­metru­lui de iesire.

Punctaj:                                                          Timp de lucru: 1:40 h


Oficiu 1 1 2 2 3.5 3 3.5


Subiectul de la celălalt rând:

1. Fie tabelele

Proiecte (idpr, denumire, deadline)

Par­tic­i­pari (idprangajat, dela, panala)

Scrieti o procedura stocata in care se verifica existenta tabelelor (daca exista – se sterg si se re-creeaza, daca nu exista – se creeaza) si se insereaza cateva in­reg­is­trari.

Observatie: Tipurile coloanelor sunt la alegere.

2. Scrieti un trigger pentru cele trei operatii INSERT, UPDATE si DELETE pentru tabelul Par­tic­i­pari, pentru oricate in­reg­is­trari afectate de operatia de­clansatoare.

Daca operatia de­clansatoare este INSERT sau UPDATE, se verifica dela \<= panala, dela \<= deadline si panala \<= deadline, unde deadline este al proiec­tu­lui core­spun­za­tor.

Operatia de stergere nu este permisa.

Valorile idpr si angajat se con­cate­neaza intr-un mesaj care va fi afisat in final pentru acele in­reg­is­trari pentru care conditiile nu au fost in­depli­n­ite. (se rezolva cat se poate)

Se cere ver­i­fi­carea trigger-ului.

3. Scrieti o procedura stocata care afiseaza urmatoarea situatie: pentru fiecare proiect, numarul mediu de zile lucrate de angajati la acesta, numarul an­ga­jatilor care au participat la acesta si numarul de zile pana la deadline (daca nu a fost atins) sau -1 daca proiectul e finalizat. Procedura va intoarce printr-un parametru de iesire numarul de proiecte finalizate. Sa se apeleze aceasta procedura din cod Transact-SQL si sa se afiseze valoarea para­metru­lui de iesire.

Punctaj:                                                          Timp de lucru: 1:40 h


Oficiu 1 1 2 2 3.5 3 3.5


1. Procedura de creare a tabelelor:

IF EXISTS(SELECT * FROM sysobjects WHERE xtype='P' AND name='creare') DROP PROC creare GO{lang="sql"}

CREATE PROC creare AS BEGIN IF EXISTS(SELECT * FROM sysobjects WHERE xtype='U' AND name='Ponderi') DROP TABLE Ponderi

CREATE TABLE Ponderi (materie varchar(30), proba varchar(30), pondere int, primary key (materie, proba))

IF EXISTS(SELECT * FROM sysobjects WHERE xtype='U' AND name='Rezul­tate') DROP TABLE Rezultate

CREATE TABLE Rezultate (student varchar(30), materie varchar(30), proba varchar(30), nota int)

INSERT INTO Ponderi VALUES ('Analiza', 'Scris', 80) INSERT INTO Ponderi VALUES ('GTBDD', 'Lab­o­ra­tor', 50) INSERT INTO Ponderi VALUES ('GTBDD', 'Scris', 50)

INSERT INTO Rezultate VALUES ('Ionescu', 'Analiza', 'Scris', 8) INSERT INTO Rezultate VALUES ('Popescu', 'GTBDD', 'Lab­o­ra­tor', 10) INSERT INTO Rezultate VALUES ('Tu­dores­cu', 'GTBDD', 'Scris', 8) INSERT INTO Rezultate VALUES ('Popescu', 'GTBDD', 'Scris', 6)

END

GO EXEC creare

  1. Trigger-ul:

IF EXISTS(SELECT * FROM sysobjects WHERE xtype='TR' AND name='tr1') DROP TRIGGER tr1 GO{lang="sql"}

CREATE TRIGGER tr1 ON Rezultate AFTER Insert,Update, Delete AS BEGIN IF EXISTS(SELECT * FROM deleted) rollback IF EXISTS(SELECT * FROM inserted WHERE nota \< 4 OR nota > 10) rollback IF EXISTS(SELECT * FROM inserted LEFT JOIN Ponderi ON inserted.materie = Ponderi.materie AND inserted.proba = Ponderi.proba WHERE pondere IS NULL) rollback IF EXISTS(SELECT count(*) FROM (SELECT student, materie, proba, nota FROM Rezultate UNION SELECT student, materie, proba, nota FROM inserted) as x GROUP BY student, materie, proba HAVING count(*) > 1) rollback END

3. Procedura:

IF EXISTS(SELECT * FROM sysobjects WHERE xtype='P' AND name='raport') DROP PROC raport GO{lang="sql"}

CREATE PROC raport @nr int output AS BEGIN SELECT student,Rezultate.materie, CONVERT(float, SUM(pon­dere*no­ta))/100 as MEDIE FROM Rezultate JOIN Ponderi ON Rezultate.materie = Ponderi.materie AND Rezultate.proba = Ponderi.proba GROUP BY student, Rezultate.materie SELECT @nr = count(*) FROM (SELECT student,Rezultate.materie, CONVERT(float, SUM(pon­dere*no­ta))/100 as medie FROM Rezultate JOIN Ponderi ON Rezultate.materie = Ponderi.materie AND Rezultate.proba = Ponderi.proba GROUP BY student, Rezultate.materie) as S WHERE medie > 4 END

DECLARE @rez int EXEC raport @rez output print(@rez)

4. ???

5. Profit