Jul/090
What is a Database
Definitions:
A database is a collection of information organized into interrelated tables of data and specifications of data objects.
A table in a relational database is a predefined format of rows and columns that define an entity.
Database tables are composed of individual columns corresponding to the attributes of the object.
In a relational database, a row consists of one set of attributes (or one tuple) corresponding to one instance of the entity that a table schema describes.
Also Known As: Record
A single data item related to a database object. The database schema associates one or more attributes with each database entity.
Also Known As: field, column
A database record consists of one set of tuples for a given relational table. In a relational database, records correspond to rows in each table.
Databases are designed to offer an organized mechanism for storing, managing and retrieving information. They do so through the use of tables. If you’re familiar with spreadsheets like Microsoft Excel, you’re probably already accustomed to storing data in tabular form. It’s not much of a stretch to make the leap from spreadsheets to databases. Let’s take a look.
Database Tables
Just like Excel tables, database tables consist of columns and rows. Each column contains a different type of attribute and each row corresponds to a single record. For example, imagine that we were building a database table that contained names and telephone numbers. We’d probably set up columns named “FirstName”, “LastName” and “TelephoneNumber.” Then we’d simply start adding rows underneath those columns that contained the data we’re planning to store.
If we were building a table of contact information for our business that has 50 employees, we’d wind up with a table that contains 50 rows.
Databases and Spreadsheets
At this point, you’re probably asking yourself an obvious question – if a database is so much like a spreadsheet, why can’t I just use a spreadsheet? Databases are actually much more powerful than spreadsheets in the way you’re able to manipulate data. Here are just a few of the actions that you can perform on a database that would be difficult if not impossible to perform on a spreadsheet:
* Retrieve all records that match certain criteria
* Update records in bulk
* Cross-reference records in different tables
* Perform complex aggregate calculations
Jun/090
Reasons to Migrate from Microsoft Access to MySQL
Use of MySQL as a storage manager for Access offers several benefits. One is that you can use your information in additional ways when it’s not locked into Access. Other differences pertain more specifically to the case where you intend to continue using Access as the user interface to your information.
Deployment of information. When your information resides in MySQL, you’re free to continue using it from Access if you wish, but a number of other possibilities open up as well. Any kind of MySQL client can use the information, not just Access. This allows your data to be exploited more fully in more contexts, and by more people. For example, other people can use the data through the standard MySQL client programs or from GUI-based applications. Your database also becomes more accessible over the Web. Access now provides some capabilities for making a database available on the Web, but if MySQL manages the database, you have a wider range of options. MySQL integrates easily with Web servers like Apache through any of a number of languages, such as Perl, PHP, Python, Java, and Ruby. This allows you to provide a Web interface to your database with the language of your choice. In addition, the interface can be accessed by browsers on many types of machines, providing a platform-independent entryway to your information. All of these components can be obtained for free–MySQL, Apache, and the languages just mentioned have been released as Open Source. You can also obtain them in packages that include support.
Multiple-user access. Although Access provides some data sharing capabilities, that is not really its strength. It has the feel of a single-user data manager designed for local use. MySQL, on the other hand, easily handles many simultaneous users. It was designed from the ground up to run in a networked environment and to be a multiple-user system that is capable of servicing large numbers of clients.
Management of large databases. MySQL can manage hundreds of megabytes of data, and more. Care to try that with Access?
Security. When Access tables are stored locally, anyone can walk up to your Windows machine, launch Access, and gain access to your tables. It’s possible to assign a database a password, but many people routinely neglect to do so. When your tables are stored in MySQL, the MySQL server manages security. Anyone attempting to access your data must know the proper user name and password for connecting to MySQL.
Backup management. If you work in an organization that supports many Access users, migrating data to MySQL provides a benefit for backups and data integrity. With Access databases centralized in MySQL, they’re all backed up using the regular MySQL backup procedures that already exist at your site. If individual Access users each store their data locally, backup can be more complicated: 50 users means 50 database backups. While some sites address this problem through the use of network backups, others deal with it by making backups the responsibility of individual machine owners–which unfortunately sometimes means no backups at all.
Local disk storage requirements. Local Access database files become smaller, because the contents of tables are not stored internally, they’re stored as links to the MySQL server where the tables reside. This results in reduced local disk usage. And, should you wish to distribute a database, less information need be copied. (Of course, anyone you distribute the database to also must have access to the MySQL server.)
Cost. MySQL can be obtained for free. Access cannot. Providing other means of using your database (such as through a Web interface) can reduce your dependence on proprietary software and lower your software acquisition and licensing costs.
Hardware choices. MySQL runs on several platforms; Access is a single-platform application. If you want to use Access, your choice of hardware is determined for you.