Database Design   «Prev  Next»

Lesson 4Relational Database Structure
ObjectiveDescribe the structure of a relational database.

Relational Database Structure

The relational database model exhibits the following structural characteristics:
  1. A relational database contains multiple tables.
  2. Each table stores data about one specific subject.
  3. Fields contain data describing the subject of a table.
  4. Records are particular instances of the subject of a table.
  5. A special primary key[1] field uniquely identifies each record in a table.

Employee Table

The sample table below clarifies the structure of a relational database table.

EmpID LastName FirstName HireDate
7922 Jackson Stephen 11-29-2007
7923 Reynolds Sandy 01-04-2003
7924 Armstrong Stephen 03-13-2008
7925 Brown Leroy 04-17-2011

The table exhibits the following characteristics:
  1. The specific subject of the table is revealed in the title: Employees.
  2. The fields describing employees are: LastName, FirstName, and HireDate.
  3. The yellow highlighted row features the record of one employee: Sandy Reynolds.
  4. The primary key uniquely identifying each record is EmpID.
In the next lesson, the structure of a relational database and its ability to be linked for accessing data will be discussed.

Why is Data Modeling Important?

Data modeling is probably the most labor intensive and time consuming part of the development process. If you are pressed for time, you should still invest the required time into the data modeling process. A common response by practitioners who write on the subject is that you should no more build a database without a model than you should build a house without blueprints.
The goal of the data model is to make sure that the all data objects required by the database are completely and accurately represented. Because the data model uses easily understood notations and natural language, it can be reviewed and verified as correct by the end-users.
The data model is also detailed enough to be used by the database developers to use as a "blueprint" for building the physical database. The information contained in the data model will be used to define
  1. the relational tables,
  2. primary and foreign keys,
  3. stored procedures, and
  4. triggers.
A poorly designed database will require more time in the longterm. Without careful planning you may create a database that omits data required to create critical reports, produces results that are incorrect or inconsistent, and is unable to accommodate changes in the user's requirements.

Data Model to Database as Blueprint to House Analogy

Question: How is a data model to a database like a blueprint for a house?
A data model and a blueprint share a strikingly similar purpose within their respective domains: they both serve as a guiding framework, laying the foundation for the construction of complex structures. In the realm of databases, a data model outlines the structure, relationships, and constraints of the data, while in the realm of architecture, a blueprint delineates the design, dimensions, and layout of a house.
Just as a skilled architect meticulously drafts a blueprint to ensure the successful execution of a building project, a proficient data modeler carefully designs a data model to facilitate the efficient storage, retrieval, and manipulation of data within a database. The blueprint, with its intricate details, provides a comprehensive representation of a house, enabling builders to construct the edifice with precision and accuracy. Similarly, a data model presents a clear, unambiguous depiction of the data, its organization, and its interactions, allowing database administrators and developers to build and maintain the database with ease and confidence.

Blueprints and Data Models

Furthermore, both blueprints and data models are essential for effective communication and collaboration among different stakeholders. In the case of a house, a blueprint serves as a common reference point for architects, engineers, and builders to understand the design intent and coordinate their efforts. In the context of a database, a data model ensures that database administrators, developers, and users share a consistent understanding of the data, its organization, and its usage. This harmonization of perspectives is crucial for the successful implementation and operation of the database system.
Moreover, just as a blueprint can be modified and updated to accommodate changing requirements or unforeseen challenges during the construction process, a data model can be adapted to address evolving business needs, regulatory changes, or technological advancements. In both cases, a thorough understanding of the existing structure and the ability to foresee potential impacts of the modifications are vital for maintaining the overall integrity and functionality of the system.
In conclusion, a data model to a database is akin to a blueprint for a house in numerous ways. Both act as guiding frameworks, essential tools for communication and collaboration, and adaptable designs that can evolve to meet changing needs. By providing a clear, concise, and comprehensive representation of the complex structures they underpin, data models and blueprints facilitate the successful realization of their respective projects – be it a robust, efficient database system or a durable, functional house.

RDBMS Structure - Exercise

Before moving on to the next lesson, click the Exercise link below to check your knowledge of the structure of a relational database table.
RDBMS Structure - Exercise

[1]primary key: A field (or combination of fields) that uniquely identifies a record in a table.