In the world of constant change in ICT and applicable software to improve the security of information Relational Database Management Systems (RDBMS) are at stake. It is especially amplified when talking about the reality of post-modern society where information is the basic product. Making some parallels with RDBMS, it is quite significant to admit such a strategic use of them within large corporations. Thus, banking systems, corporate activities, exchange of valuable products are all related to the RDBMS.
However, most of the companies place their stake on the type and relevance of the database to the norms and requirements of time. In this respect one simply varies among RDBMS products by Oracle, IBM, Microsoft, and other software “tycoons” of modernity. The scope of main patterns going round the basic implementation of RDBMS is researched in their relation to each other. It means that featuring each product, one should take a look at its type, maintainer, date of release, main characteristics, and, particularly, type of a license.
Contemporary companies take advantage of the variability of tables which each RDBMS is constructed of. Such a complicated at the sight conception of the system requires one to shape it through a set of the main and subsidiary tables. The main elements in the structure of RDBMS are: Permit and Well tables linked mutually and extended to Loc and Construct sub-tables (RBDMS Online 1). Hence, more attention is drawn to the implications of each among four below analyzed RDBMS so as to state particular similarities and/or differences among them.
It is especially signified on the example of Firebird, SQLite, MySQL and DB2. Each of the systems is analyzed through all needful parameters in order to point out the idea of their functional workability and security for a definite company. The most important aspects of the relational database management system are the database interoperability with other databases, the maximum file size.
In this respect several options should be discussed: DB size, table size, row size, amount of columns per row, blob/clob size, number size, maximum and minimum date value, and column name size; moreover, data types (small integer – 16bit, Integer – 32bit, big integer – 64bit), security features and functions, database functions including discussion of union, intersect, except, inner joins, outer joins, inner selects, merge joins, blobs and clobs, common table expressions, and windowing functions make the software programmes useful implications in information, communication, and business areas. Consequently, all these features or at least most of them should be analysed and discussed in order to find out the advantages and disadvantages of those for further use.
Results of the Research
The software application of IBM DB2. RDBMS DB2 produced by IBM Corporation is one of the most accredited and usable systems which runs the gamut of corporate relational hubs and applicable dataset implementation. In this respect the system is known to be first yielded in 1983 while its latest updated version (9.7) was announced in 2009 (Chamberlin and Chamberlin 27). Thereupon, the system has shown its high standards in approaching the quality of services maintained and drawn by the companies.
The system went through the evolution of different upgrades and connectivity to other RDBMS’s. In this respect it is necessary to remark its interoperability with, particularly, such other systems as: SQL and XQuery (Adler 1). Thus, an innovative character of DB2 is considered today with its rich in applications character that encompasses, perhaps, the overall set of programmes needful for modern business.
In accordance with SQL, it is quite clear that DB2 is relevant to be operated along with other databases. The question is that the type of the query and its code as well as construct is introduced to be remarkable throughout the system’s interaction with other RDBMS products. It is known that BD2 is mostly considered to be in based on UDB runtime client (Preston 532). Thus, it shares the workability function with the rest similar databases in terms of practical or coherent implementation.
To make it plain, it is generally accepted that DB2 databases are interrelated in terms of catalogue tables logically and physically (Preston 532). It is no wonder that datasets are all accomplished by particular engine dispatch units (EDU) going hand in hand with SQL optimizers and compilers (Preston 533). Nevertheless, the functionality of DB2 measures with its complexity. In most points it is facilitated due to database trigger which in part serves to better evaluate and detect errors in the system.
What is more, the previous studies have shown the “DB2 UDP object-relational capability” pursuant to user-defined functions (UDF’s) (Adler 3). This makes the problem of compatibility of the system solved, in particular. It is no surprise that in the course of time DB2 has grown into a unified concept run on UNIX, Windows, Mac OS X and other OS’s. Such an all-pervading approach gave positive results in DB2’s market share. It is known to have been 30% of the database market being popular in all segments of businesses (from small up to large) (Mullins 17). It touched upon the DB2 Universal Database V8 (T-Rex) (Mullins 17).
However, it is needful to state that the newest version of DB2 is compatible for big integers (64 bit) that are usable for the currently most popular Windows 7, XP, Vista, Linux, and UNIX (IBM). Moreover, DB2 9.7 version is more improved in terms of functionality and security as well as IT costs as compared to Oracle 11g (IBM). This is why IBM is trying to perceive the rest of the market so as to make it possible to extend horizons of its use.
Further still, DB2 is explicitly used for making corporate operations functionally complete, but costly lower than those RDBMS’s proposed by other suppliers. In this respect one should focus on the compatibility of DB2 optimizer for SAP (IBM). All in all, the system serves as a binding feature for making information run from one table in a row to another. It provides a circumspect relation between inner and outer joins of DB2. The security of the system is outlined with options of authentication and authorization: “Access to the DB2 database system is managed by facilities that reside outside the DB2 database system (authentication), whereas access within the DB2 database system is managed by the database manager (authorization)” (IBM).
The system is remarkable for its support of different features as related to linking tables. In this respect inner joins are represented in the system as referred to DB2 optimizer using three join methods, namely:
- Nested-loop join
- Merge join
- Hash join (IBM).
Looking at how tables are performed and used, join operations, thus, provide a scope of data generic for working out preliminary outlined task. One hereby should draw attention to alteration of queries: “You can provide explicit join operators, such as INNER or LEFT OUTER JOIN, to determine how tables are used in the join” (IBM). In DB2 9.7 there is a need to avoid outer joins by means of removing rows with zero value in definite columns. It is a particular attribute of the amplified RDBMS. Windowing function is supported in the system while copying and after migration of data (IBM).
It is a gist of having a transmitted character of removing and creating data with more conveniences. Exception tables are also represented in the newest version of the software. Thereupon, they serve to reflect user-defined actions specific for linking “set integrity statement with immediate checked option” (IBM). The geometry of intersections in DB2 is likely to be improved a rollout option which is faster and more reliable. UNIONS and inner selects also accompany the overall architecture of DB2 from the 5th optimizer version up to 9.7. Hence, the functional and physical characteristics of DB2 proved to suit contemporary corporate needs.
Firebird, a cross-platform software programme
The Firebird Version 2.0.6 is one of the latest versions of this programme. This is Release Candidate 1 of 29 March 2010. This is an open source system of relational database management. It runs on GNU, Linux, Windows, and many Unix platforms. So, it can be considered one of the most interoperable ones because the programmes written by the Microsoft Corporation can run on Windows only, no exceptions were made.
The Firebird is an effective version of the relational database management system. The official website provides the users and ordinary visitors with sufficient set of documentation information. You can get all information concerning the installation, operation, and maintenance of the programmes. The Firebird develops a lot of different software programmes that operate on different platforms.
The currently discussed version is the latest innovation developed by the Firebird.The full name of the version is the Firebird. The discussed version is the 2.0.6. The maintainer of the Firebird is the Firebird Project. The interoperability with other databases is rather high in terms of the operation on different platforms, except for AmigaOS and Symbian.
The maximum file size should be discussed separately for different options. Thus, the maximum DB size of the firebird is unlimited which is interpreted as the largest known size available for databases, the maximum table size is about 32 TB, the maximum row size stands for 65,536 B, whereas the data types used influence the maximum number of columns per row. The maximum blob/clob size is approximately 2 GB, the maximum CHAR size is 32,767 B, the maximum number size is 64 bits. The date value limits range from 100 of minimum size and to 32768 of maximum size. The maximum column name size is 31.
The data type may vary from small integer (16bit) to big integer (64bit), including Integer (32bit). As suggested by Digital Equipment Corporation in Section 4.1 of the general concepts, data types are described in the following way:
Each host language has its own data types, which are separate and distinct from SQL data types, even though similar names may be used to describe the data types. Not every SQL data type has a corresponding data type in every host language.
Security features and functions (19).
The data type of the firebird stands for 6.5 which correspond to the big integer (64bit). This data type can be also referred to as the bigint (Firebird n.p.). The database functions in terms of being supported by the programme include unions that are supported by the firebird. In addition such capabilities as inner joins, outer joins, inner selects, merge joins, blobs and clobs, and common table expressions are supported by the Firebird. Unfortunately, the support of the following capabilities had not been detected yet: intersect, except, windowing functions, and parallel query.
The overall characteristics of the firebird can be found on the official website of the firebird Project as well as the characteristics of other software programmes are better identified by the specialised websites. There are also features implemented natively for RDBMS, suchlike ACID, referential integrity, transactions, Unicode, and interface. All of them can e found in the latest version of the Firebird relational database management system including the SQL interface which is common for most modern systems currently used in the information and technologies area.
The software licence includes IPL and IDPL. As the relational database management systems are operated with the help of such software computer programmes as Firebird developed and maintained by the firebird Project, it is preferable that the whole package of installations and all necessary documentation required for normal operation and maintenance of the programme was developed by the Firebird Project. Other types of software programmes of this type are effective as well, though some drawbacks and errors may be detected in the organisation.
The Firebird is the cross-platform, so, it is aimed at operating on different types of platforms. As it was originally developed for Windows, Mac OS, and Linux, it now has been developed for additional BSD and Unix operating systems. The Firebird can be considered one of the software cross-platform programmes for relational database management systems representing the new generation of the programmes of this type.
SQLite for relational database management system
The next software programme for relational database management system that should be considered is the SQLite. “SQLite is a software library that implements a self-contained, serverless, zero-configuration, transactional SQL database engine. SQLite is the most widely deployed SQL database engine in the world. The source code for SQLite is in the public domain” (SQLite n.p.). As a rule, the SQLite is considered as a compact library that can store the information including the materials of the smallest size.
The current development and maintenance of the SQLite and related programmes is sponsored by the members of the SQLite Consortium, including Symbian, Adobe, Mozilla, and Bloomberg. Thus, the software is developed by the corporations that are aware of the requirements to the software programmes and all possible features that the most adequate software programmes should possess.
The latest development and the latest version of the SQLite is the Version 22.214.171.124 which is “recommended for all new development. Upgrading from version 3.6.12, 3.6.13, 3.6.22, and 3.6.23 is optional. Upgrading from all other SQLite versions is recommended” (SQLite n.p.). The main developer and maintainer of the project is D. Richard Hipp. As the Firebird which was previously discussed, the SQLite is a cross-platform which can run on all operating systems including “Unix (Linux and Mac OS X), OS/2, and Windows (Win32 and WinCE) are supported out of the box. Easy to port to other systems.” (SQLite n.p.)
The SQLite software programme has a lot of advantages before other software programmes developed by other companies. As claimed by the developers of the SQLite and presented at the official website of the company,
SQLite is often used as a surrogate for an enterprise RDBMS for demonstration purposes or for testing. SQLite is fast and requires no setup, which takes a lot of the hassle out of testing and which makes demos perky and easy to launch (SQLite n.p.).
As you can see, the interoperability of the SQLite software programme for RDBMS is high comparing with other programmes of this type including the ones developed by the Microsoft Corporation.
The basic features that can be compared with other programmes include the support of ACID, referential integrity, transactions, and Unicode; the interface of the SQLite is based on the SQL. As the maximum file size should be considered regarding many options, it includes maximum DB size which stands on 32 TB and means 32 KB maximum page size, maximum table size and maximum row size have not been indicated yet. The maximum number of columns in the row includes 32767, whereas the maximum blob/clob size is only 1GB compared with 2GB of the firebird software programme for relational database management system. However, the maximum CHAR size is measured with 1GB, and the maximum number size stands on 64 bits. Date and type functions are supported by the SQLite programme as well.
Despite its small footprints, the SQLite is faster that other programmes of this type which are commonly used by the database engines. All the information on limits of the programme can be found at the official website as well. The limits include the maximum length of a string or blob; as presented in the site, the “current implementation will only support a string or blob length up to 231-1 or 2147483647” (SQLite n.p.). Moreover, the information from every row is encoded into a single blob or a string; which means that the option of the maximum size of blob influences the number of bytes per row.
Though the maximum number of columns is 32767, the number of columns in applications is up to several dozen. The maximum number of tables in a join is 64 because the larger number of tables in a join is not supported by the application. The following database capabilities can be attributed to the SQLite: the union is supported by the SQLite as well as intersect, except, inner joins, inner selects, blobs and clobs. However, the common table expressions are not supported by the SQLite programme and the outer joins can be only left. The information on the merge joins and windowing functions is not indicated in the literary sources.
MySQL, a cross-platform application
The next programme for comparison in terms of usage in the relational database management systems is the MySQL. This software programme is developed and maintained by the MySQL AB which is a subsidiary of the Oracle Corporation which has been the company Sun Microsystems. The software licence includes GPL or Proprietary. This is the cross-platform software programme as well. So, the database is highly interoperable with other databases and can run on different operating systems. Thus, Windows, Linux, Mac OS, BSD, Amiga OS, Unix, and Symbian are the operating systems that are required fir normal operation and maintenance of the MySQL software programme which is used for relational database management systems.
The further advantages of the MySQL include the security measures that are incorporated into the operation of this software programme:
Starting from MySQL 5.0.2, MySQL gives warnings or errors if you try to insert an illegal date. By setting the SQL mode to the appropriate value, you can specify more exactly what kind of dates you want MySQL to support (Oracle n.p.).
As a rule, the programmes are easily cracked by efficient hackers or is available at official website of the corporation-developer. As you can see, this application can be only legally purchased and indicates all errors when trying to insert wrong date and time information.
The maximum file size should be considered from different points. “The maximum legal display width is 255. Display width is unrelated to the range of values a type can contain” (Oracle n.p.). thus, the maximum DB size is unlimited for the MySQL; the maximum table size can be described with the help of the following data: the storage limit is vary from 64TB to 256 TB. The maximum row size is 64 KB. The maximum number of columns in a row is limited to 4096, whereas in InnoBD it is eve smaller and makes only 1000. The maximum length of a blob is up to 4 GB which is the largest length from the models considered in the current report. The maximum CHAR size includes 64 KB of the information introduced in the text format. The limit of the number size allows only 64 bits of information. However, the minimum date value can be 1000, whereas the highest limit of the date value stands on the point of 9999. And the maximum column name size can be about 64 bytes.
Temporary tables are available for the MySQL software programme, whereas the materialised view can be received using triggers. The database capabilities include union which is supported by the MySQL, inner joins, outer joins, inner selects, and merge joins; blobs and clobs are supported by the current application as well. On the contrary, the application does not support the windowing functions, common table expressions, intersect, and except. The data types used by the MySQL include the range of different size requirements starting from 8-bit, including 16-bit, 24-bit, and 32-bit, and ending with 64-bit. Thus, the MySQL software application has a wide range of sizes available for operation.
Comparison and Contrast of the Presented Systems
The systems under consideration include the Firebird, SQLite, MySQL, and IBM DB2. All of them are used as applications for relational database management systems which are commonly presented as servers for work with clients. Thus, business area has been successfully connected with the information and communication technologies. The applications discussed have some features in common, though the differences are also detectable.
The first difference is the operating system on which the application can run on as different developers have different target audience, their requirements, namely the requirements for normal operation and maintenance of the applications are different. So, the IBM DB2 cannot run on BSD, Amiga OS, and Symbian operating systems. The Firebird is not operable on the Amiga OS and Symbian. And only SQLite and MySQL can run on all existing operating systems including those which are not appropriate for normal operation of the IBM DB2 and the firebird.
The fundamental features of the applications that are implemented natively do not differ much except for the MySQL which does not support the ACID. The file size matters greatly; though the SQLite is a small application, it has 1 GB limit of the blob size. In this respect, the application of MySQL can be considered the one with the largest limit of the blob size which influences other options and functions.
Temporary tables are available for all applications under consideration, whereas the materialised view is treated as a minor problem. Thus, IBM DB2 has the materialised view, the Firebird has only common views of the tables, the MySQL can produce the materialised view through triggers, and SQLite is not able to produce the materialised views at all. As you can see, only IBM DB2 can normally produce the materialised views in contrast with three other applications.
The database capabilities including the support of unions, intersections, exceptions, inner joins, outer joins, inner selects, merge joins, windowing functions by relational database management systems. With regard to these features, the IBM DB2 developed by the IBM Corporation can be considered the most unique and interoperable because it supports all these functions. The Firebird does not support the intersect, except, and windowing functions. The MySQL does not support event more functions than the Firebird (intersect, except, common table expressions, and windowing functions). The SQLite can be considered the one obtaining the second place after the IBM DB2 because it lacks only the function of supporting the common table expressions.
The data domain is another feature that can be compared regarding the software applications used in relational database management systems. Thus, the IBM DB2 and the Firebird have the data domain natively, whereas the other two applications do not have the data domain. Security features and functions, suchlike password complexity rules can be also regarded in the applications considered. Only the IBM DB2 has the password complexity rules, whereas the Firebird, MySQL, and SQLite such rules. Moreover, the applications except for the IBM DB2 have no security certification which makes these software programs vulnerable for breaches of confidentiality.
Summary of the Discussion
The main information about the applications used in the relational database management systems concerns the security measures, data types and other different features that can be essential while choosing the most appropriate application. The current research was based on the following information: database product name and company, the database interoperability with other databases, maximum file size, data type: small integer (16bit), integer (32bit), big integer (64bit), security features and functions, database functions: include in your comparison whether or not the RDBMS supports the following functions: unions, intersections, exceptions, inner joins, outer joins, inner selects, merge joins, and windowing functions.
The most unique and valid in terms of the relevance and appropriateness of all functions, the data types and size of the storage systems can be considered the IBM DB2 application developed by the IBM Corporation.
Adler, David W. “Proceedings of the 27th VLDB Conference.” IBM® DB2® Spatial Extender – Spatial data within the RDBMS. 2001: IBM Corporation.
Chamberlin, Don and Donald Dean Chamberlin. A complete guide to DB2 universal database. 2. San Francisco, CA: Morgan Kaufmann, 1998.
Digital Equipment Corporation. “Information Technology – Database Language SQL (Proposed revised text of DIS 9075)”. Digital Equipment Corporation. Maynard, Massachusetts, 1992. Web.
Firebird. Documentation. 2000-2010. Web.
IBM. IBM DB2 Software. 2009. Web.
Mullins, Craig. DB2 Developer’s Guide. 5. Upper Saddle River, NJ: Pearson Education, 2004.
Oracle. “Documentation. MySQL Manual.” MySQL. 2010. Web.
Preston, W. Curtis. Backup & recovery. Sebastopol, CA: O’Reilly Media, Inc., 2007.
RBDMS Online. RBDMS Data Models. 2010. Web.
SQLite. Official Website. N.d. Web.