Basics of database management?
A database management system or DBMS is essentially nothing more than a computerized data-keeping system. Users of the system are given facilities to perform several kinds of operations on such a system for either manipulation of the data in the database or the management of the database structure itself. Database Management Systems (DBMSs) are categorized according to their data structures or types.
There are several types of databases that can be used on a mainframe to exploit inverted lists, hierarchic, network, or relational.
Mainframe sites tend to use a hierarchical model when the data structure (not data values) of the data needed for an application is relatively static. For example, a Bill of Material (BOM) database structure always has a high-level assembly part number and several levels of components with subcomponents. The structure usually has a component forecast, cost, pricing data, and so on. The structure of the data for a BOM application rarely changes, and new data elements (not values) are rarely identified. An application normally starts at the top with the assembly part number and goes down to the detailed components.
Hierarchical and relational database systems have common benefits. RDBMS has the additional, significant advantage over the hierarchical DB of being non-navigational. By navigational, we mean that in a hierarchical database, the application programmer must know the structure of the database. The program must contain specific logic to navigate from the root segment to the desired child segments containing the desired attributes or elements. The program must still access the intervening segments, even though they are not needed.
The remainder of this section discusses the relational database structure.
What structures exist in a relational database?
Relational databases include the following structures:
Database
A database is a logical grouping of data. It contains a set of related table spaces and index spaces. Typically, a database contains all the data that is associated with one application or with a group of related applications. You could have a payroll database or an inventory database, for example.
Table
A table is a logical structure made up of rows and columns. Rows have no fixed order, so if you retrieve data you might need to sort the data. The order of the columns in the order specified when the table was created by the database administrator. At the intersection of every column and row is a specific data item called a value, or, more precisely, an atomic value. A table is named with a high-level qualifier of the owner’s user ID followed by the table name, for example, TEST.DEPT or PROD.DEPT. There are three types of tables:
- A base table that is created and holds persistent data
- A temporary table that stores intermediate query results
- A results table is returned when you query tables.
Indexes
An index is an ordered set of pointers to rows of a table. Unlike the rows of a table that are not in a specific order, an index must always be maintained in order by DB2®. An index is used for two purposes:
- For performance, to retrieve data values more quickly
- For uniqueness.
By creating an index on an employee’s name, you can retrieve data more quickly for that employee than by scanning the entire table. Also, by creating a unique index on an employee number, DB2 will enforce the uniqueness of each value. A unique index is the only way DB2 can enforce uniqueness.
Creating an index automatically creates the index space, the data set that contains the index.
Keys
A key is one or more columns that are identified as such in the creation of a table or index, or in the definition of referential integrity.
Primary key
A table can only have one primary key because it defines the entity. There are two requirements for a primary key:
- It must have a value, that is, it cannot be null.
- It must be unique, that is, it must have a unique index defined on it.
Unique key
We already know that a primary key must be unique, but it is possible to have more than one unique key in a table. In our EMP table example, the employee number is defined as the primary key and is therefore unique. If we also had a social security value on our table, hopefully, that value would be unique. To guarantee this, you could create a unique index on the social security column.
Foreign key
A foreign key is a key that is specified in a referential integrity constraint to make its existence dependent on a primary or unique key (parent key) in another table.
The example given is that of an employee’s work department number relating to the primary key defined on the department number in the DEPT table. This constraint is part of the definition of the table.
