Relational Database Management System (RDBMS) Quiz

Study Notes

RDBMS is a database software system organizing data into tables with defined relationships between them.
Tables consist of rows (records) and columns (attributes).
Relationships between tables are defined using foreign keys.
Key features include data integrity, consistency, and scalability.
Common examples include MySQL, PostgreSQL, Oracle, and SQL Server.
RDBMS follows ACID properties (Atomicity, Consistency, Isolation, Durability) for reliable transactions.

SQL (Structured Query Language) is a standard language for managing and querying databases.
Used to interact with RDBMS systems.
Core commands include SELECT, INSERT, UPDATE, DELETE.
SELECT queries retrieve data based on specified conditions.
Often involve filtering, sorting, and grouping data using clauses such as WHERE, ORDER BY, and GROUP BY.
Joins combine data from multiple tables based on related columns.
Common join types include inner join, left join, right join, and full outer join.
SQL also supports subqueries for complex filtering and aggregation.

Database design involves planning and structuring data for efficient storage and retrieval.
Normalization is a crucial design principle.
Normalization reduces data redundancy and improves data integrity by organizing data into multiple related tables.
Normal forms (e.g., 1NF, 2NF, 3NF) define various levels of normalization, each minimizing redundancy.
Entity-Relationship Diagrams (ERD) are visual representations of database tables and relationships.
ERDs help communicate the logical design to stakeholders.
Key components of ERD include entities, attributes, and relationships.
Database design also ensures data security and access control.

Indexes improve query performance by allowing quicker data retrieval.
Indexes are data structures that speed up lookups on specific columns or a combination of columns.
Suitable for frequently queried data or those involved in joining tables.
Indexes create an index structure to map values to rows in the database.
Choosing appropriate indexes is crucial for optimize query efficiency.
Performance is influenced by the volume of data and frequency of queries.
Appropriate indexes can dramatically improve query response times by significantly lowering the number of rows the database engine needs to scan.
Common index types include B-tree indexes and hash indexes.
Poor indexes may have a detrimental impact on database performance, potentially increasing query processing time.
Database administrators should monitor query performance and adjust indexes as needed to keep optimal performance.