Database

A relational database organizes data into tables made up of rows and columns. In this structure, rows represent individual records, and columns represent fields.

Data is typically divided into multiple related tables rather than stored in a single table with many columns. This approach ensures data integrity by minimizing redundancy, as each piece of information is recorded only once, reducing the risk of errors or inconsistencies.

For example, in a company database, instead of storing customer information (like name and address) in every order record, a separate Customer table can be created. Each order record in the Order table would then reference the relevant customer via a customer ID, avoiding the need to repeatedly enter the same customer information in multiple places.

Relationships between tables are created using shared ID fields, allowing data from different tables to be joined and read together. For instance, the Order table and the Customer table might both have a CustomerId column, which can be used to match rows when joining these tables.

Key Concepts

  • RDBMS: A Relational Database Management System (RDBMS) is the software that manages relational databases. Popular RDBMS examples include PostgreSQL, MySQL, Microsoft SQL Server and Oracle.

  • SQL: Structured Query Language (SQL) is the standard language used to interact with relational databases. It allows users to create, read, update, and delete data, as well as manage database structures and relationships.

  • Query: A query is a specific type of SQL statement used to retrieve data from the database. For example, a query might be used to find all orders placed by a specific customer or to list all customers from a particular city.

  • Query Parameter: A query parameter is a placeholder in an SQL query that allows you to pass specific values at the time the query is executed, rather than typing those values into the query itself.

  • Primary Key: A primary key is a unique identifier for each record in a table. It ensures that each row in the table can be uniquely identified by its primary key value, which must be unique and cannot be null.

  • Foreign Key: A foreign key is a column in a table that refers to the primary key of another table, establishing a link between the data in the two tables. Defining foreign key relationships helps the RDBMS enforce these connections by ensuring that all values in the foreign key column exist in the primary key column of the referenced table.

  • NULL: A special marker used in relational databases to indicate that a data value does not exist in a field. It represents missing or unknown information, rather than a zero or an empty string. A nullable column is a column that is allowed to contain NULL values.