Chapter 2

Data Models

2.1 data models
• Underlying structure of the database is called as data models.
• It is a collection of conceptual tools for describing data, data relationships, data semantics,
and consistency constraints.
Different types of data models are:
• Hierarchical model
• Network model
• Object oriented model
• Object relational model
• Relational model
• Entity relationship model

2.2 HIERARCHICAL MODEL

The hierarchical data model organizes data in a tree structure. In this model, each entity has only
Copyright © 2016. Alpha Science International. All rights reserved.

one parent but can have several children. Only one entity at the top of the hierarchy is called as
Root. The structure is based on the rule that one parent can have many children but children are
allowed only one parent. Linkages are only possible vertically but not horizontally or diagonally,
i.e. there is no relation between different trees at the same level unless they share the same parent.
Advantages
• High speed of access to large datasets.
• Data security: Hierarchical model was the first database model that offered the data security
that is provided and enforced by the DBMS.
• Efficiency: The hierarchical database model is very efficient when the database contains a
large number of transactions using data whose relationships are fixed.
• The model allows easy addition and deletion of new information. Data at the top of the
Hierarchy is very fast to access. It is very easy to work with the model because it works

Vidhya, V., et al. Database Management Systems, Alpha Science International, 2016. ProQuest Ebook Central,
http://ebookcentral.proquest.com/lib/univ-people-ebooks/detail.action?docID=5248352.
Created from univ-people-ebooks on 2025-05-10 18:06:12.
 2.2 Database Management Systems

well with linear type data storage such as tapes. The model relates very well to natural
hierarchies such as assembly plants and employee organization in corporations. It relates
well to anything that works through one to many relationships.

Department

No. Name

Course Student

No. Name Unit Id Name Course

Id Name Professor

Fig. 2.1. Hierarchical model.

Disadvantages
• Implementation complexity
• Database management problems
• Lack of structural independence.
• This model cannot be used for more sophisticated relationships. It requires data to be
repetitively stored in many different entities. The database can be very slow when searching
for information on the lower entities.
• We no longer use linear data storage mediums such as tapes so that advantage is null.
• Searching for data requires the DBMS to run through the entire model from top to bottom
until the required information is found, making queries very slow. It can only model one to
many relationships; many to many relationships are not supported.
Copyright © 2016. Alpha Science International. All rights reserved.

2.3 NETWORK MODEL

The Network Data Model is also known as the “CODASYL Data Model” or sometimes as “DBTG
Data Model.” The model is based on directed graph theory. The network model replaces the
hierarchical tree with a graph thus allowing more general connections among the nodes. The main
difference of the network model from the hierarchical model is its ability to handle many-to-many
(n : n) relationship or in other words, it allows a record to have more than one parent.
Advantages
• Conceptual simplicity
• Capability to handle more relationship types
• Data independence

Vidhya, V., et al. Database Management Systems, Alpha Science International, 2016. ProQuest Ebook Central,
http://ebookcentral.proquest.com/lib/univ-people-ebooks/detail.action?docID=5248352.
Created from univ-people-ebooks on 2025-05-10 18:06:12.
 Data Models 2.3

Department Student

No. Name Id Name Course Student

Course Professor

No. Name Unit Id Name

Fig. 2.2. Network model.

Disadvantages
• Detailed structural knowledge is required
• Lack of structural independence

2.4 OBJECT-ORIENTED MODEL
Object DBMSs add database functionality to object programming languages. Object DBMSs
extend the semantics of the C++, Smalltalk and Java object programming languages to provide
full-featured database programming capability, while retaining native language compatibility. A
major benefit of this approach is the unification of the application and database development into
a seamless data model and language environment. As a result, applications require less code, use
more natural data modeling, and code bases are easier to maintain. Object developers can write
complete database applications with a modest amount of additional effort.
The object-oriented model is based on a collection of objects. An object contains values stored
in instance variables within the object. An object also contains bodies of code that operate in the
object, these bodies of code are called methods. Objects that contain the same types of values and
the same methods are grouped together into classes.
Advantages
Copyright © 2016. Alpha Science International. All rights reserved.

• Applications require less code

• Applications use more natural data model
• Code is easier to maintain
• It provides higher performance management of objects and complex interrelationships
between objects
• Object-oriented features improve productivity
• Data access is easy.

2.5 OBJECT RELATIONAL MODEL

Object relational database management systems (ORDBMSs) add new object storage capabilities
to the relational systems at the core of modern information systems. These new facilities integrate
management of traditional fielded data, complex objects such as time-series and geospatial data

Vidhya, V., et al. Database Management Systems, Alpha Science International, 2016. ProQuest Ebook Central,
http://ebookcentral.proquest.com/lib/univ-people-ebooks/detail.action?docID=5248352.
Created from univ-people-ebooks on 2025-05-10 18:06:12.
 2.4 Database Management Systems

and diverse binary media such as audio, video, images, and applets. By encapsulating methods
with data structures, an ORDBMS server can execute complex analytical and data manipulation
operations to search and transform multimedia and other complex objects.
A system that includes both object infrastructure and set relational extenders. Object-relational
systems combine the advantages of modern object-oriented programming languages with relational
database features such as multiple views of data and a high-level, non-procedural query language.
Some of the object-relational systems available in the market are IBM DB2 universal server, oracle
corporation’s oracle 8, Microsoft Corporations SQL server 7 and so on.

2.6 RELATIONAL MODEL

The relational model was introduced by Dr. E. F. Codd in 1970. The relational model represents
data in the form of two dimensional tables. The organization of data into relational tables is known
as the logical view of the database. Software such as Oracle, Microsoft SQL Server, Sybase, are
based on the relational model.

2.6.1 Characteristics of Relational Model

• The relational model eliminated all parent child relationships and instead represented all
data in the database as simple row/column tables of data values.
• A relation is similar to a table with rows/columns of data values.
• Each table is an independent entity and there is no physical relationship between tables.
• Most data management systems based on the relational model have a built-in support for
query languages like ANSI SQL or QBE. These queries are simple English constructs that
allow adhoc data manipulation from a table.
• Relational model of data management is based on set theory.
• The user interface used with relational models is non-procedural because only what needs
to be done is specified and not how it has to be done.

2.6.2 E. F. Codd’s Laws for a Fully Functional Relational Database

Management System
Copyright © 2016. Alpha Science International. All rights reserved.

CODD’s 12 rules define an ideal relational database which is used as a guideline for designing
relational database systems. Though no commercial database system completely conforms to all 12
rules, they do interpret the relational approach. The CODD’s 12 rules are as follows:
Rule 0: Foundation rule: The system must qualify as relational both as a database and as a
management system.
Rule 1: The information rule: All information in the database must be represented in one and
only one way (that is, as values in a table).
Rule 2: The guaranteed access rule: All data should be logically accessible through a
combination of table name, primary key value and column name.
Rule 3: Systematic treatment of null values: A DBMS must support Null Values to represent
missing information and inapplicable information in a systematic manner independent of data
types.

Vidhya, V., et al. Database Management Systems, Alpha Science International, 2016. ProQuest Ebook Central,
http://ebookcentral.proquest.com/lib/univ-people-ebooks/detail.action?docID=5248352.
Created from univ-people-ebooks on 2025-05-10 18:06:12.
 Data Models 2.5

Rule 4: Active online catalog based on the relational model: The database must support
online relational catalog that is accessible to authorized users through their regular query language.
Rule 5: The comprehensive data sublanguage rule: The database must support at least
one language that defines linear syntax functionality, supports data definition and manipulation
operations, data integrity and database transaction control.
Rule 6: The view updating rule: Representation of data can be done using different logical
combinations called Views. All the views that are theoretically updatable must also be updatable
by the system.
Rule 7: High-level insert, update, and delete: The system must support set at the time of
insert, update and delete operators.
Rule 8: Physical data independence: Changes made in physical level must not have any
impact and require a change to be made in the application program.
Rule 9: Logical data independence: Changes made in logical level must not impact and
require a change to be made in the application program.
Rule 10: Integrity independence: Integrity constraints must be defined and separated from
the application programs. Changing Constraints must be allowed without affecting the applications.
Rule 11: Distribution independence: The user should be unaware about the database location
i.e. whether or not the database is distributed in multiple locations.
Rule 12: The non subversion rule: If a system provides a low level language, then there should
be no way to subvert or bypass the integrity rules of high-level language. Of all the rules, rule 3 is
the most controversial. This is due to a debate about three-valued or ternary logic. Codd’s rules and
SQL use ternary logic, where null is used to represent missing data and comparing anything to null
results in an unknown truth state. However, when both Booleans or operands are false, the operation
is false; therefore, not all data that is missing is unknown, hence the controversy.

2.6.3 Principle Components of Relational Model

The relational model consists of following three basic components:
1. Data structure Primary Key
2. Data integrity
Copyright © 2016. Alpha Science International. All rights reserved.

3. Data manipulation Emp_on Name Age Department

1. The Relational
E09898 Smith 32 Marketing
Cardinality

Data Structure:
The relational Relation E19736 Jones 40 Production
data structure is E21375 Blake 28 Operations
shown in Fig 2.3.
E31467 Clark 52 Maintenance
It is based on the
Tuples
employee relation.
Attributes
Degree

Fig. 2.3. The relatinal data structure.

Vidhya, V., et al. Database Management Systems, Alpha Science International, 2016. ProQuest Ebook Central,
http://ebookcentral.proquest.com/lib/univ-people-ebooks/detail.action?docID=5248352.
Created from univ-people-ebooks on 2025-05-10 18:06:12.
 2.6 Database Management Systems

Structural terminology summary

Formal relational term Informal Equivalence
Relation Table
Tuple Row or record
Cardinality Number of rows
Attribute Column or field
Degree Number of columns
Primary key Unique identifier
Domain Pool of legal values

2. Relational Integrity: Integrity constraints means when changes made to the database by
authorized users that should not result in a loss of data consistency.
There are two main types of integrity constraints.
• Domain constraints
• Referential integrity
Domain Constraints
• Domain constraints specify the set of values that can be associated with an attribute.
• Domain constraints are tested easily by the system whenever a new data item is entered
into the database.
• Domain constraints also prohibit use of null values for particular fields.
Referential Integrity
• A value that appears in one relation for a given set of attributes also appears for a certain
set of attributes in another relation. This is called referential integrity.
Referential Integrity in the E-R Model: Referential integrity constraints arise frequently.
If we derive our relational database scheme by constructing tables from E-R diagrams then
every relation arising from a relationship set has referential integrity constraints.
Copyright © 2016. Alpha Science International. All rights reserved.

E1

E2

En – 1

En

Fig. 2.4. Referential integrity in the E-R model.

As shown in Fig. 2.4, an n-array relationship set R, relating entity sets E1, E2,……..En. Let
Ki denote the primary key of Ei. The attributes of the relation scheme for relationship set

Vidhya, V., et al. Database Management Systems, Alpha Science International, 2016. ProQuest Ebook Central,
http://ebookcentral.proquest.com/lib/univ-people-ebooks/detail.action?docID=5248352.
Created from univ-people-ebooks on 2025-05-10 18:06:12.
 Data Models 2.7

R include K1∪K2∪……∪Kn. Each Ki in the scheme for R is a foreign key that leads to a
referential integrity constraints.
Referential Integrity in SQL: Using SQL primary key, candidate key, and foreign key are
defined as part of the create table statement as given below
Example: Create table deposit (br-name char (15), acc-no char(10), cust-name cher (20)
not null, balance integer, primary key (acc-no, cust-name), foreign key (branch-name),
Foreign key(cist-name) references customer);
Null: “Null represents a value for an attribute that is currently unknown or is not applicable
for this tuple.”
Other integrity constraints are:
• Entity integrity
• Enterprise constraints.
Entity Integrity: “In a base relation, no attribute of a primary key can be null”.
A primary key is used to identify tuples uniquely. This means that no subset of the primary
key is sufficient to provide unique identification of tuples. Therefore, primary key should
not be null.
Enterprise Constraints: These are additional rules specified by the uses or database
administrators of a database.
3. Data Manipulation: The manipulated part of the relational algebra model consists of a set
of operators known collectively as the relational algebra together with relational calculus.
Advantages of Relational Model
• Structural independence
• Conceptual simplicity
• Design, implementation, maintenance and usage ease
• Good for adhoc requests
• It is simpler to navigate
• Greater flexibility.
Disadvantages of Relational Model
Copyright © 2016. Alpha Science International. All rights reserved.

• Significant hardware and software overheads

• Not as good for transaction process modeling as hierarchical and network models
• May have slower processing than hierarchical and network models.

2.7 COMPARISON BETWEEN THE VARIOUS DATABASE MODELS

Model Data element Relationship Identity Access language
organization representation
Hierarchical Files, records Tree Record based Procedural
Network Files, records Graph Record based Procedural
Relational Tables Foreign key concept Value based Non-Procedural
Object-oriented Objects Logical containment Record based Procedural
Object- Relational Objects Relational extenders Value based Non-Procedural

Vidhya, V., et al. Database Management Systems, Alpha Science International, 2016. ProQuest Ebook Central,
http://ebookcentral.proquest.com/lib/univ-people-ebooks/detail.action?docID=5248352.
Created from univ-people-ebooks on 2025-05-10 18:06:12.
 2.8 Database Management Systems

2.8 ENTITY RELATIONSHIP MODEL

The E-R data model considers the real world consisting of a set of basic objects, called entities,
and relationships among these objects.

2.8.1 Basic Concepts

The E-R data model employs three basic notions:
• Entity
• Attributes
• Relationship sets

2.8.1.1 Entity
An entity is ‘thing’ or ‘object in the real world that is distinguishable from all other objects.
For example, each person is an entity.
An entity has a set of properties, and the values for some set of properties may uniquely
identify an entity.
For example, a customer with customer-id property with value C101 uniquely identifies that
person.
An entity may be concrete, such as person or a book, or it may be abstract, such as a loan, or
a holiday.
Entity sets: An entity set is a set of entities of the same type that share the same properties,
or attributes.

2.8.1.2 Attributes
• The properties that describe an entity are called attributes.
• An entity is represented by a set of attributes.
• The attributes of customer entity set are customer_id , customer_name and city.
Attributes are classified as:
Copyright © 2016. Alpha Science International. All rights reserved.

1. Simple
2. Composite
3. Single-valued
4. Multi-valued
5. Derived
6. Stored
1. Simple attribute: Cannot be subdivided
Example: Age, sex, GPA.
2. Composite attribute: Can be subdivided
Example: Address: street, city state zip.
3. Single-valued attribute: Has only a single value
Example: Social security number.

Vidhya, V., et al. Database Management Systems, Alpha Science International, 2016. ProQuest Ebook Central,
http://ebookcentral.proquest.com/lib/univ-people-ebooks/detail.action?docID=5248352.
Created from univ-people-ebooks on 2025-05-10 18:06:12.
 Data Models 2.9

4. Multi-valued attribute: Can have many values

Example: Person may have several college degrees.
5. Derived attribute: Can be calculated from other information
Example: Age can be derived from D.O.B.
6. Stored attributes: The attributes stored in a database are called stored attributes.
2.8.1.3 Relationships and Relationships Sets
• Relationship is an association among several entities.
• Relationship set is a set of relationships of the same type.
Types of Relationships
(i) Unary relationship: A unary relationship exists when an association is maintained within
a single entity.
Boss
Employee

Manager

Worker

(ii) Binary relationship: A binary relationship exists when two entities are associated.

Publisher Publishes Book

(iii) Ternary relationship: A ternary relationship exists when there are three entities associated.

Teacher Teaches Subject

Copyright © 2016. Alpha Science International. All rights reserved.

Student

(iv) Quaternary relationship: A quaternary relationship exists when there are four entities
associated.

Vidhya, V., et al. Database Management Systems, Alpha Science International, 2016. ProQuest Ebook Central,
http://ebookcentral.proquest.com/lib/univ-people-ebooks/detail.action?docID=5248352.
Created from univ-people-ebooks on 2025-05-10 18:06:12.
 2.10 Database Management Systems

Entity role: The function that an entity plays in a relationship is called that entity’s role. A role is
one end of an association.

Here, Entity role is Employee.

2.8.2 Constraints
An E-R enterprise schema may define certain constraints to which the contents of a database
system must conform.
Two types of constraints are:
• Mapping cardinalities
• Participation constraints
2.8.2.1 Mapping Cardinalities
• Mapping cardinalities express the number of entities to which another entity can be
associated via a relationship set.
• Cardinality in E-R diagram that is represented by two ways:
(i) Directed line (→)  (ii) Undirected line ()
For a binary relationship set R between entity sets A and B, the mapping cardinalities must be
one of the following:
(i) 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.

a1 b1
b2
a2
b3
Copyright © 2016. Alpha Science International. All rights reserved.

a3 b4

Example: A customer with single account at a branch is shown by one-to-one relationship

as given below.

Customer Depositor Account

(ii) One-to-many: An entity in A is associated with any number of entities (zero or more) in B.
An entity in B, however, can be associated with at most one entity in A.

Vidhya, V., et al. Database Management Systems, Alpha Science International, 2016. ProQuest Ebook Central,
http://ebookcentral.proquest.com/lib/univ-people-ebooks/detail.action?docID=5248352.
Created from univ-people-ebooks on 2025-05-10 18:06:12.
 Data Models 2.11

a1 b1
b2
a2 b3
b4
a3 b5

Example: A customer having two accounts at a given branch is shown by one-to-many

relationship as given below.

Customer Depositor Account

(iii) 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 (zero or more) of entities in A.
a1 b1
a2 b2
a3 b3
a4 b4
a5 b5

Example: Many employees works for a company. This relationship is shown by many-to-

one as given below.

Employees Works for Company

(iv) Many-to-many: An entity in A is associated with any number (zero or more) of entities in
B, and an entity in B is associated with any number (zero or more) of entities in A.
Copyright © 2016. Alpha Science International. All rights reserved.

a1 b1
a2 b2
a3 b3
a4 b4
a5 b5

Example: Employee works on number of projects and project is handled by number of

employees. Therefore, the relationship between employee and project is many-to-many as
shown below.

Employees Works for Project

Vidhya, V., et al. Database Management Systems, Alpha Science International, 2016. ProQuest Ebook Central,
http://ebookcentral.proquest.com/lib/univ-people-ebooks/detail.action?docID=5248352.
Created from univ-people-ebooks on 2025-05-10 18:06:12.
 2.12 Database Management Systems

2.8.2.2 Participation Constraint

• Participation can be divided into two types.
1. Total 2. Partial
• If every entity in E participates in at least one relationship in R, the participation is called
Total Participation
• If only some entities in E participate in relationships in R, then the participation is called
Partial Participation.
2.8.3 Keys
• A key allows us to identify a set of attributes and thus distinguishes entities from each other.
• Keys also help uniquely identify relationships, and thus distinguish relationships from each
other.
Key Type Definition
Superkey Any attribute or combination of attributes that uniquely identifies a row in the table.
Example: Roll_No attribute of the entity set ‘student’ distinguishes one student entity
from another.
Candiate Key Minimal Superkey is called candidate key. A superkey that does not contain a subset of
attributes that is itself a superkey.
Example: Student_name and Student_street, are sufficient to uniquely identify one
particular student.
Primary Key The candidate key selected to uniquely identify all rows. Cannot contain null values
Example: Roll_No is a primary set of ‘student’ entity set.
Foreign Key An attribute (or combination of attributes) in one table that must either match the primary
key of another table or be null
Example: Consider in the staff relation the branch_no attribute exists to match staff to the
branch office they work in. In the staff relation, branch_no is foreign key.
Secondary Key An attribute or combination of attributes is used to make data retrieval more efficient.
Copyright © 2016. Alpha Science International. All rights reserved.

2.8.4 Entity-Relationship Diagram
• E-R diagram can express the overall logical structure of a database graphically.
• E-R diagram consists of the following major components:
Component name Symbol Description
Rectangles Represent entity sets

Ellipses Represent attributes

Diamonds Represent relationship sets

Vidhya, V., et al. Database Management Systems, Alpha Science International, 2016. ProQuest Ebook Central,
http://ebookcentral.proquest.com/lib/univ-people-ebooks/detail.action?docID=5248352.
Created from univ-people-ebooks on 2025-05-10 18:06:12.
 Data Models 2.13

Lines Link attributes to entity sets and entity sets to relationship sets

Double ellipses Represent multi-valued attributes

Dashed ellipses Represent derived attributes

A

Double lines Represent total participation of an entity in a relationship set

Double rectangles Represent weak entity sets

Summary of ER diagram notation

E Entiry Set A Attribute

E Weak entiry set A Multi-valued attribute

R Relationship set A Derived attribute

R Identifying relationship R E Total participation of

set for weak entity set entity set in relationship

Discriminating attribute
A Primary key A
of weak entity set

R Many-to-many R Many-to-one
Copyright © 2016. Alpha Science International. All rights reserved.

relationship relationship

l.h
R One-to-one relationship R E Cardinality limit

role-
name ISA ISA (Specialization or
R E Role indicator
generalization)

ISA ISA
Total generalization Disjoint generalization
disjoint

Vidhya, V., et al. Database Management Systems, Alpha Science International, 2016. ProQuest Ebook Central,
http://ebookcentral.proquest.com/lib/univ-people-ebooks/detail.action?docID=5248352.
Created from univ-people-ebooks on 2025-05-10 18:06:12.
 2.14 Database Management Systems

2.8.5 Dependency
Existence Dependencies
• If the existence of entity x depends on the existence of entity y, then x is said to be existence
dependent on y.
• If y is deleted, so is x.
• Entity y is said to be a dominant entity, and x is said to be subordinate entity.
Example: Figure 2.5 shows the dominant entity set ‘loan’ which is also called Strong entity
set, and subordinate entity set ‘payment’ which is also called week entity set, connected by
relationship ‘loan-payment’.

Payment-date

Loan-number Amount Payment-number Payment-amount

Loan-
Loan Payment Payment

Fig. 2.5. Dependency.

Definition: Strong and Weak Entity Sets

• An entity that is existence dependent on some other entity is called a week entity type.
• An entity set on which week entity set depends is called strong entity set.
Example: Figure below shows weak entity set ‘Parent’ which depends on strong entity set
‘Employee’.
Copyright © 2016. Alpha Science International. All rights reserved.

Emp_code Employee Has Parent Name

Emp_name Emp_address Parent_code Emp_code

Representation of Role
• The function that an entity plays in a relationship is called its role.

Vidhya, V., et al. Database Management Systems, Alpha Science International, 2016. ProQuest Ebook Central,
http://ebookcentral.proquest.com/lib/univ-people-ebooks/detail.action?docID=5248352.
Created from univ-people-ebooks on 2025-05-10 18:06:12.
 Data Models 2.15

employee-name

employee-id telephone-number

manager
employee Works-for
worker

Fig. 2.6. E-R Diagram with role indicators.

Strong Entity Set Weak Entity Set

1. It has its own primary key. 1. It does not have sufficient attributes to form a
primary key on its own
2. It is represented by a rectangle. 2. It is represented by double rectangle.
3. It contains the primary key represented by an 3. It contains a Partial key or discriminator
underline. represented by a dashed line.
4. The members of strong entity set is called as 4. The member of weak entity set is called as
dominant entity set. subordinate entity set.
5. The primary key is one of its attributes which 5. The primary key of weak entity set is a
uniquely identifies its member. combination of partial key and primary key of
the strong entity set.
6. The relationship between two strong entity set 6. The relationship between one strong and one
is represented by a diamond symbol. weak entity set is represented by a double
diamond sign. It is known as identifying
relationship.
7. The line connecting strong entity set with the 7. The line connecting weak entity set with the
relationship is single. identifying relationship is double.
8. The total participation in the relationship may 8. Total participation in the identifying relationship
or may not exist. always exists.

2.8.6 Sample E-R Diagram

Copyright © 2016. Alpha Science International. All rights reserved.

1. Total participation of an entity set in a relationship set

• In Fig: 2.6., Double line from loan to borrower indicates that each loan must have at least
one associated customer

Fig. 2.7. Total participation of an entity set in a relationship set.

Vidhya, V., et al. Database Management Systems, Alpha Science International, 2016. ProQuest Ebook Central,
http://ebookcentral.proquest.com/lib/univ-people-ebooks/detail.action?docID=5248352.
Created from univ-people-ebooks on 2025-05-10 18:06:12.
 2.16 Database Management Systems

2. Cardinality limits on relationship sets

• In Fig. 2.8, edge between loan and borrower has a cardinality constraint of 1...1, meaning
that each loan must have exactly one associated customer.
• The limit 0...* on the edge from customer to borrower indicates that a customer can have
zero or more loans.

Fig: 2.8. Cardinality limits on relationship sets.

3. E-R diagram with an attribute attached to a relationship set

customer-name customer-street
account-number balance

customer-id customer-city

customer depositor account

access-date
Copyright © 2016. Alpha Science International. All rights reserved.

Fig. 2.9 E-R diagram with an attribute attached to a relationship set.

2.8.7 E-R Diagram
Let us design an Entity Relationship (ER) model for a college database.
• A college contains many departments.
• Each department can offer any number of courses.
• Many instructors can work in a department.
• An instructor can work only in one department.
• For each department there is a head.

Vidhya, V., et al. Database Management Systems, Alpha Science International, 2016. ProQuest Ebook Central,
http://ebookcentral.proquest.com/lib/univ-people-ebooks/detail.action?docID=5248352.
Created from univ-people-ebooks on 2025-05-10 18:06:12.
 Data Models 2.17

• An instructor can be head of only one department.

• Each instructor can take any number of courses.
• A course can be taken by only one instructor.
• A student can enroll for any number of courses.
• Each course can have any number of students.
Step 1: Identify the Entities
What are the entities here?
From the statements given, the entities are;
1. Department
2. Course
3. Instructor
4. Student
Step 2 : Identify the relationships
1. One department offers many courses. But one particular course can be offered by only one
department. Hence the cardinality between department and course is One to Many (1:N)
2. One department has multiple instructors. But instructor belongs to only one department.
Hence the cardinality between department and instructor is One to Many (1:N)
3. One department has only one head and one head can be the head of only one department.
Hence the cardinality is one to one. (1:1)
4. One course can be enrolled by many students and one student can enroll for many courses.
Hence the cardinality between course and student is Many to Many (M:N)
5. One course is taught by only one instructor. But one instructor teaches many courses. Hence
the cardinality between course and instructor is Many to One (N :1)
Step 3: Identify the key attributes
• “Departmen_Name” can identify a department uniquely. Hence Department_Name is the
Copyright © 2016. Alpha Science International. All rights reserved.

key attribute for the Entity “Department”.

• Course_ID is the key attribute for “Course” Entity.
• Student_ID is the key attribute for “Student” Entity.
• Instructor_ID is the key attribute for “Instructor” Entity.
Step 4: Identify other relevant attributes
• For the department entity, other attributes are location
• For course entity, other attributes are course_name, duration
• For instructor entity, other attributes are first_name, last_name, phone
• For student entity, first_name, last_name, phone

Vidhya, V., et al. Database Management Systems, Alpha Science International, 2016. ProQuest Ebook Central,
http://ebookcentral.proquest.com/lib/univ-people-ebooks/detail.action?docID=5248352.
Created from univ-people-ebooks on 2025-05-10 18:06:12.
 2.18 Database Management Systems

Step 5: Draw complete E-R diagram

By connecting all these details, we can now draw E-R diagram as given below.

Department
Location
Name

Department

1
1 1

Offers Headed Has

by
Course_ID
1 N
N
is
N 1
duration Course taught Instructor
by
M
course_name first_name
Enrolled
By
Instructor_ID last_name
N
phone
Student

Student_ID phone

First_name Last_name
Copyright © 2016. Alpha Science International. All rights reserved.

Vidhya, V., et al. Database Management Systems, Alpha Science International, 2016. ProQuest Ebook Central,
http://ebookcentral.proquest.com/lib/univ-people-ebooks/detail.action?docID=5248352.
Created from univ-people-ebooks on 2025-05-10 18:06:12.
 Data Models 2.19

Another example for E-R diagram – Bank Enterprises

Copyright © 2016. Alpha Science International. All rights reserved.

2.8.8 Extended E-R Features

• ER model that is supported with the additional semantic concepts is called the extended
entity relationship model or EER model.
• EER model deals with
1. Specialization
2. Generalization
3. Aggregation

Vidhya, V., et al. Database Management Systems, Alpha Science International, 2016. ProQuest Ebook Central,
http://ebookcentral.proquest.com/lib/univ-people-ebooks/detail.action?docID=5248352.
Created from univ-people-ebooks on 2025-05-10 18:06:12.
 2.20 Database Management Systems

2.8.8.1 Specialization
• The process of designating sub groupings within an entity set is called Specialization.
• Specialization is a top-down process.
• Consider an entity set person. A person may be further classified as one of the following:
• customer
• employee
• Specialization is depicted by a triangle component labeled ISA, as Fig. 2.10 shows.
• The label ISA stands for “is a” for example, that a customer “is a” person.
• The ISA relationship may also be referred to as a super class-subclass relationship.
2.8.8.2 Generalization
• Generalization is a simple inversion of specialization.
• Generalization is the process of defining a more general entity type from a set of more
specialized entity types.
• Generalization is a bottom-up approach.
• Generalization results in the identification of a generalized super class from the original
subclasses.
Copyright © 2016. Alpha Science International. All rights reserved.

Fig. 2.10. Specialization and Generalization.

Vidhya, V., et al. Database Management Systems, Alpha Science International, 2016. ProQuest Ebook Central,
http://ebookcentral.proquest.com/lib/univ-people-ebooks/detail.action?docID=5248352.
Created from univ-people-ebooks on 2025-05-10 18:06:12.
 Data Models 2.21

• Person is the higher-level entity set.

• Customer and employee are lower-level entity sets.
• The person entity set is the super class of the customer and employee subclasses.

2.8.8.2.1 Constraints on Generalizations

1. One type of constraint determines which entities can be members of a lower-level entity set.
Such membership may be one of the following:
• Condition-defined. In condition-defined lower-level entity sets, membership is evaluated

on the basis of whether or not an entity satisfies an explicit condition.
• User-defined. User defined constraints are defined by user.

2. A second type of constraint relates to whether or not entities may belong to more than one
lower-level entity set within a single generalization. The lower-level entity sets may be one
of the following:
• Disjoint. A disjointness constraint requires that an entity belong to no more than one

lower-level entity set.
• Overlapping. Same entity may belong to more than one lower-level entity set within a

single generalization.
3. A final constraint, the completeness constraint specifies whether or not an entity in
the higher-level entity set must belong to at least one of the lower-level entity sets .This
constraint may be one of the following:
• Total generalization or specialization. Each higher-level entity must belong to a lower-

level entity set.
• Partial generalization or specialization. Some higher-level entities may not belong to

any lower-level entity set.
Copyright © 2016. Alpha Science International. All rights reserved.

2.8.8.2.2 Attribute Inheritance
• A property of the higher- and lower-level entities created by specialization and generalization
is attribute inheritance.
• The attributes of the higher-level entity sets are said to be inherited by the lower-level entity
sets.

2.8.8.3 Aggregation
• Aggregation is a process when relation between two entities is treated as a single entity.
Here the relation between Center and Course, is acting as an entity in relation with visitor.

Vidhya, V., et al. Database Management Systems, Alpha Science International, 2016. ProQuest Ebook Central,
http://ebookcentral.proquest.com/lib/univ-people-ebooks/detail.action?docID=5248352.
Created from univ-people-ebooks on 2025-05-10 18:06:12.
 2.22 Database Management Systems

Fig. 2.11. E-R diagram with aggregation.

2.8.9 Reduction of an E-R Schema to Tables

• We can represent an E-R database by a collection of tables.
• For each entity set and for each relationship set in the database, there is a unique table.
• Each table has multiple columns, each of which has a unique name.
• Both the E-R model and the relational-database model are abstract, logical representations
of real-world enterprises.
• Two models employ similar design principles; we can convert an E-R design into a relational
design.
2.8.9.1 Tabular Representation of Strong Entity Sets
• Let E be a strong entity set with attributes a1, a2, . . . , an. We represent this entity by a table
called E with n distinct columns, each of which corresponds to one of the attributes of E.
• Each row in this table corresponds to one entity of the entity set E.
• As an illustration, consider the entity set loan of the E-R diagram in Figure This entity set
Copyright © 2016. Alpha Science International. All rights reserved.

has two attributes: loan-number and amount.

The loan table

Loan_number Amount
L-11 900
L-14 1500
L-15 1500
L-16 1300
L-17 1000
L-23 2000
L-93 500

Vidhya, V., et al. Database Management Systems, Alpha Science International, 2016. ProQuest Ebook Central,
http://ebookcentral.proquest.com/lib/univ-people-ebooks/detail.action?docID=5248352.
Created from univ-people-ebooks on 2025-05-10 18:06:12.
 Data Models 2.23

2.8.9.2 Tabular Representation of Relationship Sets

• Consider the relationship set borrower in the E-R diagram of Figure. This relationship set
involves the following two entity sets:
• customer, with the primary key customer-id
• loan, with the primary key loan-number
• Since the relationship set has no attributes, the borrower table has two columns, labeled
customer-id and loan-number, as shown in Figure
The borrower table

Customer_id Loan_number
019-28-3746 L-11
019-28-3746 L-23
244-66-8800 L-93
321-12-3123 L-17
335-57-7991 L-16
555-55-5555 L-14
677-89-9011 L-15
963-96-3963 L-17
2.8.9.3 Tabular Representation of Weak Entity Sets
• Consider the entity set payment. This entity set has three attributes: payment-number,
payment-date, and payment-amount.
• The primary key of the loan entity set, on which payment depends, is loan-number.
• Thus, we represent payment by a table with four columns labeled loan-number, payment-
number, payment-date, and payment-amount, as in Figure
The payment table
Copyright © 2016. Alpha Science International. All rights reserved.

Loan_number Payment_number Payment_date Payment_amount

L-11 53 7 June 2001 125
L-14 69 28 May 2001 500
L-15 22 23 May 2001 300
L-16 58 18 June 2001 135
L-17 5 10 May 2001 50
L-17 6 7 June 2001 50
L-17 7 17 June 2001 100
L-23 11 17 May 2001 75
L-93 103 3 June 2001 900
L-93 104 13 June 2001 200

Vidhya, V., et al. Database Management Systems, Alpha Science International, 2016. ProQuest Ebook Central,
http://ebookcentral.proquest.com/lib/univ-people-ebooks/detail.action?docID=5248352.
Created from univ-people-ebooks on 2025-05-10 18:06:12.
 2.24 Database Management Systems

2.8.9.4 Redundancy of Tables

• In general, the table for the relationship set linking a weak entity set to its corresponding
strong entity set is redundant and does not need to be present in a tabular representation of
an E-R diagram.
Composite Attributes
• We handle composite attributes by creating a separate attribute for each of the component
attributes; we do not create a separate column for the composite attribute itself.
Multi-valued Attributes
• Consider an entity set employee with multi-valued attribute dependent-name. For this multi-
valued attribute, create a separate table as dependent-info with attributes dependent-name
and employee-id, which is the primary key of employee entity set.

2.8.9.5 Tabular Representation of Generalization

There are two different methods for transforming an E-R diagram that includes generalization
to a tabular form.
Consider the generalization shown in fig. where account is a higher-level entity, while lower-
level entity sets—that is, savings-account and checking-account.
Copyright © 2016. Alpha Science International. All rights reserved.

1. Create a table for the higher-level entity set.

• For each lower-level entity set, create a separate table

• Thus, for the E-R diagram of Figure we have three tables:

• account, with attributes account-number and balance
• savings-account, with attributes account-number and interest-rate
• checking-account, with attributes account-number and overdraft-amount
2. An alternative representation is possible,
• If the generalization is disjoint and complete

Vidhya, V., et al. Database Management Systems, Alpha Science International, 2016. ProQuest Ebook Central,
http://ebookcentral.proquest.com/lib/univ-people-ebooks/detail.action?docID=5248352.
Created from univ-people-ebooks on 2025-05-10 18:06:12.
 Data Models 2.25

• Here, do not create a table for the higher-level entity set.

• Then, for the E-R diagram of Figure, we have two tables.

• savings-account, with attributes account-number, balance, and interest-rate
• checking-account, with attributes account-number, balance, and overdraftamount

2.8.9.6 Tabular Representation of Aggregation

• Transforming an E-R diagram containing aggregation to a tabular form is straightforward.
• The table for the relationship set manages between the aggregation of works-on and the
entity set manager includes a column for each attribute in the primary keys of the entity set
manager and the relationship set works-on.

Review Questions
1. Define data model. List out different data models and explain the same.
2. State and explain the E. F. Codd’s Laws for a Fully Functional Relational Database
Management System.
3. Compare the different data models.
4. Explain the basic components of relational model.
5. Define ER model and describe the notations used in ER model.
6. Explain the different types of attributes with suitable examples.
7. Define keys. List out the different types of keys and explain the same.
8. Distinguish between weak and strong entity.
9. Construct an E-R diagram for a car-insurance company whose customers own one or more
cars each. Each car has associated with it zero to any number of recorded accidents.
10. Construct an E-R diagram for a hospital with a set of patients and a set of medical doctors.
Associate with each patient a log of the various tests and examinations conducted.
Copyright © 2016. Alpha Science International. All rights reserved.

11. A university registrar’s office maintains data about the following entities:
(a) courses, including number, title, credits, syllabus, and prerequisites;
(b) course offerings, including course number, year, semester, section number, instructor(s),
timings, and classroom;
(c) students, including student-id, name, and program; and
(d) instructors, including identification number, name, department, and title. Further,
the enrollment of students in courses and grades awarded to students in each course
they are enrolled for must be appropriately modeled. Construct an E-R diagram for
the registrar’s office. Document all assumptions that you make about the mapping
constraints.

Vidhya, V., et al. Database Management Systems, Alpha Science International, 2016. ProQuest Ebook Central,
http://ebookcentral.proquest.com/lib/univ-people-ebooks/detail.action?docID=5248352.
Created from univ-people-ebooks on 2025-05-10 18:06:12.
 2.26 Database Management Systems

12. Consider a database used to record the marks that students get in different exams of
different course offerings.
(a) Construct an E-R diagram that models exams as entities, and uses a ternary relationship,
for the above database.
(b) Construct an alternative E-R diagram that uses only a binary relationship between
students and course-offerings. Make sure that only one relationship exists between a
particular student and course-offering pair.
13. Consider a university database for the scheduling of classrooms for final exams. This
database could be modeled as the single entity set exam, with attributes course-name,
section-number, room-number, and time. Alternatively, one or more additional entity sets
could be defined, along with relationship sets to replace some of the attributes of the exam
entity set, as
(a) course with attributes name, department, and c-number
(b) section with attributes s-number and enrollment, and dependent as a weak entity set on
course
(c) room with attributes r-number, capacity, and building

qqq
Copyright © 2016. Alpha Science International. All rights reserved.

Vidhya, V., et al. Database Management Systems, Alpha Science International, 2016. ProQuest Ebook Central,
http://ebookcentral.proquest.com/lib/univ-people-ebooks/detail.action?docID=5248352.
Created from univ-people-ebooks on 2025-05-10 18:06:12.