Data Dictionary and SQL
- Data Dictionary Definition- A DBMS component that stores metadata. The data dictionary contains the data definition and its characteristics and entity relationships. This may include the names and descriptions of the various tables and fields within the database. Also included are things like data types and length of data items. The inclusion of primary and foreign key also adds to the consistency of the database being built. Overall having a well designed data dictionary will help make it easier to build and maintain your database.
To summarize a data dictionary is a centralized repository of information about data such as meaning, relationships to other data, origin, usage, and format. (wikipedia.org)
There are two other types of Data Dictionaries-
Active Data Dictionary- A data dictionary that is automatically updated by the DBMS every time the database is accessed.
Passive Data Dictionary- Similar to Active DD however it is not automatically updated and usually requires a batch process to be run. Examples are http://www.red-gate.com/products/sql-development/sql-doc/ , http://www.apexsql.com/sql_tools_doc.aspx , and http://www.sqldatadictionary.com/ .
There is a third style of data dictionaries known as a middleware data dictionary. Middleware is computer software that connects software components or applications. The software consists of a set of services that allows multiple processes running on one or more machines to interact. Traditional data dictionaries provide structure and basic function to the database. Middleware data dictionaries are located within the DBMS itself and operate on a higher level. Middleware data dictionaries can provide alternate entity relationship structures that can be tailored to fit different users that interact with the same database. Middleware data dictionaries can also assist in query optimization as well as distributed database.
Middleware also helps database designers by reducing the amount of time it takes to create forms, queries, reports, menus and many other database components. They do this by automatically generating SQL code for common items such as forms and views. Some middleware data dictionaries can also help with data security as well as database optimization. It is a growing field with many new companies entering the market.
Example of a Data Dictionary and Oracle SQL
Below is a Simple data dictionary. Creating a data dictionary first makes creating tables in SQL easier. There are many variations to the data dictionary, however create one that will include everything you plan on coding later.
Creating the Student table in SQL is quite simple by using the data dictionary as a template. CREATE TABLE STUDENT ( STU_ID NUMBER(9), STU_AGE NUMBER(2), STU_GPA NUMBER(3,2), STU_LNAME CHAR(30), PRIMARY KEY (STU_ID), FOREIGN KEY (STU_GPA) REFERENCES GRADES); After the table has been created it can be modified or used in a query or report by connecting the SQL database to Access or some other frontend database management tool. For more information on connecting SQL database to Access or another DBMS see the wiki entry for linking Access to Oracle.