Data Modelling

1
Data Modelling

• Which data to include in the database

2
Aim of Data Modelling

• Form a model of the enterprise so that the data
  are accurate and useful.
• Yet another form of abstraction.
• First and most important step in database design.
• Requires a good understanding of enterprise being
  modeled.
• Different methods of data modeling, but the most
  widely used is the Entity-Relationship (E-R)
  model.

3
Entity-Relationship Model

• Primarily used to specify an external view of the
  database.
• Recall that the conceptual schema can be created
  out of the various external views.
• Model the world in terms of
• entities
• relationships between entities
• attributes of entities and relationships.

4
Elements in the E-R Model I

• Entity
• A distinguishable thing, person or event
• Entity set
• A set of entities of the same type
• Relationship
• Representation of the fact that certain entities
  are related to each other in a specific way.
• Relationship set
• Set of relationships of a given type.

5
Elements in the E-R Model II

• Attributes
• Properties of entities or relationships
• Attribute values are drawn from a given domain or
  value set
• Attributes may have a set of values (multi-valued
  or set-valued attributes).
• Key
• An attribute or set of attributes that uniquely
  identifies each entity in an entity set.

6
E-R Diagrams

• E-R models can be visualized in E-R diagrams.
• There are different conventions for drawing E-R
  diagrams. The following is widely used
• Rectangles for entity sets
• Ellipses for attributes
• Diamonds for relationship sets
• Lines to link
• Each component is labelled with its corresponding
  name.

7
Example of an E-R Diagram I

• UWI offers many courses.
• Each course has a unique course number and a
  name.
• Students can enroll in many courses and a course
  can be taken by many students.
• A student has a name and a unique student number.
• Student obtain grades on courses.
• A course is taught by a single instructor.

8
Example of an E-R Diagram II
c num
Course
c name
Enrol
grade
Teaches
Student
Instructor
s num
i num
s_name
i name
9
Constraints I

• In addition to the entity and relationship sets
  and their attributes, during data modelling, one
  also identifies constraints.
• Examples
• A course is taught by a single instructor.
• No student can enroll for more than 4 courses.

10
Constraints II

• Usually, constraints are not captured in the E-R
  diagram and need to be written down.
• Two exceptions
• Mapping cardinalities
• Concerns the number of entities involved in a
  relationship
• Existence dependencies
• Concerns the fact that sometimes the existence of
  an entity depends on the existence of another
  entity.

11
One-to-One Relationships

• Each entity in E1 is related to at most one
  entity in E2 and vice versa.
• Example
• A department has only one head and no person can
  be head of more than one department.

Head Of
Employee
Department
12
One-to-Many Relationships

• An entity in E2 is related to at most one entity
  in E1, but an entity in E1 can be related to many
  entities in E2.
• Example
• A student has one professor as their supervisor
  but a professor may supervise many students.

Super vises
Professor
Student
13
Many-to-many Relationships

• An entity can be related to many entities in the
  other set, and vice versa.
• Example
• A student can be enrolled in many courses, a
  course can have many students enrolled in it.

Enroll
Course
Student
grade
14
Different Conventions

• There are different conventions for mapping
  cardinalities. For example, instead of a 1 on
  the link, one may use an arrow pointing into the
  entity.

15
Existence Dependencies

• An entity x is existence dependent on an entity y
  if the existence of x is dependent on the
  existence of y.
• Y is called the dominant entity x the
  subordinate entity.
• Example In a personnel database, a dependent
  (e.g., child) is existence dependent on the
  employee. If the employee is deleted, so is the
  dependent.

16
Representation of Subordinate Entities

• Subordinate entities are usually represented in a
  double box

Employee
Dependent
17
Some Complicationsto the E-R Diagram

• An entity set may stand in some relationship to
  itself.
• The links are then labelled with the role that
  the entity plays.

Person
Parent
Child
Child-of
18
Multiple Relationships Between Entity Sets
Enroll
Student
Course
Tutor for
19
Ternary Relationships

• A customer may has an account at a particular
  branch.

Branch
CAB
Account
Customer
20
Generalizations

• Inheritance hierarchies

Employee
IS A
Support Staff
Academic Staff
21
Specialization

• Sometimes, you want to make a lower level entity
  from a higher level entity. However, not every
  high-level entity must also be a lower-level
  entity.

Student
IS A
Grad Student
22
Aggregation

• Sometimes, you want to treat a relationship as an
  entity in its own right, e.g., to allow modelling
  of relationships between relationships.
• Example
• A customer is assigned a loan manager for every
  loan that they get.
• Note that the relationship from the login is not
  with the customer alone, or with the loan.

23
Aggregation in an E-R Model
borrows
Loan
Customer
Loan-Manager
Employee
24
Problems in Data Modelling

• Should a real-world concept be modelled as an
  entity set or a relationship set?
• Should a real-world concept be modelled as an
  attribute or an entity set?
• Is the use of a generalization or aggregation
  appropriate?