Chapter-3

Data modeling
and 
Data modeling methodologies

1
Data Model
Data Model is a collection of tools or concepts for
describing:
• Data
• Data relationships
• Data semantics
• Data constraints
The main purpose of Data Model is to represent the data in
an understandable way.
Categories of data models include:
• Record-based
• Physical
• Object-based
2
Data Models
• Object-Based Data Models
• Entity-Relationship
• Functional
• Object-Oriented.

• Record-Based Data Models

• Relational Data Model
• Network Data Model
• Hierarchical Data Model.

• Physical Data Models

3
 Hierarchical Data Model
• The simplest data model
• Record type is referred to as node or segment
• The top node is the root node
• Nodes are arranged in a hierarchical structure as sort of
upside-down tree
• A parent node can have more than one child node
• A child node can only have one parent node
• The relationship between parent and child is one-to-
many
• Relation is established by creating physical link between
stored records (each is stored with a predefined access
path to other records) 4
Hierarchical Data Model

5
 Network Data Model
• Allows record types to have more than one parent
unlike hierarchical model
• A network data models sees records as set members
• Each set has an owner and one or more members
• Allow many to many relationship between entities
• Like hierarchical model network model is a collection
of physically linked records.
• Allow member records to have more than one owner

6
Network Data Model

7
Relational Data Model
Properties of Relational Databases:
• Each row of a table is uniquely identified by a PRIMARY KEY.
• Each tuple in a relation must be unique
• Group of columns, that uniquely identifies a row in a table is
called a CANDIDATE KEY
• ENTITY INTEGRITY RULE of the model states that no component
of the primary key may contain a NULL value.
• A column or combination of columns that matches the primary
key of another table is called a FOREIGN KEY. Used to cross-
reference tables.
•The REFERENTIAL INTEGRITY RULE of the model states that, for
every foreign key value in a table there must be a corresponding
primary key value in another table in the database.
• All tables are LOGICAL ENTITIES
8
Relational Data Model

9
Conceptual Modeling
• Conceptual schema is the core of a system
supporting all user views.
• Should be complete and accurate representation of
an organization’s data requirements.

• . Conceptual modelling is process of developing a

model of information use that is independent of
implementation details.
• Result is a conceptual data model.

10
Entity relationship model
• Entity relationship model defines the conceptual
view of database. It works around real world entity
and association among them. At view level, ER
model is considered well for designing databases.
• ER DIAGRAM
ER diagram is a visual or graphical representation of
data that describes how data is related to each other.

11
 Components of ER diagram
1. ENTITY
• An entity can be a person, place, event, or object that is
relevant to a given system. For example, a school
system may include students, teachers, major courses,
subjects, fees, and other items. All entities have some
attributes or properties that give them their identity.
• An entity set is a collection of similar types of entities.
Entity set may contain entities with attribute sharing
similar values. For example, Students set may contain
all the student of a school; likewise Teachers set may
contain all the teachers of school from all faculties.
Entities sets need not to be disjoint.
12
 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.

13
 2. Attributes
• Entities are represented by means of their properties,
called attributes. All attributes have values. For
example, a student entity may have name, class, age as
attributes.
• There exist a domain or range of values that can be
assigned to attributes. For example, a student's name
cannot be a numeric value. It has to be alphabetic. A
student's age cannot be negative, etc.
• TYPES OF ATTRIBUTES:
• Simple attribute: Simple attributes are atomic values, which
cannot be divided further. For example, phone-number is an
atomic value of 10 digits.
• Eg. gender, id, passport number…..

14
• Composite attribute: Composite attributes are made of
more than one simple attribute.
eg. Name, Address.
• Derived attribute: Derived attributes are attributes,
which do not exist physical in the database, but there
values are derived from other attributes presented in the
database. For another example, age can be derived from
data_of_birth.
• Single-valued attribute: Single valued attributes
contain on single value. For example:
Social_Security_Number, Age.
• Multi-value attribute: Multi-value attribute may
contain more than one values. For example, a person can
have more than one phone numbers, email_addresses
etc. 15
ENTITY-SET AND KEYS
• Key is an attribute or collection of attributes that
uniquely identifies an entity among entity set. For
example, roll_number of a student makes her/him
identifiable among students.
• Super Key: Set of attributes (one or more) that
collectively identifies an entity.
• Candidate Key: Minimal super key is called
candidate key that is, supers keys for which no proper
subset are a super key. An entity set may have more
than one candidate key.
• Primary Key: This is one of the candidate key
chosen by the database designer to uniquely identify
the entity. 16
Symbols and notations of ER
diagram

17
…cnt’d
Derived attribute

Connector

18
 Example:
• Problem:
• A company database needs to store information about
employees (identified by ssn, with salary and phone as
attributes), departments (identified by dno, with dname
and budget as attributes), and children of employees
(with name and age as attributes).
• Employees work in departments; each department is
managed by an employee; a child must be identified
uniquely by name when the parent (who is an employee;
assume that only one parent works for the company) is
known. We are not interested in information about a
child once the parent leaves the company.

19
 Schemas and instances and database state
Schemmas
this descibes how data to be structured ,defined at
setup/design time (also called metadata)
Instances
it is the collection of data in the database at a
particular point of time .

20
• Write the schema and Draw an ER diagram that captures this
information.
• Write the statements to create those tables and insert
some sample data.
Solution
• Schemas:
• Employees(ssn,salary,phone)
• Departments(dno,dname,budget)
• Children(name,age)
• Then, we shall design the entities and relationships.
• “Employees work in departments…”
• “…each department is managed by an employee…”
• “…a child must be identified uniquely by name when the
parent (who is an employee; assume that only one parent
works for the company) is known.”
21
Solution

dname
salary
phone dno
ssn budget

Employees Departments
Manages

Dependent
Works_In

Child

name age
some Complications to the E-R
Diagram
• An entity set may stand in some relationship to
itself.
• The links are then labelled with the role that the
entity plays.

Person

Parent Child

Child-of

23
Multiple Relationships Between
Entity Sets

Enroll

Course Student

Tutor for

24
Ternary Relationships

• A customer may has an account at a particular

branch.
Branch

CAB

Customer Account

25
 Enhanced E-R (EER) Models
 
object-oriented extensions to E-R model

• EER is important when we have a relationship between

two entities and the participation is partial between
entity occurrences. In such cases EER is used to reduce
the complexity in participation and relationship
complexity.
• ER diagrams consider entity types to be primitive objects
• EER diagrams allow refinements within the structures of
entity types

26
Generalization

27
EER diagram
notation for an
attribute-
defined
specialization
on JobType.

Chapter 7-28
Specialization
• Sometimes, you want to make a lower level entity
from a higher level entity. However, not every
high-level entity must also be a lower-level entity.

Student

IS A

Grad Student
29
 3. Relationship
• The association among entities is called relationship.
For example, employee entity has relation works_at
with department. Another example is for student who
enrolls in some course. Here, Works_at and Enrolls are
called relationship.
DEGREE OF RELATIONSHIP
• The number of participating entities in a relationship
defines the degree of the relationship.
• Unary = degree 1
• Binary = degree 2
• Ternary = degree 3

30
 Cardinality ratio in relationship
• One-to-one(1:1): one entity from entity set A can
be associated with at most one entity of entity set
B and vice versa.

Example Employee manages department

31
 • One-to-many(1:N): One entity from entity set A
can be associated with more than one entities of
entity set B but from entity set B one entity can be
associated with at most one entity.

Example father have son

32
• Many-to-one(N:1): More than one entities from
entity set A can be associated with at most one
entity of entity set B but one entity from entity set
B can be associated with more than one entity from
entity set A.

33
• Many-to-many(N:N): one entity from A can be
associated with more than one entity from B and
vice versa.

Example employee works on project

34
Participation of an Entity Set in a
Relationship Set
 Total participation (indicated by double line): every
entity in the entity set participates in at least one
relationship in the relationship set
E.g. participation of loan in borrower is total
every loan must have a customer associated to it via
borrower
 Partial participation: some entities may not participate
in any relationship in the relationship set
E.g. participation of customer in borrower is partial
 Database design problems
• If database design is done right, then the development,
deployment and subsequent performance in production
will not give much mistakes.
• A well-designed database 'just works‘ well.
• But some Common Database Design Mistakes are there
like:
1. Poor design/planning 10. Lack of testing
2. Ignoring normalization
3. Poor naming standards
4. Lack of documentation
5. One table to hold all domain values
6. Using identity/guid columns as your only key
7. Not using SQL facilities to protect data integrity
8. Not using stored procedures to access data
9. Trying to build generic objects 36
 Good design practice
• Use well defined and consistent names for tables and columns
• Use singular for table names and Don’t use spaces for table
names. 
• Don’t use unnecessary prefixes or suffixes for table names
• Keep passwords as encrypted for security.
• Choose appropriate data type  and avoid redundancy
commands.
• Provide authentication for database access.
• Use constraints  like primary key and foreign key
• Use proper documentation
• Normalization must be used as required, to optimize the
performance.
• Spend time for database modeling and design as much as
required.  37