Relaţii redundante si multiple

Atunci când o relaţie poate fi dedusă din alte relaţii spunem că acea relaţie este redundantă. Relatia se poate elimina.pot exista si relaţii multiple între entităţi

Modelarea datelor istorice

Viaţa înseamnă schimbare, orice lucru se schimbă de-a lungul timpului, şi nu doar obiectele se modifică în timp dar chiar şi relaţiile dintre aceste obiecte se schimbă. Preţul produselor poate suferi modificări destul de des. Factorii care duc la aceste modificări pot fi dintre cei mai diverşi, rata inflaţia, anotimpul etc. Aşadar atributul preţ din cadrul entităţii produs se modifică de-a lungul timpului. Dacă nu ne interesează decât preţul actual al fiecărui produs modelul este foarte simplu, ca cel din fig.Dacă însă pentru afacerea modelată este important să reţinem un istoric al preţurilor pentru fiecare produs, atunci atributul preţ se va transforma într-o nouă entitate

Atributul data_sfarsit este opţional, deoarece data până la care este valabil preţul curent al unui produs nu este de obicei cunoscut.

Vom considera acum o situaţie puţin mai dificilă. Să presupunem că dorim să modelăm o bază de date pentru o bibliotecă. Evident este important de reţinut un istoric al tuturor împrumuturilor, deoarece pe baza acestora, se pot afla domeniile de interes ale cititorilor, şi astfel vom şti ce achiziţii de carte să facem în viitor, vom putea determina uzura cărţilor astfel încât să le putem înlocui etc.

Într-o primă fază vom obţine o relaţie de many-to-many între entităţile CARTE şi CITITOR. Fiecare carte poate fi împrumutată de mai mulţi cititori (evident nu în acelaşi timp), şi fiecare cititor poate împrumuta mai multe cărţi .

Să rezolvăm această relaţie many-to-many. Aplicând ceea ce am învăţat în capitolele anterioare vom obţine schema din fig. a 2 a

Să verificăm că acest caz este cel corect. Cheia primară este acum combinaţia coloanelor cod_carte şi data_imprumut. Poate un cititor împrumuta două cărţi în aceeaşi dată? Adică următoarele două înregistrări pot exista simultan în tabela ISTORIC_IMPRUMUTURI? Răspunsul este DA, combinaţia celor două coloane, pentru cele două înregistrări fiind unică.

Deci bararea automată a celor două relaţii dinspre entitatea de intersecţie nu este întotdeauna o soluţie corectă. Pentru a evita aceste complicaţii putem recurge la introducerea unei chei artificiale în entitatea de intersecţie. În exemplul nostru se poate decide ca pentru fiecare împrumut în parte să se completeze câte o fişa separată care are un număr unic. Obţinem modelul din figura I.4.13, care este de asemenea unul corect.

Fig. Introducerea unei chei artificiale

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.

Figura I.3.3.

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.

Figura I.3.4.

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

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.

Figura I.3.6. Maparea relaţiilor barate

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ă.

Figura I.3.7. Relaţii barate în cascadă

Tabelul I.3.6. Tabela A Tabelul I.3.7. Tabela B

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

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

Tabelul I.3.8. Tabela C Tabelul I.3.9. Tabela D

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

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).

Figura I.3.8. Exemplu de încălcare a integrităţii referenţiale

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 Tabelul I.4.2. Tabela SECRETARE

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

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

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

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

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 Tabelul I.4.6. Tabela MANAGERI

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

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	Permis_conducere	Tip_angajat	…
10	125	5	(null)	(null)	1
121	(null)	(null)	Transilvania	568147	2
245	(null)	(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 Tabelul I.4.11. Tabela PERSOANE_FIZICE

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

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

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

Tabelul I.4.12. Tabela FIRME 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.

Caracteristica relaţiei	Valoare	Mod de reprezentare
Numele relaţiei	un verb	se scrie deasupra relaţiei
Opţionalitatea	relaţie obligatorie (TREBUIE)	linie continuă
Opţionalitatea	relaţie opţională (POATE)	linie întreruptă
Cardinalitatea	una şi numai una	linie simplă
Cardinalitatea	una sau mai multe	picior de cioară

1. Date, informaţii, cunoştinţe

2. Colectarea şi analizarea datelor. Modelul conceptual

3. Entităţi. Instanţe. Atribute. Identificator unic.

4. Relaţii între entităţi

4.Convenţii de reprezentare a relaţiilor

Tipuri şi subtipuri

Documentare Business Rules

Tipuri de relaţii

Rezolvarea relaţiilor many-to-many

Ce este normalizarea?

Prima formă normală

A doua formă normală

A treia formă normală

3. Relaţii exclusive (arce)

Relaţii ierarhice. Relaţii recursive