1 / 46

Chapter 14

Chapter 14. Data and Database Administration. Outline. Organizational context Tools of database administration Processes for database specialists Overview of processing environments. Database Support for Decision Making. Decision Making Examples. Information Life Cycle.

rachel-puckett
Download Presentation

Chapter 14

An Image/Link below is provided (as is) to download presentation Download Policy: Content on the Website is provided to you AS IS for your information and personal use and may not be sold / licensed / shared on other websites without getting consent from its author. Content is provided to you AS IS for your information and personal use only. Download presentation by click this link. While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server. During download, if you can't get a presentation, the file might be deleted by the publisher.

E N D

Presentation Transcript

  1. Chapter 14 Data and Database Administration

  2. Outline • Organizational context • Tools of database administration • Processes for database specialists • Overview of processing environments

  3. Database Support for Decision Making

  4. Decision Making Examples

  5. Information Life Cycle

  6. Knowledge Management

  7. Database Specialists • Data administrator • Middle or upper management • Broad view of information resources • Database administrator (DBA) • Support role • Emphasis on individual databases and DBMSs

  8. Responsibilities of Specialists • Data administrator • Develops enterprise data model • Establishes inter database standards • Negotiates contractual terms • Database administrator • Performs database development tasks • Consults on application development • Evaluates DBMS capabilities and features

  9. Database Administration Tools • Security • Integrity • Management of stored procedures and triggers • Data dictionary access

  10. Database Access Control

  11. Discretionary Access Control • Assign access rights or privileges to users • Specify ability to read, write, and delete specified parts of a database • Use views for fine level of control • Use groups to reduce the number of authorization rules

  12. SQL Statements for Security I CREATE ROLE ISFaculty CREATE ROLE ISAdministrator WITH ADMIN CURRENT_ROLE CREATE ROLE ISAdvisor

  13. SQL Statements for Security II GRANT SELECT ON ISStudentGPA TO ISFaculty, ISAdvisor, ISAdministrator GRANT UPDATE ON ISStudentGPA.StdGPA TO ISAdministrator REVOKE SELECT ON ISStudentGPA FROM ISFaculty RESTRICT GRANT ISAdministrator TO Smith WITH GRANT OPTION;

  14. Common SQL Privileges

  15. Oracle Security Statements • CREATE USER statement • Predefined roles • CONNECT • RESOURCE • DBA • System versus object privileges

  16. Access Security Tools

  17. Mandatory Access Control • Less flexible security approach for highly sensitive and static databases • Assign classification levels to database objects • Assign clearance levels to users • Access granted if a user's clearance level provides access to the classification level of a database object

  18. Encryption • Encoding data to obscure its meaning • Plaintext • Ciphertext • Encryption key

  19. SQL Domains • Limited ability to define new domains • CREATE DOMAIN statement CREATE DOMAIN StudentClass AS CHAR(2) CHECK(VALUE IN ('FR','SO','JR','SR') ) • Distinct type CREATE DISTINCT TYPE USD AS DECIMAL(10,2);

  20. SQL Assertions • Supports complex constraints • Constraint specified through a SELECT statement • Enforcement can be inefficient • Stored procedures and form events are alternatives

  21. Assertion Example CREATE ASSERTION FullTimeEnrollment CHECK (NOT EXISTS ( SELECT Enrollment.RegNo FROM Registration, Offering, Enrollment, Course WHERE Offering.OfferNo =Enrollment.OfferNo AND Offering.CourseNo = Course.CourseNo AND Offering.RegNo = Registration.RegNo AND RegStatus = 'F' GROUP BY Enrollment.RegNo HAVING SUM(CrsUnits) >= 9 ) )

  22. CHECK Constraints • Use when a constraint involves columns of the same table • Part of CREATE TABLE statement • Easy to write • Efficient to enforce

  23. CHECK Constraints Example CREATE TABLE Student (… CONSTRAINT ValidGPA CHECK ( StdGPA BETWEEN 0 AND 4 ), CONSTRAINT MajorDeclared CHECK ( StdClass IN ('FR','SO') OR StdMajor IS NOT NULL ) )

  24. Coding Practice Concerns • Documentation • Parameter usage • Content of triggers and stored procedures

  25. Management of Dependencies • Referenced tables, views, and procedures • Access plans for SQL statements • DBMS support incomplete • Obsolete statistics • Remotely stored procedures • No automatic recompilation after deletion

  26. Managing Trigger Complexity • Coding guidelines to minimize interaction • Trigger analysis tools • Additional testing for interacting triggers

  27. Metadata • Define the source, use, value, and meaning of data • Stored in a data dictionary • DBMS data dictionary to track objects managed by the DBMS • Information resource dictionary to track objects relating to information systems development

  28. Catalog Tables • Most DBMSs provide a large collection • Definition Schema and Information Schema in SQL:2003 • Modify using data definition and control statements • Use SELECT statement to retrieve from catalog tables • Integrity of catalog tables is crucial

  29. Sample Oracle Catalog Tables

  30. Information Resource Dictionary

  31. Processes for Database Specialists • Data planning • DBMS selection and evaluation

  32. Goals of Data Planning • Evaluate current information systems with respect to the goals and objectives of the organization • Determine the scope and the timing of developing new information systems and utilizing of new information technology • Identify opportunities to apply information technology for competitive advantage

  33. Planning Models

  34. Level of Detail in Models

  35. DBMS Selection • Detailed process • Requires knowledge of organization goals and DBMS features • Systematic approach is important • High switching cost if wrong choice

  36. Selection Process Phases

  37. Analytic Hierarchy Process • Multi-criteria decision making tool • Supports systematic assignment of weights and scores to candidate DBMSs • Uses pairwise comparisons

  38. Rating Values for Comparisons

  39. Analytic Hierarchy Process Details • Assign importance weights to pairwise combinations of requirement groups and requirement categories • Combine and normalize importance weights • Score candidate DBMSs for each requirement • Combine and normalize scores • Combine importance weights and DBMS scores

  40. Final Selection Factors • Benchmarks and trial usage • Contractual terms • Vendor expectations

  41. Benchmarking • Workload to evaluate the performance of a system or product • A good benchmark should be relevant, portable, scalable, and understandable. • Standard, domain-specific benchmarks by TPC

  42. TCP Benchmarks • Reasonable estimates about a DBMS in a specific hardware/software environment • Total system performance and cost measures • Audits to ensure unbiased results

  43. Current TCP Benchmarks • TPC-C: order entry benchmark • TPC-App: business to business transactions • TPC-H: decision support ad hoc queries • TPC-W: Ecommerce benchmark

  44. Managing Database Environments • Transaction processing • Data warehouse processing • Distributed processing • Object data management

  45. Responsibilities of Database Specialists • Application development • Database infrastructure and architectures • Performance monitoring • Enterprise data model development • Contingency planning

  46. Summary • Two roles for managing information resources • Tools for security, integrity, rule processing, stored procedures, and data dictionary manipulation • Processes for data planning and DBMS selection • Context for studying other Part 7 chapters

More Related