Database Management System - Relational Databases

Study Notes

Database Management System - Relational Databases

Definition:
- A relational database is a type of database that stores data in structured formats using rows and columns.
Key Concepts:
- Tables: Data is organized in tables (relations), each consisting of rows (records) and columns (attributes).
- Primary Key: A unique identifier for each record in a table, ensuring no duplicate entries.
- Foreign Key: A field in one table that relates to the primary key of another table, establishing relationships between tables.
Data Integrity:
- Entity Integrity: Ensures that each table has a primary key and that the key is unique and not null.
- Referential Integrity: Ensures that a foreign key must either be null or must match an existing primary key in another table.
Normalization:
- The process of organizing data to minimize redundancy and improve data integrity, typically involving multiple related tables.
SQL (Structured Query Language):
- The standard language used for querying and managing relational databases.
- Common SQL commands:
  - SELECT: Retrieve data from one or more tables.
  - INSERT: Add new records to a table.
  - UPDATE: Modify existing records in a table.
  - DELETE: Remove records from a table.
Advantages:
- Data independence: Changes in database structure do not affect applications.
- Flexibility: Complex queries can be executed easily using SQL.
- Security: Access control can be implemented for data protection.
Disadvantages:
- Performance overhead due to complex queries and joins.
- Not suitable for unstructured data or large-scale data storage (Big Data).
Examples of Relational Database Management Systems (RDBMS):
- MySQL
- PostgreSQL
- Oracle Database
- Microsoft SQL Server
Use Cases:
- Transactional applications (e.g., banking, e-commerce).
- Data warehousing where structured data is needed for analysis.

Definition

Relational databases store data in structured formats using rows and columns.

Key Concepts

Tables: Organize data into tables (relations) consisting of rows (records) and columns (attributes).
Primary Key: Acts as a unique identifier for each record, preventing duplicate entries.
Foreign Key: Relates to a primary key in another table, establishing relationships between tables.

Data Integrity

Entity Integrity: Guarantees each table has a unique, non-null primary key.
Referential Integrity: Ensures that foreign keys either match an existing primary key or are null.

Normalization

Involves organizing data to reduce redundancy and enhance data integrity, typically resulting in multiple related tables.

SQL (Structured Query Language)

The main language for querying and managing relational databases.
Common SQL Commands:
- SELECT: Retrieve data from tables.
- INSERT: Add new records to a table.
- UPDATE: Modify existing records.
- DELETE: Remove records from a table.

Advantages

Data Independence: Database structural changes do not impact applications.
Flexibility: Enables execution of complex queries easily with SQL.
Security: Allows implementation of access controls for data protection.

Disadvantages

Performance may suffer due to complex queries and joins.
Not suitable for handling unstructured data or extensive data storage scenarios like Big Data.

Examples of RDBMS

MySQL
PostgreSQL
Oracle Database
Microsoft SQL Server

Use Cases

Ideal for transactional applications such as banking and e-commerce.
Suitable for data warehousing, where structured data is essential for analysis.

Description

Explore key concepts of relational databases in this quiz. Understand tables, primary and foreign keys, data integrity, and normalization. Test your knowledge of how these elements interact within a database management system.