Relational Database Concepts Flashcards
(32 cards)
What is a relational database?
A database that stores data in structured tables with relationships between tables.
Examples include Oracle, SQL Server, MySQL.
What is a Primary Key (PK)?
A column (or set of columns) that uniquely identifies each record in a table.
Example: EmployeeID in Employees table.
What is a Foreign Key (FK)?
A column that creates a relationship between two tables by linking to the primary key of another table.
Example: DepartmentID in Employees linking to Departments table.
What is database normalization?
Database normalization organizes tables to reduce redundancy and improve integrity.
Breaks data into related tables.
Follows “normal forms” like 1NF, 2NF, 3NF.
Goal: each data piece stored only once.
📖 Example: Separate Customers, Orders, and Products tables instead of repeating customer info.
Example: Moving repeated address fields into a separate Addresses table.
What is database denormalization?
Introducing redundancy to improve read performance.
Example: Adding DepartmentName directly into Employees table to avoid JOINs.
What is an INNER JOIN?
Combines rows from two tables only if they match on the join condition.
What is a LEFT JOIN?
Returns all rows from the left table, even if there’s no match in the right table.
Example of an INNER JOIN
SELECT * FROM Employees INNER JOIN Departments ON Employees.DepartmentID = Departments.ID;
Example of a LEFT JOIN
SELECT * FROM Employees LEFT JOIN Departments ON Employees.DepartmentID = Departments.ID;
What is an Index?
A database structure that speeds up data retrieval but can slow down writes.
Example: Indexing LastName for faster search queries.
What is a Transaction in databases?
A series of SQL operations that must be completed together, or not at all.
What are the ACID properties?
Atomicity, Consistency, Isolation, Durability — principles that guarantee reliable transactions.
What is a Query Execution Plan?
A report generated by the database showing how a SQL query will be executed.
Used for query optimization.
How to optimize a slow SQL query?
Add indexes on WHERE and JOIN columns, avoid SELECT *, use proper JOIN types, analyze execution plans.
What is Data Migration?
Moving data from a source system to a target system.
What are common issues during data migration?
Data type mismatches, missing data, encoding issues, duplicate records.
What is ETL?
Extract → Transform → Load. Process of moving and modifying data during migration.
Example of ETL process
Extract users from old CRM → Transform date formats to new system standards → Load into new CRM database.
What is Data Mapping?
Defining how fields from source system map to fields in target system.
Example of Data Mapping
Source: fname → Target: first_name; Source: phone_no → Target: phone_number.
What is Data Reconciliation?
Comparing source and target systems to ensure data integrity after migration.
How to perform Reconciliation?
Compare record counts, use checksum hashes, manually sample data entries.
What is Interface Development?
Building connections between systems to exchange data (APIs, file-based transfers, database links).
Example of an Interface
A REST API that sends customer order data from a website to a warehouse management system.
