The widespread use of computers has made database interaction a daily occurrence. Although databases can be large and complex, they are generally defined by basic terms that come together to create the larger system. Understanding these database basics helps users understand the underlying structure of the system and how they interact with it.
Tables and Fields
A database is a collection of tables. Each table represents an entity, such as a person, place, or thing. Tables are made up of smaller units called records. Each record represents a single entity of the type that the table defines. Similarly, a record is a collection of fields. Each field describes an aspect of the entity type the table represents. Although the attributes stored in fields do not have to be unique, the collection of all fields in a record should make that record unique. This hierarchical organization of data allows for improved usefulness of the database (Farrell, 2008).
Keys and Relationships
Although tables are useful by themselves, describing the relationships between different entities is another feature of relational databases. A single primary key identifies each record within a table. The primary key is a field that is guaranteed to be unique within the table. Foreign keys exist in one table but reference a primary key in a different table (Hernandez, 2003). Together these keys represent the endpoints of a relationship between the records in each table.
There are three kinds of relationships in a relational database. One-to-one and one-to-many database relationships include a parent table and the child table that refers to it. One-to-one relationships exist when a single record in a child table is linked to a single record in the parent table. In a one-to-many relationship a single record in the parent table may be linked to many records in the child table. Finally, many-to-many relationships exist when many records in one table can be related to many records in another. This relationship cannot be represented directly between the two tables, but instead requires a linking table to complete the relationship (Hernandez, 2003).
Business Rules
Business rules are statements that constrain some part of the application. Business rules may apply to the data type within a field or define how the data within the database can be used. There are two types of business rules. Database-oriented business rules affect the design of the database. They are implemented at design time. Application-oriented business rules cannot be represented in the database. Database-oriented business rules can be broken down further into two general categories. Field-specific business rules constrain a specific field. Relationship-specific business rules define a relationship between two tables in the database (Hernandez, 2003).
Logical and Physical Models
When designing a database, the initial work goes into designing the logical model. This model takes inputs such as interviews with users and business rules and translates them into an abstract model. The logical model can be represented in many forms. One example is an Entity Relationship Diagram that describes the various tables in a database and how they are related. The logical model is used to create the physical model. The physical model applies the logical model to a specific relational database management system (RDBMS). At this time, specific implementation details are determined, such as indexes and field specifications. These details are dependent on the RDBMS, and are therefore not part of the logical model (Halpin & Morgan, 2008).
References
Farrell, J. (2008). Programming logic and design, comprehensive (5th ed.). Boston: Thomson Course Technology.
Halpin, T. & Morgan, T. (2008). Information modeling and relational databases (2nd ed.). Burlington, MA : Elsevier/Morgan Kaufman Publishers.
Hernandez, M. J. (2003). Database design for mere mortals: A hands-on guide to relational database design (2nd ed.). Boston: Pearson.