Database Systems Planning and Administration by elc18177

VIEWS: 17 PAGES: 36

									                                                       DBS3Planning:1




     Database Systems
Planning and Administration


      Strategic
        Data
      Planning
                                                 Database
                             Data
                                               Administration
                          Administration
       Planning




           Planning/
         Administration            Administration
                                                         DBS3Planning:2



                                Data
                             Requirements
               Processes



     IS Priorities



Data Architecture
                                 Pragmatic
Integration                      Outcomes



                                             Strategic
                                               Data
Future                                       Planning


                     Corporate
 Logical             Database
                      Model
                                          DBS3Planning:3

           Application Data Model Types




Logical
             Legacy           Strategic

Physical
             Legacy           Strategic
            Current            Future
                                                  DBS3Planning:4

          Corporate Data Modelling



 real
world

        requirements
          analysis

                       conceptual
                       modelling

                                      Corporate
                                     Data Model
                                                               DBS3Planning:5
                  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
                                                                             DBS3Planning:6

                             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.
                                                      DBS3Planning:7

                     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
                                                                               DBS3Planning:8

                                    MDSM


Information          Korner Data Model Report (KDMR)
Mangement Centre
(IMC)                                                                        1989
                      Minimum Data Set Model (MDSM)
                                                                             1991

                      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)
                                                                      DBS3Planning:9
MDSM
MDS07 Accident and                                 MDS01
                                        Health                  MDS01
Emergency Service                      Authority   Hospital
Attendances                                          On         Service
                     GP                             Site         Point



                             Patient                      AandE
                                                        Department
  Incident      AandE
    Type        Cause



                           AandE                          AandE
                           Episode                         Dept          Lodged
                                                        Operational      Patient
                                       Departure           Plan
  InitiatorOf    ModeOf
  Attendance     Arrival                Method

                                                   A Data Model
                                                   from MDSM
                        AandE
                      Attendance
                                                            DBS3Planning:10

                   A Dictionary Entry from MDSM
AandEAttendance
(Accident and Emergency Attendance)
  Health Authority Code                   K - Primary Key
                                          F - Foreign Key
  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*
                                                                                 DBS3Planning:11


                     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.
                                                                                                           DBS3Planning:12
                                              Untimely
                                               Data
                                Conflicting                  Irrelevant
                                  Data                          Data
           Inconsistent                                                                       Logical
                                                                                                             Physical
              Data                                                                 Conceptual


                  Multiple                    Need
                   Data
                 Definitions                                                        Passive
                                                                                                             Level
                 Data                                                          Active
               Definition
                                 Data
                                Sharing
        Data
       Control
                                                                 Data                                     Data
                                                              Administration                            Dictionary
       Consultancy


   Corporate
                            Scope
   Awareness

                                                           Data
 Corporate                                               Integrity
Requirements
                                                  Data
           Data Analysis                        Dictionary
                                               Management
                         Data        Data
                        Security    Privacy
                                                                                 DBS3Planning:13

                             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.
                                                                                 DBS3Planning:14
                      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
                                                                               DBS3Planning:15

                    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.
                                                                                DBS3Planning:16
                             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
                                                   DBS3Planning:17

                Database Security



Data Administration

                                Computer-based
                                Measures
   Threats
                              Non-Computer-based
                              Measures
                                                                                  DBS3Planning:18


                        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.
                                                                                                    DBS3Planning:19


                      Tuning         Irrelevant
                      DBMS              Data

         Monitoring
          Usage
                                                                                                       Training
                                                                               Privacy,Security
  Configuration
                                                                                   Integrity
    Control
                                                                    Impact
                               Administration                      Asessment
    Installation                 DBMS



    Backup&
    Recovery                                                                           Database
                                                                                      Environment
                                                    Database
    Data
                                                  Administration
  Archiving

   Tuning
  Databases
                       Administration
                         Database                                                         Data
Monitoring                                                                               Control
Data Usage
                                                              View

       Data                                                        Create View                       Revoke
     Standards
                         Physical
                                                                                    Grant
                         Design
                                                                                DBS3Planning:20


                           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.
                                                   DBS3Planning:21

        Key Functions of Database Administration


• Administration of the Database
• Administration of the DBMS
• Administration of the Database Environment
                                                                            DBS3Planning:22

                   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
                                                                          DBS3Planning:23

                     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
                                                                             DBS3Planning:24

         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
                                                 DBS3Planning:25
        Database Administration Tools




        Data Management Subsystem
                   DBMS

   Data         Information       Database
Maintenance      Retrieval      Administration




                  Database
                                   DBS3Planning:26



                      DBA Issues


• Storage
• Data Control (Security)
• Backup/recovery
• Performance
• Distribution
                                                  DBS3Planning:27

DBMS, File Manager, Disk Manager Interaction

             Application System
                        Requests rows

                   DBMS
                        Requests files

                File Manager

                       Requests logical pages

                Disk Manager
                        Requests physical pages


                    Disk
                                                            DBS3Planning:28

              Oracle Physical and Logical Structures

User
Structure   User Table A                   User Table B


                            Data                             Data
Logical     Tablespace A    Blocks         Tablespace B
                                                             Blocks
Structure




Physical
Structure      File 1                     File 2

                                                   File 3
                                             DBS3Planning:29

               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;
                                                               DBS3Planning:30

                           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)
                                                                          DBS3Planning:31

                                        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'
                                                    DBS3Planning:32

                     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
                                                 DBS3Planning:33

                     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
                                                          DBS3Planning:34


                   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.
                                                                                 DBS3Planning:35

                                  Database Recovery

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




                                      Backup
                                                               Backup
                 Database
                                                                Copy


                                       Recovery

                                          Log
                                          File
                                                                                 DBS3Planning:36


                     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.

								
To top