This essay has been submitted by a student. This is not an example of the work written by our professional essay writers.
A database is a systematic collection of data. A Database Management System is required to store, access, delete, or otherwise organize data in a database.
MySQL is an open source DBMS. You can freely download, modify, and use open source software without having to pay any fees or royalty to the original author.
In this session, you will learn about MySQL, its features, and its advantages over other Relational Database Management Systems (RDBMS). An RDBMS is a database management system that is based on relational model as specified by E.F. Codd. In addition, you will learn about the advantages of MySQL as an open source database and using HyperText PreProcessor (PHP) with MySQL.
Overview of 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. This information can be stored in rows and columns in a table. A database can be created to store these tables. In addition, a DBMS can be used to manage the databases.
A DBMS can be defined as a software program that stores and manages databases. A database is a system used to store the data in a structured format. In other words, database can be defined as an organized collection of data. A DBMS is responsible for managing the various database operations such as adding, accessing, and processing of data. A DBMS helps you to manage data in two ways:
It provides an interface to manage data
It supports connectivity to other applications that can be used to manage data
Both, DBMS and RDBMS, perform the same task of storing and managing data. One of the key differences between DBMS and RDBMS is that RDBMS splits large data into smaller tables and establishes relationship between the tables. DBMS stores large amount of data in a single table. Also, the RDBMS is based on a relational model whereas DBMS is not.
MySQL is an open source RDBMS. MySQL uses the standardized Structured Query Language (SQL) to manage the database. MySQL 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 hence, MySQL is now owned by Oracle Corp.
Features of MySQL
MySQL was designed to achieve speed, robustness, and ease of use. The features of MySQL are as follows:
Is written in C and C++
Is tested with different compilers
Is compatible with multiple operating systems
Has support for multiple storage engines; both transactional and non-transactional
Has Application Programming Interfaces (APIs) for accessing MySQL databases available in many languages, including C, C++, Eiffel, Java, Perl, PHP, Python, Ruby, and Tcl
Is using multiple kernel threads or processing units, if available, for data processing
Is using multiple processors where available, boosting performance
Has ability to divert memory resources from inactive threads to active threads for faster processing
Has commands and features to retrieve, update, and delete data from several tables
Has support for compatibility to be used as a separate application or as an embedded library
Includes multiple column or data types including numeric, date and time, and string
Includes mapping of data types from other databases to MySQL data types
Includes fixed- and variable-length strings
Commands and Functions:
Has support for all MySQL operators and functions in the SELECT statement and the WHERE clause
Has support for tables from different databases in one statement
Has support for table and column aliases
Has support for displaying information about databases, tables, and indexes using the SHOW command
Has support for displaying query resolution information using the EXPLAIN command
Has full support for SQL GROUP BY and ORDER BY clauses, group functions and left and right outer joins
Has support for the use of function names as table or column name
Has support for in-built data encryption and decryption
Has support for user account privileges
Has support for password encryption
Scalability and Limits:
Handles large databases that have up to 5 billion rows
Allows up to 64 indexes per table
Allows up to 16 keys per table
Supports connectivity on any platform to MySQL server using Transmission Control Protocol/Internet Protocol (TCP/IP) sockets
Supports connectivity on Windows NT, 2000, XP, 2003, and Vista using named pipes or shared-memory connections
Supports connectivity on UNIX systems using UNIX domain socket files
Displays error messages in languages, such as Czech, French, German, Japanese, Korean, Norwegian, Polish, and Russian
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 a 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 MySQL Over Other RDBMS
There are many commercial DBMSes such as Oracle, Microsoft SQL Server, and Sybase available in the market. These DBMSes are robust, reliable, and support most of the features that a user wants. However, it is impossible for these databases to compete with MySQL with regards to price, as MySQL is available for free. In addition, for commercial DBMSes, the initial setup cost is more expensive, resource intensive, and time consuming, whereas with MySQL, this is not the case. This is one of the key advantages of MySQL.
Also, as the source for MySQL is fully available, you can customize MySQL as required. There are many troubleshooting techniques, command help, and syntax help that are available. This information is available in blogs, forums, and lists that do not require paid subscriptions. However, troubleshooting techniques, command help, and syntax help for commercial databases may require a paid subscription.
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 provides different versions that work on the different versions of Linux, UNIX, Microsoft, Windows, and other operating systems. MySQL also supports various built-in and third-party GUI tools for faster and easier design, implementation, and administration.
Following are the other advantages that MySQL offers over other RDBMS:
Reliable: Supports tables that can store and handle large number of records
Ease of Use: Provides a modular and flexible architecture that makes it easy to manage and customize.
Cross Platform Support: Supports different operating systems, such as Linux, UNIX, and Microsoft Windows.
Views: Supports views where data is copied into temporary or virtual tables during processing. This feature ensures data security.
Stored Procedures: Supports stored procedures and functions. This allows you to implement business logic at the database level.
Triggers: Supports triggers. This feature also enables you to implement business logic during data processing.
Comparing MySQL As An Open Source Database With Other RDBMSes
There are many open source and commercial databases available today. Some of the popular open source databases include mSQL, PostgreSQL, and InstantDB. As mentioned earlier, 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 that are present in 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. 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. Vaccuum procedure refers to the process of optimizing the data stored in the database. PostgreSQL withstands higher loads. The latest versions of PostgreSQL and MySQL support features, such 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 more user-friendly command interface so it is popular among Web developers. Also, MySQL supports more data types and functions as compared to mSQL.
InstantDB competes well with MySQL when you consider the different features. The only feature of MySQL that InstantDB is unable to compete with is performance. MySQL is faster as compared to InstantDB.
Advantages of PHP In MySQL Environment
A scripting tool enables you to control one or more applications when executed. PHP is a scripting tool designed for Web development. PHP supports embedding scripts into HTML code. Developers can use PHP scripts to create HTML Web pages that can read and write data from a database.
PHP is a scripting language that is executed at run-time. It enables interaction of the application with the database. You can use PHP and MySQL together to manage data on the Web. PHP is compatible with MySQL. You can also store and manage information from the database. Figure 1.1 displays the interaction between the client, server, and database:
Figure 1.1: Interaction between Client, Server, and the Database
Consider a database connected to a server as shown in figure 1.1. The server is connected to several clients. The database, server, and client maintain a two-way communication. This system appears to be simple; however, it has certain limitations. For example, when a client requests for data, the browser sends a request to the server. The server locates the data from the database, and returns it to the browser. If several clients make a request to the server for the same data, then the server will return the data to all the clients. This results in slow performance of the system.
The advantage of using PHP is that the database can be accessed directly through a Web page. In this scenario, the client will request for a PHP file. The PHP preprocessor will connect to the database, retrieve the data, convert the data into HTML format, and send it to the browser.
Following are some examples of real-world Websites where databases are used:
Online Ticket Reservation: In an online reservation system you can reserve a seat using the Internet. Your action updates the backend or the database of this booking system. You can access several parts of the database by changing the Uniform Resource Locator (URL).
Message Boards: On the Internet, message boards are widely present that run on MySQL and PHP. It is an online discussion site where messages can be posted. Message boards running on PHP and MySQL are more efficient because you need to update only one page and the changes are automatically reflected in others.
Marketing Websites: Consider that a large Website is required to be updated. The Website can be updated using few PHP scripts as the information related to these pages is stored in MySQL database. The PHP scripts accesses the MySQL database to obtain the information about the pages.
Advertising Banners: Consider a Website where several advertisement banners are present on the site. These banners are stored in a database on the server. You can call a PHP script to display each banner. In order to insert, modify, or delete a banner you have to access the database. A PHP script can be written to select the database from which the banner will be displayed. The PHP script would select and display the correct banners for the pages on the site.
To run a PHP script, you will need to install the following software:
PHP supports different operating systems such as Mac OS X, Linux, UNIX, and Windows.
Both PHP and MySQL are open source. This feature makes them cost effective as compared to other software products.
MySQL also supports the command line interface. This interface enables the PHP page to access the database and display the query results.
You can use PHP scripts to control the administration activities of the database. However, it is better to install a PHPMyAdmin on the server. PHPMyAdmin is an administrative interface for MySQL databases. It consists of a set of free scripts for administration of the database.
Open Source Software Licensing
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.
The advantage of using open-source software is that the developer can customize as per requirements. There is no limit on the customization. Also, there are various troubleshooting and performance tips freely available on the Internet for open-source software. However, open-source license does not allow you to sell customized software. In addition, you will have to provide the modified source code when you distribute the software.
However, open-source software must comply with certain conditions and some of them are given below:
The source code must be distributed along with the binary.
The software can be modified as required and redistributed under the same terms as the original software.
When the software is used as a part of development of other applications, the software must be redistributed with the application without any royalty or fee.
Note: For a detailed description of the open-source license terms and conditions, visit: http://www.opensource.org/docs/osd
Licensing of MySQL
MySQL is available under two licensing schemes. They are:
General Public License (GPL) - Applies to developers who use, and/or distribute open source software under the GPL.
Commercial License - Applies to developers who only use MySQL to develop their own executables and not the source code.
A database stores data in a structured format. A database management system is responsible to store, access, and delete data from a database.
MySQL is an open source RDBMS. It was developed and distributed by MySQL AB, which is now owned by Oracle.
There are many commercial RDBMSes available, such as Oracle, Microsoft SQL, and Sybase that support most of the data management features.
MySQL works on different operating systems, such as Mac OS X, Linux, UNIX, and Windows.
Unlike in traditional DBMSes that are proprietary, in open source software, you can modify the source code to customize the features.
PHP is a scripting language that enables interaction with a database. You can use PHP and MySQL to store and manage data on the Web.
In order to run a PHP script, you will need to install a Web server, PHP and MySQL.
Open-source software licenses allow you to read, access, change, and reuse the source code of a software product.
MySQL is available under General Public License and Commercial License.
Check Your Progress
The mysqlshow command displays information about the__________.
Databases and tables on the server
Tables and columns in a database
Columns and rows in a table
Version of MySQL Server
_____________is an administrative interface for MySQL databases.
To execute a PHP script, you will need to install
Web Server, PHP, MySQL
MySQL, Web Server
PHP, Web Server
MySQL allows up to _______ indexes per table.
______________ tool manages users of MySQL.
This page has been intentionally left blank