Chapter 6

Database Design and the E-R Model

 Entity

An entity is a ‘thing’ or ‘object’ in the real world that is distinguishable from all
other objects.

For example: each student in the discipline is an entity.

An entity may be concrete such as a student or a book.

An entity may be abstract such as a course, or a holiday, or a concept.

 Entity Set

An entity set is a set of entities of the same type that share the same properties,
or attributes.
For example: the set of all persons who are enrolling in a given discipline can
be defined as the entity set student.

Entity sets do not need to be disjoint.

For example: consider the entity set of all teachers of a university is defined as
teacher and the set of all students of the university is defined as student. A
person entity may be a teacher entity, a student entity, both, or neither.
 Attribute

Attributes are descriptive properties possessed by each member of an entity set.

An entity is represented by a set of attributes.
For example: possible attributes of the student entity set are student-name,
student-no, year, and term.

For each attribute, there is a set of permitted values, called the domain, or value
set, of that attribute.
For example: the domain of attribute student-name might be the set of all text
strings of a certain length (say, 40 characters).
 Simple Attribute vs. Composite Attribute

Simple attributes are not divided into subparts.

For example: student-no, course-title etc.

Composite attributes can be divided into subparts (i.e., other attributes).

For example: student-name could be structured as a composite attribute

consisting of first-name, middle-initial, and last-name.

A composite attribute may

appear as a hierarchy.
 Single-valued vs. Multi-valued Attributes

The attribute, which has a single value for a particular entity, is called single-
valued attribute.
For example: the course-no attribute for a specific course entity refers to only
one course number.

There may be instances where an attribute has a set of values for a specific
entity. This type of attribute is said to be multi-valued attribute.

For example: the attribute telephone-no for a person entity set. Any particular
person may have zero, one, or more telephones; therefore, different person
entities within the entity set will have different numbers of values for the
telephone-no attribute.

Where appropriate, upper and lower bounds may be placed on the number of
values in a multi-valued attribute.
 Null Attributes

A null value is used when an entity does not have a value for an attribute and
has the meaning of ‘not applicable’.
For example: the value for the attribute telephone-no will be null, if the person
does not have any telephone.

Null can also designate that an attribute value is unknown.

An unknown value may be missing (the value does exist, but presently
unavailable).
For example: if the value of the attribute student-no for a particular student is
null, it is assumed that the value is missing.
An unknown value may be not known (it is not known whether or not the value
actually exists).
For example: A null value for road-no attribute could mean that the address does
not include a road number, that a road number exists but it is not known, or that it
is not known whether or not a road number is part of the person’s address.
 Derived Attributes

The value for this type of attribute can be derived from the values of other
related attributes or entities.

For example: consider that the person entity set has the related attributes
date-of-birth and age.

The value for age can be derived from the value for date-of-birth and the
current date.

In this case, age is the derived attribute.

date-of-birth may be referred to as a base attribute, or a stored attribute.

 Relationship and Relationship Sets

Relationship
A relationship is an association among several entities.
For example: a relationship can be defined that associates student 010201 with
course CSE-3203.
This relationship specifies that 010201 is the student-no of a student, who
enrolls in the course with course-no CSE-3203.
Relationship Sets
A relationship set is a set of relationships of the same type.
Formally, it is a mathematical relation on n  2 (possibly nondistinct) entity
sets. If E1, E2, …, En are entity sets, then a relationship set R is a subset of

{(e1, e2, …, en) | e1  E1, e2  E2, …, en  En}

where (e1, e2, …, en) is a relationship.

 Relationship Sets

Participation
The association between entity sets is referred to as participation.
For example: the entity sets E1, E2, …, En participate in relationship R.

Degree of Relationship Set

The number of entity sets that participate in a relationship set is the degree of
the relationship set.
A binary relationship set, which involves two entity sets, is of degree 2.
A ternary relationship set is of degree 3.
 Mapping Constraints

Two of the most important types of constraints:

(a) Mapping Cardinalities

(b) Existence Dependencies

Mapping Cardinalities

Mapping cardinalities, or cardinality ratios, express the number of entities to

which another entity can be associated via a relationship set.

Mapping cardinalities are most useful in describing binary relationship sets.

Note: It is always possible to replace a non-binary (n-ary, n > 2) relationship

set by a number of distinct binary relationship sets.
 Mapping Cardinality

For a binary relationship set R between entity sets A and B, the mapping
cardinality must be one of the following:

(a) One to One: An entity in A is associated with at most one entity in B, and
an entity in B is associated with at most one entity in A.

(b) One to many: An entity in A is associated with any number of entities in B.

An entity in B, however, can be associated with at most one entity in A.

(c) 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.

(d) Many 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.
 Existence Dependency

If the existence of entity x depends on the existence of entity y, then x is said to

be existence dependent on y.

Entity y is said to be a dominant entity.

Entity x is said to be a sub-ordinate entity.

For example: Consider the entity set course and the entity set registration that
keeps information about all the registrations that were made in connection to a
particular course.

A relationship set course-registration is formed between these two entity sets,

which is one-to-many from course to registration.

The entity set course is dominant and registration is sub-ordinate in the

relationship set course-registration.
 Super Keys

A superkey is a set of one or more attributes that, taken collectively, allows

users to identify uniquely an entity in the entity set.

For example: the student-no attribute of the entity set student is sufficient to
distinguish one student entity from another.

Thus, student-no is a superkey.

Similarly, the combination of student-no and student-name is a superkey for

the entity set student.

The student-name attribute of student is not a superkey, because several people

might have the same name.
 Candidate Keys

The superkey for which no proper subset is a superkey is called candidate key.

The minimal superkeys are called candidate keys.

For example: The student-no attribute of the entity set student is a candidate
key for the entity set.

Similarly, the combination of student-name and student-address is a candidate

key for the entity set student.

Although the combination of the attributes student-no and student-name of

student is a superkey, but their combination does not form a candidate key,
since the attribute student-no alone is a candidate key.

The term primary key is used to denote a candidate key that is chosen by the
database designer as the principal means of identifying entities within an entity
set.
 Entity-Relationship Diagram

The overall logical structure of a database can be expressed graphically by a

Entity-Relationship (ER) diagram.

ER diagram consists of the following major components:

Rectangles Represent entity sets

Ellipses Represent attributes
Diamonds Represent relationship sets
Lines Link attributes to entity sets and entity sets
to relationship sets
Double Ellipses Represent multi-valued attributes
Dashed Ellipses Represent derived attributes
Double Lines Represent total participation of an entity in
a relationship set
 Entity-Relationship Diagram

For example: A very simple Entity-Relationship (ER) diagram is shown below

with the most common components.

teacherID name courseNo title

teacher conduct course

Note: Attributes of an entity set that are members of the primary key are
underlined.
Symbols
 Entity-Relationship Diagram

many-to-many E1 R E2

many-to-one E1 R E2

one-to-many E1 R E2

one-to-one E1 R E2
 Weak Entity Sets

An entity set may not have sufficient attributes to form a primary key. Such an
entity set is termed a weak entity set.
For example: Consider, entity set employee with attributes personID,
employee-name, designation, and department and entity set dependent with
attributes dependentID, dependent-name, and age.
Entity sets employee and dependent participate in a relationship depend.

Entity set dependent is a weak entity set.

An entity set that has a primary key is termed a strong entity set.

A member of a strong entity set is by definition a dominant entity, whereas a

member of a weak entity set is a subordinate entity.
 Weak Entity Sets

Discriminator
The discriminator of a weak entity set is a set of attributes that works as a
means of distinguishing among all those entities in the entity set that depend on
one particular strong entity set.

The primary key of a weak entity set is formed by the primary key of the
strong entity set on which the weak entity set is existence dependent, plus the
weak entity set’s discriminator.

For example: The primary key for the weak entity set depend is formed by
the primary key employeeID of entity set employee and its discriminator
dependentID.
 Weak Entity Sets

A weak entity set is indicated in E-R diagrams by a doubly outlines box, i.e.,
Double Rectangle.
The corresponding identifying relationship is indicated by a doubly outlined
diamond, i.e., Double Diamond.
The discriminator of a weak entity set is underlined with a dashed line.

personID name dep_ID dep_name

employee depend dependent

Partially modified ERD with the design given in examples on weak entity set.
 Extended E-R Features

Specialization

An entity set may include sub-groupings of entities that are distinct in some
way from other entities in the set. The process of designating sub-groupings
within an entity set is specialization.
For example: The entity set account with attributes account-no and balance.

An account is classified as follows:

saving-account with attribute interest-rate.

current-account with attribute overdraft-amount.

Here, the distinguishing feature is the type of the account.

Again, account may be commercial-account and personal-account with the

distinguishing feature ‘ type of the account ownership’ .
 Extended E-R Features

Specialization can be applied repeatedly to refine the design scheme.

In terms of an E-R diagram, specialization is depicted by a triangle component
labeled ISA, which stands for “is a”.

The ISA relationship may be referred to as a superclass-subclass relationship.

Generalization

Generalization is a containment relationship that exists between a higher-level

entity set and one or more lower-level entity sets.

For all practical purpose, generalization is a simple inversion of specialization.

Generalization proceeds from the recognition that a number of entity sets share
some common properties.

Based on the commonalities among entity sets, generalization synthesizes these

entity sets into a single, higher-level entity set.
 Extended E-R Features

Generalization is used to emphasize the similarities among lower-level entity

sets and to hide the differences.
Generalization also permits an economy of representation in that shared
attributes are not repeated.

Attribute Inheritance
Attribute inheritance is a crucial property of the higher-level and lower-level
entities created by specialization and generalization.

The attributes of higher-level entity sets are said to be inherited by the lower-
level entity sets.

A lower-level entity set (or subclass) also inherits participation in the

relationship sets in which its higher-level entity set (or superclass) participates.

Attribute inheritance applies through all tiers of lower-level entity sets.

Extended E-R Features
 Extended E-R Features

Completeness Constraint

This specifies whether or not an entity in the higher-level entity set must be
belong to at least one of the lower-level entity sets within a generalization.

•Total: Each higher-level entity must belong to a lower-level entity set.

•Partial: Some higher-level entities may not long to any lower-level entity set.

For example: ?? (Find by yourself)

THANK YOU!!