Assignment 05

Introduction

A database will only be as good as the data it contains. When complex updates and transformations are introduced into database management there is potential for the data to get degraded. SQL has several tools built in to maintain security and accuracy of data. These tools include views, functions, stored procedures and transactional statements.

Recommended Features of Professional Databases

A constraint is the first line of defense in database management to prevent “bad” data from being entered into the database. Constraints will prevent duplication, ensure data is formatted correctly and allow tables to reference each other. A view is a saved select statement; views are recommended for use in database design as it allows developers to work with a copy of the data, preserving the integrity and allows developers to work around complex SQL code. A stored procedure returns single or multiple values from a database and can accept different parameters to create different returns. This makes the utilization of this database feature more flexible.

Transactional Statements

It is often the case that two or more commends be used while managing the contents of a database. For example, moving a value from one table to another is perceived in lay terms as one action but requires multiple steps in the software; the data must be deleted from its current table and inserted at its new location. Without a transactional statement, data may be more vulnerable to loss as there are multiple steps involved with its placement. Code that has errors in it will also present problems if transactional statements are not used. If multiple update and transformation statements are given to the same table and half succeed and half fail, the table will exist in multiple phases of completion and will require additional work. This confusion and frustration can be prevented with the use of transactional statements. In lay term, transactional statements group multiple commands together and prevents execution unless all of the commands are able to be executed. These statements are crucial for database management and are very useful in a stored procedure. A rollback statement can be used as part of the transactional statement. When assigned, the rollback statement will act as a save point and return the data to its initial form. Although there is not an ‘UnDo’ command in SQL, the use of a rollback statement will operate in a similar fashion and prevent errors from emerging. A Try and Catch block may also be employed when working with transactional statements in stored procedures. The naming of try and catch block describes their function well. A try block will attempt to run a segment of code, if successful the code will be executed; if unsuccessful, the transactional statement will “catch” the error by triggering the catch block and returning an error message to the user.

Conclusions

            Using more advanced features in SQL increases what one is able to do with a database. The use of these tools can increase the efficiency and complexity of what one is able to do with a database and provide more information to different users.