curs programare web Tipuri de relaţii între tabele în MySql Unul din scopurile unei proiectări bune ale unei baze de date este de a elimina redundanța datelor (datele dublură). Pentru a atinge acest obiectiv, datele se împart în mai multe tabele în funcție de subiect, astfel încât fiecare aspect să fie reprezentat o singură dată. Apoi, i se furnizează programului mijloacele prin care să aducă la un loc informațiile care au fost împărțite — acest lucru se face prin plasarea câmpurilor comune în tabele care sunt asociate. Pentru a realiza acest pas corect, trebuie să înțelegeți ce sunt relațiile dintre tabele, apoi trebuie să specificați relații în baza de date.
Introducere După ce ați creat un tabel în baza de date pentru fiecare subiect, trebuie să furnizați programului MySql mijloacele de a aduce informațiile la un loc atunci când aveți nevoie de ele. Faceți aceasta plasând câmpurile comune în tabele care sunt asociate, apoi definind relații între tabele. Apoi se pot crea interogări, formulare și rapoarte care afișează informații din mai multe tabele odată. De exemplu, formularul afișat aici include informații extrase din mai multe tabele:
Informațiile din acest formular provin din tabelul Clienți... ...tabelul Comenzi... ...tabelul Produse... ...și tabelul Detalii comenzi. Numele clientului din caseta Facturat către: este regăsit din tabelul Clienți, valorile IDComandă și Datăcomandă provin din tabelul Comenzi, denumire este furnizat din tabelul Produse, iar valoarea Cantitate provin din tabelul Comenzi. Aceste tabele sunt legate unele de altele în mai multe moduri, pentru a se aduce informațiile necesare în formular. În exemplul precedent, câmpurile din tabele trebuie să fie coordonate astfel încât să afișeze informații despre aceeași comandă. Această coordonare este realizată prin utilizarea relațiilor între tabele. O relație tabel funcționează prin potrivirea datelor din câmpurile cheie — un câmp care are deseori același nume în ambele tabele. În majoritatea cazurilor, aceste câmpuri de potrivire sunt cheia primară dintr-un tabel, care furnizează un identificator unic pentru fiecare înregistrare și o cheie externă în celălalt tabel. De exemplu, angajații pot fi asociați cu comenzile pentru care sunt responsabili prin crearea unei relații tabel între câmpurile IDAngajat din tabelul Angajați și tabelul Comenzi.
1
curs programare web IDAngajat apare în ambele tabele — sub formă de cheie primară... ... și cheie secundară. Tipuri de relații între tabele Există trei tipuri de relații între tabele. •
Relația unu-la-mai-mulți
Să ne gândim la o bază de date care urmărește comenzi și are un tabel Clienți și un tabel Comenzi. Un client poate plasa oricâte comenzi. Este logic că, pentru fiecare client din tabelul Clienți, pot exista una sau mai multe comenzi în tabelul Comenzi. Relația dintre tabelul Clienți și tabelul Comenzi este, în consecință, o relație unu-la-mai-mulți. Pentru a crea o relație unu-la-mai-mulți în proiectarea bazei de date, luați cheia primară din partea "unu" a relației și adăugați-o sub formă de câmp suplimentar sau câmpuri suplimentare la tabelul din partea "mai-mulți" a relației. În acest caz, adăugați un câmp nou — câmpul ID din tabelul Clienți — la tabelul Comenzi și îl denumiți IDClient. MySql utilizează apoi numărul IDClient în tabelul Comenzi pentru a găsi clientul corect pentru fiecare comandă. •
O relație mai-mulți-la-mai-mulți
Să presupunem existența unei relații între tabelul Produse și tabelul Comenzi. O singură comandă poate include mai mult de un produs. Pe de cealaltă parte, un singur produs poate apărea în mai multe comenzi. De aceea, pentru fiecare înregistrare din tabelul Comenzi pot exista mai multe înregistrări în tabelul Produse. În plus, pentru fiecare înregistrare din tabelul Produse, pot exista mai multe înregistrări în tabelul Comenzi. Acest tip de relație este denumit o relație mai-mulțila-mai-mulți deoarece, pentru orice produs pot exista mai multe comenzi și pentru fiecare comandă pot exista mai multe produse. Rețineți că, pentru a detecta relații mai-mulți-la-mai-mulți între tabelele dvs., este important să luați în considerare ambele părți ale relației. Pentru a crea o relație mai-mulți-la-mai-mulți, trebuie să creați mai întâi un al treilea tabel, denumit deseori tabel de relație, care împarte relația mai-mulți-la-mai-mulți în două relații unu-la-mai-mulți. Inserați câmpul cheie primară din fiecare dintre cele două tabele în al treilea tabel. Ca rezultat, al treilea tabel înregistrează fiecare apariție, denumită și instanță, dintr-o relație. De exemplu, tabelul Comenzi și tabelul Produse au o relație mai-mulți-la-mai-mulți care este definită prin crearea a două relații unu-la-mai-mulți în tabelul Detalii_comenzi. O comandă poate avea mai multe produse și fiecare produse poate apărea în mai multe comenzi. •
O relație unu-la-unu
Într-o relație unu-la-unu, fiecare înregistrare din primul tabel poate avea o singură înregistrare potrivită în al doilea tabel și fiecare înregistrare din al doilea tabel poate avea o singură înregistrare potrivită în primul tabel. Acest tip de relație nu este comun, deoarece, de cele mai multe ori, informațiile asociate în acest mod se stochează în același tabel. O relație unu-la-unu poate fi utilizată pentru a diviza un tabel cu multe câmpuri, pentru a izola o parte dintr-un tabel din motive de securitate sau pentru a stoca informații care se aplică numai pentru un subset al tabelului principal. De ce se creează relații între tabele? •
Relațiile tabel oferă informații proiectărilor de interogare
Pentru a lucra cu înregistrări din mai mult de un tabel, este necesar deseori să creați o interogare care asociază tabelele. Interogarea funcționează prin potrivirea valorilor din câmpul primar al primului tabel cu un câmp cheie externă din alt doilea tabel. De exemplu, pentru a returna câmpuri care listează toate comenzile pentru fiecare client, generați o interogare care asociază tabelul Clienți cu tabelul Comenzi pe baza câmpului IDClient. În fereastra relații, aveți posibilitatea să specificați manual câmpurile de asociere.
2
curs programare web
•
Relațiile între tabele sunt fundația pe care se poate impune integritatea referențială pentru a împiedica apariția înregistrărilor solitare în baza dvs. de date. O înregistrare solitară este o înregistrare cu referință către altă înregistrare care nu există — de exemplu, o înregistrare de comandă care se referă la o înregistrare de client care nu există.
Când proiectați o bază de date, divizați informațiile în tabele, fiecare având o cheie primară. Apoi, adăugați chei externe la tabelele asociate care au referințe la acele chei primare. Aceste perechi de chei de forma cheie externă - cheie primară formează baza pentru relațiile între tabele și interogările multi-tabel. De aceea, este important ca aceste referințe cheie externă - cheie primară să fie sincronizate. Integritatea referențială asigură că referințele sunt sincronizate și dependente de relațiile tabel. Despre integritatea referențială Când proiectați o bază de date, împărțiți informațiile în mai multe tabele în funcție de subiect, pentru a minimiza redundanța datelor. Apoi, furnizați mijloacele de a aduce datele la un loc plasând câmpuri comune în tabele asociate. De exemplu, pentru a crea o relație unu-la-mai-mulți, luați cheia primară din tabelul "unu" și o adăugați sub formă de câmp suplimentar la tabelul "mai-mulți". Pentru a corela datele, Access ia valoarea din tabelul "mai-mulți" și caută valoarea corespondentă în tabelul "unu". În acest mod, valorile din tabelului "mai-mulți" fac referire la valorile corespondente din tabelul "unu". Să presupunem că aveți o relație unu-la-mai-mulți între Expeditori și Comenzi și doriți să ștergeți un expeditor. Dacă expeditorul pe care doriți să-l ștergeți are comenzi în tabelul Comenzi, acele comenzi vor deveni "solitare" atunci când ștergeți înregistrarea expeditorului. Înregistrările vor conține în continuare un ID de expeditor, dar ID-ul nu va mai fi valid, deoarece înregistrarea la care face referire nu mai există. Scopul integrității referențiale este de a împiedica apariția înregistrărilor solitare și de a ține sincronizate referințele, astfel încât această situație ipotetică să nu apară niciodată. Integritatea referențială se impune activând-o pentru o relație între tabele. Odată impusă, se respinge orice operațiuni care încalcă integritatea referențială pentru acea relație tabel. Acest lucru înseamnă că se vor respinge atât actualizările care modifică ținta referinței, cât și ștergerile care elimină ținta unei referințe. Cu toate acestea, este posibil să aveți nevoie în mod justificat să modificați cheia primară pentru un expeditor care are comenzi în tabelul Comenzi. Pentru astfel de cazuri, lucrul de care aveți cu adevărat nevoie este ca să actualizaţi automat într-o singură operațiune toate rândurile afectate. Astfel, vă asiguraţi că actualizarea este completă, astfel încât baza de date nu este lăsată într-o stare inconsistentă, cu unele rânduri actualizate și altele nu De asemenea, este posibil să aveți nevoie să ștergeți un rând sau și înregistrările asociate — de exemplu, o înregistrare din Expeditor și toate comenzile asociate pentru expeditorul respectiv. Vizualizarea relațiilor între tabele Pentru a vizualiza relațiile tabel, daţi comanda SELECT şi uniţi prin coduri comune tabelele, vedeţi rezultatul ca un tabel-relaţie şi apoi puteţi da Print View pentru a printa relaţia rezultată sau doar vizualiza.
3
curs programare web
Note • •
Pentru a crea o relație unu-la-unu - Ambele câmpuri comune (de obicei cheia primară și cheia externă) trebuie să aibă un index unic. Acest lucru înseamnă că proprietatea Primary Key(cheie primară) pentru aceste câmpuri trebuie să fie setată la Da (fără dubluri). Dacă ambele câmpuri au un index unic, se creează o relație unu-la-unu. Pentru a crea o relație unu-la-mai-mulți Câmpul de pe partea "unu" a relației (de obicei cheia primară) trebuie să aibă index unic. Acest lucru înseamnă că proprietatea Indexat pentru acest câmp trebuie setată la Da (fără dubluri). Câmpul din partea "mai mulți" nu trebuie să aibă index unic. Poate avea index, dar trebuie să permită dubluri. Acest lucru înseamnă că proprietatea Indexat pentru acest câmp trebuie să fie setată la Nu sau la Da (se permit dubluri). Când un câmp are index unic și celălalt nu are se creează o relație unu-la-mai-mulți.
Setarea tipului de asociere Următorul tabel (utilizând tabelele Clienți și Comenzi) afișează cele trei opțiuni , tipul de asociere folosit și dacă se returnează toate rândurile sau numai cele care se potrivesc pentru fiecare tabel. Alegere
Asociere relațională Tabel stânga
1. Se includ numai rândurile unde câmpurile asociate din ambele tabele sunt egale. 2. Se includ TOATE înregistrările din "Clienți" și numai acele înregistrări din "Comenzi" unde câmpurile asociate sunt egale. 3. Se includ TOATE înregistrările din "Comenzi" și numai acele înregistrări din "Clienți" unde câmpurile asociate sunt egale. •
•
Rânduri potrivite Asociere externă la Toate stânga rândurile Asociere externă la Rânduri dreapta potrivite Asociere internă
Tabel dreapta Rânduri potrivite Rânduri potrivite Toate rândurile
Nu se poate șterge o înregistrare dintr-un tabel primar dacă există înregistrări care se potrivesc în tabelul asociat. De exemplu, nu se poate șterge înregistrarea unui angajat din tabelul Angajați dacă există comenzi atribuite acestuia în tabelul Comenzi. Cu toate acestea, se poate selecta ștergerea unei înregistrări primare și a tuturor înregistrărilor asociate într-o singură operațiune, prin bifarea casetei de selectare Ștergere în cascadă câmpuri corelate. Nu se poate modifica o valoare de cheie primară în tabelul primar dacă acest lucru creează înregistrări solitare. De exemplu, nu se poate modifica numărul unei comenzi în tabelul Comenzi dacă există elemente de linie atribuite acelei comenzi în tabelul Detalii comenzi. Cu toate acestea, se poate actualiza înregistrarea primară și toate înregistrările asociate într-o operațiune, prin bifarea casetei de selectare Actualizare în cascadă câmpuri corelate.
Note Dacă întâmpinați dificultăți în activarea integrității referențiale, rețineți că sunt necesare următoarele condiții pentru a impune integritatea referențială: • • •
Câmpul comun din tabelul primar trebuie să fie cheie primară sau să aibă un index unic. Câmpurile comune trebuie să aibă același tip de date. Singura excepție este în cazul unui câmp AutoNumerotare, care poate fi asociat unui câmp Număr care are proprietatea Dimensiune câmp setată la Întreg lung. Ambele tabele există în aceeași bază de date. Integritatea referențială nu poate fi impusă pentru tabelele legate. Cu toate acestea, dacă tabelele sursă se află în format MySql, aveți posibilitatea să deschideți baza de date în care sunt stocate și să activați integritatea referențială în acea bază de date.
4
curs programare web
Pentru a înţelege mai bine trebuie creată o bază de date în MySql care să conţină următoarele tabele: • • • •
Produse(codp, denumire, pret) Clienti(IDClient, nume, prenume, telefon, email) Angajati(IDAngajat, nume, prenume, departament, telefon, email) Comenzi(IDComanda, IDClient, IDAngajat, codp, data, nrbucati)
Având aceste tabele putem rezolva problemele propuse: 1. 2. 3. 4. 5.
Câte comenzi avem pe o dată anume Cine a comandat pâine Ce încasări totale are magazinul pe data de 22 iulie 2010 Să se afle ce client a dat comenzi totalizând cea mai mare valoare (a cotizat cel mai mult la magazin) Să se afişeze lista angajaţilor de la care putem comanda creioane colorate 6. La ce departament putem comanda paste făinoase 7. Ce vânzare a realizat fiecare angajat în parte pe luna iunie 8. Ce vânzări medii a înregistrat fiecare departament 9. Ce produse conţine fiecare departament 10. Să se găsească numele, telefonul şi e-mailul clienţilor care au dat mai mult de 3 comenzi în luna iunie
Rezolvările le veţi face în laborator şi dacă nu aveţi timp pentru tot restul vă rămâne temă.
5