Data Model

• Provides a way to describe the design of a DB at logical level.
• Underlying the structure of the DB is the Data Model
  • A collection of conceptual tools for describing data, data relationships, data semantics & consistency constraints.
  • E.g: ER model, Relational Model, object-oriented model, object-relational data model etc.
• Collection of conceptual tools for describing data, data relationships, data semantics, and consistency

ER-Model and ER-Diagram

• Entity-Relationship Data Model
• It is a high level data model based on a perception of a real world that consists of a collection of basic objects, called entities and of relationships among these objects.
• Graphical representation of ER Model is ER diagram, which acts as a blueprint of DB.

Entity

• An Entity is a thing or object in the real world that is distinguishable from all other objects.
• In DBMS, each row inside this table is an entity
• Entity can be uniquely identified. (By a primary attribute, aka Primary Key)
• Entity example
  • BankAccount
  • Student, Customers
• Strong Entity
  • Can be uniquely identified.
• Weak Entity
  • Can’t be uniquely identified., depends on some other strong entity.

Entity set

• In DBMS, the whole table in the tabular representation of data is an entity set
• It is a set of entities of the same type that share the same properties, or attributes.
• For example:
  • Student is an entity set.
  • Customer of a bank

Attribute

• Each entry in the row is an Attribute
• An entity is represented by a set of attributes.
• There are some attributes that can uniquely represent a row/entity in the given table is/are called primary attribute and primary key*
• For example: Student Entity has the following attributes
  • Student_ID, Name, Standard
  • Course, Batch

Types of Attributes

• Simple and Key attribute
  • Attributes which can’t be divided further
  • E.g., Customer’s account number in a bank, Student’s Roll number etc.
  • Key attribute
    • Some simple attribute can uniquely represent a row/entity are called as key-attribute and primary key
• Composite
  1. Can be divided into subparts (that is, other attributes).
  2. E.g., Person Address, can be divided street, city, state, PIN
  3. If user wants to refer to an entire attribute or to only a component of the attribute.
• Multi-valued
  1. Attribute having more than one value
  2. E.g., phone-number, nominee-name on some insurance, dependent-name etc.
  3. Limit constraint may be applied, upper or lower limits.
• Derived
  1. Value of this type of attribute can be derived from the value of other related attributes
  • e.g., Age, loan-age, membership-period etc.
• NULL Value
  1. An attribute takes a null value when an entity does not have a value for it.
  2. It may indicate not applicable, value doesn’t exist. e.g., person having no middle-name
  3. It may indicate unknown
     1. Unknown can indicate missing entry, e.g., name value of a customer is NULL, means it is missing as name must have some value.
     2. Not known, salary attribute value of an employee is null, means it is not known yet.

Relationships

• Association among two or more entities
  • E.g., Person has vehicle, Parent has Child, Customer borrow loan etc.
• Strong Relationship, between two independent entities
• Weak Relationship, between weak entity and strong entity.
  1. e.g., Loan <instalment-payments> Payment.

Degree of Relationship

• Number of entities participating in a relationship.
• Unary, Only one entity participates
  • E.g., Employee manages employee.
• Binary, two entities participates.
  • E.g., Student takes Course.
• Ternary relationship, three entities participates.
  • E.g, Employee works-on branch, employee works-on job.
• Binary are common.

Relationships Constraints

1. Mapping Cardinality / Cardinality Ratio
   • Number of entities to which another entity can be associated via a relationship.
   • One to one, Entity in A associates with at most one entity in B, where A & B are entity sets. And an entity of B is associated with at most one entity of A.
     • E.g., Citizen has Aadhar Card
   • One to many, Entity in A associated with N entity in B. While entity in B is associated with at most one entity in A.
     • E.g., Citizen has Vehicle
   • Many to one, Entity in A associated with at most one entity in B. While entity in B can be associated with N entity in A.
     • E.g., Course taken by Professor
   • Many to many, Entity in A associated with N entity in B. While entity in B also associated with N entity in A.
     1. Customer buys product
     2. Student attend course.
2. Participation Constraints
   • Aka, Minimum cardinality constraint.
   • Types, Partial & Total Participation.
   • Partial Participation, not all entities are involved in the relationship instance.
   • Total Participation, each entity must be involved in at least one relationship instance.
   • E.g.,
     • Customer borrow loan, loan has total participation as it can’t exist without customer entity.
     • And customer has partial participation
   • Weak entity has total participation constraint, but strong may not have total.

Extended/Enhanced ER Features

• Basic ER Features can be used to model most DB features but when complexity increases, it is better to use Extended ER features to model the DB Schema.

Specialisation

• Specialisation is splitting up the entity set into further sub entity sets on the basis of their functionalities, specialities and features.
• It is a Top-Down approach
• E.g., Person entity set can be divided into customer, student, employee.
  • Person is superclass and other specialised entity sets are subclasses
• We have is-a relationship between superclass and subclass.
• Why Specialisation?
  1. Certain attributes may only be applicable to a few entities of the parent entity set.
  2. DB designer can show the distinctive features of the sub entities.
  3. To group such entities we apply Specialisation, to overall refine the DB blueprint.

Generalisation

• It is just a reverse of Specialisation
• It is a Bottom-up approach
• DB Designer, may encounter certain properties of two entities are overlapping.
  • Designer may consider to make a new generalised entity set.
  • That generalised entity set will be a super class.
• E.g., Car, Jeep and Bus all have some common attributes, to avoid data repetition for the common attributes.
  • DB designer may consider to Generalise to a new entity set Vehicle
• Why Generalisation?
  • Makes DB more refined and simpler.
  • Common attributes are not repeated.

Attribute Inheritance

• Both Specialisation and Generalisation, has attribute inheritance
• The attributes of higher level entity sets are inherited by lower level entity sets.
• E.g., Customer & Employee inherit the attributes of Person.

Participation Inheritance

• If a parent entity set participates in a relationship then its child entity sets will also participate in that relationship

Aggregation

• In aggregation, the relation between two entities is treated as a single entity
• In aggregation, relationship with its corresponding entities is aggregated into a higher level entity.
• For example
  • Center entity offers the Course entity
  • In the real world, if a visitor visits a coaching center then he will never enquiry about the Course only or just about the Center instead he will ask the enquiry about both

ER Diagram Example

Steps to make ER Diagram

1. Identify Entity sets
2. Identify attributes & their types
3. Identify Relationship & Constrain(mapping, participation)

Banking ER Design

Facebook ER Design