Overview
This lecture introduces relational databases, covering their structure, key components, entity relationships, and core benefits for application development.
Structure of Relational Databases
- Relational databases have existed for nearly 50 years.
- Data is organized into tables representing entities (e.g., books).
- Columns represent attributes of entities (e.g., name, date, author).
- Each table includes a unique ID column to identify records.
- Each row is a record representing an individual item or entry.
- Data is queried using Structured Query Language (SQL).
Entity Relationships
- Tables can be related using different relationship types: one-to-one and one-to-many.
- Relationships are established using a foreign key, which is an attribute referencing the ID of another table.
- Foreign keys enable combining data from related tables via SQL queries.
Database Optimization
- Indexes can be created on columns frequently used in queries (e.g., name, date).
- Indexes speed up data retrieval by maintaining a constantly updated cache of values.
Core Benefits of Relational Databases
- Consistency ensures data integrity when executing multiple operations; failed transactions can be rolled back.
- Stored procedures allow grouping several SQL statements into programmable functions to reduce code repetition.
- Views provide virtual tables for simplified query results and modular queries.
- Locking and concurrency mechanisms prevent data conflicts when multiple users or applications access the database simultaneously.
Key Terms & Definitions
- Relational Database — A database system organizing data into related tables.
- Table — Collection of data about a specific entity or item.
- Column — Attribute or property of an entity in a table.
- Row (Record) — Single item or instance of an entity in a table.
- SQL (Structured Query Language) — Language used to query and manage relational databases.
- Foreign Key — A column linking to the unique ID of another table to create relationships.
- Index — Data structure that speeds up query performance on specific columns.
- Stored Procedure — Predefined SQL code blocks for reusable database actions.
- View — A virtual table presenting simplified or specific data from one or more tables.
- Locking/Concurrency — Mechanisms ensuring data accuracy when accessed or modified by multiple users at once.
Action Items / Next Steps
- Review and familiarize yourself with key SQL concepts: tables, columns, rows, foreign keys, and indexes.
- Practice modeling simple entity relationships (one-to-one, one-to-many) in a relational schema.
- Explore basic SQL queries for selecting, inserting, and joining data.