Week 2. ER Data Model

Week 2. Entity-Relationship Data Model

Overview of DB Design Process

There are two main activities:

• Database design: design the conceptual schema for a database application.
• Applications design: focus on the programs and interfaces that access the db.

Entity-Relationship Model Concepts

Entities, Entity Types, Attributes

Entity-Relationship Model

An Entity-Relationship model has three main concepts:

• Entities: and their types / sets

  Entity

  Entities are specific objects or things in the mini-world that are represented in the database. They may be either physical or non-physical objects.

  For example:

  • an EMPLOYEE
  • the research DEPARTMENT
  • the product PROJECT

  Link to original

• Attributes: simple, composite, single-valued, multi-valued

  Attribute

  Attributes are properties used to describe an entity, a specific entity will have a value for each of its attributes. Each attribute has a value set (data type) associated with it, e.g. integer, string, date, etc.

  Types of attributes:

  • Simple attribute

    Simple attribute: each entity has an atomic value for this attribute. Example: ID

    Link to original

  • Composite attribute

    Composite attribute: the attribute may be composed of several components, it may form a hierarchy where some components are themselves composite. Example: Name(First, Middle, Last)

    Link to original

  • Single-valued attribute

    Single-valued attribute: each entity can have a single value for this attribute. Example: Age attribute for each Person.

    Link to original

  • Multi-valued attribute

    Multi-valued attribute: an entity may have multiple values for this attribute. Example: Current Courses of a STUDENT.

    Link to original

    An attribute may be both composite and multi-valued.

  Link to original

• Relationships: and their types / sets

Link to original

Entity type

Entities with the same basic attributes are grouped or typed into an entity type.

Link to original

Key attribute

An attribute of an entity type for which each entity must always have a unique value is called a key attribute of the entity type. Always underlined in the ER diagram.

The key attribute may be composite and an entity type may have more than one key attribute

Link to original

In ER diagrams, an entity type is displayed in a rectangular box and attributes are displayed in ovals.

• Each attribute is connected to its entity type.
• Components of a composite attribute are connected to the oval representing the composite attribute.
• Key attribute is underlined.
• Multi-valued attributes are displayed in double ovals.

graph {
	layout=neato
	overlap=false
	DEPARTMENT [shape=rectangle]
	DEPARTMENT--Manager,Manager_start_date,Number,Name,Locations
 
	Name [label=<<u>Name</u>>]
	Number [label=<<u>Number</u>>]
	Locations [peripheries=2]
}

Entity Set

Each entity type will have a collection of entities stored in the database, called the entity set. The entity set is the current state of the entities of that type that are stored in the database.

Link to original

Example: Entity Type / Set

For example, given the following entity type CAR:

graph {
	layout=neato
	overlap=false
 
	CAR [shape=rectangle]
	CAR--Year,Model,Make,Color,Vehicle_id,Registration
	Registration--Number,State
 
	Registration [label=<<u>Registration</u>>]
	Vehicle_id [label=<<u>Vehicle_id</u>>]
	Color [peripheries=2]
}

We can have the entity set:

Registration (Number, State)	Vehicle_id	Make	Model	Year	{Color}
(ABC 123, Deez)	AB123	Deez	convertible	2069	(red,blue)
(FRED 4, Cat)	F432	Real	electric	2222	(purple)

Relationships and Relationship Types

Some aspects in requirement will be represented as relationships.

Relationship

A relationship relates two or more distinct entities with a specific meaning. Example: EMPLOYEE John works on the product PROJECT.

Link to original

Relationship type

Relationships of the same type are grouped or typed into a relationship type. Example: WORKS_ON relationship which EMPLOYEEs and PROJECTs are linked.

Relationship types are sets of associations among entities and identify the relationship name and participating entity types. It also identifies relationship constraints.

Link to original

Degree of a relationship type

The degree of a relationship type is the number of participating entity types. Example: MANAGES and WORKS_ON are binary relationships.

Link to original

Relationship set

The relationship set is the current set of relationship instances represented in the database, it is the current state of a relationship type.

Link to original

In ER diagrams, we represent the relationship type as a diamond-shaped box and it is connected to the participating entity types via straight lines.

graph {
	layout=neato;
 
	EMPLOYEE, DEPARTMENT [shape=rectangle]
	WORKS_FOR, MANAGES [shape=diamond]
	
	EMPLOYEE--WORKS_FOR [label=" N" color="white:white"]
	DEPARTMENT--WORKS_FOR [label=" 1" color="white:white"]
	EMPLOYEE--MANAGES [label=" 1"]
	DEPARTMENT--MANAGES [label=" 1" color="white:white"]
}

Recursive Relationship Type

Recursive relationship type

A recursive relationship type is a type with the same participating entity type in distinct roles, e.g. supervisor and supervisee.

Link to original

In an ER diagram, we need to display role names to distinguish participation.

graph {
	nodesep=1.2
 
	EMPLOYEE--SUPERVISION [label=" 1 Supervisor"]
	EMPLOYEE--SUPERVISION [label=" N Supervisee"]
}

Weak Entity Types

Weak entity type

A weak entity type is an entity that does not have a key attribute, it must participate in an identifying relationship type with an owner or identifying entity type.

Entities are identified by the combination of:

• the particular entity they are related to in the identifying entity type

• Partial key

  a partial key: an attribute that can uniquely identify weak entities that are related to the same owner entity

  Link to original

Link to original

Constraints and Attributes in Relationship Types

There are different structural constraints on relationship types (ratio constraints).

• Cardinality Ratio

  Cardinality Ratio: specifies maximum number of relationship instances that an entity can participate in: one-to-one (1:1), one-to-many (1:N), many-to-one (N:1) or many-to-many (M:N).

  Shown on an ER diagram by placing appropriate number on the relationship edges.

  Link to original

• Existence Dependency Constraint

  Existence Dependency Constraint (participation constraint): specifies minimum participation of an entity in a relationship, i.e. if existence of an entity depends on it being related to another entity via relationship type.

  • zero (optional participation, not existence-dependent)
  • one or more (mandatory participation, existence-dependent)

  Existence dependence / total constraint is shown on an ER diagram by a double line, while partial constraint is shown by a single line.

  Link to original

A relationship type may have attributes, for example:

• HoursPerWeek of WORKS_ON

    graph {
    	rankdir=LR
    	WORKS_ON [shape=diamond]
    	WORKS_ON--Hours
    }

Alternative (min, max) notation for relationships

(mix, max) notation for relationships

Structural constraints are specified on each participation of an entity type E in a relationship type R. It specifies that each entity e in E participates in at least min and at most max relationship instances in R.

• By default, with no constraint, min = 0 and max = n (no limit).
• Must have min $\le$ max, min $\ge 0$, and $max\ge 1$.
• Derived from knowledge of mini-world constraints.

Link to original

Examples of (min, max) notation

For example, a department has exactly one manager and an employee that can manage at most one department.

• Specify $(0,1)$ for participation of EMPLOYEE in MANAGES.
• Specify $(1,1)$ for participation of DEPARTMENT in MANAGES.

graph {
	rankdir=LR
	EMPLOYEE--MANAGES [label="(0,1)"]
	MANAGES--DEPARTMENT [label="(1,1)"]
 
	EMPLOYEE, DEPARTMENT [shape=rectangle]
	MANAGES [shape=diamond]
}

Another example is that, 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 $(1,N)$ for participation of DEPARTMENT in WORKS_FOR.

graph {
	rankdir=LR
	EMPLOYEE--WORKS_FOR [label="(1,1)"]
	WORKS_FOR--DEPARTMENT [label="(1,N)"]
 
	EMPLOYEE, DEPARTMENT [shape=rectangle]
	WORKS_FOR [shape=diamond]
}

ER Diagrams - Notation

ER Diagram Notation

Link to original

Example: ER Diagram for COMPANY Schema

The company is organised into DEPARTMENTs. Each department has a name, number, and an employee who manages it. We keep track of the start date of the department manager. A department may have multiple locations.

Each department controls a number of PROJECTs. Each project has a unique name, unique number and is located at a single location.

We store each EMPLOYEE’s social security number, address, salary, gender and birth date. Each employee works for one department but may work on several projects. We keep track of the number of hours per week that an employee currently works on each project. We keep track of the direct supervisor of each employee.

Each employee may have a number of DEPENDENTs. For each, we keep track of their name, sex, birth date and relationship.

Relationships and participating entity types:

• WORKS_FOR (between EMPLOYEE, DEPARTMENT)
• MANAGES (between EMPLOYEE, DEPARTMENT)
• CONTROLS (between DEPARTMENT, PROJECT)
• WORKS_ON (between EMPLOYEE, PROJECT)
• SUPERVISION (between EMPLOYEE (as subordinate), EMPLOYEE (as supervisor))
• DEPENDENTS_OF (between EMPLOYEE, DEPENDENT)

Most of these are binary relationships (of degree 2) apart from SUPERVISION that is unary (of degree 1).

Initial Design of Entity Types

graph {
	layout=neato
	overlap=false
	
	DEPARTMENT [shape=rectangle]
	DEPARTMENT--Manager,Manager_start_date,Number,Name,Locations
 
	Name [label=<<u>Name</u>>]
	Number [label=<<u>Number</u>>]
	Locations [peripheries=2]
}
 
graph {
	layout=neato
	overlap=false
	
	PROJECT [shape=rectangle]
	PROJECT--Name,Location,Number,Controlling_department
 
	Name [label=<<u>Name</u>>]
	Number [label=<<u>Number</u>>]
}
 
graph {
	layout=neato
	overlap=false
	
	EMPLOYEE [shape=rectangle]
	EMPLOYEE--Birth_date,Department,Address,Supervisor,Salary,Sex,SSN,Works_on,Name
	Name--Fname,Minit,Lname
	Works_on--Project,Hours
 
	SSN [label=<<u>Name</u>>]
	Works_on [peripheries=2]
}
 
graph {
	layout=neato
	overlap=false
	
	DEPENDENT [shape=rectangle]
	DEPENDENT--Relationship,Birth_date,Sex,Employee,Dependent_name
}

ER Diagram

ER Diagram using (min, max) notation