The Elements of a Database , Page 2
The integrity of the information stored in a database is controlled by keys. A key is a column value in a table that is used to either uniquely identify a row of data in a table, or establish a relationship with another table. A key is normally correlated with one column in table, although it might be associated with multiple columns. There are two types of keys: primary and foreign.
A primary key is the combination of one or more column values in a table that make a row of data unique within the table. Primary keys are typically used to join related tables. Even if a table has no child table, a primary key can be used to disallow the entry of duplicate records into a table. For example, an employee's social security number is sometimes considered a primary key candidate because all SSNs are unique.
A foreign key is the combination of one or more column values in a table that reference a primary key in another table. Foreign keys are defined in child tables. A foreign key ensures that a parent record has been created before a child record. Conversely, a foreign key also ensures that the child record is deleted before the parent record.
Figure 5 illustrates how a foreign key constraint is related to a primary key constraint. The Auth_id column in the Booklist table references the Auth_id in the Authors table. Authors is the parent table and Booklist is the child table. If a record for Auth_id=3 needs to be created, the record must be inserted into the Authors table first. If an author needs to be removed from the database, all references to the author must first be deleted in Booklist, the child table.
Figure 5 - Referential integrity, or parent/child relationships.
Most databases are divided into many tables, most of which are related to one another. In most modern databases, such as the relational database, relationships are established through the use of primary and foreign keys. The purpose of separating data into tables and establishing table relationships is to reduce data redundancy. The process of reducing data redundancy in a relational database is called normalization and is discussed in detail in Chapter 8 Of our book, Database Design.
Three types of table relationships that can be derived are as follows:
- One-to-one--One record in a table is related to only one record in another table.
- One-to-many--One record in a table can be related to many records in another table.
- Many-to-many--One record in a table can be related to one or more records in another table, and one or more records in the second table can be related to one or more records in the first table.
Figure 6 briefly illustrates table relationships in a relational database. A relational database allows parent tables to have many child tables, and child tables to have many parent tables. The figure shows two tables. Table 1 has an ID column (primary key) and Table 2 has an FK column (foreign key). In the one-to-one relationship example, notice that for every ID in Table 1, there is only one ID in Table 2. In the one-to-many relationships example, notice that the ID of 1 has many occurrences in Table 2. In the many-to-many relationship example, notice that the ID in Table 1 might occur multiple times in Table 2 as a foreign key, and the ID in Table 2 might occur multiple times in Table 1.
Figure 6 - Available table relationships in the relational model.
The intent of this article was to provide a brief coverage of basic database elements to provide you with a basic understanding of the elements found in a database. While it may not seem like a lot has been covered, you have actually now been exposed to the key topics involved in working with databases. As you continue to work with databases, you will find that you will continually be confronted with the above terms and concepts, including schemas, tables, columns, rows, and keys.
About the Authors
Ryan K. Stephens and Ronald R. Plew are the authors of Database Design (ISBN: 0-672-31758-3), a book by Sams Publishing. Ryan is president and CEO of Perpetual Technologies, Inc. a database company specializing in Oracle consulting and training. Ron is vice president and CIO of Perpetual Technologies, Inc. Both have numerous years of experience training and consulting at the DBA level.
This article is based on techniques presented in Database Design (ISBN: 0-672-31758-3), which is available by clicking on the title.
© Copyright Sams Publishing, All Rights Reserved