Entity Relationship Diagrams

1
Entity Relationship Diagrams

• 4 main concepts
• Entity
• Property
• Relationship
• Subtype

2
Entity Relationship Diagrams

• Entity
• A distinguishable object or thing
• The world is made up of entities, both concrete
  and abstract.
• e.g. supplier, part, shipment, purchase order,
  employee, student, module, result
• In a description, entities are often referred to
  as nouns
• (but not all nouns become entities)

3
Entities

• Can be classified into Entity types, with
  entities being instances of that type
• e.g. employee as an entity type, and individual
  employees as instances of that type
• Advantage is that some properties can be
  associated with an entity type, and thus apply to
  all instances of that type

4
Entities

• Can be further subdivided into
• Weak entities
• These are entities whose existence is dependent
  upon another entity
• e.g. an employees qualifications need the
  corresponding employee entity to be of value
• Regular entities (aka strong entities or
  entities)
• These are entities that are of interest in their
  own right

5
Properties

• Property
• Some piece of information that describes an
  entity
• e.g. supplier number, shipment quantity,person
  height, module type, degree classification
• In a description these might be nouns or
  adjectives

6
Properties

• There are various kinds of property
• Simple or composite
• e.g. employee name (composite)
• first name or surname or title (simple)
• Key (or unique within a context)
• e.g. an NI Number defines a specific employee

7
Properties

• Single or multi-valued
• e.g. Alumni qualification(s) A UWE student may
  have earned more than one qualification and we
  may be interested in only the most recent, or in
  all of them
• Base or derived
• Property is derived rather than an actual value,
  
• e.g. total price for an invoice is the sum of
  the prices of invoice items plus VAT

8
Relationships

• Relationships interconnect two or more entities
• e.g shipment (supplier and part)
• recording (composition, orchestra and
  conductor)
• They can be nouns or verbs in descriptions
• The entities involved in a relationship are
  called participants, and the number of
  participants is called the degree of the
  relationship

9
Relationships

• If R is a relationship involving E as a
  participant, then if every instance of E
  participates in at least one instance of R, then
  the participation of E in R is said to be total
  otherwise it is partial
• e.g. If every employee must belong to a
  department then the participation of employee
  with a relationship department-employs
  (connecting employee with department) is total
• If it is possible for a department to have no
  employees, then the participation of department
  with department-employs is partial

10
Relationships

• Assuming all relationships are binary they can be
  classified as
• One-to-one e.g. birth mother and firstborn
  child
• One-to-many or many-to-one
• e.g. department and employee
• Many-to-many e.g. employee and project

11
Entity subtypes and supertypes

• Any entity is of at least one entity type but an
  entity can be of several types simultaneously
• e.g. car is a subtype of entity type vehicle, and
  conversely, vehicle is a supertype of entity type
  car. Thus properties of car can be inherited from
  vehicle

12
E/R diagrams (optional/mandatory relationships)
13
E/R diagrams(recursive relationships)
14
E/R diagrams(hidden entities)
15
E/R diagrams (entity subtypes and supertypes)
16
(No Transcript)
17
Mapping E/R models to Relations

• Entities
• Entities map into relations with their properties
  as attributes
• Key properties are candidate keys
• Relationships
• Relationships (especially mm) map into relations
• These are likely to have the primary keys of the
  participating entities as foreign keys for the
  relation. The designer needs to specify an
  appropriate set of Update and Delete rules for
  each foreign key

18
Mapping E/R models to Relations

• Relationships (cont)
• Primary key could be the composite of the primary
  keys of the participating entities, e.g. S,P
  for SP. or could introduce a new key property,
  e.g. shipment number SP
• One-to-one relationships are comparatively rare
  and often result in a merging together of
  relations

19
Mapping E/R models to Relations

• Properties
• Single-valued properties become attributes
• Multi-valued properties are removed so as to
  conform to First Normal Form and so need their
  own relation

20
Mapping E/R models to Relations(handling
supertypes and subtypes)
21
(No Transcript)
22
(No Transcript)
23
(No Transcript)
24
(No Transcript)
25
(No Transcript)
26

• create table EMPLOYEE ( EMP_ID
  NUMBER(12) not null, DEPT_ID
  NUMBER(8) not null,
  EMP_SSN CHAR(9)
  not null, EMP_FIRST_NAME VARCHAR2(20)
  not null, EMP_LAST_NAME
  VARCHAR2(30) not null,
  EMP_BIRTH_DATE DATE
  not null, EMP_GENDER CHAR(1)
  not null, EMP_HIRE_DATE
  DATE not null,
  EMP_STREET VARCHAR2(80), EMP_CITY
  VARCHAR2(40), EMP_STATE
  CHAR(2), EMP_ZIP CHAR(5),
  constraint PK_EMPLOYEE primary key (EMP_ID),
  constraint AK_EMP_UID2_EMPLOYEE unique
  (EMP_SSN), constraint AK_EMP_UID3_EMPLOYEE
  unique (EMP_FIRST_NAME, EMP_LAST_NAME,
  EMP_BIRTH_DATE, EMP_GENDER), constraint
  FK_EMPLOYEE_RELATIONS_DEPARTME foreign key
  (DEPT_ID) references DEPARTMENT
  (DEPT_ID))

27

• create table EXEMPT_EMPLOYEE ( EMP_ID
  NUMBER(12) not null,
  EMP_MONTHLY_SALARY NUMBER(8,2)
  not null, EMP_VACATION_WEEKS SMALLINT
  not null, constraint
  PK_EXEMPT_EMPLOYEE primary key (EMP_ID),
  constraint FK_EXEMPT_E_INHERITAN_EMPLOYEE foreign
  key (EMP_ID) references EMPLOYEE (EMP_ID))
• create table NON_EXEMPT_EMPLOYEE ( EMP_ID
  NUMBER(12) not null,
  UNION_ID NUMBER(8)
  not null, EMP_HOURLY_RATE NUMBER(5,2)
  not null, EMP_OVERTIME_RATE
  NUMBER(5,2) not null,
  constraint PK_NON_EXEMPT_EMPLOYEE primary key
  (EMP_ID), constraint FK_NON_EXEM_RELATIONS_UNIO
  N foreign key (UNION_ID) references "UNION"
  (UNION_ID), constraint FK_NON_EXEM_INHERITAN_EM
  PLOYEE foreign key (EMP_ID) references EMPLOYEE
  (EMP_ID)

28
(No Transcript)
29
(No Transcript)