Rezolvare:

  1. Se cer instrucțiuni SQL de creare a tabelelor, în 3NF, dintr-o bază
    de date relațională ce memorează următoarele informații dintr-o școală:
  • elevi: cod, nume, o medie generală
  • clase: cod, profil, nume diriginte, elevii din clasă
  • parinti: nume, adresa, elevii pe care îi au în școală

Justificați că tabelele construite sunt în 3NF.

Rezolvare:

CREATE TABLE elevi (cod int primary key, nume varchar(30), medie float, cod_clasa int) CREATE TABLE clase (cod int primary key, profil varchar(30), diriginte varchar(30)) CREATE TABLE parinti (nume varchar(30), adresa varchar(30), cod_elev int, primary key(nume,adresa,cod_elev)){lang="sql"}

E în 1NF pentru că nu există atribute repetitive. E în 2NF pentru că nu
avem dependențe funcționale parțiale. Este în 3NF pentru că nu avem
dependențe tranzitive.

Update:
Unii au zis că e problemă faptul că în tabela părinți se tot repetă
nume+adresă. O soluție alternativă ar fi să creăm o variabilă surogată,
și apoi o tabelă de legătură între părinți și copii.

CREATE TABLE parinti (codP int primary key, nume varchar(30), adresa varchar(30)) CREATE TABLE relatii (codP int, codE int, primary key(codP,codE)){lang="sql"}

2. Dați un exemplu de relație în care există o dependență funcțională.
Justificați dacă este bine sau nu ca relația să fie memorată în această
formă. Exemple pentru afirmația aleasă.

Rezolvare:

Relația: CodProdus | CodFiscalFurnizor | DenumireFurnizor (CodProdus
e cheie primară). DenumireFurnizor depinde de CodFiscalFurnizor,
deoarece acesta îl determină în mod unic. Nu e bine ca relația să fie
păstrată astfel, deoarece la o schimbare a uneia din ele valori, trebuie
avut grijă ca să se efectueze aceași schimbare la toate rândurile care
au avut aceleași valori inițial, și astfel se ot introduce inconsistențe
în baza de date.

3. Transformați interogarea SQL: "Select X,Y,Z From A,B,C where A.U =
B.U and A.V = C.V order by X,Y" într-o interogare din algebra
relațională. Transformați expresia într-o variantă optimă. Descrieți o
situație pentru care expresia se evaluează repede, fără determinarea
produsului cartezian. Ce rol are un eventual index? Justificați
răspunsurile.

Rezolvare:

$ Pi_{{X,Y,Z}} ( S_{X,Y} ( sigma_{A.U = B.U and A.V = C.V} (A
times B times C))) Leftrightarrow Pi_{{X,Y,Z}} (S_{X,Y} ((A
otimes_{A.U = B.U} B )otimes_{A.V=C.V} C )) Leftrightarrow
Pi_{{X,Y,Z}} (S_{X,Y} ((Pi_{{X,U,V}} (A) otimes_{A.U = B.U}
Pi_{{Y,U}}(B) )otimes_{A.V=C.V} Pi_{{Z,V}}(C) ) $.

Se folosește asociativitatea produsului cartezian, distributivitatea
selecției față de produs cartezian, echivalența dintre selecție cu
produs cartezian și join condițional și la sfârșit distributivitatea
proiecției față de join.

Evaluarea se face repede în cazul în care se folosește algoritmul Merge
Join la evaluarea joinurilor condiționale. Dacă există un index pe unul
din tabele, joinul condițional se poate face cu algoritmul Hybrid Merge
Join, astfel îmbunătățing performanța lui.

4. Pentru baza de date de la punctul 1 se cer instrucțiuni SQL care
determină:

  1. Numele părinților (din baza de date) cu cel mai mare număr de elevi
    (în baza de date).
  2. Codul claselor în care există elevi ai căror părinți nu sunt în baza
    de date.
  3. Pentru fiecare clasă se cere numărul de elevi și media generală
  4. Pentru elevul cu media cea mai mare din fiecare clasă se cere numele
    părinților (sau valoarea null dacă elevul nu are părinți în baza de
    date).

Rezolvare:

  1. SELECT nume,adresa FROM parinti GROUP by nume,adresa HAVING count(*) = (SELECT max(c) FROM (SELECT count(*) as c FROM parinti GROUP by nume,adresa) as x) {lang="sql"}
  2. SELECT cod_clasa FROM elevi LEFT JOIN parinti ON elevi.cod = parinti.cod_elev WHERE parinti.nume IS null{lang="sql"}
  3. SELECT count(*) as nr, AVG(elevi.medie) as medie_gen FROM elevi GROUP BY elevi.cod_clasa{lang="sql"}
  4. SELECT parinti.nume FROM elevi LEFT JOIN parinti ON elevi.cod = parinti.cod_elev WHERE medie = (SELECT max(x.medie) FROM elevi as x WHERE x.cod_clasa = elevi.cod_clasa) {lang="sql"}

5. Trei dintre cerințele de la problema 4 (la alegere) se vor rezolva cu
algebra relațională.

Rezolvare:

b. $ Pi_{cod_clasa} (sigma_{parinti.nume is null}(elevi
otimes_{elevi.cod = parinti.cod_elev} parinti)) $

c. $ gamma_{{count(*),AVG(medie)} group by {cod_clasa}} (elevi)
$

d. $ Pi_{parinti.nume}(sigma_{medie =
gamma_{max(x.medie)}(sigma_{x.cod_clasa = elevi.cod_clasa}(elevi as
x))} (elevi otimes_{elevi.cod = parinti.cod_elev} parinti)) $

6. Pentru baza de date de la punctul 1 se cer proceduri în VB.Net pentru
rezolvarea următoarelor probleme

  1. Într-un listbox construit se cere adăugarea numelor părinților, în
    ordine alfabetică.
  2. La alegerea unui părinte din lista precedentă se cer următoarele
    date (într-o zonă text): numărul de elevi pe care-i are acest
    părinte, media generală a acestor elevi, elevul (nume, clasa, media)
    cu media cea mai mare dintre acești elevi.

Explicați semnificația fiecărei variabile, proprietate sau metodă
folosită.

Rezolvare:

a.

private void incarca_parinti() { SqlConnection conn = new SqlConnection(); // conexiunea cu baza de date conn.ConnectionString = "..."; // informatiile pentru autentificarea cu baza de date conn.Open(); SqlCommand cmd = new SqlCommand(); // obiectul folosit pentru efectuarea comenzilor asupra bazei de date cmd.Connection = conn; //asociem conexiunea{lang="c#"}

cmd.CommandText = "SELECT nume FROM parinti ORDER BY nume";
SqlDataReader dr = cmd.ExecuteReader(); //executam comanda si obtinem
un data reader cu informatiile cerute

parinti.Items.Clear(); // golim listboxul

while (dr.Read()) {
parinti.Items.Add(dr.GetString(0)); //introducem in listbox numele
parintelui, care se afla in prima coloana a randului curent
}
dr.Close();
conn.Close(); // inchidem obiectul data reader si conexiunea
}

b.
private void schimbare_selectie() { conn.Open(); // e acelasi ca mai sus cmd.CommandText = "SELECT count(*), AVG(elevi.medie),x.nume,x.cod_clasa,x.medie FROM parinti JOIN elevi ON elevi.cod = parinti.cod_elev JOIN (SELECT nume,cod_clasa,cod, medie FROM elevi WHERE medie = (SELECT max(medie) FROM elevi as y JOIN parinti as z ON y.cod = z.cod_elev WHERE z.nume = @n)) as x ON parinti.cod_elev = x.cod WHERE parinti.nume = @n GROUP BY x.nume,x.cod_clasa,x.medie"; //feast yer eyes{lang="c#"}

SqlParameter n = new SqlParameter();
n.ParameterName = "@n";
n.Value = parinti.Selected_Item;
cmd.Parameters.Clear();
cmd.Parameters.Add(n);

SqlDataReader dr = cmd.ExecuteReader();
if (dr.Read() {
text_area.Text = dr.GetInt32(0).ToString() + " " +
dr.GetFloat(1).ToString() + " " + dr.GetString(2) + " " +
dr.GetInt32(3).ToString() + " " + dr.GetInt32(4).ToString()
}
dr.Close();
conn.Close();
}

Aștept comentarii, sugestii, îmbunătățiri.

1. Se cer instrucțiuni SQL de creare a tabelelor, în 3NF, într-o bază de
date relațională ce memorează următoarele informații despre examenele
date de studenți dintr-o facultate:

  • discipline: cod, denumire, număr
    credite, lista studenților care au dat examen la disciplina
    respectivă
  • studenți: cod, nume, data nașterii, grupa, anul de studiu,
    specializarea, lista disciplinelor la care a dat examene (inclusiv
    data examenului și nota obținută).

Justificați că tabele construite sunt în 3NF.

2. Explicați cel puțin trei reguli (restricții) de integritate pentru
modelul relațional. Exemplificați pentru baza de date de la punctul 1.

3. Pentru baza de date de la punctul 1 se cer instrucțiuni SQL care
determină:

  1. Studenți (nume, grupa) care nu au
    notă la disciplina cu denumirea „Baze de date”
  2. Disciplinele pentru care nu există note
  3. Studenți (nume, grupa, nr. discipline promovate) ce au promovat în
    anul 2012 pentru 5 discipline. Dacă un student are la o disciplină
    mai multe examene cu note de promovare, atunci disciplina se numără
    o singură dată.

Explicați fiecare instrucțiune.

4. Se cer expresii în algebra relațională pentru rezolvarea cerințelor
de la problema 3.

5. Pentru baza de date de la punctul 1 se cer două funcții ce furnizează
următoarele valori:

  1. Un obiect DataTable cu codul
    grupelor ce au studenți și nr. de studenți din grupă
  2. Pentru un cod de grupă (dat ca parametru, de tip șir de caractere),
    se cere un DataTable ce conține lista studenților (Cod, Nume,
    Specializarea, Nr. examene date) ce aparțin la această grupă.

Datele furnizate de una din aceste funcții se va extrage (într-o zonă de
text, sau grid, sau fișier)

Explicați semnificația fiecărei variabile, proprietate sau metodă
folosită.

6. a. Pentru interogarea de la punctul 3b determinați expresia
relațională echivalentă. Transformați această expresie în una optimală
(explicați regulile folosite la transformare).

b. Precizați un algoritm pentru evaluarea unui operator de join care nu
necesită un produs cartezian.

Punctaj: 1.5, 0.5, 1.5, 1, 1.5, 1, Oficiu 3

Termen: 2 ore

Mersi Bianca, pentru poza ta, care o ieșit mai bine decât poza făcută de
mine =)))

1. Se cer instrucțiuni SQL de creare a tabelelor, în 3NF, dintr-o bază
de date relațională ce memorează următoarele informații dintr-o firmă
soft:

  • activități: cod activitate (este
    cheie), descriere, tip activitate,
  • angajați: cod angajat (este cheie), nume, lista activităților la
    care participă, echipa din care face parte,
  • echipe: cod, denumire, lista angajaților din echipă, liderul
    echipei,

unde:

  • un angajat face parte dintr-o
    singură echipă, iar echipa are un lider (care este angajat al
    firmei),
  • un angajat participă la realizarea mai multor activități, iar la o
    activitate participă mai mulți angajați.

Justificați că tabelele obținute sunt în 3NF.

2. Descrieți o metodă de memorare a unui B-arbore de ordin 3.

3. Pentru baza de date de la punctul 1 se cer instrucțiuni SQL care
determină:

  1. Angajații care lucrează la o
    activitate de tipul TA1 și nu lucrează la o activitate de tipul
    TA2.
  2. Angajații care sunt liderii unei echipe cu cel puțin 10 angajați.
  3. Angajații care sunt în aceași echipă cu un angajat precizat prin
    nume.

Explicați fiecare instrucțiune.

4.Se cer expresii în algebra relațională pentru rezolvarea cerințelor de
la problema precedentă.

5. Pentru baza de date de la punctul 1 se cer două funcții în .NET ce
furnizează următoarele valori:

  1. Un obiect DataTable cu echipele din
    baza de date.
  2. Pentru un cod de echipă (cod se dă ca parametru în funcție, de tip
    șir de caractere), se cere un DataTable ce conține lista angajaților
    din această echipă.

Datele furnizate de una din aceste funcții se va extrage (într-o zonă
text, sau grid, sau fișier)

Explicați semnificația fiecărei variabile, proprietate sau metodă
folosită.

6. a. Pentru interogarea de la punctul 3c determinați expresia
echivalentă în algebra relațională. Transformați această expresie în una
optimală (explicați regulile folosite la transformare).

b. Descrieți un algoritm, în pseudocod, pentru evaluarea unui operator
de join care nu necesită un produs cartezian.

Punctaj și termen la fel ca la celălalt.

Thanks István!