Databases Reference
In-Depth Information
FOR EXAMPLE
Comparing Data Storage Formats
A DBMS stores data in an operating system file. The total number of files
involved and how they are structured depends on the particular DBMS. You
can get an idea of common variations by comparing popular examples.
Our first example is Microsoft Access. Access uses a relatively simple
storage structure, creating a single file when you create the database. The
database objects are contained in this one file. This has its advantages, such
as making the database extremely portable, but it is also limited, with few
options for managing storage. For example, there is little you can do with
the database file to improve performance.
MySQL is an open source application with versions available that you
can download and use free of charge. There are also for-purchase versions
that include additional tools and utilities. It is a complete RDBMS product
and a popular choice for Web-based applications. When you create a MySQL
database, it creates a data folder containing an.opt file, which contains
option settings for the database. Separate data files are created in the data-
base folder for each table and view that you create in the database (a
view
is a database object that supports custom data retrieval). One drawback of
this storage system is that you must deal with a large number of separate
files when managing a large, complicated database.
MySQL also shares log files between databases. Log files are used in most
database systems to store errors, informational messages, database activity,
and other database information. These shared log files include a log that
tracks statements executed against the database as a way of logging trans-
action activity.
Microsoft SQL Server is one of the most full-featured database products
on the market. It includes a wide range of features and supports something
of an object-relational database model. Different versions, called editions, are
available to support a wide range of database needs.
A SQL Server database is physically made up of at least two and pos-
sibly more files. One required file is the database file and contains the
database objects and data. The other is the transaction log that tracks any
activity that modifies the database. The data storage model is designed so
you can create additional data files as needed and specify the physical file
locations. You can then define the file in which a database object is cre-
ated. This gives you a way of spreading a database across multiple hard
disks. You have control over how the space is used and, by sharing the
work between multiple drives, a way of improving database performance
and I/O throughput.
