Database Design and Implementation Process: Phase 6: Database System Implementation and Tuning

Phase 6: Database System Implementation and Tuning

After the logical and physical designs are completed, we can implement the database system. This is typically the responsibility of the DBA and is carried out in conjunction with the database designers. Language statements in the DDL, including the SDL (storage definition language) of the selected DBMS, are compiled and used to create the database schemas and (empty) database files. The database can then be loaded (populated) with the data. If data is to be converted from an earlier computerized system, conversion routines may be needed to reformat the data for loading into the new database.

Database programs are implemented by the application programmers, by referring to the conceptual specifications of transactions, and then writing and testing pro-gram code with embedded DML (data manipulation language) commands. Once the transactions are ready and the data is loaded into the database, the design and implementation phase is over and the operational phase of the database system begins.

Most systems include a monitoring utility to collect performance statistics, which are kept in the system catalog or data dictionary for later analysis. These include statistics on the number of invocations of predefined transactions or queries, input/output activity against files, counts of file disk pages or index records, and frequency of index usage. As the database system requirements change, it often becomes necessary to add or remove existing tables and to reorganize some files by changing primary access methods or by dropping old indexes and constructing new ones. Some queries or transactions may be rewritten for better performance. Database tuning continues as long as the database is in existence, as long as performance problems are discovered, and while the requirements keep changing (see Chapter 20).