- Database: A database is a collection of related information stored in a structured format.
- Relational Database: A collection of "related" databases, for example and travel agent booking system consisting of customer, airline and account details, can be combined and would be referred to as a Relational Database.
- Record: A database comprises of records and each record contains all the information about a single member or item.
- Field: A record comprises of fields where each field is a single piece of information relating to the record for example your Medibank record may contain individual fields for first name, last name, Medibank number, date of birth, etc. All records have the same structure and so contain the same fields but obviously each field has its own unique data.
- DBMS: Database Management System.
- RDMS: Relational Database Management System.
- Constraint: A constraint is a property assigned to a column or the set of columns in a table that prevents inconsistent data values from being placed in the column/s. They are used to enforce data integrity and therefore database reliability. There are several types of data integrity including Entity, Domain, Referential and User-Defined.
- Dirty read: If a database allows a user to read updated data that has not been "committed" by the user inputting the data then this is called a dirty read. A database should only allow data to be read that has been committed.
- Query optimiser: When you perform a report or query the optimiser is responsible for determining the fastest and most efficient way to perform the operation. Often the query optimiser relies on table and index statistics to determine the best way to optimise the operation.
- Primary key: The Primary key is a field that uniquely identifies each record in the table and is often an item that is unique for each record in the database such as you drivers license number for example. The database can generate a primary key itself if you require it but it makes more sense to use an existing unique field. A foreign key identifies records in an external table.
- JDBC: JDBC is an API that is included in J2SE and J2EE releases that provides cross-database connectivity to a broad range of SQL databases and even provides access to other data sources, such as spreadsheets or flat files.
- Stored procedure: SQL commands are compiled into a stored procedure that can be called and executed repeatedly saving time and boosting performance.
- Triggers: A trigger is an action that automatically takes place when a certain database event occurs, the action could for example be a stored procedure that "fires" when a trigger condition is satisfied.
- OLAP: OLAP is an abbreviation for Online Analytical Processing and is a category of tools that provides analysis of database data. This enables users to analyse different dimensions of multidimensional data and can for example provide trend analysis.
- ODBC: ODBC is an abbreviation for Open DataBase Connectivity, this is a standardised method for accessing databases developed in 1992 by the SQL Access Group. ODBC makes it possible to access and exchange data between any compliant database systems.
- ACID Transaction Management: ACID is the cornerstone for DBMS and is an abbreviation for Atomicity, Consistency, Isolation, and Durability. Obviously if you are attempting to purchase a DVD online at the same time another user is attempting to purchase the identical DVD you would want to ensure your transactions were independent and distinguishable with no overlap to ensure a reliable and accurate transaction occurs.
- Entity integrity: Refers to the fact that there are no duplicate rows in a table.
- Domain integrity: Ensures valid entries in a column by restricting the type, range or format of the data values possible.
- Referential integrity: Where row data is shared between more than one record this ensures that the data cannot be deleted while there are still dependent records.
- User-defined integrity: You may have your own business rules that do not fall into the other data integrity categories, these can be enforced by "User-Defined Integrity"
Ensuring the database performs well is not as simple as it sounds, and can appear a black art to the uninitiated. Unless you know what you are doing, a self install is probably a bad idea. Based on your own scenario, most vendors will be able to suggest settings that will improve the performance of the database appreciably over its standard out-of-the-box configuration. We did find, however, that some of the products include Wizards that did a good job of optimising the database by asking the user to select simple descriptions of their database type and usage patterns.
Security will always be a very important consideration given that the database will contain sensitive customer information such as credit card details. Not only is it important that the database be encrypted, it is also important to provide an audit trail to ensure there is no abuse of privileges by "authorised staff".
The front end for the database in this scenario would include data consistency and bounds checking but the database should also support robust integrity checking of its own.
What follows is not a detailed breakdown of the databases tested but rather a highlight of some of the more unique features of each vendor's product.