+ Classification of Database Management Systems
–Oracle
— Data Model
Oracle DBMS is world renowned as a relational database management system facilitating the relational model of data. The relational model was invented by E.F. (Ted) Codd as a general model of data. There are 12 rules sated by Codd, which needs to be adhered with, so that a database can be declared as a Relational model database. Oracle does adhere to most of the rules but provides the user the ability to violate the rules in order to extend the capabilities. Newer distributions of Oracle (10g and 11g) are capable of catering data requests relating other data models also i.e. RDF Data Model, Spatial Network Data Model. The necessary data transformations are performed by Data Interfacing Layers mostly written using Java. Recent ratings have claimed that Oracle is the number one Relational database provider.
—Number of Users
Oracle has a customer base of 250,000 customers, highest market share of 47.1% that is 7168 million USD in 2006; most of the customers are corporate organizations, which fulfill their data requirements using Oracle.
—Cost
Oracle has several database server configurations, each having different levels of functionalities and accordingly different levels of pricing. Oracle Express Edition is available freely but is limited to 4 GB of user data, 1 GB of RAM and single processor utilization. Standard ONE, Standard, and Enterprise are the other versions, which are priced according to the level of performance they offer, e.g. memory utilization, number of parallel processors etc.
—Number of Sites of distribution
Oracle can be considered as the number one enterprise preferred database server provider. Oracle is widely distributed in the high range and mid-range database market on UNIX and Linux platforms, Oracle has a fair stake on the windows based database servers market also.
–Microsoft SQL Server
— Data Model
Microsoft SQL Server is also a Relational model database management system. Like Oracle, Microsoft SQL Server also facilitates the usage of multiple data models in the recent versions. SQL Server 2008 supports the ADO.NET Entity Framework and the reporting tools, replication, and data definition will be built around the Entity Data Model. In 2008 Microsoft introduced LINQ to Relational Data which is an object-relational mapping (ORM) framework that allows the direct 1-1 mapping of a Microsoft SQL Server database to .NET classes, and query of the resulting objects using LINQ
—Number of Users
Microsoft SQL had a market share of 2654.4 million USD by the end of 2006, that’s a 15.6% of the total market share in 2006.
—Cost
Microsoft SQL Server 2008 has the option to be purchased under a server operating system license with incremental Client Access Licenses (CALs), or a per-processor license model. Express Edition, Compact Edition & Evaluation edition are the free versions. A free edition of Microsoft SQL Server is ideal for learning and building desktop and small server applications. Non fee editions are priced according to the functionality and the level of performance provided.
—Number of Sites of distribution
Microsoft SQL Server dominates the high range and mid-range database market on Microsoft Windows platforms. Microsoft SQL server has no stake on the Linux / UNIX market share because it is a Windows only version.
–My SQL
— Data Model
Like Oracle and MS SQL Server, MySQL is also a Relational model database management system. When we consider presentation and accessibility of data with other data models, MySQL is with poor ranking compared to Oracle and MS SQL Server i.e. When we consider Network model interfacing, both Oracle and MS SQL Server have recursive operators for processing recursive sets, though they all work a little differently. MySQL has no such special tools, though there is a graph engine under development for MySQL
—Number of Users
With over 65,000 downloads per day, MySQL has the highest adoption and growth. MySQL has gained 25% market share in overall database usage by developers in the past two years. MySQL continues to have the largest mindshare in the open source database market and has the highest number of paying customers for product support: an estimated 16,000.
—Cost
MySQL is available free of cost. MySQL is an “Open Source” database. MySQL is part of LAMP (Linux, Apache, MySQL, PHP / Perl / Python) environment, a fast growing open source enterprise software stack. More and more companies are using LAMP as an alternative to expensive proprietary software stacks because of its lower cost, reliability, and documentation.
—Number of Sites of distribution
MySQL is the world’s most popular open source database. Broad acceptance of MySQL can be attributed to the rapid overall adoption of the L/WAMP stack. Because of its popularity, MySQL has the support of leading IT organizations including major hardware and software vendors
+ Introduction to Oracle DBMS
–Sharing Data
Oracle provides several methods which can be used to share data among other DBMS. Additionally these methods provide a consistent way to perform logical database backup and recovery. Of these, the common utilities used are the import and export tools. Mostly they are used to move schema definitions and data between different Oracle instances and users. While still supported on 10g the original import (imp) and export (exp) utilities were replaced in 10g with the faster and more advanced Data Pump-based, impdp and expdp.
–Minimizing Redundancies and Inconsistency
As relational database Oracle provides the facilities to reduce redundancies and inconsistencies. Minimizing redundancy requires complex queries and schema structures, which in turn requires a high performance database system which is capable is fulfilling complex requests. Oracles highly scalable architecture and high performance therefore provides the ability to reduce redundancies and inconsistencies. Also the newest Oracle release Oracle Database 11g Release 2 contains Automatic Storage Management, Oracle Real Application Clusters, and Active Data Guard allow storage and server resources to be used for both fault tolerance and running production workloads, keeping servers and storage from sitting idle waiting for failures, thus reducing inconsistencies and redundancy.
–Support for Transactions
Oracle DBMS provides the support transactions. Oracle transactions act according to definitions defined by the SQL standard. A transaction is a sequence of SQL statements that Oracle treats as a single unit of work, every SQL DML (Data Manipulation Language) statement issued subsequently becomes a part of this transaction. When disconnecting from the database the transaction ends as well as when issued with a COMMIT or ROLLBACK command.
–Integrity
Oracle provides a number of integrity constraints and database triggers to facilitate the management of business rules. Referential integrity (foreign key constraint) is one way Oracle provides for maintaining business rules. Relational systems allow control of business rules with constraints, and referential integrity rules form the backbone of relational tables. Several types of constraints can be applied to Oracle tables to enforce data integrity, i.e. Check Constraint: validates incoming columns at row insert time, Not Null Constraint: used to specify that a column may never contain a NULL value, Primary Key Constraint: used to identify the primary key for a table, References Constraint: used to ensure that a row is not deleted, if rows still exist in a dependent table, Unique Constraint: used to ensure that all column values within a table does not contain duplicate entries.
–Security
Oracle database has many complex security measures which make it an ideal database system for enforcing tight security standards. Data confidentiality, integrity, and availability can all be well protected with properly designed Oracle database. Oracle allows for various types of authentication. Oracle-based authentication allows for Oracle database accounts with user-ids and strong password management. Oracle passwords are encrypted with a modified DES algorithm for each database connection. Oracle passwords are stored in an encrypted format in the data dictionary. All passwords are encrypted, including user passwords whether across the network or local connections, server to server passwords, and even database administrator passwords when the database is down. Oracle also supports host-based authentication which is based on the operating system’s user accounts which are then passed on to Oracle. Additional authentication options are available for those that choose the Oracle Advanced Security Option Oracle makes use of profiles to allow the administrator to place specific restrictions and controls on a number of system resources, password use and various Oracle products. These profiles can be defined, named, and then assigned to specific users or groups of users. By default, new Oracle users are not given any privileges. New users must be given privileges before they can logon or execute any database operation. Users can not do anything unless they have been given the specific privilege to do so. There are an impressive number of privileges that can be given, around 100 in all. There are two types of privileges available to be granted to users. They are system and object privileges. Roles are used to ease the management task of assigning a multitude of privileges to users. Roles are first created and then given sets of privileges that can be assigned to users and other roles. Users can be given multiple roles. It is much easier to create sub-sets of privileges that are organized into roles and then assign the role to one or more users. Roles can be protected with passwords. Roles that are protected with passwords require that a password be provided before activating a role unless it is the user’s default role. There are three standard types of auditing available in Oracle, including SQL statement-level, privilege-level, and object-level auditing. Audit records can be written to the standard Oracle audit table, to an operating system audit trail (dependent on operating system used), or to an external file.
–Standards
Oracle conforms to Entry level conformance defined in the ANSI document, X3.135-1992, “Database Language SQL.” Oracle supports the ASCII character set (FIPS PUB 1-2). Oracle supports both single-byte and multibyte character sets. Also Oracle provides extend functionality, which are not defined in standards, but provide valuable functionality.