Database Management Systems - MySQL and Oracle - Case Study Example

Database Management Systems Introduction The se and need for data has in the recent past become a vital part of any organization. Organisations are increasingly demanding to track their activities for which they need the data. Data that often ranges from their profits, sales, income, inventory, customers’ demographics, number of customers etc. as the amount of data increases, there is the need to have a storage for which the organization can sufficiently rely and use. Therefore the use of a database management system (a software tool that allows aids in the organization of data into databases) has been introduced. The database management systems (DBMs) are primarily useful in the storage and construction of useful information for users. There exists two ways in which this data can be modelled: logic or physical, both of which seek to produce information in line with the organisations requirements. The logical modeling of data collects business information that is in turn made into a model through the business process diagrams, flowcharts and adapts flow diagrams. Physical modeling on the other hand, is the coming up of the design following the information obtained from the logical models. I particular the physical modelling converts the logical model data into relational data through, in most cases, software. This can be done using the products: Oracle DB2, IBM SQL or Microsoft Access. The use of either model depends on the requirements of the organization and their financial capability. This report will analyze both models, the products in use, their advantages and the disadvantages of the use of these products to any organization. Relational Data Model The data model is based on a model developed in 1970 by Edgar Codd, who insisted that data should be presented in a way that shows relationships with each other and that the data should be able to changed or manipulated: which has since developed due to the advance nature of technology. Out of this technology, such products as Oracle DB, Microsoft SQL, MySQL and Microsoft Access have been developed. The top 3 leading market players include Oracle (48% market share), IBM (20%), Microsoft (17%) according to the revenue levels as determined by Gartner in 2011 (Omics International Research n.d). the three companies provide licensing to their products chargeable at various rates though there are also available the open source versions of their products. The most popular database products include: oracle database, Microsoft SQL Server, MySQL and IBM DB2, in order of popularity (Omics International Research n.d) MySQL The MySQL database management system, developed by Oracle, is an open source that allows users access to the various data kept in its database. It uses the relational data modelling in its structure. It has by far been ranked the best DBMs because of it is considered faster, cheap (it is a freeware) and reliable (TechTarget n.d). this property of the software is owed largely to the features that it presents: the use of the SQL( structured query language) language that allows users to query and the database is a client/server system, that is man users can access the database on the same server(usual connected through a local area network (Bassil 2012). MySQL is also preferred because of its ability to process Select query. In a study to compare the efficiency of SQL server and MySQL servers, Saikia et al (2015 pg. 164) found that MySQL has twice the performance that SQL had. Other interesting features of MySQL include full text search making search query much easier, allows for the user to copy the data to other computers, and executes multiple functions or commands (TechTarget n.d). The server also supports various types of data and allows for data storage for both fixed and variable length (Saikia et al 2015 pg. 160). The MySQL is what is used for the databases for some of the very large companies: Wikipedia, Google and Facebook. The server is also popular because it is easy to get started with it though it does not offer the full standard SQL (Digital Ocean 2014) as that provided by SQL server. To support the functionality of the system, the server is compatible with other sets of tools that assist in its operation. (Digital Ocean 2014) The server also supports various kinds of data from TINYINT to BIGINT, dates, and floats (Digital Ocean 2014) SQL server The SQL server was developed by Microsoft, in 1989, initially for the small and medium enterprises. Microsoft has since launched SQL 2000, 2005, 2008 versions and currently the 2016 version (Mullins S n.d). Unlike MySQL, SQL is not an open source but it Microsoft has a flexible package depending on the needs of the client which include: enterprise for large business; standard for the small and medium enterprises; workgroup that is designed for relatively small organisations; and express which is the open source version of SQL. SQL server is considered to be among the top three relational DBMs, ranking against IBM DB2 and oracle. The server is only compatible with Windows (Mullins n.da) but it allows for the migration of data from its server to other servers (Saikia et al 2015 pg. 160). SQL also allow for users to replicate data, and long memory for procedures (Saikia et al 2016 pg. 160). The 2016 version has new features that make the server much more popular: cloud integration; back up services on Azure; better security through strong encryption; stretch database that allow access to data remotely; data masking to ensure data safety form unauthorized third parties; temporal data support (Mullins S n.d a); in database analytics that allow one to analyze the data within the server (Microsoft 2016) In a study to compare the performance of SQL Server and MySQL (Saikia et al 2016) it was found that SQL Server ranked high than MySQL server: offered a higher response time. Oracle Database Developed by the industry leader, Oracle DB was created in 1977 by Ellison L and his team. The framework of the server is that the various databases can be accessed by the users through the SQL language structure. Oracle can basically be provided in either the enterprise package, standard package, the express package that is open source or the elite package for mobile devices. Oracle DB can be used on the various platforms, windows, UNIX or Linux which makes it a suitable product for those that have the different OS. The most outstanding characteristic about Oracle is the fact that it can incorporate both the logical and physical models in its architecture (Bassil 2012): data stored in the server are both in tables and data files. Currently Oracle DB has the 12c version which comes with features that certainly assert its popularity. The 12c version has an optimized standard language that eases the data search, data clustering characteristic, an improved JavaScript Object Notation (JSON) support (Oracle Help Center n.d). Moreover Oracle 12c has a multitenancy feature that reduces the use of transportable tablespace, one which the other servers do not currently have. The feature comes with the container database and pluggable database concepts both which allow the server to clone data or create these cones remotely (Oracle Base n.d). The user can be able to insert and remove databases from one source to another. IBM DB2 This relational database management system product is an invention of IBM since it early work in 1983. Currently IBM has released, April 2016, a new version of the software: Data server 11.1. IBM pries itself in offering flexible, reliable, high performance and better scalability database products (IBM Analytics). The performance of the product is anchored in its ability to provide both full standards SQL capabilities and the NoSQL capabilities and supports the JavaScript Object Notation technology. The DB2 can be used on various platforms such as windows, Linux and Unix (LUW); on iSeries and for z/OS. The current version of DB2 offers various packages for its clients: Advanced enterprise server with no memory limits; advanced Workgroup Server Edition especially for the mid-size organisations; enterprise server edition with no limits on the memory and database; workgroup server edition; Direct advanced edition with the same features as advanced workgroup server edition; direct standard edition; DB2 for big data; DB2 developer edition (; DB2 Express-C open source database software. DB2 for Linux, Unix and Windows is the most widely used package because of it salient features: automates tasks, BLU acceleration feature, high availability, use of the SQL language, optimal performance, pure Scale feature and time travel query feature that allows one to access data in their different forma ta different times they were entered (IBM DB2 features and benefits n.d) The DB2 11 is seemingly popular owing to its advanced features apart from the basic SQL compatibility and optimal storage: pure Scale and IBM BLU Acceleration (IBM Analytics). The IBM BLU Acceleration feature increases the performance of the product through storage in column and compressing the data (Mullins consulting n.d b). The pure Scale feature supports High Availability Disaster Recovery in case of site failures where data can be replicated from one cluster to another. The feature allows for the online update which eases the work of programmers. The DB2 11 also provides security measures to the users by the use through enterprise key management and advanced recovery for backing up data. Furthermore, Bassil (2012) in his comparison study of the top DBMs systems found that IBM DB2 was has the fastest response to queries but with the highest memory usage. Logical Data Modelling The logical data modeling is specifically meant to achieve the need of then business rather than the database according to Stephens and Plew (2001), logical modeling involves the collection of data pertaining to the process, sections and the organizational units of the business. Principally, this form of modeling is referred to as logical as it is created independent of technology. The basic characteristics of the logical data model are: designed for a particular project, independent of technology, assigned precisions to its datatypes and may be used in relation to other data models, such as input for the relational databases. Information gathered is usually represented in diagrams or a document (Stephens and Plew 2001) before it is translated into the physical model form of the data. Some of the popular logical data model products include entity Relationship diagrams, Business process diagrams. There are other DBMs products that are based on the traditional models: usually the hierarchical model or the network logical model. Under hierarchical model there the products that apply this model include IBM Information Management System and the mobile based RDM. Under this model data is represented as a tree where each piece of data can be traced to a root source/parent (omics international research network model n.d). On the other hand network model, each data can have multiple parent/sources. Based on this technology, there has been developed various DBMs such as integrated data store, integrated database management system, RDM Embedded, RDM server. Hierarchical model database systems Integrated management system applies the hierarchal model in the presentation of data in the form of fields of several segments of data. The database has since been developed and advanced from the time it was first used in 1966. In 2009, IBM released the latest version of the model that allows for content dictionary entries, and fast path buffers which increases its scalability (in terms of storage) and usability (Crawford n.d). Another notable application of the hierarchical model is the RDM mobile developed by Raima. The current version of RDM supports both the SQL and NoSQL language. The system has the capability of encryption, replicating of data and the mirroring of databases (Omics international research: RDM). The flexibility of its use is advanced by the fact that the system can be used on the Linux and Windows platforms, android and well as iOS Network model database systems The network data model has formed the basis of Raima’s technologies database product: the RDM embedded. The product allows for data to be accessed by the user using indexes (finding by using keys), sets (where the user go through the connections in the sets) and sequential (through identifying the position of the data as either first last etc.) (Raima Data base concepts n.d). The RDM embedded most known feature in-memory database. The Integrated database management system (IDMS) was initially based on the network model before it was scaled up to the relational model. Strengths and Weaknesses Logical and other mainframe data models The logical data models forms of DBMs are the mostly the crudest possible way of data presentation. DBMS based on this model are usually very easy to understand by the user as they pertain to a single project, in most cases. As in the case of hierarchical modelling, all data are listed clearly in how they are related sing the tree that identifies the ‘parent’ and ‘child’. The ease of use also applies to the user as they do not pose a lot of difficulty in their construction. Usually, the logical model is applied first in the construction of databases because of its ability to capture information in the various subject, that is, data can be represented in different ways but still achieve the same result when compiled. The traditional methods have since been phased out or adapted into the new technology mainly out of the need to address the challenges they pose in their use. DBMS based on the traditional methods usually does not allow ease of data synchronization: it is not easy to manipulate data once it has been entered. The modification of the data is also difficult given the fact that it could affect certain clusters of the data and it may require that the whole data be changed. The use of hierarchical models also becomes a challenge when the set of data is too large thus implementation challenges and complexity in its applications. It also becomes difficult to manage the database when data is too large. Relational Database system The relational database is the combination of both the hierarchical model and the network logical model that is considered to have addressed the challenges posed by the earlier version of the DBMs. Relational database apply SQL language that is easy to use and understand. Furthermore the query language allows the database to have high processing speeds. The incorporation of advanced technology into the system provides security thus promotes reliability and concurrent use access to the data. Most advanced products have high availability and data recovery that acts as a buffer should data be lost in the process. The advantages that are experienced using the physical model based DBMs are not without a cost: they are expensive to acquire, maintain and license. The software also takes up a large portion of the memory than what normal file storage hence the need for computers with high storage space and processing power. The data stored in relational database can prove to be too complex sometimes for the end user especially is the data is too large. References Bassil Y 2012 A Comparative Study on the Performance of the Top DBMS Systems Journal of Computer Science & Research (JCSCR) Vol. 1, No. 1, Pages. 20-31, February 2012 Crawford R IMS version 11: Reviewing features and functions of IBM’s new release [online] available from: searchdatacenter.techtarget.com/tip/IMS-version-11-Reviewing-feature-and-functions-of-IBM-new-release Digital Ocean 2014 SQLite vs MySQL vs PostgreSQL: A Comparison of Relational Database Management Systems [online] available from: https://www.digitalocean.com/community/tutorials/sqlite-vs-mysql-vspostgresql-a-comparison-of-relational-database-managemnt-systems IBM Analytics IBM DB2 [online] available from: https://www.ibm.com.analytics/us/en/technology/db2/ IBM DB2 features and benefits DB2 for Linux, UNIX and windows [online] available from: https://www.ibm.com/software/au/data/db2/linux-unix-windows/features.html Mullins S C Microsoft SQL Server 2016 relational DBMS overview [online] available from: searchdatamanagement.techtarget.com/feature/Microsoft-SQL-Server-2016-relational-DBMS-overview Mullins S C IBM DB2 relational DBMS overview [online] available from: serachdatamanagement.techtarget.com/feature/IBM-DB2-relational-DBMS-overview IBM Overview DB2 for Linux, UNIX and Windows [online] available from: https://www.ibm.com/software/au/data/db2/linux-unix-windows/features.html Saikia A, Joy S, Dolma D and Mary R 2015 International Journal of Advanced Research in Computer and Communication Engineering Vol. 4, Issue 3, March 2015 TechTarget Database Management Features of MySQL [online] available from: searchitcahnnel.techtarget.com/feature/What-are-the-top-MySQL-features-What-is-MySQL Microsoft SQL Server 2016 [online] available from: https://www.microsoft.com/en-cy/sql-server/sql-server-2016 Omics International: RDM [online] available from: research.omicsgroup.org/index.php/RDM_Mobile Omics International Research: Network Model [online] available from research.omicsgroup.org/index.php/Network database Oracle Help Center DATABASE New features Guide [online] available from: https://docs.oracle.com/database/121/NEWFT/chapter12102.htm Oracle Base Multitenant: Overview of Container Database and Pluggable Databases Raima Database concepts [online] available from: docs.raima.com/rdme/9_1/Content/UG/Chapter2.htm Stephens R K and Plew R 2001 logical versus physical database modelling [online] available from: www.developer.com/article.php/641521/Logical-Versus-Physical-Database-Modelling.htm TechTarget MySQL [online] available from: searchitchannel.techtarget.com/feature/What-are-the-top-MySQL-features-What-is-MySQL Read More