Please style sheet are not equal in internet explorer browser Firefox, Chrome, Safari, Apple and Opera browser please visit this website.

Thank for Visit My Site and Please sent me Shayari, Status and Quotes post request.

MySQL - Introduction to RDBMS, Introduction to MySQL, Installing MySQL, SQL & MySQL Terminologies

Introduction to RDBMS
Introduction to SQL
SQL (sometimes expanded as Structured Query Language) is a computer language used to create, retrieve, update and delete data from relational database management systems. SQL has been standardized by both ANSI and ISO.
SQL is commonly spoken either as the names of the letters ess-cue-el, or like the word sequel. The official pronunciation of SQL according to ANSI is ess-cue-el. However, each of the major database products (or projects) containing the letters SQL has its own convention:
MySQL is officially and commonly pronounced "My Ess Cue El"; PostgreSQL is expediently pronounced postgres (being the name of the predecessor to PostgreSQL); and Microsoft SQL Server is commonly spoken as Microsoft-sequel-server.
SQL was adopted as a standard by ANSI (American National Standards Institute) in 1986 and ISO (International Organization for Standardization) in 1987.
However, since the dissolution of the NIST data management standards program in 1996 there has been no certification for compliance with the SQL standard so vendors must be relied on to self-certify.
Evolution Of RDBMS
A relational database management system (RDBMS) is a database management system (DBMS) that is based on the relational model as introduced by E. F. Codd . Relational databases are the most common kind of database in use today (assuming one does not count a file system as a database).
A relational database management system (RDBMS) is an essential tool in many environments, from the more traditional uses in business, research, and education contexts, to newer applications, such as powering search engines on the Internet.
However, despite the importance of a good database for managing and accessing information resources, many organizations have found them to be out of reach of their financial resources. Historically, database systems have been an expensive proposition, with vendors charging healthy fees both for software and for support.
In addition, because database engines often had substantial hardware requirements to run with any reasonable performance, the cost was even greater. A short definition of a RDBMS may be a DBMS in which data is stored in tables and the relationship among the data is also stored in tables.
E. F. Codd introduced the term in his seminal paper "A Relational Model of Data for Large Shared Data Banks", published in 1970. In this paper and later papers he defined what he meant by relational.
One well-known definition of what constitutes a relational database system is Codd's 12 rules. However, many of the early implementations of the relational model did not conform to all of Codd's rules, so the term gradually came to describe a broader class of database systems.
At a minimum, these systems:
presented the data to the user as relations (a presentation in tabular form, i.e. as a collection of tables with each table consisting of a set of rows and columns, can satisfy this property) provided relational operators to manipulate the data in tabular form
The most popular definition of an RDBMS is a product that presents a view of data as a collection of rows and columns not based strictly upon relational theory. The majority of real world popular RDBMS products implement some of Codd's 12 rules.
Codd's 12 rules are a set of thirteen rules proposed by Edgar F. Codd, a pioneer of the relational model for databases, designed to define what is required from a database management system in order for it to be considered relational, i.e., an RDBMS.
Codd's 12 rules
Rule 0: The system must qualify as relational, as a database, and as a management system. For a system to qualify as a relational database management system (RDBMS), that system must use its relational facilities (exclusively) to manage the database.
Rule 1: The information rule: All information in the database is to be represented in one and only one way, namely by values in column positions within rows of tables.
Rule 2 : The guaranteed access rule: All data must be accessible with no ambiguity. This rule is essentially a restatement of the fundamental requirement for primary keys. It says that every individual scalar value in the database must be logically addressable by specifying the name of the containing table, the name of the containing column and the primary key value of the containing row.
Rule 3: Systematic treatment of null values: The DBMS must allow each field to remain null (or empty). Specifically, it must support a representation of "missing information and inapplicable information" that is systematic, distinct from all regular values (for example, "distinct from zero or any other number," in the case of numeric values), and independent of data type. It is also implied that such representations must be manipulated by the DBMS in a systematic way.
Rule 4: Active online catalog based on the relational model: The system must support an online, inline, relational catalog that is accessible to authorized users by means of their regular query language. That is, users must be able to access the database's structure (catalog) using the same query language that they use to access the database's data.
Rule 5: The comprehensive data sublanguage rule: The system must support at least one relational language that (a) Has a linear syntax (b) Can be used both interactively and within application programs, (c) Supports data definition operations (including view definitions), data manipulation operations (update as well as retrieval), security and integrity constraints, and transaction management operations (begin, commit, and rollback).
Rule 6: The view updating rule: All views that are theoretically updatable must be updatable by the system.
Rule 7: High-level insert, update, and delete: The system must support set-at-a-time insert, update, and delete operators. This means that data can be retrieved from a relational database in sets constructed of data from multiple rows and/or multiple tables. This rule states that insert, update, and delete operations should be supported for any retrievable set rather than just for a single row in a single table.
Rule 8: Physical data independence: Changes to the physical level (how the data is stored, whether in arrays or linked lists etc.) must not require a change to an application based on the structure.
Rule 9: Logical data independence: Changes to the logical level (tables, columns, rows, and so on) must not require a change to an application based on the structure. Logical data independence is more difficult to achieve than physical data independence.
Rule 10: Integrity independence: Integrity constraints must be specified separately from application programs and stored in the catalog. It must be possible to change such constraints as and when appropriate without unnecessarily affecting existing applications.
Rule 11: Distribution independence: The distribution of portions of the database to various locations should be invisible to users of the database. Existing applications should continue to operate successfully : (a) when a distributed version of the DBMS is first introduced; and (b) when existing distributed data are redistributed around the system.
Rule 12: The nonsubversion rule: If the system provides a low-level (record-at-a-time) interface, then that interface cannot be used to subvert the system, for example, bypassing a relational security or integrity constraint.
Introduction to MySQL
Introduction of MySQL
MySQL, the most popular Open Source SQL database management system, is developed, distributed, and supported by MySQL AB. MySQL AB is a commercial company, founded by the MySQL developers. It is a second generation Open Source company that unites Open Source values and methodology with a successful business model.
A relational database management system (RDBMS) is an essential tool in many environments, from the more traditional uses in business, research, and education contexts, to newer applications, such as powering search engines on the Internet.
However, despite the importance of a good database for managing and accessing information resources, many organizations have found them to be out of reach of their financial resources.
Historically, database systems have been an expensive proposition, with vendors charging healthy fees both for software and for support. In addition, because database engines often had substantial hardware requirements to run with any reasonable performance, the cost was even greater.
In recent years, the situation has changed on both the hardware and software sides of the picture. Personal computers have become inexpensive but powerful, and a whole movement has sprung up to write high-performance operating systems for them that are available for the cost of an inexpensive CD, or even free over the Internet.
These include several BSD UNIX derivatives (FreeBSD, NetBSD, OpenBSD) as well as various forms of Linux (RedHat, Caldera, LinuxPPC, to name a few).
Production of free operating systems to drive personal computers to their full capabilities has proceeded in concert with-and to a large extent has been made possible by-the development of freely available tools such as gcc, the GNU C compiler.
These efforts to make software available to anyone who wants it have resulted in what is now called the Open Source movement, and which has produced many important pieces of software.
For example, Apache is the most widely used Web server on the Internet. Other Open Source successes are the Perl general-purpose scripting language and PHP, a language that is popular due largely to the ease with which it allows dynamic Web pages to be written.
These all stand in contrast to proprietary solutions that lock you into high-priced products from vendors that don't even provide source code.
Database software has become more accessible, too. Database systems such as PostgreSQL are available for free. More recently, commercial vendors such as Informix and Oracle have begun to offer their software at no cost for operating systems such as Linux.
(However, these latter products generally come in binary-only form with no support, which lessens their usefulness.)
Another entry into the no-to-low cost database arena is MySQL, a SQL (Structured Query Language) client/server relational database management system originating from Scandinavia. MySQL includes a SQL server, client programs for accessing the server, administrative tools, and a programming interface for writing your own programs.
MySQL's roots began in 1979, with the UNIREG database tool created by Michael "Monty" Widenius for the Swedish company TcX. In 1994, TcX began looking around for a SQL server for use in developing Web applications.
They tested some commercial servers, but found all too slow for TcX's large tables. They also took a look at mSQL, but it lacked certain features TcX required. Consequently, Monty began developing a new server.
The programming interface was designed explicitly to be similar to the one used by mSQL because several free tools were available for mSQL; by using a similar interface, those same tools could be used for MySQL with a minimum of porting effort.
In 1995, David Axmark of Detron HB began to push for TcX to release MySQL on the Internet. David also worked on the documentation and on getting MySQL to build with the GNU configure utility.
MySQL 3.11.1 was unleashed on the world in 1996 in the form of binary distributions for Linux and Solaris. Today, MySQL works on many more platforms and is available in both binary and source form. The company MySQL AB has been formed to provide distributions of MySQL and to offer support and training services.
And MySQL continues to develop. The addition of features such as transactions, row-level locking, foreign key support, and replication has caused people who once would have considered only "big engine" databases for their applications to give MySQL a second look.
MySQL is an Open Source project that can be used for free under most circumstances, for which reason it enjoys widespread popularity in the Open Source community. But MySQL's popularity isn't limited to Open Source enthusiasts.
Yes, it runs on personal computers (indeed, much MySQL development takes place on inexpensive Linux systems). But MySQL is portable and runs on commercial operating systems (such as Solaris, Mac OS X, and Windows) and on hardware all the way up to enterprise servers. Furthermore, its performance rivals any database system you care to put up against it, and it can handle large databases with millions of records.
MySQL lies squarely within the picture that now unfolds before us: freely available operating systems running on powerful but inexpensive hardware, putting substantial processing power and capabilities in the hands of more people than ever before, on a wider variety of systems than ever before.
This lowering of the economic barriers to computing puts powerful database solutions within reach of more people and organizations than at any time in the past. Organizations that once could only dream of putting the power of a high-performance RDBMS to work for them now can do so for very little cost.
Why use MySQL?
If you're looking for a free or inexpensive database management system, several are available from which to choose: MySQL, PostgreSQL, one of the free-but-unsupported engines from commercial vendors, and so forth.
When you compare MySQL with other database systems, think about what's most important to you: Performance, support, features (SQL conformance, extensions, and so forth), licensing conditions and restrictions, and price all are factors to take into account. Given these considerations, MySQL has many attractive features to offer:
. Speed: MySQL is fast. The developers contend that MySQL is about the fastest database you can get. You can investigate this claim by visiting performance-comparison page on the MySQL Web site.
. Ease of use: MySQL is a high-performance but relatively simple database system and is much less complex to set up and administer than larger systems.
. Query language support. MySQL understands SQL, the language of choice for all modern database systems.
. Capability Many clients can connect to the server at the same time. Clients can use multiple databases simultaneously. You can access MySQL interactively using several interfaces that let you enter queries and view the results:
command-line clients, Web browsers, or X Window System clients. In addition, a variety of programming interfaces are available for languages such as C, Perl, Java, PHP, and Python.
You can also access MySQL using applications that support ODBC (Open Database Connectivity), a database communications protocol developed by Microsoft. Thus, you have the choice of using prepackaged client software or writing your own for custom applications.
. Connectivity and security: MySQL is fully networked, and databases can be accessed from anywhere on the Internet, so you can share your data with anyone, anywhere.
But MySQL has access control so that people who shouldn't see your data can't. To provide additional security, MySQL now supports encrypted connections using the Secure Sockets Layer (SSL) protocol.
. Portability: MySQL runs on many varieties of UNIX, as well as on other non-UNIX systems, such as Windows and OS/2. MySQL runs on hardware from home PCs to high-end servers.
. Small size: MySQL has a modest distribution size, especially compared to the huge disk space footprint of certain commercial database systems.
. Availability and cost MySQL is an Open Source project, freely available under the terms of the GNU General Public License (GPL). This means that MySQL is free for most in-house uses. (If you want to sell MySQL or services that require it, that is a different situation and you should contact MySQL AB.)
. Open distribution: MySQL is easy to obtain; just use your Web browser. If you don't understand how something works or are curious about an algorithm, you can get the source code and poke around in it. If you don't like how something works, you can change it. If you think you've found a bug, report it; the developers listen.
. MySQL is a relational database management system: A relational database stores data in separate tables rather than putting all the data in one big storeroom. This adds speed and flexibility. The SQL part of "MySQL" stands for "Structured Query Language.
SQL is the most common standardized language used to access databases and is defined by the ANSI/ISO SQL Standard. The SQL standard has been evolving since 1986 and several versions exist.
In this manual, "SQL-92" refers to the standard released in 1992, "SQL:1999" refers to the standard released in 1999, and "SQL:2003" refers to the current version of the standard. We use the phrase "the SQL standard" to mean the current version of the SQL Standard at any time.
What about support? Good question-a database isn't much use if you can't get help for it. Naturally, I'd like to think this book is all the assistance you'll ever need. But, realistically, you'll have questions that I never thought of or didn't have room to cover.
You'll find that other resources are available and that MySQL has good support. MySQL is freely available, but you're not on your own when you install it:
. The MySQL Reference Manual is included in MySQL distributions and also is available online. The Reference Manual regularly receives good marks in the MySQL user community. This is important, because the value of a good product is diminished if no one can figure out how to use it.
. Training classes and technical support contracts are available from MySQL AB, for those who prefer or require formal arrangements.
. There is an active mailing list to which anyone may subscribe. The list has many helpful participants, including several MySQL developers. As a support resource, many people find this list sufficient for their purposes.
The MySQL community, developers and non-developers alike, is very responsive. Answers to questions on the mailing list often arrive within minutes. When bugs are reported, the developers generally release a fix quickly, and fixes become available immediately over the Internet.
Contrast this with the often-frustrating experience of navigating the Byzantine support channels of the big vendors. (You've been there? Me, too. I know which alternative I prefer when I have a question about a product.
Being put on hold at a vendor's convenience has no appeal compared to being able to post a question to a mailing list and check for replies at my convenience.)
MySQL is an ideal candidate for evaluation if you are in the database-selection process. You can try MySQL with no risk or financial commitment. Yet, if you get stuck, you can use the mailing list to get help.
An evaluation costs some of your time, but that's true no matter what database system you're considering-and it's a safe bet that your installation and setup time for MySQL will be less than for many other systems.
Features of MySQL
The following list shows the most important properties of MySQL. This section is directed to the reader who already has some knowledge of relational databases.
We will use some terminology from the relational database world without defining our terms exactly. On the other hand, the explanations should make it possible for database novices to understand to some extent what we are talking about.
. Relational Database System: Like almost all other database systems on the market, MySQL is a relational database system.
. Client/Server Architecture: MySQL is a client/server system. There is a database server (MySQL) and arbitrarily many clients (application programs), which communicate with the server; that is, they query data, save changes, etc.
The clients can run on the same computer as the server or on another computer (communication via a local network or the Internet).
Almost all of the familiar large database systems (Oracle, Microsoft SQL Server, etc.) are client/server systems. These are in contrast to the file-server systems, which include Microsoft Access, dBase, and FoxPro.
The decisive drawback to file-server systems is that when run over a network, they become extremely inefficient as the number of users grows.
. SQL compatibility: MySQL supports as its database language-as its name suggests-SQL (Structured Query Language). SQL is a standardized language for querying and updating data and for the administration of a database.
There are several SQL dialects (about as many as there are database systems). MySQL adheres to the ANSI-SQL/92 standard, although with significant restrictions and many a number of extensions.
This topic will be dealt with more extensively later. Beyond the ANSI-SQL/92 standard, MySQL supports, among other things, several additional data types, full-text indexes, and replication.
. User Interface: There are a number of convenient user interfaces for administering a MySQL server.
. Full-text search: Full-text search simplifies and accelerates the search for words that are located within a text field. If you employ MySQL for storing text (such as in an Internet discussion group), you can use full-text search to implement simply an efficient search function.
. Replication: Replication allows the contents of a database to be copied (replicated) onto a number of computers. In practice, this is done for two reasons: to increase protection against system failure (so that if one computer goes down, another can be put into service) and to improve the speed of database queries.
. Transactions: In the context of a database system, a transaction means the execution of several database operations as a block. The database system ensures that either all of the operations are correctly executed or none of them.
This holds even if in the middle of a transaction there is a power failure, the computer crashes, or some other disaster occurs. Thus, for example, it cannot occur that a sum of money is withdrawn from account A but fails to be deposited in account B due to some type of system error.
Transactions also give programmers the possibility of interrupting a series of already executed commands (a sort of revocation). In many situations this leads to a considerable simplification of the programming process.
In spite of popular opinion, MySQL has supported transactions for a long time. One should note here that MySQL can store tables in a variety of formats. The default table format is called MyISAM, and this format does not support transactions.
But there are a number of additional formats that do support transactions. The most popular of these is InnoDB, which will be described in its own chapter.
. Foreign key constraints: These are rules that ensure that there are no cross references in linked tables that lead to nowhere. MySQL supports foreign key constraints for InnoDB tables.
. Programming languages: There are quite a number of APIs (application programming interfaces) and libraries for the development of MySQL applications. For client programming you can use, among others, the languages C, C++, Java, Perl, PHP, Python, and Tcl.
. ODBC: MySQL supports the ODBC interface Connector/ODBC. This allows MySQL to be addressed by all the usual programming languages that run under Microsoft Windows (Delphi, Visual Basic, etc.). The ODBC interface can also be implemented under Unix, though that is seldom necessary.
Windows programmers who have migrated to Microsoft's new .NET platform can, if they wish, use the ODBC provider or special MySQL providers for .NET.
. Platform independence: It is not only client applications that run under a variety of operating systems; MySQL itself (that is, the server) can be executed under a number of operating systems.
The most important are Apple Macintosh OS X, Linux, Microsoft Windows, and the countless Unix variants, such as AIX, BSDI, FreeBSD, HP-UX, OpenBSD, Net BSD, SGI Iris, and Sun Solaris.
. Speed: MySQL is considered a very fast database program. This speed has been backed up by a large number of benchmark tests (though such tests-regardless of the source-should be considered with a good dose of skepticism).
Installing MySQL
Getting the Software
MySQL is available for Windows, Linux and other Unix variants under GNU GPL(Genera Public License) as well as Commercial License .
MySQL download - the Windows version
On the download page of MySQL web site, you'll find the links to Windows version. According to the MySQL site the Windows version "contains both the Standard and Max server binaries. It also contains a version of the command-line client which uses the Cygwin library to provide command history and editing".
MySQL download - the Linux version
RPM download is recommend for Linux workstations. (Make sure you download all the RPMs; the MySQL server, client and development RPMs. Note: You need the MySQL client software for this tutorial. Check MySQL website, it might have a complete RPM package that contains all the RPMS in one download file). If you run Linux as a server, the tarball download might be better.
You can download MySQL 5.0 by using the url given below:
if you want to download the latest release or any other version of your choice use the URL given below to go MySQL download home:
or you also download it from here:
Installing MySQL
As you already know that MySQL is available for a variety of Platforms such as Linux, Unix, Windows etc. MySQL installation on different platform is different from the other. In this chapter, we will guide you, MySQL 5.0 installation on the Windows platform.
Once you have successfully downloaded the Windows version, installing it is a breeze... trust me!, the reason is, neither it is going to take too much time as Oracle 9 installation takes nor you have configure to much options like one in MS SQL server installation.
To install MySQL on Windows follow these steps:
1. Open the folder where you have downloaded MySQL and double click on Setup to start the installation process or if you have downloaded the Zip file in place of Binary Setup first extract the contents of ZIP file and then Double click on Setup .
Click on Next to begin the installation process.
Choose the desired installation type from Setup types and click on Next to proceed.
You can choose Setup type that best suits you or your organization requirement.
Now Click on Install to install or Back to reconfigure the options.
MySQL is being installed wait a while.
Form Sign Up wizard choose Skip Sign-Up and click on Next to Finish the installation.
Post Install Configuration
When we install MySQL server 5.0 on windows we need to configure the MySQL server options such as root password, char-set, anonymous access, database file location etc. This procedure is known as Post Install Configuration.
To Configure MySQL server instance on your machine, follow these steps: After MySQL installation is complete, Select the "Configure the MySQL Server now" and Click on Finish button.
MySQL Server Instance Configuration wizard is displayed click on Next to begin.
Select the Configuration type and click on Next. Detailed Configuration allows us to configure almost everything such Server type, Database type, Datafile location, Number of connections and TCP/IP port etc; on the other hand Standard Configuration allows us to configure minimal options.
I will advice you to select Detailed Configuration option for maximum optimization and full utilization of MySQL server services.
This chapter will use Detailed Configuration to guide you how to configure MySQL Server instance.
Select Server type, remember your selection will influence memory and CPU usage, so be cautious. I will suggest you to select Developer Machine, because this type of MySQL Server use minimum memory and thus best suited for individual usage. Select the desired option and click on Next.
Select the Database usage, Multifunctional Database in best for individual uses, and click on Next.
Select the Datafile location, default location is installed directory and click on Next.
Set the approximate number of concurrent connections to the server and click on Next.
Select the TCP/IP port and click on Next.
Select the default Char Set and click on Next.
Set the Windows options such as Windows Service Name and Windows PATH options and click on Next.
Modify the security settings as per your choice and click on Next.
SQL & MySQL Terminologies
Database Theory
The earliest known use of the term 'data base' was in July 1973, when the System Development Corporation sponsored a symposium under the title Development and Management of a Computer-centered Data Base.
Database as a single word became common in Europe in the early 1970s and by the end of the decade it was being used in major American newspapers. (Databank, a comparable term, had been used in the Washington Post newspaper as early as 1966.)
In computing, a database can be defined as a structured collection of records or data that is stored in a computer so that a program can consult it to answer queries. The records retrieved in answer to queries become information that can be used to make decisions.
The computer program used to manage and query a database is known as a database management system (DBMS). The properties and design of database systems are included in the study of information science.
The term "database" originated within the computing discipline. Although its meaning has been broadened by popular use, even to include non-electronic databases, this article is about computer databases.
Database-like records have been in existence since well before the industrial revolution in the form of ledgers, sales receipts and other business related collections of data.
Broader Definition
There is scarcely to be found a term that is less precise than database. A database can be a list of addresses residing in a spreadsheet program (such as Excel), or it can be the administration files of a telecommunications firm in which several million calls are registered daily, their charges accurately calculated, monthly bills computed, and warning letters sent to those who are in arrears.
A simple database can be a stand-alone operation (residing locally on a computer for a single user), while others may be used simultaneously by thousands of users, with the data parceled out among several computers and dozens of hard drives. The size of a database can range from a few kilobytes into the terabytes.
In ordinary usage, the word "database" is used to refer to the actual data, the resulting database files, the database system (such as MySQL or Oracle), or a database client (such as a PHP script or a program written in C++). Thus there arises a great potential for confusion as soon as two people begin to converse on the subject of databases.
Relations, Database Systems, Servers, and Clients
A database is an ordered collection of data, which is normally stored in one or more associated files. The data are structured as tables, where cross references among tables are possible. The existence of such relations among the tables leads to the database being called a relational database.
Let us clarify matters with an example. A database might consist of a table with data on a firm's customers (name, address, etc.), a table with data on the products the firm offers, and finally, a table containing the firm's orders. Through the table of orders it is possible to access the data in the other two tables (for example, via customer and product numbers).
MySQL, Oracle, the Microsoft SQL server, and IBM DB2 are examples of relational database systems. Such a system includes the programs for managing relational databases.
Among the tasks of a relational database system are not only the secure storage of data, but also such jobs as the processing of commands for querying, analyzing, and sorting existing data and for storing new data. All of this should be able to take place not only on a single computer, but over a network as well. Instead of a database system we shall often speak of a database server.
Where there are servers, there are clients. Every program that is connected to the database system is called a database client. Database clients have the job of simplifying the use of the database for the end user. No user of a database system in his or her right mind would wish to communicate directly with the database server.
That is much too abstract and inconvenient. (Let programmers worry about such direct communication!) Instead, the user has a right to expect convenient tables, listboxes, and so on to enable the location of data or to input new data.
Database clients can assume a variety of forms, and indeed, they are often not recognized by the user as database programs at all. Some examples of this type of client are HTML pages for the display and input of messages in an on-line discussion group, a traditional program with several windows for managing addresses and appointments, and a Perl script for executing administrative tasks. There is thus wide scope for database programming.
Relational vs Object-Oriented Database Systems
Relational databases have dominated the database world for decades, and they are particularly well suited for business data, which usually lend themselves to structuring in the form of tables. Except for the following two paragraphs, this entire book discusses only relational databases (though we shall not always stress this point).
Another kind of database is the object-oriented database. Such databases can store free-standing objects (without having to arrange them in tables). Although in recent years there has been a trend in the direction of object-oriented programming languages (such as Object-Store, O2, Caché), object-oriented databases have found only a small market niche.
Note that relational databases can be accessed by means of object-oriented programming languages. However, that does not turn a relational database into an object-oriented one.
Object-oriented database systems enable direct access to objects defined in the programming language in question and the storage of such objects in the database without conversion (persistency). It is precisely this that is not possible with relational database systems, in which everything must be structured in tables.
Tables, Records, Fields, Queries, SQL, Index, Keys
We have already mentioned tables, which are the structures in which the actual data are located. Every line in such a table is called a data record, or simply record, where the structure of each record is determined by the definition of the table.
For example, in a table of addresses every record might contain fields for family name, given name, street, and so on. For every field there are precise conditions on the type of information that can be stored (such as a number in a particular format, or a character string with apredetermined maximum number of characters).
Note Sometimes, instead of data records with fields, we speak of rows and columns. The meaning is the same.
The description of a database consisting of several tables with all of its fields, relations, and indexes (see below) is called a database model. This model defines the construction of the data structures and at the same time provides the format in which the actual data are to be stored.
Tables usually contain their data in no particular order (more precisely, the order is usually that in which the data have been entered or modified). However, for efficient use of the data it is necessary that from these unordered data a list can be created that is ordered according to one or more criteria.
It is frequently useful for such a list to contain only a selection of the data in the table. For example, one could obtain a list of all of one's customers, ordered by ZIP code, who have ordered a rubber ducky within the past twelve months.
To create such a list, one formulates queries. The result of the query is again a table; however, it is one that exists in active memory (RAM) and not on the hard drive.
To formulate a query one uses SQL instructions, which are commands for selecting and extracting data. The abbreviation SQL stands for Structured Query Language, which has become a standard in the formulation of database queries. Needless to say, every producer of a database system offers certain extensions to this standard, which dilutes the goal of compatibility among various database systems.
When tables get large, the speed at which a query can be answered depends significantly on whether there is a suitable index giving the order of the data fields. An index is an auxiliary table that contains only information about the order of the records. An index is also called a key.
An index speeds up access to data, but it has disadvantages as well. First, every index increases the amount of storage on the hard drive necessary for the database file, and second, the index must be updated each time the data are altered, and this costs time.
(Thus an index saves time in the reading of data, but it costs time in entering and altering data. It thus depends on the use to which the data are to be put whether an index is on the whole a net plus or minus in the quest for efficiency.)
A special case of an index is a primary index,or primary key,which is distinguished in that the primary index must ensure a unique reference to a record. Often, for this purpose one simply uses a running index number (ID number). Primary indexes play a significant role in relational databases, and they can speed up access to data considerably.
A Sample Database
This section describes the sample database we'll use throughout the rest of this book. It gives you a source of examples you can try out as you learn to put MySQL to work. We'll draw examples primarily from two situations:
. The organizational scenario: We need something more definite than "an organization," so I'll make one up with these characteristics: It's composed of people drawn together through a common affinity for an organization such as
The members maintain their affiliation by renewing their memberships periodically on a dues-paying basis. Dues go toward the expenses incurred by the company, such as publication of a newsletter, Convention . The company also operates a Web site.
The web site of provide information on variety of topics such as what the is about, who the Associates/Sponsors/Students are, and how people can join as well as also provide online education(e-Learning), Web Hosting for Associates and Web mail(e-mail).
. The exam/grade-keeping scenario: During the grading period, you administer quizzes and tests, record scores, and assign grades. Afterward, you determine final grades. As you know every Associate/Student have to pass certain courses in order to receive the certificates.
Now let's examine these situations more closely in terms of two requirements:
. You have to decide what you want to get out of the database-that is, what goals you want to accomplish.
. You have to figure out what you're going to put into the database-that is, what data you will keep track of.
Perhaps it seems backward to think about what comes out of the database before considering what goes into it. After all, you must enter your data before you can retrieve it.
But the way you use a database is driven by your goals, and those are more closely associated with what you want to get from your database than with what you put into it. You certainly aren't going to waste time and effort putting information into a database unless you're going to use it for something later.
The organizational scenario:
The initial situation for this scenario is that you as League secretary maintain the membership list using a word processing document. That works reasonably well for generating a printed directory but limits what else you can do with the information. You have the following objectives in mind:
. You want to be able to produce output from the directory in different formats, using only information appropriate to the application. One goal is to be able to generate the printed directory each year-a requirement the Company has had in the past that you plan to continue to carry out.
You can think of other uses for the information in the directory, too-for example, to provide the current member list for the printed program that's handed out to attendees of the Company annual Conventions. These applications involve different sets of information.
The printed directory uses the entire contents of each member's entry. For the banquet program, you need to pull out only member names (something that hasn't been easy using a word processor).
. You want to search the directory for members whose entries satisfy various criteria. For example, you want to know which members need to renew their memberships soon.
Another application that involves searching arises from the list of keywords you maintain for each member. Members sometimes ask you certain question related to Commission, Conventions, and you'd like to be able to satisfy these requests.
. You want to put the directory online at the Web site. This would benefit both the members and yourself.
If you could convert the directory to Web pages by some reasonably automated process, an online version of the directory could be kept up to date in a more timely fashion than the printed version.
And if the online directory could be made searchable, members could look for information easily themselves
I'm well aware that databases are not the most exciting things in the world, so I'm not about to make any wild claims that using one stimulates creative thinking.
Nevertheless, when you stop thinking of information as something you must wrestle with (as you do with your word processing document) and begin thinking of it as something you can manipulate relatively easily (as you hope to do with MySQL), it has a certain liberating effect on your ability to come up with new ways to use or present that information:
. If the information in the database can be moved to the Web site in the form of an online directory, you might be able to make information flow the other way.
For example, if members could edit their own entries online to update the database, you wouldn't have to do all the editing yourself, and it would help make the information in the directory more accurate.
. If you stored email addresses in the database, you could use them to send email to members who haven't updated their entries in a while.
The messages could show members the current contents of their entries, ask them to review it, and indicate how to make any needed modifications using the facilities provided on the Web site.
. A database might help you make the Web site more useful in ways not even related to the membership list. The League publishes a newsletter, Chronicles of U.S. Past, that has a children's section in each issue containing a history-based quiz.
Some of the recent issues have focused on biographical facts about U.S. presidents. The Web site could have a children's section, too, where the quizzes are put online.
Perhaps this section could even be made interactive by putting the information from which quizzes are drawn in the database and having the Web server query the database for questions to present to visitors.
Well! At this point the number of uses for the database that you're coming up with may make you realize that you could be getting a little carried away. After pausing to come back down to earth, you start asking some practical questions:
. Isn't this a little ambitious? Won't it be a lot of work to set this up? Anything's easier when you're just thinking about it and not doing it, of course, and I won't pretend that all of this will be trivial to implement.
Nevertheless, you'll have done everything we've just outlined by the end of this book. Just keep one thing in mind: It's not necessary to do everything all at once. We'll break the job into pieces and tackle it a piece at a time.
. Can MySQL do all these things? No it can't, at least not by itself. For example, MySQL has no direct Web-programming facilities. But even though MySQL alone cannot do everything we've discussed, you can combine MySQL with other tools that work with it to complement and extend its capabilities.
MySQL integrates well with other languages(such as PHP, Perl, Java)and tools and gives you the flexibility to choose how to combine them to achieve the ends you have in mind. You're not locked into some all-in-one suite's components that have highly touted "integration" capabilities but that actually work well only with each other.
. And, finally, the big question-how much will all this cost?. This may surprise you, but it probably won't cost anything. If you're familiar with the usual ken of database systems, you know that they're generally pretty pricey.
By contrast, MySQL is usually free. (See the MySQL Reference Manual for specific details.) The other tools that can be easily used with (Java, Perl, DBI, PHP, Apache) are free, so, all things considered, you can put together a useful system quite inexpensively.
The choice of operating system for developing the database is up to you. Virtually all the software we'll discuss runs under both UNIX (which I use as an umbrella term that includes BSD UNIX, Linux, Mac OS X, and so on) and Windows.
The few exceptions tend to be shell or batch scripts that are specific to either UNIX or Windows. Now let's consider the other situation for which we'll be using the sample database.
How the Sample Database Applies to You
If you're not particularly interested in the example scenario, you may be wondering what any of this has to do with you. The answer is that these example scenarios aren't an end in themselves. They simply provide a vehicle by which to illustrate what you can do with MySQL and tools that are related to it.
With a little imagination, you'll see how example database queries apply to the particular problems you want to solve. Suppose you're working in that dentist's office I mentioned earlier.
You won't see many dentistry-related queries in this book, but you will see that many of the queries you find here apply to patient record maintenance, office bookkeeping, and so on.
For example, determining which Historical League members need to renew their memberships soon is similar to determining which patients haven't visited the dentist for a while.
Both are date-based queries, so once you learn to write the membership-renewal query, you can apply that skill to writing the delinquent-patient query in which you have a more immediate interest.
Basic Database Terminology
Within the database world, MySQL is classified as a relational database management system (RDBMS). That phrase breaks down as follows:
. The database (the "DB" in RDBMS) is the repository for the information you want to store, structured in a simple, regular fashion:
o The collection of data in a database is organized into tables.
o Each table is organized into rows and columns.
o Each row in a table is a record.
o Records can contain several pieces of information; each column in a table corresponds to one of those pieces.
. The management system (the "MS") is the software that lets you use your data by allowing you to insert, retrieve, modify, or delete records.
. The word "relational" (the "R") indicates a particular kind of DBMS, one that is very good at relating (that is, matching up) information stored in one table to information stored in another by looking for elements common to each of them.
The power of a relational DBMS lies in its ability to pull data from those tables conveniently and to join information from related tables to produce answers to questions that can't be answered from individual tables alone.
Query Language Terminology
To communicate with MySQL, you use a language called SQL (Structured Query Language). SQL is today's standard database language, and all major database systems understand it. SQL supports many different kinds of statements, all designed to make it possible to interact with your database in interesting and useful ways.
As with any language, SQL may seem strange while you're first learning it. For example, to create a table, you need to tell MySQL what the table's structure should be. You and I might think of the table in terms of a diagram or picture, but MySQL doesn't, so you create the table by telling MySQL something like this:
( company_name CHAR(30),
company_num INT,
address CHAR(30),
phone CHAR(12),
associate_type CHAR(10)
Statements like this can be somewhat imposing when you're new to SQL, but you need not be a programmer to learn how to use SQL effectively. As you gain familiarity with the language, you'll look at CREATE TABLE in a different light-as an ally that helps you describe your information, not as just a weird bit of gibberish.
MySQL Architectural Terminology
When you use MySQL, you're actually using two programs, because MySQL operates using a client/server architecture:
. The server program, mysqld, is located on the machine where your databases are stored. It listens for client requests coming in over the network and accesses database contents according to those requests to provide clients with the information they request.
. Clients are programs that connect to the database server and issue queries to tell it what information they want.
The MySQL distribution includes the database server and several client programs. You use the clients according to the purposes you want to achieve. The one most commonly used is mysql, an interactive client that lets you issue queries and see the results.
Two administrative clients are mysqldump, which dumps table contents into a file, and mysqladmin, which allows you to check on the status of the server and performs administrative tasks, such as telling the server to shut down.
The distribution includes other clients as well. If you have application requirements for which none of the standard clients is suited, MySQL also provides a client-programming library so that you can write your own programs. The library is usable directly from C programs.
If you prefer a language other than C, interfaces are available for several other languages-Perl, PHP, Python, Java, C++, and Ruby, to name a few. MySQL's client/server architecture has certain benefits:
. The server provides concurrency control so that two users cannot modify the same record at the same time. All client requests go through the server, so the server sorts out who gets to do what and when.
If multiple clients want to access the same table at the same time, they don't all have to find and negotiate with each other. They just send their requests to the server and let it take care of determining the order in which the requests will be performed.
. You don't have to be logged in on the machine where your database is located. MySQL understands how to work over the Internet, so you can run a client program from wherever you happen to be, and the client can connect to the server over the network.
Distance isn't a factor; you can access the server from anywhere in the world. If the server is located on a computer in Australia, you can take your laptop computer on a trip to Iceland and still access your database. Does that mean anyone can get at your data just by connecting to the Internet? No. MySQL includes a flexible security system, so you can allow access only to people who should have it.
And you can make sure those people are able to do only what they should. Perhaps Sally in the billing office should be able to read and update (modify) records, but Phil at the service desk should be able only to look at them. You can set each person's privileges accordingly.
If you do want to run a self-contained system, just set the access privileges so that clients can connect only from the host on which the server is running.
Beginning with MySQL 5, you have another option for running the server. In addition to the usual mysqld server that is used in a client/server setting, MySQL includes the server as a library, libmysqld, that you can link into programs to produce standalone MySQL-based applications.
This is called the embedded server library because it's embedded into individual applications. Use of the embedded server contrasts with the client/server approach in that no network is required.
This makes it easier to create and package applications that can be distributed on their own with fewer assumptions about their external operational environment. On the other hand, it should be used only in situations where the embedded application is the only one that will need access to the databases managed by the server.
MySQL & You
A database system is essentially just a way to manage lists of information. The information can come from a variety of sources. For example, it can represent research data, business records, customer requests, sports statistics, sales reports, personal hobby information, personnel records, bug reports, or student grades.
However, although database systems can deal with a wide range of information, you don't use such a system for its own sake. If a job is easy to do already, there's no reason to drag a database into it just to use one.
A grocery list is a good example; you write down the items to get, cross them off as you do your shopping, and then throw the list away. It's highly unlikely that you'd use a database for this. Even if you have a palmtop computer, you'd probably keep track of a grocery list by using its notepad function rather than its database capabilities.
The power of a database system comes into play when the information you want to organize and manage becomes voluminous or complex and your records become more burdensome than you care to deal with by hand. Clearly this is the case for large corporations processing millions of transactions a day; a database is a necessity under such circumstances.
But even small-scale operations involving a single person maintaining information of personal interest may require a database. It's not difficult to think of scenarios in which the use of a database can be beneficial because you needn't have huge amounts of information before that information becomes difficult to manage. Consider the following situations:
. Your carpentry business has several employees. You need to maintain employee and payroll records so that you know whom you've paid and when, and you must summarize those records so that you can report earnings statements to the government for tax purposes.
You also need to keep track of the jobs your company has been hired to do and which employees you've scheduled to work on each job.
. You run a network of automobile parts warehouses and need to be able to tell which ones have any given part in their inventories so that you can fill customer orders.
. As a toy seller, you're particularly subject to fad-dependent demand for items that you carry. You want to know what the current sales trajectory is for certain items so that you can estimate whether to increase inventory (for an item that's becoming more popular) or decrease it (so you're not stuck with a lot of stock for something that's no longer selling well).
. That pile of research data you've been collecting over the course of many years needs to be analyzed for publication, lest the dictum "publish or perish" become the epitaph for your career. You want to boil down large amounts of raw data to generate summary information and to pull out selected subsets of observations for more detailed statistical analysis.
. You're a popular speaker who travels the country to many types of assemblies, such as graduations, business meetings, civic organizations, and political conventions. You give so many addresses that it's difficult to remember what you've spoken on at each place you've been, so you'd like to maintain records of your past talks and use them to help you plan future engagements.
If you return to a place where you've spoken before, you don't want to give a talk similar to one you've already delivered there, and a record of each speech would help you avoid repeats. You'd also like to note how well your talks are received. (Your address "Why I Love Cats" at the Metropolitan Kennel Club was something of a dud, and you don't want to make that mistake again the next time you're there.)
. You're a teacher who needs to keep track of grades and attendance. Each time you give a quiz or a test, you record every student's grade. It's easy enough to write down scores in a gradebook, but using the scores later is a tedious chore.
You'd rather avoid sorting the scores for each test to determine the grading curve, and you'd really rather not add up each student's scores when you determine final grades at the end of the grading period. Counting each student's absences is no fun, either.
. The organization for which you are the secretary maintains a directory of members. (The organization could be anything-a professional society, a club, a repertory company, a symphony orchestra, or an athletic booster club.) You generate the directory in printed form each year for members, based on a word processor document that you edit as membership information changes.
You're tired of maintaining the directory that way because it limits what you can do with it. It's difficult to sort the entries in different ways, and you can't easily select just certain parts of each entry (such as a list consisting only of names and phone numbers).
Nor can you easily find a subset of members, such as those who need to renew their memberships soon—if you could, it would eliminate the job of looking through the entries each month to find those members who need to be sent renewal notices.Also, you'd really like to avoid doing all the directory editing yourself, but the society doesn't have much of a budget and hiring someone is out of the question.
You've heard about the "paperless office" that's supposed to result from electronic record keeping, but you haven't seen any benefit from it. The membership records are electronic, but, ironically, aren't in a form that can be used easily for anything except generating paper by printing the directory!.
. You are a Librarian. You want to maintain records of book easily so that anyone search the books available in the library through internet and order the books. It will also help you to keep your book stock updated and improve your productivity and response time.
. There are endless scenarios where you can think to use a RDBMS product.
These scenarios range from situations involving relatively small amounts to large amounts of information. They share the common characteristic of involving tasks that can be performed manually but that could be performed more efficiently by a database system.
How MySQL can help you?
What specific benefits should you expect to see from using a database system such as MySQL? It depends on your particular needs and requirements-and as illustrated by the preceding examples, those can vary quite a bit. Let's look at a type of situation that occurs frequently and so is fairly representative of database use.
Database management systems are often employed to handle tasks such as those for which people use filing cabinets. Indeed, a database is like a big filing cabinet in some ways, but one with a built-in filing system.
There are some important advantages of electronically maintained records over records maintained by hand. For example, if you work in an office setting in which client records are maintained, the following are some of the ways MySQL can help you in its filing system capacity:
. Reduced record filing time You don't have to look through drawers in cabinets to figure out where to add a new record. You just hand it to the filing system and let it put the record in the right place for you.
. Reduced record retrieval time When you're looking for records, you don't search through each one yourself to find the ones containing the information you want. Suppose you work in a dentist's office.
If you want to send out reminders to all patients who haven't been in for their checkup in a while, you ask the filing system to find the appropriate records for you. Of course, you do this differently than if you were talking to another person to whom you'd say, "Please determine which patients haven't visited within the last 6 months." With a database, you utter a strange incantation:
. SELECT last_name, first_name, last_visit FROM associate WHERE last_visit < DATE_SUB(CURDATE(),INTERVAL 6 MONTH);
That can be pretty intimidating if you've never seen anything like it before, but the prospect of getting results in a second or two rather than spending an hour shuffling through your records should be attractive.
. Flexible retrieval order. You needn't retrieve records according to the fixed order in which you store them (by associates last name, for example). You can tell the filing system to pull out records sorted in any order you like-by last name, insurance company name, date of renewal , and so on.
. Flexible output format. After you've found the records in which you're interested, there's no need to copy the information manually. You can let the filing system generate a list for you. Sometimes you might just print the information.
Other times you might want to use it in another program. Or you might be interested only in summary information, such as a count of the selected records. You don't have to count them yourself; the filing system can generate the summary for you.
. Simultaneous multiple-user access to records. With paper records, if two people want to look up a record at the same time, the second person must wait for the first one to put the record back. MySQL gives you multiple-user capability so that both can access the record simultaneously.
. Remote access to and electronic transmission of records. Paper records require you to be where the records are located or for someone to make copies and send them to you. Electronic records open up the potential for remote access to the records or electronic transmission of them.
If your dental group has associates in branch offices, those associates can access your records from their own locations. You don't need to send copies by courier. If someone who needs records doesn't have the same kind of database software you do but does have electronic mail, you can select the desired records and send their contents electronically.
If you've used database management systems before, you already know about the benefits just described, and you may be thinking about how to go beyond the usual "replace the filing cabinet" applications. The manner in which many organizations use a database in conjunction with a Web site is a good example.
Suppose your company has an inventory database that is used by the service desk staff when customers call to find out whether you have an item in stock and how much it costs. That's a relatively traditional use for a database. However, if your company puts up a Web site for customers to visit, you can provide an additional service-a search page that allows customers to determine item pricing and availability.
This gives customers the information they want, and the way you provide it is by searching the inventory information stored in your database for the items in question-automatically. The customer gets the information immediately, without being put on hold listening to annoying canned music or being limited by the hours your service desk is open.
And for every customer who uses your Web site, that's one less phone call that needs to be handled by a person on the service desk payroll. (Perhaps the Web site can pay for itself this way?)
But you can put the database to even better use than that. Web-based inventory search requests can provide information not only to your customers but to your company as well. The queries tell you what customers are looking for, and the query results tell you whether or not you're able to satisfy their requests.
To the extent that you don't have what they want, you're probably losing business. Consequently, it makes sense to record information about inventory searches-what customers were looking for and whether you had it in stock. Then you can use this information to adjust your inventory and provide better service to your customers.
Another Web-based application for databases is to serve up banner advertisements in Web pages. I don't like them any better than you do, but the fact remains that they are a popular application for MySQL, which can be used to store advertisements and retrieve them for display by a Web server.
In addition, MySQL can perform the kind of record keeping often associated with this activity by tracking which ads have been served, how many times they've been displayed, which sites accessed them, and so on. So how does MySQL work? The best way to find out is to try it for yourself, and for that we'll need a database to work with.



Post a Comment

Circle Me On Google Plus


Follow Us