| Now that we have 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 within the tables, other characteristics and functions within 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 Security Relationship Management Keys Normalization Stored Procedures Stored procedures are a way of actually storing code that you use to work with your database on the database itself. They are a way of modularizing repetitive code so that you never have to write the same line of code within your applications more than once. You simply 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 a recordset of information back to the application that is calling it. TIP When a user makes a request for information from the database, information is returned with a recordset. A recordset is a cluster of information bundled within an object that the developer can then dynamically iterate through to retrieve the data sent back by the database.
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) is performed on a physical file. Although it may 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 that you may have described within your database. They enforce stability and integrity within your tables. TIP Referential integrity refers to the process of setting up tables with rules so that data cannot alter any of your tables without first abiding by those rules.
For example, in the Web store database, you could set up another table for customers to place a listing of all their phone numbers. NOTE For the sake of demonstration, the customers table is set up to accept only one phone number; ideally, you would want a customer to store phone numbers associated with day and evening. Both numbers would be stored in a separate table along with a third table to determine the type of phone number it is. All three of the tables would be connected through a relationship.
If the customer ended a relationship with the Web store, you could have useless phone number information that would not be associated with any customer. Triggers would make sure that if a customer ended a relationship with the Web store, not only their information within the customers table is deleted, but also the relationship with the phone numbers table. Views Views are awkward to think about at first because their name is deceiving. A view isn't actually what it implies; rather, it is a virtual table whose contents are defined by a query. Much like a real table with rows and columns, views exist as a stored set of data values. Rows and columns of data come from the tables that are referenced and are produced dynamically when the view is called. For example, you could have multiple databases set up throughout your company one for sales, one for marketing, and possibly one for operations. You could use a view to combine similar data within all those databases to produce a virtual table with sales numbers, marketing reports, and even information from operations. Security Security is always important to any facet of engineering, not just database development. Ensuring that your database is secure and accessible only by certain individuals or departments is crucial. Many database management systems provide a means for setting security options for users and groups of users. Figure 26.7 illustrates how you could modify permissions for specific users with SQL Server. 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 26.8. Figure 26.7. Adding users and permissions using the SQL Server Enterprise Manager.
Figure 26.8. Adding users and permissions to an Access database file.
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 customers table. The reason for doing this is simple. It allows you to store more than one credit card for a particular customer. Suppose a user wanted to store more than one credit card or even phone number. You would end up duplicating the same information numerous times for one particular customer. This is inefficient and unnecessary. Instead, create a separate table for credit cards and assign each row within that column a unique identifier, usually an automatically generated number. The relationship would exist between the unique identifier within the customer table (CustomerID) and that identifier within the credit cards table. Figure 26.9 displays a relationship between the customers table and the credit cards table. Figure 26.9. Relationships are added to avoid data duplication within tables.
Three types of relationships exist between database tables: One-to-one One-to-many Many-to-many One-to-One Relationships 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 is usually because of a limitation with the database that requires data to be stored separately usually because of its size. One-to-Many Relationships 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 be associated with it that exist in a second table. These records are usually related based on a unique number. In the customers/credit cards example earlier in this chapter, 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 Relationships 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 may have a thousand customers in your customers table, and a hundred of those customers may 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 names 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 that you were trying to work with and may end up either deleting the wrong record, throwing an error, or corrupting the data within your tables. Records can be identified through the use of three different keys: Candidate keys Primary keys Foreign keys Candidate Keys A candidate key is a set of columns that are unique across the board. Take the following example: | ZipCode | Area | | 92069 | San Marcos | | 92115 | San Diego | | 92105 | San Diego | | 92128 | San Diego | In this example, the ZipCode column could be considered a candidate key because the values never repeat. Although the Area names do repeat, together with the ZipCode 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 that is being stored. Database developers will often create primary keys with an automatically generated number, guaranteeing that the row will always increment by one and remain completely unique from the previous record. 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 may contain ZIP codes. Because the primary key is different, the records remain completely unique. | ZipPK | ZipCode | | 1 | 92069 | | 2 | 92115 | | 3 | 92105 | | 4 | 92128 | NOTE Because no ZIP code will ever be added more than once, you could make the ZIP code the primary key.
Foreign Keys A foreign key is a column that contains values that are found in the primary key of another table. A foreign key may be null and almost always is not unique. Consider the following example: | ZipPK | ZipCode | | 1 | 92069 | | 2 | 92115 | | 3 | 92105 | | 4 | 92128 | | AreaPK | AreaName | AreaFK | | 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 AreaFK 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 ClrFK values may be null and often repeat. A null foreign key means that 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 Web store database 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 a customer, as well. Instead, you could separate the customers into their own table and the products along with their price into a second table. If a specific customer orders a product, the product and its price are placed into a third table (orders) along with the corresponding customer data. There are roughly five normal forms that define how data is laid out within a database. The First Normal Form The first normal form basically states that all rows in a table must contain different data. No duplicate rows are permitted. It also states that all entries within a specific column must be of the same type for 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 customers table, you could not 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 that you achieved in the foreign key example. Instead of storing the credit cards of a customer within the customers table, you separate it 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 will be freed of modification irregularities. Domain/key normal form is the normalization form that most database developers try to achieve. | 
