FUNDAMENTALS OF DATABASE

SYSTEMS

CHAPTER 1 Introduction

Prepared By:-
Henok Ephrem(M.sc)
Page 1 of 146
Introduction to Database
 data
Data can be defined as any type of representation of an object or an
event. Representation could be in the form of,number, text symbols, and
voice, static or dynamic image and so on
 Information:-Information can be defined as a processed or summarized
data for a particular analysis or decision making.
Digital computers store data using two state components. With two such
states, we can represent exactly two different values. These two values
correspond to the two digits used by the binary number system or base 2
thatrequires just two symbols, _ 0_ 1, but the pattern remains the same as
for decimal. The digits are termed binary
digits. bits.

Page 2 of 146
Con’t

 Database:

Database is a stored collection of logically related data

- Database is a repository collection of computerized data file
- Database is a collection of logically related data where those logically
related data comprises entities, attribute relationships and business rules of
an organization information

Page 3 of 146
Con’t
 - Database contains the description of the data called
meta data - Each Database application will perform
the combination creating, reading, updating and
deleting data - Database designed once and used
simultaneously by many users.
Database - Is a shared collection of logically related
data and description of this data which designed to
meet the information needs of an organization - Is a
logically coherent collection of data with some inherent
meaning

Page 4 of 146
Advantages of database

 Control of data redundancy – database approach does not

eliminate redundancy entirely but can control the amount of
redundancy inherent the database. sometimes it is necessary to
duplicate key data items to model relationship
 Data consistency - by reducing the amount of redundancy we
are reducing the risk of inconsistencies
 Sharing of data – a database belongs to the entire
organization and can be shared by all authorized users
 Improved data integrity – express in terms of constraint on a
single record and it will increase the validity and consistency
of the data.

Page 5 of 146
Con’t
 Improved security – protection of the database from unauthorized access
 Enforcement of standards – again integrations allows the database
administrator to define necessary standards
 Increase productivity – it mostly use the fourth generation language
 Improved maintenance through data independence –
 Improved data accessibility and responsiveness – as a result of
integration data across subsystems is directly accessible to the end users
 Improved backup and recovery services – facilitate and minimize the
amount of processing that can be lost following a failure
 Increase concurrency – allow to access the same file simultaneously due to
integrity of information

Page 6 of 146
Disadvantage of database
 Sense of ownership and responsibility easily lost
 DB may become target of security breaches
 Cost incurred to develop DBMS
 Higher impact of failure
 Complexity

Page 7 of 146
Con’t
 Metadata:-
is "data [information] that provides information about other
data". Three distinct types of metadata exist: structural
metadata, descriptive metadata, and administrative
metadata.
Structural metadata is data about the containers of data. For
instance a "book" contains data, and data about the
book is metadata about that container of data.
Descriptive metadata uses individual instances of application
data or the data content.
For example, author, date created and date modified and file
size are examples of very basic document metadata.

Page 8 of 146
Con’t
 Database Management System(DBMS ):-
- is a software package designed to store and manage
database - is a software system that allows users to
define, create and maintain a database and provide
controlled access to the data. - consists of a collection of
interrelated data and set of programs to access that data
- it is a software that is helpful in maintaining and utilizing
a database - are system in which interpretation and
storage of information are of primary importance

Page 9 of 146
Con’t

Page 10 of 146
Con’t

Page 11 of 146
Con’t
 - allows users to define the database usually through
data definition language ( DDL ) which specify data
type structure and constraints on the data to be stored
in database
 - it allows users to insert, update delete and retrieve
data from the database through data manipulation
language ( DML)
 - it provides controlled access ( security, integrity,
concurrency, recovery …)

Page 12 of 146
Components of the DBMS Environment

Major components of DBMS environment are:-

Hardware : C/S machine, Network device
machine level
Software : 4GL nonprocedural
Data :-A bridge between machine and human
Procedures
People Human level requirements

Page 13 of 146
data model

 Data modeling is often the first step

in database design and object oriented as the
designers first create a conceptual model of how
data items relate to each other.
 Data modeling involves a progression from
conceptual model to logical model to physical
schema.

Page 14 of 146
Con’t
 DBMS example
SQL server
Oracle
Mysql
Microsoft Access

Page 15 of 146
Entities

 An entity is a thing or an object in the real world

that is distinguishable from all other objects
 An entity may be concrete (a person or a book, for
example) or abstract (like or a concept like Account
in a bank)
 An entity set is a set of entities of the same type
(e.g. all persons having an account at a bank)

Page 16 of 146
Relationships

 A relationship is an association between two or more entities

 A relationship set is a set of relationships of the same type
Eg. CUSTOMER –ACCOUNT
 to denote the association between customers and accounts
(degree 2)
Class schedule could be relate INSTRACTOR-SECTION-
COURSE and ROOM entities.( degree 4)
 the number of entity sets that participate in a relationship
set is know as the degree of the relationship set

Page 17 of 146
Relational databases

 A relational database usually contains many

relations, with tuples in relations that are related in
various ways.

 Read about Relational Database

Page 18 of 146
Traditional File-Processing systems

 Before DBMS was invented, Information was stored

using File Processing System. In this System, data is
stored in permanent system files (secondary
Storage). Different application programs are
written to extract data from these files and to add
record to these files. But, There are Number of
disadvantages in using File Processing System, to
store the data.

Page 19 of 146
Con’t
 - A collection of application programs that perform
services for end users, but each program defines
and manages its own data.
 - Each and every subsystem of information systems
will have its own set files. As a result there will be a
duplication of data b/n various subsystems of the
information systems.

Page 20 of 146
Drawbacks of conventional file
based approach
 Uncontrolled redundancy of data – due to
decentralized approach taken by each department
a file based approach lead to uncontrolled
duplication of data and this will lead
 Additional disk space
 Inconsistency of data integrity
 It cost time and money

Page 21 of 146
Con’t
 Data dependency – means the application program
depend on the data
 Incompatible file format – the data depends on the
application programming language and this incompatibility
of such files makes them difficult to process jointly.
 Inconsistency of data – a particular data element will
enter differently in different files.
 Inflexibility – problem to provide a desired result as user
demand
 Limited data sharing – due to decentralized data storing
mechanism there is less probability of data sharing

Page 22 of 146
Con’t
 Poor enforcement of standards - different application
are developed by different group independentl
 Excessive program maintenance – due to redundancy
recording system there will be also redundancy of
maintenance here and there and this take excessive
resources
 Low programmer productivity – having decentralized
approach the programmer efficiency will be less

Page 23 of 146
Roles in the database environment

 Data and Database Administrators

 Database designers
 Application programmers
 End-users

Page 24 of 146
Con’t
 Database Administrator - a person having a
centralized control over data and program
accessing that data - focused on management of
technical aspects of the database system such as o
control the DB environment o standardize the use of
DB and associated software o support the
development and maintenance of DB application o
ensure all documentation related to standards and
implementation is up-todate

Page 25 of 146
Con’t
 Database Designer o either logical or physical o
logical designer is concerned with identifying the
data relationship between the data constraints on
the data that is to be stored in the DB o physical
designer takes the logical data model and decides
the way in which it can be physically implemented

Page 26 of 146
Con’t
 Application program o An program module which
provides the interface between the low level data
stored in database application program and
queries submitted to the system o DML, integrity
enforcement, security, backup and recovery

Page 27 of 146
Con’t
 End users Individuals who interact with the
database to carryout business responsibilities
Sophisticated user – includes engineers, scientist,
business analyst, and others who thoroughly
familiarize themselves with the facilities of the
DBMS so as to implement their applications to meet
their complex requirements.

Page 28 of 146
Con’t
 Naïve user – their main job function revolves around
constantly querying and updating the database using
standard types of queries and updates that have been
carefully programmed and tested such as bank tellers
check account balances and post withdrawals and
deposits. Reservation clerks for airlines, hotels and car
rental companies check availability for a given request
and make reservations.

 interact with system using a permanent application

program

Page 29 of 146
Review questions

1. Define the following 2. Discuss the main characteristics

terms: data, database, DBMS, of the database approach and how
database system, data it differs from traditional file
systems
independence and meta-data

Evolution of
database systems
???

3. What are the responsibilities of the 4. Discuss when not to use a

DMA and the database designers? DBMS

Page 30 of 146
 CHAPTER 2
THE DATABASE DEVELOPMENT PROCESS

Prepared By:-
Henok Ephrem(M.sc)
Page 31 of 146
information systems architecture
 An information system architecture is a formal
definition of the business processes and rules,
systems structure, technical framework, and
product technologies for a business or
organizational information system.
 An information system architecture usually consists
of four layers: business process architecture,
systems architecture, technical architecture, and
product delivery architecture.

Page 32 of 146
Con’t
 The architecture of an information system encompasses the
hardware and software used to deliver the solution to the final
consumer of services.
 The architecture is a description of the design and contents of a
computerized system. If documented, the architecture may include
information such as a detailed inventory of current hardware,
software and networking capabilities; a description of long-range
plans and priorities for future purchases, and a plan for upgrading
and/or replacing dated equipment and software.
 The architecture should document: What data is stored, How does
the system function Where are components located, When do
activities and events occur in the system and Why does the system
exist

Page 33 of 146
Information engineering
 Information engineering (IE) or information
engineering methodology (IEM) is a software
engineering approach to designing and
developing information system.
 It can also be considered as the generation,
distribution, analysis and use of information in
systems.

Page 34 of 146
Con’t
 Information engineering research is focused on
enhancing the decision making process through
knowledge discovery methods and information
modeling that helps us understand how information
is used within an enterprise.
 Faculty working in this area conduct research
related to data mining, experimental design,
decision theory, and statistical analysis.

Page 35 of 146
Information Systems Planning
 identifying strategic planning factors
 identify corporate planning objectives
 develop an enterprise model

Page 36 of 146
 Database development process
 Systems Development Life Cycle:- The major steps in
database design are;

1. Planning: that is identifying information gap in an

organization and propose a database solution to solve the
problem.
2. Analysis: that concentrates more on fact finding about the
problem or the opportunity. Feasibility analysis, requirement
determination and structuring, and selection of best design
method are also performed at this phase.

Page 37 of 146
Con’t
3. Design: in database designing more emphasis is given to this phase.
The phase is further divided into three sub-phases.
1. Conceptual Design: concise description of the
data, data type, relationship between data and
constraints on the data.
• There is no implementation or physical detail
consideration.
• Used to elicit and structure all information
requirements

Page 38 of 146
Con’t
 which describe the stature of the whole DB for
community of users
 Conceptual schema hides the details of physical storage
structures and concentrates on describing entities, data
type, relationships user operations and constraints.
 Describes all the data items and relationship between
data items together with integrity constraints
 The implementation of conceptual schema is often
designed in high level data model.

Page 39 of 146
 2.Logical Design:
a higher level conceptual abstraction with selected
specific data model to implement the data structure.
• It is particular DBMS independent and with no
other physical considerations.

Page 40 of 146
Con’t
- Often called view level which include a number of
external schemas or user view of the data
- Each external schema describes the part of the DB
that a particular user group is interested in and
hides the rest of the DB from the user group.
- External schema also typically implemented using
high level data model.

Page 41 of 146
3.Physical Design:
 physical implementation of the upper level design
of the database with respect to internal storage
and file structure of the database for the selected
DBMS.
• To develop all technology and organizational
specification.

 SQL Query

Page 42 of 146
Con’t
- which describe the physical storage structure of the
DB
- - describe how data is stored in the DB
- - it covers the data structure and organization used
to store data on storage devices
- - the complete description of the internal model

Page 43 of 146
Con’t
4. Implementation: the testing and deployment of the
designed database for use.
5. Operation and Support: administering and
maintaining the operation of the database system
and providing support to users.

Page 44 of 146
Con’t

Page 45 of 146
Con’t

Page 46 of 146
Alternative IS development Approaches

 Prototyping
 Packaged data models
 CASE and Repository

Page 47 of 146
Different roles of team members involved in
a database development project

 Read about Different roles of team members

involved in a database development project

Page 48 of 146
Three-tiered Database Location
Architecture
 Client tire
 Application/Web server tier
 Enterprise server tier

Page 49 of 146
Schema, instance and Database state

 The description of a database is called the database

schema, which is specified during DB design and is
not expected to change frequently
 The actual data in a database may change quite
frequently. For example, a data in a student
database, we add student or enter a new grade for
a student.
 The data in the database at a particular moment in
time is called a database state or snapshot. It is
called the current set of occurrences or instance in the
database.
Page 50 of 146
Con’t

Page 51 of 146
Data independence
 change the schedule at one level of a database system
without having to change the schema at the next higher level

- there are two kinds of data independence

 logical data independence - is the capacity to change the
conceptual schema without having to change external
schemas or application program
 physical data independence - is the capacity to change the
internal schema without having to change the conceptual
schema

Page 52 of 146
Database language

 The DBMS must provide appropriate languages

and interface for each category of users. Once the
design of a database is completed and a DBMS is
chosen to implement the database, the first order of
the day is to specify conceptual and; internal
schemas for the database and any mapping
between the two.

Page 53 of 146
Data Definition Language (DDL) -
 A descriptive language that allows the DBA and
database designer to specify the database schema
or modify the existing one

Page 54 of 146
Data Manipulation Language (DML) -

 Once the database schema is compiled and the

database is populated with data, users must have
some means to manipulate the database. -
Typically manipulations include retrieval, insertion,
deletion and modification of the data

Page 55 of 146
Con’t
There two types of DML :-
 High level or non-procedural
describe only what output is to be obtained
a language that allows the user to state what data is
needed rather than how it is to be retrieved
four generation language (4GL)
use query language(SQL)
 Low level or procedural
Specify how the data output of DML statement must be
obtained
a language that allows the user to tell the system what
data is needed and exactly how to retrieve the data
Cobol, FORTRAN, Pascal

Page 56 of 146
Data Control language(DCL)
 The Data Control Language (DCL) is a subset of the
Structured Query Lanaguge (SQL) that allows
database administrators to configure security access
to relational databases.

 DCL is the simplest of the SQL subsets, as it consists

of only three commands: GRANT, REVOKE, and
DENY

Page 57 of 146
Database Model

 - Model is a representation of real world objects

and their associations.
 It is an abstraction that concentrates on the
essential, inherent aspects of objects and ignores
unnecessary details.

Page 58 of 146
Con’t
 - Data model is an integrated collection of concepts
for describing and manipulating data, relationships
between data, and constraints on the data in an
organization. - database model vary in both
complexity and richness however all data models
are equivalent as far as their ability to model
information concerned

Page 59 of 146
Con’t
-a data model consists of three main components:-

-data structure: set of rules how data are organized

-manipulative parts: types of operations that are
allowed on the data
- integrity rule: procedures to ensure accuracy of
data

Page 60 of 146
Types of database model

 Hierarchical data model:-

- The first hierarchical DBMS was developed by IBM
and North America Aviation in the late 1960s.
- - Uses two main data structuring concepts
- Record
- Parent child relationship (PCRs)

Page 61 of 146
Con’t
 - A record is a collection of field values that
provide information on an entity ( relationships
instance )
Each field has certain data type - A PCRs type: is
one-to-many relationship between two records
One side record type – parent record
Many side record type – child record

Page 62 of 146
Properties of network schema:-
 - represents data as hierarchical tree structures
 - Each hierarchy represents a number of related
records
 - Allows a node to have only one parents which
means each child must have one and only one
parents
 - Data is organized hierarchically just in the way
data is organized in a file cabinet

Page 63 of 146
Con’t
 - Each relation ship must be explicitly the database
created
 - Record of the same type are grouped into record
types
 - One record type, called the root of the hierarchy
doesn’t participate in any PCR as a child record type.
 - Every record, except the root, participates as a child
exactly one PCR type.
 - A record type can participate as parent type in any
number of PCR types

Page 64 of 146
Con’t
 Problem:- - It is difficult to search for items in the
bottom or middle of the hierarchy
 - Are inflexible which is usually only a relatively small
set of pre defined queries that can be applied to the
data
 - Inefficient at answering queries
 - Inflexible and wholly unsuited to ad-hoc querying
 - Many-to-many relationship is handled by duplication
of record instances

Page 65 of 146
Con’t

Page 66 of 146
Network model
- It was an extension of the hierarchical data model - The
differences are:
- In hierarchical data model, a child record has exactly
one parent
- In network data model, a child can have any number
of parents - It consists of two sets of structure
- Set of records
- Set of links - Each link type involves two record types,
parent and child record type

Page 67 of 146
Properties of network schema:-

 - Each occurrence of a link represents a one-to-

many (1:N) relationship between parents occurrence
and corresponding child occurrences.
 - Defined to address some of the issues of
inflexibility of the hierarchical model
 - There are minimal data independence
 - Each child could have many parents

Page 68 of 146
Con’t
- There is no restriction on how many links can be combined into link types.
- The child record type in one link can be the parents record type of another
link
- A given record type can be a parent in any number in any number of link
types.
- A given record type can be a child in any number of link types except root
- Direct link make system implemented using network model is very fast create
relationship among data through a linked list structure in which subordinates
records( member) can be linked more than one data element(owner)
- use index ( unique number) for searching item

Page 69 of 146
Con’t
Problem:- - the developers must anticipated every
possible question that users might ask about the
data - increase complexity - need huge amount of
processor time and storage space due to
fragmentation of data - flexibility does come at the
great complexity and duplication of data

Page 70 of 146
Con’t

Page 71 of 146
Relational model
- allows the definition of data structure, storage and retrieval
operation and integrity constraints
- in such database the data and relations between them are
organized in tables
- Each tables has a number of columns( attribute ) with unique
names - use high-level query language called SQL and
support a limited form of user views
- it allows files to be related by means of a common field
- the relational database model is based on the mathematical
concept of a relation, which is physically represented as a
table

Page 72 of 146
Con’t
- relational data model employs three basic notations:
 entity sets

 relationship sets
 attributes

- best features :-
allows a high degree of data independence
provide substantial grounds for dealing with data semantics, consistency
and redundancy problems
the development of structured query language
the strength of the relation database is a designer does not need to know
which questions might be asked of the database. If the data is carefully
defined the database can answer virtually any question

Page 73 of 146
Con’t

Page 74 of 146
Object-Oriented model
- Defines in terms of objects, their properties, and their
operations
- - An object contains values stored in instance variables
within the object
- - An object also contains bodies of code that operate
on the object called methods.
- - Objects that contain the same types of values and
same methods are grouped into classes
- - A class may be viewed as a type definition for
objects.

Page 75 of 146
Con’t
- Objects with the same structure and behavior belong to a
Class, and Class are organized into hierarchies. The
operations of each class are specified in terms of
predefined procedures called methods.
- - The only way in which one object can access the data of
another object is by invoking the method of that other
object, this is called sending message to the object.
- - The internal parts of the object, the instance variables and
method code, are not visible externally called data and
method hiding.

Page 76 of 146
Example of OO Model
Class employee /* methods are usually defined
{ separattley from the class
/*variables or attributes*/ definition.*/
Private: string get-address( )
string name; {
string address; return address;
date start-date; }
int salary; int set-address(string new-
/*Methods*/ address)
Public: {
int annual-salary( ); address = new-address;
string get-name( ); }
string get-address( ); int employment-length( )
int set-address(string new-address); {
int employment-length( ); return today( ) – start-date;
} }

Page 77 of 146
 Exercise
1. Define the following terms: data 2. Discuss the ANSI-SPARC
model, database schema, architecture
database state, internal schema,
conceptual schema, external
schema , data independence, DDL,
DML
Reading
Assignment
???
3. What is the difference between a 4. Describe the three-schema architecture.
database schema and a database Why do we need mapping between schema
state? levels? How do different schema definition
languages support this architecture?

Page 78 of 146
Con’t
5. What is the difference 6. What is the difference between
procedural and nonprocedural
between logical data DMLs?
independence and physical
data independence?

Reading
Assignment
???
7. What is the difference between the
two tier architecture and three tier 8. Discuss the main
architecture? categories of data models

Page 79 of 146
 CHAPTER 3
DATABASE ANALYSES

Prepared By:-
Henok Ephrem(M.sc)
Page 80 of 146
Data Modeling

Page 81 of 146
Business Rule

Page 82 of 146
Scope of Business rules

Page 83 of 146
Gathering business rules

Page 84 of 146
Data names and definition

Page 85 of 146
The E-R Model
 The E-R (entity-relationship) data model views the real
world as a set of basic objects (entities) and
relationships among these objects.
 E-R data model is based on perception or real world
that consists of a set of basic object called entities and
of relationships among these object
 The relational model is based on the mathematical
concept of a relation which is physically represented as
a table

Page 86 of 146
Con’t
- The E-R data model employs three basic notations:

 Entity sets
 Relationship sets
 Attributes

Page 87 of 146
Entity sets

 An entity is a thing or an object in the real world

that is distinguishable from all other objects
 An entity may be concrete (a person or a book, for
example) or abstract (like or a concept like Account
in a bank)
 An entity set is a set of entities of the same type
(e.g. all persons having an account at a bank)

Page 88 of 146
Type of entity:-
 Person – employee, student, patient
 Place – store, workhouse, state

 Object – machine, car, building

 Event – registration, renewal

 Concept – account, course, job, project

Strong Entity An entity set that has a primary key is

called as Strong entity set

Page 89 of 146
Classification of Entity Sets

 entity sets can be broadly classified into:

Page 90 of 146
Con’t

Page 91 of 146
Con’t
 Consider customer borrows loan. Here loan is a
weak entity. For every loan, there should be at least
one customer. Here the entity loan depends on the
entity customer hence loan is a weak entity.

Page 92 of 146
Attribute
 A property of an entity or a relationship set.
 An attribute domain is the set of allowable values for one
more attributes
 Every entity is described by a set of (attribute, data value)
pairs, one pair for each attribute of the entity set
 An attribute are descriptive properties proposed by each
member of an entity set
 An attribute is used to describe the properties of the entity.
This attribute can be broadly classified based on value and
structure.

Page 93 of 146
Con’t

Page 94 of 146
Con’t
 - An attribute, as used in E-R model, can be characterized by the
following attribute types:
- Simple attribute– an attributes that can’t be divided into
subparts(other attributes)
Eg. Pobox number, age of a person
- - Composite attribute – is the one which can be further subdivided
into simple attributes
Eg. Consider an attribute name which can be further subdivided into
first name, middle name, last
name.
- Address which has also subparts like street name, city and state.
Degree earned by a particular scholar,etc

Page 95 of 146
Con’t

Page 96 of 146
Con’t

Page 97 of 146
Con’t

Page 98 of 146
Relationship and Relationship sets -
 A relationship is an association between two or more entities
 A relationship set is a set of relationships of the same type
Eg. CUSTOMER – ACCOUNT
 to denote the association between customers and accounts
(degree 2)
Class schedule could be relate INSTRACTOR-SECTION-
COURSE and ROOM entities.( degree 4)
 the number of entity sets that participate in a relationship
set is know as the degree of the relationship set

Page 99 of 146
ER Diagram

 The overall logical structure of a database can be

expressed graphically by E-R diagram
 Symbols used in ER diagram

Page 100 of 146

Con’t

Page 101 of 146

Relationship Degree
- The degree of a relationship type is the number of
participating entity types.
- Relationship degree refers to the number of associated
entities
- Relationships can generally be of any degree, but the
ones most common are binary relationships are binary
relationships.
- The relationship degree can be broadly classified into:

Page 102 of 146

Con’t
 Unary Relationship – - A relationship of degree one -
In the unary relationship the number of associated
entity is one. - An entity related to it self is known as
recursive relationship.

 Binary Relationship :– - A relationship of degree two -

In the binary relationship, two entities are involved. -
Eg.1 Student take course

Page 103 of 146

Con’t
Ternary Relationship :- - A relationship of degree three
- Three entities are simultaneously involved
- Ternary relationships are required when binary
relationships are not sufficient to accurately describe
the semantics of an association among three entities.
- Eg.1Entities EMPLOYEE, PROJECT and LOCATION with a
relationship

Page 104 of 146

Con’t
Quaternary Relationship:- - A relationship of degree
four
- Quaternary relationship involve four entities
- Eg.1 A PROFESSOR teaches COURSE to STUDENT
using SLIDES.

Page 105 of 146

Multiplicity Constraints on relationship

The most common relationship is binary.

Binary relationship are generally referred to as being
 one-to-one (1:1),

 one-to-many (1:*)

 or many-to-many (*:*)

Page 106 of 146

Con’t
 Multiplicity actually consists of two separate
constaraints known as cardinality and participation
Cardinality ratio
- Describes the maximum number of possible relationship
occurrences for an entity participating in a given
relationship type.
- describe the maximum number of possible relationship
for each participating entity refers to what we have
seen as 1:1, 1:N/N:1, M:N

Page 107 of 146

Con’t
Participation constraint
- represents weather all entity occurrence are

involved in a particular relationship (called total

participation ) or only (partial participation)
- - total participation also called existence
dependence

Page 108 of 146

Relationship Classification

Relationship is an association among one or more

entities.

Page 109 of 146

One-to-One-Relationship Type
 - One-to-one relationship is a special case of one-to-
many relationship. True one-to-one relationship is rare.
The relationship between the President and the country
is an example of one-to-one relationship. For a
particular country there will be only one President. In
general, a country will not have more than one
President hence the relationship between hence the
relationship between the country and the President is an
example of one-to-one relationship. Another example
of one-to-one relationship is House to Location. A house
is obviously in only one location.

Page 110 of 146

Con’t
 only one location. - 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.
(see figure below)

Page 111 of 146

Con’t

Page 112 of 146

Con’t

Page 113 of 146

One-to-Many-Relationship Type
 The relationship that associates one entity to more than one
entity is called one-to-many relationship. Example of one-to-
many relationship is Country having states. For one country
there can be more than one state hence it is an example of
one-to-many relationship. Another example of one-to-many
relationship is parent child relationship. For one parent there
can be more than one child. Hence it is an example of one-
to-many relationship. - One-to-many: An entity in A is
associated with any number in B. An entity in B, however, is
associated with at most one entity in A (see figure below)

Page 114 of 146

Con’t

Page 115 of 146

Con’t

Page 116 of 146

Many-to-Many Relationship Type

Page 117 of 146

Con’t

Page 118 of 146

Con’t

Page 119 of 146

Reducing ER Diagram to Tables
A database conforming to an ER 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 set
- Each table has a number of columns with unique names
- Converting a database representing from an ER diagram to a
table format is the way we arrive at a relational database
design from an ER diagram
- To implement the database, it is necessary to use the relational
model. There is a simple way of mapping from ER model to the
relational model. There is almost one-to-one correspondence
between ER constructs and the relational ones.

Page 120 of 146

Con’t

Page 121 of 146

Mapping Weak Entity
Weak entity type does not have an independent existence and it exist
only through an identifying relationship with another entity type
called the owner
- For each weak entity type create a new relation and include all of
the simple attributes as attributes of the relation.
- For each weak entity W that is associated with a 1-N or M-N
identifying relationship, or participates in more than one
relationship, create a new table T and include as its columns, all the
simple attributes and simple components of the composite attributes
of W. also form its primary key by including as a foreign key in R,
the primary key of its owner entity.

Page 122 of 146

Con’t

Page 123 of 146

Mapping Composite Attribute

 - when a regular entity type has a composite

attribute, only the simple components attributes of
the composite attribute are included in the relation
Eg. Under the CUSTOMER entity customer address
consists of four simple attribute

Page 124 of 146

Con’t

Page 125 of 146

Mapping Multi-valued Attribute

Multi - One way to map a multivalued attribute is to

create two tables Eg. Under Employee entity- employee
can have more than one skill for each multivalued
attribute, create a new table and include as columns of
T, the simple attribute or simple components of the
attribute A. Include as foreign key, the primary key of
the entity or relationship type that has A. Specify as the
primary key of T, the foreign key and the columns
corresponding to A.

Page 126 of 146

Con’t

Page 127 of 146

Con’t

Page 128 of 146

Converting Binary relationship to
table
- A relationship which involves two entities can be termed as binary
relationship
Mapping One-to-Many relationship
- - For each weak entity first create a relation for each of the two
entity type’s participation in the relationships
- - In one to many relationship, include the primary key attribute of
the entity on the one side of the relationship as a foreign key in the
relation that is on the many side of the relationship Eg. CUSTOMER
submit ORDER relationship there is a one to many cardinality.
Therefore the CustID, the primary key of the one side of the
relationship, must be referred as a foreign key at the many side of
the relation which is ORDER

Page 129 of 146

Con’t

Page 130 of 146

Mapping Associate Entity to tables

- Many-To-Many relationship can be modeled as an

associative entity in the ER diagram
Without identifier
- - the associative entity which is without any key
attribute
- - The first step is to create three relations. One for each
of the two participating entity types and the third for
the associative entity. The relation formed from the
associative entity is associative relation.

Page 131 of 146

Con’t

Page 132 of 146

With identifier
- Some times data model assigns an identifier to the
associative entity on the ER diagram. There are two
reason to motivate this approach
- the associative entity type has a natural identifier that
is familiar to end user
- the default identifier may not uniquely identify instance
of the associative entity Eg. Shipment number is a
natural identifier to end user rather than having the
default identifier customer id and vendor id as an
identifier for the associative entity called shipment

Page 133 of 146

Con’t

Page 134 of 146

Mapping Unary relationship to tables

- Unary relationship also called recursive relationships

- Unary relationship are one-to-many and many-to-many
One-To-Many Unary relationship Eg. Each employee
has exactly one manager. A given employee may
manage zero to many employee. The foreign key in the
relation is named ManagerID. This attribute has the
same domain as primary key EmployeeID.

Page 135 of 146

Con’t

Page 136 of 146

Many-To-Many Unary relationship

- Create two relation

- - One for the entity type
- - One for an associative relationship in which the
primary key has two attribute both taken from the
primary key of the entity

Page 137 of 146

Con’t

Page 138 of 146

Mapping Ternary relationship to
tables -
 relationship among three entity type

Page 139 of 146

Con’t

Page 140 of 146

Mapping n-ary relationship to tables

- for each n-ary relationship type R, when n is >

2,creat anew relationship to represent R
- - Include as a foreign key attribute in s the primary
key of the relationship that represent the
participating entity types.
- - Also include simple attribute of n-ary relationship
type (or single or components of composite
attribute) as attributes of s.

Page 141 of 146

Con’t

Page 142 of 146

Con’t
Steps for drawing ER diagram
 Identifying entity

 One method is to examine the users requirement’s

specification for noun or noun phrases.
 Identifying relationships

 One method is to examine the users specification for verbal

or verbs expression
 Identify and associate attribute with entities or relationship

Page 143 of 146

Con’t
 Attributes can be identified where noun or noun phrase
is a property, quality, identifier or characterstics of one
of the entities or relationships previously found.
 Determine key attribute
 Select the minimal set of attributes that is less likely to
have it’s value change, that is less likely to lose
uniqueness in the future.
 Draw ER diagram
 Based on the standard representation draw the ER
diagram for the relationship specified.

Page 144 of 146

Suppose that after initial meeting with a user of company x, the following
list of business rules and specifications have been developed to begin the
design of an ER model.

- We would like to track the following customer name, address, city,

state, PO Box, telephone and date of birth.
- A customer may place many order.
- A customer does not always have to order through the same branch at
all the time.
- Customer may have one or more accounts although they may also
have no accounts.
- Information to be record about balance last payment date, last
payment amount.
- A branch may have many customers, information to be record about
each branch. Branch number location (Address, City, State, PO Box).

Page 145 of 146

Con’t

- A branch may sale all items or may only sale certain item.
- - Orders are composed of one or more items.
- - Information record about order, order date, create authorization status.
- - Information recorded for each item description, color, size, pattern, type
an item can be composed of multiple items.
- - Information record about each employee name address (street, city, state,
PO Box) telephone date of birth, title salary skill age.
- - Each employee works in one and only one branch.
- - We want to keep track of the dependant of each employee for insurance
purpose. We may wish to record the name of the dependent as well as age
and relationship.
- - Employee can have one or more skill.

Page 146 of 146