ER Model Study Notes

Entity-Relationship model (ER model) in software engineering is an abstract way to describe a database. Describing a database usually starts with a relational database, which stores data in tables.

Here are the key components of an ER model:

Relationship: An association among entities

Relationship Set: An association of entity sets

Entity: Anything that exists and can be distinguished/ real world object which can be distinguished from other objects.

Entity Set: A group of similar entities

• All entities in an entity set have the same set of attributes.
• Each entity set has a key.
• Can be mapped to a relation easily

Strong Entity Set: An entity set that has a primary key is termed a strong entity set.

Weak Entity Set: An entity set may not have sufficient attributes to form a primary key. Such an entity set is termed a weak entity set. For a weak entity set to be meaningful, it must be associated with another entity set, called the identifying or owner entity set. Every weak entity must be associated with an identifying entity i.e., the weak entity set is said to be existence dependent on the identifying entity set.

Attribute: Properties that describe an entity or we can say attributes are descriptive properties possessed by each member of an entity set and each attribute has a domain.

There are many types of attributes

Composite Attribute: Attributes which can have component attribute. e.g., a composite attribute name, with component attributes First_Name, Middle_Name and Last_Name.

Derived Attribute: The value for this type of attribute can be derived from the values of other related attributes or entities. For instance, let us say that the customer entity set has an attribute Loans_Held, which represents how many loans a customer has from the bank. We can derive the value for this attribute by counting the number of loan entities associated with that customer.

Descriptive Attribute: If a relationship set has also some attributes associated with it, then we link these attributes to that relationship set. e.g., consider a relationship set depositor with entity sets customer and account. We could associate the attribute Access_Date to that relationship to specify the most recent date on which a customer accessed an account.

Single Valued Attribute: Attribute which has only one value, e.g., the Employee_Number attribute for a specific Employee_entity refers to only one employee number.

Multi Valued Attribute: Attributes which can have 0, 1 or more than 1 values. An employee entity set with the attribute Phone_Number. An employee may have zero, one or several phone numbers and different employees may have different numbers of phones.

Prime Attributes: Attributes which are part of some candidate key.

Non-Prime Attributes: Attributes which are not part of any candidate key.

Notations/Shapes in ER Modeling

Mapping Cardinalities / Cardinality Ratio / Types of Relationship: Expresses the number of entities to which another entity can be associated via a relationship set. For a binary relationship set R between entity sets A and B, the mapping cardinality must be one of the following:

• One to One: An entity in A is associated with at most one entity in B and an entity in B is associated with at most one entity in A.
• One to Many: An entity in A is associated with any number (zero or more) of entities; in B. An entity in B, however, can be associated with at most one entity in A.
• Many to Many: An entity in A is associated with any number (zero or more) c entities in B and an entity B is associated with any number (zero or more) of entities in A.

Specialization: Consider an entity set person with attributes name, street and city, A person may be further classified-as one of the following: Customer, and Employee. Each of these person types is described by a set of attributes 1 at includes all the attributes of entity set person plus possibly additional attributes. The process of designating subgroupings within an entity set is called specialization.

The specialization of person allows us to distinguish among persons according to whether they are employees or customers,

The refinement from an initial entity set into successive levels of entity subgroupings represents a top-down design process in which distinctions are made explicitly.

Formulas for GATE Computer Science Engineering – Databases

Generalization: Basically generalization is a simple inversion of specialization. Some common attributes of multiple entity sets are chosen to create higher level entity set. If the customer entity set and the employee entity set are having several attributes in common, then this commonality can be expressed by generalization.

Here, person is the higher level entity set and customer and employee are lower level entity sets. Higher and lower level entity sets also may be designated by- the terms super class and subclass, respectively.

Aggregation: Aggregation is used when we have to model a relationship involving entity set and a relationship set. Aggregation is an abstraction through which relationships are treated as higher level entities.

Integrity Constraints: Necessary conditions to be satisfied by the data values in the relational instances so that the set of data values constitute a meaningful database.

There are four types of integrity constraints

Domain Constraint: The value of attribute must be within the domain.

Key Constraint: Every relation must have a primary key.

Entity Integrity Constraint: Primary key of a relation should not contain NULL values.

Referential Integrity Constraint: In relational model, two relations are related to each other over the basis of attributes. Every value of referencing attributes must be NULL or be available in the referenced attribute.