Page381
The Data Dictionary
The data dictionary contains a description of the database tables. This is called metadata: data about data. The data dictionary contains database view information, information about authorized database administrators, user accounts including their names and privileges, and auditing information, among others. A critical data dictionary component is the database schema: it describes the attributes and values of the database tables. Table 9.5 shows a very simple data dictionary that describes the two tables we have seen previously in this chapter: employees and HR.
Table. 9.5 Simple Database Schema.
| Table | Attribute | Type | Format |
|---|---|---|---|
| Employee | SSN | Digits | ###-##-#### |
| Employee | Name | String | <30 characters> |
| Employee | Title | String | <30 characters> |
| HR | SSN | Digits | ###-##-#### |
| HR | Sick Time | Digits | ### days |
| HR | Vacation Time | Digits | ### days |
Database Query Languages
Database query languages allow the creation of database tables, read/write access to those tables, and many other functions. Database query languages have at least two subsets of commands: Data Definition Language (DDL) and Data Manipulation Language (DML). DDL is used to create, modify, and delete tables. DML is used to query and update data stored in the tables.
The most popular relational database query language is SQL (Structured Query Language), created by IBM in 1974. Many types of SQL exist, including MySQL, PostgreSQL, PL/SQL (Procedural Language/SQL, used by Oracle), T-SQL and ANSI SQL (used by Microsoft SQL), and many others.
Common SQL commands include:
- CREATE: create a table
- SELECT: select a record
- DELETE: delete a record (or a whole table)
- INSERT: insert a record
- UPDATE: change a record
Tables are created with the CREATE command, which uses Data Definition Language to describe the format of the table that is being created. An example of a Data Manipulation Language command is SELECT, which is used to search and choose data from a table. The following SELECT command could be used to create the database view shown in Table 9.4:
SELECT * FROM Employees WHERE Title = “Detective”
This means: show any (“*”) records where the Title is “Detective.”
Hierarchical Databases
Hierarchical databases form a tree: the global Domain Name Service (DNS) servers form a global tree. The root name servers are at the “root zone” at the base of the tree; individual DNS entries form the leaves. www.syngress.com points to the syngress.com DNS database, which is part of the dot com (.com) top level domain (TLD), which is part of the global DNS (root zone). From the root, you may go back down another branch, down to the dot gov (.gov) TLD, to the nist.gov (National Institute of Standards and Technologies) domain, to www.nist.gov.
A special form of hierarchical database is the network model (referring to networks of people, not data networks): this allows branches of a hierarchical database to have two parents (two connections back to the root). Imagine an organization’s org chart is stored in a database that forms a tree, with the CEO as the root of the hierarchy. In this company, the physical security staff reports to both facilities (for facility issues) and IT (for datacenter physical security). The network model allows the physical security staff to have “two bosses” in the hierarchical database: reporting through an IT manager and a facilities manager.
Object-Oriented Databases
While databases traditionally contain just (passive) data, object-oriented databases combine data with functions (code) in an object-oriented framework. Object-Oriented Programming (OOP) is used to manipulate the objects (and their data), managed by an Object Database Management System (ODBMS).