why is data validation important when creating a database
Data validation is often a topic of great importance when it comes to databases. Since information is constantly being updated, deleted, queried, or moved around, having valid data is a must. By practicing simple data validation rules, databases are more consistent, functional, and provide more value to their users. When using SQL, data validation is the aspect of a database that keeps data consistent. The key factors in data integrity are constraints, referential integrity and the delete and update options. The main types of constraints in SQL are check, unique, not null, and primary constraints. Check constraints are used to make certain that a statement about the data is true for all rows in a table. The unique constraint ensures that no two rows have the same values in their columns. The not null constraint is placed on a column and states that data is required in that column. However, in SQL, the not null constraint can only be placed on a single column. Finally, the primary key constraint is a mixture of the unique constraint and the not null constraint meaning the no two rows can have the same values in their columns and that a column must have data. Referential integrity is a key aspect in data integrity that is usually associated with two tables; the lookup table and the data table. Typically, referential integrity is applied when data is inserted, deleted, or updated. The inserts and updates to the data table prevented by referential integrity happen in the foreign key column. Referential integrity will prevent inputting data in the foreign key column that is not listed in the lookup table. However, the inserts and updates allowed by referential integrity occur when the data inserted is located in the lookup table. In addition, updates and deletes in the lookup table prevented by referential integrity occur when the data in the foreign key column of the data table is not present in the lookup table. Consequently, the inserts and deletes allowed by referential integrity come from data located in the lookup table. In addition to the updates and deletes authorized by referential integrity, there are three options associated with it:
Data Validation is also a key in databases created through Microsoft Access.
Data validation can be implemented during the design process of a database by setting data requirements for the user input to avoid errors. There are several different ways to validate data through Microsoft Access, some of which include: 1. Validation Rule Property: This property allows the database designer to set a validation rule, so that data inputted into the database must follow a certain rule. Example: Student titles such as Freshman, Sophomore, Junior, and Senior must be entered as вFRв, вSFв, вJRв, or вSRв. The database designer can also implement a validation rule text that displays a message stating the above rule if entered incorrectly. 2. Data Types: You can restrict data types that are entered into an Access database by setting a certain required data type. Example: If a data type is set to be вnumericв, then all other types, such as a character(s) will be denied with an error. By setting an input mask in a field in Microsoft Access, it controls the way data can be entered. Example: Input masks can specify that social security numbers be entered in the form of вAAA"-"AA"-"AAAAв. By using this setting the userвs input automatically formats to the specified form. 3. Required Property: Using the required property is an easy way to avoid null values in unwanted areas. If the required property is set for a certain field but the user attempts to leave it blank, they will be prompted with an error message, requiring data to be entered before going any further. Patrick, John J. SQL Fundamentals: 3rd Edition. Boston: Prentice Hall, 2009. "Validating Data in Microsoft Access Database Solutions for Microsoft Access databasedev. co. uk. " Database Solutions Downloads for Microsoft Access databasedev. co. uk. Web. 05 Dec. 2009. In software testing, Data Migration Testing is conducted to compare migrated data with original data to discover any discrepancies when moving data from a legacy database(s) to a new destination database.
These data can be migrated either automatically using a migration tool or by manually extracting data from the source database and inserting the data into the destination database. Data migration testing encompasses Data Level Validation testing and Application Level Validation testing. Data Level Validation Testing: This type of software testing verifies that data has been migrated from multiple databases to a common database without any discrepancies. The following levels of verifications will be performed during Data Level Validation Testing: Row counts: Verify the number of records that will be migrated. Data verification: Verify the accuracy of data of a selected sample from the migrated data. Entitlement verification: Verify the destination database set up for users and data samples. Application Level Validation Testing In Application Level Validation testing, a software tester verifies the functionality of a sample migration application that was migrated from an old legacy system to the new system. Application Level Validation testing ensures smooth running of the migrated application with the new database using following validations: After migration, log in to the new application and verify a sample data set. After migration, log in to legacy systems and verify the locked/unlocked status of accounts. Verify customer support access to all legacy systems, despite the user being blocked during the migration process. Verify whether new users are prohibited from creating a new account in a legacy system after launching the new application. Verify immediate reinstatement of user access to the legacy system if migration to the new system fails. Verify the termination of access to legacy systems at migration. Validate system login credentials for the new application. Test Approach for Data Migrating Testing Data Validation Test Design When you test database migration, it is important to create a set of SQL queries to validate the data before (source database) and after (destination database) migration.
The validation queries can be arranged in a hierarchy and it should cover the designed scope. For example, to test if all users have been migrated, it is essential to check how many users are in the source database and how many have been migrated. Checking the raw counts of each database will ensure this. Take a sample data set from the source and compare the data with the destination data in the database. Check the different time formats/zones, currencies etc. Test Environments Test environments should consist of a source database copy and a blank isolated destination database. A tester can migrate data using a migration tool, which will facilitate the migration both table-by-table and using a set of reference tables. The tool should be able to accommodate a large data load since the data can include data from historical databases. Data Validation Test Runs The database migration process must be completed prior to the test depending on the test design. Reporting Bugs If the migration test fails, it is important to report the bug with the following information: Tips to Create an Effective Data Migration Test Approach Users should be able to access existing data and post migration data easily without any issues. Performance of database should be the same or better after the migration. Note the duration of the migration. A duration of a migration can be long, running the risk of application downtime. Have a copy of the source databases to conduct re-tests at any time on a new database. It will also help reproduce the bugs. Corrupted data should not be migrated to the destination database, and necessary actions should be taken to resolve the corrupted data. Get stakeholders involved the migrationPplan, as their permission to access different data sources could be mandatory. Make sure there are no inconsistencies in currency, date and time, time zone fields, and decimal points of currencies. Click Click Author: i3 Labs is the technology and innovations Lab at Brandix i3
- Views: 83
why do we use view in sql
why do we use queries in a database
why do we use index in sql
why do we use forms in a database
why do we use data validation in excel
why do relational databases use primary keys and foreign keys
why use a database instead of a spreadsheet