Database Systems Flashcards
(13 cards)
Explain relational databases and normalization.
Relational databases organize data in tables with rows and columns, using SQL for queries. Normalization reduces data redundancy:
1NF: Eliminate repeating groups
2NF: Remove partial dependencies
3NF: Remove transitive dependencies
BCNF: Every determinant is a candidate key. Benefits include data integrity, reduced storage, and easier maintenance.
What is the difference between SQL and NoSQL databases?
SQL Databases: Structured, ACID-compliant, use fixed schemas, suitable for complex queries and transactions. Examples: MySQL, PostgreSQL, Oracle. NoSQL Databases: Flexible schemas, horizontally scalable, handle unstructured data. Types include:
Document stores (MongoDB)
Key-value stores (Redis)
Column-family (Cassandra)
Graph databases (Neo4j).
Explain ACID properties in databases.
Atomicity: Transactions are all-or-nothing operations. Consistency: Database remains in valid state after transactions. Isolation: Concurrent transactions don’t interfere with each other. Durability: Committed changes persist even after system failures. These properties ensure database reliability and data integrity in multi-user environments.
What are database indexes and how do they work?
Indexes are data structures that improve query performance by creating shortcuts to data locations. Types include:
B-tree indexes: Good for range queries and equality searches
Hash indexes: Excellent for exact matches
Bitmap indexes: Efficient for low-cardinality data
Composite indexes: Cover multiple columns. Trade-offs include faster queries vs. slower writes and additional storage overhead.
Describe database transactions and concurrency control.
Transactions are sequences of database operations treated as single units. Concurrency control manages simultaneous access:
Locking: Prevents conflicts through shared/exclusive locks
Optimistic Control: Assumes conflicts are rare, checks at commit
Pessimistic Control: Prevents conflicts proactively
Isolation Levels: Read uncommitted, read committed, repeatable read, serializable. Proper concurrency control prevents lost updates, dirty reads, and phantom reads.
What is database sharding and partitioning?
Techniques for distributing data across multiple storage systems:
Horizontal Partitioning/Sharding: Split rows across multiple databases
Vertical Partitioning: Split columns across multiple tables/systems
Functional Partitioning: Separate different features/services. Benefits include improved performance, scalability, and fault tolerance. Challenges include cross-shard queries, rebalancing, and maintaining consistency.
Explain CAP theorem in distributed databases.
CAP theorem states distributed systems can guarantee only two of three properties:
Consistency: All nodes see the same data simultaneously
Availability: System remains operational
Partition Tolerance: System continues despite network failures. Examples: CP systems (traditional databases), AP systems (eventual consistency), CA systems (single-node systems). Understanding CAP guides architectural decisions.
What are database views and stored procedures?
Views: Virtual tables based on query results, provide data abstraction, security, and simplified access to complex joins. Stored Procedures: Precompiled database programs that execute on server, offering better performance, security, and code reuse. Both help encapsulate business logic and provide abstraction layers between applications and raw data structures.
Describe data warehousing and OLAP vs OLTP.
OLTP (Online Transaction Processing): Handles real-time transactions, optimized for inserts/updates, normalized schemas. OLAP (Online Analytical Processing): Supports complex queries and analysis, optimized for reads, denormalized schemas. Data warehouses integrate data from multiple sources for analytical processing, using ETL processes and dimensional modeling (star/snowflake schemas).
What is database backup and recovery?
Backup strategies include:
Full Backup: Complete database copy
Incremental Backup: Changes since last backup
Differential Backup: Changes since last full backup
Transaction Log Backup: Continuous log of changes. Recovery involves restoring from backups and replaying transaction logs. Key metrics: Recovery Time Objective (RTO) and Recovery Point Objective (RPO).
Explain database security best practices.
Security measures include:
Authentication: Verify user identity
Authorization: Control access permissions
Encryption: Protect data at rest and in transit
Auditing: Log database activities
SQL Injection Prevention: Use parameterized queries
Network Security: Firewalls, VPNs, secure connections
Regular Updates: Patch security vulnerabilities. Comprehensive security requires multiple layers of protection.
What are database triggers and when to use them?
Triggers are special procedures that automatically execute in response to database events (INSERT, UPDATE, DELETE). Types:
Before Triggers: Execute before the triggering event
After Triggers: Execute after the triggering event
Instead Of Triggers: Replace the triggering event (views). Use cases: auditing, validation, automatic calculations, maintaining derived data. Should be used judiciously as they can impact performance.
Describe different database modeling techniques.
Database modeling approaches:
Conceptual Model: High-level business requirements (ER diagrams)
Logical Model: Platform-independent detailed design
Physical Model: Platform-specific implementation details
Dimensional Modeling: For data warehouses (facts and dimensions)
Object-Relational Mapping: Bridging OOP and relational concepts. Good modeling ensures data integrity, performance, and maintainability.
