Tutoriale ORACLE - Maparea relațiilor în Oracle

Conventii de ridabilitate: Se aplica conventiile Oracle de scriere a ERD-ului:

Entitatea se scrie cu majuscule, singular in interiorul unui dreptunghi cu vf rotunjite.Atributele se scriu cu litere mici , avand in fata unul din semnele #,*,o(UID, obligatoriu, optional).Orientarea liniilor este de la V la E si de sus in jos, evitand intersectia. Se pot folosi subdiagrame de explicare a diagramelor complexe, si explicarea entitatilor cu multe atribute.

Modelarea generica

Modelul generic aduce beneficii daca cerintele afacerii se schimba des. Atunci e nevoie de entitati si atribute noi.Se poate modela o singura entitate Article type care sa pastreze oricate tipuri de articole e nevoie, aceasta reduce nr de entitati.

Procesul maparii

Transformarea modelului conceptual, a ERD-ului, în modelul fizic, adică în baza de date propriu zisă, se numește mapare. Acest proces implică transformarea fiecărui element al ERD-ului.

Numele coloanei	Tip	Tip cheie	Opționalitatea
titlu	Varchar2	Pk	*
autor	Varchar2	Pk	*
data_apariției	Date		*
Format	Varchar2		*
Nr_pagini	Number		*

Entitati » tabele, (CARTE-carti.dbf)

atribute» campuri,

coloane, UID»cheie primara,

relatie»cheie straina,

business rules »constrangeri

Se mapeaza procesul de transformare in diagrama tabelei:

Tipuri de date in Oracle:

dacă relația pe partea many este opțională atunci și coloanele cheii străine vor fi opționale. Ce înseamnă acest lucru? Faptul că un jucător poate la un moment dat să nu joace la nici o echipă, atunci câmpul cod_echipă va rămâne necompletat în dreptul lui (va avea valoarea NULL). Dacă însă relația este obligatorie pe partea many atunci coloanele ce fac parte din cheia străină vor fi opționale.

În gereral, la maparea unei relații de tip one-to-many, vom introduce în tabela corespunzătoare entității de pe partea many a relației cheia primară a entității de pe partea one a relației. Câmpurile astfel întroduse se vor numi cheie străină (foreign key).

Așadar:

- cheia străină a unei tabele este cheia primară din tabela referintă

- cheia străină este întotdeauna introdusă în tabela corespunzătoare entității din partea many a relației.

Maparea relațiilor one-to-one

Dându-se două entități A și B legate între ele printr-o relație one-to-one, este evident că putem include cheia primară A în cadrulul tabelei B, dar putem proceda la fel de bine și invers, incluzând cheia primară a tabelei B în cadrul tabelei A, deoarece fiecărei instanțe a entității A îi corespunde cel mult o instanță a entității B, dar și invers, oricărei instanțe a entității B îi corespunde cel mult o instanță a entității A.

Pentru relația din figura I.3.3 de exemplu putem memora pentru fiecare persoană seria de pașaport, dar și invers, pentru fiecare pașaport putem memora cnp-ul deținătorului.

Decizia depinde de specificul afacerii modelate. Dacă de exemplu ne interesează în primul rând persoanele și abia apoi datele de pe pașapoarte, atunci vom adopta probabil prima variantă, a memorării seriei de pașaport în cadrul tabelei PERSOANE, dacă însă baza de date este destinată evidenței pașapoartelor, atunci probabil vom adopta varianta a doua.

Uneori este convenabil să memorăm cheia străină în ambele părți ale relației, în exemplul nostru pentru fiecare pașaport să memorăm cnp-ul persoanei care îl deține, dar și pentru fiecare persoană să memorăm seria de pașaport.

Maparea relațiilor recursive

Dacă vom privi o relație recursivă ca pe o relație de tipul one-to-many între o entitate și ea însăși, atunci acest caz se reduce la ceea ce deja am discutat. Să exemplificăm relația din figura I.3.4. Relația recursivă din această figură poate fi privită ca o relație între două entități identice, ca în figura I.3.5.

Așadar vom introduce în cadrul tabelei ANGAJAŢI, marca șefului său. Diagrama de tabela va arăta ca mai jos.

Tabelul I.3.4.

Maparea relațiilor barate

Relațiile barate sunt mapate ca cheie străină în tabela aflată în partea many a relației, la fel ca la maparea oricărei relații one-to-many. Bara de pe relație exprimă faptul că acele coloane ce fac parte din cheia străină vor devenii parte a cheii primare a tabelei din partea many a relației barate.

Pentru exemplul din figura I.3.6, cheia primară a tabelei ATRIBUTE va fi format din coloanele denumire_atribut și denumire_entitate, aceasta din urmă fiind de fapt cheie străină în tabela ATRIBUTE.

Tabelul I.3.5. Tabela ENTITĂŢI

Tabelul I.3.5. Tabela ATRIBUTE

Să considerăm acum un exemplu în care există mai multe relații barate, în cascadă.

Tabelul I.3.6. Tabela A

Numele coloanei	Tip cheie	Opționalitate
idA	Pk	*
C1		*

Tabelul I.3.7. Tabela B

Numele coloanei	Tip cheie	Opționalitate
idB	Pk	*
C2		*
idA	Pk, Fk	*

Tabelul I.3.8. Tabela C

Numele coloanei	Tip cheie	Opționalitate
idC	Pk	*
C3		*
idA	Pk, Fk	*
idB	Pk, Fk	*

Tabelul I.3.9. Tabela D

Numele coloanei	Tip cheie	Opționalitatea
idD	Pk	*
C4		*
idA	Fk	*

Operații specifice prelucrării bazelor de date

Orice sistem de gestiune a bazelor de date (SGBD) trebuie să asigure următoarele funcții:

· definirea structurii bazei de date

· încărcarea datelor în baza de date (adăugarea de noi înregistrări la baza de date)

· accesul la date pentru:

o interogare (afișarea datelor, sortarea lor, calcule statistice etc.)

o ștergere

o modificare

· întreținerea bazei de date:

o refacerea bazei de date prin existența unor copii de siguranță

o repararea în caz de incident

o colectarea și refolosirea spațiilor goale

· posibilitatea de reorganizare a bazei de date prin:

o restructurarea datelor

o modificarea accesului la date

· securitatea datelor.

O parte din aceste operații pot fi realizate cu ajutorul limbajului SQL, altele cu ajutorul unor programe specializate, care sunt puse la dispoziția administratorului bazei de date de către sistemul de gestiune al bazelor de date.

Reguli de integritate

Detalierea caracteristicilor pe care trebuie să le prezinte un SGBD pentru a fi considerat relațional s-a făcut de E. F. Codd în 1985 sub forma a 13 reguli. Una dintre aceste reguli precizează că restricțiile de integritate trebuie să poată fi definite în limbajul utilizat de SGBD pentru definirea datelor.

Regulile de integritate garantează că datele introduse în baza de date sunt corecte și valide. Aceasta înseamnă că dacă există orice o regulă sau restricție asupra unei entități, atunci datele introduse în baza de date respectă aceste restricții. În Oracle, regulile de integritate se definesc la crearea tabelelor folosind constrângerile. Dar asupra acestora vom reveni în partea a doua a manualului.

Tipurile de reguli de integritate sunt următoarele:

§ Integritatea entităților – indică faptul că nici o coloană ce face parte din cheia primară nu poate avea valoarea NULL. În plus, pentru fiecare înregristrare, cheia primară trebuie să fie unică.

§ Integritatea de domeniu – acest tip de reguli permite ca într-o anumită coloană se introducă doar valori dintr-un anumit domeniu. De exemplu putem impune ca salariul unui angajat să fie cuprins între 4500 și 5000 RON.

§ Integritatea referențială – este o protecție care asigură ca fiecare valoare a cheii străine să corespundă unei valori a cheii primare din tabela referită. De exemplu, referindu-ne la tabelele JUCĂTORI și ECHIPE, corespunzătoare ERD-ului din figura I.3.2, cod este cheie primară în tabela ECHIPE, iar în tabela JUCĂTORI, cod devine cheie străină. Astfel valoarea câmpului cod din cadrul tabelei JUCĂTORI corespunzătoare unui anumit jucător trebuie să se regăsească printre valorile câmpului cod din tabela ECHIPE, altfel ar însemna că jucătorul respectiv joacă la o echipă inexistentă (vezi figura I.3.8).

Situații de încălcare a integrității referențiale pot apărea:

§ la adăugarea unei noi înregistrări în baza de date, se poate încerca introducerea unor valori invalide pentru câmpurile cheii străine;

§ la actualizarea bazei de date;

§ la ștergerea unei înregistrări. De exemplu se șterge înregistrarea corespunzătoare unei anumite echipe (echipa se desființează). Înregistrările jucătorilor care au jucat la acea echipă vor încălca integritatea referențială, deoarece se vor referi la o echipă care nu mai există. Soluțiile posibile sunt ca la ștergerea unei echipe, toți jucătorii care au activat la acea echipă să fie și ei șterși din baza de date (ștergere în cascadă) sau valoarea câmpului cod_echipă pentru acei jucători să fie setată la NULL, ceea ce va înseamnă că acei jucători nu activează la nici o echipă.

Programe de validare și de acțiune

În realizarea modelului conceptual al unei baze de date se ține cont de modul în care funcționează afacerea modelată, datele care trebuie să fie memorate, relațiile dintre acestea etc. Modul de utilizare a diferitelor date, modul în care acestea sunt relaționate pot diferi de la o afacere la alta.

Regulile afacerii unei organizații se referă în esență la procesele și fluxurile tuturor datelor și activităților zilnice din cadrul organizației. Cum funcționează organizația? Care sunt activitățile sale?

Regulile afacerii acoperă următoarele aspecte ale unei organizații:

§ Orice tip de politici organizaționale de orice tip și de la orice nivel al organizației.

§ Orice tip de formule de calcule (ca de exemplu modul de calcul al ratelor pentru diverse împrumuturi, modul de calcul al salariilor etc)

§ Orice tip de reguli impuse de lege sau reguli interne ale organizației.

Regulile simple ale afacerii pot fi implementate în modelul bazei de date prin intermediul relațiilor dintre entități. Acest tip de reguli se numesc reguli structurale.

Alte reguli ale afacerii pot fi implementate folosind regulile de integritate despre care am discutat în paragraful anterior. Există totuși reguli pentru implementarea cărora va trebui să scriem programe speciale folosind limbaje specializate specifice SGBD-ului utilizat. Acest tip de reguli se numesc numite reguli procedurale. În Oracle acest tip de programe se vor scrie folosind limbajul PL/SQL (Procedural Language/Structuded Query Languge) și se numesc declanșatoare (triggere).

Există două tipuri de declanșatoare:

- declanșatoare de aplicație care se execută când apar anumite evenimente la nivelul anumitor evenimente;

- declanșatoare ale bazei de date care sunt lansate în execuție când apar diverse evenimente asupra datelor (de exemplu la executarea unor comenzi ca INSERT, UPDATE, DELETE) sau la apariția unor evenimente system (logarea la baza de date sau delogarea).

Orice declanșator poate avea rol de validare a unei operații, poate realiza diferite operații suplimentare, ca de exemplu diferite calcule, caz în care vom spune că e vorba de un declanșator de acțiune.

Maparea tipurilor și subtipurilor

Nici un sistem de gestiune a bazelor de date nu suportă în mod direct supertipurile și subtipurile. Putem adopta mai multe soluții ale acestei probleme. Vom exemplifica aceste variante pentru schema din figura I.4.1, în care, pentru simplitate, vom presupune că nu avem nevoie de subentitatea ALTUL.

Varianta 1. Vom crea o tabelă pentru supertip și câte o tabelă pentru fiecare subtip. Diagramele de tabelă în acest caz vor fi:

Tabelul I.4.1. Tabela ANGAJAŢI

Numele coloanei	Tip	Tip cheie	Opționalitatea
Id_angajat	Number	Pk	*
Nume	Varchar2		*
Adresa	Varchar2		*
Data_nasterii	Date		*
Id_departament	Number	Fk	*

Tabelul I.4.2. Tabela SECRETARE

Numele coloanei	Tip	Tip cheie	Opționalitatea
Id_angajat	Number	Pk	*

Tabelul I.4.3. Tabela MANAGERI

Numele coloanei	Tip	Tip cheie	Opționalitatea
Id_angajat	Number	Pk	*
Bonus	Number		*
Id_depart_condus	Number	Fk	o

Tabelul I.4.4. Tabela REPREZENTANŢI_VÂNZĂRI

Numele coloanei	Tip	Tip cheie	Opționalitatea
Id_angajat	Number	Pk	*
Zona_vanzari	Varchar2		*
Permis_conducere	Varchar2		*

Am notat cu Id_depart_condus codul departamentului pe care îl conduce un manager, iar cu Id_departament codul departamentului în care lucrează un anumit angajat.

Cheia primară a supertipului va fi inclusă în toate tabelele corespunzătoare subtipurilor și va deveni cheia primară a acelei tabele.

Atributele și cheile străine provenite din relațiile de la nivelul supertipului vor fi memorate în tabela corespunzătoare supertipului. Atributele și relațiile de la nivel de subtip, se vor memora doar în tabela corespunzătoare subtipului respectiv.

Acest model este cel mai natural dar poate crea multe probleme privind eficiența întrucât sunt necesare multe operații de interogare din tabele multiple, pentru a obține informații suplimentare despre toți angajații.

Varianta 2. Vom crea câte o tabelă pentru fiecare subtip. Atributele și cheile străine provenite din relațiile de la nivelul supertipului vor fi introduse în fiecare tabelă astfel obținută, acestea fiind moștenite de către fiecare subtip.

Tabelul I.4.5. Tabela SECRETARE

Numele coloanei	Tip	Tip cheie	Opționalitate
Id_angajat	Number	Pk	*
Nume	Varchar2		*
Adresa	Varchar2		*
Id_departament	Number	Fk	*
Data_nasterii	Date		*

Tabelul I.4.6. Tabela MANAGERI

Numele coloanei	Tip	Tip cheie	Opționalitate
Id_angajat	Number	Pk	*
Nume	Varchar2		*
Adresa	Varchar2		*
Data_nasterii	Date		*
Bonus	Number		*
Id_depart_condus	Number	Fk	o
Id_departament	Number	Fk	*

Tabelul I.4.7. Tabela REPREZENTANŢI_VÂNZĂRI

Varianta 3. Vom crea o singură tabelă pentru supertip. Această tabelă va conține toate coloanele corespunzătoare atributelor de la nivelul supertipului, dar și toate coloanele corespunzătoare tuturor atributelor din toate subtipurile. Atributele de la nivelul supertipului își vor păstra opționalitatea, însă atributele de la nivelul subtipurilor, vor fi toate introduse în tabelă, dar vor fi toate opționale.

Relațiile de la nivelul supertipului se transformă normal. Relațiile de la nivelul subtipurilor se vor implementa cu ajutorul cheilor străine opționale.

Tabelul I.4.8. Tabela ANGAJAŢI

Am introdus un atribut suplimentar Tip_angajat, cu ajutorul căruia vom codifica dacă un angajat este manager, secretară sau reprezentant de vânzări. Deoarece atributele de la nivelul subtipurilor sunt obligatorii pentru subtipul respectiv, va trebui să stabilim o regulă de integritate la nivel de înregistrare, care să verifice că pentru o înregistrare de un tip anume sunt completate câmpurile corespunzătoare. De exemplu, la adăugarea unui nou manager în tabela ANGAJAŢI, trebuie să verificăm dacă este completat câmpul bonus.

Se observă că vor fi multe câmpuri cu valoarea null, ceea ce înseamnă o risipă de spațiu de memorie.

Tabelul I.4.9. Tabela ANGAJAŢI

Id_angajat	Bonus	Id_departament_condus	Zona_vanzari	Tip_angajat
10	125	5	(null)	1
121	(null)	(null)	Transilvania	2
245	(null)	(null)	(null)	3
…

În acest tabel am codificat managerii cu 1, reprezentanții de vânzări cu 2, iar secretarele cu 3. Așadar această variantă de implementare este convenabilă când există puține atribute și relații la nivelul subtipurilor.

Maparea arcelor

Pentru a mapa un arc vom crea atâtea chei străine câte relații există în arcul respectiv. Pentru modelul din figura I.4.2 vom obține următoarele tabele:

Tabelul I.4.10. Tabela CONTURI

Numele coloanei	Tip	Tip cheie	Opționalitatea
IBAN	Number	Pk	*
Sold_curent	Number		*
Data_deschiderii	Date		*
Cnp	Number	Fk1	o
Autorizatie_functionare	Number	Fk2	o

Tabelul I.4.11. Tabela PERSOANE_FIZICE

Numele coloanei	Tip	Tip cheie	Opționalitatea
Cnp	Number	Pk	*
Nume	Varchar2		*
Prenume	Varchar2		*
Adresa	Varchar2		*
Telefon	Number		*

Tabelul I.4.12. Tabela FIRME

Numele coloanei	Tip	Tip cheie	Opționalitatea
Autorizatie_functionare	Number	Pk	*
Nume	Varchar2		*
Adresa	Varchar2		*
Telefon	Number		*
Fond_social	Number		*

Deși relațiile din arc sunt obligatorii, cheile străine corespunzătoare au fost setate ca fiind opționale, deoarece pentru fiecare înregistrare trebuie să avem completată una din cele două chei străine, iar cealaltă cheie străină trebuie să rămână necompletată (principiul exclusivității). Va trebui să implementăm o condiție de integritate care să verifice această condiție.