Relational Database Management System

The thumbnail is sourced from Modeling: relational to graph

1. Relational Database Management System (RDBMS)

A Relational Database Management System (RDBMS) is a database management system built upon the relational model, which provides a structured approach to organizing and managing data. Key characteristics include the relational data model that enables graphical representation of organizational requirements and data structure, tabular data representation that displays information to users in the form of relations (tables), and relational operators that provide tools to manipulate data in tabular format through operations like selection, projection, and joins. Core requirements for RDBMS classification include data presentation that must display data in relational format (tables with rows and columns), data manipulation that must provide relational operators for processing tabular data, and table-based design that uses the universally recognized table model as the foundation for database design. The table structure serves as the fundamental building block, where data is organized into rows (records) and columns (attributes), establishing relationships between different data entities.

2. Core Functions of RDBMS

2.1. Data Management Operations

Archiving, Deletion, and Data Review:

• Information stored in the form of relations
• Depending on access rights, databases allow direct processing of stored information through SQL queries
• Data in databases can be completely deleted, with recovery options varying by implementation

2.2. Multi-user Access

In multi-user environments:

• Distribution of rights and data security are critical
• Systems must keep data consistent even when multiple transactions are requested simultaneously
• Concurrent access control prevents data conflicts

2.3. Data Integrity

What is Data Integrity? The data within a database contains specific rules, ensuring the correctness of the data and defining the logic of the business process that the database is intended to model.

How is it Maintained?

• Prevent multiple users from editing the same record simultaneously (record locking)
• Prevent duplicate records from being entered
• Enforce business rules and constraints

2.4. Data Security

Data security involves:

• Preventing illegitimate access to stored data
• Management by responsible administrators
• Implementation of appropriate cryptographic methods
• Technical precautions to prevent unwanted manipulation or loss of data

2.5. Metadata Management

• Information is usually stored in databases by creating tables
• Metadata is information that describes a set of data
• Metadata can also be queried and managed like regular data

3. ACID Properties - Database Transaction Requirements

ACID represents the fundamental features and requirements for database management systems:

3.1. Atomicity

Definition: Denotes the all-or-nothing property of the DBMS.

• Only valid requests are executed in the established order
• Ensures that the entire transaction is correct
• A transaction is either completely executed or not executed at all
• Guarantees transaction completeness and correctness

3.2. Consistency

Definition: Requires that successful transactions leave a stable database.

• If a transaction completes successfully, it should be recorded in the database
• All integrity rules and constraints must be maintained
• Database remains in a valid state before and after transactions

3.3. Isolation

Definition: Transactions do not obstruct each other.

• Guaranteed by specific blocking functions
• When multiple transactions occur simultaneously, each executes independently
• Prevents interference between concurrent transactions
• Maintains transaction independence

3.4. Durability

Definition: All data is stored permanently in the DBMS.

• Data persistence even after successful transaction completion
• Valid especially in case of system errors or DBMS failures
• Ensures data will not be lost during system failures or crashes
• Once committed, data is reliably written to the database

4. Relations in Relational Databases

4.1. Definition of Relation

A relation is a set of tuples, where each element $d_j$ is a member of domain $D_j$.

Example:

Domain Examples:

• For CustomerID: domain $D_1$ could be the set of positive integer numbers
• For CustomerName: domain $D_2$ could consist of all strings (text values)
• For Status: domain $D_3$ could be a predefined list of valid statuses (Active, Inactive)

4.2. Functional Dependency

Definition: Attribute B of relation R is functionally dependent on attribute A of R if, at every instant of time, each value in A has no more than one value in B associated with it under R.

Notation:

• $R.A \rightarrow R.B$: if $R.B$ is functionally dependent on attribute $A$ in $R$
• $R.A \neq R.B$: if $R.B$ is NOT functionally dependent on attribute $A$ in $R$
• If $R.A \rightarrow R.B$ and $R.B \rightarrow R.A$ then $R.A$ and $R.B$ are in one-to-one correspondence: $R.A \iff R.B$

The definition given above can be extended to collections of attributes: If $D,E$ are distinct collections of attributes of $R$, then $E$ is functionally dependent on $D$ if at every instant of time, each $D$-value has no more than one $E$-value associated with it under $R$.

5. Database Modeling and Normalization

Database normalization addresses the fundamental challenge of organizing data efficiently within relational database systems. While databases serve as powerful tools for grouping and managing information through interconnected tables, a single table approach presents significant problems including the risk of data redundancy, failure to highlight relationships between data subsets in a timely manner, and the presence of duplicated and redundant information. Normalization provides the solution through a gradual process of progressive optimization that begins with non-normalized relations and systematically reaches higher levels of normalization to effectively eliminate redundancy and improve data integrity.

A primary key is a set of one or more attributes that uniquely identifies each record in a table while maintaining non-redundancy, meaning that removing any attribute from the key would result in loss of uniqueness. When designing primary keys, it's recommended to use fewer attributes when possible, prioritize attributes that are frequently used in relational operations for efficiency, and ensure stability by selecting values that won't change frequently over time.

A foreign key is a column (or set of columns) in one table that connects to the primary key data in another table.

Key Terminology:

• Parent Table: The reference table containing the primary key
• Child Table: The table containing the foreign key
• Relationship: The foreign key in the child table references the primary key in the parent table

6. Referential Integrity

Referential Integrity is a property of a database in which all values of all foreign keys are valid.

A referential constraint is the rule by which, given a child table, the values of the foreign key are valid only if:

• They appear as values of a parent key
• Null values are usually allowed (depending on business rules)

References

• Lecture notes from Business Analytics course, Prof. Paolo Menna, University of Verona 2024-2025
• Neo4j. "Modeling: relational to graph"