ER - Model

E-R Model
The entity-relationship (ER) data model allows us to describe the data involved in a real-world enterprise in terms of objects and their relationships and is widely used to develop an initial database design

Entities

• The two main constructs of the Entity-Relationship model are Entities & Relationships
• An entity is an object in the real world that is distinguishable from other objects
• e.g. Lecturer, Student, Subject, etc.
• A collection of similar entities is called an entity set
• e.g. Lecturers, Students, Subjects, etc.

Attribute

• An entity is described using a set of attributes
• e.g. Name, NIC, etc.
• All entities in an entity set have the same attribute

• Example: name, id, age & salary are attributes in EMPLOYEES entity

• The attributes are said to be composite if they can be split. Eg:- the entity Employee contains attribute 'name' which is a composite one

• A multi-valued attribute of an entity is one which has multiple values.
• Eg:- attribute phone numbers in employee entity.

• Some attributes that can be computed from other attributes are called derived attributes.
• Eg:- age is derived from date of birth & current date

Domain: Each attribute in an entity set has domain; possible values

                e.g. Age attribute of an employee has possible integer values from 18-60

Keys

• A Key is a minimal set of attributes whose values uniquely identify an entity in the set

• A candidate key is a combination of attributes that can be uniquely used to identify a database record
• If a table has one or more candidate keys, one of these candidate keys is selected as the table primary key and the rest are called alternate keys
• Primary key is underlined

• A key formed by combining at least two or more columns is called composite key
• Always, the minimal set of attributes are considered for the key.
• Therefore,
• A key is a minimal set of attributes whose values uniquely identify an entity in the set.

Relationship

• A relationship is an association among two or more entities
• Sanath works for MIS Department
• Thilan works for MIS Department
• A collection of similar relationships is called a relationship set

Graphically,

• A relationship can also have descriptive attributes
• These are used to record information about the relationship
• E.g. SaNa works for MIS Department frm November 2006. In the figure, this is captured by the since in Works_In

• An instance of a relationship set is the set of relationships

• Degree of a relationship is the number of participating entities in the relationship.
• •A relationship of degree two is called a binary relationship (e.g. Works In)
• •A relationship with degree three is called ternary.

• Sometimes, relationships can also have attributes called descriptive attributes that record information about the relationship

• The cardinality ratio for a binary relationship specifies the number of relationship instances that an entity can participate in.
• •There are three types of cardinality ratios for binary relationships. They are one-to-one, one-to-many (many-to-one) and many-to-many.

For example, an employee works in at most one department.

Graphically,

This is a one-to-many relationship

One-to-One relationship

Example: An employee manages at most one department. A department can have only one manager managing it.

Many-to-many relationship

Example: An employee can work on several projects. A project can have many employees working on it.

• Participating constraint specifies whether the existence of an entity depends on its being related to another entity via the relationship type
• For example, if we specify that an employee must always work for a department. Then we say that the relationship “works in” is in total participation from Employee entity to Department entity

• If the relationship is not in total participation, then it is known as in partial.
• For example, from DEPARTMENTS to EMPLOYEES

• Entity types without any key attributes is called weak entity types.
• A weak entity is uniquely identified by considering some of its attributes with the primary key of another entity called the identifying owner.
• The attributes in the weak entity participating in the key are called partial keys
• The owner entity and the weak entity participates in an identifying relationship.
• The cardinality of the identifying relationship is either one-to-one or one-to-many from owner entity to weak entity
• The weak entity must have total participation in the identifying relationship

Graphically (Weak Entity)

• Entities participating in a relationship need not be distinct. Such relationships are called recursive relationships.
• •Each entity in the relationship play a role in the relationship. It is recommended to state the role in recursive relationships.