Transcript presentation source - Courses

Data Administration and
Database Administration
University of California, Berkeley
School of Information Management and
Systems
SIMS 257: Database Management
10/5/1999
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/5/1999
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/5/1999
Database Management -- R. Larson
Database System Life Cycle
Database
Planning
Database
Analysis
Growth &
Change
Operation &
Maintenance
Database
Design
Database
Implementation
10/5/1999
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/5/1999
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/5/1999
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/5/1999
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/5/1999
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/5/1999
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/5/1999
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/5/1999
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/5/1999
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/5/1999
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/5/1999
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/5/1999
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/5/1999
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/5/1999
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/5/1999
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/5/1999
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/5/1999
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/5/1999
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/5/1999
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/5/1999
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 ...
• Request X lock (denied)
• wait...
10/5/1999
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/5/1999
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/5/1999
Database Management -- R. Larson
Views
• A subset of the database presented to some
set of users.
– SQL: CREATE VIEW viewname AS SELECT
field1, field2, field3,…, FROM table1, table2
WHERE <where clause>;
– Note: “queries” in Access function as views.
10/5/1999
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/5/1999
Database Management -- R. Larson
Database Backup and Recovery
•
•
•
•
Backup
Journaling (audit trail)
Checkpoint facility
Recovery manager
10/5/1999
Database Management -- R. Larson