| What is so special about a database, you might ask? There are other ways of storing data, such as a creating a file format of your own design. For example, you might just store information in a comma-delimited text file. However, a database management system provides a lot of advantages, such as the following: Databases allow you to forget about how data is physically stored on the hard drive and just concentrate on the information itself. If you store information in a custom file, any programs that use the file have to know not only what information they are looking for, but also how to get it from the file. Databases can handle multiple users accessing data at the same time, and process user requests as separate units of work. If you just store data in a text file, you need to write code to ensure that multiple programs can access the file at the same time, and that their manipulations of the data do not interfere with each other. Databases provide a way to query data through the use of a query language and application programming interfaces. Databases can be optimized for speed, through the use of indexes and internal tuning algorithms.
As you can see, you would have to write a lot of extra code to make a text file act like a database. Some examples of different types of database systems are Microsoft SQL Server, Oracle, Microsoft Access, Sybase, and the Microsoft Database Engine. What Is a Relational Database? This book discusses a type of database known as a relational database. Relational databases are probably the type of database most widely used in business applications and are very easy to understand. In a relational database, information is stored in tables, which have rows and columns like an Excel spreadsheet. A sample table is shown in Figure 20.1. Figure 20.1. A sample table in a relational database stores information about your CD collection.  Note Each table in the database has a name, which is specified when the table is created. The name of the table in the picture is MyCDs.
The column headers shown in Figure 20.1 are the fields in the table. A field represents one fact, or piece of information. For example, if you wanted to store information about your CD collection, you might want to include fields with names like title, artist, year published, genre, and so on. Fields have a data type and a value, just like variables. In our example, the year published field would be defined as a numeric type, so it could contain values such as 1999, 2000, and so on. The artist field would contain a string of characters that represent the artist's name. Each row in the table, which represents a collection of field values, is known as a record. Each of your CDs would be represented by one record; each record would contain all of the field values relevant to that CD. One important point to note is that even if a particular field does not contain any information (for example, if you do not know the price you paid for a particular CD), that (empty) field is still contained in that CD's record. So, each record in a table has the same field names, but may have different values stored in those fields. A database can hold multiple tables. For example, you may have a database called media that stores information about your CD and DVD collection. The database might contain a table for your CDs (which, in turn, has one record for each CD), another table for your DVDs, and so on. In this scenario, the records in the DVD table may contain different fields than the records in the CD table (for example, your DVD table may contain a field to store the screen aspect ratio, which is not relevant to a CD). This means that each table has its own structure, or definition of what fields comprise the table. Note Many database management systems, such as Microsoft SQL Server, can contain multiple databases.
Queries and Relationships One great feature of a database is its ability to execute queries. For example, you can query the database to retrieve the titles of CDs by artists whose names start with the letter B and were published in 1969. If you were to run this query on the database containing the table pictured in Figure 20.1, you would get the following result: The Red Album Flabby Road As you can see, a query can be used to specify what fields you want (that is, just title) as well as what records you want (published in 1969 and artist names that start with B). Our example query is used to retrieve information from the database. This is the most common type of database query, although later in the chapter you will learn about other queries that can delete or update records as well. Note Queries can be developed externally and sent to the database for execution, or stored within the database. Queries stored within the database are sometimes known as stored procedures, and work like procedures in your VB programs.
Another powerful feature of the relational database model is the ability to establish relationships between tables and then join those tables in a query. Relationships are based on shared fields between tables. For example, suppose we added another table to our database called RecordLabels. It would contain just two fields: artist and record label. We would then be able to join the RecordLabels table to the MyCDs table based on the artist field and ask more complex queries. For example, suppose we wanted to know the titles of the CDs published by Siler records in 1972. To determine the answer to this query, we would have to use information from both tables. As you will see in a moment, joining tables and writing queries using SQL is very easy to do. |
