What is DBMS and which capabilities that should be provided by a DBMS?

The proliferation of data shows no signs of slowing down. As a result, businesses are investing in database management tasks, database managers, and database management systems to do the following:

• Keep business operations running as planned
• Keep track of customers, data inventory, and employees
• Maintain application and database performance
• Store and organize unique, varied types of data
• Automate database processes and procedures

Specifically, the benefits of a database management system are hard to ignore, especially as the amount of data people and machines create every day increases. One of the more notable benefits is improving the security of the data stored in a database. With DBMS, businesses can increase their access to data and help end users throughout the organizations share the data. As a result, these end users can deliver faster sales and make quicker decisions as they have access to the exact data they need.

Furthermore, with a database management system, businesses can eliminate the problems that occur with data inconsistency, which happens if multiple forms of the same data exist in different locations. A DBMS provides organizations a complete, clear view into the way data is shared, ensuring there aren’t unnecessary copies of data. Not to mention, A DBMS allows organizations to enforce data security and privacy policies to reduce the risk of a data breach. 

Finally, end users will be empowered to make informed decisions based on the data available. Better, more consistent data means high-quality, usable information that can help users make decisions with the exact data they need. Ultimately, this results in improved productivity throughout the organization.

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 z/OS®: inverted list, 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, and 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 detail 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:DatabaseA 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.TableA 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 is 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 that is returned when you query tables.

Figure 1. Example of a DB2 table (department table)

In this table we use:

• Columns–The ordered set of columns are DEPTNO, DEPTNAME, MGRNO, and ADMRDEPT. All the data in a given column must be of the same data type.
• Rows–Each row contains data for a single department.
• Values–At the intersection of a column and row is a value. For example, PLANNING is the value of the DEPTNAME column in the row for department B01.

IndexesAn 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.

KeysA 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 keyA table can only have one primary key because it defines the entity. There are two requirements for a primary key:

1. It must have a value, that is, it cannot be null.
2. It must be unique, that is, it must have a unique index defined on it.

Unique keyWe 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 in our table, hopefully that value would be unique. To guarantee this, you could create a unique index on the social security column.Foreign keyA 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.

What are the capabilities that should be provided by a DBMS?

What are the capabilities of DBMS and its application?

What is DBMS?

What are database capabilities?