I am currently doing a SQL assignment but a certain task has mentioned that we need to implement validation and verification for our tables in the database which we created.
However we did not learn anything about this topic and after googling for hours I only found the check constraints which you can give in the create table statement.
Please mention some of the TECHNIQUES of validation and verification for a SQL table or database.
Usually you validate by running some sort of test on the data.
There are different layers through which data passes and there are different types of tests.
If you consider the following system layers:
- user interface/client layer
- application layer
- database layer
You can do data validation at any of these layers, with following differences
- validation in the database layer is preferred because you can have different applications connecting to the same database and while this is a good thing, the validation in those applications is not necessarily the same, therefore validation that is implemented on the database layer is better in terms of data integrity
- at the same time validation in the database layer should be fastest because the database layer is closest to the data (data does not need to pass through other layers to be processed, in this case validated)
- the exception is if the validation rule is static and does not depend on other data from the database (for example testing if the input is an even positive integer), then the client can validate the data before the database and avoid unnecessarily tasking application layer and or database layer. (at the same time you want to implement validation at the database layer also, just in case there is a bug or other applications will connect to the database). same principle applies to application layer - if there is a validation that application layer can perform without hitting the database server it should do it, but the same should be done (guaranteed) in the database layer.
You can consider following to be validation tests (validation types):
- type validation (examples: is the data that is inserted an integer?). this can be checked at database layer by declaring proper type on the column.)
- domain validation (example: is the data a positive integer?). this can be checked at the database layer using CHECK constraint
- relationship validation (example: for the record you are adding is there already a record in another table). this can be checked at the database layer by proper use of FOREIGN KEYS
- business rules validation (these can be complex, for example 'if the value you are trying to record is > 100 then make sure that you can record IP of the user, otherwise cancel transaction; if not don't bother'.) these kind of validations can be done through triggers and stored procedures. in real world business rules validation are often implemented at the application layer (for various reasons - from relative non-maintainability and non-portability of PL/SQL, which is usually the language you will have to write triggers in, to the fact that implementing complicated triggers can reduce performance and finally make the system very complex).
You need to make sure that the schema you created which was based on the specifications from the teacher, actually does the job. Validation is the process whereby you test something using another means. So...write a small validation program that try to insert bad data. Make a few test cases. Once your schema design has passed the validation it can be said to be verified or validated.
Menu
684
