Proiectarea bazelor de date

# 17

Adrian Runceanuwww.runceanu.ro/adrian

2018

Model fizic i model conceptualEntiti si instane (partea II)

Model fizic i model conceptual

Entiti si instane (partea II)

30.12.2018 2

Curs 17

Model fizic i model conceptualEntiti si instane (partea II)

1. Rezolvarea relaiilor Many to Many

2. Analiza CRUD

3. UID

4. Normalizare

5. Arce

6. Ierarhii

30.12.2018 3

REZOLVAREA RELAIILOR MANY TO MANY

n vederea implementrii fizice a modeluluiconceptual, este important ca relaiile Many to Many s fie rezolvate.

ntr-o relaie M:M exist cel puin un atributcare descrie relaia.

Se creeaza a treia entitate, numit entitate de intersecie, gasind astfel un loc pentru acelatribut.

30.12.2018 4

Pai

1. Se creeaz entitatea de legatur2. Se creeaz noile relaii3. Se dau nume acestor relaii4. Daca e cazul, se adaug atribute n entitatea

de legtur5. Se creaz un UID pentru entitatea de

intersecie

30.12.2018 5

30.12.2018 6

30.12.2018 7

30.12.2018 8

30.12.2018 9

Exercitiu

Desenati ERD-ul

30.12.2018 10

rezolvare

30.12.2018 11

30.12.2018 12

SOLUTIE

30.12.2018 13

Model fizic i model conceptualEntiti si instane (partea II)

1. Rezolvarea relaiilor Many to Many

2. Analiza CRUD

3. UID

4. Normalizare

5. Arce

6. Ierarhii

30.12.2018 14

ANALIZA C.R.U.D.(Create, Retrieve, Update, Delete)

Prin analiza CRUD identificm cerinele decreare, regsire, actualizare i tergere a datelor.

Analiza CRUD ne ajut s observm dac:am omis ceva din modelam inclus n model ceva ce nu trebuie

30.12.2018 15

Cum se realizeaz analiza CRUD ?

Urmrim n interviurile cu clientul sau n descrierile activitilor, cuvintele care exprim cele 4 operaii: 1. creare2. regsire3. actualizare4. tergere

Dac exist cerine pentru care nu exist date, s-ar putea ca modelul s fie incomplet.

Dac exist date asupra crora nu se efectueaz nici una dintre operaiile CRUD, atunci acestea nu sunt necesare pentru afacere.

30.12.2018 16

Model fizic i model conceptualEntiti si instane (partea II)

1. Rezolvarea relaiilor Many to Many

2. Analiza CRUD

3. UID

4. Normalizare

5. Arce

6. Ierarhii

30.12.2018 17

IDENTIFICATOR UNIC

Identificatorul unic (UID) poate fi format din:1. o valoare, sau2. o combinaie de valorii permite utilizatorului s identifice n mod unic o anumit

instan. Atunci cnd un UID este alctuit dintr-un singur atribut,

se numete simplu. Dac un UID este alctuit dintr-o combinaie de

atribute, se numete compus. Un UID creat special pentru a numerota unic fiecare

instan se numete artificial.

30.12.2018 18

Exemple de UID

30.12.2018 19

30.12.2018 20

UID rezultat dintr-o relaie barat

Uneori UID este o combinaie dintre un atribut i o relaie.

Care este UID-ul entitii ACCOUNT?

n transferurile bancare se transmit ntotdeauna att contul clientului ct i cel al bncii.

Atunci cnd un UID este o combinaie ntre un atribut i o relaie barat putem considera c UID-ul este alctuit dintr-un atribut i UID-ul celeilalte entiti din relaie.

30.12.2018 21

n cazul unei entiti de intersecie UID poate fi o combinaie a UID ale entitilor care se aflau iniial n relaia M:M.

Aceasta se reprezint n diagram prin bararea relaiilor.

30.12.2018 22

Pot exista mai muli UID. unul este ales ca UID primar

(ex. student ID); ceilali UID sunt numii

secundari.

n exemplul alturat prima entitate are un UID secundar,

a doua entitate are doi: badge number, identificator unic secundar simplu si first name, last name, identificator unic secundar compus.

30.12.2018 23

Model fizic i model conceptualEntiti si instane (partea II)

1. Rezolvarea relaiilor Many to Many

2. Analiza CRUD

3. UID

4. Normalizare

5. Arce

6. Ierarhii

30.12.2018 24

NORMALIZARE

Normalizarea se refer la procesul de creare a unei structuri relaionale eficiente, flexibile care aeaz fiecare dat ntr-un singur loc, care este i cel mai potrivit loc, astfel nct operaiile de adugare, modificare, tergere s se fac ntr-un singur tabel.

30.12.2018 25

Un astfel de model va respecta:

FLEXIBILITATEA DATELORDatele vor fi pastrate n locul cel mai bun i vor putea fi vizualizate n diverse feluri.

INTEGRITATEA DATELORNormalizarea asigur integritatea datelor n operaii de tergere, adugare, actualizare.

EFICIENANu vor exista date redundante. Se va face economie de spaiu.

30.12.2018 26

PRIMA FORMA DE NORMALIZARE

30.12.2018 27

UN ATRIBUT NU POATE AVEA VALORI CARE SE REPET

30.12.2018 28

Exerciiu

Analizai entitile de mai jos i decidei dac respect prima form de normalizare.

30.12.2018 29

Exercitiu

Examinati urmatoarele entitati.

Identificati atributele cu valori

multiple.

30.12.2018 30

FORMA A II-a DE NORMALIZARE

Se aplic n mod special entitilor care au un UID compus din mai multe atribute sau dintr-un atribut i o relaie.

30.12.2018 31

ORICE ATRIBUT CE NU E UID S DEPIND DE NTREGUL UID

FORMA A II-a DE NORMALIZARE

Acest exemplu respecta forma 2 de normalizare.

n acest ERD, bank_location depinde numai de banc, nu i de contul persoanei i astfel ncalc Forma de Normalizare 2

30.12.2018 32

Exemplu

Durata depinde numai de SONG.

Event_date depinde numai de eveniment.

30.12.2018 33

FORMA A II-a DE NORMALIZARE

EXERCITIU

1. The identifier of a library book includes its shelf location.

Does this ERD follow the rules of Second Normal Form?

If you spot a violation, correct it.

30.12.2018 34

FORMA A II-a DE NORMALIZARE

SOLUTIE:

30.12.2018 35

FORMA A II-a DE NORMALIZARE

EXERCITIU

2. A store can be located in several shopping malls, and a shopping mall may house several stores.

To locate a particular store in a specific neighborhood, you will need to know the name and address of the nearby shopping mall, plus the name of the store.

Does the ERD follow the rules of Second Normal Form? If you spot a violation, correct it.

30.12.2018 36

FORMA A II-a DE NORMALIZARE

30.12.2018 37

FORMA A II-a DE NORMALIZARE

SOLUTIE:

30.12.2018 38

FORMA A III-a DE NORMALIZARE

30.12.2018 39

UN ATRIBUT CE NU E UID NU TREBUIE S DEPIND DE ALT ATRIBUT NON-UID.

Exemplu

n acest exemplu, store adress depinde de store name.

Soluia acestei probleme este s crem o nou entitate, STORE, care va conine cele dou atribute.

30.12.2018 40

state flower depinde de state i nu de ora.

Soluia este s crem o nou entitate STATE

30.12.2018 41

Exemplu

FORMA A III-a DE NORMALIZARE

EXERCITIU

1. Identify the transitive dependency in the model below.

State which attributes violate Third Normal Form

30.12.2018 42

FORMA A III-a DE NORMALIZARE

EXERCITIU

2. A color scheme for a car includes specifications for paint color for the body and the interior colors and materials.

For example: The Desert color scheme includes silver paint and gray leather interior; the Sunburst color scheme includes gold paint and cream leather interior.

Does the model below follow the rules of Third Normal Form? If you spot a violation, correct it.

30.12.2018 43

FORMA A III-a DE NORMALIZARE

SOLUTIE

30.12.2018 44

Model fizic i model conceptualEntiti si instane (partea II)

1. Rezolvarea relaiilor Many to Many

2. Analiza CRUD

3. UID

4. Normalizare

5. Arce

6. Ierarhii

30.12.2018 45

ARCE

Orice afacere are restricii ce pot fi aplicate valorilor unor atribute sau asupra relaiilor dintre entiti.

Acestea se numesc CONSTRNGERI.

ARCele sunt un mod de a reprezenta relaiile mutual exclusive: pentru fiecare instan numai una dintre relaii este valid.

30.12.2018 46

Exemplu

Fiecare eveniment se poate desfura fie ntr-un mediu privat fie ntr-un mediu public.

30.12.2018 47

Explicati ERD-ul

30.12.2018 48

Rezolvare

Pe un panou publicitar pot aparea la un moment dat ori un film, ori o reclama, ori un anun public.

30.12.2018 49

Arcele pot fi modelate i cu Subtipuri i Supertipuri.

Cnd se dorete s se reprezinte o clasificare se folosesc subtipurile.

Folsim arce atunci cnd dorim

s reprezentm relaiile mutual

exclusive dintre entiti.

30.12.2018 50

ARCE

ARCE

EXERCITIU

1. A show ticket is purchased from an agent, the box office, or the Internet.

A ticket has a description, an event, a date and a price. An agent has a name and a phone number. The box office has an address and a phone number. The Internet has a URL address. Draw the entities and represent the exclusive relationship.

30.12.2018 51

ARCE

SOLUTIE

30.12.2018 52

Model fizic i model conceptualEntiti si instane (partea II)

1. Rezolvarea relaiilor Many to Many

2. Analiza CRUD

3. UID

4. Normalizare

5. Arce

6. Ierarhii

30.12.2018 53

IERARHII, RELATII

n viaa de zi cu zi ntlnim adesea modele organizate n ierarhii cum ar fi:

1. scheme organizaionale

2. reprezentarea unor structuri fizice

3. arbori genealogici

30.12.2018 54

MODELE IERARHICE

30.12.2018 55

Model organizaional

30.12.2018 56

Exercitiuexplicati ERD-ul aratand care este UID-ul

fiecarei entitati

30.12.2018 57

Exercitiu

Fie o baza de date despre hotelurile unei statiuni. Fiecare camera (ROOM) trebuie sa fie identificata

printr-un numar sau cod. Camera este situat n apartament (SUITE) care se afl la un anumit etaj (FLOOR) care se afl n cldire (BUILDING).

Desenati ERD-ul. UID-ul unui sir de entiti ierarhice se poate propaga

prin relaii multiple !

30.12.2018 58

Rezolvare

30.12.2018 59

RELAII RECURSIVE

O relaie recursiv este o relaie ntre o entitate i ea nsi.Exemplu:

Fiecare angajat (EMPLOYEE) poate fi condus de unul i numai unul dintre angajai (EMPLOYEE).

Fiecare angajat (EMPLOYEE) poate fi eful (managerul) unuia sau mai muli angajai (EMPLOYEE).

30.12.2018 60

30.12.2018 61

Relaie recursiv Many to Many

Pentru o fabric de automobile putem considera: prti elementare, componente i produse.

O astfel de situaie poate fi modelat astfel:

Fiecare component poate fi o parte a uneia sau mai multor componente.

Fiecare component poate fi fcut din una sau mai multe componente.

Exercitiu. Cum rezolvati M_M?30.12.2018 62

30.12.2018 63

30.12.2018 64

30.12.2018 65

Our company sells products throughout the United States. So weve divided the U.S. into four major sales regions: the Northern,

Eastern, Southern, and Western regions. Each sales region has a unique region code. Each sales region is then divided into sales districts. For example, the Western region is divided into the Rocky Mountain,

Northwest, Pacific Coast, and Pacific districts. Each district has a unique district code. Each district is made up of sales territories. The Rocky Mountain district is composed of three territories: Wyoming-

Montana, Colorado, and Utah-New Mexico. The Northwest district is made up of two territories: the Washington and

Oregon-Idaho territories. The Pacific Coast district is composed of two territories: the California

and Nevada territories.

Exercitiu:

Develop two ER diagrams to represent the following situation. Develop one using a hierarchical structure and one using a recursive structure

30.12.2018 66

The Pacific District includes the Hawaii territory and the Alaska territory. Each territory has a unique territory code. Then each sales territory is broken down into sales areas. For example, Colorado is made up of two sales areas: the Front Range and the

Western Slope sales areas. Each sales area has a unique sales-area code. Each salesperson is responsible for one or more sales areas and has a specific sales

quota. We also have sales managers who are responsible for one or more sales districts,

and sales directors who are responsible for one or more sales regions. Each sales manager is responsible for the territories with his/her districts. We dont overlap our employees responsibilities. Each sales area is always the responsibility of a single salesperson, and our

managers' and directors' responsibilities dont overlap. Sometimes our salespersons, managers, and directors will have special assignments

and will not be responsible for sales. We identify all our sales personnel by their employee IDs.

Exercitiu:

Develop two ER diagrams to represent the following situation. Develop one using a hierarchical structure and one using a recursive structure

SOLUTIE

30.12.2018 67

SOLUTIE

30.12.2018 68

Subtypes Hide Relationships in Arc

Every A is either a B or a C

Every B is an A

Every C is an A30.12.2018 69

A

C

B

C

BA

is

is

is

is

Every A mustbe a B or be a C

Every B must be an A

Every C must be an A

Arc and Subtypes

30.12.2018 70

A

QP

2

A

QP

R

1

A

P

CB

Q

3

A CB

QP

R

4 5

A CB

QP

R

Materialul utilizat in acest curs face parte din curriculum Oracle Academy

30.12.2018 71

ntrebri?

30.12.2018 72