# 17 model fizic şi model conceptual entităţi si instanţe ... · model fizic şi model...
Embed Size (px)
TRANSCRIPT
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