Entity Relationship Modeling - illustrate how relationships between entities are defined and refined.

This lesson cover these areas illustrate how relationships between entities are defined and refined.
To know how relationships are incorporated into the database design process. And the final one, describe how ERD components affect database design and implementation.

An Entity-Relationship Model (ERM)

ER model often used as a tool for communication between database designers and end-user during the analysis phase of database development and this is a top-down approach to data analysis.
The ER model is a detail logical representation of entities, data elements, and relationships.

What is Conceptual Database Design?

Process of describing the data, relationships between the data, and the constraints on the data.
After analysis - Gather all the essential data required and understand how the data are related
The focus is on the data, rather than on the processes.
The output of the conceptual database design is a Conceptual Data Model ( + Data Dictionary)

Gathering Information for Conceptual Data Modeling

Two perspectives

Top-down - Data model is derived from an intimate understanding of the business.
Bottom-up - Data model is derived by reviewing specifications and business documents.

Entity-Relationship (ER) Modeling.

ER Modeling is a top-down approach to database design.
Entity-Relationship (ER) Diagram.
A detailed, logical representation of the entities, associations and data elements for an organization or business
Notation uses three main constructs
- Data entities
- Relationships
- Attributes

To ensure that we get a precise understanding of the nature of the data and how it used by the enterprise, we need to have a model for communication that is non-technical and free of ambiguities, ER Model is such example.

ER Model is a top-down approach to database design that begins by identifying the important data called entities and relationships between the data that must be represented in the data model.

We then add more details such as the information we want to hold about the entities and relationships called attributes and any constraints on the entities, relationships, and attributes.

Entity Type:

A group of objects with the same properties, which are identified by the enterprise as having an independent existence.
A database normally contains many different entity types, such as Staff, Branch, PropertyForRent, and PrivateOwner, etc.

Entity Occurrence:

A uniquely identifiable object of an entity type. The following diagram illustrates the representation of Staff and Branch entity types.

Relationship type:

A set of meaningful associations among entity types.

Relationship Occurrence:

A uniquely identifiable association, which includes one occurrence from each participating entity type.

Entities

Examples of entities:

Person: EMPLOYEE, STUDENT, PATIENT
Place: STORE, WAREHOUSE
Object: MACHINE, PRODUCT, CAR
Event: SALE, REGISTRATION, RENEWAL
Concept: ACCOUNT, COURSE

Guidelines for naming and defining entity types:

An entity type name is a singular noun
An entity type should be descriptive and specific
An entity name should be concise
Event entity types should be named for the result of the event, not the activity or process of the event.

Attributes

Example of entity types and associated attributes:
STUDENT: Student_ID, Student_Name, Home_Address, Phone_Number, Major

Guidelines for naming attributes:

An attribute name is a noun.
An attribute name should be unique
To make an attribute name unique and clear, each attribute name should follow a standard format
Similar attributes of different entity types should use similar but distinguishing names.

Identifier Attributes

Candidate key

Attribute (or combination of attributes) that uniquely identifies each instance of an entity type
Some entities may have more than one candidate key
Ex: A candidate key for EMPLOYEE is Employee_ID, a second is the combination of Employee_Name and Address.
If there is more than one candidate key, need to make a choice.

Identifier

A candidate key that has been selected as the unique identifying characteristic for an entity type

Relational Database Theory

A table is a basic object of the relational model.

Primary Key:

An attribute or group of attributes are used to identify uniquely each record within a table, that is, they uniquely identify each occurrence of the entity type which the table represents.

An example where a group of attributes is used as the primary key.

CONCATENATED PRIMARY KEY.

Where more than one attribute column is needed to established unique identification for records within a table, resulting primary key is referred to as a Concatenated Primary Key.

FOREIGN KEY

A primary key for one table is known as a Foreign Key in another table, embedded for the purposes of identifying relationship occurrences.

Definition: Key attributes are referred to as foreign keys when they are used in any of the ways outside their "native" table

CANDIDATE KEY:

Candidate key is a collection of attributes or attribute that has the same properties as the primary key.

Referential Attributes

Make Reference to another instance in another table

Relationships

Relationship Types:

Relationships are associations or connections between entities.
Relationship between pairs of entity types have a proportion. The proportion of a relationship can be one of the ratios;
One to One ( 1 : 1 )
One to Many ( 1 : m )
Many to One ( m : 1 )
Many to Many ( m : n )

One to One ( 1 : 1 ):

For every occurrence of an entity type on either side of a relationship, there can be just one corresponding occurrence of an entity type on the other.

One to Many ( 1 : m ):

For each occurrence of an entity type on the "one" side of a relationship, there can be many corresponding occurrences of the entity type on the "many" side of the relationship, but that for each occurrence of an entity type on the "many" side of the relationship, there can only be one occurrence of the entity type on the "one" side.

Many to Many ( m : n )

For any occurrence of an entity type on the "m" side of the relationship, there can be many corresponding occurrences of the entity type on the "n" side of the relationship, and for any occurrence of an entity type on the "n" side of the relationship, there can be many occurrences of the entity type on the "m" side of the relationship

E-R Modelling

Degree of Relationships

Degree: number of entity types that participate in a relationship
Three cases

Unary: between two instances of one entity type
Binary: between the instances of two entity types
Ternary: among the instances of three entity types

Is the number of entity types that participate in it.
Thus ‘Completes’ has degree 2, since there are two participating entity types, EMPLOYEE and COURSE
The three most common relationship degrees are unary (degree 1), binary (degree 2) and ternary (degree 3 –see following Fig.)
Higher degree relationships are possible but rarely encountered in practice

Unary relationship

Is between the instances of a single entity type (also called recursive relationships)
‘Is_Married_To’ is a one-to-one relationship between instances of the PERSON entity type
‘Manages’ is a one-to-many relationship between instances of the EMPLOYEE entity type

Entity-Relationship Modeling

Degree of a relationship type:

The number of participating entity types in a relationship.

The degree of a relationship indicates the number of entity types involved in a relationship. A relationship of degree two is called binary.

For example:

Binary Relationships

1:1 relationship

Should be rare in any relational database design

A single entity instance in one entity class is related to a single entity instance in another entity class

Could indicate that two entities actually belong in the same table

The 1:1 Relationship Between PROFESSOR and DEPARTMENT

1:M relationship

Relational modeling ideal
Should be the norm in any relational database design

The 1: M relationship between PAINTER and PAINTING

M:N relationships

Must be avoided because they lead to data redundancies.
Can be implemented by breaking it up to produce a set of 1:M relationships
Can avoid problems inherent to M:N relationship by creating a composite entity or bridge entity
- This will be used to link the tables that were originally related in a M:N relationship
- The composite entity structure includes-as foreign keys-at least the primary keys of the tables that are to be linked.

Binary relationships

Between the instances of two entity types, and is the most common type of relationship encountered in data modelling. e.g. (one-to-one) an EMPLOYEE is assigned one PARKING_PLACE, and each PARKING_PLACE is assigned to one EMPLOYEE
e.g. (one to many) a PRODUCT_LINE may contain many PRODUCTS, and each PRODUCT belongs to only one PRODUCT_LINE
e.g. (many-to-many) a STUDENT may register for more than one COURSE, and each COURSE may have many STUDENTS

Ternary relationships

A ternary relationship is a simultaneous relationship among the instances of 3 entity types
It is the most common relationship encountered in data modelling
The following Fig. shows a typical ternary relationship
Here, vendors can supply various parts to warehouses
The relationship ‘Supplies’ is used to record the specific PARTs supplied by a given VENDOR to a particular WAREHOUSE
There are two attributes on the relationship ‘Supplies’, Shipping_Mode and Unit_Cost
e.g. one instance of ‘Supplies might record that VENDOR X can ship PART C to WAREHOUSE Y, that the Shipping_Mode is ‘next_day_air’ and the Unit_Cost is £5-00 per unit

We do not use diamond symbols on the lines from SUPPLY_SCHEDULE to the three entities, because these lines do not represent binary relationships
It is recommended that all ternary (or higher) relationships are converted into associative entities (as in the Fig.), as it makes the representation of participation constraints (discussed later) easier
Many CASE tools cannot represent ternary relationships, so you must represent the ternary relationship with an associative entity and three binary relationships

Cardinality and Connectivity

Relationships can be classified as either

one – to – one
one – to – many
many – to –many

Cardinality: minimum and a maximum number of instances of Entity B that can (or must be) associated with each instance of entity A.

Connectivity

Mandatory vs. Optional Cardinalities

Specifies whether an instance must exist or can be absent in the relationship

How to Evaluate a Data Model?

A good data model has the following:
Accuracy and completeness
Non redundancy
Enforcement of business rules
Data Reusability
Stability and Flexibility
Communication Effectiveness
Simplicity