Conceptual Data Models
• Types of data models
• The ER model
1
Types Of Data Models
1. Object Based Logical Models
- They are used in describing data at the
conceptual and view levels.
- They provide fairly flexible structuring
capabilities and allow data constraints to
be specified explicitly.
Types Of Data Models cont’d
They include:
E - R Model
Object Oriented Model
Binary Model
Semantic Data Models
Info-logical Data Model
Function
Types Of Data Models cont’d
2. Record Based Logical Models
These are models used in describing data at the conceptual
and view levels.
They are used to specify the overall logical structure of the
database and to provide a higher-level description
implementation.
It is hard to understand.
3. Physical Data Models
These are models that are used to describe data at the lowest
level.
They are very few in number and the two widely known ones
are: i. Unifying model ii. Frame memory model
The E- R Model (Entity Relationship)
It is based on a perception over a real world, which consists of
a collection of basic objects called entities and relationships
among this objects.
An entity is an object that is distinguished from other objects
via a specific set of attributes.
E-R Model Basic Concepts
Entity sets
Attributes
Relationship sets
1. Entity sets- An entity is a thing or object in the real world that
is distinguishable from all other objects.
- An entity set is a set of entities of the same type that share the
same properties or attitudes e.g. a set of all persons who are
customers of a bank.
Characteristics of Attributes
2. Attributes-They are descriptive properties or characteristics
possessed by each member of an entity set.
Atomic and Composite attributes –
Atomic attribute: represents a single data value e.g. a
customer name or first name, middle name, last name. 15,
“Daniel", 12/25/2009
Composite attribute:
An attribute composed of many other attribute is called as
composite attribute
- can be decomposed into atomic attributes
"James B. Brown"
Composite Attributes
Decompose into atomic components for:
Student_First_Name
Student_MI
Student_Last_Name
Student
Student_ID Student_Address_Line_1
Student_Name Student_Address_Line_2
Student_Address Student_City
Student_DOB Student_State
Student_Class Student_Country
Student_Postal_Code
Single valued and Multi valued Attribute -
The social security number or ID number can
only have a single value at any instance and
therefore its said to be single valued.
An attribute like dependant name can take
several values ranging from o-n thus it is said to
be multi valued.
Multi-Valued Attributes
An attribute consisting more than one value for a
given entity.
For example, Phone_No (can be more than one
for a given student).
In ER diagram, multivalued attribute is represented
by double oval.
Multi-Valued Attributes cont’d
Student Employee
Student_ID (PK) Employee_ID (PK)
Student_First_Name Employee_First_Name
Student_Last_Name Employee_Last_Name
Student_Address Employee_Address
Student_DOB Employee_DOB
Student_Class Employee_Dependent1
Student_Phone1 Employee_Dependent2
Student_Phone2
Calculated/derived attribute
The value for this type of attribute can be
derived from the values of other related
attributes or entities e.g.
Student age value can be derived from the value
for the date of birth and the current date.
Value that can be derived from other attributes
Student_Age = 22 (DOB = 11/20/1986,
current date is 11/13/2009)
Null Attributes
A null value is used when an entity does not
have a value for an attribute e.g.
dependent name.
NB//During design attributes leads to
columns
Reg_No First_Name DOB
STUDENT
3. Relationship sets
An association between two or more entities is called a
relationship. Ie connects 2 entities
Relationship set: Collection of similar relationships
among two or more entity sets.
It indicates that there is a business relationship between
these Entity Types.
They represent logical links between two or
more entities.
Represented by
(1,1) enr (1,M)
STUDENT ollm UNIVERSITY
ent
Instances of an ER Diagram
Relationship set contains a set (no duplicates!) of
relationships, each relating a set of entities, one
from each of the participating entity sets.
Components are entities, not attribute values.
Works_In
Employee (ssn) Department (did)
12345678 1
14789632 1
56756322 2
... ...
Data Model Relationships
Specify the number of instances of one entity that can be
associated with instances of a related entity
Eg CUSTOMER places ORDER
ORDER is placed by CUSTOMER
EMPLOYEE works on PROJECT
PROJECT has project member EMPLOYEE
Types:
1:1
1:M
M:M
“M” denotes some value greater than 1 whose upper bound
is undetermined
This is called relationship cardinality
cardinality refers to the relationship between two tables.
Relationship can be of four types
Data Model Relationships
Qn: Differentiate between instance and cardinality
1:1 Relationship
i One to one relationship (1:1) - An entity in A is associated with
utmost one entity in B
ii. B is associated with at utmost one entity in A.
DEPARTMENT has MANAGER
Each DEPARTMENT has one and only
one MANAGER
Each MANAGER manages one and only
one DEPARTMENT
1:M Relationship
One to Many relationship (1:M) - An entity in A is
associated with any number of entities in B while an
entity in B can be associated with at most one entity in
A.
CUSTOMER places ORDER
Each CUSTOMER sometimes (95%) place one or
more ORDERs
Each ORDER always is placed by exactly one
CUSTOMER
Many to many (M:N)
Many to many (M:N) - An entity in A is associated
with at least one entity in B and an entity in B can be
associated with a number of entities in A.
INSTRUCTOR teaches COURSE
Each INSTRUCTOR teaches zero, one, or
more COURSEs
Each COURSE is taught by one or more
INSTRUCTORs
Resolving Many-To-Many Relationships
Many-to-many relationships cannot be
used in the data model because they
cannot be represented by the relational
model.
Resolving Many-To-Many Relationships
cont’d
A student can be enrolled in multiple classes
at a time (for example, they may have
three or four classes per semester).
A class can have many students (for
example, there may be 20 students in one
class).
This means a student has many classes, and
a class has many students.
Resolving Many-To-Many Relationships
cont’d
We cant do this, because we cannot have
one column (class ID)storing more than one
value as it would be hard to query.
Resolving Many-To-Many Relationships
cont’d
M:M relationship is resolved by introducing
a joining table or bridging table
So, we have a new table called
class_enrollment, It stores two columns:
one for each of the primary keys from the
other table.
Resolving Many-To-Many Relationships
cont’d
Resolving Many-To-Many Relationships
cont’d
Recursive Relationships
Recursive relationships are also possible,
that is relationships between an entity and
itself.
Supervi
se
Employee
Structured constrains
(Cardinalities)
These are specified for each entity participating in a
relationship and describe the maximum and
minimum number of relationship occurrences in
which an entity occurrence can participate.
Cardinalities state how many times can an entity
instance participate in instances of a given
relationship.
Structured constrains
(Cardinalities)
• Optional relationships are shown by either, use of
a small circle drawn along the line or a dotted line
• Mandatory relationships are shown by use of
either a bar drawn across the line or a continuous
line
ER Model Notation
Represent entities as rectangles
List attributes within the rectangle
Entity UniversityStudent
PK StudentID Primary key
StudentName
Attributes StudentDOB
StudentAge
Weak Entity Set -This is an entity set that does not
have sufficient attributes to form a primary
Strong Entity Set -This is an entity set that has a
primary key..
Specialisation -An entity set may include sub-groupings of
entities that are distinct in some way from other entities in
the set. This is called specialization of the entity set
Aggregation- This is abstraction through which relationship
are treated as higher-level entities e.g. the relationship set
borrower and the entity sets customer and loan can be
treated as a higher set called borrower as a whole.
Primary and Foreign Keys
Primary keys enforce entity integrity by uniquely identifying
entity instances.
The primary key is an attribute or a set of attributes that uniquely
identify a specific instance of an entity.
Foreign keys enforce referential integrity by completing an
association between two entities.
Qn:Differentiate between entity integrity and referential integrity
Candidate Key
This is where an entity has more than one attribute
that can serve as a primary key
Any key or minimum set of keys that could be a
primary key is called a candidate key\
Qn: Define candidate key
Composite Keys
Sometimes it requires more than one attribute
to uniquely identify an entity.
A primary key that made up of more than one
attribute is known as a composite key
Composite Keys cont’d
• Each instance of the entity Work can be uniquely
identified only by a composite key composed of
Employee ID and Project ID
Exercise
Draw an E-R diagram that shows the
hospital environment, theatres, patients
(in and out-patients) doctors, nurses,
wards and ward beds
