A relational database is based on the fact that data in one table can be tied, o

Question

A relational database is based on the fact that data in one table can be tied, or related to data in another table. To do this, the database uses various types of keys to form or define these relationships. The process of identifying, defining, and implementing these keys to enable data to be represented in the best way is one of the more important aspects of developing a well-defined database. Discuss some of your thoughts on this area based on either your experience, readings in the book or other resources you can find.

Explanation / Answer

you will understand the following key concepts: • What a relational database is • How information in tables can be related • Why keys are important in a relational environment • How to create a relationship between two tables • What referential integrity means Understanding Data Relationships You may have heard or read somewhere that Access is a relational database. In general, a relational database is a table-based database in which the information in one table relates (corresponds) to information in another table. For example, you may have a database which you use to keep track of your music collection. In one table, you have all your CDs. In another, you have all the music artists in your collection. The information in the one table relates to the information in the other since every CD has at least one musical artist. Relationships within a database are often based on relationships between objects in everyday life. For instance, the relationship between a CD and a musical artist is one that exists in the real world. Likewise, a real-world relationship exists between inventory products and the companies that make those products. In a database, you may have one table that contains inventory products and another that contains vendors. When you tie the two tables together, you create a relational database. The idea behind relational databases is to limit the amount of redundant information in a database. Consider the inventory example again. If you want to keep track of your inventory, you might have a table that includes the following items: • Item ID • Description • Color • Weight • Cost • Price • Quantity On Hand • Reorder Level • Vendor Name • Vendor Address • Vendor City • Vendor State • Vendor Zip Code • Vendor Phone For most inventories, you need to maintain this information for each item. The problem occurs when you start ordering more than one item from a given vendor. Pretty soon, you have a lot of redundant information in your table. Redundant information simply consumes disk space. Consider how much space could be wasted by thirty occurrences of the same vendor address and phone number! Multiply that wasted space by several dozen vendors, and you can begin to see the magnitude of the problem. Wasted space is only one problem. Another is that redundant interaction increases the chance of error. For instance, when employees enter new inventory items, it is possible that they will enter typos when they put in vendor names, addresses, and phone numbers. Down the road, when you need to refer back to the information, how do you determine which names, addresses, and phone numbers are correct? Still another problem is that redundant data increases your update and processing time. For instance, if you need to change the phone number of a vendor, you must pull up each inventory record and retype the information. As you can guess, such changes can take quite a bit of time. If you use a command (such as the Replace command in the Datasheet), the command may not change some numbers if they were initially entered incorrectly. All in all, the redundant data increases your personal management tasks. Luckily, using a relational database, you can use two related tables, one containing inventory items and one containing vendor information. For instance, consider the following two tables: Item ID Vendor ID Description Name Color Address Weight City Cost State Price Zip Code Quantity On Hand Phone Reorder Level Vendor ID Notice that the Vendor ID field exists in both tables. Access uses the Vendor ID field to tie the information in the inventory table to the information in the vendor table. By setting up your database in this way, you don't have any redundant information and thus you eliminate many of the problems inherent in the other layout: • Less storage space. Since there is only one record for each vendor, you eliminate the redundancy inherent in the other layout. • Less chance for error. Since vendors are not being entered more than once, human error is less likely to creep into the table. • Faster processing time. One record per vendor means you can make universal changes quicker. (Actually, there is no universe to change--there is only one record.) Also, you can do other changes faster, such as an across-the-board price increase. What Type of Relationships Can Exist? Information in two tables can relate in any of three different ways. These ways are based on the number of possible relations between the records. These three types of relationships are: • One-to-one • One-to-many • Many-to-many A one-to-one relationship indicates that for each record in the first table, there is one corresponding record in the second table. For instance, you might have two tables, one with general customer information and another with detailed customer information. For each record in one table, there is a corresponding record in the other. For security reasons, you might want to set up a one-to-one relationship; the general information may be open to anyone within your company, but the detailed information may be available to only those with a particular security clearance. A one-to-many relationship indicates that for each record in one table there are many records in the other table. For instance, in the example given in the previous section, for each record in the inventory table there is only one corresponding record in the vendor table. This is because each inventory item has only one supplier. However, for each record in the vendor table, there could be many records in the inventory table--because a particular vendor may supply more than one inventory item. The many-to-many relationship is the most complex relationship. For any record in either table, there can be many corresponding records in the other table. The classic example of a many-to-many relationship is the relationship between a table that contains invoices and one that contains inventory. Each invoice record can refer to many different inventory items, and each inventory item can refer to many different invoices. Revisiting Keys we learned about primary keys. You learned that keys provide a method for Access to quickly organize and retrieve data. In a relational environment, however, keys can do much more than that. Keys, in effect, become the tie that binds two tables together. For instance, in the inventory example provided earlier, the key in the vendor table is Vendor ID. This key field is also used in the inventory table to tie it to the vendor table. In the vendor table, the Vendor ID is unique; in the inventory table it is not. Characteristics of Keys A key for any given table possesses three attributes which are important to remember: • It must be unique • It cannot be undefined (null) • It is, by definition, indexed A key can be an account number (as in a Vendor ID), social security number, license number, part number, or any other numeric value or combination of characters that are unique. That is the important thing--the key is unique. No other row in the table can have the value of the key. However, other tables may share the same set of key information, as was pointed out in the inventory example. Text names (such as company names or titles) are not generally unique and cannot be used in math operations; therefore, they do not make good keys. You should instead put together your own unique value. Types of Keys There are three types of keys which you can use in a table: • Simple key • Complex key • Foreign key You have actually used a simple key throughout this book. A simple key contains the value of a single column, such as an account number. Access also supports complex keys, which use the values of more than one column. For instance, you might not have a single column that uniquely identifies a record. But if you combine the contents of two columns, you can achieve the purposes of a key. As an example, assume you are developing a billing system for your company. There are different departments in your company, and each can generate their own invoices. When you look at what you can bill for, you find that it is highly possible that there will be an overlap between departments. In other words, two departments could sell and bill for the same item. Since you want to maintain separate inventories for each department, you can't simply combine them. Your solution is to use a complex key that is composed of the department ID and the part number, together. A foreign key is one that does not belong to the current table, but instead provides a link to another table. A good example of a foreign key is the inventory and vendor tables described earlier. The Vendor ID is a simple key for the vendor table, but it is also contained in the inventory table. To the inventory table, it is a foreign key.

Related Questions

Navigate

• Browse (All)
• Browse A
• Subjects

---

---

Integrity-first tutoring: explanations and feedback only — we do not complete graded work. Learn more.