Database

Management
Systems

Lecturer: Nalinda Madurawela

 WHY DATABASE?
Many people collect things
 How about you?
If you collect anything, you probably
are familiar with some of the
problems of managing a collection
e.g., stamps, photos, applications, bills,
information,
One way to keep track of a collection
is to create a database
THE EVOLUTION OF DATABASE
TECHNOLOGY
Information is a major resource and must managed using the principles
that are used to manage other assets such as employees, materials,
equipment and financial resources.

These resources can be divided into two,

 Physical resources (personal, materials, machines, facilities , money ,
etc...)
 Conceptual resources (Data and Information)

As the operations grow in organization is becomes difficult to manage, the

physical resources by observation. Therefore, the managers are forced to
rely on the conceptual resources.
Database File - the main file that
encompasses the entire database
Table - a collection of data about a
specific topic

Field - categories of data within a

table

Data type - the properties of the

data (text, integer, time/date, etc.)

Value - the actual data

EXAMPLE

File – Employee database

Table – Employee
Field – Employee_ID
Datatype – TEXT
Value – CICRA_EMP11
WHAT IS A DATABASE?
A database(DB) a collection of interrelated data that can be
processed by one or more application systems.
 Databases are designed to offer an organized mechanism for
▪ storing,
▪ managing and
▪ retrieving information.
They do so through the use of tables. If you’re familiar with
spreadsheets like Microsoft Excel, you’re probably already
accustomed to storing data in tabular form.
EXAMPLES FOR DATABASES
Library systems
Mailing list
Video rent stall
Order details
Staff Attendance
Salary Details
Inventory Controls System
Student details
Contact information
Flight reservation systems
Hotel reservation systems, etc...
DATABASE APPLICATIONS
 WHAT IS A DATABASE MANAGEMENT SYSTEM?
▪ A Database Management System (DBMS) is a
software package designed to store and
manage databases:
1. Manages very large amounts of data.
2. Supports efficient access to very large amounts of
data.
3. Supports concurrent access to very large amounts
of data.
▪ Example: Bank and its ATM machines.
4. Supports secure, atomic access to very large
amounts of data.
▪ Contrast two people editing the same UNIX file – last to
write “wins” – with the problem if two people deduct
money from the same account via ATM machines at the
same time – new balance is wrong whichever writes last.
DATABASE MANAGEMENT SYSTEM (DBMS)

Database Management System is a software that

facilitates the management of the database.
or
A collection of programs that enables you to store,
modify, and extract information from a database.
There are many different types of DBMSs, ranging
from small systems that run on personal
computers to huge systems that run on
mainframes and cloud
EXAMPLES

 Microsoft Office
Ms Access
 Open office
Base
 DB
 Oracle
 SQL Server
 Sybase
 FoxPro
 Clipper
 Paradox
EXAMPLES
FUNCTIONS OF A DBMS
Data Security
Data Integrity
Backup and recovery
Concurrent Data Access
User-Oriented Data Manipulation
Application Development
ADVANTAGES OF DATABASES
 Reduced data redundancy
 Reduced updating errors and increased
consistency
 Greater data integrity and independence from
applications programs
 Improved data access to users through use of host
and query languages
 Improved data security
 Reduced data entry, storage, and retrieval costs
 Facilitates development of new applications
program
DISADVANTAGES OF DATABASES
 Database systems are complex, difficult,
and time-consuming to design
 Substantial hardware and software start-up
costs
 Damage to database affects virtually all
applications programs
 Extensive conversion costs in moving from a
file-based system to a database system
 Initial training required for all programmers
and users
SQL
STRUCTURED QUERY LANGUAGE
TYPES OF KEYS
Super key
Any unique field(s) of a table
Candidate key
Every minimal super key of a table
Primary key
One of the candidate keys that we choose as the unique
identifier of a table
Alternate key
Candidate keys other than the primary key
Composite key
When the primary key contain more than one field it is called a
composite key
 KEYS
Primary key
a field in which very uniquely identifies its
records.

An attribute which act as a primary key

must have certain characteristics.
▪ Can not have NULL values
▪ Must be UNIQUE
▪ value should not be redundant
Foreign key
A key whose values matches with a primary
key of another table.
A primary key can be targeted by multiple
foreign keys from other tables.

But a primary key does not

necessarily have to be the target of
any foreign keys.
DATABASE DESIGN PROCESS
ENTITY RELATIONSHIP MODEL
(ER)
Most used database structure is the relational model.
ENTITY – An object which can be identified distinctly.

Notation : - Name of the entity

Ex : -

Employee Student
ATTRIBUTE – A property or characteristic of an entity

Notation : - Attribute

Ex : -
Emp_id Emp_name

Employee
 TYPES OF ATTRIBUTES
SIMPLE (ATOMIC) ATTRIBUTES
An attribute that cannot be broken down into parts

COMPOSITE ATTRIBUTES
An attribute that can be subdivided into parts.

First_name mid_name Last_name

Emp_name
SINGLE VALUED ATTRIBUTES
An attribute that take only one value for a given entity.

MULTI VALUED ATTRIBUTES

An attribute that take more than one value for a given entity.

Phone_no Skills

Employee
STORED ATTRIBUTES
An attribute whose values are recorded in the database

DERIVED ATTRIBUTES
An attribute whose values can be calculated from related attributes.

Birthday age

Employee
 RELATIONSHIPS
If an attribute of one entity type refers to another entity type,
then there are some relationship between them.

Notation : -

Relationship

Prepared by: Nimesha Rajakaruna(BIT, CIM)

 DEGREE OF RELATIONSHIP

Unary relationship

Binary relationship

Ternary relationship

N - ary relationship
UNARY RELATIONSHIP (DEGREE 1)
Relationship among instances of one entity type.

Employee

Manages
BINARY RELATIONSHIP (DEGREE 2)
Relationship among instances of two entity types.

Customer Place Order

Course
Lecturer teaches Module
 CARDINALITY RATIOS
(MULTIPLICITY)
Cardinality ratio specifies the number of
relationship instances that an entity can
participate in.

1. One-to-One Relationships (1:1)

2. One-to-Many Relationships (1:M)
3. Many to One Relationships (M:1)
4. Many-to-Many Relationships (M:N)
1 TO 1 RELATIONSHIP (1 : 1)

President rules Country

1 1

Person has Passport

1 1
1 TO MANY RELATIONSHIP (1 : M)

Customer Place Order

1 M

Class contains Student

1 M
MANY TO 1 RELATIONSHIP (M : 1)

Order for Product

M 1

Student Study Campus

M 1
MANY TO MANY RELATIONSHIP (M:N)

Student Follow
M N Courses

Customer buy
M N Product