RDBMS Fundamentals

Question 1

What is an RDBMS?

Answer 1

A Relational Database Management System stores data in structured formats using tables with rows and columns.

Question 2

What are tables in RDBMS?

Answer 2

Tables are collections of related data organized in rows and columns.

Question 3

What is a row in a table?

Answer 3

A row represents a single record or entry in a table.

Question 4

What is a column in a table?

Answer 4

A column defines a specific attribute or field in the table, such as name or age.

Question 5

What is a primary key?

Answer 5

A primary key uniquely identifies each row in a table and cannot contain NULL values.

Question 6

What is a foreign key?

Answer 6

A foreign key links a column in one table to the primary key of another table to establish relationships.

Question 7

What is a composite key?

Answer 7

A composite key is a combination of two or more columns used together to uniquely identify a row.

Question 8

What is a NOT NULL constraint?

Answer 8

Ensures a column cannot have NULL values.

Question 9

What is a UNIQUE constraint?

Answer 9

Ensures all values in a column are different.

Question 10

What is a CHECK constraint?

Answer 10

Ensures that all values in a column satisfy a specific condition.

Question 11

What is a DEFAULT constraint?

Answer 11

Provides a default value for a column when none is specified.

Question 12

What is a One-to-One relationship?

Answer 12

Each row in Table A is linked to one and only one row in Table B.

Question 13

What is a One-to-Many relationship?

Answer 13

A row in Table A can be linked to multiple rows in Table B.

Question 14

What is a Many-to-Many relationship?

Answer 14

Rows in Table A can be linked to multiple rows in Table B and vice versa, usually via a join table.

Question 15

What is normalization?

Answer 15

The process of organizing data to reduce redundancy and improve data integrity.

Question 16

What is First Normal Form (1NF)?

Answer 16

A table is in 1NF if it contains only atomic values and each column contains values of a single type.

Question 17

What is Second Normal Form (2NF)?

Answer 17

A table is in 2NF if it is in 1NF and all non-key attributes are fully dependent on the primary key.

Question 18

What is Third Normal Form (3NF)?

Answer 18

A table is in 3NF if it is in 2NF and all its attributes are not transitively dependent on the primary key.

Question 19

What is BCNF?

Answer 19

Boyce-Codd Normal Form is a stronger version of 3NF where every determinant is a candidate key.

Question 20

What is denormalization?

Answer 20

The process of merging normalized tables to improve read performance at the cost of redundancy.

Question 21

What are the advantages of normalization?

Answer 21

Reduces redundancy, improves data integrity, and simplifies data maintenance.

Question 22

What are the disadvantages of normalization?

Answer 22

Can result in complex queries and slower read performance due to multiple joins.

Question 23

What are the advantages of denormalization?

Answer 23

Improves query performance by reducing joins.

Question 24

What are the disadvantages of denormalization?

Answer 24

Can lead to data redundancy and update anomalies.

Question 25

What is a best practice for defining primary keys?

Answer 25

Use a single, immutable, and simple column as the primary key.

Question 26

What is a best practice for relationships?

Answer 26

Use foreign keys with proper indexing to maintain data integrity and performance.

Question 27

What is a common use case for One-to-Many relationships?

Answer 27

A customer and their orders.

Question 28

What is a use case for Many-to-Many relationships?

Answer 28

Students and courses where a student can enroll in multiple courses and vice versa.

Question 29

What is the impact of normalization on system design?

Answer 29

Improves data consistency but may add query complexity and performance overhead.

Question 30

What is the architectural implication of denormalization?

Answer 30

Reduces read latency in read-heavy applications but increases write complexity.

Question 31

How does normalization affect performance?

Answer 31

Improves write performance and integrity but may slow down reads due to joins.

Question 32

What tools help in debugging relational databases?

Answer 32

SQL log analyzers, EXPLAIN query plans, and database performance monitoring tools.

Question 33

What is a real-world tradeoff in normalization?

Answer 33

Better data integrity vs. potential performance issues in large-scale reads.

Question 34

What is a common interview question about keys?

Answer 34

What is the difference between a primary key and a foreign key?

Question 35

What is a common gotcha with foreign keys?

Answer 35

Not indexing foreign keys can degrade join performance.

Question 36

What’s a common issue in denormalized designs?

Answer 36

Increased chance of data inconsistency during updates or deletes.

Question 37

What’s a gotcha with composite keys?

Answer 37

They can make foreign key references more complex and error-prone.

Question 38

Why is 3NF preferred in most OLTP systems?

Answer 38

It maintains data integrity and avoids redundant updates.

Question 39

When should you consider denormalization?

Answer 39

When you need to optimize for fast read access and analytics queries.

Question 40

Can a table have multiple foreign keys?

Answer 40

Yes, a table can have multiple foreign keys referencing different tables.

Question 41

What is a candidate key?

Answer 41

An attribute, or a set of attributes, that can uniquely identify a row in a table.

Question 42

What is a surrogate key?

Answer 42

An artificial key (e.g., auto-increment ID) used as the primary key instead of a natural key.