11

Data and Database Data and Database AdministrationAdministration

ByByLec. Adeel ShahzadLec. Adeel Shahzad

FromFromBook-BBook-B

Chapter 13 22

ObjectivesObjectives• Definition of termsDefinition of terms• List functions and roles of data/database administrationList functions and roles of data/database administration• Describe role of data dictionaries and information Describe role of data dictionaries and information

repositoriesrepositories• Compare optimistic and pessimistic concurrency controlCompare optimistic and pessimistic concurrency control• Describe problems and techniques for data securityDescribe problems and techniques for data security• Describe problems and techniques for data recoveryDescribe problems and techniques for data recovery• Describe database tuning issues and list areas where changes Describe database tuning issues and list areas where changes

can be done to tune the databasecan be done to tune the database• Describe importance and measures of data availabilityDescribe importance and measures of data availability

Chapter 13 33

Traditional Administration DefinitionsTraditional Administration Definitions

• Data AdministrationData Administration:: A high-level function that is A high-level function that is responsible for the overall management of data responsible for the overall management of data resources in an organization, including maintaining resources in an organization, including maintaining corporate-wide definitions and standardscorporate-wide definitions and standards

• Database AdministrationDatabase Administration:: A technical function that A technical function that is responsible for physical database design and for is responsible for physical database design and for dealing with technical issues such as security dealing with technical issues such as security enforcement, database performance, and backup enforcement, database performance, and backup and recoveryand recovery

Chapter 13 44

Traditional Data Administration Traditional Data Administration FunctionsFunctions

• Data policies, procedures, standardsData policies, procedures, standards• PlanningPlanning• Data conflict (ownership) resolutionData conflict (ownership) resolution• Managing the information repositoryManaging the information repository• Internal marketing of DA conceptsInternal marketing of DA concepts

Chapter 13 55

Traditional Database Traditional Database Administration FunctionsAdministration Functions

• Selection of DBMS and software toolsSelection of DBMS and software tools• Installing/upgrading DBMSInstalling/upgrading DBMS• Tuning database performanceTuning database performance• Improving query processing performanceImproving query processing performance• Managing data security, privacy, and integrityManaging data security, privacy, and integrity• Data backup and recoveryData backup and recovery

Chapter 13 66

Evolving Approaches to Data Evolving Approaches to Data AdministrationAdministration

• Blend data and database administration into one roleBlend data and database administration into one role• Fast-track development–monitoring development process Fast-track development–monitoring development process

(planning, analysis, design, implementation, maintenance)(planning, analysis, design, implementation, maintenance)• Procedural DBAs–managing quality of triggers and stored Procedural DBAs–managing quality of triggers and stored

proceduresprocedures• eDBA–managing Internet-enabled database applicationseDBA–managing Internet-enabled database applications• PDA DBA–data synchronization and personal database PDA DBA–data synchronization and personal database

managementmanagement• Data warehouse administrationData warehouse administration

Chapter 13 77

Open Source DBMSsOpen Source DBMSs• An alternative to proprietary packages such as Oracle, An alternative to proprietary packages such as Oracle,

Microsoft SQL Server, or Microsoft AccessMicrosoft SQL Server, or Microsoft Access• mySQL is an example of an open-source DBMSmySQL is an example of an open-source DBMS• Less expensive than proprietary packagesLess expensive than proprietary packages• Source code available, for modificationSource code available, for modification• Absence of complete documentationAbsence of complete documentation• Ambiguous licensing concernsAmbiguous licensing concerns• Not as feature-rich as proprietary DBMSsNot as feature-rich as proprietary DBMSs• Vendors may not have certification programsVendors may not have certification programs

Chapter 13 88

Figure 13-2 Data modeling responsibilities

Chapter 13 99

Database SecurityDatabase Security

• Database Security:Database Security: Protection of the Protection of the data against accidental or intentional data against accidental or intentional loss, destruction, or misuseloss, destruction, or misuse

• Increased difficulty due to Internet Increased difficulty due to Internet access and client/server technologiesaccess and client/server technologies

Chapter 13 1010

Figure 13-3 Possible locations of data security threats

Chapter 13 1111

Threats to Data SecurityThreats to Data Security• Accidental losses attributable to:Accidental losses attributable to:

– Human errorHuman error– Software failureSoftware failure– Hardware failureHardware failure

• Theft and fraudTheft and fraud• Improper data access:Improper data access:

– Loss of privacy (personal data)Loss of privacy (personal data)– Loss of confidentiality (corporate data)Loss of confidentiality (corporate data)

• Loss of data integrityLoss of data integrity• Loss of availability (through, e.g. sabotage)Loss of availability (through, e.g. sabotage)

Chapter 13 1212

Figure 13-4 Establishing Internet Security

Chapter 13 1313

Web SecurityWeb Security• Static HTML files are easy to secureStatic HTML files are easy to secure– Standard database access controlsStandard database access controls– Place Web files in protected directories on serverPlace Web files in protected directories on server

• Dynamic pages are harderDynamic pages are harder– Control of CGI scriptsControl of CGI scripts– User authenticationUser authentication– Session securitySession security– SSL for encryptionSSL for encryption– Restrict number of users and open portsRestrict number of users and open ports– Remove unnecessary programs Remove unnecessary programs

Chapter 13 1414

Database Software Security Database Software Security FeaturesFeatures

• Views or subschemasViews or subschemas• Integrity controls(Integrity controls(Accuracy, Completeness, ConsistencyAccuracy, Completeness, Consistency))• Authorization rulesAuthorization rules• User-defined proceduresUser-defined procedures• EncryptionEncryption• Authentication schemesAuthentication schemes• Backup, journalizing, and checkpointingBackup, journalizing, and checkpointing

Chapter 13 1515

Database RecoveryDatabase Recovery Mechanism for restoring a database quickly Mechanism for restoring a database quickly

and accurately after loss or damageand accurately after loss or damage Recovery facilities:Recovery facilities:

• Backup FacilitiesBackup Facilities• Journalizing FacilitiesJournalizing Facilities• Checkpoint FacilityCheckpoint Facility• Recovery ManagerRecovery Manager

Chapter 13 1616

Back-up FacilitiesBack-up Facilities• Automatic dump facility that produces Automatic dump facility that produces

backup copy of the entire databasebackup copy of the entire database• Periodic backup (e.g. nightly, weekly)Periodic backup (e.g. nightly, weekly)• Cold backup–database is shut down during Cold backup–database is shut down during

backupbackup• Hot backup–selected portion is shut down Hot backup–selected portion is shut down

and backed up at a given timeand backed up at a given time• Backups stored in secure, off-site locationBackups stored in secure, off-site location

Chapter 13 1717

Checkpoint FacilitiesCheckpoint Facilities

• DBMS periodically refuses to accept new DBMS periodically refuses to accept new transactionstransactions

• system is in a system is in a quietquiet state state• Database and transaction logs are Database and transaction logs are

synchronizedsynchronized

This allows recovery manager to resume processing from short period, instead of repeating entire day