4 Comprehensive Guide to Relational Database Design and SQL Implementation
Normalization and the Iterative Design Process
Third Normal Form (3NF) Visibility: In the third normal form, all transitive dependencies within the data are immediately visible because they are represented directly by the structure of the tables themselves. This is visually represented in Figure 20 from Jörg Dreikauss (2022).
The Iterative Design Process: The structuring of a relational database is an iterative process. It begins with the creation of a business data model, which serves as the foundation for a technical data model. This process mirrors system design, employing graphical modeling languages for both design and documentation phases.
Entity Relationship Diagrams (ER Diagrams): ER Diagrams are the standard tool used to illustrate the core elements of a database structure:
Entity: The objects or concepts about which data is stored.
Attribute: The specific properties or characteristics of an entity.
Key: Unique identifiers for entity instances.
Relationship: The connections between entities, which include forms such as , , and .
Measuring Data Quality via Normalization: Determining the normal form of a database allows developers to measure the quality of the data schema. It provides explicit information on how redundant data are stored. Normalization is specifically used to convert a data schema into a normal form to reduce data redundancy.
Logical Database Design Activities
Introduction to Database Creation: After the conceptual and modeling phases, the data model must be transferred into concrete tables within a Database Management System (DBMS). This involves logical database design activities described below, often following the sequence established by Steiner (2014) in Figure 21.
Activity 1: Identify Entities: This step identifies the relations (comparable to identifying classes in object-oriented programming). Starting from technical entities, the model is refined step-by-step with technical details. Often, this step is handled during requirements engineering, meaning designers start with an agreed-upon technical data model rather than a blank page.
Activity 2: Define Relationships: Designers must determine relationships between identified entities, including the specification of cardinalities (quantities for the relationships). The result of identifying entities and relationships is the "conceptual data model."
Activity 3: Determine Primary Key: Primary keys are identified first. Options include:
Simple Primary Keys: A single attribute that uniquely identifies a record.
Compound Primary Keys: A combination of multiple attributes used as a key.
Artificial Keys: Keys created specifically for the database (e.g., ID numbers) that do not have a real-world technical equivalent.
Activity 4: Conversion into Physical Data Model: This involves transforming the set of entities and relationships into a model supported by a relational DBMS. All conceptual relationships are transformed into concrete table structures.
Activity 5: Defining and Normalizing Attributes: This involves identifying all instance attributes required to describe entities. Designers must determine the concrete data type for each attribute. The database is then normalized to a level sufficient for the project requirements (typically the third normal form ()). This involves examining relations for structure and dependencies and adding new relations if necessary.
Activity 6: Determine Consistency Conditions: Specific conditions must be guaranteed by the DBMS to ensure data is available in a "clean" form. Key considerations include:
Ensuring all values in an ID attribute are unique.
Enforcing compliance with specified data types and value ranges.
Preserving referential integrity and the secure resolution of foreign keys.
During deletion or changes, ensuring an identical number of relations are updated to maintain functional consistency.
Activity 7: Formulate Transactions: Transactions are operations on the dataset (reading, creating, changing, deleting). In industrial systems, queries often consist of multiple SQL statements grouped into transactions. This set of commands ensures that if an error occurs during execution, all changes can be reversed (rollback), keeping the dataset consistent.
Conceptual vs. Physical Data Models
Conceptual Data Model:
Represents a domain-oriented data model identified at the start of database design.
It is ideally the result of specifications and requirements engineering.
Does not contain database-specific information.
Often visualized as a network of entities/classes and may contain inheritance relationships (see UML Class Diagram, Figure 22).
Physical Data Model:
Accounts for all constraints and properties of relational databases.
Only and relationships can be mapped directly.
Contains tables, attributes, primary keys, and foreign keys.
Identifiers should not contain special characters like umlauts (ä, ü, ö).
Relationships from the conceptual model that cannot be mapped directly (like or inheritance) must be transformed first.
Mapping Relationship Types to the Physical Data Model
Mapping 1:1 Relationships:
Option A: Two tables (e.g., Customer and Address). Store the primary key of Address (AddressID) in the Customer table. Condition: Each AddressID must occur in Customer exactly once.
Option B: Two tables. Store the primary key of Customer (CustomerID) in the Address table. Condition: Each CustomerID must occur in Address exactly once.
Option C: Merge into a single table. Only possible if the table (e.g., Address) has no other relationships. Condition: Combined attributes (Street, PostalCode, City) must be unique.
Mapping C:C Relationships: Customer and Address are in separate tables. A junction table (CustomerAddress) contains foreign keys AddressID and CustomerID. Condition: Values in the junction table cannot be NULL and each value combination appears at most once.
Mapping 1:N Relationships
Standard 1:N: Two tables. Primary key of "1" side (Customer) is a foreign key in the "N" side (Rating). Condition: CustomerID in Rating must not be NULL and must contain each value of CustomerID at least once (requires application logic or implementation as ).
1:CN (Conditional N): Primary key of Customer is a foreign key in Rating. Condition: CustomerID in Rating must not be NULL.
C:CN: Foreign key in Rating can be NULL.
C:N: Foreign key in Rating can be NULL, but an additional condition requires every CustomerID in the Customer table to be present in the Rating table (requires application logic).
Mapping N:M Relationships:
Requires a junction table (e.g., VoucherSolution) containing foreign keys (CustomerID and ActionID).
The primary key can be composed of both foreign keys or an artificial key.
N:M variant: Requires every CustomerID and ActionID to appear at least once in the junction table.
CN:CM variant: No special requirements for single use of IDs in the junction table.
Recursive Relationships: Formed when a table references itself. For example, an Article table where a foreign key points back to the ArticleID in the same table. This foreign key can be NULL.
Mapping Inheritance Relationships
Relational databases do not natively support inheritance. Three main strategies are used:
Single Table (Single Table per Class Hierarchy):
Maps the entire hierarchy to one table using a "Discriminator" attribute to identify the record type.
Advantages: Extremely fast read/write access (one table, one SQL statement); efficient access to relationships.
Drawbacks: Tables become large and performance might suffer; lots of NULL values in complex hierarchies (inefficient storage).
Joined Subclass Table:
Each class (including abstract ones) has its own table. Subclasses contain a foreign key to their superclass table.
Advantages: Least redundancy; easy to add subclasses without changing existing structures.
Drawbacks: Slowest mapping; reading/writing requires multiple SQL statements and JOINS across tables.
Table per Class:
Each concrete class has a separate table containing all its attributes (including inherited ones).
Advantages: Efficient if the concrete class name is known (one table access); easy to add classes.
Drawbacks: Very difficult to implement relationships to superclasses if the subclass isn't known; evaluations across the superclass require many SQL commands.
Data Types and Table Creation in SQL
Data Definition Language (DDL): SQL keywords used to create table structures. Selection of data types determines storage space and internal organization. Specific types vary by DBMS manufacturer.
Text Types:
CHAR(n): Fixed length of characters (e.g., Country CodesCHAR(2), ISBNCHAR(13), Postal CodesCHAR(5)).VARCHAR(n): Variable length up to characters (e.g., names, descriptions).TEXT: Very long strings, limited by DBMS parameters (e.g., blog entries).
Number Types:
INTEGER/INT: 32-bit integers ( to ).DECIMAL(n,m): Exact decimal numbers with total digits and digits after the decimal (recommended for money).REAL: 32-bit floating-point numbers (measured values).
Date and Time Types:
DATE: formatYYYY-MM-DD(Calendar days).TIME: formatHH:MM:SS.TIMESTAMP: formatYYYY-MM-DD HH:MM:SS(exact second, creation/modification markers).
Other Types:
VARBINARY(n)/BLOB: Binary Large Objects for images, audio, or video.BOOLEAN: ValuesTRUE/FALSEor0/1.
SQL Syntax for Table Creation
CREATE TABLE Structure:
CREATE TABLE [Name] ([Column Definitions], [Primary Key], [Integrity Rules], [Foreign Keys]);
Column Definition Schema:
[name] [Data Type] [NOT NULL] [DEFAULT value] [UNIQUE] [CHECK (condition)].NOT NULL: Prohibits empty values.DEFAULT: Defines a fallback value.UNIQUE: Ensures all values in the column are unique.CHECK: Formulates conditions (e.g.,CHECK (EndDate > StartDate)). Note: MariaDB does not supportCHECK.
Primary Key Definition: Use
PRIMARY KEY (ColumnName). If using an artificial integer key,AUTO_INCREMENT(in MariaDB) can be used to generate unique values automatically.Foreign Key Definition: Defined using
FOREIGN KEY (ColumnName) REFERENCES ReferencedTable(ReferencedColumn). This creates a logical dependency (Referential Integrity).
Preservation of Referential Integrity
Actions can be defined for ON UPDATE and ON DELETE events to maintain integrity:
CASCADE: Changes/deletions in the referenced record are automatically applied to the referencing record.
SET NULL: Sets referencing foreign key to
NULL(column must not beNOT NULL).RESTRICT: Prohibits the change or deletion in the referenced table.
NO ACTION: Interferes with referential integrity (standard SQL term).
SET DEFAULT: Resets the foreign key to a specified default value.