Welcome to

Database Management and

Administration
(COMP1638)

Dr Tatiana Simmonds
Introduction
and
Databases Overview
 Objectives
• What is a database?
• Database Management Systems
• Properties of a database and Data concepts
• Databases evolution
• Different types of database
• Relational Database Model
• DBA responsibilities
• Oracle RDBMS and Tools

T.Simmonds 3
 Part 1
“Information is a source of learning.
But unless it is organized, processed,
and available to the right people in a
format for decision making, it is a
burden, not a benefit.”
William Pollard (Physicist (1911–1989))
 What is
a DATABASE?
?

T.Simmonds 5
 A DATABASE is …
 Database
 is defined as collection of related
data that is organised and stored
 and enables us to produce information
 Data can be defined as
 recorded facts and numbers
 Information can be defined as:
 Knowledge derived from data
 Data presented in a meaningful context
 Data processed by summing, ordering, averaging,
grouping, comparing, or other similar operations
T.Simmonds 6
 About Databases
 Today, database technology is part of almost every
information system.
 It covers all the largest sources of data, with many new
ideas
 Web search
 Data mining
 Scientific and medical databases
 Integrating information
 You may not notice it, but databases are behind almost
everything you do on the Web
 Google searches
 Queries at Amazon, eBay, etc

T.Simmonds 7
 Database Management System
The DBMS (Database Management System) is a
software that is used to
 create, maintain and access databases
 control organisation of the data
 protect the integrity and security of the data
The DBMS is a large, complicated program that is
licensed from a software vendor.
 companies almost never write their own DBMS programs.
The DBMS receives requests encoded in SQL and
translates those requests into actions on the
database.
T.Simmonds 8
 Advantages and Disadvantages of the DBMS
Approach
Better information
Faster response time
Lower operating costs
Lower storage requirements
Improved data integrity
Better data management
But potentially –
 Higher software cost
 Increased vulnerability
T.Simmonds 9
 The evolution of Databases
 The organisation of data has evolved from a collection of
independent flat files with tree and branching structures and high
level of data redundancy to a collection of tables and objects
 1940 - Flat Files
 1960 - Hierarchical and Network databases
 1970 - Relational model (1978: Oracle, DB2)
 1985 - Object-Oriented (Oracle ODBMS and others)
 1990 - Multi-Dimensional
 1995 - Open source DBMS (MySQL and others)
- Hybrid (XML/relational, etc.)
 2009 - The NoSQL movement
T.Simmonds 10
 The Object-Oriented Database Model
 Database capabilities are combined with object-oriented
programming language capabilities
 OODBMS is able to store and retrieve complex
unstructured data, such as documents, digital
photographs, video and audio clips etc.
 Data is stored in objects, which contain data along with
methods (actions)
 Objects can be retrieved using object query language
(OQL) – an object oriented version of SQL
 The ability to directly manipulate data stored in OOD
using an object-oriented programming language is called
transparent persistence
 For more info go to www.odbms.org

T.Simmonds 11
 Hybrid XML/Relational Database Models

Application

SQL and XQuery query languages

Hybrid XML/Relational database management server

XML data Relational data

T.Simmonds 12
 NoSQL movement
This movement should really be called a
NoRelational movement, because the work is really
on databases that do not follow the relational
model.
 Next Generation Databases mostly are non-relational,
distributed, open-source and horizontally scalable.
 They can be schema-free, they store huge amount of
unstructured data.
 Often based on XML (the open-source dbXML).
 NoSQL movement are finding wide acceptance in
such applications as Facebook and Twitter.
 Both Facebook and Twitter use Apache Software
Foundation’s Cassandra database.
 "nosql" -> "not only sql". SQL
T.Simmonds 13
 Database Types
 Single-user or Multi-user database systems
 Client-Server or Multi-tier database systems
 Centralised or Distributed database systems
 Disk-based or In-memory or hybrid databases

Personal database systems or Enterprise-class

database systems.
 Transactional or Data warehousing database
systems

T.Simmonds 14
Introduction
and
Databases Overview

Part 2
RDBMS
 Relational Database Concept
In 1970, a then little-known IBM engineer named
E. F. Codd published a paper with proposed the
relational model for database systems
It is the basis for the relational database
management system (RDBMS)
The relational model consists of the following:
 Collection of tables or relations
 Set of operators to act on the relations
 Data integrity for accuracy and consistency

T.Simmonds 16
 Definition of a Relational Database
Relational databases come with relational
operators that produce new relations from old.
A relational database can be defined as a database
in which:
The data is perceived by the user as tables (and
nothing but tables) and the operators available to
the user for retrieval are operators that derive
“new” tables from “old” ones.
C.J. Date’s An Introduction to Database Systems, 2003

T.Simmonds 17
 Relational Database Model
A relational database is a collection of
relations or two-dimensional tables.
Database
server

Table Name: EMPLOYEES Table Name: DEPARTMENTS

… …
T.Simmonds 18
 Relational Model Terms
Entity is something (such a person or
object) of importance to the business or
organisation to which the database belongs
Characteristics of entities are called
attributes
A relationship describes an association
between two or more entities
There are three basic entity relationships:
One-to-One, One-to-Many, Many-to-Many
T.Simmonds 19
 Data and metadata
A database is a self-describing collection of
integrated tables.
 Integrated tables are tables that store both data and the
relationship among the data.
 self-describing because it contains a description of
itself.
 Thus, databases contain not only tables of user data, but
also tables of data that describe that user data.
 Such descriptive data is called metadata because it is
data about data.
 Collection of metadata is often called data dictionary.
T.Simmonds 20
 What Is SQL?
Structured Query Language (SQL) is the
American National Standards Institute (ANSI)
standard language for operating relational
databases
SQL provides statements for a variety of tasks,
including:
 Querying data
 Inserting, updating, and deleting rows in a table
 Creating, replacing, altering, and dropping objects
 Controlling access to the database and its objects
 Guaranteeing database consistency and integrity
T.Simmonds 21
 SQL Statements
SELECT
INSERT
UPDATE Data manipulation language (DML)
DELETE
MERGE

CREATE
ALTER
DROP
RENAME Data definition language (DDL)
TRUNCATE
COMMENT

GRANT
Data control language (DCL)
REVOKE
COMMIT
ROLLBACK Transaction control language (TCL)
SAVEPOINT

T.Simmonds 22
Introduction
and
Databases Overview

Part 3
Properties of a database
 Properties of a database
The term database usually implies series of related
properties:
 Data abstraction
 Data sharing
 Data definition
 Data integrity
 Data security
 Data independence
 Data concurrency
 Data consistency

T.Simmonds 24
 Data abstraction and Data Sharing
Data abstraction
 A database can be viewed as a model of reality.
 The information stored in a database is usually an attempt to
represent the properties of some objects in the real world.
 For example, an academic database is meant to record relevant
details of university activity
Data Sharing
 Data stored in a database is not usually
held solely for the use of one person.
 A database is normally expected to be accessible by more
than one person, perhaps at the same time.
 For example a student database might be accessible by members of
T.Simmonds
not only academic but also administrative staff. 25
 Data Definition and Data Integrity
Data definition
 involves describing the properties of the data that go into
each database table.
 Each column has
 Name (must be unique within the table).
 Data type (such as Number, Date/Time, Text).
 Properties (such as size, format , any allowable range etc.)
 Description (an optional description of the data).

Data integrity
 means that data in a database adheres to specified
business rules,
 refers to maintaining and assuring
the accuracy of data over its entire life-cycle.
 To avoid “Garbage in – garbage out”.
T.Simmonds 26
 Data Security and Data Privacy
Data security refers to protecting data against
destruction and misuse – both intentional and
accidental
It involves protecting database access by users
 usernames, passwords, user privileges
And protecting against data loss
 backup and disaster-recovery
A company that stores data about individuals
is responsible for protecting the privacy of that
data

T.Simmonds 27
 Data Independence
One of the main requirements of the database
system is the idea of buffering data from the
processes that use such data.
Data separated from all programs that make use of
the data.
The data remains
 accessible
 stable
 and cannot be
corrupted by the
applications using it
T.Simmonds 28
 Data concurrency and Data consistency
Data concurrency
 ensures that multiple users can
access data at the same time

Data consistency
 ensures that each user sees a consistent view of the
data,
 including visible changes made
by the user's own transactions and
committed transactions of other users

T.Simmonds 29
Introduction
and
Databases Overview

Part 4
DBA
 Database domains

T.Simmonds 31
 Database Administrator
 Database administrator is a person responsible
for the design, implementation, maintenance and
repair of an organization's database.
The role includes the development and design of
database strategies, monitoring and improving
database performance and capacity, and planning
for future expansion requirements.
They may also plan, co-ordinate and implement
security measures to safeguard the database.

T.Simmonds 32
 Database Giants
Oracle
 Oracle 12c Database
 MySQL

Microsoft
 SQL Server 2012

IBM
 DB2
T.Simmonds 33
 Oracle Database
Oracle Database is a powerful and robust DBMS
that runs on many different operating systems,
including Windows 7, Windows Server 2008 R2,
several variations of UNIX, and Linux.
It is a very popular DBMS, and it has a long history
of development and use.
Oracle Database exposes much of its technology to
the developer; consequently, it can be tuned and
tailored in many ways.

T.Simmonds 34
 Oracle’s RDMS

Oracle
server

Data
User tables
dictionary

T.Simmonds 35
 Oracle Database Tools
Query Tools Administration Tools
 SQL*Plus (command line)  Database Configuration
 SQL Developer (GUI) Assistant
 Discoverer (Reporting tool)  Oracle Net Manager
Developer Tools  Oracle Enterprise Manager
 SQL Developer  Recovery Manager
 Forms
 Reports
 JDeveloper

Moving Data Tools

 Data Pump
 SQL Loader

T.Simmonds 36
 Essentials
 What is a database?
 Database Management Systems
 Properties of a database
 Databases evolution
 Different types of database
 Relational Database Model
 Data concepts and characteristics
 DBA responsibilities
Oracle RDBMS and Tools

T.Simmonds 37