Transcript Database Management Systems - The Institute of Finance

Database Management
Systems
Module 2
Module Contents


Conceptual modeling
Entity - Relationship concepts



Structure of relational databases
The relational algebra


Attributes, Domains, Primary Keys Foreign
Keys, ER Diagram and Naming
tuple and domain relational calculus
Modifying the databases
Conceptual modeling


Conceptual modeling involves building a
model of the real world expressed in
terms of of the data requirements
established.
A conceptual model is a model of the
real world expressed in terms of data
requirements.
E-R Diagramming/Modeling


One of the major approaches in
conceptual modeling is Entity
Relationship Modeling/Diagramming (ER Modeling).
Using ER model, a real world enterprise
data can be described in terms of
objects and their relationships and it is
widely used to develop an initial
database design.
E-R Diagramming/Modeling

It provides useful concepts that allow
us to move from an informal description
of what users want from their database
to a more detailed, ad precise,
description that can be implemented in
a DBMS.
E-R Diagramming/Modeling


Many variations of ER diagrams are in
use.
No widely accepted standards prevails.
Overview of Database Design


The database design process can be
divided into six steps.
The ER model is most relevant to the
first three steps.



Requirement analysis
Conceptual Database Design
Logical Database Design
Requirement analysis

The very first step in designing
database application is to understand:




what data is to be stored in the database
what application must be built on top of it
and what operations are most frequent
and subject to performance requirements.
Understand what users want from the
database.
Requirement analysis


It is an informal process that involves
discussions with user groups.
Several methodologies have been
proposed for organising and presenting
the information gathered in this step.
Conceptual Database Design


The information gathered in the
requirements analysis step is used to
develop a high level description of the
data to be stored in the database, along
with the constraints that are known to
hold over this.
This step can be carried out using ER
Model.
Logical Database Design


A process of choosing DBMS to
implement database design, and
converting the conceptual database
design into a database schema in the
data model of the chosen DBMS.
In case of relational DBMS this step
involve converting an ER schem into a
relational database schema.
Schema Refinement


This is the fourth step in database
design where analysis of the collection
of relations in relational database
schema is done, and also it identifies
potential problems and refines it.
Schema refinement can be guided by
some elegant and powerful theory
(Normalisation of relations).
Physical Database Design


Further refine the database design to
ensure that it meets desired
performance criteria.
This step may involve building indexes
in some tables.
Security Design


In this step, we identify different user
groups and different roles played by
various users (e.g., the development
team for a product, the customer
support representatives, product
manager)
For each role and user group, we must
ensure that users can access only
necessary part of the Database.
Fundamentals of E-R
Diagramming


We assume that our relational data
model is represented using the
constructs of the E-R approach.
In this approach a given universe of
discourse is represented using an entity
model: a model built up of entities,
relationships and attributes.
Entities


An entity can be defined as a thing
which an organisation recognises as
being of an independent existence and
which can be uniquely identified.
It is an aspect of the real world which
can be distinguished from other aspects
of the real world.
Entities

It can be a physical object such as:

a house or a car, an event such as a
house sale or a car service, or a
concept such as customer transaction
or order.
Entities



There is a real difference between an
entity and an entity-type.
An entity-type is a category.
An entity is an instance of a given entity
type. There are usually many instances
of an entity type.
Entities

Example:



A student is an entity-type whereas Juma
is an instance of this entity-type.
Juma is an entity
Because the term entity-type is some
what cumbersome, most people tend to
use the term entity as synonym for this.
Entities




But remember that when it is referred to
an entity we normally mean an entitytype.
An entity is described using a set of
attributes.
A collection of similar entities is called an
entity – set
All entities in a given entity-set have the
same attribute.
Entities


A collection of similar entities is called an
entity – set
All entities in a given entity-set have the
same attribute.
Attributes


As a real world aspect, an entity is
characterised by a number of properties
or attributes.
Values assigned to attributes are used
to distinguish one entity from another.

E.g deptName and Location are both
attributes which defines the entity
Department
Attributes

Our choice of attributes reflects the
level of detail at which we wish to
represent information about entities.
For example the Employee entity set
could use name, social-security number
(ssn) as attributes.
Domain


For each attribute associated with an
entity set we must identify a Domain
of possible values.
For example the domain associated with
the attribute name of Employees might
be the set of 20 – character string.
Key



For each entity set you have to choose
a key.
A key is a minimal set of attributes
whose values uniquely identify an entity
in the set.
There could be more than one
candidate key; design a primary key.
Relationships



A relationship is some association
between entities.
The focus is on binary relationships,
which is associations between two
entities.
In E-R approach more than one
relationship can exist between any two
entites
Relationships

Example:


The entities House and Person can be
related by ownership and/or by occupation.
The objective of the E-R approach is to
document only direct relationships

E.g direct relationships exists between the
entities Parent and Child and between
Child and School. The relationship between
Parent and School is indirect: it exist only
by virtue of the Child entity
Relationships
Note:
 You must be clear of what you are
attempting to represent.
 A relationship set can be thought of as
set of n-tuples.
{(e1,...,en) | e1  E1,...,en  En}

Each n-tuple denotes a relationship
involving n entities e1 through en, where
entity ei is an entity set Ei.
Relationships

Below is the relationship set Works_In, in
which each relationship indicates a
department in which an employee works
since
ssn
name
Employees
age
deptID
Works_In
dame
Departments
budget
Ternary Relationship

This occur when association is between more
than two entities.

E.g. When each department has different offices
in several locations at which each employee works
since
ssn
name
age
Employees
address
deptID
Departments
Works_In
Locations
dame
capacity
budget
Ternary Relationship

This relationship is ternary because we
must record an association between an
employee, a department, and a
location.
Degree of Cardinality

This refer to level of relationship
between one entity and another.



One-to-One – Only one entity is associated
with a single entity of another entity set.
E.g. employee-dept.
One-to-Many – Indicate one entity can be
associated with many entities of another
entity set. E.g. Dept->Employee
Many-to-Many – Indicate that many
entities from one entity set is associated
with many entity from another entity set.
E.g. employee->dept, dept->employee
Class Work