dept of computer science university of ruhuna, sri lanka database management systems (dbms)...

Dept of Computer Science University of Ruhuna, Sri Lanka

Database Management Systems

(DBMS)

Certificate Course in Information Technology

Anil Luvishewa

[email protected]


Learning Outcomes

Understand the need for database management as opposed to the traditional stand - alone applications approach.

Learn the major factors involved in proper database design including the use of entity-relationship diagrams and normalization.

Learn to use SQL for data definition and data manipulation.


Topics

Introduction to DBMS

The Database Architecture

Database Design Process

Conceptual Data modeling – (Entity-Relationship approach)

The logical Database Model – (The Relational Model)

Logical Database Design – (Normalization)

Data Manipulation using SQL


Introduction to Database Concepts

Why use a Database? Why Database Technology?


Branch BranchMobile

ATM users

ATM users

Bank


Data and Information

Data Raw facts Less meaningful Not processed

Eg:

Date of birth

Information Processed data Meaningful Organized

Eg:Processed DOB: Age


File Processing System

Individual applications maintain own private data files. Leads to data duplicate (redundancy). Inconsistency Management is very difficult Data is in different formats. Inflexible


What is a database?

A database is an organized collection of data that are related in a meaningful way and stored in a common pool which can be accessed by one or many persons for many purposes.

A database is a group of related files

File 1 File 2 File 3

Database

Student database

Eg: Course fileStudent name fileMarks file


What is a Database?

Data collection may be . . .

Electronically Collected Manually Collected


Key Features of a database

The data is

input (stored) only once Organized accessible

effectively and efficiently


Database Building Blocks

File/Table

A collection of related records.

RecordA record is a group of related fields.

FieldField represents an attribute, or a characteristic or a piece of information


Table

a Record

a Field

Database Building Blocks..


Database Management System (DBMS)

The DBMS is a software package which allows a user to define, create, and maintain the database and provides controlled access to this database.

The interface between users and data is provided by a database management system.

Eg: Oracle, MS SQL-Server, MS Access

MySQL, PostgreSQL


Overview of a DBMS

DBMS Data-bases

Data-bases

Modify/Retrieval

(Command)

Results

Admin

Users


Characteristics of Modern DBMS

Query processing Transaction management Concurrency control Database recovery Database security and authorization Distributed databases


Advantages of using DBMS

Data duplication and storage space wasting can be avoided

Inconsistency can be avoided Data can be shared Unauthorized access can be restricted Recovery from failures


Disadvantages of using DBMS

cost of extra hardware cost of entering data cost of training people to use DBMS cost of maintaining DBMS complex


Components of a Database System Data

The data in the database integrated and shared.

HardwarePhysically stores data, it can be secondary storage on which the database physically resides together with associated I/O devices.

SoftwareBetween the physical database & the users is a layer of software which is known as the DBMS.

ProceduresRefer to the instructions & rules that governs the design & use of the database.

UsersApplication Programmers.End-users


Controlling & Maintaining a Database

Database Administrator (DBA)

Database


Duties of the Database Administrator

Installing and upgrades of database software Performance tuning Backup and recovery strategies Start the database & shutdown the database Monitoring the database Give and drop database access to the users Consultation with developers


Database Architecture

Data-bases

Data-bases


The Need of a Database Architecture

Users need to access same data but in different customized views.

User views can be changed without affecting data or other user views.

Users are not deal directly with physical data storage

Database administrator should be able to change the database storage without affecting the user’s views.


Database Architecture

An architecture for a database system is useful for explaining the structure of a specific database system

The database architecture is a three level architecture which reflects the underlying database system


Schemas and Instances

Database schema The description of a database called database schema The logical structure of the database Schema is defined during the database designing and is not

frequently changed

Instance Data (the actual content) in the database at a particular moment

in time is called database state. It is also called the instances or current set of occurrences


Three Schema Architecture

External Level

Conceptual Level

Internal Level

External / Conceptual mapping

External View 1

External View n

Conceptual Schema

Internal Schema

DBDB DB

Conceptual / Internal mapping

End users


Three Schema Architecture..

Internal (Physical) Schema

Defines the physical view of data (as seen by a DBMS)

Describe the physical storage of the database

Describe how the data is stored in the database

Concern with storage space allocation, record description, data compression and encryption techniques.


Internal (Physical) Level

Struct Employee {

Char Emp_no[5],

Char Name[25],

Date Date_joined,

BooleanPay_Tax,

Int OT_Hours,

Char Des_Code[3]

} ;


Three Schema Architecture..Conceptual Schema

Defines the logical view of data (as seen by programs)

Describe the structure of the whole database for a community of users.

Hides the details of physical storage structure.

Describe entities, data types, relationships, user operations and constraints.


Conceptual Level

Employee Table

Base Table


Three Schema Architecture..

External Schema

Defines the external view of data (as seen by a user)

The users view of the database

Describe the part of the database that a particular user group is interested.

Hides the other details of the database.


External Level

The data what user wants (Only selected parts of selected records from base table)

Base Table (Conceptual View)

View Table (External View)


MappingExternal/Conceptual mapping

Define the correspondence between a particular external view and conceptual view.

This specified how a particular external schema is derived from conceptual schema

Conceptual/Internal mapping Define the correspondence between the conceptual

view and stored database. This specifies how the conceptual schema is

represented at the internal level. If the structure of the stored database is changed,

the conceptual internal mapping must also be changed accordingly.


Mapping..

Sno FName LName

Struct STAFF { int Staff_No; int Branch_No;

char FName[15];char LName[15];date date_of_birth};

Staff_No LName Bno

Staff_No FName LName DOB Batch_no

External view 1 External view 2

Conceptual Level

Internal (Physical) Level

External Level


Data Independence

The major objective for the three-level architecture is to provide data independence

Logical Data Independence

Capacity to change the conceptual schema without having to change external schemas or application program.

Physical Data Independence The ability to modify the physical schema without

changing the conceptual (or external ) schema


Database Design


Database Design

The database design process can be broken down into four phases.

Requirements collection and analysis phase Conceptual Design Logical Design Physical Design


Requirements collection and analysis phase

Prospective database users are interviewed to understand and document their data requirements


Conceptual Design

This is a high level description of the structure of a database

Eg: E-R Diagram

Concise description of the data requirements of the users and includes detailed description of the data, relationship and constraints.


Logical Design

This is the process of mapping the database structure developed in the previous phase to a particular database model.

Eg: Map E-R model to relational


Physical Design

This is the process of defining structure that enables the database to be queried in an efficient manner

Eg: index and hash file design


Phases of Database Design

Requirements collection & analysis

Conceptual Design

Logical Design

Physical Design

Database Requirements

Conceptual Data Model

Logical Data Model

Physical Data Model

Problem

Database Implementation


Entity – Relationship Modeling

ER model is high level conceptual data model


The Concepts of the ER Model

Entity An entity is a thing in the real world with

an independent existence . As entity may be an object with a physical

existence . e.g. : a person , a student , a house, etc.

An entity is any object that is relevant to the organization. Tangible object Intangible objects Events


Entities

Conceptual Design

Department

Employee

Project

Dependent


The Concepts of the ER Model

AttributeEach entity has particular properties or

features (characteristics ) called attributes . e.g.

student entity(student no, name, address, dob)


Type of attributes

Simple (atomic) attribute

Attributes that are not divisible are called simple or atomic attribute.

Eg: Emp_No

Composite attributeCan be divided in a smaller part which represent more basic attributes with independent meaning of their own.

Eg: Name (First Name, Mid Name, Last Name)


Type of attributes

Multi-valued attribute In some case an attribute can have a set of values for the same entity such attributes are called multi-valued attributes.

Eg. Degree of a person , Qualification

Derived attribute An attribute whose value can be

calculated from related attribute valuesEg: Age (Using Date of Birth) .

Years Employed (Using Employed Date) .


Relationship

RelationshipA meaningful association between entities.

Eg:

Student follows courses

Employee works for department


Relationship

Degree of a RelationshipThe degree of the relationship type is no. of participating entity types . Unary Relationship

Relationship between the instances of a single entity type.

E.g.: person is married to a person (1:1)

Employee manages Employees (1:M)

Binary Relationship Relationship between the instances of two entity types.

Degree = 2;

Student Coursesfollows

Manages

Employee


Relationship

Degree of a Relationship Ternary Relationship

A simultaneous relationship among the instances of three entity type.

Supplier Projectsupplies

Part

Degree = 3 ,Ternary Relationship


Constraints on Relationship Types

One To One ( 1: 1)

Employeeentity

Managesrelationship

.m1

.m2

.m3

.

. .mn

.e1 .e2 .e3

.

. .en

. d1

. d2 . d3 . . .dn

Departmententity

EMPLOYEE DEPATMENTmanages1 1

An employee can manage one department and a department is managed by only one employee.


Constraints on Relationship Types..

One To Many (1:N) Eg: An employee works for one department and a

department has many employees.

Employeeentity

Works forrelationship

.w1

.w2

.w3

.

. .wn

.e1 .e2 .e3

.

. .en

. d1

. d2 . d3 . . .dn

Departmententity

M 1EMPLOYEE DEPATMENTWorks for


Constraints on Relationship Types..

Many – To – Many (M:N) Eg: Consider two entities employee and the project. An

employee can work on several projects and a project has many employees.

Employeeentity

Works forrelationship

Projectentity

M NEMPLOYEE PROJECTWorks

.w1

.w2

.w3

.

. .wn

.e1 .e2 .e3

.

. .en

. P1

. P2 . P3 . . .Pn


Participation Constraint(Membership class)

Participation constraint specifies the existence of an entity depends upon it being related to another entity through the relationship.

There are two types of participation constraints. Total ( obligatory)

Each and every entity in the entity set MUST BE related to the other entity set via the relationship.

Partial (non–obligator, optional) Some or parts of the entity set are related to

the other entity set but not necessarily all.


Participation Constraint Examples

Total participationEvery employee must work for a department

EMPLOYEE DEPATMENTWorks for

.w1

.w2

.w3

.

.

.e1 .e2 .e3

.

.

. d1

. d2 . d3 .


Participation Constraint Examples

Partial participationSome employees manage a department but not all.

EMPLOYEE DEPATMENTmanages

.w1

.w2

.

.

.

.e1 .e2 .e3

.

.

. d1

. d2 . d3 .


Attributes of relationship types

Relationship types can also have attributes similar to those of entity type.

Eg: Employee works on project

If we need to record the number of hours per week that an employee works on project we can include an attribute hours for the works on relationship type.

EMPLOYEE PROJECTWorks on

hours


Weak Entity Type

Weak entity An entity type that is existence-dependent on some other entity

type. Identifying Owner

The entity type on which the weak entity type depends

E.g. Employee is the Owner of Dependant. Identifying Relationship

The relationship between a weak entity type and it’s owner


Weak Entity Type

Can not exist without it’s identifying owner

Weak entity always has total participation with it’s identifying owner.

B

Weak entity

Total

Identifyingrelationship

Identifyingowner

A


Strong Entity Type

Strong entity An entity type that is not existence-dependent on some other

entity type is called strong entity.

Eg: employee works on project

EMPLOYEE PROJECTWorks on


Conceptual Design – Notations

Entity Type

Weak Entity

Relationship

Identifying Relationship



Attribute

Key Attribute

Multi valued Attribute

Derived Attribute



Composite Attribute

Total Participation of E2 in R

Cardinality ratio 1:N for E 1: E 2 in R

E2 E1R

RE2E 1

1 N


Example - A company Database

A company database keeps track of a company’s employees, departments and projects. The requirements are as follows:

1. The company is organized into departments. Each department has a unique name, a unique number and a particular employee who manages the department. A department may have several locations.


Example - A company Database

2. A department controls a number of projects, each of which has a unique name, a unique number and a single location


Example - A company Database..

3. We store each employee’s name, i.d. number , address, salary , sex, and birth date . An employee is assigned to one department but may work on several project, which are not necessarily controlled by the same department. Employee may or may not supervise another employee and we need to keep track of the direct supervisor of each employee.


Example - A company Database..

4. We want to keep track of the dependants of each employee for insurance purposes. We keep each dependant’s name , sex, birth date and relationship to the employee.


Solution - A company Database..

Step One Identify entity types, attribute on each entity.

Step Two Identify relationship types

Step Three ER Diagram for the company database



Step OneWhat are the entity type in your company

database?What are the attributes belongs to each

entity?Sketch



Step TwoIdentify the relationship types in your

company database?SketchWrite assumptions



Step ThreeDraw the ER Diagram.


Solution - A company DatabaseDetailed Conceptual Design

Step One Identify entity types, attribute on each entity.

1. Department

DEPARTMENT

Number

LocationName

Note : Name or number can be the key


Step One - Identify entity types & attribute

2. Project

Project

Number

LocationName

Name

surname

FN

Employee Address

i.d# Salary

Sex

Dob

Number

street

city

3. Employee


Step One - Identify entity types & attribute..

4. Dependent

Dependent

Name

DobSex


Step Two - Identify relationship types

“A particular employee who manages the department”

Assumptions: An employee can manage one department.

A department is manage by only one employee. A department must have a manager at all times. (Total)

Employee DepartmentManages1 1

1:1


Step Two - Identify relationship types..

“A department controls a number of projects.”

Assumptions: A project is controlled by one department.

A department controls number of projects. A project must be controlled by a department.

(Total)

Department ProjectsControls1 M

1:M



“An employee is assigned to one department but many worked on several project.”

Assumptions: A department has many employee. An employee must belong to a department and department must have

employees. (Total) A project may have employees An employee can work on a project and a project

should have employee (Total)

M:1

Employee DepartmentAssigned to

M 1

Works on Project

N

M



“An employee may have dependents.”

Employee DependentHasdependant

1 M



“An employee may or may not supervise another employee.”

Assumptions: Supervision relationship is known as recursive relationship.

Employee play two roles: Supervisor , Supervisee

Employee

1 M

supervision

supervisor supervisee


E-R Model (schema) Diagram

for the company database EMPLOYEE Assigned

to

DEPARTMENT

Supervision

Manages

Works on

Project

Controls

DEPARTMENT

Has dependant

N

N

N

N

N

1

1

1

1 11

M


THE RELATIONAL DATA MODEL


Introduction

This model was introduced by Ted Codd of IBM in the early 1970’s.

Provide concepts which can be used to build a logical design.

It is based on the concept of mathematical relations.

DBMS which supports this model is said to be a Relational Database Management System (RDBMS)

The RDB is a collection of relations.


Terminology

RelationA relation is a table with columns and rows.

AttributeAn attribute is a named column of a relation.

TupleA tuple is a row of a relation.


Terminology..

Degreeo The number of attributes of a relation.

Relation schemao A relation name followed by set of attributes o A relation schema is denoted by

R (A1, A2, A3,….,An)R – Relation name

A1, A2, A3,….,An - – list of attributes


Terminology..

Cardinality o The cardinality of the relation is the

number of tuples present in the body, which varies with time.


Example - Terminology

St – No

Name Address Dob

S 1 Kamal Matara 01-05-1980

S 2 Himali Kaluthara 31-10-1981

S 3 Renuka Galle 19-06-1979

STUDENT

Tuple

R (Student)

Degree of student – 4

Cardinality of student -3

R Attribute


Properties of Relation

The relation has a name that is distinct from all other relations

Tuple of relation do not have any particular order.

Each attribute has a distinct name Attributes are unordered. There are no duplicate tuple. All attribute values are atomic.

i.e. Not divisible into components.


Key Types of a Relation

Candidate key A candidate key is an attribute or set of attributes

whose values uniquely identify a tuple of a relation.

There may be more than one candidate keys for a relation.

E.g. Consider the following relation schema Department (d#, dname, location )

Assumption : d# and dname are unique Candidate keys:{d#} and {dname}


Key Types of a Relation..

Primary key One of the relation candidate key is selected as

primary key. The other candidate keys are called alternate keys.

E.g.Consider the following relation schema Department (d#, dname, location )

Assumption : d# and dname are unique Primary key: d# Alternate key: dname


Key Types of a Relation..

Foreign keyA foreign key is an attribute in one relation

that matches the primary key attribute of another relation.

E.g.Consider the following relation schemas Department (d#, dname, location )Project (P#, desc, d#, Start_date)

Foreign key: d# is foreign key in Project schema


Dept_No

Dept_Name

Phone

D01 Construction 0415670045

D02 Finance 0412222652

D03 Personal

FK

Project

Department

Prj_No Prj_Name Location Dept

P01 ABC Auditing Matara D02

P02 TRC Budgeting Colombo D02

P03 Ground Construction Galle D01

Eg: Foreign key...

PK

PK

dept of computer science university of ruhuna, sri lanka database management systems (dbms)...

Documents

database student database

database technology

database concepts

sri lanka introduction

sri lanka table

sri lanka overview

logical database model

proper database design