Database systems planning and administration l.jpg
This presentation is the property of its rightful owner.
Sponsored Links
1 / 36

Database Systems Planning and Administration PowerPoint PPT Presentation


  • 41 Views
  • Uploaded on
  • Presentation posted in: General

Database Systems Planning and Administration. Strategic Data Planning. Database Administration. Data Administration. Planning. Planning/ Administration. Administration. Strategic Data Planning. Data Requirements. Processes. IS Priorities. Data Architecture. Pragmatic Outcomes.

Download Presentation

Database Systems Planning and Administration

An Image/Link below is provided (as is) to download presentation

Download Policy: Content on the Website is provided to you AS IS for your information and personal use and may not be sold / licensed / shared on other websites without getting consent from its author.While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server.


- - - - - - - - - - - - - - - - - - - - - - - - - - E N D - - - - - - - - - - - - - - - - - - - - - - - - - -

Presentation Transcript


Database systems planning and administration l.jpg

Database SystemsPlanning and Administration

Strategic

Data

Planning

Database

Administration

Data

Administration

Planning

Planning/Administration

Administration


Strategic data planning l.jpg

StrategicDataPlanning

Data

Requirements

Processes

IS Priorities

Data Architecture

Pragmatic

Outcomes

Integration

Future

Corporate

Database

Model

Logical


Application data model types l.jpg

Application Data Model Types

Legacy

Strategic

Logical

Legacy

Strategic

Physical

Current

Future


Corporate data modelling l.jpg

Corporate Data Modelling

real

world

requirements

analysis

conceptual

modelling

Corporate

Data Model


Types of corporate data model l.jpg

Types of Corporate Data Model

  • 1. High-level data map

    • Controlling the information systems development process

  • 2. Minimum data set

    • Standardising data capture and usage


Rationale for cdm l.jpg

Rationale for CDM

CDM = important tool for information management

1. Encourages the production of integrated and inter-operable systems:

a. Reduces the replication of data and systems across organisations.

b. Introduces cost-savings in development effort.

2. Can be used as an important element of information systems planning.

a. To determine which systems need to be produced to serve business needs.

b. Enables prioritisation of development options.

c. Encourages consideration of out-sourcing of systems.

3. Can be used to identify the strategic use of information systems.


Cdm in the nhs l.jpg

CDM in the NHS

NHS/DHSS Health Services Information Steering Group

Appointed in 1980

Chaired by Mrs Edith Korner

Remit to identify a minimum data set to be used routinely for management purposes in every health authority

Number of working groups set up with membership from the NHS

Korner Data Model Report (KDMR)

1985


Slide8 l.jpg

MDSM

Korner Data Model Report (KDMR)

Information Mangement Centre (IMC)

1989

1991

Minimum Data Set Model (MDSM)

The Data Dictionary

'to define for health authorities those data which they have all agreed to collect consistently. The model provides a tool for assessing ready-made systems, for creating new ones and for evaluating the impact of proposals for change' (NHS IMC, 1989)


A data model from mdsm l.jpg

A Data Model from MDSM

MDSM

MDS07 Accident and

Emergency Service

Attendances

Health

Authority

MDS01

MDS01

Hospital

On

Site

Service

Point

GP

Patient

AandE

Department

Incident

Type

AandE

Cause

AandE

Dept

Operational

Plan

AandE

Episode

Lodged

Patient

Departure

Method

InitiatorOf

Attendance

ModeOf

Arrival

AandE

Attendance


A dictionary entry from mdsm l.jpg

A Dictionary Entry from MDSM

AandEAttendance

(Accident and Emergency Attendance)

Health Authority Code

Service Point Code

District Patient Number

Arrival Date

Arrival Time

First Attendance

Initiator of Attendance

*F INITIATOR OF ATTENDANCE*

Method of Departure

*F DEPARTURE METHOD*

Mode of Arrival

*F MODE OF ARRIVAL*

Start Date

*F AandE Episode*

K - Primary Key

F - Foreign Key


Summary strategic data planning l.jpg

Summary - Strategic Data Planning

  • Strategic data planning is the activity devoted to the construction of future/logical data models.

  • A corporate data model is a map of the data requirements of the whole or a substantial part of an organisation.

  • There are two main types of corporate data model: a type 1 CDM maps key features of the data terrain; a type 2 CDM provides a minimum data set for key organisational functions.

  • Organisations generally conduct strategic data planning with one or more of the following objectives in mind: the implementation of integrated information systems; the development of a corporate wide data architecture; the clear identification of information systems priorities; the rethinking of an organisation’s key processes; the education and communication of data requirements throughout the organisation.

  • Strategic data planning may be relavant only for certain forms of organisation. Generally speaking those orginisations with homogeneous and interdependent subunits will benefit most from the data integration arising from strategic data planning.


Data administration l.jpg

DataAdministration

Untimely

Data

Conflicting

Data

Irrelevant

Data

Logical

Inconsistent

Data

Physical

Conceptual

Need

Multiple

Data

Definitions

Passive

Level

Active

Data

Definition

Data

Sharing

Data

Control

Data

Dictionary

Consultancy

Scope

Corporate

Awareness

Data

Integrity

Corporate

Requirements

Data Analysis

Data

Dictionary

Management

Data

Security

Data

Privacy


Data administration13 l.jpg

Data Administration

Data administration is that function concerned with the management, planning and documentation of the data resource of some organisation.

Data administration is concerned with the management of an organisation’s meta-data, that is, data about data. It is a function which deals with the conceptual or business view of an organisation's data resource.


The scope of data administration l.jpg

The Scope of Data Administration

  • Consultancy. Offering consultancy on all aspects related to an organisation’s meta-data, particularly expertise in data analysis.

  • Corporate Awareness. Educating to increase awareness of the importance of data. Also disseminating information on what data exists and for what purpose.

  • Corporate Requirements. Identifying corporate data requirements. Particularly building a corporate data architecture which incorporates strategic planning.

  • Data Analysis. Coordinating the use of a standard data analysis methodology. Using such a methodology to develop business data models.

  • Data Control. Implementing standards for ensuring that access to data is controlled. Also ensuring that suitable recovery procedures are in place.

  • Data Security. Ensuring that both technical and administrative controls are in place to protect against threats.

  • Data Definition. Implementing standards for the definition of data and controlling the medium for the recording and communication of such definitions.

  • Data Integrity. Implementing standard mechanisms for ensuring the integrity of an organisation’s data. Also, documenting the rules for ensuring integrity.

  • Data Dictionary Management. Promoting the use of a logical data dictionary and implementing standards for its control. Also monitoring the use of and content of the data dictionary.

  • Data Privacy. Implementing procedures to ensure that the organisation complies with any legislation concerning national data regulation.

  • Data Sharing. To encourage sharing of data across applications and to promote the idea of data that is independent of applications


The need for data administration l.jpg

The Need for Data Administration

  • A number of applications are developing within some organisation which use different definitions for the same data.

  • Data held by a number of diverse applications is inconsistent.

  • Decision-makers within an organisation receive conflicting data from different sources within that organisation.

  • Decision-makers receive data too late for it to be useful.

  • Decision-makers receive too much irrelevant data.

  • There are notable gaps in the data collected by an organisation.

  • Departments within an organisation have no clear idea why they collect certain data.


The data dictionary l.jpg

The Data Dictionary

  • A data dictionary is a means for recording the meta-data of some organisation

Conceptual Data Dictionary

Active Data Dictionary

Logical Data Dictionary

Passive Data Dictionary

Physical Data Dictionary


Database security l.jpg

Database Security

Data Administration

Threats

Computer-based Measures

Non-Computer-based Measures


Summary data administration l.jpg

Summary - Data Administration

  • Database technology, because of its centrality in the modern IS architecture has stimulated the development of a large range of roles for servicing this technology. Two of the most common-place roles are the data administrator and the database administrator.

  • The data administrator is a high-level, corporate function.

  • As far as development is concerned, the data administrator will be involved in the analysis and design of a database system. Data administrators are also particularly involved in the management of the organisational data resource.

  • The main tool of the data administrator is the data dictionary, particularly conceptual and logical data dictionaries. A data dictionary is a means for recording the metadata of some organisation

  • Database security is a concern of the data administrator in association with the database administrator. Database security involves protecting a database from external threat by using computer-based and non-computer-based controls.


Database administration l.jpg

DatabaseAdministration

Tuning

DBMS

Irrelevant

Data

Monitoring

Usage

Training

Privacy,Security

Integrity

Configuration

Control

Administration

DBMS

Impact

Asessment

Installation

Database

Environment

Backup&

Recovery

Data

Archiving

Tuning

Databases

Administration

Database

Data

Control

Monitoring

Data Usage

View

Create View

Revoke

Data

Standards

Physical

Design

Grant


Database administration20 l.jpg

Database Administration

The database administrator is responsible for the technical implementation of database systems, managing the database systems currently in use and setting and enforcing policies for their use.

Whereas the data administrator works primarily at the conceptual level of business data, the database administrator works primarily at the physical level.

The place where the data administrator and the database administrator meet is at the logical level. Both the data administrator and database administrator must be involved in the system-independent specification and management of data.


Key functions of database administration l.jpg

Key Functions of Database Administration

  • Administration of the Database

  • Administration of the DBMS

  • Administration of the Database Environment


Administration of the database l.jpg

Administration of the Database

  • Physical Design. Whereas the data administrator will be concerned with the conceptual and logical design of database systems, the database administrator will be concerned with the physical design and implementation of databases

  • Data Standards and Documentation. Ensuring that physical data is documented in a standard way such that multiple applications and end-users can access the data effectively

  • Monitoring Data Usage and Tuning Database Structures. Monitoring live running against a database and modifying the schema or access mechanisms to increase the performance of such systems

  • Data Archiving. Establishing a strategy for archiving of ‘dead’ data

  • Data Backup and Recovery. Establishing a procedure for backing-up data and recovering data in the event of hardware or software failure


Administration of the dbms l.jpg

Administration of the DBMS

  • Installation. Taking key responsibility for installing DBMS or DBMS components

  • Configuration Control. Enforcing policies and procedures for managing updates and changes to the software of the database system

  • Monitoring DBMS Usage and Tuning DBMS. Monitoring live running of database systems and tailoring elements of the DBMS structure to ensure the effective performance of such systems


Administration of the database environment l.jpg

Administration of the Database Environment

  • Data Control. Establishing user groups, assigning passwords, granting access to DBMS facilities, granting access to databases

  • Impact Assessment. Assessing the impact of any changes in the use of data held within database systems

  • Privacy, Security and Integrity. Ensuring that the strategies laid down by data administration for data integrity, security and privacy are adhered to at the physical level

  • Training. Holding responsibility for the education and training of users in the principles and policies of database use


Database administration tools l.jpg

Database Administration Tools

Data Management Subsystem

DBMS

Data

Maintenance

Information

Retrieval

Database

Administration

Database


Dba issues l.jpg

DBA Issues

  • Storage

  • Data Control (Security)

  • Backup/recovery

  • Performance

  • Distribution


Dbms file manager disk manager interaction l.jpg

DBMS, File Manager, Disk Manager Interaction

Application System

Requests rows

DBMS

Requests files

File Manager

Requests logical pages

Disk Manager

Requests physical pages

Disk


Oracle physical and logical structures l.jpg

User Table A

User Table B

Oracle Physical and Logical Structures

User

Structure

Data

Blocks

Data

Blocks

Tablespace A

Tablespace B

Logical

Structure

Physical

Structure

File 2

File 1

File 3


Creating an oracle database l.jpg

Creating an Oracle Database

CREATE TABLESPACE TA

DATAFILE ‘file2.ora’ SIZE 100 MB,

‘file4.ora’ SIZE 100MB;

ALTER TABLESPACE TA ADD

‘file6.ora SIZE 20MB;


Data control l.jpg

Data Control

  • 1. Prevent would-be users from logging on to the database

  • (non-standard facilities)

  • 2. Allocate access to specific parts of the database to specific users

  • (standard facilities)

  • 3. Allocate access to specific operations to specific users

  • (non-standard facilities)


Views l.jpg

Views

  • Three main uses for views: Simplification. Functionality. Security.

  • CREATE VIEW S1 AS

  • SELECT *

  • FROM Students

  • WHERE courseCode = 'CSD'

  • CREATE VIEW S2 AS

  • SELECT studentName, sex, courseCode

  • FROM Students

  • SELECT *

  • FROM S1

  • WHERE sex = 'F'


  • Grant and revoke l.jpg

    GRANT and REVOKE

    GRANT [ALL : SELECT : INSERT : UPDATE : DELETE ]

    ON [<table name> : <view name>]

    TO <user name>

    GRANT INSERT ON Modules TO pbd

    GRANT SELECT ON Lecturers TO pbd

    REVOKE [ALL : SELECT : INSERT : UPDATE : DELETE ]

    ON [<table name> : <view name>]

    FROM <user name>

    REVOKE INSERT ON Modules FROM pbd

    REVOKE SELECT ON Lecturers FROM pbd


    Granting access via views l.jpg

    Granting Access via Views

    CREATE VIEW Evans AS

    SELECT *

    FROM Lecturers

    WHERE deptName =

    (SELECT deptName

    FROM Lecturers

    WHERE staffName = ‘Evans R’)

    AND staffName <> 'Evans R'

    GRANT SELECT, UPDATE

    ON research

    TO REvans


    Dba privileges in oracle l.jpg

    DBA Privileges in ORACLE

    GRANT {CONNECT : RESOURCE : DBA}

    TO <username>

    [IDENTIFIED BY <password>]

    Connect user is able to look at other users' data only if allowed by other users, perform data manipulation tasks specified by the DBA, and create views

    Resource privilege allows the user to create database tables and indexes and grant other users access to these tables and indexes.

    Dba privilege is normally given to a chosen few. Such privileges permit access to any user’s data, and allows the granting and revoking of access privileges to any user in the database.


    Database recovery l.jpg

    Database

    Backup

    Copy

    Log

    File

    Database Recovery

    Database recovery involves the restoration of a database to a consistent state after system failure

    Backup

    Recovery


    Summary database administration l.jpg

    Summary - Database Administration

    Database technology, because of its centrality in the modern IS architecture has stimulated the development of a large range of roles for servicing this technology. Two of the most common-place roles are the data adninistraor and the database administrator.

    Data administration is a high-level business function. Data administrators are given responsibility for documenting corporate-wide data requirements and designing database systems to meet these requirements.

    Database administration is a low-level, technical function. DBAs are give responsibility for implementing and maintaining databases run under particular DBMS. Because modern-day DBMS are complex piecs of technology, DBAs are specialists in DBMS.

    ISO SQL has a limited range of DBA functions, particularly focused on granting access to data and revoking access to data. Most DBMS also have a range of non-standard functions for declaring users and passwords, fine-tuning database sizing, monitoring the performance of a database, and fragmenting databases. ORACLE is a DBMS which offers all these facilities.


  • Login