With a general understanding of the different types of databases and database environments, the database designer can draw more intelligent conclusions about how to proceed with any database system design effort. As discussed in this excerpt from Ryan Stephens & Ronald Plew's new book Teach Yourself Database Design (Sams, 2000), there are numerous database models (types) available:
- Flat-file database model
- Hierarchical database model
- Network database model
- Relational database model
- Object-oriented (OO) database model
- Object-relational (OR) database model
The modern database of choice is the relational database (RDB). So why is the relational model so attractive?
- It is the most stable.
- RDB standards are well established by organizations such as the International Standards Organization (ISO) and the American National Standards Institute (ANSI).
- There are many RDB vendors to choose from, including Oracle, Microsoft, Informix, IBM, and Sybase.
- It is easy to convert between different relational database implementations.
- It is easy to define and maintain data with SQL.
- It is easy to manipulate data with SQL.
- The ad hoc query process is simple.
- Data is well-protected through referential integrity and other constraints.
The following subsections discuss the relational dastabase in a bit more detail that will assist in your overall understanding for designing a relational database. First, the characteristics of a relational database are discussed as outlined by the creator of the relational model. Then, the various objects that are most commonly found in a relational database are outlined.
Relational Database Characteristics
The relational model was designed by the IBM research scientist and mathematician, Dr. E.F. Codd. Two of Dr. Codd's main focal points when designing the relational model were to further reduce data redundancy and to improve data integrity within database systems. The relational model originated from a paper authored by Dr. Codd entitled, "A Relational Model of Data for Large Shared Data Banks," written in 1970. This paper included the following concepts that apply to database management systems for relational databases:
A relational database management system (DBMS) must be able to manage databases entirely through its relational capabilities.
All information in a relational database (including table and column names) is represented explicitly as a value in tabular format.
Every value in a relational database is guaranteed to be accessible by using a combination of the table name, primary key value, and column name. This means that you need not know the physical location of the data, and that you can directly access any row of data in a database table independently.
The DBMS should provide support for the treatment of null values (unknown or inapplicable data), which are distinct from default values, and independent of any domain.
The description of the database and its contents is represented at the logical level in tabular format and can therefore be queried using the database language. This refers to metadata that is stored in the data in regards to the database itself. Metadata is data about other data.
At least one supported language must have a well-defined syntax and be comprehensive. It must support data definition, manipulation, integrity rules, authorization, and transactions.
All views that are theoretically updateable can be updated through the system.
The DBMS supports set-level retrievals, inserts, updates, and deletes.
Application programs and ad hoc queries are logically unaffected when physical access methods or storage structures are altered.
Application programs and ad hoc queries are logically affected as little as possible when changes are made to the table structures. Although, if a table is split into two tables, the application will have to be changed so that the appropriate columns are accessed from both tables.
The database language must be capable of defining integrity rules to protect the data. These rules must be stored in the online catalog, and cannot be bypassed when data is modified.
Application programs and ad hoc queries are logically unaffected when data is first distributed or when it is redistributed. Distribution refers to how data is stored on hardware devices.
It must be possible to bypass the integrity rules defined through the database language by using lower-level languages.
Relational Database Objects
Various types of objects can be found in a relational database. Some of the most common objects found in a relational database include
Table -- A table is the primary object used to store data in a relational database. When data is queried and accessed for modification, it is usually found in a table. A table is defined by columns. One occurrence of all columns in a table is called a row of data.
View -- A view is a virtual table, in that it looks like and acts like a table. A view is defined based on the structure and data of a table. A view can be queried and sometimes updated.
Constraint -- A constraint is an object used to place rules on data. Constraints are used to control the allowed data in a column. Constraints are created at the column level and are also used to enforce referential integrity (parent and child table relationships).
Index -- An index is an object that is used to speed the process of data retrieval on a table. For example, an index might be created on a customer's name if users tend to search for customers by name. The customer names would be stored alphabetically in the index. The rows in the index would point to the corresponding rows in the table, much like an index in a book points to a particular page.
Trigger -- A trigger is a stored unit of programming code in the database that is fired based on an event that occurs in the database. When a trigger is fired, data might be modified based on other data that is accessed or modified. Triggers are useful for maintaining redundant data.
Procedure -- A procedure is a program that is stored in the database. A procedure is executed at the database level. Procedures are typically used to manage data and for batch processing.
The first four objects deal with the definition of the database, whereas the last two objects deal with methods for accessing database objects. Objects in a relational database provide users with a logical representation of data, such that the physical location of the data is immaterial to the user.
For More Information
- You can purchase Teach Yourself Database Design (Sams, 2000) here.
- What do you think about this tip? E-mail us at editor@searchDatabase.com with your feedback.
- The Best Database Design Web Links: tips, tutorials, scripts, and more.
- Have a database design to offer your fellow DBA's? The best tips submitted will receive a cool prize--submit your tip today!
- Ask your technical database design questions--or help out your peers by answering them--in our live discussion forums.
- Check out our new Ask the Experts feature: Our database design gurus are waiting to answer your toughest questions.