ITC114

1
ITC114

• Database Administration

2
Objectives

• At the completion of this lecture you should be
  able to
• Describe and contrast the technical managerial
  roles of the Database Administrator.
• Explain the role of the data dictionary.
• Outline the role of CASE tool in management of a
  database.
• Describe the requirements for the successful
  implementation of a data administration strategy.

3
Database Administration

• Data is a corporate asset.
• Data is a resource to be translated into
  information.
• To maximise on the value of data, managers must
  understand the value of information (processed
  data)
• The responsibility for supervising both the
  database and the use of the DBMS resides with the
  database administration.

4
Data-Information Decision Cycle
Decision making
User
triggers
used in
Knowledge
Actions
interprets
analysis
that is the basis of
generates more
Data
Information
converted
5
What does a DBMS do?

• ensures data is processed according to
  requirements integrity constraints.
• distributes data information in a timely
  manner.
• ensures the security of the data.
• Maintains data independence allows changes in
  the structure of the database without the
  applications being affected.
• supports managerial decision making at all
  levels.
• Allows for the replication of data at different
  locations.
• Provide facilities to recover the database in the
  even of damage to the database.

6
Managerial Decision Making Levels
Top managemt
DBMS
Middle managemt
Operational managemt
Database
Databases predominant role to support managerial
decision making at all levels.
7
A DBMS is a tool to manage data.

• DBMS must be properly managed from
• Technological side
• involves selecting, installing, configuring
  monitoring of DBMS. Requires approximately
  technical skills.
• Managerial side
• DBMS does not guarantee a good information
  system, it still requires good management and
  organisational support.
• Cultural side
• can create resistance as involves change. e.g.
  data ownership will be shared, value, accuracy,
  security, privacy all issues.

8
Role of DBA versus Data Administrator

• Data Administrator
• Responsible for developing the enterprise data
  model plus the maintenance of data definitions
  and standards.
• Database Administrator (DBA)
• A technical and managerial function responsible
  for all aspects of the physical database analysis
  and design plus including such as its
  performance, the security, and backup and
  recovery.

9
More on the data administrator

• Data Administrator (DA) provides a global view
  of the organisations data (manual
  computerised). The role could be consumed by the
  DBA but it typically is a lower managerial level
  compared to the DBA.
• Responsibilities include issues of
• determine the data requirements of the
  organisation.
• data consistency integrity.
• highlighting data as a corporate resource.

10
Career path to be a DBA
Programmer Analyst
ProjectLeader / DA
Database Administrator
Programmer
11
Role of Database Administrator (DBA)

• Database Administrator
• is a both a technical and a management function
  responsible for the physical database support.
• The DBA is charged with all aspects of a DBMS,
  including selection and management.
• basically responsible for supervising the
  database and the use of the DBMS.
• Responsibilities skills can be broken into
• managerial.
• technical.

12
Management qualities of a DBA

• ability to be able to effectively liaise with
  end-users.
• be able to develop policies and procedures
  (standards).
• Skills in
• planning
• analysis, design, implementation, testing
  procedures
• data security, privacy and integrity.
• backup disaster recovery procedures.
• The qualities of the DBA lie between the
  technical and the managerial activities

13
Technical Role of the DBA

• Generic Skills
• Specific tasks of the DBA in selecting DBMS
• choose the DBMS model.
• storage capacity, add-on support, security and
  integrity, backup-recovery, concurrency
  controls, performance, database admin support,
  H/W requirements, cost, training support.
• data loading, conversion, access plan.
• affording access rights to users.

14
Access privileges and security

• Access
• Users should access the database only in ways in
  which they are entitled.
• Defined by the DBA
• Security
• Protection of data against accidental or
  intentional loss, destruction or misuse. Can
  include
• Human error
• Software failure
• Hardware failure

15
Security issues .

• Theft of Information
• Improper data access
• Privacy issues
• Confidentiality
• Unauthorised modification of data
• Unauthorised destruction of data

16
What are some of the security issues

• Authorisation
• Access restrictions ensure that the database is
  secure.
• Effective password protection is critical however
  .
• Can be shared
• Can be written down and copied
• Automatically login can be employed
• Unencrypted passwords can be intercepted.
• Alternatives
• Biometrics
• Digital certificates

17
(No Transcript)
18
Other duties of the database administrator

• enforcement of standards programming, naming
  conventions, testing procedures, H/W upgrade
  standards, change control ..
• maintaining the data dictionary
• provide assistance and support during the
  conceptual, logical and physical design.
• database design support.
• testing and evaluation of the database.training

19
The database administrator can use a checklist to
assist in the selection and the evaluation of the
DBMS
20
The DBA can evaluate the purchase of a DBMS in
terms of the categories lists in this figure.
21
and yes . there is more .
22
Database Administration Strategy

• depends on the organisation.
• can be referred to by different names.
• need to link it to the corporate strategy.
• it is a framework of management/ support for
  organisations data (asset).

23
Planning for a disaster

• A DBA must plan for disasters
• Intentional unauthorised access
• Accidental disk problem, power outage, computer
  malfunction, flood or some other natural disaster
• DBA must take an active role in a disaster
  recovery plan
• So how do you plan for a disaster?
• you must know the value of your data .
• for what period can your organisation function
  without access to the data?
• The DBA must be proactive in developing a
  disaster recovery plan.

24
Disaster Recovery Plan

• Typically a journal or a file will be accessed to
  obtain information about the state of the
  database just before the disaster.
• PC-based DBMS machines lack facilities to
  maintain an effective journal ? limited recovery
  is possible.
• Alternative is to add additional DBMS features to
  support the database

25
How does the journal operate?
A journal records changes made to the database
basically creates an audit trail
To recover the database the back copy is
recovered first, and then the journal is used to
simulate changes since the last backup.
26
What is the purpose of an archive?

• Archiving is the process of off-loading
  documents and records to an alternate disk space.

27
Backup facilities

• Ensure automatic dump of the database
• Periodic nightly, weekly, monthly
• Backup completed at specific time may require
  shut down of the part/whole system.

28
So what happens if the database fails .

• Aborts transaction typically rollback
• Incorrect data rollback
• System failure prefer approach use redundant
  system
• Database destruction again switch to redundant
  system otherwise roll forward

29
Where does the position of DBA fit?

• initially was a functional responsibility.
• depends on the organisation.
• Should be a very high managerial position.

30
Resources of the DBA

• Data Dictionary
• Case tools
• Project Management tools
• Decision Support Systems

31
Resources of the DBA

• The Data Dictionary
• - stores the definitions and relationships of
  the DBMS.
• Classified by
• stand-alone or integrated.
• active or passive.

32
Data Dictionary

• A data dictionary is a comprehensive document
  that defines the everything about a system.
• A DD not only includes the actual data but also
  metadata - data about the database e.g.
• table definitions
• indexes
• who created the database
• valid users
• valid applications that can access the DBMS
• Authorisations / security

33
More on the data dictionary

• no standard to a DD but the more it supports the
  DBMS the better.
• Some authors may refer to it as a Project
  Dictionary
• you must be able to access the data dictionary in
  the same way as you would the actual data -
  therefore can use SQL e.g.
• SELECT NAME, CTIME FROM SYSTABLES WHERE
  CREATOR ATKINSON

34
Resources of the DBA

• 2. CASE tools
• (Computer Aided SoftwareEngineering)
• Can be classified as
• Front-end CASE tools (support for planning,
  analysis design) .
• Back-end CASE tools (support for coding
  implementation).

35
Functions of a CASE tool

• Graphics in particular model drawing facilities
• Screen designers and report generators
• An integrated repository (DD)
• An analysis component to provide an automated
  check on system consistency, syntax and
  completeness
• A program documentation generator.
• Central to a CASE tool is the central repository,
  basically the DD, to integrate all the data.

36
Resources of the DBA

• 3. Project Management resources / skills
• Basically requires the DBA to coordinate,
  direct, control and manage the timing of any
  project work. Software to do this can include
• MS Project
• GANNT Charts
• PERT chart
• ITC301

37
Resources of the DBA

• 4. Decision Support Systems
• DSS is software the DBA to help make the
  decision maker make decisions.
• - Alternatively it could be Executive
  Information Systems (EIS)
• - Assisted through use of data warehousing
  technology