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

ﺑﺴﻢ ﺍﻟﻠﻪ ﺍﻟﺮﺣﻤﻦ ﺍﻟﺮﺣﻴﻢ )5 ( Lecture

ENTITY RELATIONSHIP MODEL ﻧﻤﻮﺫﺝ ﺍﻟﻜﺎﺋﻨﺎﺕ ﺍﻟﻤﺮﺗﺒﻄﺔ Popular high_ level conceptual data Model , developed • by Peter Chen (1976). -: • ﺍﻟﻤﻔﺎﻫﻴﻢ ﺍﻷﺴﺎﺳﻴﺔ ﻟﻠﻨﻤﻮﺫﺝ Entities 1. ﺍﻟﻜﺎﺋﻨﺎﺕ Attributes 2. ﺍﻟﺼﻔﺎﺕ Relationships 3. ﺍﻟﻌﻼﻗﺎﺕ

• ﺍﻝ conceptual Schema ﺗﻜﻮﻥ ﻋﻠﻲ ﺷﻜﻞ Diagram ﻳﺴﻤﻲ ﺏ E-R diagram ﻭﻳﻜﻮﻥ ﺫﻟﻚ ﺑﺎﺳﺘﺨﺪﺍﻡ ﺃﺸﻜﺎﻝ ﻣﻌﻴﻨﺔ ) (page 80) ( E-R diagram Notations ﻟﻜﻞ ﺍﻟﻤﻔﺎﻫﻴﻢ ﻭﺍﻟﻘﻴﻮﺩ.

ﺍﻟﻜﺎﺋﻨﺎﺕ ﻭﺍﻟﺼﻔﺎﺕ • ﺍﻟﻜﺎﺋﻦ ﻫﻮ ﺍﻝ ) (Basic object ﻓﻲ ﻧﻤﻮﺫﺝ ﺍﻟﻜﺎﺋﻨﺎﺕ • ﺗﻌﺮﻳﻒ ﺍﻝ ) Entity ﺍﻟﻜﺎﺋﻦ( : ﻭﻫﻮﺍﻯ ﺷﺊ ﻣﻮﺟﻮﺩ ﻓﻲ ﺍﻟﻌﺎﻟﻢ ﺍﻟﺤﻘﻴﻘﻲ ﻭﻻ ﻳﻌﺘﻤﺪ ﻭﺟﻮﺩﻩ ﻋﻠﻲ ﻭﺟﻮﺩ ﺷﺊ ﺃﺨﺮ. " ” Thing In real world with an Independent existence • ﻭﺟﻮﺩ ﺍﻟﻜﺎﺋﻦ ﺃﻤﺎ ﺃﻦ ﻳﻜﻮﻥ ﻓﻴﺰﻳﺎﺋﻴﺎ ) (physical existence ﻭﻣﺜﺎﻝ ﻟﺬﻟﻚ Car ، house ، student ، person ﺃﻮ ﻳﻜﻮﻥ ﻣﻔﺎﻫﻴﻤﻲ ) (Conceptual existence ﻭﻣﺜﺎﻝ ﻟﺬﻟﻚ jop ، Course ، Company

( ﺗﺴﺘﺨﺪﻡ ﻟﻮﺻﻔﻪ Attributes) • ﻟﻜﻞ ﻛﺎﺋﻦ ﻋﺪﺩ ﻣﻦ ﺍﻟﺼﻔﺎﺕ (Properties to describe entity) -: • ﻣﺜﺎﻝ Entity : Employee -: ﺍﻟﺼﻔﺎﺕ ﻭﻛﻞ ﺻﻔﺔ ﻣﻦ ﻫﺬﻩ ﺍﻟﺼﻔﺎﺕ Name, SSN , Sex , sal , address , age • -: ( ﻣﺜﺎﻝ particular Entity) ( ﻟﻜﻞ ﻛﺎﺋﻦ ﺧﺎﺹ Value) ﺗﻜﻮﻥ ﻟﻬﺎ ﻗﻴﻤﺔ (particular Entity) ﻫﻮ ﻛﺎﺋﻦ ﺧﺎﺹ E 1 • : • ﻗﻴﻢ ﺻﻔﺎﺗﻪ Ali , 46 , M , 30. 000 , Omdurman , 40 • Attribute Values become a major part of data in the DB)(

ﺃﻨﻮﺍﻉ ﺍﻟﺼﻔﺎﺕ ) (Attribute types ( : Atomic attribute ﺍﻟﺼﻔﺔ ﺍﻟﺼﻤﺎﺀ) ﺻﻔﺔ ﻻ ﻳﻤﻜﻦ ﺗﺠﺰﺋﺘﻬﺎ ﻷﻜﺜﺮ ﻣﻦ ﺻﻔﺔ. ﺍﻟﺼﻔﺔ ﻏﻴﺮ ﺍﻟﺼﻤﺎﺀ) -: ( Composite attribute ﺻﻔﺔ ﻳﻤﻜﻦ ﺗﺠﺰﺋﺘﻬﺎ ﻷﻜﺜﺮ ﻣﻦ ﺻﻔﺔ ﻭﻛﻞ ﺻﻔﺔ ﻣﺴﺘﻘﻠﺔ ﻋﻦ ﺍﻻﺧﺮﻱ ﻭﺗﺴﻤﻰ ﺍﻟﺼﻔﺎﺕ ﺍﻟﻤﺠﺰﺋﺔ ﺏ). ( Sub parts ﻣﺜﺎﻝ: - Address Office Address City Home Address Street No state

ﺍﻟﺼﻔﺔ ﻭﺣﻴﺪﺓ ﺍﻟﻘﻴﻤﺔ ) -: ( Single Valued Attribute ﺻﻔﺔ ﻟﻬﺎ ﻗﻴﻤﺔ ﻭﺍﺣﺪﺓ ﻓﻘﻂ ﻟﻜﻞ ﻛﺎﺋﻦ ﺧﺎﺹ. • ﻣﺜﺎﻝ : - ﺍﻟﺼﻔﺔ Age ﻓﻲ ﺍﻟﻜﺎﺋﻦ Employee ﻛﻞ Employee ﻟﻪ ﻋﻤﺮ ﻭﺍﺣﺪ ﻓﻘﻂ. ﺍﻟﺼﻔﺔ ﻣﺘﻌﺪﺩﺓ ﺍﻟﻘﻴﻢ ) -: ( Multivalued attribute • ﺻﻔﺔ ﻟﻬﺎ ﺍﻛﺜﺮ ﻣﻦ ﻗﻴﻤﺔ ﻟﻠﻜﺎﺋﻦ ﺍﻟﺨﺎﺹ. • ﻣﺜﺎﻝ : ﻓﻰ ﺍﻟﻜﺎﺋﻦ Car ﺍﻟﺼﻔﺔ color ﻗﺪ ﻳﻜﻮﻥ ﻟﻌﺮﺑﺔ ﻣﻌﻴﻨﺔ ) (particular entity ﺍﻛﺜﺮ ﻣﻦ ﻟﻮﻥ. • ﺍﻟﺼﻔﺔ ﺍﻟﻤﺮﻛﺒﺔ ) : (complex attributes ﻟﻠﻘﺮﺍﺀﺓ ﻣﻦ ﺍﻟﻜﺘﺎﺏ ﺻﻔﺤﺔ )84(.

• • • ﺍﻟﺼﻔﺔ ﺍﻟﻤﺤﺴﻮﺑﺔ ) -: (Derived attribute ﺻﻔﺔ ﻗﻴﻤﺘﻬﺎ ﻣﺤﺴﻮﺑﺔ ﻣﻦ ﺻﻔﺔ ﺍﻭ )ﺻﻔﺎﺕ( ﺍﺧﺮﻯ ) stored . (attribute ﻣﺜﺎﻝ : - ) Derived attribute (age), stored attribute (Birth date : - • Null value • ﺑﻌﺾ ﺍﻟﺼﻔﺎﺕ ﻗﺪ ﺗﺤﻤﻞ ﺍﻟﻘﻴﻤﺔ Null ﻟﺒﻌﺾ ﺍﻟﻜﺎﺋﻨﺎﺕ ﺍﻟﺨﺎﺻﺔ ﻭﻫﻲ ﺗﻌﻨﻲ ﻗﻴﻢ ﻏﻴﺮ ﻣﻌﺮﻭﻓﺔ ﺍﻭ ﻏﻴﺮ ﻣﻮﺟﻮﺩﺓ . Null = unknown • ﻣﺜﺎﻝ : ﺍﻟﺼﻔﺔ College certificate ﻟﺸﺨﺺ ﻻ ﻳﺤﻤﻞ ﻣﺆﻬﻞ ﺟﺎﻣﻌﻲ ﺗﺤﻤﻞ ﺍﻟﻘﻴﻤﺔ . Null

Entity Type (: - ﻣﺠﻤﻮﻋﺔ ﻣﻦ ﺍﻟﻜﺎﺋﻨﺎﺕ ﻟﻬﺎ ﻧﻔﺲ Entity type ) • ﻧﻮﻉ ﺍﻟﻜﺎﺋﻦ . ﺍﻟﺼﻔﺎﺕ (Set or Collection of the entities that have the same attributes) ( ﻭﻛﻞ Entity types ) • ﻗﺎﻋﺪﺓ ﺍﻟﺒﻴﺎﻧﺎﺕ ﺗﺘﻜﻮﻥ ﻣﻦ ﻋﺪﺓ ﻣﻦ ﺍﻝ . ﻳﻌﺮﻑ ﺑﺎﺳﻤﻪ ﻭﺻﻔﺎﺗﻪ Entity type

Entity Type name : - Attributes: - employee Name, age , salary E 1 company Name, No, president C 1 Ali , 30000 Sisco, ab 60, omer E 2 Entity sets (extension) C 2 Ahmed, 40, 50000 ……. ……… Nilegroup, ac 360, ali ……. . . The collection of all entities of a particular entity type in the database at any point in time is called an entity set. Entity type & entity set have the same name.

E R_ Diagram Notation ( )ﻋﻠﻲ ﺷﻜﻞ ﻣﺴﺘﻄﻴﻞ E R_ DIAGRAM ﻓﻲ ﺍﻝ Entity Type • ﺷﻜﻞ Entity Type. ﻭﺑﺪﺍﺧﻠﻪ ﺇﺳﻢ ﺍﻝ ﺗﻜﻮﻥ ﻋﻠﻲ ﺷﻜﻞ ﺑﻴﻀﺎﻭﻱ ﻣﺘﺼﻠﺔ ﻣﻌﻪ ﺑﺨﻄﻮﻁ Entity Type • ﺻﻔﺎﺕ ﺍﻝ ﻣﺴﺘﻘﻴﻤﺔ age salary employee name

ﺃﺸﻜﺎﻝ ﺍﻧﻮﺍﻉ ﺍﻟﺼﻔﺎﺕ • Multivalued attribute: - • Composite attribute : Sub parts attribute Composite attribute name

• Derived Attribute : -

Key attributes of an entity type ﻣﻦ ﺍﻟﻘﻴﻮﺩ ﺍﻟﻬﺎﻣﺔ ﺟﺪﺍ ﻟﻠﻜﺎﺋﻨﺎﺕ ﺍﻟﻤﻜﻮﻧﺔ ﻝ Entity – type ﻭﻫﻮ ﺍﻥ ﻛﻞ Entity – type ﻟﻪ ﺻﻔﺔ ﺗﻤﺜﻞ ﺻﻔﺔ ﺍﻟﻤﻔﺘﺎﺡ Key attribute ﻭﻟﻬﺬﻩ ﺍﻟﺼﻔﺔ ﻗﻴﻤﺔ ﻣﺨﺘﻠﻔﺔ ﻟﻜﻞ ﻛﺎﺋﻦ ﻓﻲ ﺍﻝ E. T ﻭﻳﻌﺮﻑ ﻫﺬﺍ ﺍﻟﻘﻴﺪ ﺏ ) ( Uniqueness Constraint on attributes : Key attribute In E. R Diagram Key attribute name • ﺍﻟﺼﻔﺔ ﺍﻟﺘﻲ ﺗﻤﺜﻞ ﺻﻔﺔ ﺍﻟﻤﻔﺘﺎﺡ ﻳﻤﻜﻦ ﺍﻥ ﺗﻜﻮﻥ ﺻﻔﺔ ﻭﺍﺣﺪﺓ ) (Simple Key ﺍﻭ ﺗﺘﻜﻮﻥ ﻣﻦ ﻋﺪﺓ ﺻﻔﺎﺕ ﻣﻦ ﺍﻝ . (Composite Key) E. T • ﻭﻳﻤﻜﻦ ﺍﻥ ﻳﻜﻮﻥ ﻝ E. T ﺍﻛﺜﺮ ﻣﻦ ﺻﻔﻪ )ﻛﻞ ﺻﻔﺔ ﻋﻠﻲ ﺣﺪﺍ( ﻳﻤﻜﻦ ﺍﻥ ﺗﻤﺜﻞ ﺻﻔﺔ ﺍﻟﻤﻔﺘﺎﺡ , ﻭﻗﺪ ﻻ ﻳﻜﻮﻥ ﻝ E. T ﺃﻲ ﺻﻔﺔ ﺗﻤﺜﻞ ﺻﻔﺔ ﺍﻟﻤﻔﺘﺎﺡ ﻭﻳﺴﻤﻲ ﻓﻲ ﻫﺬﻩ ﺍﻟﺤﺎﻟﺔ ﺑﺎﻝ E. T ﺍﻟﻀﻌﻴﻒ ). (weak Entity type

Value sets (Domain) ﻟﻬﺎ ﻣﺠﻤﻮﻋﺔ ﻣﻦ ﺍﻟﻘﻴﻢ ﺍﻟﺘﻲ ﻳﻤﻜﻦ ﺍﻥ ﺗﺎﺧﺬﻫﺎ ﺗﺴﻤﻰ E. T ﺃﻲ ﺻﻔﺔ ﻣﻦ ﺻﻔﺎﺕ ﺍﻝ . ( ﻟﻬﺬﻩ ﺍﻟﺼﻔﺔ Domain or Value sets ) ﺑﺎﻟﻤﺠﺎﻝ • Value sets can’t be displayed In E-R diagram • (Value sets (Domain ) + Key ) called attribute constraints in E-R model. •

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

An Example Database Application • Company DB requirements: • The company has several departments. Each department has a unique name, a unique number, and a particular employee who manages the department. we keep track of the start date when the employee began managing the department. A department may have several locations. • A department controls a number of projects, each of which has a unique name, a unique number, and a single location.

Cont. • We store each employee's name, social security number, address, salary, sex, and birth date. An employee is assigned to one department but may work on several projects. We keep track of the number of hours per week that an employee works on each project. • We want to keep track of the dependents of each employee for insurance purposes. We keep each dependent ‘s first name , sex, birth date, and relationship to the employee.

ﺍﻟﺘﺤﺪﻳﺪ ﺍﻹﺑﺘﺪﺍﺋﻰ ﻟﻞ E. Ts ﻭﺍﻟﺼﻔﺎﺕ ﻟﻘﺎﻋﺪﺓ ﺍﻟﺒﻴﺎﻧﺎﺕ Company • Four Entity types : 1. Department Attributes: Name , Number, Location , Manager start date. • Location : - Multivalued attribute. • Name, Number: - Separate Key Attribute ( )ﺃﻲ ﺻﻔﺔ ﻣﻨﻬﻢ ﻳﻤﻜﻦ ﺍﻥ ﺗﻤﺜﻞ ﺍﻟﻤﻔﺘﺎﺡ 2. Project: Attributes: - Name, Number, Location, Controlling Departments. Name or Number ( )ﺃﻲ ﺻﻔﺔ ﻣﻨﻬﻢ ﻳﻤﻜﻦ ﺍﻥ ﺗﻤﺜﻞ ﺻﻔﺔ ﺍﻟﻤﻔﺘﺎﺡ

Cont. 3. Employee : • Attributes: - Name, SSN, Sex, Address, Salary, Birth date, Department, Supervisor. • Name and Address: - composite Attributes. 4. Dependant • Attributes: Employee, Dependant Name, Sex, Birth date, Relation (Employee).

• ﻫﻨﺎﻟﻚ ﺷﺮﻁ ﻟﻢ ﻳﻮﺿﺢ ﻓﻲ ﺍﻝ E. Ts ﻭﻫﻮ ﺍﻥ ﻛﻞ ﻣﻮﻇﻒ ﻳﻌﻤﻞ ﻓﻲ ﺍﻛﺜﺮ ﻣﻦ Project ﻭﺗﺤﺴﺐ ﻟﻪ ﻋﺪﺩ ﺍﻟﺴﺎﻋﺎﺕ ﺍﻻﺳﺒﻮﻋﻴﻪ ﻓﻲ ﻛﻞ Project • ﻫﺬﻩ ﺍﻟﺼﻔﺔ ﻟﻬﺎ ﺧﻴﺎﺭﻳﻦ ﺍﻣﺎ ﺗﻮﺿﻊ ﻙ ) (Multivalued Composite Attribute ﺿﻤﻦ ﺻﻔﺎﺕ ﺍﻝ Employee E. T ﻛﺎﻷﺘﻲ : - }) {( Workers on (project , Hours • ﺃﻮ ﺿﻤﻦ ﺻﻔﺎﺕ ﺍﻝ project ﻛﺎﻻﺗﻲ: - }) { Workers(employee, hours

Relationships (ﺍﻧﻮﺍﻉ ﺍﻟﻌﻼﻗﺎﺕ Relationship types) -: ● Relationship type R among n entity types E 1, . …. , En defined as a set of associations (or relationship set) among entities from these types. Relationship: types: R. Ts) (Remark : - ( ●

ﻣﺜﺎﻝ

• R: Relationship type (or relationship set ) • E 1 , E 2 : Entity types , Each of one is said to participate in relationship type R • r 1 , r 2 , r 3, …, r 7 : relationship instances (or set ). • (individual entities) (e 1, e 2, e 3, …, e 7) and (e'1, e'2, e'3) are said to participate into relationship instances. ﻋﻠﻲ ﺍﻧﻬﺎ ﻣﺠﻤﻮﻋﺔ ﻣﻦ relationship type • ﻭﻟﺬﺍ ﻳﻤﻜﻦ ﺗﻌﺮﻳﻒ ﺍﻟـ (e 1, …. en) individual entities ( ﻭﺍﻟﺘﻲ ﺗﺮﺑﻂ ﺑﻴﻦ ﺍﻝ ri) instances ( participant entity types) ( ﻫﻲ ﻛﺎﺋﻨﺎﺕ ﻟـ e 1, …. en) ﺣﻴﺚ

: ﻣﺜﺎﻝ ● ● ● Works- for : Relationship type Participant entity types: Employee and Department ﺃﻲ ﺃﻦ ﺍﻟﻌﻼﻗﺔ ﺑﻴﻦ Department , Employee) ( )ﺍﻟﻤﻮﻇﻒ ﻭﺍﻟﻘﺴﻢ ﺍﻟﺬﻱ ( ﺑﻪ

R. T in E-R diagram Std no Student std name Teach-on University name University

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

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

• • • 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

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

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

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

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

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

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

Cont. • Possible cardinality ratio 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

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

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

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

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

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

-: 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 e 4 r 3. . Department d 1 d 2 d 3. .

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

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

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

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

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

• In E-R digram: Identifying relationship type Weak entity type

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

• ﺍﻟﺮﺟﺎﺀ ﻣﺮﺍﺟﻌﺔ ﺍﻻﺷﻜﺎﻝ ﺍﻟﻤﺴﺘﺨﺪﻣﺔ ﻓﻲ E-R DIAGRAM ﻓﻲ) 3( CHAPTER ﻭﻣﺮﺍﺟﻌﺔ )6. 3( . SECTION • ﺗﺤﺪﻳﺪ ﺍﻟﻌﻼﻗﺎﺕ ﻭﺷﺮﻭﻃﻬﺎ ﻭﺻﻔﺎﺗﻬﺎ ﻟﻘﺎﻋﺪﺓ ﺍﻟﺒﻴﺎﻧﺎﺕ COMPANY • DESIGN FOR THE COMPANY – REFINING THE ER DATABASE