The Open Source Database Of Mysql Information Technology Essay

A database is a systematic collection of data. A database management system is needed to store, access, delete, or otherwise organize data in a database.

MySQL is an open source database management system.. You can freely download, modify, and use open source software without having to pay any fees/royalty to the original author.

In this chapter, you will learn about MySQL and its advantages over other relational database management systems (RDMS). In addition, you will learn about the advantages of MySQL as an open source database and of using PHP with MySQL.

What is MySQL?

Consider a library, which lends books to its members. Traditionally, the details of books, members, and lending are maintained manually using ledgers. as the number of books increases, managing and searching for books, members and lending details becomes difficult. To maintain and retrieve information fast, this information can be stored in an electronic database and a DBMS can be used to manage these electronic databases.

A DBMS manages the database. A database is used to store the data in a structured format. A DBMS is responsible for managing the operations such as adding, accessing, and processing data. A DBMS plays a central role in computing; as a standalone utility or as a part of applications.

MySQL is a relational database management system (RDBMS), which manages a relational database. A relational database stores information in several tables instead of storing it as a large table. This structure imparts flexibility to the database. MySQL uses the standardized Structured Query Language (SQL) to manage the database.

MySQL is a popular Open Source database. It is developed and distributed by MySQL AB, a company founded by the MySQL developers. In 2008, Sun Microsystems acquired MySQL AB. In 2010, Oracle acquired Sun Microsystems and so MySQL is now owned by Oracle Corp.

Main Features in MySQL

MySQL was designed to achieve goals such as speed, robustness, and ease of use. The features of MySQL are as follows:

Technical Features:

Written in C and C++

Tested with different compilers

Cross-platform compatibility, that is, works on multiple operating systems

Supports multiple storage engines; both transactional and non-transactional

APIs for accessing MySQL databases available in many languages, including C, C++, Eiffel, Java, Perl, PHP, Python, Ruby, and Tcl

Uses kernel threads to achieve complete multi-threading

Can use multiple CPUs where available, boosting performance

Dynamic memory cache and use of threads to allocate memory

Uses a single-sweep multi-join method to allow fast resolution of joins

Server can be used as a separate application or as an embedded library

Column Types:

Supports multiple column or data types including Numeric, Date and Time, and String

Provides for mapping data types from other databases to MySQL data types

Supports fixed- and variable-length strings

Commands and Functions:

Supports the use of all MySQL operators and functions in the SELECT statement and the WHERE clause

Supports the use of tables from different databases in one statement

Supports table and column aliases

Displays information about databases, tables, and indexes using the SHOW command

Displays query resolution information using the EXPLAIN command

Provides full support for SQL GROUP BY and ORDER BY clauses and group functions as well as left and right outer joins

Supports the use of function names as table or column name

Security:

Support for in-built data encryption and decryption

Privileges can be assigned to users

Password encryption

Scalability and Limits:

Handles large databases sizes

Allows up to 64 indexes per table

Allows up to 16 keys per table

Connectivity:

On any platform, clients may connect to the MySQL server using TCP/IP sockets

On Windows NT, 2000, XP, 2003 and Vista, clients may connect to the MySQL server using named pipes or shared-memory connections

On UNIX systems, clients may connect using UNIX domain socket files

Localization:

Displays error message in 20 languages including Czech, French, German, Japanese, Korean, Norwegian, Polish, and Russians

Supports Unicode and various character sets

Allows data to be stored, sorted and compared using the chosen character set

Clients and tools:

Provides built in support to check, optimize, and repair tables

Provides “mysql” tool to execute individual SQL commands or SQL commands stored in a file

Provides “mysqlaccess” tool to check host, user, and database privileges

Provides “mysqladmin” tool to manage the database server

Provides “mysqldump” tool to backup the contents of one ore more MySQL databases to a file

Provides “mysqlhotcopy” tool to backup a single database or table on to the same computer

Provides “mysqlimport” tool to import data into MySQL table from a file

Provides “mysqlshow” tool to display information about the databases, tables, and columns

Provides “mysqld_safe” tool that enables safe start up of the MySQL server

Advantages of using MySQL over the other RDBMS

There are many commercial DBMSessuch as Oracle, Microsoft SQL Server, and Sybase available in the market. These DBMSes are robust and reliable, and support most of the features a user wants.

It is impossible for these databases to compete MySQL on price, as MySQL is available for free downloads. In addition, for commercial DBMSes, the initial setup is more expensive, resource intensive, and time consuming.

Another factor that adds to the cost is the training required on commercial databases. For MySQL, there are many free training courses available on the Internet. Also, you can download MySQL for free and learn to use it.

As the source is available, you can customize MySQL as required. The vast support networks available (as blogs, forums, and lists) enables you to access information and support easily as opposed to the paid support structures of commercial DBMSes.

Typically, open source software tends to be updated more frequently than commercial software because many users contribute to its development. As a result, new features are available more often than for commercial databases.

MySQL has different versions available that work on the various versions of Linux, UNIX, Microsoft Windows, and other operating systems. MySQL also offers various in-built and third-party GUI tools for faster and easier design, implementation, and administration.

Following are the advantages that MySQL offers over other RDMS:

Reliable: Different users have found MySQL even under very heavy loads and large database sizes.

Ease of Use: MySQL has a modular and flexible architecture that makes it easy to manage and customize.

Cross Platform Support: MySQL is available on more than twenty different operating systems including Linux, UNIX, and Microsoft Windows.

Contrasting MySQL as an open source database with other RDBMS

There are many open source and commercial databases available. Some of the popular open source databases include mSQL, PostgreSQL, and InstantDB. Similarly, Oracle, MS SQL Server, and Sybase are some popular commercial databases.

The early editions of MySQL did not support all the SQL features. For example, transaction support and stored procedures were not available in the older versions of MySQL. The latest versions of MySQL, however, provide full SQL and transaction support.

The commercial databases support almost all the features as MySQL, but the performance of MySQL is better.

One drawback in MySQL is that it does not support advanced SQL3 features such as object oriented data types. PostgresSQL supports advanced SQL3 features and is a better choice as an open source DBMS with SQL3 features. However, PostgresSQL has a major disadvantage in its hidden limit of 8k of data per row.

PostgreSQL is more powerful but MySQL is faster. MySQL does not need a vacuum procedure as PostgreSQL. PostgreSQL withstands higher loads. The latest versions of PostgreSQL and MySQL support features as sub-selects, stored procedures, triggers, unions, and views. However, older versions of MySQL did not support all these features. In addition, because these features are new to MySQL, there are some performance issues.

However, MySQL provides with more user-friendly command interface so it is it popular among web developers.

The databases such as mSQL cannot compete with MySQL on many fronts. For example, MySQL has a richer feature set of SQL than mSQL. MySQL also supports more data types and functions as compared to mSQL.

InstantDB competes well with MySQL when we consider the different features. The only feature of MySQL that InstantDB is unable to compete is performance. MySQL is faster as compared to InstantDB.

Advantages of PHP in MySQL environment

PHP is a simple tool designed for web development. It is easy to learn and allows a developer to implement various web-programming tasks, including database-related routines. It offers good flexibility and performance.

PHP is a scripting language that is executed at run-time. It enables a user to interact with databases. PHP and MySQL are used together to store data on the Web. PHP is compatible with MySQL, which is one of the most popular database engines. MySQL is free and provides with high performance.

Using databases allows a store of information that is accessed by users. In addition to this, a user is able to store his information in the database. Following diagram will help us to understand the interaction between the client-server-database.

Figure 1.1 Interaction between Client, Server, and the Database

In the above figure, a server is connected to several clients and a database connected with the server. These components have two-way interactions between them. When a client requests for a HTML file, the browser sends a request to the server. The server will find the requested file and sends the HTML file to the browser. The working of the system above is simple. However, such a system has limitations. If several users require the same file then the file is sent to all the clients who requested it as a static page and HMTL is not able to interact with the server. The main advantage of using PHP is that a Web page is able to access a database.

Alternatively a client will request for a PHP file. The PHP preprocessor will then convert the file to HTML, which is sent to the user.

Examples where databases are used on a website are listed below:

Sites that work on database: We are quite familiar with the online reservation system in which the user is able to book a seat using the Internet. The backend database of this system gets updated depending on the action of the user. Different parts of the database can be accessed by changing the URL, which is the uniform resource locator.

Message Boards: Several message boards on the Internet are run using MySQL and PHP. Such systems are more efficient than those using other utilities. Other systems create a page for each message and provide various options. While updating such system would need to change all the pages. A system using MySQL and PHP would just require changing one page.

Updating Websites: Consider that a large website is to be updated. A user is able to modify the whole website using few PHP scripts. The information related to these pages is stored in MySQl database. The PHP scripts would access a MySQL database to access the information about the pages. For updating the website, the user would need to just change one page.

Banner Rotation: We have several banners on a site. A PHP script is called for each banner. A database of banners is opened and a random banner is displayed. In order to insert, modify or delete a banner we just have to change the database. The PHP script would pick the correct banners for the pages on the site.

To run a PHP script a user will need the following:

Web server

PHP needs to be installed

MySQL needs to be installed

PHP is compatible with almost all the operating systems.

PHP and MySQL both are open source. It is convenient to obtain these for free downloads from the Internet. This feature makes it cost effective as compared with others.

If we compare PHP and MySQL against ASP, the code for searching a database in ASP has a line at the top which states the number of matches. In order to get this number one needs to cycle through the records once to count them, as the RecordCount property keeps returning -1. The equivalent MySQL function mysql_num_rows worked first time as advertised, shortening the code, and the time taken to run the server-side page.

MySQL provides with command-line function, which allows access to the database so that user is able to view the query results which the PHP page acquires.

PHP scripts can also be used to control all the database administrative options but it is better to install a PHPMyAdmin on the server. PHPMyAdmin is an administrative interface for MySQL databases. It consists of set of free scripts to provide administrative interface.

Licensing issues related to open source softwares

There are many types of software licenses available. An open-source software license permits users to read, access, change, and reuse the source code of a software product. Open-source software does not necessarily mean free software. Open source commonly refers to software that is made available to everyone at no cost under a license agreement having a few restrictions.

The distribution of open-source software must fulfill with the following criteria:

Free Distribution – The license allows you to sell the software or distribute it for free as a part of your software distribution. You are not required to pay any fee or rayalty against such a distribution.

Source Code – The source code of the product is made available along the product. If the source code is not distributed with the software, then it must be made available free or at a reasonable cost. In addition, the source code must be in a form that a user is able to modify it.

Derived Works – The license must allow modifications to the software. Also after the modification, it must allow the redistribution under the same terms as the license of the original software.

No Discrimination against Persons or Groups – The license must be applied same for everybody

No Discrimination against Fields of Endeavor – It means that the license should not restrict the software in a specific field of endeavor.

Distribution of License – The rights attached to the program must apply to all to whom the program is redistributed without the need for execution of an additional license by those parties.

License must not be Specific to a product -The rights attached to the program must not depend on the program’s being part of a particular software distribution. If the program is extracted from that distribution and used or distributed within the terms of the program’s license, all parties to whom the program is redistributed should have the same rights as those that are granted in conjunction with the original software distribution.

License must not contaminate Other Software – The license must not place restrictions on other software that is distributed along with the licensed software

Integrity of the Author’s Source Code – The license may restrict source-code from being distributed in modified form only if the license allows the distribution of “patch files” with the source code for the purpose of modifying the program at build time. The license must explicitly permit distribution of software built from modified source code. The license may require derived works to carry a different name or version number from the original software.

Open Source Software Benefits

The open source software are freely available on the Internet for free downloads. Open-source software has become popular due to several reasons. Following are the benefits of Open-Source Software:

Access to Source Code- Enables user to understand the software at a deep level. It also enables to debug the software.

Large Community- Creates an opportunity for widespread and rapid teamwork on development projects.

Low Cost- Available for free downloads from on the Internet.

Broad Rights- Enables to grant licenses to use, modify and redistribute the open source software, which is a major advantage. The commercial software products are distributed only in binary form and may not be modified.

Licensing of MySQL

MySQL software is released under the General Public License (GPL). It may often be used for free, but users may require buying commercial licenses from MySQL AB for certain purpose. Licensing options are as follows:

Commercial License- Suppose an organization wants software to be accessed by the customers or the members in the organization. In this case the commercial license is needed. This is useful when an organization wants the source code to be secret. Such organizations do not want to act in accordance with the General Public License. The commercial license is an agreement with MySQL AB for organizations that do not want to release their application source code. Commercial licensed users get a commercially supported product from with assurances from MySQL. Commercially licensed users are also free from the requirement of making their own application open source.

Open Source License- Enables users to offer the features added to everyone. Anyone is able to use, modify or distribute the software. The open license is free of cost. The condition is that the user must make the complete source code available.

Summary

MySQL is the most popular Open Source SQL database. It is developed and distributed by MySQL AB, which is a company, founded by the MySQL developers.

The MySQL is a database management system which manages the database. A database is used to store the data in a structured format. A database management system is responsible to manage the operations such as adding, accessing, and processing data.

MySQL is a relational database management system, which stores information in several files instead of storing it as a large table. This structure imparts flexibility to the database.

MySQL is Open Source software that enables any one to use and modify the source code.

We have many commercial databases available in the market such as Oracle, Microsoft, and Sybase. These commercial databases support most of the features a user wants. These databases fail to compete with MySQL for read-heavy-database applications.

A user is able to modify the source code due to which new features become easily available as compared to the traditional databases.

An open-source database is cheap. It is free if you choose the open source license.

MySQL works on different operating systems such as Linux, UNIX, and Windows.

PHP is a scripting language. It enables a user to interact with the databases. PHP and MySQL are used together to store data on the Web.

In order to run a PHP we need a Web sever. Also PHP and MySQL need to be installed.

An open-source software license permits users to read, access, change, and reuse the source code of a software product.

Check Your Progress

The mysqlshow tool that displays information about the__________.

MySQL server and the information about the databases and tables on the server

MySQL client and the information about the databases and tables of the client

All the databases of the client and the server

_____________is an administrative interface for MySQL databases.

mysqladmin

PHPMyAdmin

admin

MySQL software is released under the_________.

Commercial source License

Open source License

General Public License

MySQL allows up to _______ indexes per table.

16

32

8

______________ tool manages users of MySQL.

mysqladmin

mysqlaccess

mysql

mysqlshow

This page has been intentionally left blank