Databases and SQL Flashcards
What is normalization?
The process of organizing data to reduce redundancy and improve data integrity.
Normalization breaks large tables into smaller ones and establishes relationships using foreign keys.
What is denormalization?
The process of combining tables to improve read performance by introducing redundancy.
Denormalization is often used in data warehousing where read speed is more critical than write efficiency.
What does the ‘A’ in ACID properties stand for?
Atomicity.
Atomicity means a transaction is either completed or not done at all.
What does the ‘C’ in ACID properties stand for?
Consistency.
Consistency means the database must be valid before and after a transaction.
What does the ‘I’ in ACID properties stand for?
Isolation.
Isolation ensures transactions happen independently without interference.
What does the ‘D’ in ACID properties stand for?
Durability.
Durability means once committed, a transaction remains even in case of a failure.
What is a transaction?
A unit of work performed against a database.
Rollback ensures actions don’t partially execute in an operation failure scenario.
What is an index?
A data structure that improves the speed of data retrieval.
What is a primary index?
Based on the primary key (unique identifier).
What is a secondary index?
Non-primary key columns.
What is a composite index?
Index on multiple columns.
What is an inner join?
Returns matching records from both tables.
What is a left join?
Returns all records from the left table, with matching row from the right.
What is a right join?
Returns all records from the right table, with matching rows on the left.
What does FULL OUTER JOIN do?
Returns record when there is a match in either table.
What is the first normalization form (1NF)?
Eliminate repeating groups and contain a primary key.
Mixing data types within the same column is not permitted (atomic columns).
What is the second normalization form (2NF)?
Remove partial dependency.
Every non-key column must depend on the whole primary key.
What is the third normalization form (3NF)?
Remove transitive dependencies.
Every non-key attribute should depend on the key, the whole key and nothing but the key.
What is the fourth normalization form (4NF)?
Multivalued dependencies in a table must be multivalued dependencies on the key.
What is the fifth normalization form (5NF)?
The table cannot be described as the logical result of joining some other tables together.
What are foreign keys and how do they ensure referential integrity?
A foreign key is a column or combination of columns in a table whose value matches in another column. It ensures referential integrity by enforcing the rule the every foreign key value refers to a valid primary key.
Explain the difference between clustered and non-clustered indexes:
Clustered index determines/stores the physical order of data in a table. Non-Clustered index does not affect the physical order but instead contains a copy of the index columns and a pointer to the actual data.
What is a deadlock in databases, and how can you prevent it?
When two or more transactions are stuck waiting for each to release locks on resources they need, creating a standstill situation.
How would you optimize a slow-performing SQL query
- Use indexes effectively
- Reduce the scope of query
- Optimize JOIN operations
- Normalize database tables
- Utilize stored procedures
What are stored procedures and when would you use them?
A prepared SQL code that you can save and reuse. When you want to reduce time to query the same information, as the stored procedure is already compile.
How do database transactions work in distributed systems?
In a distributed system, data is spread across multiple databases or servers. Database transactions have to follow the principles of ACID, to ensure data consistency and atomicity.
Explain sharding vs partitioning in databases
Sharding disperses data across various databases or servers, while partitioning segregates data within a single database instance into subsets.
