Introduction - Database Processing: Fundamentals, Design, and Implementation - page 12

is a large database, searching through the table to find, for example, all employees in the
accounting department would take a long time. To improve performance, we can create
an index (akin to the index at the back of a book) for DepartmentName to show which
employees are in which departments. Such an index is an example of a supporting struc-
ture that is created and maintained by a DBMS.
The next two functions of a DBMS are to read and modify database data. To do this, a
DBMS receives SQL and other requests and transforms those requests into actions on the
database files. Another DBMS function is to maintain all the database structures. For example,
from time to time it might be necessary to change the format of a table or another supporting
structure. Developers use a DBMS to make such changes.
With most DBMS products, it is possible to declare rules about data values and have a
DBMS enforce them. For example, in the Student-Class-Grade database tables in Figure 1-3,
what would happen if a user mistakenly entered a value of 9 for StudentNumber in the GRADE
table? No such student exists, so such a value would cause numerous errors. To prevent this
situation, it is possible to tell the DBMS that any value of StudentNumber in the GRADE table
must already be a value of StudentNumber in the STUDENT table. If no such value exists, the
insert or update request should be disallowed. The DBMS then enforces these rules, which are
called referential integrity constraints .
The last three functions of a DBMS listed in Figure 1-12 have to do with database ad-
ministration. A DBMS controls concurrency by ensuring that one user's work does not inap-
propriately interfere with another user's work. This important (and complicated) function is
discussed in Chapter 9. Also, a DBMS contains a security system that ensures that only autho-
rized users perform authorized actions on the database. For example, users can be prevented
from seeing certain data. Similarly, users' actions can be confined to making only certain types
of data changes on specified data.
Finally, a DBMS provides facilities for backing up database data and recovering it from
backups, when necessary. The database, as a centralized repository of data, is a valuable or-
ganizational asset. Consider, for example, the value of a book database to a company such as
Amazon.com. Because the database is so important, steps need to be taken to ensure that no
data will be lost in the event of errors, hardware or software problems, or natural or human
catastrophes.
The Database
A database is a self-describing collection of integrated tables. Integrated tables are tables that
store both data and the relationships among the data. The tables in Figure 1-3 are integrated
because they store not just student, class, and grade data, but also data about the relationships
among the rows of data.
A database is self-describing because it contains a description of itself. Thus, databases
contain not only tables of user data, but also tables of data that describe that user data.
Such descriptive data is called metadata because it is data about data. The form and format
of metadata varies from DBMS to DBMS. Figure 1-13 shows generic metadata tables that
describe the tables and columns for the database in Figure 1-3.
You can examine metadata to determine if particular tables, columns, indexes, or other
structures exist in a database. For example, the following statement queries the Microsoft SQL
Server metadata table SYSOBJECTS to determine if a user table (Type = 'U') named CLASS
exists in the database. If it does, then all the metadata about the table is displayed.
SELECT *
FROM SYSOBJECTS
WHERE [Name]='CLASS'
AND Type='U';
Do not be concerned with the syntax of this statement. You will learn what it means and
how to write such statements yourself as we proceed. For now, just understand that this is one
way that database administrators use metadata.