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

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

Lecture(5) • Chapter 3 Data Modeling Using the Entity-Relationship (ER) Model 2

Database Design process ﻋﻤﻠﻴﺔ ﺗﺼﻤﻴﻢ ﻗﺎﻋﺪﺓ ﺍﻟﺒﻴﺎﻧﺎﺕ ﺗﺤﺘﻮﻱ ﻋﻠﻲ ﺍﻟﺨﻄﻮﺍﺕ ﺍﻟﺘﺎﻟﻴﺔ: 1 st step: - Requirements collection and analysis ﺗﺠﻤﻴﻊ ﺣﺎﺟﺎﺕ ﺍﻟﻤﺴﺘﺨﺪﻣﻴﻦ ﻭﺗﺤﻠﻴﻠﻬﺎ. . ﺗﻨﺘﻬﻲ ﻫﺬﻩ ﺍﻟﺨﻄﻮﺓ ﺑﺘﺤﺪﻳﺪ ﻛﻞ ﺍﺣﺘﻴﺎﺟﺎﺕ ﺍﻟﻤﺴﺘﺨﺪﻣﻴﻦ ) ( data requirements ﻭﺗﺤﺪﻳﺪ ﻛﻞ ﺍﻟﻌﻤﻠﻴﺎﺕ ﺍﻟﻤﻄﻠﻮﺑﺔ ﻋﻠﻲ ﻗﺎﻋﺪﺓ ﺍﻟﺒﻴﺎﻧﺎﺕ ) (Functional requirements ﻭﺗﺸﻤﻞ ﺍﻟﻌﻤﻠﻴﺎﺕ ﺍﻟﻤﻌﺮﻓﺔ ﺑﻮﺍﺳﻄﺔ ﺍﻟﻤﺴﺘﺨﺪﻣﻴﻦ ) ( User defined operations ﻭﻋﻤﻠﻴﺎﺕ ﺍﻻﺳﺘﺮﺟﺎﻉ ﻭﺍﻟﺘﺤﺪﻳﺚ by using dataflow diagram , sequences diagram . . . etc

: 2 nd step: - Conceptual design ﺑﻤﺠﺮﺩ ﺗﺠﻤﻴﻊ ﺍﻻﺣﺘﻴﺎﺟﺎﺕ ﻭﺗﺤﻠﻴﻠﻬﺎ ، ﻳﺸﺮﻉ ﺍﻟﻤﺼﻤﻤﻮﻥ ﻓﻲ ﺇﻧﺸﺎﺀ ﻣﺎ ﻳﻌﺮﻑ ﺑـ Conceptual Schema ﻟﻘﺎﻋﺪﺓ ﺍﻟﺒﻴﺎﻧﺎﺕ ﻭﻹﻧﺸﺎﺀ ﻫﺬﻩ ﺍﻟﺨﺎﺭﻃﺔ ﺗﺴﺘﺨﺪﻡ ﺍﺣﺪﻱ ﻧﻤﺎﺫﺝ ﺍﻟـ ) (High-level conceptual data Models ﻭﺫﻟﻚ ﺑﻮﺻﻒ ﺍﻟﺒﻴﺎﻧﺎﺕ ﻋﻦ ﻃﺮﻳﻖ ﺍﺳﺘﺨﺪﺍﻡ ﻣﻔﺎﻫﻴﻢ ﺍﻟﻨﻤﻮﺫﺝ ﻣﺜﺎﻝ ﻟﺬﻟﻚ ﺍﻟـ ) . (E- R Model ﻭﻫﺬﻩ ﺍﻟﻤﻔﺎﻫﻴﻢ ﻻ ﺗﺤﺘﻮﻱ ﻋﻠﻲ ﺃﻲ ﺗﻔﺎﺻﻴﻞ ﻟﻠﺘﻄﺒﻴﻖ ). (Implementation details ﻭﻟﺬﺍ ﺗﺴﺘﺨﺪﻡ ﺍﻟـ Conceptual Schema ﻛﻤﺮﺟﻊ ﻟﻠﺘﺄﻜﺪ ﻣﻦ ﺃﻦ ﺍﺣﺘﻴﺎﺟﺎﺕ ﻛﻞ ﺍﻟﻤﺴﺘﺨﺪﻣﻴﻦ ﻣﺴﺘﻮﻓﺎﻩ ﻓﻲ ﺍﻟﺒﻴﺎﻧﺎﺕ ﻭﺍﻥ ﺍﻻﺣﺘﻴﺎﺟﺎﺕ ﻟﻴﺲ ﺑﻬﺎ ﺗﻀﺎﺭﺏ ﻷﻦ ﺍﻟﻤﺼﻤﻤﻴﻦ ﻳﻜﻮﻥ ﺗﺮﻛﻴﺰﻫﻢ ﻓﻘﻂ ﻋﻠﻲ ﺗﺼﻤﻴﻢ ﺧﺎﺭﻃﺔ ﺗﺴﺘﻮﻓﻲ ﻛﻞ ﺍﺣﺘﻴﺎﺟﺎﺕ ﺍﻟﻤﺴﺘﺨﺪﻣﻴﻦ ﺩﻭﻥ ﺍﻟﺘﻔﻜﻴﺮ ﻓﻲ ﻃﺮﻳﻘﺔ ﺍﻟﺘﻄﺒﻴﻖ ﻭﺗﻜﻮﻥ ﺍﻟﺨﺎﺭﻃﺔ ﻣﻔﻬﻮﻣﺔ ﻟﺪﻱ ﺍﻟﻤﺴﺘﺨﺪﻣﻴﻦ ﻏﻴﺮ ﺍﻟﻤﺘﺨﺼﺼﻴﻦ.

3 rd Step: - Implementation of the database, using a commercial DBMS: ﻣﻦ Conceptual Schema ﻓﻲ ﻫﺬﻩ ﺍﻟﺨﻄﻮﺓ ﻳﺘﻢ ﺗﺤﻮﻳﻞ ﺍﻟـ ﺍﻟﻲ High level conceptual data Model ﺍﻟـ ﺃﻲ ﺍﻟﻨﻤﻮﺫﺝ ﺍﻟﻤﺴﺘﺨﺪﻡ ﻓﻲ Implementation data Model ﺍﻟﻤﺨﺘﺎﺭﺓ ﻟﺘﻄﺒﻴﻖ ﻗﺎﻋﺪﺓ ﺍﻟﺒﻴﺎﻧﺎﺕ. ﻭﻫﺬﺍ ﺍﻟﺘﺤﻮﻳﻞ ﻳﺴﻤﻲ ﺑـ DBMS ﺍﻝ Logical design ﺃﻮ Data Model Mapping Database ) ﻭﻫﺬﻩ ﺍﻟﺨﻄﻮﺓ ﺗﻨﺘﻬﻲ ﺑﺈﻧﺸﺎﺀ ﺧﺎﺭﻃﺔ ﻗﺎﻋﺪﺓ ﺍﻟﺒﻴﺎﻧﺎﺕ : ﻣﺜﺎﻝ ﻟﺬﻟﻚ DBMS ( ﺑﺎﺳﺘﺨﺪﺍﻡ ﺍﻟﻨﻤﻮﺫﺝ ﺍﻟﻤﺴﺘﺨﺪﻡ ﻓﻲ ﺍﻟـ schema E-R model mapping to Relational Model.

: Final step: - physical design phase ﺃﺜﻨﺎﺀﻫﺬﻩ ﺍﻟﺨﻄﻮﺓ ﻳﺘﻢ ﺗﺤﺪﻳﺪ ﻛﻞ ﺗﻔﺎﺻﻴﻞ ﺍﻟﺘﺨﺰﻳﻦ ﻭﺫﻟﻚ ﺑﺘﺤﺪﻳﺪ: . Access paths, file organization for database file ﻭﻫﺬﻩ ﺍﻟﺨﻄﻮﺓ ﺗﻨﺘﻬﻲ ﺑﺈﻧﺸﺎﺀ ﺧﺎﺭﻃﺔ ﺩﺍﺧﻠﻴﺔ ﻟﻘﺎﻋﺪﺓ ﺍﻟﺒﻴﺎﻧﺎﺕ ) (internal schema ﻋﻠﻲ ﺍﻟﺘﻮﺍﺯﻱ ﻣﻊ ﻛﻞ ﻫﺬﻩ ﺍﻟﺨﻄﻮﺍﺕ ﻳﺘﻢ ﺃﻴﻀﺎ ﺗﺼﻤﻴﻢ ﺍﻟﺒﺮﺍﻣﺞ ﺍﻟﺘﻄﺒﻴﻘﻴﺔ )ﺑﺎﺳﺘﺨﺪﺍﻡ ﻃﺮﻕ ﻫﻨﺪﺳﺔ ﺍﻟﺒﺮﻣﺠﻴﺎﺕ( ﻭﺗﻄﺒﻴﻘﻬﺎ ﻛﺤﺮﻛﺎﺕ ﻟﻘﺎﻋﺪﺓ ﺍﻟﺒﻴﺎﻧﺎﺕ ) ( Database Transactions : See figure bellow

Overview of Database Design Process Slide 3 - 7

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 ﻟﻠﻘﺮﺍﺀﺓ ﻣﻦ ﺍﻟﻜﺘﺎﺏ ﺻﻔﺤﺔ )56(.

ﺍﻟﺼﻔﺔ ﺍﻟﻤﺤﺴﻮﺑﺔ ) -: (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.

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. (Remark : - (Relationship: types: R. Ts)

ﻣﺜﺎﻝ

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

(Relationship type Degree) ﺩﺭﺟﺔ ﻧﻮﻉ ﺍﻟﻌﻼﻗﺔ . ﺍﻟﻤﺸﺎﺭﻛﺔ ﻓﻲ ﻧﻮﻉ ﺍﻟﻌﻼﻗﺔ 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 in 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. .