Sams Teach Yourself SQL in 24 Hours

By Ron Plew and Ryan Stephens

• Table of Contents
  • Copyright
  • About the Authors
  • Acknowledgments
  • Tell Us What You Think!
  • Introduction
  • Part I: A SQL Concepts Overview
  • Hour 1. Welcome to the World of SQL
  • Part II: Building Your Database
  • Hour 2. Defining Data Structures
  • Hour 3. Managing Database Objects
  • Hour 4. The Normalization Process
  • Hour 5. Manipulating Data
  • Hour 6. Managing Database Transactions
  • Part III: Getting Effective Results from Queries
  • Hour 7. Introduction to the Database Query
  • Hour 8. Using Operators to Categorize Data
  • Hour 9. Summarizing Data Results from a Query
  • Hour 10. Sorting and Grouping Data
  • Hour 11. Restructuring the Appearance of Data
  • Hour 12. Understanding Dates and Times
  • Part IV: Building Sophisticated Database Queries
  • Hour 13. Joining Tables in Queries
  • Hour 14. Using Subqueries to Define Unknown Data
  • Hour 15. Combining Multiple Queries into One
  • Part V: SQL Performance Tuning
  • Hour 16. Using Indexes to Improve Performance
  • Hour 17. Improving Database Performance
  • Part VI: Using SQL to Manage Users and Security
  • Hour 18. Managing Database Users
  • Hour 19. Managing Database Security
  • Part VII: Summarized Data Structures
  • Hour 20. Creating and Using Views and Synonyms
  • Hour 21. Working with the System Catalog
    • What Is the System Catalog?
    • How Is the System Catalog Created?
    • What Is Contained in the System Catalog?
    • Examples of System Catalog Tables by Implementation
    • Querying the System Catalog
    • Updating System Catalog Objects
    • Summary
    • Q&A
    • Workshop
  • Part VIII: Applying SQL Fundamentals in Today's World
  • Hour 22. Advanced SQL Topics
  • Hour 23. Extending SQL to the Enterprise, the Internet, and the Intranet
  • Hour 24. Extensions to Standard SQL
  • Part IX: Appendixes
  • Appendix A. Common SQL Commands
  • Appendix B. Using MySQL for Exercises
  • Appendix C. Answers to Quizzes and Exercises
  • Appendix D. CREATE TABLE Statements for Book Examples
  • Appendix E. INSERT Statements for Data in Book Examples
  • Appendix F. Glossary
  • Appendix G. Bonus Exercises

What Is Contained in the System Catalog?

The system catalog contains a variety of information accessible to many users and is sometimes used for different specific purposes by each of those users.

The system catalog contains information such as the following:

• User accounts and default settings
• Privileges and other security information
• Performance statistics
• Object sizing
• Object growth
• Table structure and storage
• Index structure and storage
• Information on other database objects, such as views, synonyms, triggers, and stored procedures
• Table constraints and referential integrity information
• User sessions
• Auditing information
• Internal database settings
• Locations of database files

The system catalog is maintained by the database server. For example, when a table is created, the database server inserts the data into the appropriate system catalog table or view. When a table's structure is modified, appropriate objects in the data dictionary are also updated. The following sections describe, by category, the types of data that are contained in the system catalog.

User Data

All information about individual users is stored in the system catalog: the system and object privileges a user has been granted, the objects a user owns, and the objects not owned by the user to which the user has access. The user tables or views are accessible to the individual to query for information. See your implementation documentation on the system catalog objects.

Security Information

The system catalog also stores security information, such as user identifications, encrypted passwords, and various privileges and groups of privileges database users utilize to access the data. Audit tables exist in some implementations for tracking actions that occur within the database, as well as by whom, when, and so on. Database user sessions also can be closely monitored through the use of the system catalog in many implementations.

Database Design Information

The system catalog contains information regarding the actual database. That information includes the database's creation date, name, objects sizing, size and location of data files, referential integrity information, indexes that exist in the database, and specific column information and column attributes for each table in the database.

Performance Statistics

Performance statistics are typically maintained in the system catalog as well. Performance statistics include information concerning the performance of SQL statements, both elapsed time and the execution method of a SQL statement taken by the optimizer. Other information for performance concerns memory allocation and usage, free space in the database, and information that allows table and index fragmentation to be controlled within the database. This performance information can be used to properly tune the database, rearrange SQL queries, and redesign methods of access to data to achieve better overall performance and SQL query response time.

Share This