In 2 sentences answer the following based on the attached PDF. Discuss the DBA’s responsibilities in formulating and enforcing database policies for access privileges, security, disaster planning, an

• Describe the need for database administration • Identify the role of the DBA • Explain the DBA’s responsibilities in formulating and enforcing database policies for access privileges, security, disaster planning, and archiving • Discuss the DBA’s administrative responsibilities for DBMS evaluation and selection, DBMS maintenance, data dictionary management, and training • Discuss the DBA’s technical responsibilities for database design, testing, and performance tuning Starks/Pratt/Last, Concepts of DB Management, 9th Edition. © 2019 Cengage. All Rights Reserved. May not be scanned, copied or duplicated, or posted to a publicly accessible website, in whole or in part. Introduction • Database approach of storing data • Has many benefits • Involves potential hazards • Fundamental difficulties of a database • Users must be made aware of the database structure; changes must be communicated • Backup and recovery must be carefully coordinated • Companies appoint a DBA to manage the database and use of the DBMS • Perform database administration tasks Starks/Pratt/Last, Concepts of DB Management, 9th Edition. © 2019 Cengage. All Rights Reserved. May not be scanned, copied or duplicated, or posted to a publicly accessible website, in whole or in part. The Role of the Database Administrator (1 of 2) • Education and qualifications • Bachelor’s degree in business, computer technology, information technology, or computer science and have two to four years of experience in databases • Duties and responsibilities • Vary greatly depending on the business or industry • Roles often overlap • Data administrator • Data architect • Database operations manager Starks/Pratt/Last, Concepts of DB Management, 9th Edition. © 2019 Cengage. All Rights Reserved. May not be scanned, copied or duplicated, or posted to a publicly accessible website, in whole or in part. The Role of the Database Administrator (2 of 2) • Database policy formulation and enforcement • Access privileges • Security • Disaster planning • Archiving • Other database administrative functions • DBMS evaluation and selection • DBMS maintenance • Data dictionary management • Training • Database technical functions • Database design Starks/Pratt/Last, Concepts of DB Management, 9th Edition. © 2019 Cengage. All Rights Reserved. May not be scanned, copied or duplicated, or posted to a publicly accessible website, in whole or in part. Database Policy Formulation and Enforcement (1 of 11) • DBA • Formulates database policies • Communicates policies to users • Enforces policies • Policies • Access privileges • Security • Disaster planning • Archiving Starks/Pratt/Last, Concepts of DB Management, 9th Edition. © 2019 Cengage. All Rights Reserved. May not be scanned, copied or duplicated, or posted to a publicly accessible website, in whole or in part. Database Policy Formulation and Enforcement (2 of 11) • Grant and revoke access privileges • DBA determines access privileges for all users • Enters appropriate authorization rules in DBMS • SQL GRANT statement • REVOKE statement • Access privilege policy • Documented by DBA • Approved by top-level management • Communicated by DBA to all users Starks/Pratt/Last, Concepts of DB Management, 9th Edition. © 2019 Cengage. All Rights Reserved. May not be scanned, copied or duplicated, or posted to a publicly accessible website, in whole or in part. Database Policy Formulation and Enforcement (3 of 11) Starks/Pratt/Last, Concepts of DB Management, 9th Edition. © 2019 Cengage. All Rights Reserved. May not be scanned, copied or duplicated, or posted to a publicly accessible website, in whole or in part. Database Policy Formulation and Enforcement (4 of 11) Starks/Pratt/Last, Concepts of DB Management, 9th Edition. © 2019 Cengage. All Rights Reserved. May not be scanned, copied or duplicated, or posted to a publicly accessible website, in whole or in part. Database Policy Formulation and Enforcement (5 of 11) • Security: prevention of unauthorized access, intentional or accidental, to database • DBA creates security policies and procedures • Obtains management approval of policies and procedures • Distributes policies and procedures to authorized users • DBMS’s security features • Encryption • Authentication • Authorizations • Views Starks/Pratt/Last, Concepts of DB Management, 9th Edition. © 2019 Cengage. All Rights Reserved. May not be scanned, copied or duplicated, or posted to a publicly accessible website, in whole or in part. Database Policy Formulation and Enforcement (6 of 11) Starks/Pratt/Last, Concepts of DB Management, 9th Edition. © 2019 Cengage. All Rights Reserved. May not be scanned, copied or duplicated, or posted to a publicly accessible website, in whole or in part. Database Policy Formulation and Enforcement (7 of 11) Starks/Pratt/Last, Concepts of DB Management, 9th Edition. © 2019 Cengage. All Rights Reserved. May not be scanned, copied or duplicated, or posted to a publicly accessible website, in whole or in part. Database Policy Formulation and Enforcement (8 of 11) • Disaster planning • Damage from physical incidents • Software, hardware, electrical, etc.

• Natural disasters • Disaster recovery plan • Ongoing and emergency actions and procedures to ensure data availability if a disaster occurs • Hard drive failures • Redundant array of inexpensive/independent drives (RAID): database updates replicated to multiple hard drives • Electrical power loss • Uninterruptible power supply (UPS): power source and power generator Starks/Pratt/Last, Concepts of DB Management, 9th Edition. © 2019 Cengage. All Rights Reserved. May not be scanned, copied or duplicated, or posted to a publicly accessible website, in whole or in part. Database Policy Formulation and Enforcement (9 of 11) • Duplicate backup systems • Hot site: completely equipped with duplicate hardware, software, and data • Can switch to hot site in minutes or hours • Warm site: duplicate hardware and software but not data • Takes longer to start processing Starks/Pratt/Last, Concepts of DB Management, 9th Edition. © 2019 Cengage. All Rights Reserved. May not be scanned, copied or duplicated, or posted to a publicly accessible website, in whole or in part. Database Policy Formulation and Enforcement (10 of 11) • Archiving: legal reasons for data retention may apply to many organizations • Governmental laws and regulations • Sarbannes-Oxley Act • Patriot Act • HIPAA • Auditing and financial requirements • Data archive (archive) • Place where record of certain corporate data is kept • Typically stored on mass storage devices • Copies of archives and database backups must be stored off-site Starks/Pratt/Last, Concepts of DB Management, 9th Edition. © 2019 Cengage. All Rights Reserved. May not be scanned, copied or duplicated, or posted to a publicly accessible website, in whole or in part. Database Policy Formulation and Enforcement (11 of 11) Starks/Pratt/Last, Concepts of DB Management, 9th Edition. © 2019 Cengage. All Rights Reserved. May not be scanned, copied or duplicated, or posted to a publicly accessible website, in whole or in part. Other Database Administration Functions • DBA responsibilities • DBMS evaluation and selection • DBMS maintenance • Data dictionary management • Training Starks/Pratt/Last, Concepts of DB Management, 9th Edition. © 2019 Cengage. All Rights Reserved. May not be scanned, copied or duplicated, or posted to a publicly accessible website, in whole or in part. DBMS Evaluation and Selection • Categories • Data definition and restructuring • Nonprocedural and procedural languages • Data dictionary • Concurrent update • Backup and recovery • Security and integrity • Replication and distributed databases • Limitations • Documentation and training • Vendor support • Performance, portability, and cost • Future plans and other considerations Starks/Pratt/Last, Concepts of DB Management, 9th Edition. © 2019 Cengage. All Rights Reserved. May not be scanned, copied or duplicated, or posted to a publicly accessible website, in whole or in part. DBMS Maintenance • DBA has primary responsibility • Installation of DBMS • Configuration changes • Upgrades for new releases • Problem resolution • Special one-time processing needs Starks/Pratt/Last, Concepts of DB Management, 9th Edition. © 2019 Cengage. All Rights Reserved. May not be scanned, copied or duplicated, or posted to a publicly accessible website, in whole or in part. Data Dictionary Management • Data dictionary is like database catalog • Wider range of information • DBA manages the data dictionary • Establishes naming conventions for tables, fields, indexes, etc.

• Creates data definitions for tables • Creates data integrity rules and user views • Updates data dictionary • Creates and distributes reports from data dictionary Starks/Pratt/Last, Concepts of DB Management, 9th Edition. © 2019 Cengage. All Rights Reserved. May not be scanned, copied or duplicated, or posted to a publicly accessible website, in whole or in part. Training • DBA provides training in the use of the DBMS and in how to access the database • Training of technical staff responsible for developing and maintaining database applications • If training is provided by vendor of DBMS, DBA handles scheduling of training Starks/Pratt/Last, Concepts of DB Management, 9th Edition. © 2019 Cengage. All Rights Reserved. May not be scanned, copied or duplicated, or posted to a publicly accessible website, in whole or in part. Technical Functions • DBA is also responsible for:

• Database design • Testing • Performance tuning Starks/Pratt/Last, Concepts of DB Management, 9th Edition. © 2019 Cengage. All Rights Reserved. May not be scanned, copied or duplicated, or posted to a publicly accessible website, in whole or in part. Database Design • DBA establishes sound methodology for database design • Does physical-level design • Creates documentation standards • Reviews changes to requirements and manages modifications to database Starks/Pratt/Last, Concepts of DB Management, 9th Edition. © 2019 Cengage. All Rights Reserved. May not be scanned, copied or duplicated, or posted to a publicly accessible website, in whole or in part. Testing (1 of 2) • Production system or live system: hardware, software, and database for users • DBA grants access to production system only to authorized users, except for:

• Troubleshooting a problem • Addition of new or modified programs • Test system or sandbox • Used by programmers to develop new programs and modify existing programs Starks/Pratt/Last, Concepts of DB Management, 9th Edition. © 2019 Cengage. All Rights Reserved. May not be scanned, copied or duplicated, or posted to a publicly accessible website, in whole or in part. Testing (2 of 2) Starks/Pratt/Last, Concepts of DB Management, 9th Edition. © 2019 Cengage. All Rights Reserved. May not be scanned, copied or duplicated, or posted to a publicly accessible website, in whole or in part. Performance Tuning (1 of 4) • Database performance deals with the ability of the production system to serve users in a timely and responsive manner • DBA attempts to get best performance within funding constraints • Creating and deleting indexes • Splitting tables • Changing table design • Denormalizing converts a table in third normal form to a table not in third normal form; improved performance Starks/Pratt/Last, Concepts of DB Management, 9th Edition. © 2019 Cengage. All Rights Reserved. May not be scanned, copied or duplicated, or posted to a publicly accessible website, in whole or in part. Performance Tuning (2 of 4) Starks/Pratt/Last, Concepts of DB Management, 9th Edition. © 2019 Cengage. All Rights Reserved. May not be scanned, copied or duplicated, or posted to a publicly accessible website, in whole or in part. Performance Tuning (3 of 4) Starks/Pratt/Last, Concepts of DB Management, 9th Edition. © 2019 Cengage. All Rights Reserved. May not be scanned, copied or duplicated, or posted to a publicly accessible website, in whole or in part. Performance Tuning (4 of 4) Starks/Pratt/Last, Concepts of DB Management, 9th Edition. © 2019 Cengage. All Rights Reserved. May not be scanned, copied or duplicated, or posted to a publicly accessible website, in whole or in part. Summary (1 of 2) • Database administrator (DBA) is responsible for supervising the database and use of the DBMS • Formulates and enforces policies about which users can access database, portions they may access, and the manner in which they can access it • Formulates and enforces policies about security • Creates and implements backup and recovery procedures as part of a disaster recovery plan • Formulates and enforces policies that govern management of an archive for data • Leads evaluation and selection of new DBMS • Installs and maintains DBMS Starks/Pratt/Last, Concepts of DB Management, 9th Edition. © 2019 Cengage. All Rights Reserved. May not be scanned, copied or duplicated, or posted to a publicly accessible website, in whole or in part. Summary (2 of 2) • Maintains data dictionary, establishes naming conventions for its content, and provides information from it to others • Provides database and DBMS training and coordinates and schedules training by outside vendors • Verifies all information-level database designs, completes all physical-level database designs, and creates documentation standards; also evaluates changes in requirements • Controls production system, which is accessible only to authorized users; other than under exceptional situations, programmers access a separate test system • Tunes database design to improve performance; includes creating and deleting indexes, splitting tables, and denormalizing tables Starks/Pratt/Last, Concepts of DB Management, 9th Edition. © 2019 Cengage. All Rights Reserved. May not be scanned, copied or duplicated, or posted to a publicly accessible website, in whole or in part.