Module 1 : Entity-Relationship (ER) diagrams

Conceptual Database Design

• Identify the “Universe of Discourse” (UoD). The database to be built will not model everything in the world, but rather some “mini-world” or “Universe of Discourse”.'
• Step 2: Convert the UoD to a data model, which can be captured by a database.

The Entity - Relationship Model

• The Entity-Relationship (ER) model at its core provides a graphical representation of data entities.

It helps:

• Define constraints in the ER model
• Gauge how the system works, how various data within the system is connected
• The ER diagram can also be extended to allow for generalisations and specialisations of data to be stored

Entities

An entity is essentially a physical or conceptual object which has data associated with it.

NOTE: all the diagrams shown are snippets for the Entity Relation Diagram Exmple here

Entity

An entity type provides a format for the data which needs to be recorded to represent a particular entity. The entity type is described by its name and attributes. Represented as a rectangular box

Key Attributes

All entity types have at least one key (or uniqueness) constraint. The key must hold for every possible extension of the entity type (see super/subclasses in later slides). Represented as an underlined attribute.

NOTE: StaffID is underlined!

Simple Attributes

Attributes used to describe an entity

Composite attributes

Composite attributes can be divided into smaller parts which represent simple attributes with independent meaning

Composite Key attribute (Several Attribute Keys)

A composite key attribute is also a unique identifiers for each entity instance.
It is the combination of simple attributes which make up the composite key which must be unique

NOTE: Name is underlined!

Multivalued attributes

Multivalued attributes are shown with double-lined ovals and can have multiple values (e.g., one person can hold multiple degrees)

Derived Attributes

|In some cases, attribute values can be derived from related attribute values (e.g., age can be derived from BirthDate)

Entity Concepts

Value sets of attributes

Value sets specify the set of values that may be assigned to a particular attribute of an entity

• E.g. employeeAge: integers between 21 & 65
• A particular entity may not have an applicable value for an attribute. A null value may be used for representing this.

it is essentially the 'Domain' of the attribute, but often it is not simply an integer domain. We will focus on this later.

Entity Set

The collection of all entities of a particular entity type in the database at any point in time is referred to as an entity set.

• The entity set can be mapped to a table

Relationships

A relationship is an association among two or more entities

• e.g., Paris Lane works on the project FileZilla

A relationship type defines the relationship.

• A relationship type may have descriptive attributes.
• A relationship type may have key attributes

Relationship Concepts

Relationship

In the ER model, relationships are represented using a diamond that is connected to the associated entity types. A relationship type may have descriptive attributes. A relationship type may have key attributes

Relationship Degree

The degree of a relationship type is the number of participating entity types. 2 Entities: Binary relationship. 3 entities: Ternary relationship. n entities: n-ary relationship (3 + entities)

BINARY

TERNARY

Role Name

The role name signifies the role that a participating entity from the entity type plays in each relationship instance. That is to say, it explains what the relationship means.

E.g. LECTURES and PARTICPATES from the Relationship above

Recursive Relationships

Same entity types can participate more than once in the same relationship type under different “roles”.

Relationship Constraints

Cardinality Constraints

A cardinality ratio for a relationship set specifies the number of relationships in the set that an entity can participate in.

Participation constraints

Existence Dependency

Existence dependency indicates whether the existence of an entity depends on its relationship to another entity.

Example: Every Course must be lectured by a lecturer.

Weak Entities

• A weak entity can be identified uniquely only by considering the primary key of another owner entity and its own partial key, which is underlined with a dotted line.
• The relationship type that relates a weak entity to its owner is called the identifying relationship. A weak entity and its identifying relationship are represented with double lines.

• Weak Entity: ENROLMENT
• Identifying relationship: PARTICIPATES
• Owner Entit(ies): COURSE, STUDENT

Superclasses and Subclasses - Enhanced ER Model

In the enhanced ER model, the entity type is called a class

• A class can be a superclass or subclass

Key points to note:

• Attributes of a superclass are inherited by the subclasses
• Subclass can have its own specific attributes and relationships
• Subclasses may be overlapping or disjoint

Example

The superclass captures the data common for a variety of different objects. The STUDENT class captures data such as the name, StudentID and DOB which is important for all kinds of students (see example here).

the subclass entities POSTGRADUATE AND UNDERGRADUATE, which are extensions of the STUDENT class, define properties (attributes) specific to those subclasses.

Specialisation

• Define a number of subclasses of an entity type
• Each subclass contains a subset of entities from the superclass
• A subclass is defined based on more specific distinguishing characteristic on entities of the superclass

Generalisation

• Abstraction is the process of ignoring differences amongst some subclasses and generalise them into a superclass