Introduction to Databases

Today, Vikram shows us the basics of a database and are introduced to concepts like Database Management Systems and Relational Database Management Systems. We are also given a thorough overview of MySQL and its features. This excerpt comes from chapter one of MySQL: The Complete Reference, by Vikram Vaswani (McGraw-Hill/Osborne, ISBN 0-07-222477-0, 2004).


MySQLWhether you love ’em or hate ’em, in today’s wired world, it’s no longer possible to live without databases. No more the sole province of bespectacled geeks in tiny back-office cubbyholes, databases have hit the mainstream in recent years, finding applications in disciplines ranging from biotechnology to electronic commerce. You’ll find them in your bank, your local library, your web browser—heck, even your neighborhood grocery store uses one. They’re everywhere, and they keep getting smarter.

As the open-source movement has gathered momentum during the past few years, more and more attention has become focused on a little-known database system called MySQL. This database system is available free of charge over the Internet, provides a wealth of features at minimal performance loss, and has been battle-tested for reliability and speed in a variety of applications and situations. Today, with more than 4 million users, it is rapidly becoming a standard for database administrators and users worldwide.

Throughout the course of this introductory chapter, I’m going to take a closer look at MySQL, explaining what it is, how it came to be, and why its feature set makes it such a compelling alternative to its better-known, better-funded commercial counterparts.

Remember: this is chapter one of MySQL: The Complete Reference, by Vikram Vaswani (McGraw-Hill/Osborne, ISBN 0-07-222477-0, 2004). Vikram is the founder of Melonfire, and has had numerous articles featured on Dev Shed.
Buy this book now.

{mospagebreak title=The Big Picture}

Before we get into the nitty-gritty of what MySQL is and isn’t, let’s take a little time out to answer a basic question: What’s a database, anyhow?

A database, fundamentally, is a collection of data organized and classified according to some criteria. The traditional analogy is that of a filing cabinet organized into drawers (see the following illustration), with each drawer holding files related to a particular subject; these files may be further classified according to a particular set of criteria (alphabetically, by color, by numeric code, and so on). This organization of information into drawers and files makes it easy to retrieve specific bits of information quickly— to lay your hands on a particular piece of information, you pull open the appropriate drawer and select the file(s) matching your criteria.

Databases - How they work

An electronic database helps you organize information and provides the tools necessary to access specific bits of it quickly and efficiently. The drawers that contain the files are referred to in database parlance as tables, the files themselves are called records, the act of pulling out information is referred to as a query, and the resulting data is referred to as a result set. The following illustration shows how you query the database (here, a filing cabinet) to retrieve information. You’ll be seeing a lot of these terms in this book, and you’ll get used to them gradually, so don’t worry if they seem a little foreign at the moment.

Databases - How They Work

Remember: this is chapter one of MySQL: The Complete Reference, by Vikram Vaswani (McGraw-Hill/Osborne, ISBN 0-07-222477-0, 2004). Vikram is the founder of Melonfire, and has had numerous articles featured on Dev Shed.
Buy this book now.

{mospagebreak title=Database Management Systems}

Although you can use a database to organize both small and large amounts of information, its true power becomes apparent when you need to manage a substantial volume of data. If, for example, you have a small amount of data to deal with, you can easily manipulate and search it manually; however, as the volume of information increases, performing a manual search becomes both tedious and costly. Consider a filing cabinet containing 20 files versus one containing 20 million files—locating a single file in the first cabinet takes a matter of minutes, whereas locating a single file in the second cabinet is well nigh impossible (given efficiency constraints).

In such situations, an electronic database management system (DBMS) can substantially simplify your work. Not only does such a system take up less physical space than its traditional paper-based counterpart, but it also comes with tools that assist you in organizing your data and simplifying information retrieval and modification. Built-in indexing makes it possible to locate information rapidly and efficiently, while automated processes ensure that data is always stored and cross-referenced in a consistent, error-free manner. A database also offers portability and compatibility (once the data is organized and stored in a database, it can be extracted and displayed in any manner you choose), and it provides a centralized storage location for important information.

A relational database management system (RDBMS) takes things one step further by creating relationships among the tables that make up a database. These relationships can then be used to combine data from multiple tables in various ways, allowing a user to view the same data from various perspectives and then use this enhanced vision to make better (read: more efficient and cost-effective) business decisions. By creating links among related pieces of information, an RDBMS thus not only makes it possible to store information more efficiently (by removing redundancies and repetition), but it also brings to the fore hitherto undiscovered relationships among disparate segments of data and permits efficient exploitation of those relationships.

Remember: this is chapter one of MySQL: The Complete Reference, by Vikram Vaswani (McGraw-Hill/Osborne, ISBN 0-07-222477-0, 2004). Vikram is the founder of Melonfire, and has had numerous articles featured on Dev Shed.
Buy this book now.

{mospagebreak title=The Challenge}

As you might imagine, this kind of information management has important implications—both in business and elsewhere, which is just one of the many reasons why RDBMSs are so popular in today’s wired world. Large software companies such as Microsoft and Oracle spend millions annually on researching and developing commercial database systems, well aware that the old adage “knowledge is power” is as true here as it was when Sir Francis Bacon first uttered it.

These commercial RDBMSs are powerful, flexible, feature-rich software systems that are designed specifically for high-volume, transaction-heavy, mission-critical applications; they can (and do) zip through millions of records and perform hundreds of transactions every second without batting an eyelid.

The only problem? They’re way beyond the budget of most small- and medium-sized businesses, which typically run smaller, less complex applications that do not require quite so much firepower.

What these users usually need is a fast, reliable alternative that meets their needs in a cost-effective and efficient manner.

That’s where MySQL comes in.

Doing Your Homework

Interested in learning more about relational database management systems? Consider adding the following book to your shopping list: Introduction to Relational Databases and SQL Programming, by Catherine Creary and Christopher Allen (McGraw-Hill, ISBN 0-07-222924-1, 2003).

Remember: this is chapter one of MySQL: The Complete Reference, by Vikram Vaswani (McGraw-Hill/Osborne, ISBN 0-07-222477-0, 2004). Vikram is the founder of Melonfire, and has had numerous articles featured on Dev Shed.
Buy this book now.

{mospagebreak title=…And the Little Database that Could}

MySQL is a high-performance, multi-threaded, multi-user RDBMS built around a client-server architecture. Designed specifically for speed and stability, it has, over the last few years, become one of the most popular RDBMS for database-driven software applications, both on and off the web. Today, more than 4 million web sites create, use, and deploy MySQL-based applications; that number rises daily (as stated on the official MySQL web site, at http://www.mysql.com/).

The MySQL RDBMS consists of the following two components:

  • Server-side tools These include the MySQL database server, which is the core software engine responsible for creating and managing databases, executing queries and returning query results, and maintaining security, together with additional tools to manage multiple MySQL servers, optimize and repair MySQL tables, and create bug reports.

  • Client-side tools These include a command-line MySQL client, tools to manage MySQL user permissions, and utilities to import and export MySQL databases. Also included are command-line tools to view and copy MySQL databases and tables, maintain tables, and retrieve server status information. These components are discussed in detail in subsequent chapters of this book.

Remember: this is chapter one of MySQL: The Complete Reference, by Vikram Vaswani (McGraw-Hill/Osborne, ISBN 0-07-222477-0, 2004). Vikram is the founder of Melonfire, and has had numerous articles featured on Dev Shed.
Buy this book now.

{mospagebreak title=History and Evolution}

MySQL came into being in 1979, when Michael “Monty” Widenius created a database system named UNIREG for the Swedish company TcX. UNIREG didn’t, however, have a Structured Query Language (SQL) interface—something that caused it to fall out of favor with TcX in the mid-1990s. So TcX began looking for alternatives. One of those alternatives was mSQL, a competing DBMS created by David Hughes.

mSQL didn’t work for TcX, however, so Widenius decided to create a new database server customized to his specific requirements. That system, completed and released to a small group in May 1996, became the first version of what is today known as MySQL.

A few months later, MySQL 3.11 saw its first public release as a binary distribution for Solaris. Linux source and binaries followed shortly; an enthusiastic developer community and a friendly, General Public License (GPL)-based licensing policy took care of the rest. (For the story from the horse’s mouth, look at http://www.linuxjournal.com/article.php?sid=3609.) Today, MySQL is available for a wide variety of platforms, including Linux, MacOS, andWindows, in both source and binary form.

A few years later, TcX spun off MySQL AB, a private company that today is the sole owner of the MySQL server source code and trademark and is responsible for maintenance, marketing, and further development of the MySQL database server.

Widenius remains at the helm of the ship, and together with David Axmark and Allan Larsson, they are ably supported in their efforts to improve and enhance MySQL by both a full-time staff and the active support of a worldwide developer community.

What’s in a Name?

Wondering where the name MySQL came from? An entry in the MySQL manual (http://www.mysql.com/doc/en/History.html) suggests that even MySQL’s developers don’t know the origin: “The derivation of the name MySQL is not perfectly clear. Our base directory and a large number of our libraries and tools have had the prefix ‘my’ for well over 10 years. However, Monty’s daughter (some years younger) is also named My. Which of the two gave its name to MySQL is still a mystery, even for us.”

Remember: this is chapter one of MySQL: The Complete Reference, by Vikram Vaswani (McGraw-Hill/Osborne, ISBN 0-07-222477-0, 2004). Vikram is the founder of Melonfire, and has had numerous articles featured on Dev Shed.
Buy this book now.

{mospagebreak title=Features}

MySQL has always been designed around three fundamental principles: performance, reliability, and ease of use. Strict adherence to these principles has resulted in an RDBMS that is inexpensive yet feature-rich, standards-compliant yet easily extensible, and fast yet efficient—making MySQL the perfect tool for developers and administrators looking to build, maintain, and deploy complex software applications.

Following are discussions of MySQL’s most compelling features.

Speed

In an RDBMS, speed—the time it takes to execute a query and return the results to the caller—is everything. Even MySQL’s most ardent critics will admit that MySQL is zippy, sometimes orders of magnitude faster than its competition. Benchmarks available on the MySQL web site show that MySQL outperforms almost every other database currently available, including commercial counterparts like Microsoft SQL Server 2000 and IBM DB2. See “The Need For Speed” sidebar in this chapter for more on how MySQL achieves this high level of performance.

NOTE You can read the full results of the benchmark tests at http://www.eweek.com/article2/0,3959,293,00.asp.

The Need for Speed

Part of the reason for MySQL’s blazing performance is its fully multi-threaded architecture, which allows multiple concurrent accesses to the database. This multi-threaded architecture is the core of the MySQL engine, allowing multiple clients to read the same database simultaneously and providing a substantial performance gain. The MySQL code tree is also structured in a modular, multi-layered manner, with minimum redundancies and special optimizers for such complex tasks as joins and indexing.

MySQL’s designers also initially left out many of the features that cause performance degradation on competing systems, including transactions, referential integrity, and stored procedures. (These features typically add complexity to the server and result in a performance hit.) User requests for these features, however, have resulted in a creative compromise: versions of MySQL later than 3.23.34a do include support for transactions but allow users to make the choice of whether to enable them (and lose some measure of performance) or exclude them (and continue to operate at peak efficiency). This choice may even be made on a table-by-table basis, making it possible to perform fine-grained optimization for maximum performance.

Finally, MySQL 4.0 also includes a unique new feature, a query cache, which can substantially improve performance by caching the results of common queries and returning this cached data to the caller without having to reexecute the query each time. (This is different from competing systems, such as Oracle, in that those systems merely cache the execution plan, not the results. However, they still need to execute the query, including all joins, and re-retrieve the query results on every run.) MySQL benchmarks claim that this feature improves performance by more than 200 percent, with no special programming required on the part of the user.

Remember: this is chapter one of MySQL: The Complete Reference, by Vikram Vaswani (McGraw-Hill/Osborne, ISBN 0-07-222477-0, 2004). Vikram is the founder of Melonfire, and has had numerous articles featured on Dev Shed.
Buy this book now.

{mospagebreak title=More Features}

Reliability

Most of the time, high database performance comes at a price: low reliability. Not true with MySQL, however. The system is designed to offer maximum reliability and uptime, and it has been tested and certified for use in high-volume, mission-critical applications. MySQL’s large user base assists in rapidly locating and resolving bugs and in testing the software in a variety of environments; this proactive approach has resulted in software that is virtually bug-free. Further, every new release of MySQL has to pass both MySQL’s in-house test suite, which tests each feature and also contains test cases for previously-fixed bugs, and MySQL’s crash-me tool, whose primary goal is to evaluate the system’s capabilities by pushing it up to (and beyond) its limits.

Ease of Use

MySQL is so easy to use that even a novice can pick up the basics in a few hours, and the software is well-supported by a detailed manual, a large number of free online tutorials, a knowledgeable developer community, and a fair number of books (hey, you’re reading one right now!). While most interaction with the MySQL server takes place through a command-line interface, a number of graphical tools, both browser-based and otherwise, are also available to simplify the task of managing and administering the MySQL database server. Finally, unlike its proprietary counterparts, which have literally hundreds of adjustable parameters, MySQL is fairly easy to tune and optimize for even the most demanding applications. For commercial environments, MySQL is further supported by MySQL AB, which offers professional training courses, consultancy services, and technical support.

Multi-User Support

MySQL is a full multi-user system, which means that multiple clients can access and use one (or more) MySQL database(s) simultaneously; this is of particular significance during development of web-based applications, which are required to support simultaneous connections by multiple remote clients. MySQL also includes a powerful and flexible privilege system that allows administrators to protect access to sensitive data using a combination of user- and host-based authentication schemes.

Scalability

MySQL can handle extremely large and complex databases without too much of a performance drop. Tables of several gigabytes containing hundreds of thousands of records are not uncommon, and the MySQL web site itself claims to use databases containing 50 million records. In an eWEEK magazine benchmark cited on the MySQL web site, MySQL scaled efficiently at loads from 50 to 1000 simultaneous users, with performance dropping only marginally once the 600-user limit had been crossed. Businesses such as as SAP, Yahoo!, NASA, and Texas Instruments; high-volume web sites such as Google (http://www.google.com/) and Slashdot (http://www.slashdot.com/); and government organizations such as the US Census Bureau and the Rhode Island State Department are all using MySQL to power their systems—and, as http://www.mysql.com/press/user_stories/ demonstrates, they’re all exceedingly pleased with the results.

NOTE Read the full results of the benchmark tests at http://www.eweek.com/article2/0,3959,293,00.asp.

Portability

MySQL is available for both UNIX and non-UNIX operating systems, including Linux, Solaris, FreeBSD, OS/2, MacOS, and Windows 95, 98, Me, 2000, XP, and NT. It runs on a range of architectures, including Intel x86, Alpha, SPARC, PowerPC, and IA64, and it supports many hardware configurations, from low-end 386s to high-end Pentium machines and IBM zSeries mainframes.

Compliance with Existing Standards

MySQL’s development team has attempted to make MySQL as standards-compliant as possible. MySQL 4.0 supports most of the important features of the ANSI SQL-99 standard, with support expected to grow in future versions. Additionally, MySQL extends the ANSI standard with custom extensions, functions, and data types designed to improve portability and provide users with enhanced functionality. (See the sidebar entitled “The Right Choice?” for more information on compatibility between MySQL and other SQL-compliant database systems.)

The Right Choice?

Wondering if MySQL is right for you, or if it’s worth switching from your current RDBMS to MySQL? Make an informed decision: take a look at the feature comparison pages on the MySQL web site at http://www.mysql.com/information/ crash-me.php and http://www.mysql.com/information/benchmarks.html, which sets MySQL head-to-head with its competition.

Remember: this is chapter one of MySQL: The Complete Reference, by Vikram Vaswani (McGraw-Hill/Osborne, ISBN 0-07-222477-0, 2004). Vikram is the founder of Melonfire, and has had numerous articles featured on Dev Shed.
Buy this book now.

{mospagebreak title=Even More Features}

Internationalization

As a program that is used by millions in countries across the globe, it would be unusual indeed if MySQL did not include support for various languages and character sets. MySQL 4.0 supports a number of important character sets (including Latin, Chinese, and European character sets), with full Unicode support available from version 4.1.

Wide Application Support

MySQL exposes application programming interfaces (APIs) to many programming languages, thereby making it possible to write database-driven applications in the language of your choice. Currently, MySQL provides hooks to C, C++, ODBC (Open Database Connectivity), Java, PHP, Perl, Python, and Tcl (Tool Command Language). (Chapters 18 through 21 of this book discuss how to use this API to develop applications in C, Perl. and PHP.)

Enthusiastic Developer Community

As with most open-source projects, MySQL is supported by an active developer community, which is at least partly responsible for the software’s current popularity. High-volume, well-informed mailing lists and user groups assist in the rapid resolution of questions and problems, and a global network of committed MySQL users and developers provides knowledgeable advice, bug fixes, and third-party utilities.

Open Source Code

MySQL AB, the developer of MySQL, is a firm believer in the open-source movement, and MySQL software is freely available under the GPL (with some caveats—see the sidebar entitled “What Goes Around, Comes Around” for more information). Users are free to download and modify the source code of the application to meet their needs, and they can use it to power their applications free of cost. This open licensing policy has fuelled MySQL’s popularity, creating an active and enthusiastic global community of MySQL developers and users. This community plays an active role in keeping MySQL ahead of its competition, both by crash-testing the software for reliability on millions of installations worldwide and by extending the engine to stay abreast of the latest technologies and newest developments. 

What Goes Around, Comes Around

It should be noted that the MySQL server and associated drivers are licensed under the GPL and, therefore, you are free to use and redistribute them in your own software applications, provided that your applications are also licensed under the GPL (or any other compatible open-source license approved by MySQL AB). In this case, the MySQL software is offered to you free of charge.

However, if your MySQL-powered application is not licensed under the GPL or an equivalent licensing scheme, and you do intend to redistribute it (whether internally or externally), you are required to purchase a commercial license for the same from MySQL AB.

MySQL AB earns revenue both from the sale of these licenses and by providing support, training, and consultation services for the MySQL database server.

Remember: this is chapter one of MySQL: The Complete Reference, by Vikram Vaswani (McGraw-Hill/Osborne, ISBN 0-07-222477-0, 2004). Vikram is the founder of Melonfire, and has had numerous articles featured on Dev Shed.
Buy this book now.

{mospagebreak title=Applications}

To quote its official web site, MySQL is “the world’s most popular open source database.” No small claim, that, but the numbers certainly seem to bear it out: according to recent statistics published on the MySQL web site, MySQL is used in more than 4 million systems worldwide, with more than 25,000 copies of the MySQL database server downloaded every day.

NOTE You can visit the official MySQL web site at http://www.mysql.com/.

As a reliable, feature-rich database server, MySQL has applications in business, education, science, and engineering—a fact amply demonstrated by MySQL AB’s customer list, which includes such names as Motorola, Sony, NASA, HP, Xerox, and Silicon Graphics. MySQL software today powers a variety of applications, including Internet web sites, e-commerce applications, search engines, data warehouses, embedded applications, high-volume content portals, and mission-critical software systems.

It’s no surprise that MySQL’s primary applications today lie in the arena of the web. As web sites and web-based distributed applications grow ever more complex, it becomes more and more important that data be managed efficiently to improve transactional efficiency, reduce response time, and enhance the overall user experience. Consequently, a pressing need exists for a data management solution that is fast, stable, and secure—one can be deployed and used with minimal fuss and that provides solid underpinnings for future development.
 
MySQL fits the bill for a number of reasons. Its proven track record generates confidence in its reliability and longevity; its open-source roots ensure rapid bug fixes and a continued cycle of enhancements (not to mention a lower overall cost); its portability and support for various programming languages and technologies make it suitable for a wide variety of applications; and its low cost/high performance value proposition makes it attractive to everyone from home users to small- and medium-sized businesses and government organizations.

Coming Soon…

While MySQL is one of the most full-featured open-source database servers currently available, it doesn’t provide a few things…yet! Here’s a quick list:

  • Stored procedures Predefined sequences of SQL statements that are compiled and saved within the database itself for more efficient execution.

  • Triggers Database actions that are automatically executed when a certain event (such as a change in the data within a table) occurs.

  • Views Virtual tables derived from the content of existing tables, which can be manipulated in exactly the same way as regular tables. The MySQL development roadmap indicates that these features will all be included in subsequent versions of the software.

Remember: this is chapter one of MySQL: The Complete Reference, by Vikram Vaswani (McGraw-Hill/Osborne, ISBN 0-07-222477-0, 2004). Vikram is the founder of Melonfire, and has had numerous articles featured on Dev Shed.
Buy this book now.

{mospagebreak title=Summary}

This chapter provided a gentle introduction into the world of MySQL, setting the stage with a description of how databases work, and then proceeding to an overview of the MySQL relational database management system. It offered insight into MySQL’s history and evolution, identified the core features that have made MySQL popular with developers all over the world, and discussed some of MySQL’s most common applications.

Now that you know a little bit about MySQL, it’s time to drill down to the next level of detail. The next chapter provides a more technical overview of MySQL, discussing the MySQL architecture and the important features of the MySQL database engine.

Remember: this is chapter one of MySQL: The Complete Reference, by Vikram Vaswani (McGraw-Hill/Osborne, ISBN 0-07-222477-0, 2004). Vikram is the founder of Melonfire, and has had numerous articles featured on Dev Shed.
Buy this book now.

[gp-comments width="770" linklove="off" ]

chat sex hikayeleri Ensest hikaye