Data Modeling Assignment Help | Data Modeling using Entity-Relationship Model

Data Modeling

A plan or blueprint for designing a database.
It provides details as to how one will go about designing a database.
It provides details the relationships between the entities.
Changing a relationship during the data modeling stage is just a matter of changing the diagram and related document. However, changing a relationship after the database is constructed is very difficult.

Data Modeling:

The process of creating a visual representation of a database.
Data modeling allows to transform business requirements to a diagram that is easy to understand.

Entity-Relationship (E-R) Data Model:

Is a detailed logical representation of the data for an organization.
E-R model describes data as entities, relationships, and attributes.
In this model, relationships are created by dividing object of interest into entity and its characteristics into attributes.
Different entities are related using relationships.
Represents the relationships into pictorial form to make it easier for different stakeholders to understand.

The E-R Model represents the following basic concepts:

Entity: is a thing or object in the real world.

Entity is a basic concept for the ER model.
Something that needs to be tracked.
Entities are specific things or objects that are represented in a database.
An entity may be an object with a physical existence (for example, person, house, car, employee, etc.).
Or it may be an object with a conceptual existence (i.e. company, job, salary, etc.).

Entity instance: is a particular occurrence of entity, such as a specific customer, an employee, a salary, etc.
Entity class: a database usually contains groups of entities that are similar and share the same attributes.
A database has many entity classes and each entity class holds data about entity instances.

Attribute: properties that describe the entities (instances, objects, etc.). The characteristics of entities are called attribute.

Entities with the same basic attributes are grouped or typed into an entity type.
For example, an EMPLOYEE entity may have the following characteristics describing the entity: name, age, address, salary, project, social, etc.
Each attribute has a value set (data type) associated with it. E.g. Integer, String, Date, Char, etc.
Each entity instances in a relation must have same attributes. An attribute can have a NULL or empty value if the value of an attribute is missing.

Types of Attributes:

Key attribute: An attribute that has unique value for each of the instances in an entity.
Composite attribute: a key attribute can be composed of several attributes. For example: first name, middle name, and last name together can serve as a composite key.
Below figures show EMPLOYEE entity with attributes. EmployeeNumber is the key attribute.

Surrogate Key attribute: A key attribute that is computer generated. It is an artificial column that is added to a table to serve as the primary key.

In a relation, when none of the attributes are unique and can not be used as primary key, an artificial column is then created that is auto generated by the system.
In situation when primary key is composed of multiple columns or contains lengthy text, a surrogate key is appropriate.
In EMPLOYEE table, EmployeeNumber is a surrogate key as it is auto generated by system.
Another example is RENTAL_PROPERTY, where PropertyID is a surrogate key as no single column can serve as the primary key.

Foreign Key attribute: a column or composite of columns that is the primary key of another table.

Foreign key is used to establish relationship with another table.
For example, the attribute DepartmentName in EMPLOYEE table is a foreign key that establish relationship between the tables, as it is a primary key in the DEPARTMENT table.

E-R Model: Requirement to Relations

Sample COMPANY Database Requirements:

The company is organized into departments. Each department has a unique name, a unique number, and a particular employee who manages the department. We keep track of the start date when that employee began managing the development. A department may have several locations.

A department controls a number of projects, each of which has a unique name, a unique number, and a single location.
The database will store each employee’s name, Social Security number, address, salary, gender, and birth date. An employee is assigned to one department, but may work on several projects, which are not necessarily controlled by the same department. It is required to keep track of the current number of hours per week that an employee works on each project, as well as the direct supervisor of each employee.
The database will keep track of the dependents of each employee for insurance purposes, including each dependent’s first name, sex, birth date, and relationship to the employee.

Based on the requirements, we can identify four entity types in the COMPANY database:

DEPARTMENT (Name, Number, Locations, Manager, Manager_start_date) PROJECT (Name, Number, Location, Controlling_department)

EMPLOYEE (Ssn, Fname, Minit, Lname, Birth_date, Address, Sex, Salary, Departmetn, Project, Hours, Supervisor)

DEPENDENT (Employee, Dependent_name, Birth_date, Sex, Relationship)

E-R model has three concepts:

- Entities

- Attributes (simple, composite, multivalued)

- Relationships

In E-R model, each entity is drawn using a box with all attributes are listed in the box.

While Entity names are always written in all CAPS, attributes are written with the mix letters.

Identifiers: each entity have identifiers that uniquely identifies the entity instance.

For example, EMPLOYEE entity can be identified using EmployeeNumber, SocialSecurityNumber, or EmployeeName, but not using Salary or HireDate.
Identifiers in an entity are same as keys in a relation.
In an entity relationship diagram, identifier is placed on the top in a separate box than rest of the attributes.

Types of Entity: In E-R model, entities are differentiated by type of identifier or key attribute.

- Strong Entity:

Entities with their own key identifier (key attribute).
A strong entity is an entity that represents something that can exist on its own.
For example, BUILDING is a strong entity with its own identifier.
Strong entity is drawn using box with squared (sharped) corner.

- Weak Entity:

Entities that do not have key attribute of their own.
Key attribute or part of the key attribute of a week entity is borrowed from another entity.
Weak entities are also called an ID-dependent entity because its existence depends on the existence of the strong entity.
Weak entity is drawn using box with smooth (curved) corner.
For example, APARTMENT is a weak entity that has part of the identifier borrowed from BUILDING entity.

Relationships: entities can be associated with one another using relationships.

A relationship relates two or more distinct entities with a specific meaning.
Each relationships are given names that describe the nature of the relationships
For example, John (EMPLOYEE) works on ProductX (PROJECT).

In E-R model, the relationships are displayed using a diamond shaped box (in the center) and connected to the participating entities using straight lines.
Note that relationships are not shown with an arrow.
The relationship name should be readable from left to right or top to bottom.

A relationship can involve two or more entity classes.
The number of entity classes in the relationship is called the degree of the relationship.

- Binary relationship (degree 2): relationship with two entities.

- Ternary relationship (degree 3): relationship with three entities.

When transforming E-R diagram to relational database design, relationships of all degrees are decomposed into binary relationships.
For example, Assignment, a ternary relationship, is decomposed into three binary relationships (CLIENT to ARCHITECT, CLIENT to PROJECT, and PROJECT to ARCHITECT)

A relationship can be two types depending on the type of entities involve in the relationship.

Strong relationship: when two strong entities are involved in a relationship, they form a strong relationship. For example, COMPANY and PART below forms strong relationship as both entities are strong with their own key attributes.
Weak relationship: when a relationship involves at least one weak entity, it becomes a weak relationship. For example, the relationship between BUILDING and APARTMENT entities are weak relationship as the APARTMENT entity is considered weak with a composite key attribute that has BuidlingName borrowed from the BUILDING entity.

E-R Model: Cardinality

Cardinality--------

In E-R model, the relationships are classified by count known as cardinality
Cardinality means “count”, and is expressed as a number.
It denotes the total number of relationship count between two entities.
Cardinality ratios help to explain the number of instances in an entity that can participate in a relationship with instance(s) in another entity.
For example, in the relationship below, one employee can belong to only one department but a department can have many employees.
Cardinality is shown inside the diamond box.
It is often referred to as cardinality ratios.
There are two types of cardinality in E-R model:

 Maximum Cardinality

 Minimum Cardinality

Maximum Cardinality – 1:1 Relationship:
- When one instance in an entity can relate to at most one instance in another entity.
- In the E-R model below, one employee can manage (at most) one department and a department can be managed by (at most) one employee. So, it is considered as a 1:1 relationship.

Maximum Cardinality – 1:N Relationship:
- When one instance in an entity can relate to many instances in another entity.
- In the E-R model below, one employee can work for (at most) one department and there can be many employees work for a department. So, it is considered as a N:1 (or 1:N) relationship.
In a one-to-many (1:N) relationship,
- The entity on the one side of the relationship is called the parent entity or just the parent.
- The entity on the many side of the relationship is called the child entity or just the child.
- So, in this example, Department is consider as a parent (as it has 1 cardinality) and employee is consider as a child entity with many (N) cardinality.

Maximum Cardinality – N:M Relationship:
- When many instances in an entity can relate to many instances in another entity. - - In the E-R model below, one employee can work on many projects and a project can have many employee. In other words, many employee can work for many projects. So, it is considered as a N:M relationship.