Tutoriale HTML - Maparea relatiilor in 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:

Tipul de date

Descriere

Dimensiune Maximă

VARCHAR2

Şir de caractere de lungime variabilă

4000 bytes

CHAR

Şir de caractere de lungime fixă

2000 bytes

NUMBER(p,s)

Număr având p cifre din care s la partea zecimală. (s negativ reprezintă numărul de cifre semnificative din faţa punctului zecimal)

p (precizia) între 1 şi 38.

s (scala) între -84 şi 127.

DATE

Dată calendaristică

De la

1 Ianuarie 4712 BC pana la

31 Decembrie, 9999 AD.

TIMESTAMP

Se memorează data calendaristică, ora, minutul, secunda şi fracţiunea de secundă

Fracţiunea de secundă este memorată cu o precizie de la 0 la 9.

INTERVAL YEAR

TO MONTH

perioadă de timp în ani şi luni.

INTERVAL DAY

TO SECOND

memorează un interval de timp în zile, ore, minute şi secunde

CLOB

Character Large Object

4 Gigabytes

BLOB

Binary Large Object

4 Gigabytes

BFILE

Se memorează adresa unui fişier binar de pe disc

4 Gigabytes

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.

Numele coloanei

Tip

Tip cheie

Opţionalitatea

Marca

Number

Pk

*

Nume

Varchar2

*

Prenume

Varchar2

*

Data_angajarii

Date

*

Adresa

Varchar2

*

Telefon

Varchar2

o

Email

Varchar2

o

Marca_sef

Number

Fk

o

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

Numele coloanei

Tip

Tip cheie

Opţionalitatea

denumire

Varchar2

Pk

*

Tabelul I.3.5. Tabela ATRIBUTE

Numele coloanei

Tip

Tip cheie

Opţionalitatea

denumire_atribut

Varchar2

Pk

*

denumire_entitate

Varchar2

Pk, Fk

*

optionalitate

Varchar2

*

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

Numele coloanei

Tip

Tip cheie

Opţionalitate

Id_angajat

Number

Pk

*

Nume

Varchar2

*

Adresa

Varchar2

*

Data_nasterii

Date

*

Id_departament

Number

Fk

*

Zona_vanzari

Varchar2

*

Permis_conducere

Varchar2

*

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

Numele coloanei

Tip

Tip cheie

Opţionalitatea

Id_angajat

Number

Pk

*

Nume

Varchar2

*

Adresa

Varchar2

*

Id_departament

Number

Fk

*

Data_nasterii

Date

*

Bonus

Number

o

Id_depart_condus

Number

Fk

o

Zona_vanzari

Varchar2

o

Permis_conducere

Varchar2

o

Tip_angajat

Numeric

*

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.