Database Management System - Chapter 14: Data and Database Administration

Chapter 14Data and Database AdministrationOutline Organizational contextTools of database administrationProcesses for database specialistsOverview of processing environments Database Support for Decision Making Decision Making Examples Information Life Cycle Knowledge ManagementDatabase SpecialistsData administratorMiddle or upper managementBroad view of information resourcesDatabase administrator (DBA)Support roleEmphasis on individual databases and DBMSsResponsibilities of SpecialistsData administratorDevelops enterprise data modelEstablishes inter database standardsNegotiates contractual termsDatabase administratorPerforms database development tasksConsults on application developmentEvaluates DBMS capabilities and featuresDatabase Administration ToolsSecurityIntegrityManagement of stored procedures and triggersData dictionary access Database Access ControlDiscretionary Access ControlAssign access rights or privileges to usersSpecify ability to read, write, and delete specified parts of a databaseUse views for fine level of controlUse groups to reduce the number of authorization rulesSQL Statements for Security ICREATE ROLE ISFacultyCREATE ROLE ISAdministrator WITH ADMIN CURRENT_ROLECREATE ROLE ISAdvisor SQL Statements for Security IIGRANT SELECT ON ISStudentGPA TO ISFaculty, ISAdvisor, ISAdministratorGRANT UPDATE ON ISStudentGPA.StdGPA TO ISAdministratorREVOKE SELECT ON ISStudentGPA FROM ISFaculty RESTRICTGRANT ISAdministrator TO Smith WITH GRANT OPTION; Common SQL PrivilegesOracle Security StatementsCREATE USER statementPredefined rolesCONNECTRESOURCEDBASystem versus object privilegesAccess Security Tools Mandatory Access ControlLess flexible security approach for highly sensitive and static databasesAssign classification levels to database objectsAssign clearance levels to usersAccess granted if a user's clearance level provides access to the classification level of a database objectEncryptionEncoding data to obscure its meaningPlaintextCiphertextEncryption keySQL DomainsLimited ability to define new domainsCREATE DOMAIN statementCREATE DOMAIN StudentClass AS CHAR(2) CHECK(VALUE IN ('FR','SO','JR','SR') ) Distinct typeCREATE DISTINCT TYPE USD AS DECIMAL(10,2); SQL AssertionsSupports complex constraintsConstraint specified through a SELECT statementEnforcement can be inefficientStored procedures and form events are alternativesAssertion ExampleCREATE 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 ) )CHECK ConstraintsUse when a constraint involves columns of the same tablePart of CREATE TABLE statementEasy to writeEfficient to enforceCHECK Constraints ExampleCREATE TABLE Student(CONSTRAINT ValidGPA CHECK ( StdGPA BETWEEN 0 AND 4 ),CONSTRAINT MajorDeclared CHECK ( StdClass IN ('FR','SO') OR StdMajor IS NOT NULL ) )Coding Practice ConcernsDocumentationParameter usageContent of triggers and stored proceduresManagement of DependenciesReferenced tables, views, and proceduresAccess plans for SQL statementsDBMS support incompleteObsolete statisticsRemotely stored proceduresNo automatic recompilation after deletionManaging Trigger ComplexityCoding guidelines to minimize interactionTrigger analysis toolsAdditional testing for interacting triggersMetadataDefine the source, use, value, and meaning of dataStored in a data dictionaryDBMS data dictionary to track objects managed by the DBMSInformation resource dictionary to track objects relating to information systems development Catalog TablesMost DBMSs provide a large collectionDefinition Schema and Information Schema in SQL:2003Modify using data definition and control statementsUse SELECT statement to retrieve from catalog tablesIntegrity of catalog tables is crucial Sample Oracle Catalog Tables Information Resource DictionaryProcesses for Database SpecialistsData planningDBMS selection and evaluationGoals of Data PlanningEvaluate current information systems with respect to the goals and objectives of the organizationDetermine the scope and the timing of developing new information systems and utilizing of new information technologyIdentify opportunities to apply information technology for competitive advantage Planning Models Level of Detail in ModelsDBMS SelectionDetailed processRequires knowledge of organization goals and DBMS featuresSystematic approach is importantHigh switching cost if wrong choice Selection Process PhasesAnalytic Hierarchy ProcessMulti-criteria decision making toolSupports systematic assignment of weights and scores to candidate DBMSsUses pairwise comparisons Rating Values for ComparisonsRanking Value of Aij Meaning 1Requirements i and j are equally important. 3Requirement i is slightly more important than requirement j. 5Requirement i is significantly more important than requirement j. 7Requirement i is very significantly more important than requirement j. 9Requirement i is absolutely more important than requirement j. Analytic Hierarchy Process DetailsAssign importance weights to pairwise combinations of requirement groups and requirement categoriesCombine and normalize importance weightsScore candidate DBMSs for each requirementCombine and normalize scoresCombine importance weights and DBMS scoresFinal Selection FactorsBenchmarks and trial usageContractual termsVendor expectationsBenchmarkingWorkload to evaluate the performance of a system or product A good benchmark should be relevant, portable, scalable, and understandable. Standard, domain-specific benchmarks by TPCTCP BenchmarksReasonable estimates about a DBMS in a specific hardware/software environment Total system performance and cost measures Audits to ensure unbiased resultsCurrent TCP BenchmarksTPC-C: order entry benchmarkTPC-App: business to business transactionsTPC-H: decision support ad hoc queriesTPC-W: Ecommerce benchmarkManaging Database EnvironmentsTransaction processingData warehouse processingDistributed processingObject data managementResponsibilities of Database SpecialistsApplication developmentDatabase infrastructure and architecturesPerformance monitoringEnterprise data model developmentContingency planningSummaryTwo roles for managing information resourcesTools for security, integrity, rule processing, stored procedures, and data dictionary manipulationProcesses for data planning and DBMS selectionContext for studying other Part 7 chapters