Database Design

In most modern relational DBMSs, such as IBM DB2, Microsoft SQL Server, or Oracle, a new database implementation requires the creation of special storage-related constructs to house the end-user tables. These constructs usually include the storage group, the table space, and the tables (see Figure 8.12). Note that a table space may contain more than one table.
During the implementation and loading phase, we must also address performance, security, backup and recovery, integrity, company standards, and concurrency control. In general, putting related tables in one table space improves access performance.
Performance
Database performance is one of the most important factors in certain database implementations. However, not all DBMSs have performance-monitoring and fine-tuning tools embedded in their software, thus making it difficult to evaluate performance.
Performance evaluation is also rendered more difficult because there is no standard measurement for database performance. Performance varies according to the hardware and software environment used. Naturally, the database’s size also affects database performance.



Important factors in database performance also include system and database configuration parameters, such as data placement, access path definition (important in the case of hierarchal and network databases), use of indexes, and buffer size.





Security



Data stored in the company database must be protected from access by unauthorized users (such as hackers) and protected from destructive viruses. Consequently, we must provide for at least the following:



Physical security allows physical access to areas by authorized personnel only.

Password security allows the assignment of access rights to specific authorized users.

Access rights can be established through the use of database software.

Audit trails are usually provided by the DBMS to check for access violations.

Data encryption can be used to render data useless to unauthorized users who might have violated some of the database security layers.

Diskless workstations allow end users to access the database without being able to download the information from their workstations.





Backup and Recovery



Timely data availability is crucial in the production of information. Unfortunately, the database can be subject to data loss through unintended data deletion, power outages, and so on. Data backup and recovery procedures create a safety vault, allowing the database administrator (DBA) to ensure the availability of consistent data.





Integrity



Data integrity is enforced through the proper use of primary keys and foreign keys.





Company Standards



Database standards may be partially defined by specific company requirements. The database administrator must implement and enforce such standards.





Concurrency Control



The feature that allows simultaneous access to a database while preserving data integrity is concurrency control. Failure to maintain concurrency control can quickly destroy data consistency and database’s effectiveness. Consider the scenario depicted in Table 8.6:








This scenario can be prevented by using concurrency-control algorithms.





8.4.4 Testing and Evaluation



Once the data have been locked into the database, the DBA tests and fine-tunes the database for performance, integrity, concurrent access, and security constraints. The testing and evaluation phase occurs in parallel with applications programming. Programmers use database tools to prototype the applications during the coding of the programs.



If the database implementation fails to meet some of the system’s evaluation criteria, several options may be considered to enhance the system:



For performance-related issues, the designer must consider fine-tuning specific system and DBMS configuration parameters.

Modify the physical design.

Modify the logical design.

Upgrade or change the DBMS software and/or the hardware platform.





8.4.5 Operation



Once the database has passed the evaluation stage, it is considered to be operational. At this point the database, its management, its users, and its application programs constitute a complete information system.



The beginning of the operational phase invariably starts the process of system evolution. As soon as all the targeted end users have entered the operations phase, problems that could have been predicted during the testing phase begin to surface.





8.4.6 Maintenance and Evolution



The database administrator must be prepared to perform routine maintenance activities within the database. Some of the required periodic maintenance activities include:



Preventive maintenance (regular maintenance of the database including backup).

Corrective maintenance (fixing and recovery of the database).

Adaptive maintenance (enhancing performance, restructuring the database by adding or subtracting entities and/or attributes).

Assignment of access permissions and their maintenance for new and old users.

Generation of database access statistics to improve the efficiency and usefulness of system audits and to monitor system performance.

Periodic security audits based on the system-generated statistics.

Periodic system-usage summaries for internal billing or budgeting purposes.



Given the likelihood of new information requirements, the demand for additional reports and new query formats will require changing application programs and possibly minor changes in the database components and contents. These changes can be easily implemented only if the database design is flexible and if all the documentation is updated online.