Fundamentals of Database system

Chapter Three:
Data Modelling
otline
 The high-level conceptual model
 Entity – Relationship (E/R) Data Model
 Entities, Attributes, and Keys
 Relationships, Associations, and Constraints
 The ER Diagrams
 Enhanced Entity Relationship (EER) Model
 Inheritance
 Aggregation and Association
 Mapping ER-models to relational tables
High level Data Models for Database Design

 High-level conceptual data models provide concepts

for presenting data in ways that are close to the way
people perceive data.
 Data modeling reduces complexities of database design.
 A typical example is the entity relationship model, which
uses main concepts like entities, attributes and
relationships.
 The two common conceptual models for Relational
Database system are
– Entity – Relationship (E/R) model
– Object-Oriented Data Language (ODL) model
Entity – Relationship (E/R) Data Model

 The E/R data model views the real world as a

set of
– Basic objects (Entities), and
– Relationships among these objects.
 The three basic notions of the E/R model are:
– Entity
– Relationship
– Attribute
Cont…
 Entities are usually recognizable concepts, either
concrete or abstract, such as:
– person, places, things, or events which have
relevance to the database.
 An Entity Set is then a set consisting of the same
type of entities that share same properties.
 In the requirement analysis the entities are
recognized by nouns and noun phrases.
Cont…

 Entities can be classified as:

– Strong (independent) entity
 One that does not rely on other entities for identification.
– Weak (dependent) entity
 one that relies on other entities for identification.
 Instance
– Instance also known as Object is an individual
occurrence of an entity set.
Attributes

 Attributes
are descriptive properties that are
associated with an entity.
 Value
– Value is a particular instance of an attribute.
 Domain
– The domain of an attribute is the collection of all
possible values an attribute can have.
 Classification of Attributes:
1. Identifier Vs Descriptor

 Identifiers
– more commonly called keys, uniquely identify an
instance of an entity.
 Descriptor
– describes a non-unique characteristic of an entity
instance.
 Example
– furniture Id
– catalog number, model type, color, …
8
 … Classification of Attributes:
2. Simple Vs Composite
 Simple Attributes
– also known as Atomic Attributes that can not be divided
into subparts mainly of primitive types.
 Composite Attributes
– are composed of smaller subparts that can be further
subdivided into the subparts (Attributes).
 Example
– Id, type, price, color
– name, address

9
 … Classification of Attributes
3. Single-valued Vs Multi-valued

 Single-valued Attributes
– are attributes having only one possible value at any
time.
 Multi-valued Attributes
– are attributes that are having possibly more than one
value.
 Example
– Id, name, type, price
– address
10
 … Classification of Attributes:
4. Derived Vs Stored

 Derived Attributes
– are attributes that can be calculated from the related
stored attributes, entities or general states.
 Stored Attributes
– are attributes that can not be calculated in any way
from the stored attributes.
 Example
– Age
– Birth Date

11
 Relationship Sets

 A Relationship represents an association between

two or more entities.
 Example
– FURNITURE is produced by an EMPLOYEE from
RESOURCE
 FURNITURE is produced by EMPLOYEE
 FURNITURE is produced from RESOURCE
 SALE of FURNITURE is made with RECEIPT

12
 … Relationship Sets
 A Relationship Set is then a set consisting same types of relationships. A
relationship should be named by a Verb or phrase which explains its
function (role name)
 Classification terms in Relationships
1. Degree
– number of entities associated with the relationship
 Binary – links two entity sets; set of ordered pairs (most common)
E.g. STUDENT and DORMITORY || STUDENT and COURSE
– Ternary – links three entity sets; ordered triples (rare). If a
relationship exists among the three entities, all three must be present.
E.g. INSTRUCTOR ,STUDENTS and a particular COURSE
 N-ary – links n entity sets; ordered n-tuples (extremely rare). If a
relationship exists among the entities, then all must be present.
E.g. INSTRUCTOR ,STUDENTS, rooms and a particular
13
COURSE
… Relationship Sets
 Cardinality
– the actual number of related occurrences for each of the two entities
– is the number of entity instances to which another entity set
can map under the relationship.
 One-to-one: X-Y is 1:1 when each entity in X is associated
with at most one entity in Y, and each entity in Y is
associated with at most one entity in X.
 One-to-many: X-Y is 1:M when each entity in X can be
associated with many entities in Y, but each entity in Y is
associated with at most one entity in X.
 Many-to-many: X:Y is M:M if each entity in X can be
associated with many entities in Y, and each entity in Y is
associated with many entities in X ("many" =>one or more
and sometimes zero)
 Cont…

 Existence
– denotes whether the existence of an entity instance is dependent
upon the existence of another, related, entity instance
Mandatory: The relationship child and parent is
mandatory because the child cannot exist without a
parent. Building and apartment
 Optional:it is not necessary for every entity occurrence to
participate in the relationship (0:*)
 … Relationship Sets
– Cardinality

16
 Entity-Relationship E/R Diagram
 Basic Building Blocks of E/R Diagram are entity, attributes, relationship
 Rectangles - for the Entity sets,
– Double border Rectangles - for the weak entity sets.
 Ellipses - for the Attributes,
– Double border Ellipses - for the multi-valued attributes.
– Dashed border Ellipses - for the derived attributes.
– Composite attributes are represented by linked ellipses.
 Diamonds - for the Relationships, and
 Lines - for the links between the attributes and the entity sets and
between the entity sets and the relationships.
– Arrow Head Line - for the link between an entity set with many-to-one
relationship.
17  The arrow is headed to the one side entity set.
 Design Issues
 Faithfulness
– classes or entity sets and their attributes should reflect reality
 Avoiding Redundancy
– be careful to say everything only once
 Simplicity
– avoid introducing more elements into your design than are absolutely
necessary
 Picking the Right kind of Element
– Entity Set versus Attributes
– Entity versus Relationship Sets
19 – Binary versus n-ary Relationship Sets
 Remarks on Designing

 Choose meaningful naming for the entities,

attributes and relationships.
 Use short links.
 Cluster diagram if it has too many entities and
relationships.

20
 Keys
 Keys are attributes or set of attributes that can be used to
uniquely identify an entity within the entity set
 Super Key also known as Super Set is then a set of one or more
attributes that can identify an entity uniquely from the entity
set.
– If K is a super set (super key) then a set consisting of K is
also a super set.
 Candidate Key is the minimal super set. a Group of columns,
that uniquely identifies a row in a table.
– If the Candidate Key is selected as a key for an entity set then it said to
Primary Key.
– A candidate key has the properties of Uniqueness or Irreducibility
21
Relational Constraints/Integrity Rules
and keys:
 Each row of a table is uniquely identified by a PRIMARY KEY
 A primary key in E/R model is represented by underlining the attribute or
set of attributes.
 A column or combination of columns that matches the primary key of
another table is called a FOREIGN KEY which is used to cross-reference
tables.
 ENTITY INTEGRITY RULE of the model states that no component of the
primary key may contain a NULL value.
 REFERENTIAL INTEGRITY RULE : for every foreign key value in a
table there must be a corresponding primary key value in another table in
the database.
 Domain constraints/ INTEGRITY
– are requirements on an attribute value to be in a specified range of values.
 Example on Requirement Analysis
 The problem is to design a database system for “X
Household and Office Furniture P.L.C” based on the
following information.
– The information are kept on daily basis about what the
employees do, what resource they use, which furniture are
made out of the resource, and all the sale and order records.
– Furniture is produced by an employee from resource (s).
– Every furniture is described by the furniture Id, catalog no,
model type, color, production start date, production complete
date and price; and every resource is described by stock no,
type, current cost, and avail quantity.
– A sale of furniture is made with a receipt for both regular
23 customers and buyers.
 … Example on Requirement
Analysis
 Regular customers are described by customer Id, name,
address and contact person; where as, no detail record is
established for buyers.
 Orders for furniture are allowed only for regular customers and
in ordering a furniture, the customer may pay part of the total
price and left in debt, then pay the debt in some other time.
 For the payment transactions three types of receipt are used;
one for sale, one for order and one for due payment (debt).
 A single receipt of each type is prepared only for one buyer or
customer but a number of furniture can be sold or ordered by
one receipt.
 Every receipt is prepared by Sales and it has receipt number,
24 prepared date, total price, and tax.
 Draw ER diagram for the previous requirement
analysis?
1. Identify the entities
2. Obtain the attributes of the entity and its type
3. Identify the relation ship between the entities
• Entity : • Relationship • Attributes
• Furniture • Order • Furniture :fid,---
• Resources • Produced • employees :eid--
• employees • sold • Sales :id
• Sales
• customers • customers
• receipt • receipt
… Example
Example:
Exercise

 Requirements of the university (oversimplified for

illustrative purposes)
– In a University database we might have entities for
Students, Modules and Lecturers.
– Students might have attributes such as their ID, Name, and
Course whereas Lecturers might have attributes such as
LID, Name, Rank and Modules might have attributes such
as ModuleNumber, ModuleName, CrHr and could have
relationships (studies) with Student and Modules and
(tutor) with Lecturers and Modules.
 Identify entity, Attributes and relationship?
 Draw the ER Diagram from the above requirement analysis?
 Enhanced E/R Modeling

 Week Entity Set

 Specialization and Generalization
 Aggregation

29
 Week Entity Set

 weak entity is uniquely identified with the help of

other entity
 The strong entity which contributes its primary
key is called the identifying or owner entity set.
 Criteria for weak entity set
– A one-to-many relationship set that relates the weak
entity set with owner entity set known as identifying
relationship or supporting relationship must exist.
– Total participation in the identifying relationship is
30 required.
 … Week Entity Set
 An attribute or set attributes with in the weak
entity set referred to as discriminator is used
to distinguish weak entities.
 Key of a weak entity set:
– Zero or more of its own attribute;
discriminator
– Key attributes of the owner (identifying)
entity set.

31
 … Week Entity Set
 Notations
– Weak Entity sets are represented by
 Double boarder Rectangles.
– The identifying many-to-one relationship is
represented by
 Double border Diamonds.
– If the entity set has a discriminator then it is
represented by
 Underlining the attribute(s)

32
 … Example (Weak entity sets)

– Each employee may have a number of

Dependents. Name Descr
Eid Name
Descr

Employee Depenedent on DEPENDANT

 For each dependent, we keep track of their name, sex, birth

date, and relationship to the employee.

33
 Specialization and Generalization
 Specialization
– top-down process in grouping of entities that are similar in some
way and distinct in some other ways in which distinctions are
made explicitly.
– is the process of classifying a class of objects into more
specialized subclasses.
 Generalization
– bottom-up approach in which multiple entity sets are synthesized
into a higher level entity set based on their common features.
– Generalization is the inverse process of generalizing several
classes into a higher-level abstract class that includes the objects
in all these classes.
34  The relationship between a subclass and its superclass an IS-A-
 … Example (Specialization &
Generalization)

EMPLOYEES

ISA

FULL-TIME PART-TIME
EMPLOYEES EMPLOYEES

35
 Constraints on Specialization and Generalization

 Condition-defined vs. User-defined Lower-level Entity Sets

– Condition defined: a members of each subclass by placing a condition
on the value of some attribute of the superclass. Such subclasses are
called predicate-defined (or condition-defined) subclasses. For
example, if the EMPLOYEE entity type has an attribute Job_type
specify the condition of membership in the SECRETARY subclass by the
condition (Job_type = ‘Secretary’), which we call the defining predicate
of the subclass. It satisfying an explicit condition or predicate
– Eg2: person entity can specializize into young citizen and senior citizen
by age condition.
– User-defined: determined upon the entry of the entities. do not have a
condition for determining membership in a subclass, the subclass is
called user-defined. a subclass is determined by the database users when
36 they apply the operation to add an entity to the subclass;
Cont…
 Disjoint vs. Overlapping Specialization
– Disjoint: which specifies that the subclasses of the specialization
must be disjoint. This means that an entity can be a member of at most
one of the subclasses of the specialization. Eg: Account users can
participate in saving account and current account but both are different
so, it can be participated one at a time
– Overlapping
 allows for a higher-level entity to belong to more than one entity.
Eg: Two or more instances of the super class are participating in
two or more sub classes then it is called overlapping constraints.
 Example: A person who knows Java and PHP can participate in
both teams.
 Constraints on Specialization and
Generalization

 Total vs. Partial Specialization or Generalization:

– Total: Higher level entity must belong to one of the lower-level
entity. A total specialization constraint specifies that every entity
in the superclass must be a member of at least one subclass in the
specialization. For example, if every EMPLOYEE must be either
an HOURLY_EMPLOYEE or a SALARIED_EMPLOYEE,
– Partial: A single line is used to display a partial specialization,
which allows an entity not to belong to any of the subclasses. For
example, if some EMPLOYEE entities do not belong to any of the
subclasses {SECRETARY, ENGINEER, TECHNICIAN}

38
 Aggregation
 E/R modeling allows relationships only between
entity sets.
 A relationship between a relationship and an
entity set or a collection of entity sets?
– Aggregation is an abstraction through which collection
of related entity sets and relationships are treated as
high-level entities.
– It allows indicating for a relationship set (identified
through a box) to participate in another relationship set.

39
 … Example (Aggregation)

FURNITUR Producti RESOURCE

E on

Manage
s

EMPLOYEES

40
Mapping ER-model into Relational Data Model )

 Relational Data Model is an implementation (representational)

model is an approach in a database design towards the
Relational Database Management System (RDBMS).
 The main construct for representing data in the relational
database is a two-dimensional table called a relation.
Example - “EMPLOYEES” relation
Employees
EmpId Name BDate Sub City Kebele Phone
E001 Alemu Girma 01/10/70 Bole 06 011-663-0712
E004 Kelem Belete 12/04/68 Gulele 03 011-227-2525
 The columns in the table are representing the attributes of the
relationship, and the rows (other than the heading row)
represent tuples (records) of the relation.
 A relation in a relational model consists of:
– The Relation schema: - that describes the column heads for the table and
– The Relation instance: - that is the table with the set of tuples.
The set of relation schema forms schema for the relational database called
database schema (relational database schema).
 In relational model the relation schema are described first. And
the schema specifies: The relation's name, Name for each
attribute (field or column), Domain of each attribute:
– Example
- Employees (EmpId:sting, Name:string, BDate:date, SubCity:string,
Kebel:integer, Phone:string)
- Projects (PrjId:integer, Name:string, SDate:date, DDate:date, CDate:date)
- Teams (Name:string, Descr:string).
 Properties of Relations
– Rows (tuples) in a single relation are unique (that is;
no two tuples are identical).
– Relations are set of tuples not lists (that is; order of
tuples in a relation is immaterial).
– Attributes are atomic
ER model to relational model Mapping

 ER model to relational Mapping Algorithm

7-Step Process:
1. Map Regular/strong Entity Types
2. Map Weak Entity Types
3. Map Binary 1:1 Relation Types
4. Map Binary 1:N Relationship Types.
5. Map Binary M:N Relationship Types.
6. Map Multivalued attributes.
7. Map N-ary Relationship Types.
ER Model
1. Map Regular/strong Entity Types

 For each regular (strong) entity type E in the ER schema,

create a relation R that includes all the simple attributes of E.
 Choose one of the key attributes of E as the primary key for R.
 If the chosen key of E is composite, the set of simple attributes
that form it will together form the primary key of R.
2. Map Weak Entity Types

 For each weak entity type W in the ER schema with owner

entity type E, create a relation R & include all simple
attributes (or simple components of composite attributes) of
W as attributes of R.
 Also, include as foreign key attributes of R the primary key
attribute(s) of the relation(s) that correspond to the owner
entity type(s).
 The primary key of R is the combination of the primary
key(s) of the owner(s) and the partial key of the weak entity
type W, if any.
Cont…
3. Map Binary 1:1 Relation Types
 For each binary 1:1 relationship type R in the ER schema,
identify the relations S and T that correspond to the entity
types participating in R.
 Choose one of the relations-say S-and include a foreign key
in S the primary key of T. It is better to choose an entity type
with total participation in R in the role of S.
Cont…
4. Map Binary 1:N Relationship Types.

 For each regular binary 1:N relationship type R, identify

the relation S that represent the participating entity type
at the N-side of the relationship type.
 Include as foreign key in S the primary key of the relation
T that represents the other entity type participating in R.
 Include any simple attributes of the 1:N relation type as
attributes of S.
Cont…
5. Map Binary M:N Relationship Types.

 For each regular binary M:N relationship type R, create a

new relation S to represent R.
 Include as foreign key attributes in S the primary keys of
the relations that represent the participating entity types;
their combination will form the primary key of S.
 Also include any simple attributes of the M:N relationship
type (or simple components of composite attributes) as
attributes of S.
Cont…
6. Map Multivalued attributes.

 For each multivalued attribute A, create a new relation R.

 This relation R will include an attribute corresponding to A,
plus the primary key attribute K-as a foreign key in R-of
the relation that represents the entity type of relationship
type that has A as an attribute.
 The primary key of R is the combination of A and K. If the
multivalued attribute is composite, we include its simple
components.
Cont..
 For each n-ary relationship type R, where n>2, create a
new relationship S to represent R.
 Include as foreign key attributes in S the primary keys of
the relations that represent the participating entity types.
 Also include any simple attributes of the n-ary
relationship type (or simple components of composite
attributes) as attributes of S.
 Thank you