DATABASE ADMINSTRATION

Database administration refers to the whole set of activities performed by a database administrator to ensure that a database is always available as needed. Other closely related tasks and roles are database security, database monitoring and troubleshooting, and planning for future growth

1. topic Database Management Tools, The kernel, The data dictionary, DBA, The utilities Under the Guidance of Ms. TEJESHWINI PRESENTED BY NUSRAT M G BI 191002 KARNATAKA STATE-AKKAMAHADEVI WOMEN’S UNIVERSITY VIJAYPUR, DEPARTMENT OF BIOINFORMATICS. MSC III SEM 2020 DATABASE MANAGEMENT SYSTEM PAPER CODE : BI. HCT- 3.1

2. INDEX JUST A GLANCE ORACLE ~ DATABASE MANAGEMENT TOOLS THE KERNEL DATA DICTIONARY DATABASE ADMINISTRATOR THE UTILITIES

3. Data : D ata are nothing but facts , text that have meaning in the user’s environment . Database: An organized collection of logically related data . Database Dictionary: A  Data Dictionary  is a collection of names, definitions, and attributes about  data  elements that are being used or captured in a database, information system, or part of a research project. Metadata: Data that describes the properties or characteristics of other data . Database Administrator: The person who is responsible for physical database design and for dealing with technical issues such as security enforcement, Database performance, Backup and recovery associated with managing a Database . JUST A MINUTE PLEASE

4. Oracel ~ DATABASE MANAGEMENT TOOLS 1. MySQL One of the most useful database management tools is MySQL. MySQL is used by top companies like Google, LinkedIn, YouTube, PayPal and Twitter, just to name a few. 2. SQL Server Management Studio : It combines a powerful set of user-friendly graphical tools and variety of script editors to provide access and management for SQL Server. It includes an all-in-one solution to profile and tune database performance. SSMS can be used by developers and database administrators of all skill levels. 3. Oracle RDBMS The Oracle database is the most widely used object-relational database management software. The latest version of the tool incorporates cloud computing and it supports multiple Windows, Linux and UNIX versions. It is secure, supports large databases, occupies less space and reduces CPU time to process data.

5. 4. DevOps : The most useful database management framework is DevOps . The database is known today for heavyweight processes that hinder performance, such as manual reviews and ticketing. 5. MICROSOFT ACCESS : Microsoft Access is a Database Management System (DBMS) from Microsoft that combines the relational Microsoft Jet Database Engine with a graphical user interface and software development tools. 6. PostgreSQL : PostgreSQL is a powerful, open source object-relational database system. It has more than 15 years of active development phase and a proven architecture that has earned it a strong reputation for reliability, data integrity, and correctness.

6. The kernel data structures are very important as they store data about the current state of the system. For example, if a new process is created in the system, a kernel data structure is created that contains the details about the process . Most of the kernel data structures are only accessible by the kernel and its subsystems. They may contain data as well as pointers to other data structures. THE KERNEL

7. Kernel Components The kernel stores and organizes a lot of information. So it has data about which processes are running in the system, their memory requirements, files in use etc. To handle all this, three important structures are used. These are process table, file table and v node/ i node information.

8. PROCESS TABLE The process table stores information about all the processes running in the system. These include the storage information, execution status, file information etc. When a process forks a child, its entry in the process table is duplicated including the file information and file pointers. So the parent and the child process share a file .

9. FILE TABLE The file table contains entries about all the files in the system. If two or more processes use the same file, then they contain the same file information and the file descriptor number. Each file table entry contains information about the file such as file status (file read or file write), file offset etc. The file offset specifies the position for next read or write into the file. The file table also contains v-node and i-node pointers which point to the virtual node and index node respectively. These nodes contain information on how to read a file .

10. V-Node and I-Node Tables Both the v-node and i-node are references to the storage system of the file and the storage mechanisms. They connect the hardware to the software. The v-node is an abstract concept that defines the method to access file data without worrying about the actual structure of the system. The i-node specifies file access information like file storage device, read/write procedures etc.

11. What is a data dictionary? It is an integral part of a database. I t ho l d s in f or m a t ion a b out t h e database and the data that it stores. A data dictionary is a “virtual database” containing metadata (data about data). META DATA Metadata is defined as data providing information about one or more aspects of the data .

12. DATA DICTIONARY PROVIDES INFORMATION ABOUT DATABASE Table Indexes Columns Constrains Relationship to other variables Precision of data Variable format Packages Data type And more

13. BIG Importance Avoid duplication. Make maintenance straight forward . To locate the error in the system . And more.

14. WHY DATA DICTIONARY? Authorization R ep o rt E a sy Searchable C atalogue Easy to search data in huge database. Provides quick report on the data and hence making the data management easy. Record what data belongs to whom. A central catalogue for metadata. DBA can easily able to track any chaos in the database.

15. Structure of Data Dictionary It can be integrated with the DBMS or stand-alone. It automatically reflect the changes in the database. Relational systems all have some form of integrated data dictionary (e.g. Oracle)

16. Disadvantages of Data Dictionary? Creating a new data dictionary is a very big task. It will take years to create one . T h e c o s t of da t a d i c tio n a ry w i ll be bit high as it includes its i n i t ial b u il d a n d h ardw a re c h arg e s as well a s c o s t of maintenance. Requires management commitment, which is not easy to achieve , pa r t i cul a rly w here the benefits are intangible and long term. It n e e ds c aref u l pl a n n i n g, defi n ing t h e exact requirements designing its contents testing, i m pl e m en t at i on a n d evaluation . .

17. Viewing Information in the Data Dictionary Although you are not allowed to modify the dictionary yourself, you can DESCRIBE and SELECT from Dictionary tables. For example, to see information about all the tables that you have privileges to use : DESCRIBE ALL_TABLES The output from this shows that many columns of data are held about each table. You decide you only want to see the name and owner, so you enter : SELECT table_name, owner FROM ALL_TABLES;

18. DATABASE ADMINISTRATOR DBA is a resource that supervises both the database and the use of the DBMS. DBA is usually a group, but sometimes it refers to the database administrator. Who is DBA ?? A database administrator is a person responsible for the installation configuration upgradation administration monitoring and maintenance of databases.

19. P RIMARY R OLES O F DBA Performance issues Capacity issues Database accessibility Data replication Database design Table Maintenance

20. DBA Tasks Database design Performance monitoring and tuning Database availability Security Backup and recovery Data integrity Release migration

21. Database design DBA must understand the theory and implementation of the relational database management system (RDBMS) he’s using to create the database. Database design - needs understanding of conceptual and logical data modeling techniques. - create and interpret entity-relationship diagrams is essential for designing a relational database. The DBA must ensure that the database design and implementation will enable a useful database for the applications and clients that will use it.

22. Performance Monitoring and Tuning Five factors influence database performance: Workload Throughput Resources Optimization Contention Whenever performance problems are encountered by an application that uses a database, the DBA is usually the first one called to resolve the problem. An effective performance monitoring and tuning strategy requires not just DBMS expertise but knowledge outside the scope of database administration.

23. Availability Ensure that database information is always available to all users in a form that suits their needs. The faster the DBA can perform administrative tasks, the more available the data becomes. The DBA must understand all of these aspects of availability and ensure that each application is receiving the correct level of availability for its needs.

24. Once the database is designed and implemented, programmers and users will need to access and modify the data. However, to prevent security breaches and improper data modification, only authorized programmers and users should have access. It is the responsibility of the DBA to ensure that data is available only to authorized users. Security

25. Backup and Recovery The DBA must be prepared to recover data in the event of a problem. The majority of recoveries today occur as a result of application software error and human error. The DBA must be prepared to recover data to a usable point, no matter what the cause, and to do so as quickly as possible. To be prepared for any type of recovery, the DBA needs to develop a backup strategy to ensure that data is not lost in the event of an error in software, hardware, or a manual process.

26. Data Integrity A database must be designed to store the correct data in the correct way without that data becoming damaged or corrupted. To ensure this process, the DBA implements integrity rules using features of the DBMS. Three aspects of integrity : physical se m an t ic internal. Physical issues can be handled using DBMS features such as domains and data types. An example of semantic integrity is the quality of the data in the database. Redundancy is another semantic issue.

27. The DBMS relies on internal structures and code to maintain links, pointers, and identifiers. In most cases, the DBMS will do a good job of maintaining these structures, but the DBA needs to be aware of their existence and how to cope when the DBMS fails.

28. DBMS Release Migration Installation, configuration and upgrading of Microsoft SQL Server/ My SQL /Oracle server software and related products. The DBA is also responsible for managing the migration from release to release of the DBMS.

29. Controlling User Access Database adm i nistra t or Username and password Privileges Users

30. P RIV I LEGES Database security: System security Data security System privileges : Gaining access to the database Object privileges: Manipulating the content of the database objects Schemas : Collections of objects, such as tables, views, and sequences The database administrator has high-level system privileges for tasks such as: Creating new users Removing users Removing tables Backing up tables

31. V$ D A T A B A SE Oracle has set of views that are frequently accessed by the DBA. V$DATABASE is one among those views. It contains more 30 columns. Sample Query: select * from v$database; This query gives the details about database name, database ID, Open mode(read/write), Protection level(yes/no), Platform name(microsoft windows 32 bit), etc.