Abhishek_DBMS-ch2_Database_Management.ppsx

Chapter 2: DATA MODELING
 Use of High-Level Conceptual Data Models
 ER Diagrams
 Entity Sets
 Relationship Sets
 Design Issues
 Mapping Constraints
 Keys
 Extended E-R Features
 Design of an E-R Database Schema
 Specialization And Generalization
 Conceptual Object Modeling using UML Class Diagrams

What is a Data Model?
 Definition: precise description of the data content in a system
 Types of data models:
1. Conceptual: describes WHAT the system contains
2. Logical: describes HOW the system will be implemented, regardless
of the DBMS
3. Physical: describes HOW the system will be implemented using a
specific DBMS
Why do we need to create data models?
 To aid in the development of a sound database design that does not allow
anomalies or inconsistencies
 Goal: to create database tables that do not contain duplicate data values
that can become inconsistent

Explanation of Data Model
 Many data models have been proposed, and we can categorize them
according to the types of concepts they use to describe the database
structure.
 High-level or conceptual data models provide concepts that are close to the
way many users perceive data, whereas low-level or physical data models
provide concepts that describe the details of how data is stored in the
computer.
Concepts provided by low-level data models are generally meant for
computer specialists, not for typical end users. Between these two extremes
is a logical data models, which provide concepts that may be understood by
end users but that are not too far removed from the way data is organized
within the computer. Logical data models hide some details of data storage
but can be implemented on a computer system in a direct way.

Explanation of Data Model (Cont.)
The three level of data modelling:-
Conceptual data model
A conceptual data model identifies the highest-level relationships between the different entities.
Features of conceptual data model include:
 · Includes the important entities and the relationships among them.
 · No attribute is specified.
 · No primary key is specified.
Logical data model
A logical data model describes the data in as much detail as possible, without regard to how
they will be physical implemented in the database.
Features of a logical data model include:
 · Includes all entities and relationships among them.
 · All attributes for each entity are specified.
 · The primary key for each entity is specified.
 · Foreign keys (keys identifying the relationship between different entities) are specified.
 · Normalization occurs at this level.
The steps for designing the logical data model are as follows:
1. Specify primary keys for all entities.
2. Find the relationships between different entities.
3. Find all attributes for each entity.
4. Resolve many-to-many relationships.
5. Normalization.

Physical data model
Physical data model represents how the model will be built in the database. A physical
database model shows all table structures, including column name, column data type, column
constraints, primary key, foreign key, and relationships between tables.
Features of a physical data model include:
 Specification all tables and columns.
 Foreign keys are used to identify relationships between tables.
 De-normalization may occur based on user requirements.
 Physical considerations may cause the physical data model to be quite different from the
logical data model.
 Physical data model will be different for different RDBMS. For example, data type for a
column may be different between Oracle, DB2 etc.
The steps for physical data model design are as follows:
1. Convert entities into tables.
2. Convert relationships into foreign keys.
3. Convert attributes into columns.
4. Modify the physical data model based on physical constraints / requirements.

Conceptual Model Design

Logical Model Design

Physical Model Design

Summery :
We can see that the complexity increases from conceptual to logical to physical. This is why
we always first start with the conceptual data model (so we understand at high level what are
the different entities in our data and how they relate to one another), then move on to the
logical data model (so we understand the details of our data without worrying about how they
will actually implemented), and finally the physical data model (so we know exactly how to
implement our data model in the database of choice). In a data warehousing project,
sometimes the conceptual data model and the logical data model are considered as a single
deliverable.

There are a number of different types of database management systems (DBMS),
also referred to as DBMS models. Each one represents a somewhat different
approach to organizing data in a systematic manner.
They include:
1. Flat file
2. Hierarchical DBMS
3. Network DBMS
4. Relational DBMS
5. Object-oriented DBMS
Types Of Data Models
Flat File
The most basic way to organize data is as a flat file. We can think of this as a single table with
a large number of records and fields. Everything we need is stored in this table, or flat file.
Think of a database of customers. Everything we want to know about the customers is stored in
this one table. We start off with a single record for every customer. A customer places an order
and we enter this in one of the fields. We continue entering customer orders in this way.
What if the same customer places a second order? Do we enter this as a new record in the
table, or do we add a field to the existing record for this customer?
There is no single best answer for this. The key point here is that a flat file is quite limited since
it provides very little in terms of structure. In fact, it is so simple that we could argue it is not a
DBMS at all.

Types Of Data Models
Flat File

Hierarchical DBMS
In a hierarchical DBMS one data item is subordinate to another one. This is called a parent-
child relationship. The hierarchical data model organizes data in a tree-like structure.
One of the rules of a hierarchical database is that a parent can have multiple children, but a
child can only have one parent.
For example, think of an online store that sells many different products. The entire product
catalog would be the parent, and the various types of products, such as books, electronics,
etc., would be the children. Each type of product can have its own children categories.
For example, books could be broken up into fiction and non-fiction. Each of these categories
can be broken up into subcategories. We can continue like this by listing individual authors and
then the individual book titles.
This is a rather simple way to represent data, but it is very efficient. This model works best for
data that is inherently hierarchical in nature. Many datasets cannot easily be organized in this
manner and require a more complex approach.
For example, in the case of the product catalog, what if a book falls into more than one
category? Or what if one author has written several books but also published an audio CD of
one of her books? This is where the hierarchical model breaks down.
Types Of Data Models (Cont..)

Hierarchical DBMS

Network DBMS
In a network DBMS every data item can be related to many others ones. The
database structure is like a graph. This is similar to the hierarchical model and also
provides a tree-like structure. However, a child is allowed to have more than one
parent.
In the example of the product catalog, a book could fall into more than one category.
The structure of a network database becomes more like a cobweb of connected
elements.
For example, consider an organization with an employee database. For each
employee there are different pieces of data, such as their name, address, telephone
number, social security number and job function. Different units in the organization
need different levels of access.
For example, the human resources department needs to have access to the social
security information for each employee so they can take care of tax deductions and
set up benefits. This is somewhat sensitive information, so other departments do not
need access to this part of the database. All the pieces of data are connected in a
network that implements these rules.
While conceptually relatively simple, this database structure can quickly become
very complicated.

Network DBMS

Relational DBMS
In a relational DBMS all data are organized in the form of tables. This DBMS model emerged in
the 1970s and has become by far the most widely used type of DBMS. Most of the DBMS
software developed over the past few decades uses this model. In a table, each row represents
a record, also referred to as an entity. Each column represents a field, also referred to as
an attribute of the entity.
A relational DBMS uses multiple tables to organize the data. Relationships are used to link the
various tables together. Relationships are created using a field that uniquely identifies each
record. For example, for a table of books, you could use the ISBN number since there are no
two books with the same ISBN. For a table of authors, you would create a unique Author ID to
identify each individual author.
Consider a relational database of books and authors. The first table is a table of authors. Each
author is identified by a unique author ID, and the table also contains their name and contact
information. The second table is a table of books. Each book is identified by its ISBN number,
and the table also contains the book's title, the publisher and the author ID associated with the
author of the book.
What makes a relational database so effective is that you can link these tables together. In this
example you would use the author ID field to do this. For example, you can store multiple books
written by the same author or multiple authors for the same book. The detailed information for
each author and each book is only stored once and not duplicated in both tables. Yet all the
information you need can be accessed using the table relationship.

Relational DBMS

Object Oriented Data Model
Object Model aims to reduce the overhead of converting information representation
in the database to an application specific representation. Unlike a traditional
database, an object model allows for data persistence and storage by storing
objects in the databases. The relationships between various objects are inherent
in the structure of the objects. This is mainly used for complex data structures such
as 2D and 3D graphics which must otherwise be flattened before storage in a
relational database.
A data model is a logic organization of the real world objects (entities), constraints
on them, and the relationships among objects. A DB language is a concrete syntax
for a data model. A DB system implements a data model.
A core object-oriented data model consists of the following basic object-oriented
concepts:
(1) Object and object identifier: Any real world entity is uniformly modeled as an
object (associated with a unique id: used to pinpoint an object to retrieve).
(2) Attributes and methods: every object has a state (the set of values for the
attributes of the object) and a behavior (the set of methods - program code - which
operate on the state of the object). The state and behavior encapsulated in an
object are accessed or invoked from outside the object only through explicit
message passing.

[ An attribute is an instance variable, whose domain may be any class: user-defined
or primitive. A class composition hierarchy (aggregation relationship) is orthogonal to
the concept of a class hierarchy. The link in a class composition hierarchy may form
cycles. ]
(3) Class: a means of grouping all the objects which share the same set of attributes
and methods. An object must belong to only one class as an instance of that class
(instance-of relationship). A class is similar to an abstract data type. A class may
also be primitive (no attributes), e.g., integer, string, Boolean.
(4) Class hierarchy and inheritance: derive a new class (subclass) from an
existing class (super class). The subclass inherits all the attributes and methods of
the existing class and may have additional attributes and methods. single inheritance
(class hierarchy) vs. multiple inheritance (class lattice).

Characteristic Hierarchical model Network model Relational model
Data structure
When we go through the structure of the hierarchical model we can identify that it used a method for storing data in a database that
looks like a family tree with one root and a number of branches or subdivisions. That's why we can say that record types are organized
in the form of a rooted tree. But in network model we can identify multiple branches emanating from one or more nodes. So some
times it is like a like several trees which share branches. In relational model is that of a relation. Relation is a two-dimensional table.
So the table can be used to represent some entity information or some relationship between them.
Data structure
One to many or one to one
relationships
Allowed the network model
to support many to many
relationships
One to One,
One to many, Many to many
relationships
In hierarchical model only one-to-many or one-to-one relationships can be exist. But in network data model makes it possible to map
many to many relationships In relational each record can have multiple parents and multiple child records. In effect, it supports many to
many relationships
Data structure
Based on parent child
relationship
A record can have many
parents as well as many
children.
Based on relational data structures
In hierarchical model relationship based in terms of parent child. So a child may only have one parent but a parent can have multiple
children. But in network data model a record can have many parents as well as many children. Relational data model is based on
relational data structures

Data manipulation
Does not provide an
independent stand alone
query interface
CODASYL (Conference on
Data Systems Languages)
Relational databases are what brings many
sources into a common query (such as
SQL)
In relational database it use powerful operations such as SQL languages or query by example are used to manipulate data stored in the
database But in hierarchical data model it does not
provide an independent stand alone query interface while network model uses CODASYL
Data manipulation
retrieve algorithms are
complex and asymmetric
Retrieve algorithms are complex and symmetric
Retrieve algorithms are
simple and symmetric
In hierarchical data model and network model retrieve algorithms are complex and symmetric But in relational data model retrieve
algorithms are simple and symmetric
Data integrity
Cannot insert the
information of a child who
does not have any parent.
Does not suffer form any insertion anomaly.
Does not suffer from any
insert anomaly.
In hierarchical data model we cannot insert the information of a child who does not have any parent. But in network model does not
suffer form any insertion anomaly. relational model does not suffer from any insert anomaly

Data integrity
Multiple occurrences of child
records which lead to
problems of inconsistency
during the update operation
Free from update anomalies.
Free form update anomalies
In network model it is free from update anomalies because there is only a single occurrence for each record set.In relational model it
also free form update anomalies because it removes the redundancy of data by proper designing through normalization process. But in
hierarchical model there are multiple occurrences of child records. which lead to problems of inconsistency during the update operation
Data intergirty
Deletion of parent results in
deletion of child records
Free from delete anomalies
Free from delete anomalies
In hierarchical model it is based on parent child relationship and deletion of parent results in deletion of child records .But in network
model and in relational model it is free from deletion anomalies. Because information is stored in different tables.

Entity Sets
 A database can be modeled as:
 a collection of entities,
 relationship among entities.
 An entity is an object that exists and is distinguishable from other
objects.
 Example: specific person, company, event, plant
 Entities have attributes
 Example: people have names and addresses
 An entity set is a set of entities of the same type that share the
same properties.
 Example: set of all persons, companies, trees, holidays

Entity Sets customer and loan
cutomer-id Customer-
name
Customer-
street
Customer-
city
Loan-
number
Amo
unt

Attributes
 An entity is represented by a set of attributes, that is descriptive
properties possessed by all members of an entity set.
 Domain – the set of permitted values for each attribute
 Attribute types:
 Simple and composite attributes.
 Single-valued and multi-valued attributes
 E.g. multivalued attribute: phone-numbers
 Derived attributes
 Can be computed from other attributes
 E.g. age, given date of birth
Example:
customer = (customer-id, customer-name,
customer-street, customer-city)
loan = (loan-number, amount)

Relationship Sets
 A relationship is an association among several entities
Example:
Hayes depositor A-102
customer entity relationship set account entity
 A relationship set is a mathematical relation among n  2
entities, each taken from entity sets
{(e1, e2, … en) | e1  E1, e2  E2, …, en  En}
where (e1, e2, …, en) is a relationship
 Example:
(Hayes, A-102)  depositor

Relationship Sets (Cont.)
 An attribute can also be property of a relationship set.
 For instance, the depositor relationship set between entity sets
customer and account may have the attribute access-date

Degree of a Relationship Set
 Refers to number of entity sets that participate in a relationship
set.
 Relationship sets that involve two entity sets are binary (or degree
two). Generally, most relationship sets in a database system are
binary.
 Relationship sets may involve more than two entity sets.
 Relationships between more than two entity sets are rare. Most
relationships are binary.
E.g. Suppose employees of a bank may have jobs (responsibilities)
at multiple branches, with different jobs at different branches. Then
there is a ternary relationship set between entity sets employee, job
and branch

Mapping Cardinalities
 Express the number of entities to which another entity can be
associated via a relationship set.
 Most useful in describing binary relationship sets.
 For a binary relationship set the mapping cardinality must be
one of the following types:
 One to one
 One to many
 Many to one
 Many to many

One to one One to many
Note: Some elements in A and B may not be mapped to any
elements in the other set

Many to one Many to many
Note: Some elements in A and B may not be mapped to any
elements in the other set

Mapping Cardinalities affect ER Design
 Can make access-date an attribute of account, instead of a
relationship attribute, if each account can have only one customer
 I.e., the relationship from account to customer is many to one,
or equivalently, customer to account is one to many

E-R Diagrams
 Rectangles represent entity sets.
 Diamonds represent relationship sets.
 Lines link attributes to entity sets and entity sets to relationship sets.
 Ellipses represent attributes
 Double ellipses represent multivalued attributes.
 Dashed ellipses denote derived attributes.
 Underline indicates primary key attributes

E-R Diagram With Composite, Multivalued, and
Derived Attributes

Relationship Sets with Attributes

Roles
 Entity sets of a relationship need not be distinct
 The labels “manager” and “worker” are called roles; they specify how
employee entities interact via the works-for relationship set.
 Roles are indicated in E-R diagrams by labeling the lines that connect
diamonds to rectangles. Role labels are optional, and are used to
clarify semantics of the relationship

Cardinality Constraints
 We express cardinality constraints by drawing either a directed
line (), signifying “one,” or an undirected line (—), signifying
“many,” between the relationship set and the entity set.
 E.g.: One-to-one relationship:
 A customer is associated with at most one loan via the relationship
borrower
 A loan is associated with at most one customer via borrower

One-To-Many Relationship
 In the one-to-many relationship a loan is associated with at most
one customer via borrower, a customer is associated with
several (including 0) loans via borrower

Many-To-One Relationships
 In a many-to-one relationship a loan is associated with several
(including 0) customers via borrower, a customer is associated
with at most one loan via borrower

Many-To-Many Relationship
 A customer is associated with several (possibly 0) loans
via borrower
 A loan is associated with several (possibly 0) customers
via borrower

Participation of an Entity Set in a
Relationship Set
 Total participation (indicated by double line): every entity in the entity
set participates in at least one relationship in the relationship set
 E.g. participation of loan in borrower is total
 every loan must have a customer associated to it via borrower
 Partial participation: some entities may not participate in any
relationship in the relationship set
 E.g. participation of customer in borrower is partial

Alternative Notation for Cardinality
Limits
 Cardinality limits can also express participation constraints

Keys
 An important constraint on the entities of an entity type is the key or uniqueness
constraint on attributes.
 A key is an attribute (also known as column or field) or a combination of attribute
that is used to identify records.
 Sometimes we might have to retrieve data from more than one table, in those
cases we require to join tables with the help of keys.
 The purpose of the key is to bind data together across tables without repeating
all of the data in every table
 Such an attribute is called a key attribute, and its values can be used to identify
each entity uniquely.
 For example, the Name attribute is a key of the COMPANY entity type because
no two companies are allowed to have the same name.

Types of Keys
(I) Super Key – An attribute or a combination of attribute that is used to identify the
records uniquely is known as Super Key. A table can have many Super Keys.
E.g. of Super Key
1 ID
2 ID, Name
3 ID, Address
4 ID, Department_ID
5 ID, Salary
6 Name, Address
7 Name, Address, Department_ID ………… So on as any combination which
can identify the records uniquely will be a Super Key.
(II) Candidate Key – It can be defined as minimal Super Key or irreducible Super
Key. In other words an attribute or a combination of attribute that identifies the
record uniquely but none of its proper subsets can identify the records uniquely.
E.g. of Candidate Key
1 Code
2 Name, Address

Types of Keys (Cont..)
For above table we have only two Candidate Keys (i.e. Irreducible Super Key) used
to identify the records from the table uniquely. Code Key can identify the record
uniquely and similarly combination of Name and Address can identify the record
uniquely, but neither Name nor Address can be used to identify the records uniquely
as it might be possible that we have two employees with similar name or two
Employees from the same house.
(III) Primary Key – A Candidate Key that is used by the database designer for
unique identification of each row in a table is known as Primary Key. A Primary
Key can consist of one or more attributes of a table.
E.g. of Primary Key - Database designer can use one of the Candidate Key as a
Primary Key. In this case we have “Code” and “Name, Address” as Candidate
Key, we will consider “Code” Key as a Primary Key as the other key is the
combination of more than one attribute.

(IV) Foreign Key – A foreign key is an attribute or combination of attribute in one
base table that points to the candidate key (generally it is the primary key) of
another table. The purpose of the foreign key is to ensure referential integrity of
the data i.e. only values that are supposed to appear in the database are
permitted.
E.g. of Foreign Key – Let consider we have another table i.e. Department Table
with Attributes “Department_ID”, “Department_Name”, “Manager_ID”,
”Location_ID” with Department_ID as an Primary Key. Now the Department_ID
attribute of Employee Table (dependent or child table) can be defined as the
Foreign Key as it can reference to the Department_ID attribute of the
Departments table (the referenced or parent table), a Foreign Key value must
match an existing value in the parent table or be NULL.
(V) Composite Key – If we use multiple attributes to create a Primary Key then that
Primary Key is called Composite Key (also called a Compound Key or
Concatenated Key).
E.g. of Composite Key, if we have used “Name, Address” as a Primary Key then
it will be our Composite Key.

(VI) Alternate Key – Alternate Key can be any of the Candidate Keys except for the
Primary Key.
E.g. of Alternate Key is “Name, Address” as it is the only other Candidate Key
which is not a Primary Key.
(VII) Secondary Key – The attributes that are not even the Super Key but can be still
used for identification of records (not unique) are known as Secondary Key.
E.g. of Secondary Key can be Name, Address, Salary, Department_ID etc. as
they can identify the records but they might not be unique.

Keys for Relationship Sets
 The combination of primary keys of the participating entity sets
forms a super key of a relationship set.
 (customer-id, account-number) is the super key of depositor
 NOTE: this means a pair of entity sets can have at most one
relationship in a particular relationship set.
 E.g. if we wish to track all access-dates to each account by each
customer, we cannot assume a relationship for each access. We
can use a multivalued attribute though
 Must consider the mapping cardinality of the relationship set
when deciding the what are the candidate keys
 Need to consider semantics of relationship set in selecting the
primary key in case of more than one candidate key

E-R Diagram with a Ternary Relationship

Cardinality Constraints on Ternary
Relationship
 We allow at most one arrow out of a ternary (or greater degree)
relationship to indicate a cardinality constraint
 E.g. an arrow from works-on to job indicates each employee works
on at most one job at any branch.
 If there is more than one arrow, there are two ways of defining the
meaning.
 E.g a ternary relationship R between A, B and C with arrows to B and C
could mean:
 1. each A entity is associated with a unique entity from B and C or
 2. each pair of entities from (A, B) is associated with a unique C entity,
and each pair (A, C) is associated with a unique B
 Each alternative has been used in different formalisms
 To avoid confusion we outlaw more than one arrow

Weak Entity Sets
 An entity set that does not have a primary key is referred to as a
weak entity set.
 The existence of a weak entity set depends on the existence of
an identifying entity set
 it must relate to the identifying entity set via a total, one-to-many
relationship set from the identifying to the weak entity set
 Identifying relationship depicted using a double diamond
 The discriminator (or partial key) of a weak entity set is the set of
attributes that distinguishes among all the entities of a weak
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.

Weak Entity Sets (Cont.)
 We depict a weak entity set by double rectangles.
 We underline the discriminator of a weak entity set with a dashed
line.
 payment-number – discriminator of the payment entity set
 Primary key for payment – (loan-number, payment-number)

Weak Entity Sets (Cont.)
 Note: the primary key of the strong entity set is not explicitly stored
with the weak entity set, since it is implicit in the identifying
relationship.
 If loan-number were explicitly stored, payment could be made a
strong entity, but then the relationship between payment and loan
would be duplicated by an implicit relationship defined by the attribute
loan-number common to payment and loan

More Weak Entity Set Examples
 In a university, a course is a strong entity and a course-offering
can be modeled as a weak entity
 The discriminator of course-offering would be semester (including
year) and section-number (if there is more than one section)
 If we model course-offering as a strong entity we would model
course-number as an attribute.
Then the relationship with course would be implicit in the course-
number attribute

Specialization
 Top-down design process; we designate sub groupings within an
entity set that are distinctive from other entities in the set.
 These sub groupings become lower-level entity sets that have
attributes or participate in relationships that do not apply to the
higher-level entity set.
 Depicted by a triangle component labeled ISA (E.g. customer “is
a” person).
 Attribute inheritance – a lower-level entity set inherits all the
attributes and relationship participation of the higher-level entity
set to which it is linked.

Generalization
 A bottom-up design process – combine a number of entity sets
that share the same features into a higher-level entity set.
 Specialization and generalization are simple inversions of each
other; they are represented in an E-R diagram in the same way.
 The terms specialization and generalization are used
interchangeably.

Specialization and Generalization
(Contd.)
 Can have multiple specializations of an entity set based on
different features.
 E.g. permanent-employee vs. temporary-employee, in addition to
officer vs. secretary vs. teller
 Each particular employee would be
 a member of one of permanent-employee or temporary-employee,
 and also a member of one of officer, secretary, or teller
 The ISA relationship also referred to as super class - subclass
relationship

Design Constraints on a
Specialization/Generalization
 Constraint on which entities can be members of a given lower-level
entity set.
 condition-defined
 E.g. all customers over 65 years are members of senior-citizen entity
set; senior-citizen ISA person.
 user-defined
 Constraint on whether or not entities may belong to more than one
lower-level entity set within a single generalization.
 Disjoint
 an entity can belong to only one lower-level entity set
 Noted in E-R diagram by writing disjoint next to the ISA triangle
 Overlapping
 an entity can belong to more than one lower-level entity set

Design Constraints on a
Specialization/Generalization (Contd.)
 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 within a generalization.
 total : an entity must belong to one of the lower-level entity sets
 partial: an entity need not belong to one of the lower-level entity
sets

Generalization Vs Specialization
1. Generalization is the result of taking the union of two or more lower
level entity sets to produce a higher level entity sets.
specialization is the results of taking subsets of a higher level entity
set to form a lower level entity sets.
2. In generalization, each higher level entity must also be a lower level
entity.
In specialization, some higher level entities may not have lower-level
entity sets at all.
3. Specialization is a Top Down process where as Generalization
is Bottom Up process.

Aggregation
 Consider the ternary relationship works-on, which we saw earlier
 Suppose we want to record managers for tasks performed by an
employee at a branch

Aggregation (Cont.)
 Relationship sets works-on and manages represent overlapping
information
 Every manages relationship corresponds to a works-on relationship
 However, some works-on relationships may not correspond to any manages
relationships
 So we can’t discard the works-on relationship
 Eliminate this redundancy via aggregation
 Treat relationship as an abstract entity
 Allows relationships between relationships
 Abstraction of relationship into new entity
 Without introducing redundancy, the following diagram represents:
 An employee works on a particular job at a particular branch
 An employee, branch, job combination may have an associated manager

E-R Design Decisions
 The use of an attribute or entity set to represent an object.
 Whether a real-world concept is best expressed by an entity set
or a relationship set.
 The use of a ternary relationship versus a pair of binary
relationships.
 The use of a strong or weak entity set.
 The use of specialization/generalization – contributes to
modularity in the design.
 The use of aggregation – can treat the aggregate entity set as a
single unit without concern for the details of its internal structure.

E-R Diagram for a Banking Enterprise

Summary of Symbols Used in E-R
Notation

UML
 UML: Unified Modeling Language
 UML has many components to graphically model different
aspects of an entire software system
 UML Class Diagrams correspond to E-R Diagram, but several
differences.

Summary of UML Class Diagram Notation

UML Class Diagrams (Contd.)
 Entity sets are shown as boxes, and attributes are shown within the
box, rather than as separate ellipses in E-R diagrams.
 Binary relationship sets are represented in UML by just drawing a
line connecting the entity sets. The relationship set name is written
adjacent to the line.
 The role played by an entity set in a relationship set may also be
specified by writing the role name on the line, adjacent to the entity
set.
 The relationship set name may alternatively be written in a box,
along with attributes of the relationship set, and the box is
connected, using a dotted line, to the line depicting the relationship
set.
 Non-binary relationships drawn using diamonds, just as in ER
diagrams

UML Class Diagram Notation (Cont.)
*Note reversal of position in cardinality constraint depiction
*Generalization can use merged or separate arrows independent
of disjoint/overlapping
overlapping
disjoint

UML Class Diagrams (Contd.)
 Cardinality constraints are specified in the form l..h, where l denotes
the minimum and h the maximum number of relationships an entity
can participate in.
 Beware: the positioning of the constraints is exactly the reverse of the
positioning of constraints in E-R diagrams.
 The constraint 0..* on the E2 side and 0..1 on the E1 side means that
each E2 entity can participate in at most one relationship, whereas
each E1 entity can participate in many relationships; in other words,
the relationship is many to one from E2 to E1.
 Single values, such as 1 or * may be written on edges; The single
value 1 on an edge is treated as equivalent to 1..1, while * is
equivalent to 0..*.

Reduction of an E-R Schema to Tables
 Primary keys allow entity sets and relationship sets to be expressed
uniformly as tables which represent the contents of the database.
 A database which conforms to an E-R diagram can be represented by
a collection of tables.
 For each entity set and relationship set there is a unique table which
is assigned the name of the corresponding entity set or relationship
set.
 Each table has a number of columns (generally corresponding to
attributes), which have unique names.
 Converting an E-R diagram to a table format is the basis for deriving
a relational database design from an E-R diagram.

Representing Entity Sets as Tables
 A strong entity set reduces to a table with the same attributes.

Composite and Multivalued Attributes
 Composite attributes are flattened out by creating a separate attribute
for each component attribute
 E.g. given entity set customer with composite attribute name with
component attributes first-name and last-name the table corresponding to
the entity set has two attributes
name.first-name and name.last-name
 A multivalued attribute M of an entity E is represented by a separate
table EM
 Table EM has attributes corresponding to the primary key of E and an
attribute corresponding to multivalued attribute M
 E.g. Multivalued attribute dependent-names of employee is represented
by a table
employee-dependent-names( employee-id, dname)
 Each value of the multivalued attribute maps to a separate row of the
table EM
 E.g., an employee entity with primary key John and
dependents Johnson and Smith maps to two rows:
(John, Johnson) and (John, Smith)

Representing Weak Entity Sets
 A weak entity set becomes a table that includes a column for
the primary key of the identifying strong entity set

Representing Relationship Sets as
Tables
 A many-to-many relationship set is represented as a table with columns
for the primary keys of the two participating entity sets, and any
descriptive attributes of the relationship set.
 E.g.: table for relationship set borrower

Redundancy of Tables
 Many-to-one and one-to-many relationship sets that are total
on the many-side can be represented by adding an extra
attribute to the many side, containing the primary key of the
one side
 E.g.: Instead of creating a table for relationship account-
branch, add an attribute branch to the entity set account

Redundancy of Tables (Cont.)
 For one-to-one relationship sets, either side can be chosen to act
as the “many” side
 That is, extra attribute can be added to either of the tables
corresponding to the two entity sets
 If participation is partial on the many side, replacing a table by an
extra attribute in the relation corresponding to the “many” side
could result in null values
 The table corresponding to a relationship set linking a weak
entity set to its identifying strong entity set is redundant.
 E.g. The payment table already contains the information that would
appear in the loan-payment table (i.e., the columns loan-number and
payment-number).

Representing Specialization as Tables
 Method 1:
 Form a table for the higher level entity
 Form a table for each lower level entity set, include primary key of
higher level entity set and local attributes
table table attributes
personname, street, city
customername, credit-rating
employeename, salary
 Drawback: getting information about, e.g., employee requires
accessing two tables

Representing Specialization as Tables
(Cont.)
 Method 2:
 Form a table for each entity set with all local and inherited
attributes
table table attributes
personname, street, city
customername, street, city, credit-rating
employee name, street, city, salary
 If specialization is total, table for generalized entity (person) not
required to store information
 Can be defined as a “view” relation containing union of
specialization tables
 But explicit table may still be needed for foreign key constraints
 Drawback: street and city may be stored redundantly for persons
who are both customers and employees

Relations Corresponding to
Aggregation
 To represent aggregation, create a table containing
 primary key of the aggregated relationship,
 the primary key of the associated entity set
 Any descriptive attributes

Relations Corresponding to
Aggregation (Cont.)
 E.g. to represent aggregation manages between relationship
works-on and entity set manager, create a table
manages(employee-id, branch-name, title, manager-name)
 Table works-on is redundant provided we are willing to store
null values for attribute manager-name in table manages

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.
 y is a dominant entity (in example below, loan)
 x is a subordinate entity (in example below, payment)
loan-payment payment
loan
If a loan entity is deleted, then all its associated payment entities
must be deleted also.

Abhishek_DBMS-ch2_Database_Management.ppsx

Recommended

More Related Content

Similar to Abhishek_DBMS-ch2_Database_Management.ppsx (20)

Recently uploaded (20)

Abhishek_DBMS-ch2_Database_Management.ppsx