SQL Server Mentalist

Hi Folks,

This post is part of Series Database Management Systems

Currently running topic for this series is listed as below :

Series of Database Management Systems

>>Chapter 1 : DBMS [Database Management Systems]

>>Chapter 2 : Database Core Concepts and  Applications

>>Chapter 3 : Record Storage and Primary File Organization

>>Chapter 4 : Index Structures of Files

>>Chapter 5 : Entity-Relationship Model <You are Here>

Continuing from my previous post on this series.

We are going to Cover the Following Points in this article

• Components of an ER Model
• Relationships, Roles and Structural Constraints
• Constraints on Relationships Types

Components of an ER Model

The ER diagram represents three main concepts.

1. Entities: The fundamental item in any ER model is the entity which is a "thing" in the real world with an independent existence that is distinguishable from all other objects.

E.g.: each employee in an organization is an entity. A company, a job, a book etc. all are entities. Each entity has particular properties called attributes that describe it.

E.g.: An employee entity may be described by the employee’s name, age, address, salary etc.

2. Entity Sets: It is a set of entities of the same type that share the same properties or attributes. The set of all employees working for the same department can be defined as the entity set employee, but each entity

has its own values for each attribute.

Different types of Attributes: Each attribute is associated with a set of values called domain.

1. Simple And Composite Attributes:

Simple attributes are not divided into sub parts. They are also called atomic attributes, e.g.: AGE.

Composite attributes can be divided into sub parts with an independent meaning of their own. E.g.: Address attribute can be composed of components like street number, area, city, pin code.

2. Single Valued and Multivalued Attributes:

A single valued attribute is one that holds a single value for a single entity. E.g.: Age, room-number.

Multi valued attribute is one that holds multiple values for a single entity. E.g.: college degree attribute for studies [B.S., M.Sc. Ph.D]

3. Derived Attributes: Is one that represents a value that is derived from the value of a related attribute.

E.g.: The value of an Age can be determined from the current date [todays] and the value of that person’s birthday, the age attribute, is hence called derived attribute and is said to be derivable from the birthday attribute which is called a stored attribute.

4. Null Attribute: A null value attribute is used when an attribute does not have any value. A null value does not mean that the value is equal to zero, but indicates no value is stored for that attribute.

E.g.: Apartment number attribute of an address applies only to address that are in apartment buildings and not in other types of residence such as single-family homes.

E-mail: All employees in an employee database may not have e-mail addresses.

5. Key Attribute: An entity type usually has an attribute whose values are distinct for each individual entity. Such an attribute is called a key attribute. These attributes which uniquely identify every instance of the entity are termed as the primary key.

Value sets or Domain attributes:

Each attribute is associated with a set of values called domain of that attribute; assume that values from a set of permitted values. E.g.: range of ages allowed for employees is between 18 and 58. Domain of the attribute name might be a set of all text strings of a certain length; mathematically an attribute A of entity type E whose value set is V can be defined as a function from E to the power set P(v) or V as A:E— >P(v).

Relationships, Roles and Structural Constraints

Relationships: In the real world, items have relationships to one another. E.g.: A book is published by a particular publisher. The association or relationship that exists between the entities relates data items to each other in a meaningful way. A relationship is an association between entities. A collection of relationships of the same type is called a relationship set.

A relationship type R is a set of associations between E, E2…..En entity types mathematically, R is a set of relationship instances ri.

E.g.: Consider a relationship type WORKS_FOR between two entity types – employee and department, which associates each employee with the department the employee works for. Each relationship instance in WORKS_FOR associates one employee entity and one department entity, where each relationship instance is ri which connects employee and department entities that participate in ri.

Employee el, e3 and e6 work for department d1, e2 and e4 work for d2 and e5 and e7 work for d3. Relationship type R is a set of all relationship instances.

Figure Some instances of the WORKS_FOR relationship

Degree of relationship type: The number of entity sets that participate in a relationship set. A unary relationship exists when an association is maintained with a single entity.

A binary relationship exists when two entities are associated.

A tertiary relationship exists when there are three entities associated.

Figure Degree of relationship type

Role Names and Recursive Relationship

Each entry type to participate in a relationship type plays a particular role in the relationship. The role name signifies the role that a participating entity from the entity type plays in each relationship instance, e.g.: In the WORKS FOR relationship type, the employee plays the role of employee or worker and the department plays the role of department or employer. However in some cases the same entity type participates more than once in a relationship type in different roles. Such relationship types are called recursive.

E.g.: employee entity type participates twice in SUPERVISION once in the role of supervisor and once in the role of supervisee.

Constraints on Relationships Types

Relationship types usually have certain constraints that limit the possible combination of entities that may participate in the relationship instance.

E.g.: If the company has a rule that each employee must work for exactly one department. The two main types of constraints are cardinality ratio and participation constraints.

The cardinality ratio specifies the number of entities to which another entity can be associated through a relationship set.

Mapping cardinalities should be one of the following.

One-to-One: An entity in A is associated with at most one entity in B and vice versa.

Employee can manage only one department and that a department has only one manager.

One-to-Many: An entity in A is associated with any number in B. An entity in B however can be associated with at most one entity in A.

Each department can be related to numerous employees but an employee can be related to only one department

Many-to-One: An entity in A is associated with at most one entity in B. An entity in B however can be associated with any number of entities in A. Many depositors deposit into a single account.

Man-to-Many: An entity in A is associated with any number of entities in B

and an entity in B is associated with any number of entities in A.

An employee can work on several projects and several employees can work on a project.

Participation Roles: There are two ways an entity can participate in a relationship where there are two types of participations.

1. Total: The participation of an entity set E in a relationship set R is said to be total if every entity in E participates in at lest one relationship in R. Every employee must work for a department. The participation of employee in WORK FOR is called total.

Figure Some instances of the WORKS_FOR relationship

Total participation is sometimes called existence dependency

2. Partial: If only some entities in E participate in relationship in R, the participation of entity set E in relationship R is said to be partial.

Figure Some instances of the WORKS_FOR relationship

We do not expect every employee to manage a department, so the participation of employee in MANAGES relationship type is partial.

Weak Entity: Some entity types may not have any key attribute of their own; they are called weak entity types. An entity set that has a primary key is termed as a strong entity type. A weak entity type always has a total participation [existence dependence] with respect to a strong entity.

A weak entity type is dependent on the existence of another entity. Weak entity is also referred to as child, dependent OR subordinate entities, and strong entities as parent, owner OR dominant entities. E.g.: In the following relationship PARENT is a weak entity as it needs the entity EMPLOYEE for its existence. The entities EMPLOYEE, COMPANY etc. are strong entities. Weak entities are represented by a double lined rectangle.

Hope you will like Series of Database Management Systems series !

If you have not yet subscribe this Blog , Please subscribe it from “follow me” tab !

So that you will be updated @ real time and all updated knowledge in your mail daily for free without any RSS subscription OR news reading !!

Happy Learning and Sharing !!

For More information related to BI World visit our all Mentalist networks Blog

SQL Server Mentalist … SQL Learning Blog

Business Intelligence Mentalist … Business Intelligence World

Microsoft Mentalist … MVC,ASP.NET, WCF & LinQ

MSBI Mentalist … MS BI and SQL Server

NMUG Bloggers …Navi Mumbai User Group Blog

Architectural Shack … Architectural implementation and design patterns

DBA Mentalist …Advance SQL Server Blog

MVC Mentalist … MVC Learning Blog

Link Mentalist … Daily Best link @ your email

Infographics Mentalist … Image worth explaining thousand Words

Hadoop Mentalist … Blog on Big Data

BI Tools Analysis … BI Tools

Connect With me on

| Facebook |Twitter | LinkedIn| Google+ | Word Press | RSS | About Me |