Relational Database Design (RDD) models information and data in a series of tables with rows and columns. Each row of a relationship / table represents a data record and each column represents a data attribute. Structured query language (SQL) is used to manipulate relational databases. The design of a relational database consists of four phases in which the data is modeled into a series of related tables. The phases are:
- Define relations / attributes
- Define primary keys
- Define relationships
Relational databases differ from other databases in their approach to organizing data and performing transactions. In an RDD, the data is organized in tables and all types of data access take place via controlled transactions.
Relational database design meets the ACID properties (atomicity, consistency, integrity, and durability) required for database design. Relational database design requires the use of a database server in applications to troubleshoot data management problems.
The four phases of an RDD are as follows:
1) Relations and Attributes: The various tables and attributes for each table are identified. The tables represent entities and the attributes represent the properties of the respective entities.
2) Primary Key: The attribute or group of attributes that help uniquely identify a record is identified and assigned as a primary key
3) Relationships: The relationships between the various tables are established with the help of foreign keys. Foreign keys are attributes that appear in one table and are primary keys of another table. The types of relationships that can exist between the relationships (tables) are:
- One to one
- One too many
- Way too much
An entity relationship diagram can be used to show the entities, their attributes, and the relationship between the entities in a schematic manner.
4) Normalization: This is the process of optimizing the database structure. Normalization simplifies database design to avoid redundancy and confusion. The various normal forms are as follows:
- First normal form
- Second normal form
- Third normal form
- Boyce-Codd normal form
- Fifth normal form
By applying a set of rules, a table is linearly normalized to the normal forms above. The efficiency of the design gets better with each higher level of normalization.