Data Modelling Using EntityRelationship ER Model

1
Chapter 3

• Data Modelling UsingEntity-Relationship (ER)
  Model

2
Phases of Database Design
3
Company Example Database Application

• Description of the mini-world
• The company is organized into departments.
• Each department has a unique name, a unique
  number and a particular employee who manages the
  department, including the date he/she began
  managing the department.
• A department may have several locations.

4
Contd

• A department controls a number of projects
• Each project has a unique name, number and a
  single location

5
Contd

• We record each employees name, SSN, address,
  salary, sex and birth date. Also we record who is
  the supervisor
• An employee works in one department but may work
  on several projects in different department
• We track work hours of employees in projects

6
Contd

• We record the dependants of each employee,
  including the first name, sex, birth date and
  relationship to the employee

7
ER Schema for Company
8
Entity Attributes

• Entity is the thing either physical existence
  (person, car, house) or conceptual (company,
  course, job).
• Attributes are the properties of the entity
• Each attributes has values.

9
Entity, attribute value example
10
Composite vs. Simple Attributes

• Composite attributes can be divided into smaller
  part.
• For example, address consist of
• Street Address
• City
• State
• Postal Code
• Simple (atomic) attributes are no longer divisble

11
Cond
12
Single valued vs. Multi-valued

• Singe valued
• Age
• Sex
• SSN
• Multi valued
• Children

13
Other terms

• Stored vs. derived values. Derived values example
  may include GPA, sub-totals, etc.
• Null values nothing, not zero, not blank
  space!

14
Complex attributes

• Composite and multi-valued attributes can be
  nested in an arbitrary way
• () for nesting
• for multi value
• Example
• AddressPhone (
• Phone(AreaCode, PhoneNumber),
• Address(StreetAddress(Number, Street,
  ApartmentNumber),
• City, State, Zip)
• )

15
Entity Type

• It defines the set of possible entities with the
  same attributes
• For example, employee type is the set of
  employees in the company

16
Key Attributes of an Entity Type

• What makes an entity unique?
• An employee SSN
• A company name
• A project number, name
• A purchase slip may have 2 keys date time
• Keys must be minimal

17
Value Sets

• Possible values of an attribute.
• Usually
• Numeric
• Text
• Boolean
• Etc.
• But can be more specific, for example
• Birth date must be gt 1850 but before lt(now)
• Sex must be either female or male

18
Initial Entity Concept of Company Database
19
Relationship

• Relationship type amongst entities defines a set
  of associations
• For example between an employee and a department
  there is a relationship Works_For.
• Represented as diamond shaped boxes

20
Degree of relationship type

• Binary relationship
• Ternary relationship

21
Binary rerlationship
22
Ternary Relationship
23
Recursive relationships
24
Constraints on relationships

• Cardinality ratios
• 11, the relation Manages
• MN, the relation Works_On
• N1, the relation Works_FOR
• Participation constraints
• For example, an employee must be working for a
  department (null value is impossible)

25
MN Relationship
26
11 Relationship
27
Attribute of a relationship

• Yes a relationship may include additional
  attributes
• For example
• Start managing in the Manages relationship

28
Weak Entity

• Dependants do not have any key on its own
• Why? Because there might be small chance that 2
  employees have the same first name, birth date,
  sex for their son!
• Personally, I do not like weak entities (hard to
  query).

29
The New ER Diagram
30
Summary of ER Diagram Notations
31
Contd