ﺑﺴﻢ ﺍﻟﻠﻪ ﺍﻟﺮﺣﻤﻦ ﺍﻟﺮﺣﻴﻢ Lecture 6 Data

ﺑﺴﻢ ﺍﻟﻠﻪ ﺍﻟﺮﺣﻤﻦ ﺍﻟﺮﺣﻴﻢ Lecture( 6) Data Modeling Using the Entity-Relationship (ER) Model 1

(Relationship type Degree) ﺩﺭﺟﺔ ﻧﻮﻉ ﺍﻟﻌﻼﻗﺔ . ﺍﻟﻤﺸﺎﺭﻛﺔ ﻓﻲ ﻧﻮﻉ ﺍﻟﻌﻼﻗﺔ Entity types ﻫﻲ ﻋﺪﺩ ﺍﻟـ (Number of participant Entity type into relationship type) 2 = works – for ﻣﺜﺎﻝ : ﺩﺭﺟﺔ ﻧﻮﻉ ﺍﻟﻌﻼﻗﺔ 2

ﻧﻮﻉ ﺍﻟﻌﻼﻗﺔ ﻣﻦ ﺍﻟﺪﺭﺟﺔ ﺍﻻﻭﻟﻲ : )ﺗﺴﻤﻲ ﺑـ ) (Recursive relationship type ﻭﺍﻟﻌﻼﻗﺔ ﻓﻲ ﻫﺬﺍ ﺍﻟﻨﻮﻉ ﺗﻜﻮﻥ ﺑﻴﻦ ﻛﺎﺋﻨﺎﺕ ﻧﻔﺲ ﺍﻟـ Entity type ﺍﻟﻤﺸﺎﺭﻙ ﻓﻲ ﻧﻮﻉ ﺍﻟﻌﻼﻗﺔ. ﻣﺜﺎﻝ ﻟﻌﻼﻗﺔ ﻣﻦ ﺍﻟﺪﺭﺟﺔ ﺍﻻﻭﻟﻲ: - R. T: Supervision ﻛﻞ ﻣﻮﻇﻒ ﻟﻪ ﻣﻮﻇﻒ آﺨﺮ ﻳﺸﺮﻑ ﻋﻠﻴﺔ ﺍﻭ )ﻳﺮﺃﺴﻪ( ﻭﻟﺬﺍ ﻧﺠﺪ ﺃﻦ ﺍﻝ Employee E. T ﻫﻲ ﺍﻝ E. T ﺍﻟﻮﺣﻴﺪﺓ ﺍﻟﻤﺸﺎﺭﻛﺔ ﻓﻲ ﺍﻟﻌﻼﻗﺔ Supper vision 3

e 1 supervisor of e 2 supervised by e 1 (all participating entity In R. T belong to only one E. T) e 1 r 2 e 3 4

(Ternary )-: ﻧﻮﻉ ﺍﻟﻌﻼﻗﺔ ﻣﻦ ﺍﻟﺪﺭﺟﺓﺎﻟﺜﺎﻟﺜﺔ Number of participate Entity – types In to R. T = 3 5

ﻣﺜﺎﻝ father f 1 parent r 1 child c 1 mother m 1 6

ﺍﻧﻮﺍﻉ ﺍﻟﻌﻼﻗﺎﺕ ﻣﻦ ﺍﻟﺪﺭﺟﺔ ﺍﻟﺜﺎﻧﻴﺔ) : (Binary Relationship types ﻣﻦ ﺍﺷﻬﺮ ﺍﻧﻮﻉ ﺍﻟﻌﻼﻗﺎﺕ )ﻟﺬﺍ ﺗﻄﺒﻴﻖ ﻣﻌﻈﻢ ﻗﻴﻮﺩ ﺍﻟﻌﻼﻗﺎﺕ ﻳﺘﻢ ﻓﻲ ﺍﻟﻌﻼﻗﺎﺕ ﺍﻟﺜﻨﺎﺋﻴﺔ( 7

ﻗﻴﻮﺩ ﺍﻧﻮﺍﻉ ﺍﻟﻌﻼﻗﺎﺕ ) ( Relationship types Constraints ﻣﻦ ﺍﻟﻌﺎﻟﻢ ﺍﻟﺤﻘﻴﻘﻲ ﻧﺠﺪ ﺍﻥ ﻣﻌﻈﻢ ﺍﻧﻮﺍﻉ ﺍﻟﻌﻼﻗﺎﺕ ﻟﻬﺎ ﺑﻌﺾ ﺍﻟﻘﻴﻮﺩ ﺍﻭ ﺍﻟﺸﺮﻭﻁ. ﻓﻤﺜﻼ ﻧﺠﺪ ﻓﻲ ﺍﻟﻤﺜﺎﻝ ) (company ﺍﻥ ﻛﻞ ﻣﻮﻇﻒ ﻳﺠﺐ ﺍﻥ ﻳﻌﻤﻞ ﻓﻲ ﻗﺴﻢ ﻭﺍﺣﺪ ﻓﻘﻂ ﻭﻫﺬﺍ ﻳﻌﺘﺒﺮ ﺷﺮﻁ ﻓﻲ ﻧﻮﻉ ﺍﻟﻌﻼﻗﺔ ﺑﻴﻦ ﺍﻟﻤﻮﻇﻒ ﻭﺍﻟﻘﺴﻢ. ﻭﻫﻨﺎﻟﻚ ﻧﻮﻋﻴﻦ ﻣﻦ ﺍﻟﻘﻴﻮﺩ ﻳﻤﻜﻦ ﺗﻄﺒﻴﻘﻬﺎ ﻋﻠﻲ ﺍﻝ -: R. Ts . 1 - Cardinality Ratio Constraints 2 - Participation Constraints ﻭﻫﺬﺍﻥ ﺍﻟﻘﻴﺪﺍﻥ ﻳﻌﺮﻓﺎﻥ ﺏ structural constraints of relationship types 8

Cont. 1 -cardinality ratio constraints: ﻟﻠﻌﻼﻗﺔ ﺍﻟﺜﻨﺎﺋﻴﺔ Cardinality ratio ﻭﻳﺘﻢ ﺗﺤﺪﻳﺪ ﺍﻝ ( ﻭﺍﻟﺘﻲ relationship Instances) ﺑﺘﺤﺪﻳﺪ ﻋﺪﺩ ﺍﻝ . (participant entity ) ﻳﺸﺎﺭﻙ ﻓﻴﻬﺎ (Specifies the relationship type instances that an entity can participate in) 9

Department : Employee ﺑﻴﻦ works – for ﻣﺜﺎﻝ: ﺍﻟﻌﻼﻗﺔ Cardinality ratio = 1: N (N zero or more) One: Many : ﻛﺎﻻﺗﻲ E. R diagram ﻭﻳﻮﺿﺢ ﺫﻟﻚ ﻓﻲ Department 1 Works-for N Employee 10

Cont. Possible cardinality ratios for binary R. T s: 1: N, N: 1, 1: 1, M: N One : Many , Many : One , One : One, Many : Many : N: 1 ﻣﺜﺎﻝ ﻝ Student N Teach-on 1 University 11

Cont. one : one ﻣﺜﺎﻝ : ﻧﻮﻉ ﺍﻟﻌﻼﻗﺔ Employee 1 Manages 1 Department 12

Cont. Many : Many ﻣﺜﺎﻝ: ﻟﻨﻮﻉ ﻋﻼﻗﺔ Employee M Works-for N Project 13

2. Participation Constraints: (Existence Dependencies): ﻳﻌﺘﻤﺪ ﻋﻠﻲ entity ﻭﻫﻲ ﻟﺘﺤﺪﻳﺪ ﻣﺎ ﺍﺫﺍ ﻛﺎﻥ ﻭﺟﻮﺩ ﺍﻟﻜﺎﺋﻦ (R. T) ﻋﻼﻗﺔ ﺗﺮﺑﻄﻪ ﺑﻜﺎﺋﻦ ﺍﺧﺮ ﻋﺒﺮ ﻧﻮﻉ ﺍﻟﻌﻼﻗﺔ (Participation Constraints) ﻫﻨﺎﻟﻚ ﻧﻮﻋﻴﻦ ﻝ 1. Total Participation. 2. partial Participation. 14

: ﺍﺫﺍ ﺍﻓﺘﺮﺿﻨﺎ ﺍﻥ ﻗﺎﻧﻮﻥ ﺍﻟﺸﺮﻛﺔ ﻳﻨﺺ ﻋﻠﻲ ﺍﻥ ﺃﻲ Total Participation ﻣﺜﺎﻝ ﻝ ﻻ ﻳﻤﻜﻦ ﺍﻥ ﻳﻜﻮﻥ ﻣﻮﺟﻮﺩ ﺍﻻ ﺍﺫﺍ Employee ﻣﻮﻇﻒ ﻳﺠﺐ ﺍﻥ ﻳﻌﻤﻞ ﻓﻲ ﻗﺴﻢ ﻣﻌﻴﻦ ﺇﺫ ﺍﻝ (works-for instances) ﻛﺎﻥ ﻣﺸﺎﺭﻙ ﻓﻲ ﺍﻝ (the participation of employee in works-for R. T is total participation). 15

works – for instances ﻣﺸﺎﺭﻛﺔ ﻓﻲ ﺍﻝ Employee ﻛﻞ ﺍﻟﻜﺎﺋﻨﺎﺕ ﻓﻲ : )ﺑﺨﻄﻴﻦ( ﻛﺎﻵﺘﻲ E. R diagram , ﻳﻮﺿﺢ ﺫﻟﻚ ﻓﻲ ﺍﻝ Employee N Works-for 1 Department 16

-: partial participate ﻣﺜﺎﻝ : ﻝ ( ﻭﻟﻜﻦ manager) ﻧﺠﺪ ﺍﻥ ﻛﻞ ﻗﺴﻢ ﻓﻲ ﺍﻟﺸﺮﻛﺔ ﻟﻪ ﺭﺋﻴﺲ ﻟﻴﺲ ﻟﻜﻞ ﺍﻟﻤﻮﻇﻔﻴﻦ ﺭﺅﺴﺎﺀ ﺍﻗﺴﺎﻡ )ﺟﺰﺀ ﻣﻦ ﺍﻟﻤﻮﻇﻔﻴﻦ( ﻓﺎﺫ ( the participation of employee entity type n manages R. T is partial participation) employee e 1 Manages r 1 r 2 e 3 r 3. . Department d 1 d 2 d 3. . e 4 17

ﺻﻔﺎﺕ ﺍﻧﻮﺍﻉ ﺍﻟﻌﻼﻗﺎﺕ ) (attributes for relationship types ﻳﻤﻜﻦ ﺍﻥ ﻳﻜﻮﻥ ﻹﻧﻮﺍﻉ ﺍﻟﻌﻼﻗﺎﺕ ﺻﻔﺎﺕ ﻣﺜﻞ ﺍﻝ ) (Entity types ﺍﻣﺜﻠﺔ : - ﺍﻟﺼﻔﻪ ) (no of hours ﻋﺪﺩ ﺍﻟﺴﺎﻋﺎﺕ ﺍﻻﺳﺒﻮﻋﻴﺔ ﻟﻠﻤﻮﻇﻒ ﻓﻰ ﺍﻟﻤﺸﺮﻭﻉ ) (project ﻳﻤﻜﻦ ﺍﻥ ﺗﻜﻮﻥ ﺻﻔﺔ ﻟﻨﻮﻉ ﺍﻟﻌﻼﻗﺔ ) ( works- on ﺑﻴﻦ ) -: (employee and project ﻭﺗﻮﺿﺢ ﻓﻰ E_R diagram ﻛﺎﻵﺘﻰ: No of hours Project 81 N Works-on M Employee

. Cont ﺍﻟﺼﻔﺔ ﺗﺎﺭﻳﺦ ﺗﻌﻴﻴﻦ ﺍﻟﻤﺪﻳﺮ ﻟﻠﻘﺴﻢ) (start date ﻳﻤﻜﻦ ﺍﻥ ﻳﻜﻮﻥ ﺻﻔﺔ ﻟﻠﻌﻼﻗﺔ ) (manages ﺑﻴﻦ ). (employee and department 91

cont ﺍﺫﺍ ﻛﺎﻥ ﻟﻨﻮﻉ ﺍﻟﻌﻼﻗﺔ ﺻﻔﺔ ﻭ 1: 1= , cardinality ratio ﻓﻴﻤﻜﻦ ﺍﻥ ﻧﺘﺒﻊ ﺻﻔﺔ ﺍﻟﻌﻼﻗﺔ ﻷﻰ ﻭﺍﺣﺪ ﻣﻦ ﺍﻝ entity types ﺍﻟﻤﺸﺎﺭﻛﺔ ﻓﻰ ﻧﻮﻉ ﺍﻟﻌﻼﻗﺔ. ﻓﻤﺜﻼ ﺍﻟﺼﻔﺔ ﺗﺎﺭﻳﺦ ﺗﻌﻴﻴﻦ ﺍﻟﻤﺪﻳﺮ ﻟﻠﻘﺴﻢ ﻳﻤﻜﻦ ﺍﻥ ﺗﻜﻮﻥ ﺻﻔﺔ ﻝ department ﺍﻭ ﺻﻔﺔ ﻝ . employee ﻭﺍﺫﺍ ﺍﻝ cardinality ratio= 1: N ﺍﻭ 1: N ﻓﺼﻔﺎﺕ ﻧﻮﻉ ﺍﻟﻌﻼﻗﺔ ﺗﺘﺒﻊ ﻝ entity type ﻓﻲ ﺍﻟﺠﺎﻧﺐ N ﻣﻦ ﺍﻟﻌﻼﻗﺔ. ﻣﺜﺎﻝ : ﺃﺬﺍ ﻛﺎﻥ ﻟﺪﻳﻨﺎ ﻣﺜﻼ ﺻﻔﺔ ﺗﺎﺭﻳﺦ ﺗﻌﻴﻴﻦ ﺍﻟﻤﻮﻇﻒ ﻓﻲ ﺍﻟﻌﻼﻗﺔ works – for ﺑﻴﻦ) , ( Employee: Department ﻭ N ﺑﺠﺎﻧﺐ ﺍﻝ employee E. T ﻓﻠﺬﺍ ﻳﺠﺐ ﺍﻥ ﺗﺘﺒﻊ ﺻﻔﺔ ﺗﺎﺭﻳﺦ ﺍﻟﺘﻌﻴﻴﻦ ﻝ . employee E. T 02

. Cont ﺍﺫﺍ ﻛﺎﻧﺖ Cardinality ratio =M: N ﻣﺜﺎﻝ : ﻋﺪﺩ ﺍﻟﺴﺎﻋﺎﺕ ﺍﻻﺳﺒﻮﻋﻴﺔ ﻟﻠﻤﻮﻇﻒ ﻓﻲ ﺍﻟﻌﻤﻞ ﻓﻲ ﺍﻟﻤﺸﺮﻭﻉ ﺗﻈﻞ ﺻﻔﺔ ﻟﻨﻮﻉ ﺍﻟﻌﻼﻗﺔ works – for ﺑﻴﻦ ﺍﻝ . employee & project 12

(Weak entity types ﺍﻧﻮﺍﻉ ﺍﻟﻜﺎﺋﻨﺎﺕ ﺍﻟﻀﻌﻴﻔﺔ . ﻟﻴﺴﺖ ﻟﻬﺎ ﺻﻔﺔ ﺍﻭ ﺻﻔﺎﺕ ﺛﻤﺜﻞ ﺻﻔﺔ ﺍﻟﻤﻔﺘﺎﺡ entity types ﻫﻲ ﻻ ﺗﻤﻴﺰ ﺍﻻ ﺑﻌﻼﻗﺘﻬﺎ ﻣﻊ ﻛﺎﺋﻨﺎﺕ weak entity type ﺍﻟﻜﺎﺋﻨﺎﺕ ﺍﻟﺘﻲ ﺗﻨﺘﻤﻰ ﻟﻞ weak E. Ts ( ﻭﺍﻝ owner or parent) ﺍﺧﺮ entity type ﺍﺧﺮﻯ ﺗﻨﺘﻤﻲ ﻝ . (child E. Ts) ﻳﻄﻠﻖ ﻋﻠﻴﻬﺎ Child entity types participation is total participation in the relation – type between( child) weak entity type and parent entity type. R. T is called (Identifying R. T) A weak entity has a partial key, which is the set of attributes that can uniquely identify weak entities that are related to the same owner entity. 22

In E-R digram: Identifying relationship type Partial key Weak entity type 23

ﻣﺜﺎﻝ : Dependent E. T ﻫﻮ ﺍﻝ WEAK – Entity type ﻻ ﻳﻤﻜﻦ ﺗﺤﺪﻳﺪ ﺍﻟﻤﻜﻔﻮﻝ ﺍﻻ ﻋﻦ ﻃﺮﻳﻖ ﺍﻟﻜﻔﻴﻞ ﻭﻻ ﻳﻤﻜﻦ ﺍﻥ ﻳﻜﻮﻥ ﻟﻪ ﻣﻔﺘﺎﺡ )ﻓﻘﺪ ﻳﺤﺘﻮﻱ ﺍﻟﻤﻔﺘﺎﺡ ﻋﻠﻲ ﺻﻔﺔ ﻣﻦ ﺍﻟﺼﻔﺎﺕ ﺍﻝ employee ﻣﺜﻞ ﺭﻗﻢ ﺍﻟﻤﻮﻇﻒ + ﺍﺳﻢ ﺍﻟﻤﻜﻔﻮﻝ( ﻭﻻ ﻳﻮﺟﺪ ﻣﻜﻔﻮﻝ ﻟﻴﺲ ﻟﻪ ﻛﻔﻴﻞ. M Dependent 42 Dependent of 1 Employee

Refining the ER diagram for the company database ER DIAGRAM – Relationship Types are: WORKS_FOR, 2. MANAGES, 3. WORKS_ON, 4. CONTROLS, 5. SUPERVISION, 6. DEPENDENTS_OF. Determine participate entity types and structural constraints for each relationship type. 1. 25

26

Alternative (min, max) notation for relationship structural constraints Specified on each participation of an entity type E in a relationship type R Specifies that each entity e in E participates in at least min and at most max relationship instances in R Default(no constraint): min=0, max=n (signifying no limit) Must have min max, min 0, max 1 Derived from the knowledge of mini-world constraints Examples: – A department has exactly one manager and an employee can manage at most one department. Specify (0, 1) for participation of EMPLOYEE in MANAGES Specify (1, 1) for participation of DEPARTMENT in MANAGES – An employee can work for exactly one department but a department can have any number of employees. Specify (1, 1) for participation of EMPLOYEE in WORKS_FOR Specify (0, n) for participation of DEPARTMENT in WORKS_FOR 27

The (min, max) notation for relationship constraints Read the min, max numbers next to the entity type and looking away from the entity type 28

COMPANY ER Schema Diagram using (min, max) notation 29

Summary of notation for ER diagrams 30

Some of the Currently Available Automated Database Design Tools COMPANY TOOL FUNCTIONALITY Embarcader o Technologie s ER Studio Database Modeling in ER and IDEF 1 X DB Artisan Database administration, space and security management Oracle Developer 2000/Designer 2000 Database modeling, application development Popkin Software System Architect 2001 Data modeling, object modeling, process modeling, structured analysis/design Platinum (Computer Associates) Enterprise Modeling Suite: Erwin, BPWin, Paradigm Plus Data, process, and business component modeling Persistence Inc. Pwertier Mapping from O-O to relational model Rational (IBM) Rational Rose UML Modeling & application generation in C++/JAVA Resolution Ltd. Xcase Conceptual modeling up to code maintenance Sybase Enterprise Application 31 Data modeling, business logic modeling

• Read section 3. 9 32

Please Be attention!!!!!!! Additional lecture on Thursday (2 -4) 33

Good bye chapter THREE 34

)2( Quiz • ﺍﺭﺳﻢ ﺍﻟـ Diagram ER ﻟﻠﻤﺘﻄﻠﺒﺎﺕ ﺍﺩﻧﺎﻩ ﻭﻫﻲ ﺗﻤﺜﻞ ﻗﺎﻋﺪﺓ ﺑﻴﺎﻧﺎﺕ ﻟﻼﻓﻼﻡ ﻓﻲ ﺍﻟﺴﻮﺩﺍﻥ. – – – ﺍﻟﻔﻴﻠﻢ ) ( Movie ﻳﻤﻴﺰ ﺑﺎﺳﻤﻪ ﻭﺗﺎﺭﻳﺦ ﻋﺮﺿﻪ ﻭﻟﻪ ﻣﺪﺓ ﻋﺮﺽ ﻣﻌﻴﻨﺔ ﻭﻟﻪ ﺷﺮﻛﺔ ﻗﺎﻣﺖ ﺑﺎﻧﺘﺎﺟﻪ ﻭﻟﻪ ﻧﻮﻋﻴﺔ ﻣﻌﻴﻨﺔ ) ﺩﺭﺍﻣﺎ ، ﻛﻮﻣﻴﺪﻳﺎ. . . (. ﺃﻲ ﻓﻴﻠﻢ ﻟﻪ ﻋﺪﺩ ﻣﻦ ﺍﻟﻤﺨﺮﺟﻴﻦ ﻭﻋﺪﺩ ﻣﻦ ﺍﻟﻤﻤﺜﻠﻴﻦ ﻭﻟﻪ ﻋﻠﻲ ﺍﻻﻛﺜﺮ ﺇﺛﻨﻴﻦ ﻣﻦ ﺍﻟﻤﻤﺜﻠﻴﻦ ﻳﻘﻮﻣﻮﻥ ﺑﺎﻟﺪﻭﺭ ﺍﻟﺮﺋﻴﺴﻲ ﻓﻲ ﺍﻟﻔﻴﻠﻢ. ﺍﻟﻤﻤﺜﻞ ) ( Actor ﻳﻤﻜﻦ ﺍﻥ ﻳﻤﻴﺰ ﺑﺎﺳﻤﻪ ﻭﺗﺎﺭﻳﺦ ﻣﻴﻼﺩﻩ ﻭﻳﻤﻜﻦ ﺍﻥ ﻳﻤﺜﻞ ﻓﻲ ﺍﻛﺜﺮ ﻣﻦ ﻓﻴﻠﻢ ﻭﺃﻲ ﻣﻤﺜﻞ ﻳﺠﺐ ﺍﻥ ﻳﻜﻮﻥ ﻟﻪ ﺩﻭﺭ ﻓﻲ ﻓﻴﻠﻢ ﻭﺍﺣﺪ ﻋﻠﻲ ﺍﻻﻗﻞ ﻭﻫﺬﺍ ﺍﻟﺪﻭﺭ ﺍﻣﺎ ﺍﻥ ﻳﻜﻮﻥ ﺩﻭﺭ ﻋﺎﺩﻱ ) ﺗﻤﺜﻴﻞ ( ﺍﻭ ﺩﻭﺭ ﺭﺋﻴﺴﻲ. ﺍﻟﻤﺨﺮﺝ ﺃﻴﻀ ﻳﻤﻴﺰ ﺑﺄﺴﻤﻪ ﻭﻳﺴﺠﻞ ﺗﺎﺭﻳﺦ ﻣﻴﻼﺩﻩ ﻭﻳﺠﺐ ﺍﻥ ﻳﺨﺮﺝ ﻓﻴﻠﻢ ﻭﺍﺣﺪ ﻋﻠﻲ ﺍﻻﻗﻞ. ﻳﻤﻜﻦ ﻟﻠﻤﺨﺮﺝ ﺍﻳﻀ ﺃﻦ ﻳﻜﻮﻥ ﻣﻤﺜﻼ ﻓﻲ ﺃﻲ ﻓﻴﻠﻢ ﺍﻭ ﺍﻻﻓﻼﻡ ﺍﻟﺘﻲ ﻗﺎﻡ ﺑﺈﺧﺮﺍﺟﻬﺎ. ﺍﻟﺸﺮﻛﺎﺕ ﺍﻟﺘﻲ ﺗﻘﻮﻡ ﺑﺎﻻﻧﺘﺎﺝ ﻟﻬﺎ ﺇﺳﻢ ﻭﻋﻨﻮﺍﻥ ﻭﻻ ﻳﻤﻜﻦ ﻟﺸﺮﻛﺘﻴﻦ ﺍﻥ ﺗﺤﻤﻼﻥ ﻧﻔﺲ ﺍﻻﺳﻢ ﻭﻳﻤﻜﻦ ﺍﻥ ﻳﻜﻮﻥ ﻟﻠﺸﺮﻛﺔ ﺍﻛﺜﺮ ﻣﻦ ﻣﻮﻗﻊ ﺩﺍﺧﻞ ﻭﺧﺎﺭﺝ ﺍﻟﺴﻮﺩﺍﻥ. 53