Entity Relationship Model

An Entity Relationship Model (ER Model) represents relationships by dividing objects of interest into entities and their characteristics into attributes.

ER models represent relationships in pictorial form so that different stakeholders can understand the database structure more easily.

This model is useful for designing a database, which can later be converted into tables in the relational model.

Components of ER Model

The main components of an ER Model include:

1. Entity
2. Attributes
3. Relationship

1. Entity

An Entity is a real-world object having state and behavior.

It can be:

• A person
• A place
• A thing

An entity exists in real life.

It is represented by a rectangle symbol in an ER diagram.

Weak Entity

A Weak Entity is an entity that depends upon another entity.

A weak entity does not contain any key entity of its own.

It is represented by a double rectangle.

Figure: Add Weak Entity diagram here.

2. Attributes

An Attribute is used to describe the property of an entity.

It is represented by an ellipse.

Types of Attributes

Some common types of attributes are:

1. Key Attribute
2. Composite Attribute
3. Multi-Valued Attribute
4. Derived Attribute

1. Key Attribute

A Key Attribute represents the main characteristic of an entity.

It represents the primary key of the entity.

In an ER diagram, a key attribute is represented by an ellipse with its text underlined.

Figure: Add Key Attribute diagram here.

2. Composite Attribute

A Composite Attribute is an attribute that is composed of many other attributes.

For example, an attribute may be divided into several smaller attributes.

Figure: Add Composite Attribute diagram here.

3. Multi-Valued Attribute

A Multi-Valued Attribute can have more than one value.

It is represented by a double ellipse.

Figure: Add Multi-Valued Attribute diagram here.

4. Derived Attribute

A Derived Attribute is an attribute derived from another attribute.

It is represented by a shaded ellipse.

Figure: Add Derived Attribute diagram here.

DB Relationship

The logical association between multiple entities is called a Relationship.

A relationship is represented by a diamond symbol.

Degree of Relationship

The number of entity types that participate in a relationship is called the Degree of Relationship.

Types of Relationship

The main types of relationships are:

1. Unary Relationship
2. Binary Relationship
3. Ternary Relationship
4. N-Degree Relationship

1. Unary Relationship

If an association exists with only one entity, it is called a Unary Relationship.

Example: Add Unary Relationship example/diagram here.

2. Binary Relationship

If an association exists between two entities, it is called a Binary Relationship.

Example: Add Binary Relationship example/diagram here.

3. Ternary Relationship

A Ternary Relationship exists when there is a relationship between three entities.

Example: Add Ternary Relationship example/diagram here.

4. N-Degree Relationship

In an N-Degree Relationship, there is an association among n number of entities.

Example: Add N-Degree Relationship example/diagram here.

Entity Set

An Entity Set is a group of entities that share the same attributes.

Keys in DBMS

Keys are used to manage data more effectively and efficiently inside a database.

Keys are used to uniquely identify a tuple or record.

Some important keys in DBMS are:

1. Primary Key
2. Foreign Key
3. Unique Key
4. Candidate Key
5. Super Key
6. Composite Key

1. Primary Key

A Primary Key is a unique identifier for each record in a table.

It ensures that every row in the table can be uniquely identified.

Important characteristics of a primary key include:

• It uniquely identifies each record.
• It cannot contain NULL values.
• A table can have only one primary key.

2. Foreign Key

A Foreign Key is a column or set of columns in one table that refers to the primary key of another table.

It is used to establish relationships between tables.

Foreign keys help enforce referential integrity.

This means that values in a foreign key column must correspond to values in the primary key column of the referenced table.

3. Unique Key

A Unique Key ensures that all values in a column or set of columns are unique.

Unlike a primary key, a unique key can contain NULL values.

According to the provided notes, only one NULL value is allowed per column.

A table can contain multiple unique keys.

4. Candidate Key

A Candidate Key is a column or set of columns that can uniquely identify a record in a table.

From the available candidate keys:

• One is selected as the primary key.
• The remaining candidate keys become alternate keys.

A candidate key must be:

• Unique
• Minimal

Minimal means that no unnecessary column should be included in the key.

5. Super Key

A Super Key is a set of one or more columns that can collectively identify a row uniquely in a table.

A super key may contain more columns than necessary.

Every primary key is a super key, but not every super key is a primary key.

6. Composite Key

A Composite Key is a primary key made up of multiple columns.

It is used when a single column cannot uniquely identify a record but a combination of multiple columns can.

Composite keys are commonly used in tables representing many-to-many relationships.

Mapping Cardinalities

Mapping Cardinalities in an Entity-Relationship Diagram define the relationships between entities by specifying how many instances of one entity are associated with how many instances of another entity.

There are four main types of mapping cardinalities:

1. One-to-One
2. One-to-Many
3. Many-to-One
4. Many-to-Many

1. One-to-One (1:1)

In a One-to-One Relationship, each instance of one entity is associated with exactly one instance of another entity, and vice versa.

This type of relationship is useful when there is a strict and singular association between two entities.

Representation:

Entity A → One record
Entity B → One related record

Figure: Add One-to-One relationship diagram here.

2. One-to-Many (1)

In a One-to-Many Relationship, one instance of an entity can be associated with zero, one, or many instances of another entity.

However, each instance of the related entity is associated with at most one instance of the first entity.

This is one of the most commonly used relationships in database modeling.

It is suitable when one entity has several related records in another entity.

Representation:

One Entity A → Many Entity B records

Figure: Add One-to-Many relationship diagram here.

3. Many-to-One (N:1)

In a Many-to-One Relationship, many instances of one entity are associated with at most one instance of another entity.

Each instance of the related entity can be associated with zero, one, or many instances of the first entity.

This relationship is essentially the inverse of a one-to-many relationship.

Representation:

Many Entity A records → One Entity B record

Figure: Add Many-to-One relationship diagram here.

4. Many-to-Many (M)

In a Many-to-Many Relationship, many instances of one entity can be associated with many instances of another entity, and vice versa.

This type of relationship generally requires an intermediary table.

The intermediary table is commonly called:

• Junction Table
• Associative Table

The junction table converts the many-to-many relationship into two one-to-many relationships.

Representation:

Many Entity A records ↔ Many Entity B records

Figure: Add Many-to-Many relationship diagram here.