Data and Database Administration Chapter 12 Modern Database Management

Data and Database Administration Chapter 12 Modern Database Management

Outline Introduction Traditional Approaches to data & DB administration Effective data and DB administration functions Data security Data Recovery and Backup

Introduction Data are corporate asset just like personnel, physical & financial resources. Effective data administration provides support for managerial decision making at all levels in the organization. Ineffective data administration leads to poor data utilization.

Data & Database Administration Data Administration includes overall management of data resources in an organization, including maintaining corporatewide definitions and standards. Database Administration includes physical database design and dealing with technical issues associated with databases, such as security enforcement, database performance, and backup & recovery.

Traditional Data Administration The data administrator (DA) must be senior-level manager. He/she should have both technical and managerial skills. Traditional DA functions: Database planning, analysis, design, implementation, maintenance & protection. Establishing procedures for improving database performance. Providing education, training and consulting support to users. Resolve disputes that my arise when data are centralized & shared among users.

Traditional Database Administration The database administrator (DBA) carries through the standards and procedures established by the data administrator. DBA can sometimes take up the role of data administrator too. The DBA needs to have a variety job skills. He/she should: Have broad technical background including sound understanding of current H/W architectures. Have solid understanding of data processing. Have understanding of database development cycle

Traditional Database Administration (contd.) Have strong design and modeling skills (at conceptual, logical and physical levels). Have managerial skills to deal with other info system personnel as the database is analyzed, designed & implemented. Provide support for end users involved with database design and use

Evolving Approaches to Data and Database Administration There are no universally accepted data and database administration structures. Organizations vary widely in their approaches. As business practices change, the roles are also changing. However, there is a core set of data and database administration functions that must be met in every organization. These can be termed as effective data and database administration functions.

Effective Data and Database Administration Functions Data policies, procedures and standards* Data policies are statements that make explicit the goals of data administration, e.g., “every user must have a password” Data procedures are written outlines of actions to be taken in order to perform a certain activity, e.g., backup & recovery procedures. Data standards are explicit conventions and behaviours that are to be followed and that can be used to evaluate DB quality, e.g., naming conventions for DB objects Planning involves understanding of organization’s information needs and contribution in development of information architecture. * See slide notes for detail

Effective Data and Database Administration Functions (contd.) Data conflict resolution Internal marketing In a shared data environment, data & database administrators resolve data conflicts and ownership issues. Involves enforcing procedures and policies within the organization to avoid data related problems. Managing the information repository Information repositories contain metadata and are replacing data dictionaries in many organizations. Info repositories are used throughout the DB system life cycle and serve the following*: Users, Automated CASE Tools, Applications, DBMS * See slide notes for detail

Effective Data and Database Administration Functions (contd.) Selection of hardware and software Installing and upgrading the DBMS New hardware and software are being developed at a rapid pace. The data and database administrators are expected to know about hardware/software architectures to be able to administer both inhouse-developed and off-the-shelf application. After selecting a DBMS, performance benchmarking must be conducted before installation. After installation, periodic update packages (released from vendors) should be used for regular DBMS upgrades. Tuning database performance DBs are dynamic, the initial DB design may be required to change with time to meet new requirements. Hence, databases must be rebuilt, reorganized and reindexed to achieve required performance.

Effective Data and Database Administration Functions (contd.) Improving database query processing Managing data security, privacy &integrity Workload against a database expands over time with increase in data and number of users. DBA must tune DB for performance and improve query processing time. The above must be managed by the database administrators. Data backup and recovery DBA must ensure that backup procedures are established and will allow recovery of necessary data in case of losses through application failure, hardware failure, physical or electrical disaster, human error or malfeasance.

Evolving Approaches to Data Administration Many organizations now have blended the data administration and DB administration roles together. They emphasize the capability to build a database quickly, tuning it for max performance and being able to restore it to production quickly when problems develop. Quinlan has suggested changes in data and DB administration practices that can be made at each stage of database development life cycle (pg 452,453) The DBA role will continue to evolve. This role is expected to become more specialized, e.g., distributed DB/network capacity planning DBAs, server programming DBAs, data warehousing DBAs, etc.

Data Security It is the protection of the data against accidental or intentional loss, destruction or misuse. Due to distributed databases and data access through internet & intranet, data security has become difficult. Threats to data security Threats to data security may be direct threats to databases. To ensure database security, all parts of the system must be secure, including the database, the network, the operating system, the building in which the database physically resides and the personnel who have system access.

Data Security (Contd.) Threats to be addressed in a comprehensive data security plan Accidental losses, including human error, software and hardware-caused breaches. Theft and fraud Loss of privacy or confidentiality Loss of data integrity Loss of availability

Data Security (Contd.) A comprehensive data security plan will include establishing administrative policies & procedures, physical protection and data management software protections. Security features of data management software are: Views or sub schemas they are created on one or more base tables they produce dynamic result tables at the time of request they restrict user view of database Domains, assertions, checks and other integrity controls they limit the values a field can hold, they limit the actions that can be preformed on data they are enforced by DBMS during DB querying & updating

Data Security (Contd.) Authorization rules User-defined procedures They define additional constraints or limitations in using DB Encryption Procedures they identify users, restrict access to data and user actions (e.g., insert, read, modify, delete) against DB objects They encode/scramble data Authentication Schemes They positively identify a person accessing the DB, examples: biometric devices that can detect personal characteristics (fingerprints, voice prints, retina, signature) authentication systems such as Kerberos Establishing no repudiation by biometrics & messaging Backup, journaling and check pointing capabilities They facilitate recovery procedures

Database Recovery and Backup Databases are often damaged or lost because of system problems that may be caused by: Human error, Hardware failure, Incorrect or invalid data, Program errors, Viruses, Network failures, Conflicting transactions or, Natural disasters Mechanisms for restoring a database quickly and accurately after loss or damage are known as Database recovery.

Basic Recovery Facilities A DBMS should provide four basic facilities for backup& recovery of DB Backup facilities Journalizing facilities Maintain an audit trail of transactions and database changes Checkpoint facilities Provide periodic backup copies of portions of or entire database Allow DBMS to periodically suspend all processing and synchronize its files and journals Recovery Manager Allow DBMS to restore the DB to a correct condition and restart processing transactions