1) Introduction
The goal of this article is to illustrates concepts about the different databases available in the market. A brief introduction will be presented regarding concepts related to databases and the differences existing between the various types, and the criteria for selecting types supported by E3 will be discussed.
2) Important concepts about relational databases
DMS – Database Management Systems
Generic name of a database server, as MS SQL Server or Oracle. File Manager is the name that is used for databases based on files, like Access and XBases.
Database
A set of tables, procedures, and data, directly or indirectly related with the objective of storing information about a given system.
Table
Set of one or more data fields, related to one other. The use of these fields (normally understood as columns) is accomplished in blocks, and each information block (known as record) is normally visualized as a row in the table.
Index
Normally, a sub-set of fields of a table, automatically kept by the server, with the objective of speeding up the consultation to this table. It can present the restriction of being single, i.e., two different records cannot have the same values in the index.
Primary Key
Single index created to serve as standard index and to maintain the integrity of data. In complex systems, the use of PKs is essential. It can be used in other tables as a foreign key.
Query
A request made to the database. Normally used for the extraction of data, but it can have other functions besides this one. The whole group of functions supported by the database server can be used in a query.
Stored Procedures
DMS support procedures that are stored in the server itself. These procedures allow the inclusion of data, queries, and updates, besides the maintenance of the database itself, with the inclusion of new tables, etc. They are excellent interfaces for the execution of repetitive tasks.
Views
They are queries used for extracting data from the server, and to store them in the server itself. They work as the procedures, with the restriction that it is only possible to extract data.
Grants
The control of users in DMS systems is very rich in resources. Each user can receive different types of grants, and once logged in, the user has to respect his/her grants. In this way, it is guaranteed that there is no problem of inadequate use of information on the part of a user or another (be it by accident or by any other reason).
SQL
A language that is popularly used for interacting with DMS. There is an established standard (ANSI-92), but this standard does not contemplate some resources implemented in more recent DMS. Therefore, many commercial systems make increments to this standard.
Transactions
Transactions are “controlled sequences of alterations” accomplished in block in databases, i.e., if one of these alterations fails, the whole process of alterations is cancelled.
3) Differences between Managers of Files and DMS
Some of the differences between DMS and File Managers are:
- Control of access to information: database management systems support more efficient controls of users than file systems.
- Optimization of queries: Databases optimize the consultation to their content through the use of indexes and/or keys.
- Data safety: filing of data is optimized, and by not being based in files, it is harder to loose all data (be it by accidental erasing of the file, or by other similar reason).
- Network support DBs have support to network, which is transparent for the database users. In this way, it is possible to create systems in which a machine is responsible just for the maintenance of data, while the E3 would enter as application server.
- Support to procedures: through procedure in the DMS it is possible to manage policies of data storage, or to guaranty the consistency of data that is generated in E3. This is especially important when besides the collection of data, it is necessary to make calculations with these values.
- Capacity of storage: .mdb files (a type of file manager), support a maximum of 2GB of data (having a worsening of performance already form 100MB), while DBs support bigger loads of data without problem.
- Support to transactions: they guaranty the integrity of business logics. In case one of the operations fails, the system returns to the state previous to the beginning of transaction.
The characteristics above illustrated are just some of those differentiating the two types of databases. Nevertheless, the factors leading to a decision between one or other solution are, in the majority of times, technicians and commercials, as the servers require a higher initial investment, and also a higher management cost. There is a brief guidance on selection in the end of this document.
4) Differences between commercial DMS
There are no significant differences in the setting of one or other database in Elipse E3. The procedures for the use of one or other server are identical; only the parameterization of the Database server object is changed. Nevertheless, the differences between servers should be respected in E3.Some of these differences are:
- Support to SQL extensions: although SQL being the standard language for communication with the database, all servers create functions that are specific for treating different requests.
- Support to data types: in the same way as functions of the SQL standard are not sufficient for working with DBs, types supported by the different servers vary. Something that is always implemented with different methods of treatment is the “Date/Hour” type. Because of that, the use of this kind of data should be treated with attention, so that there is no problem.
- Mode of users authentication: the E3 automatically treats the issue of authentication of users. Nevertheless, different servers require different parameters for authentication. Verify the correct parameterization before accessing a given server.
5) Databases in E3
MS Access is the default database adopted by Elipse E3. All objects of data storage use the .mdb format for filing information. Besides that, the file that is necessary for the operation of a given project is automatically created when necessary. For example, when saving a record of alarms, if the file doesn’t exist, E3 will create the necessary file, and then will insert the record in the appropriate table. The most important is that it is not necessary to install the MS Access software in the PC in which the E3 will run.
As an alternative, Elipse E3 makes available an interface with other servers of commercial databases like MS SQL Server and Oracle, through the ODBC/ADO standard.
Table of reference for selection:
|
ACCESS IS BETTER WHEN… |
DMS IS BETTER WHEN… |
| The application is used by less than 10 users at the same time. | The application is used by more than 10 users at the same time. |
| Data and data structures should be easily modified by final users. | Data should be centralized, maintained, and protected. |
| The client machine is proportionally more powerful than the server machine. | The server machine is much more powerful than the client machine. |
| Satisfactory bandwidth of network. | Network is loaded. |
| Speed and “convenience” are more important than integrity. | Integrity of data is crucial. |
| Low availability of network managers and qualified databases. | Availability of network managers and qualified databases. |
| Only one application will routinely access data. | Various applications can access data. |
| Application will be responsible for maintaining the integrity of data. | The database will be responsible for the integrity of data, regardless of applications. |
| Small or moderate quantity of data (< 100MB). | Moderate to big quantity of data (> 100MB). |
