Data and database administration - PowerPoint PPT Presentation

Loading...

PPT – Data and database administration PowerPoint presentation | free to download - id: 5a6b82-ODQ5N



Loading


The Adobe Flash plugin is needed to view this content

Get the plugin now

View by Category
About This Presentation
Title:

Data and database administration

Description:

Title: Data and database administration Author: mrina Last modified by: mrina Created Date: 7/4/2008 12:43:26 AM Document presentation format: On-screen Show (4:3) – PowerPoint PPT presentation

Number of Views:44
Avg rating:3.0/5.0
Slides: 23
Provided by: MRI53
Category:

less

Write a Comment
User Comments (0)
Transcript and Presenter's Notes

Title: Data and database administration


1
Data and database administration
2
Learning outcomes
  • At the end of this chapter, you should be able
    to
  • Define terms related to data dbase
    administration
  • List functions and roles of data/database
    administration
  • Describe role of data dictionaries and
    information repositories

3
Traditional Administration Definitions
  • Data Administration A high-level function that
    is responsible for the overall management of data
    resources in an organization, including
    maintaining corporate-wide definitions and
    standards
  • Database Administration A technical function
    that is responsible for physical database design
    and for dealing with technical issues such as
    security enforcement, database performance, and
    backup and recovery

4
Traditional Data Administration Functions
  • Data policies, procedures, standards
  • Data Policies statements that make explicit the
    goals of DA
  • Data procedure written outlines of actions to be
    taken to perform certain activity
  • Data Standard explicit conventions and behaviors
    that are to be followed and that can be used to
    help evaluate dbase quality
  • Planning
  • Data conflict (ownership) resolution
  • Managing the information repository
  • Internal marketing of DA concepts

5
Data Policies, Procedures, standards
  1. All users must have passwords
  2. Password must be changed every 6 months
  3. Password must have minimum of 7 characters
  4. Password must have maximum of 12 characters
  5. Password must be combination of alphabet and
    numbers
  6. I/C number, names, birth date cannot be used as
    passwords.

6
Data Policies, Procedures, standards
  • To create password
  • End user send request to DBA
  • DBA approves and forward it to computer operator
  • Computer operator creates account, assign
    temporary password and end it to the user.
  • A copy of the account information is sent to the
    DBA
  • User changes the temporary password.

7
Traditional Database Administration Functions
  • Selection of DBMS and software tools
  • Installing/upgrading DBMS
  • Tuning database performance
  • Improving query processing performance
  • Managing data security, privacy, and integrity
  • Data backup and recovery

8
Functions of DA DBA
DATABASE PLANNING
Develop corporate database strategy/policies
Develop enterprise model (information architecture)
Develop cost/benefit models
Design database environment/select technologies
Develop and market data administration plan
DATABASE ANALYSIS
Define and model data requirements (conceptual)
Define and model business rules
Define operational requirements
Resolve requirements conflicts
Maintain corporate data dictionary/repository
DA
9
Functions of DA DBA
DATABASE DESIGN
Perform logical database design
Design external model (sub schemas)
Design internal (physical) models
Design integrity controls
DATABASE IMPLEMENTATION
Specify database access policies
Establish security controls
Install DBMS
Supervise database loading
Specify test procedures
Develop application programming standards
Establish procedures for backup and recovery
Conduct user training
DBA
DA/DBA
DBA
DA/DBA
10
Functions of DA DBA
OPERATIONS AND MAINTENANCE
Backup and recover databases
Upgrade DBMS
Monitor database performance
Tune and reorganize databases
Resolve access conflict
Tune and rewrite queries
Enforce standards and procedures
Support users
GROWTH AND CHANGE
Implement change-control procedure
Plan growth and change
Evaluate new technology
DBA
DA/DBA
11
Evolving Approaches to Data Administration
  • Blend data and database administration into one
    role
  • Fast-track developmentmonitoring development
    process
  • Database planning improve selection of
    technology
  • Database analysis work on physical design in
    parallel with development of the logical
    physical models
  • Database design prioritize application by
    volume, importance complexity
  • Database implementation
  • Database maintenance

12
Evolving Approaches to Data Administration
  • New DBA Roles
  • Procedural DBAsmanaging quality of triggers and
    stored procedures
  • eDBAmanaging Internet-enabled database
    applications
  • PDA DBAdata synchronization and personal
    database management
  • Data warehouse administration

13
Data Warehouse Administration
  • New role, coming with the growth in data
    warehouses
  • Similar to DA/DBA roles
  • Emphasis on integration and coordination of
    metadata/data across many data sources
  • Specific roles
  • Support DSS applications
  • Manage data warehouse growth
  • Establish service level agreements regarding data
    warehouses and data marts

14
Open Source DBMSs
  • DEFINITION
  • Free DBMS source code software that provides the
    core functionality of an SQL-compliant DBMS
  • An alternative to proprietary packages such as
    Oracle, Microsoft SQL Server, or Microsoft Access
  • mySQL is an example of an open-source DBMS
  • Advantages
  • Less expensive than proprietary packages
  • Source code available, for modification
  • Have different version (simple to complex, free
    to some cost(additional features)
  • No additional cost for copies or licensing

15
Open Source DBMSs
  • Disadvantages
  • Absence of complete documentation
  • Ambiguous licensing concerns
  • Not as feature-rich as proprietary DBMSs
  • Vendors may not have certification programs

16
Open Source DBMSs
  • Consider these factors when choosing an open
    source DBMS
  • Features
  • Support
  • Ease-of-use
  • Stability
  • Speed
  • Training
  • Licensing

17
Data modeling responsibilities
18
Information in this slides were taken from Modern
Database Management System, Ninth edition by
Jeffrey A.Hoffer, Mary B.Prescott Heikki Topi.
  • END OF CHAPTER

19
Routines and Triggers
  • Routines
  • Program modules that execute on demand
  • Functionsroutines that return values and take
    input parameters
  • Proceduresroutines that do not return values and
    can take input or output parameters
  • Triggers
  • Routines that execute in response to a database
    event (INSERT, UPDATE, or DELETE)

20
Routines and Triggers
21
Stored Procedure
  • CREATE PROCEDURE sp_GetInventory_at_location
    varchar(10)ASSELECT Product, QuantityFROM
    InventoryWHERE Warehouse _at_location
  • EXECUTE sp_GetInventory Kajang'

22
Triggers
  • CREATE TRIGGER STANDARD_PRICE_UPDATE
  • AFTER UPDATE OF STANDARD_PRICE ON PRODUCT_T
  • FOR EACH ROW
  • INSERT INTO PRICE_UPDATES_T VALUES
  • (PRODUCT_DESCRIPTION, DATE, STANDARD_PRICE)
About PowerShow.com