Quiz 3

Question 1

What is the purpose of indexing?

Answer 1

Speeds up data retrieval by avoiding full table scans.

Question 2

What is a primary index?

Answer 2

Built on primary key; one entry per record; sorted.

Question 3

What is a secondary index?

Answer 3

Built on non-primary key; may point to multiple records.

Question 4

Define a dense index.

Answer 4

Every key value has an index entry.

Question 5

Define a sparse index.

Answer 5

One index entry per block; fewer entries, more efficient for storage.

Question 6

What is a multi-level index?

Answer 6

Index built over an index for scalability.

Question 7

What is hashing?

Answer 7

Converts key to hash for fast lookup. Static or dynamic.

Question 8

What are B+-Trees used for?

Answer 8

Balanced tree used for indexing; great for range queries.

Question 9

How do you insert into B+-Trees?

Answer 9

Insert → Split if needed → Push middle key up.

Question 10

What is a clustered index?

Answer 10

Physical data order matches index order; only one per table.

Question 11

What is an unclustered index?

Answer 11

Logical ordering via pointers; table data unordered.

Question 12

What is the SQL command for creating an index?

Answer 12

CREATE INDEX idx_name ON table(col);

Question 13

What are best practices for indexing?

Answer 13

Index frequently queried columns, avoid over-indexing.

Question 14

What is relational algebra (RA)?

Answer 14

Abstract way to write queries: σ (select), π (project), ⋈ (join).

Question 15

What does logical plan equivalence refer to?

Answer 15

Two RAs are equivalent if same output for all valid inputs.

Question 16

What are examples of equivalence in RA?

Answer 16

Selection pushdown, join associativity, commutativity.

Question 17

What is cardinality estimation?

Answer 17

Predicting the number of rows a query step will return.

Question 18

What is direct client access?

Answer 18

App directly connects to DB (less secure).

Question 19

What is server access?

Answer 19

App uses a middle layer (more secure, scalable).

Question 20

What does the CIA model stand for?

Answer 20

Confidentiality, Integrity, Availability.

Question 21

What is authentication?

Answer 21

Verifying user identity.

Question 22

What is authorization?

Answer 22

Verifying permissions.

Question 23

What is role-based access control?

Answer 23

Permissions assigned to roles, roles assigned to users.

Question 24

What is discretionary access control?

Answer 24

Data owner assigns access directly.

Question 25

What is the SQL command to grant permissions?

Answer 25

GRANT SELECT ON table TO user;

Question 26

What is the SQL command to revoke permissions?

Answer 26

REVOKE SELECT FROM user;

Question 27

What is application-level security?

Answer 27

Input validation, prevent SQL injection.

Question 28

What is SQL injection?

Answer 28

Code injection via unvalidated inputs. Use prepared statements.

Question 29

What does ACID stand for?

Answer 29

Atomicity, Consistency, Isolation, Durability.

Question 30

What is a lost update?

Answer 30

Overwriting another transaction’s changes.

Question 31

What is a dirty read?

Answer 31

Reading uncommitted data.

Question 32

What is an unrepeatable read?

Answer 32

Re-reading same data gives different results.

Question 33

What are the states of a transaction?

Answer 33

Active → Partially Committed → Committed / Aborted.

Question 34

What does scheduling determine?

Answer 34

Determines transaction execution order.

Question 35

What is a serial schedule?

Answer 35

Transactions run one after another (safe but slow).

Question 36

What is a serializable schedule?

Answer 36

Interleaved but equivalent to some serial order.

Question 37

What is conflict-serializability?

Answer 37

If no cycles in precedence graph → serializable.

Question 38

What is PostgreSQL MVCC?

Answer 38

Multi-Version Concurrency Control avoids locking.

Question 39

What does read committed mean?

Answer 39

Sees only committed rows; may see different versions.

Question 40

What does the serializable level prevent?

Answer 40

Prevents anomalies; forces serializable behavior.

Question 41

What is a distributed database?

Answer 41

Single logical DB spread across nodes.

Question 42

What is the difference between homogeneous and heterogeneous databases?

Answer 42

Same vs. different DB systems.

Question 43

What is the difference between synchronous and asynchronous replication?

Answer 43

Immediate vs delayed replication.

Question 44

What is partitioning in databases?

Answer 44

Splitting data: Horizontal (rows) vs Vertical (columns).

Question 45

What is replication?

Answer 45

Copies data across nodes for fault tolerance.

Question 46

What is the difference between ACID and BASE?

Answer 46

BASE is more relaxed: eventual consistency.

Question 47

What does the CAP theorem state?

Answer 47

Consistency, Availability, Partition Tolerance — pick 2.

Question 48

What is the difference between SQL and NoSQL?

Answer 48

SQL: structured, ACID. NoSQL: flexible, BASE.

Question 49

What is denormalization?

Answer 49

Fewer joins, more redundancy. Better for read performance.

Question 50

What is the structure of MongoDB?

Answer 50

Databases → Collections → Documents → Fields.

Question 51

What is the command to insert a single document in MongoDB?

Answer 51

insertOne().

Question 52

What is the command to insert multiple documents in MongoDB?

Answer 52

insertMany().

Question 53

What is the command to read documents in MongoDB?

Answer 53

find(), projections select specific fields.

Question 54

What is the command to delete a single document in MongoDB?

Answer 54

deleteOne().

Question 55

What is the command to delete multiple documents in MongoDB?

Answer 55

deleteMany().

Question 56

What is the command to update a single document in MongoDB?

Answer 56

updateOne(), updateMany() + $set.

Question 57

What is static Hashing?

Answer 57

open hashing (separate chaining) and closed hashing (open addressing)

Question 58

What is dynamic hashing?

Answer 58

offers a way for the hash table to adapt its size as needed.

Question 59

What is password hashing?

Answer 59

a cryptographic process that transforms a user's plain text password into a seemingly random, fixed-length string of characters called a hash