|
14 Database Basics
In the simplest of terms, a database is storage for data organized in a searchable manner. A database consists of one or more tables. A database table contains one or more fields. A database field contains actual data, which can be any of the supported database types and can vary in size. Data stored in a table’s row is considered a record. A database record can contain all, some, or one of a table’s fields. Records can be added, deleted, or otherwise modified within a table. Databases must be able to support thousands of these records.
14.1 Relational Databases
Relational databases are the world standard for database architecture. With a relational database, data is stored in a two-dimensional array, or table. The table below shows a database table named employee_data:
The first row of the table contains the field names (Employee_Number, First_Name, etc.). The remaining rows contain the table data.
The first column of this table uses a data type called AutoNumber, which automatically increments the number of each new row. All of the other fields are of the text data type.
The real power of a relational database lies in the ability to link multiple tables to one another. Suppose you have a business in which you want to track individual salesmen, but you don’t want to insert all of their data when a sale is made. We may have the above table in our database, along with another named sales. For this example, our sales table will contain the Employee_Number.
The graphic below displays the relationships between the employee_data and the sales tables within your database:
The line in this illustration indicates that there is a one-to-many relationship between the employee_data and the sales tables. This means that a particular value of Employee_Number will appear once in the employee_data table but many times in the sales table.
This allows us to insert many sales using only the salesperson’s employee number and not all of their data. The end result is a smaller database and faster retrieval of data from the database. This relationship also allows you to set properties about it.
In the following screen shot, we have chosen to “Enforce Referential Integrity.” This option allows you to cascade updates and deletes.
When “Cascade Update Related Fields” is checked, updates to one field will cause the other to update as well.
For example, if you change an employee’s number in the employee_data table, the number will also be changed wherever it occurs in the sales table.
Similarly, the “Cascade Delete Related Records” option allows you to remove all the records from the sales table where the Employee_Number is the same as the one that you delete from the employee_data table.
14.2 SQL Boot Camp
Structured Query Language (SQL) is a language designed for accessing and manipulating data within a database. Each database provider, such as Microsoft Access, has its own dialect of SQL, but most implementations support the most basic statements, which are covered here.
Although the SQL language is not case sensitive, all keywords will be displayed in ALL CAPS to distinguish them from the data. The following table summarizes the major SQL keywords and their uses.
To illustrate the correct uses of these keywords, we will refer to the employee_data table in the next section.
14.2.1 The SELECT Statement
Suppose you want to see all of the records contained in the table. We would need to execute the following SELECT query. Words in all uppercase represent SQL keywords.
The above query would return the entire table, as shown in Table 19-2.
There are several components that make up an SQL query. The first word always represents the action that the SQL statement will perform. The word that follows the keyword FROM is the table from which we want the data to be returned.
In the above query, the asterisk specifies that we want to see all of the fields in the table. We can also perform a query to return specific fields. Suppose that we would like to see just the first and last name of all of the employees in the table. The query that we would need to perform would look like:
SELECT First_name, Last_name from employee_data
As in the above query, commas must be used to separate multiple fields.
This query would return the following results:
If we were interested in seeing which employees had the first name bob, we would prepare the following query:
SELECT First_name, Last_name FROM employee_data WHERE First_name = ‘bob’
The above query returns the record shown in Table 19-3 below:
Note that we have introduced a new SQL keyword. The WHERE keyword is important for displaying a specific record. Also, you will notice that the text of the WHERE clause is surrounded by single quotation marks. Single quotation marks are how SQL qualifies text.
Pitfall: A common pitfall is to have an apostrophe in the text that the WHERE clause is trying to narrow down to. An example of this would be if we were searching for a last name of O`Sullivan. We would think that the query would look like:
SELECT First_name, Last_name FROM employee_data WHERE First_name = `O`Sullivan`
However, we must double each single apostrophe in our WHERE clause like so:
SELECT First_name, Last_name FROM employee_data WHERE First_name = `O``Sullivan`
We can use a built in VBScript function to take care of this. An example of this would be:
<%
Dim SQLStatement
SQLStatement= "SELECT First_name, Last_name " &_
"FROM employee_data " &_
"WHERE First_name = `" & replace ("O``Sullivan","`","``") & "`"
%>
This would allow us to now execute this SQL query. We will discuss later how to actually execute this query against a database.
14.2.2 The UPDATE Statement
The UPDATE statement allows you to update existing values within a table, and depends heavily on the WHERE clause. Suppose that we want to update Bob Smith’s name to Robert Smite. To do so, we could perform the following query:
UPDATE employee_data SET First_Name = ‘Robert’, Last_Name = ‘Smite’ WHERE Employee_Number = 1
Note that the UPDATE query begins with the UPDATE statement, followed by the table name that we are updating; in this case, employee_data.
The next item in the query is the keyword SET, which is followed by a comma-delimited list of the fields and values that we wish to update. In this example we are updating both the first and last name.
Finally, the WHERE keyword limits the query to a specific record. In this example the UPDATE query applies only to the record of employee number 1.
After executing this query, the employee_data table contains the following data:
By using the WHERE clause, we ensured that only the name of the President would be updated. Had we left out the WHERE clause, the table would have contained the following values:
This is certainly not the outcome that we wanted. However, if you add a column to a database that already contains data, you may want to use the UPDATE statement to set an entire column in your database to a default value.
14.2.3 The INSERT Statement
The INSERT statement allows you to add new data to your database, and is used heavily in a Web application environment.
Suppose that we hired a new employee named Steve Wilson. Steve is going to be running our marketing and sales departments for us, so he will have the title of Vice President of Sales and Marketing. In order to add Steve to our staff list, we would have to execute the following SQL query:
INSERT INTO employee_data (First_name, Last_Name,Title) VALUES (`Steve`,`Wilson`, `VP Sales and Marketing`)
In the above query, the INSERT statement comes first. The second word in the query is the keyword INTO, followed by the name of the table that we want to insert data into and the (optional) field names.
Finally, the VALUES keyword lets SQL know that the values that follow are to be inserted into the table. A comma must separate each value.
Because Employee_Number is a Microsoft Access AutoNumber data type, we do not need to specify a value for it. An AutoNumber automatically increments by one number when a new row is added.
When executed, the above query produces the following table:
As previously mentioned, all text values must be surrounded by single quotation marks. Numerical values do not require quotation marks.
The values must follow the same order that we define the values in the INSERT statement, but the INSERT statement does not have to follow the same order that the columns are defined as within the table. Therefore, we could have achieved the same results using the following query:
INSERT INTO employee_data (Title, First_name, Last_Name) VALUES (`VP Sales and Marketing`,`Steve`,`Wilson`)
Also, because we were inserting all of the values for the table, we could have shortened the above SQL statement to the following:
INSERT INTO employee_data VALUES (`Steve`,`Wilson`,`VP Sales and Marketing`)
When we execute a SQL statement without the optional table information, the order of the values must follow the same order of the column names within the table.
14.2.4 The DELETE Statement
The DELETE statement allows you to remove data from a database. Using the DELETE statement, you can delete a single row or the entire contents of a table.
Example: Suppose Jim Cannon has left the company. Since he is no longer an employee, we must remove him from our database. To perform this action, we could perform the following SQL query:
Note that this query begins with the DELETE keyword. The second keyword, FROM, tells us from what table we are going to be deleting data from, in this case the employee_data table.
The WHERE clause lets us know that we are only going to delete a specific row in the table. If no row is specified, no action will be taken on the database.
After executing the above command, our table would look like the following:
Notice that the data for Jim is gone. This query let us remove all of the data about him. Depending on the circumstances, we may also wish to get rid of any sales data that he may have made.
If we modify the above query, we can get the DELETE query to delete all of the data from our table. To do this we would need a query that looked like
This query is similar to the above query but we have left off the WHERE clause. Our table will now look like the following:
As expected, our table has had all of its data removed. This may be useful if we have made too many errors in our table data, and we want to start over. However, the table still exists in our database. If we wanted to remove this table from our database we would have to construct a slightly different DELETE query.
14.3 Advanced SQL
Now that we have looked at SQL basics, we are going to examine two more advanced topics that will help in your database development. The following are the two advanced topics that we are going to cover:
There are many more topics that may be specific to your database provider. Please consult your database provider to see the exact usage of these and other SQL commands that are available.
14.3.1 CREATE TABLE
The CREATE TABLE query allows you to create a table dynamically. An example of when we may want to use this is when we want to add a new table for product discussions when we add a product to our offerings. Suppose we want to create such a table for our new ultra widget. To perform this action we would create the following SQL query:
CREATE TABLE ultra_widget (tblindex INTEGER PRIMARY KEY, message Text (255))
Our CREATE TABLE query begins with two important keywords, followed by the name of the table we wish to create. (NOTE: A valid table name cannot start with a number or contain any spaces. Please check with your database engine for any additional rules concerning table names).
The rest of the query contains information about the fields to be inserted into the table. In this example, the first field (tblindex) is specified as the PRIMARY KEY for the table. It is important to include a primary key in each table, as they allow you to distinguish one row from another. Some fields work better as primary keys than do others; a social security number is a good example of a primary key. The main point is that the primary key should be unique to every record in the table. Some database providers have a built in auto number field that automatically increments when a field is inserted. Auto number fields are most useful when there are no fields that would normally be unique for every row of the table.
All fields must be separated by a comma (,). The second field of our example contains a message about the "ultra widget" product. The message field is a Text type field, and is limited to 255 characters.
It is best to avoid using keywords as field names. You should check with your database provider to determine which keywords are used, as well as valid field names and field data types for your database.
14.3.2 DROP TABLE
Suppose you have created a table that contains information about a platinum widget that you no longer produce. You now need to remove the table that contained the discussion information about that product. The query to remove the platinum_widget table would appear as:
When executed, this statement will remove the entire table. Therefore, all the data inside that table will be lost and any queries or relationships that reference this table will be broken.
| 
