Data Administration and Database Administration University of California
Data Administration and Database Administration University of California, Berkeley School of Information Management and Systems SIMS 257: Database Management 10/25/2001 Database Management -- R. Larson
Mid-Term Assignment #5 • See WWW site: – http: //sims. berkeley. edu/courses/is 257/f 01/index. html • Report on personal database including: – – – 10/25/2001 Database description Data Dictionary Relationships Diagram Sample queries and results Sample forms Sample reports Database Management -- R. Larson
Today • Data Administration • Database Administration 10/25/2001 Database Management -- R. Larson
Terms and Concepts • Data Administration – Responsibility for the overall management of data resources within an organization • Database Administration – Responsibility for physical database design and technical issues in database management • Data Steward – Responsibility for some subset of the organization’s data, and all of the interactions (applications, user access, etc. ) for that data 10/25/2001 Database Management -- R. Larson
Terms and Concepts • DA – Data adminstrator - person responsible for the Data Administration function in an organization – Sometimes may be the CIO -- Chief Information Officer • DBA – Database Administrator - person responsible for the Database Administration Function 10/25/2001 Database Management -- R. Larson
Database System Life Cycle Database Planning Database Analysis Growth & Change Operation & Maintenance Database Design Database Implementation 10/25/2001 Database Management -- R. Larson Note: this is a different version of this life cycle than discussed previously
Database Planning • Development of a strategic plan for database development that supports the overall organization’s business plan. • DA supports top management in development of this plan. • The result of this stage is an enterprise data model. 10/25/2001 Database Management -- R. Larson
Database Planning: DA & DBA functions • • • Develop corporate database strategy (DA) Develop enterprise model (DA) Develop cost/benefit models (DA) Design database environment (DA) Develop data administration plan (DA) 10/25/2001 Database Management -- R. Larson
Database Analysis • This is the process (discussed previously) of identifying data entities currently used by the organization, precisely defining those entities and their relationships, and documenting the results in a form that can support the follow-on design phase. • Must also identify new data elements or changes to data elements that will be required in the future. • The result of this phase is the Conceptual Data Model -- usually represented as an ER diagram. 10/25/2001 Database Management -- R. Larson
Database Analysis: DA & DBA functions • • Define and model data requirements (DA) Define and model business rules (DA) Define operational requirements (DA) Maintain corporate Data Dictionary (DA) 10/25/2001 Database Management -- R. Larson
Database Design • Purpose of the design phase is the development of the logical database design that will serve the needs of the organization and the physical design implementing the logical design. • In relational systems the outcome is normalized relations, and the data definition for a particular database systems (including indexes, etc. ) 10/25/2001 Database Management -- R. Larson
Design 2: Physical Creation • Development of the Physical Model of the Database – data formats and types – determination of indexes, etc. • Load a prototype database and test • Determine and implement security, privacy and access controls • Determine and implement integrity constraints 10/25/2001 Database Management -- R. Larson
Database Design: DA &DBA functions • Perform logical database design (DA) • Design external models (subschemas) (DBA) • Design internal model (Physical design) (DBA) • Design integrity controls (DBA) 10/25/2001 Database Management -- R. Larson
Database Implementation • Database design gives you an empty database • Load data into the database structure • Convert existing data sets and applications to use the new database – May need programs, conversion utilities to convert old data to new formats. • Outcome is the actual database with its data 10/25/2001 Database Management -- R. Larson
Database Implementation DA & DBA functions • • • Specify database access policies (DA & DBA) Establish Security controls (DBA) Supervise Database loading (DBA) Specify test procedures (DBA) Develop application programming standards (DBA) • Establish procedures for backup and recovery (DBA) • Conduct User training (DA & DBA) 10/25/2001 Database Management -- R. Larson
Operation and Maintenance 1: Operations • Users are responsible for updating the database, DA and DBA are responsible for developing procedures that ensure the integrity and security of the database during the update process. • Specific responsibility for data collection, editing and verification must be assigned • Quality assurance must be practiced to protect and audit the database quality. 10/25/2001 Database Management -- R. Larson
Operation and Maintenance 2: Maintenance • The ongoing process of updating the database to keep it current – – adding new records deleting obsolete records changing data values in particular records modifying relation structures (e. g. adding new fields) • Privacy, security, access control must be in place. • Recovery and Backup procedures must be established and used 10/25/2001 Database Management -- R. Larson
Operation and Maintenance: DA & DBA functions • • Monitor database performance (DBA) Tune and reorganize databases (DBA) Enforce standards and procedures (DBA) Support users (DA & DBA) 10/25/2001 Database Management -- R. Larson
Growth & Change • Change is a way of life – Applications, data requirements, reports, etc. will all change as new needs and requirements are found – The Database and applications and will need to be modified to meet the needs of changes to the organization and the environment. – Database performance should be monitored to maintain a high level of system performance. 10/25/2001 Database Management -- R. Larson
Growth & Change: DA & DBA functions • Implement change control procedures (DA & DBA) • Plan for growth and change (DA & DBA) • Evaluate new technology (DA & DBA) 10/25/2001 Database Management -- R. Larson
Issues/functions in Database Administration • Planning and Design (we have already looked at theses processes in detail) • Data Integrity • Backup and Recovery • Security Management 10/25/2001 Database Management -- R. Larson
Data Integrity • Intrarecord integrity (enforcing constraints on contents of fields, etc. ) • Referential Integrity (enforcing the validity of references between records in the database) • Concurrency control (ensuring the validity of database updates in a shared multiuser environment). 10/25/2001 Database Management -- R. Larson
No Concurrency Control: Lost updates John • Read account balance (balance = $1000) • Withdraw $200 (balance = $800) • Write account balance (balance = $800) 10/25/2001 Marsha • Read account balance (balance = $1000) • Withdraw $300 (balance = $700) • Write account balance (balance = $700) Database Management -- R. Larson ERROR!
Concurrency Control: Locking • Locking levels – – – Database Table Block or page Record Field • Types – Shared (S locks) – Exclusive (X locks) 10/25/2001 Database Management -- R. Larson
Concurrency Control: Updates with X locking John • Lock account balance • Read account balance (balance = $1000) • Withdraw $200 (balance = $800) • Write account balance (balance = $800) • Unlock account balance 10/25/2001 Marsha • Read account balance (DENIED) • Lock account balance • Read account balance (balance = $800) • etc. . . Database Management -- R. Larson
Concurrency Control: Deadlocks John • Place S lock • Read account balance (balance = $1000) Marsha • Place S lock • Read account balance (balance = $1000) • Request X lock (denied) • wait. . . 10/25/2001 Database Management -- R. Larson Deadlock!
Concurrency Control • Avoiding deadlocks by maintaining tables of potential deadlocks and “backing out” one side of a conflicting transaction. 10/25/2001 Database Management -- R. Larson
Database Security • Views or restricted subschemas • Authorization rules to identify users and the actions they can perform • User-defined procedures (and rule systems) to define additional constraints or limitations in using the database • Encryption to encode sensitive data • Authentication schemes to positively identify a person attempting to gain access to the database 10/25/2001 Database Management -- R. Larson
Views • A subset of the database presented to some set of users. – SQL: CREATE VIEW viewname AS SELECT field 1, field 2, field 3, …, FROM table 1, table 2 WHERE <where clause>; – Note: “queries” in Access function as views. 10/25/2001 Database Management -- R. Larson
Authorization Rules • Most current DBMS permit the DBA to define “access permissions” on a table by table basis (at least) using the GRANT and REVOKE SQL commands. • Some systems permit finer grained authorization (most use GRANT and REVOKE on variant views. 10/25/2001 Database Management -- R. Larson
Database Backup and Recovery • • Backup Journaling (audit trail) Checkpoint facility Recovery manager 10/25/2001 Database Management -- R. Larson
Disaster Recovery Planning Risk Analysis Recovery Strategies Plan Maintenance Testing and Training Budget & Implement Procedures Development 10/25/2001 Database Management -- R. Larson From Toigo “Disaster Recovery Planning”
Terms and Concepts • Data Administration – Responsibility for the overall management of data resources within an organization • Database Administration – Responsibility for physical database design and technical issues in database management • Data Steward – Responsibility for some subset of the organization’s data, and all of the interactions (applications, user access, etc. ) for that data 10/25/2001 Database Management -- R. Larson
Issues/functions in Database Administration • Planning and Design (we have already looked at theses processes in detail) • Data Integrity • Backup and Recovery • Security Management 10/25/2001 Database Management -- R. Larson
Today • Additional issues – Handling special data considerations – Database/Data Dictionary – Database Building in SQL (maybe) 10/25/2001 Database Management -- R. Larson
- Slides: 35
