Data Models

Dr. Kamal Gulati

Fundamentals of Data Modeling and Database Design

Dr. Kamal Gulati

ERM vs. ERD

• ERM (Entity Relationship Data Model / Entity–Relationship Modeling): is a detailed, Logical representation of the data for an organization or for a business area. ERM is expressed in terms of :• Entities

• Attributes

• Relationships

• ERD (Entity Relationship Diagram): is a graphical representation of a Entity-Relationship Model.

Dr. Kamal Gulati

ERM (Entity Relationship Modeling)• Is a data model for describing the data or

information aspects of a business domain or itsprocess requirements, in an abstract way that lendsitself to ultimately being implemented in adatabase such as a relational database.

• Data Model: A set of concepts to describe the structureof a database and certain constrain that the databaseshouldobey.

Dr. Kamal Gulati

• The main components of ER models are entities (things) and the relationships that can exist among them.

• Entity–Relationship Modeling was developed by Peter Chen and published in a 1976.

Dr. Kamal Gulati

The three schema approach to software engineering uses three levels of ER models that may be developed.

• Conceptual data model

• Logical data model

• Physical data model

Dr. Kamal Gulati

Dr. Kamal Gulati

Conceptual data model

• A conceptual schema or conceptual data model isa map of concepts and their relationships usedfor databases.

• This describes the semantics of an organizationand represents a series of assertions about itsnature.

• Specifically, it describes the things of significance toan organization (entity classes), about which it isinclined to collect information, and characteristicsof (attributes) and associations between pairs ofthose things of significance (relationships).

Dr. Kamal Gulati

CONCEPTUAL DATA MODEL

• This ER model establishes a broad view of what should be included in the model set. Conceptual data models:• Include important entities and the relationship between

them.• Do not specify attributes.• Do not specify primary keys.

• Conceptual ERDs can be used as the foundation for logical data models. They may also be used to form commonality relationships between ER models as a basis for data model integration.

Dr. Kamal Gulati

Logical data model

• Is a type of data model showing a detailed representation of an organization's data, independent of any particular technology, and described in business language.

• A logical data model standardizes people, places, things and the rules, relationships and the events between them.

Dr. Kamal Gulati

LOGICAL DATA MODEL

• This model contains more detail than the conceptual ER model, without regard to how information will be physically implemented in the database. Logical data models:• Include all entities and relationships between them.• Specify attributes for each entity.• Specify primary key for each entity.• Specify foreign keys, which identify the relationship between

different entities.• Involve normalization, which is the process of removing

redundancy in a table so that the table is easier to modify. Normalization typically occurs by dividing an entity table into two or more tables and defining relationships between the tables.

Dr. Kamal Gulati

Physical data model

• A physical data model (or database design) is a representation of a data design which takes into account the facilities and constraints of a given database management system.

• In the lifecycle of a project it typically derives from a logical data model, though it may be reverse-engineered from a given database implementation.

Dr. Kamal Gulati

PHYSICAL DATA MODEL

• The physical data model represents the process of adding information to the database. This model shows all table structures, including column name, column data type, column constraints, primary key, foreign key, and relationships between tables. Physical data models:• Specify all tables and columns.

• Include foreign keys to identify relationships between tables.

• May include denormalization, depending on user requirements.

• May be significantly different from the logical data model.

• Will differ depending on which DBMS (database management system) is used.

Dr. Kamal Gulati

Conceptual Data Model (CDM) Logical Data Model (LDM) Physical Data Model (PDM)

Includes high-level data constructsIncludes entities (tables), attributes

(columns/fields) and relationships (keys)

Includes tables, columns, keys, data types,

validation rules, database triggers, stored

procedures, domains, and access

constraints

Non-technical names, so that executives

and managers at all levels can understand

the data basis of Architectural Description

Uses business names for entities &

attributes

Uses more defined and less generic

specific names for tables and columns,

such as abbreviated column names,

limited by the database management

system (DBMS) and any company

defined standards

Uses general high-level data constructs

from which Architectural Descriptions are

created in non-technical terms

Is independent of technology (platform,

DBMS)

Includes primary keys, also all other keys

and indices for fast data access.

May not be normalizedIs normalized to fourth normal

form (4NF)

May be de-normalized to meet

performance requirements based on the

nature of the database. If the nature of the

database is Online Transaction

Processing (OLTP) or Operational Data

Store (ODS) it is usually not de-

normalized.

De-normalization is common in Data

warehouses.

Dr. Kamal Gulati

Dr. Kamal Gulati

ERD (Entity Relationship Diagram)

• Logical representation of data in an organization.

• Views the entire system as a collection of entitiesrelated to one another.

Dr. Kamal Gulati

Advantages of E-R Model

Dr. Kamal Gulati

Elements of E-R Diagram

• 1: Entity

• 2: Attributes

• 3: Relationships

Dr. Kamal Gulati

Dr. Kamal Gulati

Dr. Kamal Gulati

Entity

• An Entity is a Person, Place, Thing or Event for which data is collected and maintained.

• Entities are represented in ER diagrams by a rectangle and named using singular nouns.

Entity name symbol

Dr. Kamal Gulati

Entity type / Entity class

• A set of entities with same attributes

• Example:

• Student entity class is a set of all students.

• Book entity type is for all Books etc.

Dr. Kamal Gulati

Entity instance / occurrence

• A member of an entity class is known as entity instance .

• Also known as entity occurrence.

Dr. Kamal Gulati

Entity Class

Entity instance

Dr. Kamal Gulati

Entity class

Entity instance

Dr. Kamal Gulati

Weak Entity• A weak entity is an entity that depends on the

existence of another entity.

• In more technical terms it can defined as an entity that cannot be identified by its own attributes.

• It uses a foreign key combined with its attributed to form the primary key.

• An entity like order item is a good example for this. The order item will be meaningless without an order so it depends on the existence of order.

Dr. Kamal Gulati

• Example: Name, address, Class and Email of a students are his attributes.

• Can you define for Employee?

Attribute name

Symbol

Dr. Kamal Gulati

Dr. Kamal Gulati

Attribute Domain:

• A set of possible values for an attribute

• All attributes have domain

• Example :

• The domain for Grade point average (GPA) can be from 0 to 4.

• Similarly, domain for Gender attribute can be Either male or female.

Dr. Kamal Gulati

Types of Attributes

• 1: Simple

• 2: Composite

• 3: Single valued

• 4: Multi-valued

• 5: Derived

Dr. Kamal Gulati

• Cannot be subdivided into smaller components.

PERSON

GENDER

Dr. Kamal Gulati

• Can be divided into smaller components.

EMPLOYEE

ADDRESS

street

city

country

Dr. Kamal Gulati

Single-valued Attributes:Contain single valued value.

Employee

Gender

Multi-valued Attributes:Contain two or more values.

person

namecity hobbies

Dr. Kamal Gulati

Multivalued Attribute

• If an attribute can have more than one value it is called an multivalued attribute.

• It is important to note that this is different to an attribute having its own attributes.

• For example a teacher entity can have multiple subject values.

Dr. Kamal Gulati

Derived Attribute

• An attribute based on another attribute. This is found rarely in ER diagrams.

• For example for a circle the area can be derived from the radius.

Dr. Kamal Gulati

Relationship

• A relationship describes how entities interact. For example, the entity “carpenter” may be related to the entity “table” by the relationship “builds” or “makes”.

• Relationships are represented by diamond shapes and are labeled using verbs.

Dr. Kamal Gulati

The number of entities in a relationship

•Types:

1: Unary relationship

2: Binary relationship

3: Ternary relationship

Dr. Kamal Gulati

Unary Relationship

• It is also called as Recursive Relationship.

• If the same entity participates more than once in a relationship it is known as a recursive relationship.

• For E.g: An employee can be a supervisor and be supervised, so there is a recursive relationship.

Dr. Kamal Gulati

Dr. Kamal Gulati

• Exist between the instances of two entity types.

• Types:

• 1: one-to-one

• 2:one-to-many

• 3:many –to-many

Dr. Kamal Gulati

Dr. Kamal Gulati

Dr. Kamal Gulati

• The maximum number of relationships.*Circle means zero

*Line means………..one

*Crow’s foot symbol means….many

Dr. Kamal Gulati

Relationship

• Cardinality and Ordinality are two other notations used in ER diagrams to further define relationships.

• Cardinality specifies how many instances of an entity relate to one instance of another entity. Cardinality specifies the maximum number of relationships and

• Ordinality specifies the absolute minimum number of relationships.

• For example, a “student” is not to required to “join” an “activity”. While an “activity” should be participated by many “student”.

Dr. Kamal Gulati

Tips on How to Draw ER Diagrams

1. Identify all the relevant entities in a given system and determine the relationships among these entities.

2. An entity should appear only once in a particular diagram.

3. Provide a precise and appropriate name for each entity, attribute, and relationship in the diagram.

4. Remove vague, redundant or unnecessary relationships between entities.

5. Never connect a relationship to another relationship.

6. Make effective use of colors.

Dr. Kamal Gulati

Proprietary ER diagramming tools

• Avolution

• Creately

• ER/Studio

• ERwin

• DeZign for Databases

• LucidChart

• MagicDraw

• MEGA International

• ModelRight

• Navicat Data Modeler

• OmniGraffle

• Oracle Designer

• PowerDesigner

• Prosa Structured Analysis Tool

• Rational Rose

• Software Ideas Modeler

• Sparx Enterprise Architect

• SQLyog

• System Architect

• Toad Data Modeler

• Visual Paradigm

• yEd

• http://creately.com/ER-diagram-software

Dr. Kamal Gulati

Exam database ERD

Dr. Kamal Gulati

Dr. Kamal Gulati

Car Rental System

Dr. Kamal Gulati

Hotel Reservation System

Dr. Kamal Gulati

Dr. Kamal Gulati

Railway Reservation System

Dr. Kamal Gulati

Dr. Kamal Gulati

Benefits of ER diagrams

• ER diagrams constitute a very useful framework for creating and manipulating databases.

• First, ER diagrams are easy to understand and do not require a person to undergo extensive training to be able to work with it efficiently and accurately.

• Second, ER diagrams are readily translatable into relational tables which can be used to quickly build databases. In addition, ER diagrams can directly be used by database developers as the blueprint for implementing data in specific software applications.

• Lastly, ER diagrams may be applied in other contexts such as describing the different relationships and operations within an organization.

Dr. Kamal Gulati

Dr. Kamal Gulati

employee

department

jobs

equipment

suppliers project

Dr. Kamal Gulati

name DOB

Phone no

Project no

Project name

Project cost duration

name Address

Date of last meeting

number

Dr. Kamal Gulati

Dr. Kamal Gulati

(Hope you are able to understand the Fundamentals

of Data Modeling and Database Design)

For More Questions / Queries Feel Free to

Contact me.

Dr. Kamal GulatiAssociate Professor |

University Quality Support Head Mentoring Programme Coordinator &

Exam Superintendent |[Ph. D., M.Sc. (Computer Science), M.C.A., M.B.A]

Professional Certifications:

• Certified Microsoft Innovative Educator• Data Science 101 Certification from Big Data University• R Language 101 Certification from Big Data University • SQL Certification from SOLOLEARN.com • Certified IBM Big Data 101 from Big Data University • R Program & Python Certified from DataCamp• Wiley Certified Big Data Analyst [WCBDA] • Certification on DBMS from IIT Mumbai • Certified Cisco Certified Network Associate [CCNA] • Certified Microsoft Certified Professional [MCP] • Certified Brainbench in (MS Access, MS Project, MySQL 5.7 Administration, Computer

Fundamentals, Advanced Ms. Excel & Windows OS) • Real-time Advertising Fundamentals Certified from RTA Academy

• Worked as Visiting Professor with Stratford University, USA for six months from Jan’2016 toJune’2016.

• Also worked at Bahrain University in Kingdom of Bahrain Sr. I.T. Faculty (Computer ScienceDepartment) for Period of 2 Years.

• Have rich experience in the field of teaching and research in Computer Science and InformationTechnology for almost 15+ years in Academia.

• Having experience of working with both private and public institutions and universities as thelecturer and self-instruction material writer for Information Technology courses.

• Had number of research papers published in national and international journals and conferenceproceedings in IEEE and Scopus Index.

• Also chaired various National and International Conferences of repute and associated withvarious International Journals as Editorial Board Member for International and National,Academic Adviser and Research Paper Reviewer.

• My current area of interest: Big Data Analytics, R Software, Internet & Web Technology, IT ProjectManagement, Decision Support System, Business Analytics, Management Information System,Database Management System, Data Networking, R Software and Advanced Excel with VisualBasic Macros.

• Country Visited: USA, Canada, UAE, Bahrain, Oman (Mostly for Teaching and Research Purpose)

Profile of Dr. Kamal Gulati

Profile Contd….• Technical Program Committee for International Conference on Data, Engineering and Applications 2017

(IDEA-2k17) which would be on October 28-29, 2017 at Bhopal. http://www.ideaconference.in

• Advisory Board Committee Member for International Conference on Energy, Communication, Data Analytics and Soft Computing (ICECDS) which would be on 1-2 August 2017 at SKR Engineering College, Poonamallee, Tamil Nadu, India. http://ecds.org.in

• Advisory Committee Member for International Conference on Innovative Research in Engineering and Science which would be on 16-17 June 2017 at Asian Institute of Technology Conference Center Thailand. http://www.iresconf.org

• Advisory Committee Member for International Conference on Cloud Computing and Internet of everything which held on 10-11 Feb’2016 at Delhi-NCR. http://www.ccioet.org

• Technical Committee member for InCITe-2016 (International Conference on Information Technology) Theme - Internet of Things: Connect Your Worlds, IT Summit, Amity University 2016 which held on 06-07 Oct, 2016. http://www.amity.edu/incite2016

• Technical Speaker for Global perspective on IT business “The Changing Scenario” – Big Data on International Students Conference New Delhi (ISCND) which held on 14-15 Oct, 2016 http://iscnd.com

• Advisory Committee Member for International Conference on Sustainable Computing Techniques in Engineering, Science and Management which has held on 09-10 Sep’2016 at Delhi-NCR. http://www.scesm.org

• Technical Program Committee Member for Program Committee Member for International Conference on Recent Trends IN ICT, Jaipur, India, Aug 18-19, 2017 http://rtict.org

• Program Committee Member for International Conference on Recent Advancement in Computer and Communication Bhopal, India, (IC-RAC-2017) May 26-27, 2017 http://www.icrac.co.in

Profile Contd….• Editorial Board member for the following International Journals:

– International Journal of Computer Science and Innovation http://www.infinitysciences.org

– International Journal of Latest Research in Engineering and Technology http://www.ijlret.com

– International Journal of Latest Trends in Engineering and Technology http://www.ijltet.org

– International Journal of Application orInnovation in Engineering & Management

http://www.ijaiem.org

– International Journal for Management http://www.ijm-apm.com

– The International Journal of Emerging Engineering and Embedded Systems http://www.ijeees.org

– Conference Info http://conferenceinfo.org/tpc.php

• Expert Speaker for Program “Insurance Beyond Doubt” Presented by Oriental

Insurance Co Ltd.

https://www.youtube.com/watch?v=GrvJkN_Zn3Q

BOOK, CHAPTER, and CASE STUDY Published

• Published Book on “A Study of Changing Trends in E-CRM of IndianInsurance Industry” Published by LAP Lambert Academic Publishing, oneof the top researchers and renowned scientists of Germany with ISBN:3330009543, 9783330009547. The Book available at Amazon.com.

• Published Real Case Study on “IoT Security Considerations for HigherEducation” published on Business Cases - RENVOI 2017 BOOK (The CaseCentre, UK) with ISBN: 978-1-4828-8840-9, Page 63-70. The Book availableat the various online website: Amazon, AbeBooks, Chegg, Barnes & Noble.

• Published Chapter on "Role of eWorld in Insurance Innovation" Publishedby Insurance Institute of India (III), 60 Years Diamond Jubilee,Compendium, Nov 2016 – (Magazine) – One of the premium InsuranceInstitute of India.

Contact Details

• Email : drkamalgulati@gmail.com

• Website : http://mybigdataanalytics.com

• LinkedIn : https://in.linkedin.com/in/drkamalgulati

• Phone: +91-9718361928