Database Management

Systems

Dr. Md. Rakibul Hoque

University of Dhaka
 Entity-Relationship Model
 Data Model: A data model is a collection of
conceptual tools for describing data, data relationship,
data semantics and consistency constraints.
 Entity-Relationship Model: This is a higher-level data
model. It is based on a perception of a real world that
consists of a collection of basic objects, called entities
and the relationship among these objects.
 Entity-Relationship Model
 Entity: An entity is a “thing” or “object” in the
real world that is distinguishable from all other
objects. An entity has a set of properties and
the values for some set of properties may
uniquely identify an entity. An entity may be
concrete, such as a person or a book, or it may
be abstract, such as loan, or a holiday, or a
concept.
 Entity-Relationship Model

 Entity Set: An entity set is a set of entities of

the same type that share the same properties,
or attributes.

Customer – The set of all persons who are

customers at a given bank
Loan – The set of all loans awarded by a
particular bank
E-R diagram of Banking
Enterprise
Human Resource Schema
Entity-Relationship Model
 Relational Model

 This is a lower level model. The relational

model uses a collection of tables to
represent both data and relationships among
those data. Each table has multiple columns
and each column has a unique name.
 Designers often formulate database schema
design by first modeling data at a high level,
using E-R model and then translating it into
the relational model.
 Relational Model

 A relational database consists of a

collection of tables, each of which is
assigned a unique name. A row in a
table represents a relationship among
a set of values. And a table is a
collection of such relationships.
 Characteristics

 1. The primary data model for

commercial data-processing
applications.
 2. It provides very simple but
powerful way of representing data
that eases the job of the
programmer.
 Characteristics

 3. This is a record-based model. The database is

structured in fixed-format records of several types.
Each table contains records of a particular type.
Each record type defines a fixed no. of fields, or
attributes. The columns of the table correspond to
the attributes of the record type.
 4. The relational model is at a lower level of
abstraction than the E-R model. Database designs
are often carried out in the E-R model and then
translated to the relational model.
Codd's 12 Rules for RDBMS

 Dr. E.F.Codd, also known to the world

as the ‘Father of Database
Management Systems’ had
propounded 12 rules which are in-fact
13 in number. The rules are numbered
from zero to twelve. According to him,
a DBMS is fully relational if it abides by
all his twelve rules.
 Codd's 12 Rules for RDBMS
 Rule 0: Foundation rule
This rule states that for a system to qualify as an
RDBMS, it must be able to manage database
entirely through the relational capabilities.
 Rule 1: Rule of Information

Relational Databases should store the data in

the form of relations. Tables are relations in
Relational Database Management Systems.
 Codd's 12 Rules for RDBMS
 Rule 2: Rule of Guaranteed Access
Each unique piece of data(atomic value) should be
accesible by : Table Name + primary key(Row) +
Attribute(column).
 Rule 3 : Systemetic treatment of NULL

Null has several meanings, it can mean missing data,

not applicable or no value. It should be handled
consistently. Primary key must not be null.
Expression on NULL must give null.
 Codd's 12 Rules for RDBMS
 Rule 4 : Active Online Catalog
Database dictionary(catalog) must have description of
Database. Catalog to be governed by same rule as
rest of the database. The same query language to
be used on catalog as on application database.
 Rule 5 : Powerful language

One well defined language must be there to provide all

manners of access to data. Example: SQL. If a file
supporting table can be accessed by any manner
except SQL interface, then its a violation to this rule.
 Codd's 12 Rules for RDBMS
 Rule 6: View Updating Rule
All the views of a database, which can theoretically be updated,
must also be updatable by the system.
 Rule 7: High-level Insert, Update, and Delete

The system is able to insert, update and delete operations fully. It

can also perform the operations on multiple rows
simultaneously.
 Rule 8 : Physical Data Independence

The physical storage of data should not matter to the system. If

say, some file supporting table were renamed or moved from
one disk to another, it should not effect the application.
 Codd's 12 Rules for RDBMS
 Rule 9 : Logical Data Independence
If there is change in the logical structure(table structures) of the
database the user view of data should not change. Say, if a
table is split into two tables, a new view should give result as
the join of the two tables. This rule is most difficult to satisfy.
 Rule 10 : Integrity Independence

The database should be able to conforce its own integrity rather

than using other programs. Key and Check constraints,
trigger etc should be stored in Data Dictionary. This also
make RDBMS independent of front-end.
 Codd's 12 Rules for RDBMS

 Rule 11: Distribution Independence

The end-user must not be able to see that the data is
distributed over various locations. Users should always
get the impression that the data is located at one site
only. This rule has been regarded as the foundation of
distributed database systems.
 Rule 12: Non-Subversion Rule

If a system has an interface that provides access to low-level

records, then the interface must not be able to subvert the
system and bypass security and integrity constraints.
 Comparison
Table Row Column

Relational relation tuple attribute

Model

E-R entity set/ entity/ attribute

Model relationship set relationship
 DBMS vs RDBMS
DBMS RDBMS
In DBMS relationship between two In RDBMS relationship between two
tables or files are maintained tables or files can be specified at the
programmatically time of table creation
DBMS does not support client/server Most of the RDBMS support
architecture client/server architecture

DBMS does not support distributed Most of the RDBMS support distributed
database database

In DBMS there is no security of data In RDBMS there are multiple levels of

security
DBMS may satisfy less than 7 to 8 rules DBMS usually satisfy more than 7 to 8
of Dr. E F Codd rules of Dr. E F Codd
 Basic Structure

 Attribute: In relational model we refer

column headers as attributes.
 Domain: For each attribute, there is a
set of permitted values, called domain
(D) of that attribute. For the attribute
branch-name, the domain is the set of all
branch names.
 Basic Structure
 For all relations r, the domains of all attributes of
r should be atomic.
 Atomic domain: A domain is atomic if elements
of the domain are considered to be indivisible
parts. Example: set of integers: 23, 45, 5, 78 etc
 Non-atomic domain: If elements of a domain
can be divided into several parts, the domain is
called non-atomic domain. Example: set of all
sets of integers: {23, 12, 4; 5, 65, 4; 34, 23, 98},
employee-id: HR001, IT005
 Basic Structure

 The important issue is not what the domain

itself is, but rather how we use domain
elements in our database
 It is possible for several attributes to have
the same domain. The attributes customer-
name and employee name – the set of all
person names. The domain of balance and
branch-name, certainly have to be distinct.
 Basic Structure

 Again it is less clear whether

customer-name and branch-name
should have the same domain. At the
physical level, both are character
strings, however, at logical level, they
should have distinct domain.
 Basic Structure

 Null value: One domain value that is a

member of any possible domain is the
null value, which signifies that the value
is unknown or does not exist. Example:
telephone-no.
 Null values cause a number of difficulties
while accessing or updating the database,
thus should be eliminated if at all possible.
 Database Schema

 Database schema: Logical design of the

database.
 Database instance: A snapshot of the
data in the database at a given instant in
time.
 The concept of a relation corresponds to the
programming-language notion of a variable.
 Database Schema

 Relation schema:
 The concept of relation schema
corresponds to the programming-language
notion of type definition. Ex. char a, int b
 There should be a name of a relation
schema. The convention is to use names
beginning with an uppercase letter.
 In general, a relation schema consists of a
list of attributes and their corresponding
domains.
 Database Schema
For Part of Banking Enterprise:
 Branch-schema = (branch-name, branch-city,
assets)
 Customer-schema = (customer-name, customer-
street, customer-city)
 Loan-schema = (loan-no., branch-name, amount)
 Borrower-schema = (customer-name, loan-no.)
 Account-schema = (account-no., branch-name,
balance)
 Depositor-schema = (customer-name, account-no.)

It is customary to list the primary key attributes of a

relation schema before other attributes.
 Database Schema

 Relation instance:
 The concept of relation instance corresponds to
the programming-language notion of a value of a
variable.
 Like variable, the contents of a relation instance
may change with time as the relation is updated.
We often simply say “relation” to mean “relation
instance”.
 To denote that account is a relation on Account-
schema by account(Account-schema) or
account(account-no., branch-name, balance)
 Attributes
 Attributes: An entity is represented by a set of attributes.
Attributes are descriptive properties possessed by each
member of the entity set. The designation of an attribute for
an entity set expresses that the database stores similar
information concerning each entity in the entity set;
however, each entity may have its own value for each
attribute.
 customer- customer-id, customer-name, customer-street,
customer-city
 loan – loan-number, amount
 Domain
 Domain or value set: For each attribute, there
is set of permitted values, called the domain or
value set.
customer-name – Set of all text strings of a
certain length loan-number – Set of all strings
of the form “L-n”, where n is a positive integer
A database thus includes a collection of entity
sets, each of which contains any number of
entities of same type.
 Attribute Types
 Simple – When an attribute is not possible to
divided into subparts, customer city, country.
 Composite – Composite attributes can be divided

into subparts i.e. other attributes. Composite

attributes help us to group together the related
attributes, making the modeling cleaner.
name – first-name, middle-name, last-name
address – street, city, state, zip-code
street – street-number, street-name, apartment
 Attribute Types

 Single-valued – when there exist a single

value for a particular entity, loan-number.
 Multi-valued – When there exist a set of
values for a specific entity, phone-number,
dependent-name of the employee entity
set
Boundary can be assigned for a multi-
valued attribute
 Attribute Types
 Derived – The value of this type of attribute can
be derived from the values of other related
attributes or entities.
 loans-held attribute in customer entity set - How
many loans a customer has from a bank
 age attribute in customer entity set where there is a
attribute named date-of-birth. date-of-birth may be
referred to as a base attribute, or a stored attribute.
The value of a derived attribute is not stored, but is
computed when required.
 Attribute Types
 Null Value: An attribute can take a null value
when an entity does not have a value for it.
1. Not applicable – middle name
2. Unknown – a. missing – the value does
exist but we do not have that information -
name
– b. not known – do not know
whether or not the value actually exist
Common Example: apartment no.
 Relationship Set

 Relationship: A relationship is an
association among several entities.

 Relationship Set: A relationship set is

a set of relationships of the same type.
 Relationship Set
 borrower – association between customer and
loan
 loan-branch – association between loan and
branch
 depositor – association between customer and
account
 Participation: The association between entity
sets is referred to as participation, i.e. the entity
sets E1, E2, ……., En participate in relationship
set R.
 Descriptive Attribute

 Descriptive Attribute: A relationship

may also have attributes called
descriptive attributes.
 depositor : customer – account,
access-date (the most recent date on
which a customer accessed an
account.
 Degree of Relationship set

 Degree of Relationship set: The

number of entity set that participate in
the relationship set is called the
degree of the relationship set. A binary
relationship is of degree 2; a ternary
relationship set is of degree 3.
 Constraints

 An E-R enterprise schema may

define certain constraints to which
the contents of a database must
conform. Two of the most important
constraints are:
 Constraints
 A. Mapping Cardinalities or cardinality ratios:
Mapping cardinalities express the number of
entities to which another entity can be associated
via a relationship set. For a binary relationship
set R between entity sets A and B the mapping
cardinality must be one of the following:
1. One to one
2. One to many
3. Many to one
4. Many to many
 Constraints

 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
 One to many - An entity in A is
associated with any number (zero or
more) entities in B. An entity in B,
however, can be associated with at
most one entity in A
 Constraints
 Many to one - An entity in A is associated
with at most one entity in B. An entity in B,
however, can be associated with any
number (zero or more) entities in A.
 Many to many - An entity in A is
associated with any number (zero or
more) entities in B and An entity in B can
be associated with any number (zero or
more) entities in A.
 Constraints
 B. Participation Constraints: The
participation of an entity set E in a
relationship set R is said to be total if every
entity in E participates in at least one
relationship in R. If only some entities in E
participate in relationships in R, the
participation of entity set E in relationship R is
said to be partial.
Thank
You