Saturday, 17 August 2013

DB2 Anatomy of a Relational Database

According to the relational model, data in a relational database is stored in relations, which are perceived by the user as tables. Each relation is composed of tuples (records) and attributes (fields). A relational database has several other characteristics, which are discussed in this section.
Tables : Tables are the main structures in the database. Each table always represents a single, specific subject.
The logical order of records and fields within a table is of absolutely no importance. Every table contains at least one field—known as a primary key—that uniquely identifies each of its records. In below figure for example, CustomerID is the primary key of the Customers table.) In fact, data in a relational database can exist independent of the way it is physically stored in the computer because of these last two table characteristics. This is great news for users because they aren’t required to know the physical location of a record in order to retrieve its data.

The subject that a given table represents can be either an object or an event. When the subject is an object, the table represents something that is tangible, such as a person, place, or thing. Regardless of its type, every object has characteristics that can be stored as data. This data can then be processed in an almost infinite number of ways. Pilots, products,machines, students, buildings, and equipment are all examples of objects that can be represented by a table.
Fields : A field is the smallest structure in the database, and it represents a characteristic of the subject of the table to which it belongs. Fields are the structures actually used to store data. The data in these fields can then be retrieved and presented as information in almost any configuration imaginable. Remember that the quality of the information you get from your data is in direct proportion to the amount of time you’ve dedicated to ensuring the structural integrity and data integrity of the fields themselves. There is just no way to underestimate the importance of fields.
Every field in a properly designed database contains one and only one value, and its name identifies the type of value it holds. This makes entering data into a field very intuitive. If you see fields with names such as FirstName, Last- Name,City, State, and ZipCode,you know exactly what type of value goes into each field.
Records : A record represents a unique instance of the subject of a table. It is composed of the entire set of fields in a table, regardless of whether or not the fields contain any values. Because of the manner in which a table is defined, each record is identified throughout the database by a unique value in the primary key field of that record.
Keys : Keys are special fields that play very specific roles within a table. The type of key determines its purpose within the table. Although a table might contain several types of keys,we will limit our discussion to the two most important ones: the primary key and the foreign key.
A primary key is a field or group of fields that uniquely identifies each record within a table. (When a primary key is composed of two or more fields, it is known as a composite primary key.) The primary key is the most important for two reasons: Its value identifies a specific record throughout the entire database, and its field identifies a given table throughout the entire database. Primary keys also enforce table-level integrity and help establish relationships with other tables. Every table in your database should have a primary key.

When you determine that a pair of tables has a relationship to each other,you typically establish the relationship by taking a copy of the primary key from the first table and inserting it into the second table, where it becomes a foreign key. (The term foreign key is derived from the fact that the second table already has a primary key of its own,and the primary key you are introducing from the first table is foreign to the second table.)
Foreign keys are important not only for the obvious reason that they help establish relationships between pairs of tables but also because they help ensure relationship-level integrity. This means that the records in both tables will always be properly related because the values of a foreign key must be drawn from the values of the primary key to which it refers. Foreign keys also help you avoid the dreaded “orphaned records,” a classic example of which is an order record without an associated customer.
Views : A view is a virtual table composed of fields from one or more tables in the database. The tables that comprise the view are known as base tables. The relational model refers to a view as virtual because it draws data from base tables rather than storing any data on its own. In fact, the only information about a view that is stored in the database is its structure. Views enable you to see the information in your database from many different perspectives, thus providing great flexibility for working with data. You can create views in a variety of ways—they are especially useful when based on multiple related tables. For example, you can create a view that summarizes information such as the total number of hours worked by every carpenter within the downtown Seattle area. Or you can create a view that groups data by specific fields.
In many RDBMS programs, a view is commonly implemented and referred to as a saved query or, more simply, a query. In most cases, a query has all the characteristics of a view, so the only difference is that it is referred to by a different name. (We often wonder if someone in some marketing department had something to do with this.) It’s important to note that some vendors are now beginning to call a query by its real name. Regardless of what it’s called in your RDBMS program, you’ll certainly use views in your database.

Created with Artisteer

No comments:

Post a comment