![Page 1: Microsoft PowerPoint - DatabaseAdministration.ppt](https://reader033.vdocuments.us/reader033/viewer/2022060116/5580f53dd8b42a9d198b479d/html5/thumbnails/1.jpg)
Database Administration
John A. Springer, PhD
Assistant Professor
Department of Computer and Information Technology
College of Technology
![Page 2: Microsoft PowerPoint - DatabaseAdministration.ppt](https://reader033.vdocuments.us/reader033/viewer/2022060116/5580f53dd8b42a9d198b479d/html5/thumbnails/2.jpg)
Motivation• Product Lifecycle Management (PLM) initiatives
frequently make extensive use of Product Data Management (PDM) systems to manage product informationinformation
• A PDM system typically uses a relational database to store its information
• Software known as a Relational Database Management System (RDBMS) manages access to the relational database
• This module discusses the tasks and procedures typically performed by a database administrator (DBA)
![Page 3: Microsoft PowerPoint - DatabaseAdministration.ppt](https://reader033.vdocuments.us/reader033/viewer/2022060116/5580f53dd8b42a9d198b479d/html5/thumbnails/3.jpg)
Traditional Life Cycle
![Page 4: Microsoft PowerPoint - DatabaseAdministration.ppt](https://reader033.vdocuments.us/reader033/viewer/2022060116/5580f53dd8b42a9d198b479d/html5/thumbnails/4.jpg)
Database Administration
in Traditional Life Cycle
![Page 5: Microsoft PowerPoint - DatabaseAdministration.ppt](https://reader033.vdocuments.us/reader033/viewer/2022060116/5580f53dd8b42a9d198b479d/html5/thumbnails/5.jpg)
Database Development
Life Cycle
![Page 6: Microsoft PowerPoint - DatabaseAdministration.ppt](https://reader033.vdocuments.us/reader033/viewer/2022060116/5580f53dd8b42a9d198b479d/html5/thumbnails/6.jpg)
Database Development
Life Cycle
![Page 7: Microsoft PowerPoint - DatabaseAdministration.ppt](https://reader033.vdocuments.us/reader033/viewer/2022060116/5580f53dd8b42a9d198b479d/html5/thumbnails/7.jpg)
Types of Database Users
• The types of users and their roles and responsibilities at a site can vary.
• Types of users:• Types of users:– Database Administrators
– Security Officers
– Network Administrators
– Application Developers
– Application Administrators
– Database Users
![Page 8: Microsoft PowerPoint - DatabaseAdministration.ppt](https://reader033.vdocuments.us/reader033/viewer/2022060116/5580f53dd8b42a9d198b479d/html5/thumbnails/8.jpg)
Types of DBA Tasks
• Installing and upgrading DBMS software
• Planning, creating, and maintaining storage allocationsstorage allocations
• Creating and maintaining database objects such as tables
• Creating and maintaining users
![Page 9: Microsoft PowerPoint - DatabaseAdministration.ppt](https://reader033.vdocuments.us/reader033/viewer/2022060116/5580f53dd8b42a9d198b479d/html5/thumbnails/9.jpg)
Additional Types of DBA Tasks
• Maintaining database security
• Complying with license agreements
• Optimizing database performance• Optimizing database performance
• Backup and recovery of database
![Page 10: Microsoft PowerPoint - DatabaseAdministration.ppt](https://reader033.vdocuments.us/reader033/viewer/2022060116/5580f53dd8b42a9d198b479d/html5/thumbnails/10.jpg)
Installing and upgrading DBMS
• DBA must have intimate knowledge of DBMS software
• DBA must also have thorough knowledge • DBA must also have thorough knowledge of operating system on which the DBMS resides
• Migration procedures are necessary to ensure minimal downtime
![Page 11: Microsoft PowerPoint - DatabaseAdministration.ppt](https://reader033.vdocuments.us/reader033/viewer/2022060116/5580f53dd8b42a9d198b479d/html5/thumbnails/11.jpg)
Role of Operating System
• DBMS is installed on an operating system (OS)
• DBMS “lives” as a process or set of • DBMS “lives” as a process or set of related processes running on the operating system
– DBMS likely to be multithreaded
• Thus DBMS must use OS efficiently and effectively
![Page 12: Microsoft PowerPoint - DatabaseAdministration.ppt](https://reader033.vdocuments.us/reader033/viewer/2022060116/5580f53dd8b42a9d198b479d/html5/thumbnails/12.jpg)
Storage Requirements
• DBMS uses storage resources extensively
• Actual database files reside on disk
• The actual disk configuration may be one • The actual disk configuration may be one of a number of arrangements
• Typically concerned about the number of disk spindles available for use
• Storage planning includes projecting number of rows in tables and considering primary key sizes and indexing needs
![Page 13: Microsoft PowerPoint - DatabaseAdministration.ppt](https://reader033.vdocuments.us/reader033/viewer/2022060116/5580f53dd8b42a9d198b479d/html5/thumbnails/13.jpg)
User Management
• Must consider access and security needs when
devising user management approach
• Must also consider the manner in which • Must also consider the manner in which
authentication will occur
– DBMS vendors provide various methods for handling
authentication
• Role-based Access Controls use roles to control
users’ access rights
– Roles are given privileges and then users are
assigned roles
![Page 14: Microsoft PowerPoint - DatabaseAdministration.ppt](https://reader033.vdocuments.us/reader033/viewer/2022060116/5580f53dd8b42a9d198b479d/html5/thumbnails/14.jpg)
Optimization
• Critical task of DBA is to ensure that database access is occurring in an optimal mannermanner
• May involve optimizing individual queries
• May also involve adding indexes to improve performance
• Careful consideration of application domains is also necessary
![Page 15: Microsoft PowerPoint - DatabaseAdministration.ppt](https://reader033.vdocuments.us/reader033/viewer/2022060116/5580f53dd8b42a9d198b479d/html5/thumbnails/15.jpg)
Backup and Recovery• Critical task of DBA is to ensure durability of
database
• Thus necessary to have frequent backups of database components that are crucial to database components that are crucial to restoring database
• Database Management Systems often record all database changes in log file– Do not consider a transaction complete until the
changes have been recorded in log file
– Consequently, such log files are essential to recovery process
![Page 16: Microsoft PowerPoint - DatabaseAdministration.ppt](https://reader033.vdocuments.us/reader033/viewer/2022060116/5580f53dd8b42a9d198b479d/html5/thumbnails/16.jpg)
References
• Elmasri, R., & Navathe, S. B. (2006).
Fundamentals of Database Systems. Boston:
Addison Wesley.Addison Wesley.
• Garcia-Molina, H., Ullman, J. D., & Widom, J.
(2001). Database Systems: The Complete Book.
Upper Saddle River, NJ: Pearson/Prentice-Hall.
• Ramakrishnan, R., & Gehrke, J. (2002).
Database Management Systems. New York:
McGraw-Hill.
![Page 17: Microsoft PowerPoint - DatabaseAdministration.ppt](https://reader033.vdocuments.us/reader033/viewer/2022060116/5580f53dd8b42a9d198b479d/html5/thumbnails/17.jpg)
Acknowledgments
The author wishes to acknowledge the support from the Society for Manufacturing Engineers - Education Foundation, SME-EF Engineers - Education Foundation, SME-EF Grant #5004 for “Curriculum Modules in Product Lifecycle Management.”