Description of investigation techniques

In order to improve a database performace or accuracy, an inital number of tests would be made on the customer data, normally using SQL scripting to verify the distinctness, cardinality and adherence to constraints. Table joins would be tested for orpaned data (unreferenced data), test-join SQL would select known correct data to prove join validity.

Summaries of table information would be matched against expected results. EG: product codes, accounts, addresses. This is normally assessed the customer, who can compare the expected and actual quantities. A backup copy of the data is normally made before an update script is run to produce correct the data. .

Quality issues may arise because of the accumulation of different data at different times, using various select-option lists. (This often shows up, for example, with address lines being accumulated in one or more columns depending on the persons training. -this would meandn printing address would look different for the different entires, or SQL queries would select different records demoending on the columns defined). Update proceedural changes may need to be reflected in historical data that is still important.

• Extracting all data from a database in a text fromat for archive or preservation.
• Reconstructing a new database for a new database engine.
• Reconstructing databases indexes to speed up data access.
• Deleting/Rebulding indexes where necessary.
• Creating new indexes (where they would greatly speed up data access)
• Creating new lookup tables providing permitted values).
• Locating data tha is now missing from queries keywords in joins have been altered or deleted.
• Detecting missmatches , missing data within a database, repairing tables.
• Constructing tables, triggers, proceedures to restore functionality or preserve database normal form.
• Archiving obsolete, un-referenced or date-aged data that could be removed from operational databases.
• Adding hidden flags to mark obsolete or aged data for certain users, (while keeping the data for legal or compliance purposes).
• Creating spreadsheets from database queries.

Techniques:

• Testing of table-joins and listing of any orphaned data
• Construction of Entity-Relationship (ER) diagrams to describe database objects.
• Generating missing data, archiving obsolete data.
• Modifying data to ensure uniqueness prior to adding indexes to tables, to imporve search speeds.
• Creating reports of accounts to summarise data.
• Automating databases tasks, actioned by Web-page buttons etc.
• Adding data-entry forms, pages and reports.

Services:

• Constructing conceptual and functional data-models.
• Creating relational schemas.
• Importing and exporting tablular data.
• Data Clensing: revising specific sets of data, updating and normalizing data.
• Preparing and testing Data-Mineing programs.
• Report writing. Reports from databases to Markup formats (Eg: HTML, EXCELL), using scripts.
• Automation of periodic reports from internal databases to external web-servers for daily summaries.

  Microsoft EXCEL spreadsheets are very usefull as methods of displaying account data , but EXCEL is not designed to be used as a database or data storage system. Over time a company may have accumulated large spreadsheets that are critical to the functioning of the business. Spreadsheet duplictes are instantly out of date compared with other copies, so in effect they are an unsound way of distributing data. Company critical spreadsheers can be converted into database tables by normalizing the data, providing search indexes and normal joins accross unique keys. to provide accurate, single-sources , multi-user views of customer data. This can be done as a one-off instance to transfer to a database, or a process that can be repeated.

At the end of a contract, all programs, all scripts and other database objects are fully documented and suplied so that any further updates can be made using similar code at a later date.