Data Modeling Using the Entity-Relationship (ER) Model

1
??? ???? ?????? ??????

• Lecture( 6)

Data Modeling Using the Entity-Relationship (ER)
Model
2
???? ??? ??????? Degree) (Relationship type

• ?? ??? ??? types Entity ???????? ?? ??? ???????.
• (Number of participant Entity type into
  relationship type)
• ???? ???? ??? ??????? 2 works for

3

• ??? ??????? ?? ?????? ??????
• (???? ??
• (Recursive relationship type) ???????? ?? ???
  ????? ???? ??? ?????? ??? ??? Entity type ???????
  ?? ??? ???????.
• ???? ?????? ?? ?????? ??????-
• R .T Supervision
• ?? ???? ?? ???? ??? ???? ???? ?? (?????) ???? ???
  ?? ?? Employee E.T ?? ?? E .T ??????? ???????? ??
  ??????? Supper vision

4

• e1 supervisor of e2
• e2 supervised by e1
• (all participating entity In R.T belong to only
  one E.T)

5

• ??? ??????? ?? ????????????? -( (Ternary
• Number of participate Entity types In to R.T
  3

6
????
father
parent
child
f1
r1
c1
mother
m1
7

• ????? ???????? ?? ?????? ???????( (Binary
  Relationship types
• ?? ???? ???? ???????? (??? ????? ???? ????
  ???????? ??? ?? ???????? ????????)

8
???? ????? ???????? (Relationship types
Constraints (

• ?? ?????? ??????? ??? ?? ???? ????? ???????? ???
  ??? ?????? ?? ?????? . ????? ??? ?? ??????
  (company) ?? ?? ???? ??? ?? ???? ?? ??? ???? ???
  ???? ????? ??? ?? ??? ??????? ??? ?????? ?????? .
• ?????? ????? ?? ?????? ???? ??????? ??? ?? R.Ts
  -
• 1- Cardinality Ratio Constraints.
• 2- Participation Constraints
• ????? ??????? ?????? ?
• structural constraints of relationship types

9
Cont.

• 1-cardinality ratio constraints-
• ???? ????? ?? ratio Cardinality ???????
  ???????? ?????? ??? ?? (relationship Instances)
  ????? ????? ???? (participant entity ).
• (Specifies the relationship type instances that
  an entity can participate in)

10

• ???? ??????? works for ??? Department
  Employee
• Cardinality ratio 1 N (N
  zero or more)
• One Many
  
• ????? ??? ?? E .R diagram ??????

11
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
• ???? ?N1

12
Cont.

• ???? ??? ??????? one one

1
13
Cont.

• ???? ???? ????? Many Many

14

• 2. Participation Constraints (Existence
  Dependencies)
• ??? ?????? ?? ??? ??? ???? ?????? entity ?????
  ??? ????? ????? ????? ??? ??? ??? ??????? (R.T)
• ????? ????? ? (Participation Constraints)
• 1. Total Participation.
• 2. partial Participation.

15

• ???? ? Total Participation ??? ??????? ?? ?????
  ?????? ??? ??? ?? ?? ???? ??? ?? ???? ?? ??? ????
  ???? ?? Employee ?? ???? ?? ???? ????? ??? ???
  ??? ????? ?? ??(works-for instances)
• (the participation of employee in works-for R.T
  is total participation).

16

• ?? ???????? ?? Employee ?????? ?? ?? instances
  works for ,???? ??? ?? ?? E.R diagram (?????)
  ??????

17

• ???? ? partial participate -
• ??? ?? ?? ??? ?? ?????? ?? ???? (manager) ????
  ??? ??? ???????? ????? ????? (??? ?? ????????)
  ?????
• ( the participation of employee entity type n
  manages R.T is partial participation)

18
???? ????? ???????? attributes for relationship
types) )

• ???? ?? ???? ?????? ???????? ???? ??? ?? (Entity
  types)
• ????? - ????? (no of hours) ??? ???????
  ????????? ?????? ?? ??????? (project) ???? ??
  ???? ??? ???? ??????? (works- on ) ??? (employee
  and project) - ????? ?? E_R diagram ??????

19
Cont.

• ????? ????? ????? ?????? ?????(start date ) ????
  ?? ???? ??? ??????? (manages) ??? (employee and
  department) .

20
cont

• ??? ??? ???? ??????? ??? ?cardinality ratio11
  ,????? ?? ???? ??? ??????? ??? ???? ?? ?? entity
  types ???????? ?? ??? ???????.
• ?????? ????? ????? ????? ?????? ????? ???? ??
  ???? ??? ? department ?? ??? ? employee.
• ???? ?? cardinality ratio 1N ?? N1
• ????? ??? ??????? ???? ? entity type ?? ??????N
  ?? ??????? .
• ???? ??? ??? ????? ???? ??? ????? ????? ??????
  ?? ??????? works for ??? EmployeeDepartment))
  , ?N ????? ?? employee E.T ???? ??? ?? ????
  ??? ????? ??????? ? employee E.T.

21
Cont.

• ??? ???? MN Cardinality ratio
• ???? ??? ??????? ????????? ?????? ?? ????? ??
  ??????? ??? ??? ???? ??????? works for ??? ??
  employee project.

22
????? ???????? ??????? Weak entity types)

• ?? entity types ???? ??? ??? ?? ???? ???? ???
  ???????.
• ???????? ???? ????? ?? weak entity type ?? ????
  ??? ???????? ?? ?????? ???? ????? ? entity type
  ??? (owner or parent) ??? weak E.Ts ???? ?????
  (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.

23

• In E-R digram
• Identifying relationship type

Weak entity type
24

• ???? Dependent E.T ?? ??type WEAK Entity
• ?? ???? ????? ??????? ??? ?? ???? ?????? ???
  ???? ?? ???? ?? ????? (??? ????? ??????? ??? ???
  ?? ?????? ?? employee??? ??? ?????? ???
  ???????)
• ??? ???? ????? ??? ?? ???? .

M
25
Refining the ER diagram for the company database

• ER DIAGRAM Relationship Types are
• WORKS_FOR,
• MANAGES,
• WORKS_ON,
• CONTROLS,
• SUPERVISION,
• DEPENDENTS_OF.
• Determine participate entity types and
  structural constraints for each relationship type.

26
(No Transcript)
27
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) min0, maxn (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

28
The (min,max) notation for relationship
constraints
Read the min,max numbers next to the entity type
and looking away from the entity type
29
COMPANY ER Schema Diagram using (min, max)
notation
30
Summary of notation for ER diagrams
31
Some of the Currently Available Automated
Database Design Tools
COMPANY TOOL FUNCTIONALITY
Embarcadero Technologies ER Studio Database Modeling in ER and IDEF1X
Embarcadero Technologies 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 Suite Data modeling, business logic modeling
Visio Visio Enterprise Data modeling, design/reengineering Visual Basic/C
32

• Read section 3.9

33

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

34

• Good bye chapter THREE

35
Quiz (2)

•   ???? ??? Diagram ER ????????? ????? ???
  ???? ????? ?????? ??????? ?? ???????.
• ?????? ( Movie ) ???? ????? ?????? ???? ???
  ??? ??? ????? ??? ???? ???? ??????? ??? ?????
  ????? ( ????? ? ??????? ...) .
• ?? ???? ?? ??? ?? ???????? ???? ?? ???????? ???
  ??? ?????? ????? ?? ???????? ?????? ??????
  ??????? ?? ??????.
• ?????? ( Actor ) ???? ?? ???? ????? ?????? ??????
  ????? ?? ???? ?? ???? ?? ???? ??? ???? ??? ??
  ???? ?? ??? ?? ???? ???? ??? ????? ???? ????? ???
  ?? ???? ??? ???? ( ????? ) ?? ??? ?????.
• ?????? ????? ???? ????? ????? ????? ?????? ????
  ?? ???? ???? ???? ??? ????? . ???? ?????? ?????
  ?? ???? ?????? ?? ?? ???? ?? ??????? ???? ???
  ????????.
• ??????? ???? ???? ???????? ??? ??? ?????? ???
  ???? ??????? ?? ?????? ??? ????? ????? ?? ????
  ?????? ???? ?? ???? ???? ????? ???????.