System Design > Database > Flashcards

Database Flashcards

(85 cards)

Study These Flashcards
1
Q

Define database.

A

A structured collection of data that can be accessed, managed, and updated.

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
2
Q

What are the two main types of databases?

A

Relational and NoSQL databases.

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
3
Q

True or false: Normalization reduces data redundancy.

A

TRUE

Normalization organizes data to minimize duplication and improve integrity.

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
4
Q

Fill in the blank: Indexing improves _______ speed in databases.

A

Query

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
5
Q

What does ACID stand for in databases?

A

Atomicity, Consistency, Isolation, Durability.

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
6
Q

True or false: BASE is an alternative to ACID.

A

TRUE

BASE stands for Basically Available, Soft state, Eventually consistent.

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
7
Q

What is a transaction in a database?

A

A sequence of operations performed as a single logical unit of work.

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
8
Q

Define sharding.

A

The process of splitting a database into smaller, more manageable pieces called shards.

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
9
Q

What is replication in databases?

A

The process of copying and maintaining database objects in multiple locations.

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
10
Q

What does the CAP theorem state?

A

A distributed data store can only guarantee two of the three: Consistency, Availability, Partition tolerance.

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
11
Q

Fill in the blank: Denormalization is the process of _______ normalization.

A

Reversing

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
12
Q

True or false: A primary key uniquely identifies a record in a table.

A

TRUE

Primary keys ensure that each record is distinct.

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
13
Q

What is a foreign key?

A

A field in one table that uniquely identifies a row in another table.

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
14
Q

Define data integrity.

A

The accuracy and consistency of data over its lifecycle.

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
15
Q

What is the purpose of a join in SQL?

A

To combine rows from two or more tables based on a related column.

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
16
Q

Fill in the blank: NoSQL databases are designed for _______ data models.

A

Flexible

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
17
Q

What is a view in a database?

A

A virtual table based on the result of a query.

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
18
Q

True or false: Stored procedures are precompiled SQL statements.

A

TRUE

Stored procedures improve performance and security.

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
19
Q

What is data warehousing?

A

The process of collecting and managing data from various sources for analysis.

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
20
Q

Define schema.

A

The structure that defines the organization of data in a database.

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
21
Q

What is a trigger in a database?

A

A set of instructions that automatically execute in response to certain events.

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
22
Q

Fill in the blank: Replication can be synchronous or _______.

A

Asynchronous

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
23
Q

What is data modeling?

A

The process of creating a data model to represent data structures and relationships.

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
24
Q

True or false: Horizontal scaling involves adding more machines.

A

TRUE

Horizontal scaling increases capacity by adding more servers.

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
25
What is **vertical scaling**?
Increasing the resources of a single machine to improve performance.
26
Define **transaction log**.
A record of all transactions that have modified the database.
27
What is **data redundancy**?
The unnecessary duplication of data within a database.
28
Fill in the blank: **Data mining** involves analyzing large datasets to discover _______.
Patterns
29
What is a **database management system (DBMS)**?
Software that interacts with the database, applications, and users.
30
True or false: **Cloud databases** are hosted on physical servers.
FALSE ## Footnote Cloud databases are hosted on virtual servers in the cloud.
31
What is **data consistency**?
The property that ensures data remains accurate and reliable across the database.
32
Define **entity-relationship model (ERM)**.
A diagram that represents the relationships between data entities in a database.
33
What is **data access**?
The ability to retrieve and manipulate data stored in a database.
34
Fill in the blank: **SQL** stands for Structured _______ Language.
Query
35
Define **JOIN** in SQL.
A SQL operation that combines rows from two or more tables based on a related column.
36
What does **INNER JOIN** do?
Returns records that have matching values in both tables.
37
True or false: **LEFT JOIN** includes unmatched rows from the right table.
FALSE ## Footnote LEFT JOIN includes all rows from the left table and matched rows from the right.
38
Fill in the blank: **FULL OUTER JOIN** returns all records from both ______.
tables
39
What is the purpose of the **GROUP BY** clause?
To arrange identical data into groups for aggregation.
40
Define **HAVING** in SQL.
A clause used to filter records after aggregation with GROUP BY.
41
True or false: **ORDER BY** can sort results by multiple columns.
TRUE
42
What is the function of **COMMIT**?
To save all changes made during the current transaction.
43
Fill in the blank: **ROLLBACK** is used to ______ changes in a transaction.
undo
44
What does **CROSS JOIN** produce?
A Cartesian product of two tables, combining every row from both.
45
Define **SELF JOIN**.
A join where a table is joined with itself.
46
What is the result of a **RIGHT JOIN**?
All records from the right table and matched records from the left.
47
True or false: **UNION** combines results from multiple SELECT statements.
TRUE
48
Fill in the blank: **DISTINCT** is used to remove ______ from results.
duplicate rows
49
What does **COUNT()** function do?
Counts the number of rows that match a specified condition.
50
Define **AVG()** function.
Calculates the average value of a numeric column.
51
What is the purpose of **SUM()**?
To return the total sum of a numeric column.
52
Fill in the blank: **MIN()** returns the ______ value in a set.
smallest
53
What does **MAX()** function return?
The largest value in a set.
54
True or false: **GROUP BY** can be used without aggregate functions.
FALSE
55
What is the effect of using **HAVING** without **GROUP BY**?
It results in an error since HAVING requires grouped data.
56
Fill in the blank: **JOIN** conditions are specified using the ______ keyword.
ON
57
Define **NATURAL JOIN**.
A type of join that automatically matches columns with the same name.
58
What does **EXISTS** do in a SQL query?
Checks for the existence of any record in a subquery.
59
True or false: **ANY** and **ALL** can be used with subqueries.
TRUE
60
Fill in the blank: **CASE** is used for ______ in SQL.
conditional logic
61
What is the purpose of **LIMIT** clause?
To specify the maximum number of records to return.
62
Define **OFFSET** in SQL.
Specifies the number of rows to skip before starting to return rows.
63
What does **INNER JOIN** require?
Matching rows in both tables based on a specified condition.
64
Fill in the blank: **UNION ALL** includes ______ duplicates.
all
65
True or false: **GROUP BY** can include non-aggregated columns.
FALSE
66
What is the result of using **DISTINCT** with **COUNT()**?
Counts unique values in a column.
67
Define **Subquery**.
A query nested inside another SQL query.
68
What does **WITH** clause do?
Defines a temporary result set for use in a subsequent query.
69
Fill in the blank: **COALESCE** returns the first non-______ value.
NULL
70
What is the purpose of **TRUNCATE**?
To delete all rows in a table without logging individual row deletions.
71
Define **Redis**.
An in-memory data structure store, used as a database, cache, and message broker.
72
What is **Cassandra** primarily used for?
A distributed NoSQL database designed for handling large amounts of data across many servers.
73
True or false: **Redis** supports data persistence.
TRUE ## Footnote Redis can persist data to disk using RDB snapshots or AOF logs.
74
Fill in the blank: **Cassandra** uses a _______ model for data storage.
wide-column
75
What does **eventual consistency** mean?
A consistency model where updates will propagate to all replicas eventually.
76
Define **strong consistency**.
A consistency model guaranteeing that all reads return the most recent write.
77
What is a **deadlock**?
A situation where two or more transactions are waiting indefinitely for each other to release locks.
78
True or false: **Cassandra** provides strong consistency by default.
FALSE ## Footnote Cassandra offers tunable consistency levels, allowing for eventual or strong consistency.
79
What is the purpose of **read/write consistency**?
To ensure that data reads and writes are synchronized across distributed systems.
80
Fill in the blank: **Redis** uses _______ as its primary data structure.
key-value pairs
81
What is **Cassandra's** partitioning strategy?
It uses consistent hashing to distribute data across nodes.
82
Define **optimistic concurrency control**.
A method that assumes conflicts are rare and checks for conflicts before committing changes.
83
What is the **CAP theorem**?
A principle stating that a distributed data store can only guarantee two of the following: Consistency, Availability, Partition tolerance.
84
True or false: **Redis** is primarily disk-based.
FALSE ## Footnote Redis is primarily an in-memory database, though it can persist data to disk.
85
What is a **write quorum**?
A requirement that a certain number of replicas must acknowledge a write before it is considered successful.
System Design
flashcards
Decks in class (24)
# Cards
Brainscape helps you reach your goals faster, through stronger study habits.
© 2026 Bold Learning Solutions. Terms and Conditions