Data Dictionary Entries

A completed data dictionary typically includes detailed information about the structure, organization, and metadata of a database. Below are the common entries that appear in a comprehensive data dictionary:

1. Table Information
   • Table Name: The name of the table in the database.
   • Description: A brief explanation of the table's purpose or function.
   • Schema Name: The schema to which the table belongs.
   • Relationships: Links to other tables, such as foreign key relationships.
2. Column (Field) Information
   • Column Name: The name of each column in the table.
   • Data Type: The type of data the column holds (e.g., INTEGER, VARCHAR, DATE).
   • Length/Precision: Details about size limits for the data type.
   • Constraints: Any constraints applied (e.g., NOT NULL, UNIQUE, PRIMARY KEY).
   • Default Value: The default value assigned to the column, if any.
   • Nullable: Whether the column can accept null values.
   • Description: A brief description of the column's role in the table.
3. Index Information
   • Index Name: The name of the index.
   • Type: The type of index (e.g., UNIQUE, CLUSTERED, NONCLUSTERED).
   • Indexed Columns: Columns included in the index.
   • Purpose: A description of the index's role (e.g., speeding up queries).
4. Keys
   • Primary Key: The column(s) that uniquely identify a row in the table.
   • Foreign Key: The column(s) linking to another table's primary key.
   • Unique Keys: Columns with unique constraints to ensure no duplicate values.
5. Relationships
   • Table Relationships: Details about how tables relate to each other.
   • Cardinality: Information about one-to-one, one-to-many, or many-to-many relationships.
6. Stored Procedures and Functions
   • Procedure/Function Name: Name of the procedure or function.
   • Parameters: Input/output parameters used.
   • Return Type: What the procedure or function returns (if applicable).
   • Description: A brief summary of the logic.
7. Triggers
   • Trigger Name: Name of the trigger.
   • Event: The event that activates the trigger (e.g., INSERT, UPDATE, DELETE).
   • Table: The table associated with the trigger.
   • Action: The logic executed when the trigger is activated.
8. Views
   • View Name: Name of the view.
   • Definition: The SQL query that defines the view.
   • Description: Purpose of the view.
9. Metadata
   • Owner: The user or role that owns the database object.
   • Date Created: When the object was created.
   • Last Modified: The last time the object was modified.
   • Permissions: Access rights for the object.
10. General Notes
    • Glossary: Definitions of terms and abbreviations used in the database.
    • Versioning: Details about the database version.
    • Audit History: Log of changes made to the database structure or dictionary.

This data dictionary serves as a central reference, aiding in understanding, maintaining, and troubleshooting the database.

Once you have used SQL to create a schema, the RDBMS stores information about the database and its tables in a series of files called the data dictionary^[1] (also called a catalog).

1. The data dictionary is the foundation of the database, it is where the RDBMS finds which tables are in a database, which columns are in the tables, which columns are primary or foreign keys, and what type of data to expect in those columns.
2. catalog: Also called the data dictionary. The catalog is the foundation of the database, it is where the RDBMS finds which tables are in a database, which columns are in the tables, which columns are primary or foreign keys, and what type of data to expect in those columns.

The data dictionary is the foundation of the database--it's where the RDBMS finds which tables are in a database, which columns are in the tables, which columns are primary or foreign keys, and what type of data to expect in those columns. If you modify a table, such as by adding a column or constraint, the RDBMS automatically updates the data dictionary to reflect the change. Formats for data dictionaries differ among RDBMSs, but they usually contain:

1. Definitions of table columns
2. Referential-integrity constraints among tables
3. Permissions
4. Definitions of views and custom data domains

When a user asks the RDBMS to work with database data, the system looks in the data dictionary to find the table and columns the user requested. If they exist, the RDBMS makes sure the user has permission to work with the data and then writes it to the screen via a virtual table. Since the data dictionary contains referential-integrity constraints, the RDBMS can also ensure that the proposed change or update follows those constraints. The next lesson describes the tables that are part of the data dictionary.

[1] data dictionary: The cross-reference of definitions and specifications for data categories and their relationships.