What Is a Database?


In 1970, E. F. Codd, an employee with IBM, proposed his idea for what would become the first relational database design model. His model, which proposed new methods for storing and retrieving data in large applications, far surpassed any idea or system that was in place at that time. His idea of "relational" stemmed from the fact that data and relationships between them were organized in "relations," or what we know today as tables. Even though Codd's terminology of what we refer to as tables, columns, and rows was different, the premise behind the relational model has remained consistent. Although the model has undergone revisions and changes over the past 35 years, the idea of storing and retrieving information in large applications has not changed, solidifying the need for the relational database model.

The best way to think of a database is in terms of a filing cabinet. The filing cabinet contains drawers, the drawers contain folders, and the folders contain documents that have information on them. A database is similar in concept. A database contains drawers, otherwise known as tables; those tables contain folders, or columns, which in turn contain rows of information pertaining to the particular column that they're in.

For a moment, let's take the web store example (crudely outlined in Chapter 21, "Introduction to Web Applications") and break it down to see exactly what kind of information we would need and just how we could break it up to make it manageable with a database.

  • Customers: We need some way of keeping track of all our registered customers, along with shipping/billing addresses, credit card information, and so forth.

  • Products: Some way of differentiating between all our products is necessary, including sizes, colors, prices, quantities left in stock, and other characteristics that relate to a specific item.

  • Orders: Whenever a product is purchased from the online store, that order should be stored somewhere in a queue of sorts so that the shipping and receiving department can process the order.

  • Transactions: We need to include a history of all transactions and a way of knowing which customers are ordering what so that we can recommend products to people dynamically in the future.

Traditionally, we could take all these elements and create a Word document or perhaps a spreadsheet in Excel and physically write on these documents whenever someone orders a product. We could take these documents and store them in folders alphabetically and even store all the folders within one central filing cabinet. Although this is a traditional example of how business can work, it closely resembles how the modern database operates in relation. The filing cabinetthe drawers, folders, and even the documents within themall represent the basic components of a modern database structure:

  • Database management system

  • Database

  • Tables

  • Columns

  • Rows

Let's outline each component with more detail.

The Database Management System (DBMS)

The database management system (DBMS) represents the framework from which you design, store, and manage all the databases that you create. Figure 23.1 shows the SQL Web Data Administrator. The Web Data Administrator is a centralized location for managing and interacting with all your MSDE databases.

Figure 23.1. SQL Web Data Administrator is a simple, web-based, database management system (DBMS).


Although smaller databases such as Access do not have what is traditionally known as a DBMS, Access does provide a way of interacting with and managing a single database file. Figure 23.2 shows how you can open a database through Access.

Figure 23.2. Access does not provide a typical DBMS, but it does allow for access to any single database file.


If you look at the Dorknozzle store example again, you can begin to imagine how the DBMS looks much like the filing cabinet discussed earlier. Unlike a filing cabinet, however, which typically contains two to four drawers, a DBMS can manage hundreds, possibly thousands of databasesall of which are immediately at your fingertips. Later in this chapter, we'll look at the various database management systems that exist on the market today for interacting with MSDE and MySQL, including the File Manager for Access.

The Database

Inside your DBMS, you have the potential for storing hundreds, if not thousands of databases. Although for most projects you would never need more than one database, you may in the future realize that your project has grown far beyond the scope of a single databasethat because of security or maintenance reasons, you require more. Figure 23.3 shows MySQL Administrator with a list of three catalogs (otherwise known as databases) housed within its framework.

Figure 23.3. MySQL Administrator and a list of the databases it contains.


Tables

After a database has been created, you might want to begin storing information relevant to a specific part of the store. As mentioned earlier, tables are very similar to file cabinet drawers. It would be a mistake to store all the information about inventory, product information, customers, and even transactions in one drawer; instead, you'll break these categories of information out and create different drawers or tables to store all this information.

Figure 23.4 shows the Dorknozzle store database in MySQL Administrator. By selecting the Dorknozzle store database from the view in the left column, you can begin to see all the tables that can reside within the store.

Figure 23.4. Selecting the Dorknozzle store database reveals all the tables associated with the project.


Notice that there are more tables than just the four outlined in the beginning of the chapter. If you think in terms of space and redundancy, you will see exactly why you need to include more than just a few tables. As far as the Employees table is concerned, you could have a customer that has multiple credit cards on filehence the need for a separate CreditCards table. You will also have customers/employees who belong to a specific departmenthence the need for a separate Departments table. We could even go beyond this example and create a separate table for CreditCardTypes, assuming a customer could use more than one credit card. This process of organizing data in an effort to avoid data duplication within tables is known as normalization and is discussed in depth toward the end of this chapter.

Before you begin any project, you will typically sketch all this out in an effort to reduce data duplication in your tables. Again, how you branch out your information and create tables depends on how big in scope your project is.

Columns

After you outline all your tables, your next step is to decide what information to include within those tables. For instance, you might want to include first name, last name, phone number, address, city, state, zip, and so on for all the employees in your company in the Employees table. You might also need to include product names, descriptions, and some sort of unique identification in your EmployeeStore table. You might even want to combine certain aspects of certain tables and place them into the Orders tablefor example you might end up with information from the Employees table as well as from the EmployeeStore table to come up with a final order requisition.

Theoretically, columns represent bits of information or more detailed descriptions of the table in which they are contained. Just as you have an Employees table, all employees must have names and physical addresses. Just as you have an EmployeeStore table, all products must have names and descriptions. Figure 23.5 shows what the Employees table might look like after columns have been outlined.

Figure 23.5. The Employees table displays all the columns associated with it.


Rows

Think back to the example I mentioned earlier regarding the documents within the folders and the folders within the drawers contained within the filing cabinet. Rows represent the actual data in those documents. Similar to the columns within the tables, rows represent the actual data within the columns. When employees/customers begin purchasing items, the rows in the Orders table begin to expand and fill up with information, similar to Figure 23.6.

Figure 23.6. The Orders table with multiple rows of information.


Database tables have the potential for containing millions of rows. Technically, this is your data. The many rows of information contained in your database tables are what you'll ultimately display in your web applications. Whether you're displaying product information for employees to select, order information for the shipping and receiving department to review, or employee information for administrators to configure, the rows in your databases tables and the data contained within those rows are what you'll ultimately be interacting with inside the web application.

Beyond the Basics

Now that we've gotten the basic structure of a database out of the way, let's begin thinking about what really drives the database. Aside from the data in the tables, other characteristics and functions of the database can improve performance, reduce network traffic, increase security, lower development time, and dramatically decrease maintenance efforts. Some of these functions and characteristics are listed next:

  • Stored procedures

  • Triggers

  • Views and queries

  • Security

  • Relationship management

  • Keys

  • Normalization

It's important to understand that these concepts are not relevant to all databases. For instance, concepts such as stored procedures, triggers, views, security management, relationship management, and keys are all relevant to MSDE and SQL Server 2000. Access, being the proverbial little brother of MSDE/SQL Server 2000, supports only queries, security management, relationships, and keys. In contrast, MySQL is extremely limited when it comes to these concepts. For instance, the latest recommended release of MySQL 4.1 supports only security management and keys.

NOTE

The newest beta build of MySQL 5.0 hopes to eclipse the lack of extended database features in MySQL 4.1. For instance, MySQL 5.0 now supports stored procedures, views, and more. Unfortunately, it's a beta build and shouldn't be installed on production database servers.


Stored Procedures

Stored procedures are a way of actually storing code that you use to work with your database in the database itself. They are a way of modularizing repetitive code so that you never have to write the same line of code in your applications more than once. You create a stored procedure within your database and call it through your application, passing in parameters as necessary. In return, the stored procedure executes complex tasks and can return information back to the application that is calling it.

Let's use the Dorknozzle store as an example. In our application, we might want to create two ways of updating registered users within our database. From a user's standpoint, the customer might want to edit existing passwords, personal information, or perhaps shipping information. From an administrative standpoint, the admin might also want the ability to update a given user's information. Although the front-end user interface will look completely different for the users and administrator, the code that accesses the database and performs the actual data modification can be exactly the same. This concept is outlined visually in Figure 23.7.

Figure 23.7. Stored procedures allow you to consolidate repetitive functionality by modularizing it into a single function exposed on the database server.


By creating a stored procedure, we would essentially eliminate the arduous task of writing repetitive code for both user and administrator instances. We simply write it once as a stored procedure and allow the user and administrator to access the stored procedure the same way.

Triggers

Triggers, which are similar to stored procedures, can be set up to run with your database data. Triggers are predefined events that run automatically whenever a specified action (preferably Insert, Delete, or Update command) is performed on a physical file. Although it might sound a bit confusing as to what triggers actually are and what they can do, think of triggers as a way of enforcing business rules you may have described within your database. Triggers enforce stability and integrity within your tables.

For example, in the Dorknozzle database you'll have a table for employee orders, called Orders. If the employee ended a relationship with the Dorknozzle store, you would have the potential for orders in the Orders table that are not associated with any employee. Triggers would enforce business rules by making sure that if an employee ended a relationship with the Dorknozzle store (maybe they were fired), not only is their information in the Employees table deleted, but also the data in the Orders table (which had a direct relationship with data in the Employees table) would be deleted as well.

Views and Queries

Views (MSDE) and queries (Access) are awkward to think about at first because their names are deceiving. Views and queries aren't actually what their names imply; rather, they are virtual tables whose contents are defined by a query. Much like a real table with rows and columns, views and queries exist as stored sets of data values. Rows and columns of data come from the tables that are referenced and are produced dynamically by the database when the view or query is called from the application.

For example, you could have multiple databases set up throughout your companyone for sales, one for marketing, and possibly one for operations. You could use views and queries to combine similar data from all those databases to produce a virtual table with sales numbers, marketing reports, and even information from operations. After the query/view has been created, the information is easily accessible by your web application.

Security

Security is always important to any facet of development, not just web development. Ensuring that your database is secure and accessible only by certain individuals or departments is crucial. Many database management systems provide a way to set security options for users and groups of users who are allowed to access the database either individually or within their own web applications. Figure 23.8 shows how you could modify permissions for specific users using MySQL Administrator.

Figure 23.8. User administration is easy using MySQL Administrator.


Access, on the other hand, enables you to modify security settings by right-clicking the database file, selecting Properties, and choosing the Security tab, as shown in Figure 23.9.

Figure 23.9. Adding users and permissions to an Access database file.


Access itself allows you to control permissions for a particular database file. You can modify permissions for a database file by first opening the database file. Next, select Tools, Security, and User and Group Permissions. The User and Group Permissions dialog allows you to specify which users get Read and Modify permissions or no permissions at all. You can even modify or set permissions for specific tables, queries, forms, and reports.

Relationship Management

When you create new tables in your database, an important aspect to consider is that of relationships. We have already touched on what relationships are and how they relate to your tables. For example, you could create a separate table for credit cards and assign that table a relationship with the Employees table. The reason for doing this is simple: It allows you to store more than one credit card for a particular employee. In this scenario, we'd simply create a separate table for credit cards and assign each row in that column a unique identifier, usually an automatically generated number. The relationship would exist between the unique identifier in the Employees table (CustomerID) and that identifier in the CreditCards table. Figure 23.10 shows a relationship between the Employees table and the CreditCards table using Access's relationship modeler.

Figure 23.10. Relationships are added to avoid data duplication in tables.


In general, when you work with relationship modeling, three types of relationships exist:

  • One-to-one: A one-to-one relationship means that for each record that exists in one table, only one other related record can exist in another table. One-to-one relationships are rarely used, and when they are, it's usually because of a limitation with the database that requires data to be stored separatelyusually because of the database's size.

  • One-to-many: A one-to-many relationship is by far the most common of relationship types. A one-to-many relationship means that for each record in one table, multiple records can exist in a second table. These records are usually related based on a unique number. In the employees/credit cards example I mentioned earlier, a one-to-many relationship created a relationship between one customer and the many possible credit card numbers that could be stored in a second (credit cards) table.

  • Many-to-many: A many-to-many relationship exists when many records in one table are related to many records in a second table. Many-to-many relationships are difficult to illustrate in a typical relational database model and are not often used in practice.

Keys

Many of the records in your database will contain information that is very similar in nature. You might have a thousand customers in your Employees table, and a hundred of those customers might be from San Diego. If you extracted all those records from the database, how would you be able to differentiate between all the records? Obviously you could differentiate by name, but what if you had three records in the database with the name John Smith from San Diego? A way to differentiate is through the use of unique keys.

Think about why uniqueness is so important. If you had more than one record in the database that was the same, what would be the sense in storing multiple copies? It would be a waste of space. Also, if you tried to update or delete a record from a database that matched a second record, the database would not be able to match the record you were trying to work with and you might end up deleting the wrong record, throwing an error, or corrupting the data in your tables. Records can be identified through the use of three different kinds of keys:

  • Candidate keys: A candidate key is a set of columns that are unique across the board. Take the following example:

    Zip

    Area

    92069

    San Marcos

    92115

    San Diego

    92105

    San Diego

    92128

    San Diego


    In this example, the Zip column could be considered a candidate key because the values never repeat. Although the Area names do repeat, together with the Zip value, they become unique and can make up a candidate key. Because the Area column contains repetitive information, it cannot be considered for a candidate key and could never be unique.

  • Primary keys: Whereas candidate keys can be made up of several columns, a primary key is usually made up of a single column that designates a row in the table as unique. For the most part, primary keys can exist even though they have no relationship to the data being stored. Database developers often create primary keys with an automatically generated number, guaranteeing that the row always increments by 1 and remains unique from any other records. Primary keys are the most useful when referenced from a second table through the use of foreign keys. The table that follows illustrates a simple table within a database that contains information about a user's area. Because the primary key is different, the records remain completely unique even though data within the Area column repeats.

    AreaPK

    Area

    1

    San Marcos

    2

    San Diego

    3

    San Diego

    4

    San Diego


  • Foreign keys: A foreign key is a column that contains values found in the primary key of another table. A foreign key can be null and almost always is not unique. Consider the following example:

    ZipPK

    ZipCode

    1

    92069

    2

    92115

    3

    92105

    4

    92128


    AreaPK

    AreaName

    ZipFK

    1

    San Diego

    3

    2

    San Diego

    2

    3

    Tijuana

    4

    San Marcos

    1

    5

    San Marcos

    1

    6

    San Diego

    4

    7

    San Diego

    4


    The ZipFK column in the second table is a foreign key to the ZipPK primary key in the first table. Notice that the ZipPK values are unique and not null, but the ZipFK values might be null and often repeat. A null foreign key means that row does not participate in the relationship. In a one-to-many relationship, the primary key has the "one" value, and the foreign key has the "many" values.

Normalization

As discussed earlier in the chapter, normalization is the process of organizing data in an effort to avoid duplication. Often this process involves separating data into discrete related tables. Advantages to normalization usually include space, performance, and easier maintenance.

Typically, normalization involves the process of identifying all the data objects that should be in your database, all their relationships, and defining the tables required and the columns within each table. Consider how the EmployeeStore table would look if we did not normalize the data into separate tables:

Customer

Order

Price

Zak

Shirt

$12

Patty

Shirt

$12

Zak

Pants

$35

Makenzie

Shoes

$75

Jessica

Blouse

$20

Judy

Shoes

$75

Jessica

Blouse

$20


If the preceding table was used specifically to keep track of the price of items and you wanted to delete a price, you would end up deleting an employee as well. Instead, you could separate the employees into their own table and the products along with their prices into a second table. If a specific employee orders a product, the product and its price are placed into a third Orders table along with the corresponding customer data referenced by a one-to-many relationship.

For the most part, normalization isn't a feature of the database, rather, it's a practice you should follow. On that point, there are roughly four normalization forms that define how data is laid out within a database:

  • The First Normal Form: The first normal form states that all rows in a table must contain different data. No duplicate rows are permitted. It also states that all entries in a specific column must be of the same typefor instance, a column named Customer must contain only names of customers.

  • The Second Normal Form: The second normal form states that no field can be inherited from another field. For example, if you store the full name of a customer in the Employees table, you cannot create a second field to store only the last name of a customer, because the data would be redundant.

  • The Third Normal Form: The third normal form states that duplicate information is not allowed in the database. This is the model you achieved in the foreign key example. Instead of storing the credit cards of a customer within the Customers table, you separate that information out into a second table, allowing for multiple credit cards to be entered.

  • Domain/Key Normal Form: A domain/key normal form states that a key uniquely identifies each row in a table. By enforcing key restrictions, the database is freed of modification irregularities. Domain/key normal form is the normalization form that most database developers try to achieve.




Macromedia Dreamweaver 8 Unleashed
Macromedia Dreamweaver 8 Unleashed
ISBN: 0672327600
EAN: 2147483647
Year: 2005
Pages: 237
Authors: Zak Ruvalcaba

flylib.com © 2008-2017.
If you may any questions please contact us: flylib@qtcs.net