Chapter 1
ER Model and Relational Model
LEARNING OBJECTIVES
Data model Entity types, entity sets and value sets
Schemas Weak entity set
Three-schema architecture Relational database
ER model NULL in tuples
Types of attributes Inherent constraint
Mapping cardinality Referential and entity integrity constraint
Complex attributes
intRodUCtion Data Model
A database is a collection of related data. By data, we mean facts A data model is a collection of concepts that can be used to describe
that can be recorded and that have implicit meaning. the structure of a database. It provides the necessary means to
achieve abstraction.
Example: Consider the names, telephone numbers and addresses
of the people. We can record this data in an indexed address book
and store it as Excel file on a hard drive using a personal computer. sChEMas
This is a collection of related data with an implicit meaning and In any data model, it is important to distinguish between the
hence is a database. description of the database and the database itself. The descrip-
A database management system (DBMS) is a collection of pro- tion of a database is called the database schema, which is
grams that enables users to create and maintain a database. The specified during database design and is not expected to change
DBMS is a general-purpose software system that facilitates the frequently.
processes of defining, constructing, manipulating and sharing The actual data in a database may change frequently, for exam-
databases among various users and applications. ple the student database changes every time we add a student or
1. Defining the database involves specifying the data types, enter a new grade for a student. The data in the database at a par-
structures, and constraints for the data to be stored in the ticular moment in time is called a database state or snapshot. It
database. is also called the current set of occurrences or instances in the
2. Constructing the database is the process of storing the data database.
itself on some storage medium that is controlled by the The distinction between database schema and database state is
DBMS very important. When we define a new database, we specify its
3. Manipulating a database includes such functions as querying database schema only to the DBMS. At this point, the correspond-
the database to retrieve specific data, updating the database ing database state is the empty state with no data. We get the ini-
to reflect changes. tial state of the database when the database is first loaded with
4. Sharing a database allows multiple users and programs to the initial data. The DBMS stores the description of the schema
access the database concurrently. constructs and constraints, also called the metadata in the DBMS
5. Fundamental characteristics of the database approach is that catalog so that DBMS software can refer to the schema whenever
it provides some level of data abstraction by hiding details of it needs. The schema is sometimes called the intension, and the
data storage that are not needed by users. database state an extension of the schema.
,4.4 | Unit 4 • Databases
Three-schema Architecture as entities. In general, there is no presumption that an entity
The goal of the three-schema architecture is to separate the is animate.
user applications and the physical database. 1. An object with a physical existence
Example: A particular person, car, house, employee.
Levels of Abstraction 2. An object with a conceptual existence
Example: A company, a job, a university course.
View 1 View 2 View 3 Each entity has attributes, the particular properties
that describe it.
Example: An employee entity can be described by
Conceptual schema
employee’s name, age, address, salary and job.
Physical schema
Entity Set
Set of entities of same type that shares the same properties.
Example: All persons, all companies etc.
Example: Entity sets of customer and loan
1. The external or view level includes a number of
external schemas or user views. Each external schema Table 1 Customer Entity Set
describes the part of the database that a particular Customer-id Cust-name Cust-street City
user group is interested in and hides the rest of the
C-143 John MG Road Sec.bad
database.
2. The conceptual level has a conceptual schema, which C-174 Mary SP Road Hyd.bad
describes the structure of the whole database for a C-183 Tony KD Road Sec.bad
community of users. The conceptual schema hides the
C-192 Satya SG Road Eluru
details of physical storage structures and concentrates
on describing entities, data types, relationships, user
operations, and constraints.
3. The internal level has an internal schema, which Table 2 Loan Entity Set
describes the physical storage structures of the Loan-no Amount
database. It describes the complete details of data L-30 $3000
storage and access paths for the database.
L-31 $4000
L-32 $3500
ER Model L-33 $4500
Entity relationship model is a popular high-level conceptual L-34 $5000
data model. This model and its variations are used for the
conceptual design of database applications, and many data- An entity is represented by a set of attributes and by a descrip-
base design tools employ its concepts. ER model describes tive properties possessed by all members of an entity set.
data as entities, relationships and attributes. The basic object
that the ER model represents is an entity.
Types of Attributes
1. Simple versus composite
Entity 2. Single valued versus multivalued
It is an object that exists and is distinguishable from other 3. Stored versus derived.
objects
(or) Composite attribute: Composite attributes can be divided
Entity is a “thing” in the real world with an independent into smaller subparts, which represent more basic attribute
existence. that has their own meaning (Figure 1).
(or) Example: A common example is Address, it can be broken
An entity is something that has a distinct, separate exist- down into a number of subparts such as street address,
ence, although it need not be a material existence. In par- city, postal code; street address is further broken down into
ticular, abstractions and legal functions are usually regarded Number, street Name and Apartment number.
, Chapter 1 • ER Model and Relational Model | 4.5
Address 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
Street address City State Zip entity in A (Figure 3).
a1 b1
a2 b2
Number Street House-No a3 b3
a4 b4
Figure 1 A hierarchy of composite attributes.
Street address is a composite attribute. Attributes that are Figure 3 One-to-one relationship set.
not divisible are called simple (or) atomic attributes.
One-to-many: An entity in A is associated with any number
Single-valued versus multivalued attributes: Most attributes of entities in B. But an entity in B is associated with at most
have a single value for a particular entity, such attributes are one entity in A (Figure 4).
called single-valued attribute.
b1
Example: Age is a single-valued attribute a1
b2
Multivalued attributes: An attribute can have a set of values a2 b3
for the same entity. a3 b4
Example: College degrees attribute for a person b5
Example: Name is also a multivalued attribute (Figure 2).
Figure 4 One-to-many relationship set.
Name Many-to-one: An entity in A is associated with at most one
entity in B. But an entity in B can be associated with any
First name Last name number of entities in A (Figure 5).
Middle name
a1
Figure 2 Multivalued attribute. b1
a2
Stored versus derived attributes: Two (or) more attribute a3 b2
values are related. a4 b3
a5
Example: Age can be derived from a person’s date of birth.
The age attribute is called derived attribute and is said to be Figure 5 Many-to-one relationship set.
derivable from the DOB attribute, which is called a stored
attribute. Many-to-many: An entity in A is associated with any num-
ber of entities in B. But an entity in B can be associated with
Domain: The set of permitted values for each attribute.
any number of entities in A (Figure 6).
Example: A person’s age must be in the domain {0-130}
a1 b1
a2 b2
Relationship Sets a3 b3
A relationship is an association among several entities. a4 b4
Relationship sets that involve two entity sets are binary.
Figure 6 Many-to-many relationship set.
Generally, most relationships in databases are binary.
Relationship sets may involve more than two entity sets. Example: One customer can have multiple accounts
Example: Employee of a bank may have responsibilities at Customer(c-Name) (Acc. no, Amount)
multiple braches, with different jobs at different branches, then Table 3 Example of One-to-many Relationship Set
there is a ternary relation between employee, job and branch.
Arun A-101 $3000
Mapping Cardinality Bunny A-102 $3500
For a binary relationship set, mapping cardinality must be: Kate A-103 $2000
1. One-to-one Mary A-104 $2500
2. One-to-many John A-105 $4000
3. Many-to-one
4. Many-to-many In Table 3, many-to-one relationship is not possible.
ER Model and Relational Model
LEARNING OBJECTIVES
Data model Entity types, entity sets and value sets
Schemas Weak entity set
Three-schema architecture Relational database
ER model NULL in tuples
Types of attributes Inherent constraint
Mapping cardinality Referential and entity integrity constraint
Complex attributes
intRodUCtion Data Model
A database is a collection of related data. By data, we mean facts A data model is a collection of concepts that can be used to describe
that can be recorded and that have implicit meaning. the structure of a database. It provides the necessary means to
achieve abstraction.
Example: Consider the names, telephone numbers and addresses
of the people. We can record this data in an indexed address book
and store it as Excel file on a hard drive using a personal computer. sChEMas
This is a collection of related data with an implicit meaning and In any data model, it is important to distinguish between the
hence is a database. description of the database and the database itself. The descrip-
A database management system (DBMS) is a collection of pro- tion of a database is called the database schema, which is
grams that enables users to create and maintain a database. The specified during database design and is not expected to change
DBMS is a general-purpose software system that facilitates the frequently.
processes of defining, constructing, manipulating and sharing The actual data in a database may change frequently, for exam-
databases among various users and applications. ple the student database changes every time we add a student or
1. Defining the database involves specifying the data types, enter a new grade for a student. The data in the database at a par-
structures, and constraints for the data to be stored in the ticular moment in time is called a database state or snapshot. It
database. is also called the current set of occurrences or instances in the
2. Constructing the database is the process of storing the data database.
itself on some storage medium that is controlled by the The distinction between database schema and database state is
DBMS very important. When we define a new database, we specify its
3. Manipulating a database includes such functions as querying database schema only to the DBMS. At this point, the correspond-
the database to retrieve specific data, updating the database ing database state is the empty state with no data. We get the ini-
to reflect changes. tial state of the database when the database is first loaded with
4. Sharing a database allows multiple users and programs to the initial data. The DBMS stores the description of the schema
access the database concurrently. constructs and constraints, also called the metadata in the DBMS
5. Fundamental characteristics of the database approach is that catalog so that DBMS software can refer to the schema whenever
it provides some level of data abstraction by hiding details of it needs. The schema is sometimes called the intension, and the
data storage that are not needed by users. database state an extension of the schema.
,4.4 | Unit 4 • Databases
Three-schema Architecture as entities. In general, there is no presumption that an entity
The goal of the three-schema architecture is to separate the is animate.
user applications and the physical database. 1. An object with a physical existence
Example: A particular person, car, house, employee.
Levels of Abstraction 2. An object with a conceptual existence
Example: A company, a job, a university course.
View 1 View 2 View 3 Each entity has attributes, the particular properties
that describe it.
Example: An employee entity can be described by
Conceptual schema
employee’s name, age, address, salary and job.
Physical schema
Entity Set
Set of entities of same type that shares the same properties.
Example: All persons, all companies etc.
Example: Entity sets of customer and loan
1. The external or view level includes a number of
external schemas or user views. Each external schema Table 1 Customer Entity Set
describes the part of the database that a particular Customer-id Cust-name Cust-street City
user group is interested in and hides the rest of the
C-143 John MG Road Sec.bad
database.
2. The conceptual level has a conceptual schema, which C-174 Mary SP Road Hyd.bad
describes the structure of the whole database for a C-183 Tony KD Road Sec.bad
community of users. The conceptual schema hides the
C-192 Satya SG Road Eluru
details of physical storage structures and concentrates
on describing entities, data types, relationships, user
operations, and constraints.
3. The internal level has an internal schema, which Table 2 Loan Entity Set
describes the physical storage structures of the Loan-no Amount
database. It describes the complete details of data L-30 $3000
storage and access paths for the database.
L-31 $4000
L-32 $3500
ER Model L-33 $4500
Entity relationship model is a popular high-level conceptual L-34 $5000
data model. This model and its variations are used for the
conceptual design of database applications, and many data- An entity is represented by a set of attributes and by a descrip-
base design tools employ its concepts. ER model describes tive properties possessed by all members of an entity set.
data as entities, relationships and attributes. The basic object
that the ER model represents is an entity.
Types of Attributes
1. Simple versus composite
Entity 2. Single valued versus multivalued
It is an object that exists and is distinguishable from other 3. Stored versus derived.
objects
(or) Composite attribute: Composite attributes can be divided
Entity is a “thing” in the real world with an independent into smaller subparts, which represent more basic attribute
existence. that has their own meaning (Figure 1).
(or) Example: A common example is Address, it can be broken
An entity is something that has a distinct, separate exist- down into a number of subparts such as street address,
ence, although it need not be a material existence. In par- city, postal code; street address is further broken down into
ticular, abstractions and legal functions are usually regarded Number, street Name and Apartment number.
, Chapter 1 • ER Model and Relational Model | 4.5
Address 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
Street address City State Zip entity in A (Figure 3).
a1 b1
a2 b2
Number Street House-No a3 b3
a4 b4
Figure 1 A hierarchy of composite attributes.
Street address is a composite attribute. Attributes that are Figure 3 One-to-one relationship set.
not divisible are called simple (or) atomic attributes.
One-to-many: An entity in A is associated with any number
Single-valued versus multivalued attributes: Most attributes of entities in B. But an entity in B is associated with at most
have a single value for a particular entity, such attributes are one entity in A (Figure 4).
called single-valued attribute.
b1
Example: Age is a single-valued attribute a1
b2
Multivalued attributes: An attribute can have a set of values a2 b3
for the same entity. a3 b4
Example: College degrees attribute for a person b5
Example: Name is also a multivalued attribute (Figure 2).
Figure 4 One-to-many relationship set.
Name Many-to-one: An entity in A is associated with at most one
entity in B. But an entity in B can be associated with any
First name Last name number of entities in A (Figure 5).
Middle name
a1
Figure 2 Multivalued attribute. b1
a2
Stored versus derived attributes: Two (or) more attribute a3 b2
values are related. a4 b3
a5
Example: Age can be derived from a person’s date of birth.
The age attribute is called derived attribute and is said to be Figure 5 Many-to-one relationship set.
derivable from the DOB attribute, which is called a stored
attribute. Many-to-many: An entity in A is associated with any num-
ber of entities in B. But an entity in B can be associated with
Domain: The set of permitted values for each attribute.
any number of entities in A (Figure 6).
Example: A person’s age must be in the domain {0-130}
a1 b1
a2 b2
Relationship Sets a3 b3
A relationship is an association among several entities. a4 b4
Relationship sets that involve two entity sets are binary.
Figure 6 Many-to-many relationship set.
Generally, most relationships in databases are binary.
Relationship sets may involve more than two entity sets. Example: One customer can have multiple accounts
Example: Employee of a bank may have responsibilities at Customer(c-Name) (Acc. no, Amount)
multiple braches, with different jobs at different branches, then Table 3 Example of One-to-many Relationship Set
there is a ternary relation between employee, job and branch.
Arun A-101 $3000
Mapping Cardinality Bunny A-102 $3500
For a binary relationship set, mapping cardinality must be: Kate A-103 $2000
1. One-to-one Mary A-104 $2500
2. One-to-many John A-105 $4000
3. Many-to-one
4. Many-to-many In Table 3, many-to-one relationship is not possible.
