Database Administration Tools:
The Data Dictionary:
A data dictionary was defined as “a DBMS component that stores the definition of data characteristics and relationships.” You may recall that such “data about data” are called metadata. The DBMS data dictionary provides the DBMS with its self-describing characteristic.
Two main types of data dictionaries exist: integrated and standalone. An integrated data dictionary is included with the DBMS. For example, all relational DBMSs include a built-in data dictionary or system catalog that is frequently accessed and updated by the RDBMS. Other DBMSs, especially older types, do not have a built-in data dictionary; instead, the DBA may use third-party standalone data dictionary systems.
You May Also Like:
Database is Corporate Asset
Role of Databases in An Organizations
Summary of DBA Activities
DBA Evoluation
Data dictionaries can also be classified as active or passive. An active data dictionary is automatically updated by the DBMS with every database access, thereby keeping its access information up to date. A passive data dictionary is not updated automatically and usually requires running a batch process. Data dictionary access information is normally used by the DBMS for query optimization purposes.
The data dictionary’s main function is to store the description of all objects that interact with the database. Integrated data dictionaries tend to limit their metadata to the data managed by the DBMS. Standalone data dictionary systems are usually more flexible and allow the DBA to describe and manage all of the organization’s data, whether or not they are computerized. Whatever the data dictionary’s format, its existence provides database designers and end users with a much-improved ability to communicate. The data dictionary typically stores descriptions of all:
• Data elements that are defined in all tables of all databases. Specifically, the data dictionary stores the names, data types, display format, internal storage format, and validation rules. The data dictionary tells where an element is used, by whom it is used, and so on.
• Tables defined in all databases. For example, the data dictionary is likely to store the name of the table creator, the date of creation, access authorizations, and the number of columns.
• Indexes defined for each database table. For each index, the DBMS stores at least the index name, the attributes used, the location, specific index characteristics, and the creation date.
• Defined databases. This includes who created each database, when the database was created, where the database is located, who the DBA is, and so on.
• End users and administrators of the database.
• Programs that access the database. This includes screen formats, report formats, application programs, and SQL queries.
• Access authorizations for all users of all databases.
• Relationships among data elements. This includes which elements are involved, whether the relationships are mandatory or optional, and what the connectivity and cardinality requirements are.
CASE Tools:
CASE is the acronym for computer-aided systems engineering. A CASE tool provides an automated framework for the Systems Development Life Cycle (SDLC). CASE uses structured methodologies and powerful graphical interfaces. CASE tools are usually classified according to the extent of support they provide for the SDLC. For example, front-end CASE tools provide support for the planning, analysis, and design phases; back-end CASE tools provide support for the coding and implementation phases. The benefits associated with CASE tools include
• A reduction in development time and costs.
• Automation of the SDLC.
• Standardization of systems development methodologies.
• Easier maintenance of application systems developed with CASE tools.
One of the CASE tools’ most important components is an extensive data dictionary, which keeps track of all objects created by the systems designer. For example, the CASE data dictionary stores data flow diagrams, structure charts, descriptions of all external and internal entities, data stores, data items, report formats, and screen formats.
Several CASE tools provide interfaces that interact with the DBMS. Those interfaces allow the CASE tool to store its data dictionary information by using the DBMS.
A typical CASE tool provides five components:
• Graphics designed to produce structured diagrams such as data flow diagrams, ER diagrams, class diagrams, and object diagrams.
• Screen painters and report generators to produce the information system’s input/output formats (for example, the end-user interface).
• An integrated repository for storing and cross-referencing the system design data. This repository includes a comprehensive data dictionary.
• An analysis segment to provide a fully automated check on system consistency, syntax, and completeness.
• A program documentation generator.
