database management systems - sabraz · pdf file02.03.2016 · database management...

HNDIT 1105

Database Management Systems

Lesson 02:

Database Design Process & ER Diagrams

By

S. Sabraz Nawaz

M.Sc. In IS (SLIIT), PGD in IS (SLIIT), BBA (Hons.) Spl. in IS (SEUSL),

MIEEE, MAIS

Senior Lecturer in MIT

Department of Management and IT, SEUSL

Database Design Process

2HNDIT 1105, DBMS By: S.Sabraz Nawaz

Database Design Process

Requirements Analysis

Conceptual Database

Design

Logical Database

Design

Schema Refinement

Physical Database

Design

Security Design

Database design process can be divided into 6 major steps: (given in Raghu’s text book)

HNDIT 1105, DBMS By: S.Sabraz Nawaz 3


• This step answers the following

question:“What users want from the database?”

- what is going to be stored in the database

- what applications are going to be built on top

the database

- what are the most frequently asked queries



Requirements Analysis…

Result:

A well-written concise

document enumerating the

user’s requirements




For example: a library database…

Data to be stored can be…

• Record of all books in the library

• Record of members of the libraryo Students

o Faculty

o Other members

• Record members’ borrowing information




Some applications on top of

the database can be…

• Renewal service (may be on-

line)

• Borrowing-Lending service

• Resource reservation system

(may be on-line)

• Resource request service

(may be on-line)



Conceptual Database Design

The information gathered in

the requirements analysis

phase is used to create a

high-level description of

the data in a conceptual

data model. (Semantic Data

Model, e.g. E-R Diagram )


Conceptual Database

Design


Logical Database Design

In this step, we determine the DBMS to implement the database & also the data model

We utilize the conceptual schema created in the previous step and convert it into a schema of a particular data model (e.g. Relational Database Schema)


Conceptual Database

Design

Logical Database

Design


Schema Refinement

The schema created

by the logical

database design

phase is further

refined for potential

problems such as

redundancies (e.g.

Normalization)


Conceptual Database Design

Logical Database Design

Schema Refinement


Physical Database Design

In this step, performance

criteria are taken into

consideration and further

enhancements to the schema

& creation of indexes are

considered


Conceptual Database

Design

Logical Database

Design

Schema Refinement

Physical Database

Design


Security Design

In this step, different user groups and their roles are identified. Appropriate levels of access are then provided to the data ensuring that users have access to only the necessary data.


Conceptual Database

Design

Logical Database

Design

Schema Refinement

Physical Database

Design

Security Design


The story is…


Conceptual Database

Design

Logical Database

Design

Schema Refinement

Physical Database

Design

Security Design

ER Diagram

Conceptual Schema or Logical Schema


Entity Relationship Diagrams


ER - Model

• The entity-relationship (ER) data model allows us to describe

the data involved in a real-world enterprise in terms of objects

and their relationships and is widely used to develop an initial

database design


E-R Model (Entities)

• The two main constructs of the Entity-

Relationship model are Entities &

Relationships

• An entity is an object in the real world that is

distinguishable from other objectso e.g. Lecturer, Student, Subject, etc.

• A collection of similar entities is called an

entity seto e.g. Lecturers, Students, Subjects, etc. ENTITY


Entity Types and Entity Sets

• A database usually contains groups of entities that are similar. For example, a company employing hundreds of employees may want to store similar information concerning each of the employees.

• These employee entities share the same attributes, but each entity has its own value(s) for each attribute.

• An entity type defines a collection (or set) of entities that have the same attributes. Each entity type in the database is described by its name and attributes.

• The collection of all entities of a particular entity type in the database at any point in time is called an entity set or entity collection.


Two entity types: EMPLOYEE

and COMPANY


E-R Model (Entities...)

Example, an employee in a company database.

EMPLOYEES

e1

e2

e3

e4

e5

e6

…

Employee Entity setHNDIT 1105, DBMS By: S.Sabraz Nawaz 19

E-R Model (Attribute)

• An entity is described using a set of attributeso e.g. Name, NIC, etc.

• All entities in an entity set have the same attribute

Attribute


Several types of attributes occur

in the ER model

• Simple versus Composite

• Single-valued versus Multivalued

• Stored versus Derived


E-R Model (Attributes…)

Example: name, id, age & salary are attributes in

EMPLOYEES entity

name

id

dob

salary

EMPLOYEES

e1

e2

e3

e4

e5

e6

…

Employee Entity set

salary

dob id

name



• The attributes are said to be composite if they can be split.

Eg:- the entity Employee contains attribute 'name' which is a

composite one

LastNameFirstName

nameEMPLOYEES



• A multi-valued attribute of an entity is one which has

multiple values. o Eg:- attribute phone numbers in employee entity.

first_namephone

surname

EMPLOYEES



• Some attributes that can be computed from other attributes are

called derived attributes.

o Eg:- age is derived from date of birth & current date

nameage

EMPLOYEES

dob


E-R Model (Attributes…

another sample)

Customers


• A Key is a minimal set of attributes whose values uniquely identify an entity in the set

E-R Model (Keys)

e1

e2

e3

e4

e5

e6

…

Employee Entity set

salary

dob id

name


E-R Model (Keys...)

• A candidate key is a combination of attributes that can be uniquely used to identify a database record

• If a table has one or more candidate keys, one of these candidate keys is selected as the table primary key and the rest are called alternate keys

• Primary key is underlined

EMPLOYEES

id


E-R Model (Keys...)

• Some entity types have more than one key attribute. For

example, each of the Vehicle_id and Registration attributes of

the entity type CAR


E-R Model (Keys...)

• A key formed by combining at least two or more columns is

called composite key

• Always, the minimal set of attributes are considered for the

key.

• Therefore,

A key is a minimal set of attributes whose values uniquely

identify an entity in the set.


E-R Model (Relationship)

• A relationship is an association among two or more entities

o SaNa works for Department of MIT

o Thilan works for Department of MIT

• A collection of similar relationships is called a relationship

set


E-R Model (Relationship…)

Graphically,

works

inEMPLOYEES DEPARTMENTS



• A relationship can also have descriptive attributes

• These are used to record information about the

relationshipo E.g. SaNa works for MIS Department from November 2006. In the figure, this

is captured by the since in Works_In

nic dob



D1

D2

D3

111

222

333

444

EmployeesDepartments

Work_in

1/2/97

1/2/99

1/5/01

1/2/87

1/3/95



An instance of a relationship set is the set of relationships

D1

D2

D3

111

222

333

444

EmployeesDepartments

Work_in

1/1/91

3/3/93

2/2/92

3/1/92

3/1/92


E-R Model (contd.)

• Degree of a relationship is the number of participating entities in the relationship.

• A relationship of degree two is called a binary relationship (e.g. Works In)

• A relationship with degree three is called ternary.


E-R Model (contd.)

Ternary relationship

D1

D2

D3

111

222

333

444

Employees

Departments

Work_in

L1

L2

L3

Locations


works


LOCATIONS

Ternary relationship

HNDIT 1105, DBMS By: S.Sabraz Nawaz

E-R Model (contd.)

39

E-R Model (contd.)

• The cardinality ratio for a binary relationship

specifies the number of relationship instances that an

entity can participate in.

• There are three types of cardinality ratios for binary

relationships.

oOne-to-One

oOne-to-Many (Many-to-One)

oMany-to-Many


E-R Model (contd.)

One-to-One relationship

Example: An employee manages at most one department. A

department can have only one manager managing it.

manages EMPLOYEES DEPARTMENTS


E-R Model (contd.)

One-to-Many relationship

For example, an employee works in at most one department.

Graphically,

works



E-R Model (contd.)

Many-to-Many relationship

Example: An employee can work on several projects. A project

can have many employees working on it.

works

onEMPLOYEES PROJECTS


E-R Model (contd.)• Participating constraint specifies whether the existence of an

entity depends on its being related to another entity via the relationship type

• For example, if we specify that an employee must always work for a department. Then we say that the relationship “works in” is in total participation from Employee entity to Department entity

• If the relationship is not in total participation, then it is known as in partial.

o For example, from DEPARTMENTS to EMPLOYEES

works in EMPLOYEES DEPARTMENTS


E-R Model (contd.)

• Entity types without any key attributes is called weak entity types.

• A weak entity is uniquely identified by considering some of its attributes with the primary key of another entity called the identifying owner.

• The attributes in the weak entity participating in the key are called partial keys

• The owner entity and the weak entity participates in an identifying relationship.

• The cardinality of the identifying relationship is either one-to-one or one-to-many from owner entity to weak entity

• The weak entity must have total participation in the identifying relationship


E-R Model (contd.)

Graphically (Weak Entity)

PolicyEMPLOYEE

name ageid

DEPENDENTS1 M


E-R Model (contd.)

• Entities participating in a relationship need not be distinct.

Such relationships are called recursive relationships.

• Each entity in the relationship play a role in the relationship. It

is recommended to state the role in recursive relationships.

HNDIT 1105, DBMS By: S.Sabraz Nawaz

manages

EMPLOYEES

supervisor subordinate

47

Reference:

Ramakrishnan, R., & Gehrke, J. (2003). Database management

systems. Osborne/McGraw-Hill. (p. 03– 23, p. 24-50)


database management systems - sabraz · pdf file02.03.2016 · database management...

Documents