Understanding Databases Flashcards
(17 cards)
What is a database and what are its three main purposes?
A structured data storage system designed to allow for: 1. Ease of data accessibility, 2. Data manipulation, 3. Data manageability
What are structured and unstructured data? Give 2 examples of each with explanations.
Structured: Data organized in tables - relational customer records (Name/Email fields), Excel employee spreadsheets (salary/department columns). Unstructured: No fixed format - social media posts (mixed text/images/videos), scanned PDF contracts (can’t be directly queried)
Classify these data types: quarterly sales report with Date/Salesperson/Revenue columns vs MP3 customer service recordings. Explain your reasoning.
Sales report: Structured (defined columns, consistent format). MP3 recordings: Unstructured (audio files with no structured metadata that can’t fit in database tables)
What does ACID stand for and define each property?
Atomicity (transactions are all-or-nothing), Consistency (data remains valid before/after transactions), Isolation (transactions don’t interfere with each other), Durability (completed transactions are permanently saved)
Explain atomicity with a banking transfer example. What happens if the transfer fails halfway?
Either the complete transaction succeeds (money debited AND credited) or the entire transaction is aborted. If it fails halfway, the system rolls back all changes to prevent partial completion
What does consistency prevent? Give a concrete example of what could go wrong without it.
Prevents invalid data states. Without consistency: bank accounts could show negative balances when overdrafts aren’t allowed, or inventory could display -5 items in stock
Two users simultaneously update the same customer record. How does isolation prevent problems?
Isolation ensures each transaction acts as if it’s the only one running, preventing data corruption from simultaneous modifications through proper transaction sequencing
Your database crashes immediately after completing a transaction. What does durability guarantee?
Durability guarantees that completed transactions survive system failures, crashes, or power outages - all changes are permanently saved
How do databases provide better data integrity than file systems?
Databases enforce constraints and ACID properties to validate data, while file systems save whatever is entered without validation, even if there are errors or inconsistencies
Compare data retrieval efficiency between databases and file systems.
Databases use targeted queries for efficient data retrieval. File systems require scanning entire files to locate specific information, which is much slower and resource-intensive
What security advantages do databases have over file systems?
Databases provide authentication (user verification) and authorization (granular access permissions). File systems lack these built-in security controls
Give a specific example of how database constraints prevent errors that file systems cannot.
Example: Email field requiring valid format (@domain.com). Databases can enforce this rule and reject invalid entries like ‘notanemail’. File systems accept any data without validation
Name the four types of databases from your notes.
- Relational databases, 2. NoSQL databases, 3. Graph databases, 4. Object-Oriented databases
You need to store social connections (who knows whom) and analyze relationship patterns. Which database type is most appropriate and why?
Graph databases - specifically designed to handle relationships and connections between entities, making it efficient to query and analyze social networks and relationship patterns
A company stores employee records (ID, name, salary, department) and needs complex reporting. Which database type and what are two key advantages?
Relational database. Advantages: 1. Perfect structure for defined relationships between data fields, 2. Supports complex SQL queries for detailed analysis and reporting
An e-commerce transaction deducts inventory but fails before charging the customer. Which ACID property prevents this issue and what should happen?
Atomicity prevents this. Since the complete transaction (inventory deduction + payment) didn’t succeed, the system should roll back and restore inventory to its original level
You’re designing storage for: customer names, order timestamps, product review text, and product photos. Categorize each and justify your reasoning.
Structured: Customer names (fixed field format), order timestamps (standardized datetime format). Unstructured: Review text (free-form content), product photos (image files) - these lack predefined structure for database tables
