DBMS interview questions and answers

Question 1

What is a database and what is a DBMS?

Accepted Answer

A database is an organised collection of related data stored so it can be efficiently retrieved and updated. A DBMS, or Database Management System, is the software layer sitting between the users and the physical data files, it handles storage, retrieval, security, concurrency, and recovery so that applications never touch raw files directly. MySQL, PostgreSQL, Oracle, and SQL Server are DBMS products; the customer data inside them is the database. Analogy: a library. The books and shelves are the database. The librarian is the DBMS, they know where everything is, control who can borrow what, stop two people taking the same copy, and put things back if something goes wrong. In an interview: The clean framing is that a DBMS turns data into a managed resource. Without it, every application would have to reimplement locking, crash recovery, and access control on top of flat files, badly, and differently each time.

Question 2

What are the problems with a file-based system that a DBMS solves?

Accepted Answer

In a file system each application owns its own files, which causes five classic problems. Data redundancy, the same customer address is stored in three files. Data inconsistency, one copy gets updated and the others do not. Difficult access, every new query needs a new program written. Data isolation, data is scattered across different file formats. And no support for concurrency, recovery, security, or integrity constraints, because files simply do not offer any of that. Analogy: everyone in an office keeping their own private spreadsheet of clients. Three versions of every phone number, none of them agreeing, and no way to know which is right. In an interview: Redundancy is the root cause. Almost every other file-system problem is a symptom of the same fact being physically stored in more than one place with nothing keeping the copies in step.

Question 3

Explain the three-schema architecture and data independence.

Accepted Answer

ANSI-SPARC defines three levels. The external or view level is what each user group sees, a subset of the data shaped for their needs. The conceptual or logical level is the whole database described as tables, columns, and relationships, independent of how it is stored. The internal or physical level is how the bytes actually sit on disk, files, pages, indexes. Separating them gives data independence: logical independence means you can change the conceptual schema without rewriting user views, and physical independence means you can change the storage or add an index without touching the logical schema. Analogy: a building. Tenants see their own apartment (external). The architect sees the floor plan of the whole building (conceptual). The engineer sees the concrete, wiring, and pipes (internal). Rewire the electrics and nobody's apartment layout changes. In an interview: Physical independence is easy to achieve and every DBMS gives it. Logical independence is much harder, because a view that selects specific columns will break if those columns disappear, which is exactly why views are the tool used to buffer applications from schema change.

Question 4

What is the difference between a schema and an instance?

Accepted Answer

A schema is the structure, the table definitions, column names, data types, and constraints. It is designed once and changes rarely. An instance is the actual data sitting in the database at a particular moment in time. The schema is like a variable declaration; the instance is like the value the variable currently holds. The schema is metadata, the instance is data. Analogy: an empty form versus a filled-in form. The blank template with labelled boxes is the schema. Every completed copy sitting in the filing cabinet right now is the instance. In an interview: The related distinction is that schema changes go through DDL and are relatively expensive and risky, while instance changes go through DML and happen constantly. A schema is a design decision; an instance is a fact.

Question 5

What are the main data models?

Accepted Answer

A data model is a set of concepts for describing data, relationships, and constraints. The hierarchical model organises data as a tree with one parent per child. The network model allows a record to have several parents, forming a graph. The relational model, which dominates, stores everything as tables of rows and columns with relationships expressed by matching values. The entity-relationship model is a conceptual design model used to draw the database before implementing it. The object-oriented and document models store richer nested structures. Analogy: different ways to file paperwork. A tree of folders inside folders (hierarchical), a web of cross-references (network), a set of cross-indexed tables (relational), or a stack of self-contained documents each carrying everything it needs (document). In an interview: The relational model won because it is based on mathematics, set theory and predicate logic, which gave it a declarative query language and a formal basis for optimisation. You say what you want, not how to navigate to it.

Question 6

Who are the different database users and what does a DBA do?

Accepted Answer

End users run queries or use applications and may never see SQL. Application programmers write the software that issues queries. Database designers decide the schema and constraints. The Database Administrator, or DBA, owns the running system: they define the schema, grant and revoke access, tune performance, plan capacity, set up backups, and handle recovery after a failure. Naive users interact only through forms; sophisticated users write their own ad-hoc queries. Analogy: a building again. Residents use it, the architect designed it, and the building manager holds the keys, fixes the boiler, and has the fire evacuation plan. In an interview: The part interviewers usually probe is backup and recovery, because it is the one DBA duty where getting it wrong is unrecoverable. Everything else can be redone.

Question 7

What are the advantages and disadvantages of a DBMS?

Accepted Answer

The advantages are controlled redundancy, enforced consistency and integrity, shared access with concurrency control, security through fine-grained privileges, backup and recovery, and data independence. The disadvantages are real and worth naming honestly: a DBMS is complex and needs skilled staff, licensing and hardware can be expensive, there is performance overhead compared with reading a plain file, and centralising everything means a single point of failure if it is not replicated. Analogy: hiring a professional accountant instead of keeping receipts in a shoebox. Far safer and more useful, but it costs money, requires expertise, and you are now dependent on them. In an interview: The honest answer to when you should not use a DBMS is when the data is small, single-user, and read-mostly, a config file or an embedded SQLite file is often the right engineering call.

Question 8

What is metadata and what is the data dictionary?

Accepted Answer

Metadata is data about data, table names, column names, data types, constraints, index definitions, user privileges, and storage statistics. The DBMS stores all of this in the data dictionary, also called the system catalog, which is itself just a set of tables the DBMS maintains. That is why you can query the catalog with ordinary SQL to discover the structure of a database you have never seen. Analogy: the library's own card catalogue. It is not any of the books, but it describes every one of them, and it lives on a shelf just like everything else. In an interview: The statistics part matters most in practice. The query optimiser makes its plan choices from catalog statistics, so stale statistics after a bulk load are a very common cause of a query suddenly becoming slow.

Question 9

What is the ER model and what are its components?

Accepted Answer

The Entity-Relationship model is a conceptual design tool for describing a database before you build it. It has three building blocks. An entity is a distinguishable real-world object, like a Student, and an entity set is the collection of all entities of that type. An attribute is a property of an entity, like name or gpa. A relationship is an association between entities, like a Student enrols in a Course. It is drawn as a diagram, rectangles for entities, ellipses for attributes, diamonds for relationships. Analogy: sketching a floor plan before pouring concrete. It is cheap to move a wall on paper and extremely expensive to move it afterwards. In an interview: The value of the ER model is that it is implementation-neutral. You can discuss it with a non-technical stakeholder, agree the meaning, and only then argue about tables and data types.

Question 10

What are the types of attributes in an ER model?

Accepted Answer

A simple attribute is atomic and cannot be divided, like age. A composite attribute breaks into parts, like address splitting into street, city, and postcode. A single-valued attribute holds one value, while a multi-valued attribute holds several, like phone numbers. A derived attribute is calculated from other data rather than stored, like age derived from date of birth. A key attribute uniquely identifies an entity. And a null-valued attribute is one that may be unknown or inapplicable. Analogy: fields on a passport form. Some are single boxes, some split into subfields, some let you list several entries, and some the clerk works out from what you already wrote. In an interview: The two that always come up in mapping are multi-valued attributes, which must become their own table to satisfy first normal form, and derived attributes, which you normally do not store because storing them creates an update anomaly.

Question 11

What is a relationship, and what are degree and cardinality?

Accepted Answer

A relationship is an association among entities. Its degree is the number of entity sets participating: unary or recursive involves one entity set related to itself, binary involves two, and ternary involves three. Cardinality describes how many entities on each side may participate, one-to-one, one-to-many, many-to-one, or many-to-many. Relationships can also have their own attributes, called descriptive attributes, such as the grade in an enrolment. Analogy: degree is how many parties are in the contract. Cardinality is how many people each party is allowed to sign with. In an interview: The rule to remember for mapping is that a many-to-many relationship always becomes its own table holding the two foreign keys as a composite primary key, plus any descriptive attributes. One-to-many just needs a foreign key on the many side.

Question 12

What is the difference between total and partial participation?

Accepted Answer

Participation constraints say whether every entity in a set must take part in a relationship. Total participation, drawn as a double line, means every entity must participate, for example every Loan must be held by some Customer. Partial participation, drawn as a single line, means participation is optional, not every Customer holds a Loan. Together with cardinality, this is what determines whether a foreign key column can be NULL. Analogy: a school where every pupil must be assigned to a class (total), but not every teacher has to be a form tutor (partial). In an interview: The practical payoff is exactly that NOT NULL decision. Total participation is the ER-level statement of a NOT NULL foreign key, and getting it wrong is how orphaned rows appear later.

Question 13

What is a weak entity set?

Accepted Answer

A weak entity is one that cannot be uniquely identified by its own attributes alone, it depends on another entity, called the owner or identifying entity. It has a partial key, or discriminator, that is unique only within the context of one owner. The relationship connecting them is the identifying relationship, and a weak entity always has total participation in it. When mapped to a table, its primary key is the owner's primary key plus its own discriminator. Analogy: a room number in a hotel. Room 204 means nothing on its own, there is a room 204 in every hotel in the country. It only becomes unique when paired with the hotel. In an interview: The ON DELETE CASCADE is the giveaway. A weak entity has no independent existence, so deleting the owner must delete it, which is a real behavioural difference from an ordinary foreign key.

Question 14

Explain the types of keys in a database.

Accepted Answer

A super key is any set of attributes that uniquely identifies a row, including sets with unnecessary extras. A candidate key is a minimal super key, remove any attribute and it stops being unique. The primary key is the one candidate key chosen as the main identifier; the remaining candidates are alternate keys. A composite key is a key made of more than one column. A foreign key is a column referencing the primary key of another table. A surrogate key is an artificial identifier like an auto-increment id, with no business meaning. Analogy: ways to identify a person. Passport number plus name plus address is a super key, it works, but the name and address are redundant. Passport number alone is a candidate key. The one your government actually prints on the ID card is the primary key. In an interview: Every primary key is a candidate key, and every candidate key is a super key, but not the reverse. The word that separates candidate from super is minimal.

Question 15

How do you map an ER diagram to relational tables?

Accepted Answer

Each strong entity becomes a table with its own primary key. A weak entity becomes a table whose primary key is the owner's key plus its discriminator. A one-to-one relationship puts a foreign key on either side, preferably the side with total participation. A one-to-many relationship puts the foreign key on the many side. A many-to-many relationship becomes its own table holding both foreign keys as a composite primary key. A multi-valued attribute becomes a separate table. Composite attributes flatten into one column per component. Analogy: translating a sketch into a parts list. Every shape on the drawing has a fixed rule for what it becomes on the build sheet, so the translation is mechanical once you know the rules. In an interview: The single rule people forget is that a many-to-many relationship can never be represented without a third table. If a design has an M:N line and only two tables, it is wrong.

Question 16

What are generalisation, specialisation, and aggregation?

Accepted Answer

Specialisation is top-down: you take a general entity like Employee and split it into subclasses like Manager and Engineer that have extra attributes. Generalisation is the same relationship seen bottom-up: you notice Car and Truck share attributes and lift them into a common Vehicle superclass. Both create an IS-A hierarchy with attribute inheritance. Aggregation is different, it treats an entire relationship as if it were a single entity, so that another relationship can connect to it. Analogy: generalisation and specialisation are the same staircase walked in opposite directions. Aggregation is putting a box around a whole scene so you can draw an arrow to the scene itself rather than to one thing in it. In an interview: In mapping, an IS-A hierarchy gives you three choices: one table per subclass, one wide table with nullable columns, or a table per class in the hierarchy. Which you pick is a trade-off between nulls, joins, and query patterns.

Question 17

What is the relational model and its terminology?

Accepted Answer

The relational model, proposed by E.F. Codd in 1970, represents everything as relations, tables. A relation is a table. A tuple is a row. An attribute is a column. A domain is the set of legal values for an attribute. The degree of a relation is its number of attributes, and the cardinality is its number of tuples. Formally a relation is a set of tuples, which means duplicate rows are not allowed and row order carries no meaning. Analogy: a spreadsheet with strict rules. Every column has one type of thing in it, no two rows are identical, and shuffling the rows changes nothing. In an interview: The set-based definition is what interviewers probe. Because a relation is a set, order is meaningless, which is why a query without ORDER BY has no guaranteed row order, no matter what you observed last time you ran it.

Question 18

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

Accepted Answer

Both enforce uniqueness. The differences are that a table can have only one primary key but many unique keys, a primary key can never contain NULL while a unique key generally allows one NULL, and most databases automatically build a clustered index on the primary key while a unique constraint creates a non-clustered one. Semantically, the primary key is the chosen identity of the row; a unique key is just a business rule saying two rows must not share a value. Analogy: the primary key is your national ID number, one per person, never blank. A unique key is your email address, also unique, but you might not have one, and you could have several kinds of unique contact details. In an interview: One nuance worth knowing: the SQL standard allows multiple NULLs in a unique column because NULL is not equal to NULL, but SQL Server permits only one. That inconsistency between vendors catches people out during migrations.

Question 19

What is a foreign key and what is referential integrity?

Accepted Answer

A foreign key is a column, or set of columns, in one table whose values must match a primary key or unique key value in another table, or be NULL. Referential integrity is the rule that this must always hold, you cannot insert an order for a customer who does not exist, and you cannot delete a customer who still has orders unless you say what should happen to them. The referenced table is the parent; the referencing table is the child. Analogy: a library loan slip that names a member. The slip is only valid if that membership number actually exists. You cannot lend a book to a member who was never registered, and you cannot delete a member who still has books out without deciding what to do about the books. In an interview: The important detail is that a foreign key must reference a primary key or a unique key, it cannot point at an ordinary column, because the reference would then be ambiguous.

Question 20

What are the referential actions like ON DELETE CASCADE?

Accepted Answer

Referential actions tell the DBMS what to do to child rows when a parent row is deleted or its key is updated. CASCADE propagates the delete or update to the children. SET NULL sets the child's foreign key to NULL, which requires the column to be nullable. SET DEFAULT sets it to the column default. RESTRICT rejects the operation immediately. NO ACTION rejects it too, but the check is deferred to the end of the statement. Analogy: closing a department. Cascade fires everyone in it. Set null leaves them employed but unassigned. Restrict refuses to close the department until you have moved everyone out yourself. In an interview: CASCADE is convenient and dangerous. A cascade chain three tables deep can quietly delete far more than the person running the statement intended, so in production systems many teams deliberately use RESTRICT and do the cleanup explicitly.

Question 21

What are the integrity constraints in a relational database?

Accepted Answer

Domain integrity restricts a column to values from its declared domain, the right data type, plus CHECK and NOT NULL constraints. Entity integrity says no part of a primary key may be NULL, because a NULL identifier could not identify anything. Referential integrity says every foreign key value must match an existing parent key or be NULL. Key integrity enforces uniqueness. And user-defined or business constraints capture rules the type system cannot, such as a discount never exceeding the price. Analogy: rules on a form. The date box must contain a date (domain), the reference number can never be blank (entity), the department code you write must be a real one (referential), and the total must equal the sum of the lines (business rule). In an interview: The design principle is to push every rule you can into the database rather than the application. Application code can be bypassed by a script, a migration, or a second service, a CHECK constraint cannot.

Question 22

What does NULL really mean and why is it awkward?

Accepted Answer

NULL is not zero and not an empty string, it means the value is unknown or inapplicable. Because it is unknown, comparing anything with NULL using equals gives neither true nor false but UNKNOWN, which is why SQL uses three-valued logic. That has consequences everywhere: WHERE clauses only keep rows that evaluate to true, so a NULL comparison filters the row out; aggregate functions skip NULLs; and you must test with IS NULL rather than equals NULL. Analogy: a blank on a form. It does not mean the answer is zero, it means nobody knows the answer. Asking 'is the unknown value equal to 5?' honestly has no yes-or-no answer. In an interview: The trap I would name is NOT IN with a subquery that returns any NULL, it silently returns zero rows, because the comparison becomes UNKNOWN. Using NOT EXISTS instead avoids the whole problem.

Question 23

What is relational algebra and what are its main operations?

Accepted Answer

Relational algebra is the formal, procedural query language underlying SQL, it describes how to compute a result as a sequence of operations on relations. The fundamental operations are selection, which picks rows by a condition; projection, which picks columns; union, set difference, and Cartesian product; and rename. Derived operations built from those include intersection, various joins, and division. Every SQL query is translated into a relational algebra expression that the optimiser then rewrites. Analogy: a set of scissors and glue for tables. Cut rows out, cut columns out, stack two tables, or pair every row of one with every row of the other, everything else is a combination of those moves. In an interview: The reason this matters practically is optimisation. Pushing selection below a join in the algebra, filtering before joining rather than after, is exactly the rewrite the query optimiser performs, and it can change runtime by orders of magnitude.

Question 24

What is the difference between relational algebra and relational calculus?

Accepted Answer

Relational algebra is procedural, it specifies the sequence of operations that produce the result, so it says how. Relational calculus is declarative, it describes the properties the result must have, so it says what, using predicate logic. Tuple relational calculus quantifies over tuples, and domain relational calculus quantifies over attribute values. SQL is closer to calculus in style but is implemented via algebra internally. Analogy: algebra is a recipe listing the steps. Calculus is a description of the finished dish, leaving it to the chef to work out the steps. In an interview: Codd's theorem is worth mentioning: relational algebra and relational calculus have exactly the same expressive power. That equivalence is what lets you write declarative SQL and still get an efficient procedural plan.

Question 25

Can a table have no primary key, and should it?

Accepted Answer

Technically yes, the SQL standard permits a table with no primary key, and such tables are called heap tables in some engines. But it is almost always a mistake. Without a key you can have exact duplicate rows that no statement can distinguish, so you cannot reliably update or delete one of them. Replication, foreign keys, and clustered storage all depend on a key. The rare legitimate exceptions are append-only log or staging tables where rows are never individually addressed. Analogy: a crowd of people with no names and no ID. You can count them, but you cannot ask one specific person to step forward. In an interview: In the relational model proper, duplicate tuples cannot exist because a relation is a set. SQL tables are technically bags, not sets, and a missing primary key is where that gap becomes a real operational problem.

Question 26

What is normalisation and what anomalies does it prevent?

Accepted Answer

Normalisation is the process of organising columns and tables to reduce redundancy and dependency, by decomposing a large table into smaller related ones. It exists to eliminate three anomalies. An insertion anomaly is when you cannot record one fact without also knowing another, you cannot add a new department until someone works in it. An update anomaly is when a fact stored in many rows gets changed in some but not all of them. A deletion anomaly is when removing one fact accidentally destroys another, deleting the last employee deletes the department's existence. Analogy: writing the head office address on every single invoice. Move office and you must find and correct thousands of invoices. Miss one and your records now disagree with themselves. In an interview: The one-line summary is that normalisation makes every fact appear exactly once, so there is only ever one place to change it and no way for copies to disagree.

Question 27

What is a functional dependency?

Accepted Answer

A functional dependency written X determines Y means that for any two rows with the same value of X, the value of Y must also be the same, X uniquely determines Y. It is a constraint about meaning, not about the data that happens to be present. A full functional dependency means Y depends on all of X and not on any proper subset. A partial dependency means Y depends on only part of a composite key. A transitive dependency is when X determines Y and Y determines Z, so X determines Z indirectly. Analogy: a postcode determines a city. Two envelopes with the same postcode must be going to the same city, that is not a coincidence in your current mail bag, it is how postcodes work. In an interview: The word to stress is 'must'. A dependency is a rule from the business domain. You cannot discover it by looking at the current rows, you have to ask what is actually true about the entities.

Question 28

What are Armstrong's axioms and attribute closure?

Accepted Answer

Armstrong's axioms are three inference rules that let you derive every dependency implied by a given set. Reflexivity: if Y is a subset of X then X determines Y. Augmentation: if X determines Y then XZ determines YZ. Transitivity: if X determines Y and Y determines Z then X determines Z. From these you get union, decomposition, and pseudotransitivity. Attribute closure, written X plus, is the set of all attributes determined by X, and if the closure of X is every attribute in the relation, then X is a super key. Analogy: a set of logical rules for following a chain of implications. Start with what you know, keep applying the rules, and see how much you can reach. In an interview: Closure is the practical tool. To test whether a set of attributes is a key, compute its closure; to test whether a decomposition is lossless, check that the shared attributes form a key of at least one fragment.

Question 29

What is first normal form?

Accepted Answer

A relation is in first normal form if every attribute is atomic, no repeating groups, no multi-valued attributes, no nested tables inside a cell. Each cell holds a single indivisible value, and each column holds values of the same type. In practice you achieve it by moving multi-valued data into its own table with a foreign key back, rather than stuffing a comma-separated list into one column. Analogy: a form where the 'phone number' box has three numbers crammed in. You cannot search, sort, or validate that box. 1NF says give each number its own row. In an interview: The repeating-columns version is the sneakier violation. Columns named phone1, phone2, phone3 look tidy but they impose an arbitrary limit and make querying across them painful.

Question 30

What is second normal form?

Accepted Answer

A relation is in second normal form if it is in 1NF and every non-key attribute is fully functionally dependent on the whole primary key, that is, there are no partial dependencies. This only ever matters when the primary key is composite, because a single-column key cannot have a proper subset. The fix is to move the partially dependent attributes into a table keyed by the part they actually depend on. Analogy: a class register keyed by pupil and subject, where you have also written the pupil's date of birth on every line. The date of birth depends only on the pupil, not on the subject, so it does not belong there. In an interview: The quick check is: if the primary key is a single column and the table is in 1NF, it is automatically in 2NF. Partial dependency is impossible without a composite key.

Question 31

What is third normal form?

Accepted Answer

A relation is in third normal form if it is in 2NF and has no transitive dependencies, no non-key attribute determines another non-key attribute. Codd's formulation is that every non-key attribute must depend on the key, the whole key, and nothing but the key. The formal definition allows an exception: for every dependency X determines A, either X is a super key or A is part of some candidate key, which is a prime attribute. Analogy: an employee record that also lists the department's location. The location depends on the department, and the department depends on the employee. Store the location with the department instead. In an interview: 3NF is the practical target for most operational systems. It removes essentially all the update anomalies while keeping the number of joins reasonable, and it is always achievable with a lossless, dependency-preserving decomposition.

Question 32

What is Boyce-Codd normal form and how does it differ from 3NF?

Accepted Answer

BCNF is a stricter version of 3NF. A relation is in BCNF if for every non-trivial functional dependency X determines Y, X is a super key, with no exception for prime attributes. So the difference only shows up when a non-key attribute determines part of a candidate key, which requires the table to have overlapping composite candidate keys. Every BCNF relation is in 3NF, but not every 3NF relation is in BCNF. The catch is that a BCNF decomposition is always lossless but is not always dependency-preserving. Analogy: 3NF says the boss can be anyone senior enough. BCNF says the boss must be the actual key holder, no exceptions, even for people who are part of a key themselves. In an interview: That trade-off is the real interview answer. 3NF guarantees both lossless join and dependency preservation; BCNF guarantees lossless join only. Sometimes staying at 3NF is the correct engineering decision.

Question 33

What are 4NF and 5NF?

Accepted Answer

Fourth normal form deals with multi-valued dependencies. A relation is in 4NF if it is in BCNF and has no non-trivial multi-valued dependency unless the determinant is a super key. The symptom is two independent multi-valued facts crammed into one table, which forces you to store every combination of them. Fifth normal form, or project-join normal form, deals with join dependencies, cases where a table can be losslessly decomposed into three or more tables but not into two. Analogy: listing every combination of a person's languages and their hobbies in one table. Three languages and four hobbies produces twelve meaningless rows, when the two facts have nothing to do with each other. In an interview: In practice 4NF matters occasionally and 5NF almost never comes up in real design. Being able to describe the multi-valued dependency symptom is enough, you spot it as a table whose row count is a product rather than a sum.

Question 34

What are lossless join and dependency preservation?

Accepted Answer

When you decompose a table you must not lose information. A decomposition is lossless if joining the fragments back together gives exactly the original rows, with no spurious extras, which is guaranteed when the shared attributes form a super key of at least one fragment. A decomposition is dependency-preserving if every original functional dependency can still be enforced by checking a single fragment, without joining. Losslessness is mandatory; dependency preservation is highly desirable but not always achievable at BCNF. Analogy: tearing a document in two. Lossless means taping it back gives you the original page. Lossy means the join invents sentences that were never on the page. In an interview: The one-sentence test is that the common attributes of the two fragments must be a super key of at least one of them. If they are not, the join will manufacture rows that were never in the original.

Question 35

What are prime and non-prime attributes?

Accepted Answer

A prime attribute is any attribute that is part of at least one candidate key. A non-prime attribute is one that belongs to no candidate key at all. The distinction matters because the definitions of 2NF and 3NF are stated in terms of non-prime attributes, 2NF forbids a non-prime attribute depending partially on a key, and 3NF forbids a non-prime attribute depending transitively on a key. BCNF drops the exemption for prime attributes entirely, which is exactly what makes it stricter. Analogy: prime attributes are the people whose names appear on the deed to the house. Non-prime attributes are everyone else living there, the rules about who may make decisions apply differently to each group. In an interview: If you can state where the prime-attribute exemption sits, the whole 3NF-versus-BCNF question answers itself. BCNF is simply 3NF with that one clause deleted.

Question 36

How do you find the candidate keys and the highest normal form of a relation?

Accepted Answer

First classify each attribute: ones appearing only on the left of every dependency must be in every key, ones appearing only on the right can never be in a key, and ones on both sides may or may not be. Start with the must-be set, compute its closure, and if it covers every attribute it is the only candidate key. If not, add the optional attributes one at a time until the closure is complete, keeping only minimal combinations. Then check each dependency against 2NF, 3NF, and BCNF in turn, the highest form all of them satisfy is the answer. Analogy: working out who must be on a committee for it to be quorate. Some people are essential, some are never eligible, and the rest you try in combinations until the smallest workable group is found. In an interview: The left-only shortcut saves most of the work. Any attribute that never appears on the right of a dependency has to be in every candidate key, so you almost always start from a small seed rather than trying every subset.

Question 37

Walk me through normalising a table from unnormalised form to 3NF.

Accepted Answer

Start with the unnormalised table and fix one violation at a time. First remove repeating groups and non-atomic cells to reach 1NF. Then identify the primary key and move out anything that depends on only part of it to reach 2NF. Then find attributes that depend on another non-key attribute rather than on the key, and move those into their own table to reach 3NF. At each step you are splitting, never deleting, the original data must be reconstructible by joining. Analogy: tidying an overloaded spreadsheet. First unstack the cells that hold lists, then pull out the facts that belong to a different subject, and keep going until each sheet is about exactly one thing. In an interview: One real-world caveat on that example: unit_price on the product is the current price, so an order line should usually store the price charged at the time as well. Normalisation removes redundancy, but a historical fact is not redundancy, it is a different fact that happens to look the same.

Question 38

What is denormalisation and when would you do it?

Accepted Answer

Denormalisation is deliberately introducing redundancy into a normalised design to improve read performance, duplicating a column to avoid a join, storing a precomputed total, or keeping a summary table. You do it when a query pattern is read-heavy and the joins are measurably expensive, typically in reporting systems and data warehouses. The cost is that you have reintroduced the update anomalies normalisation removed, so you now need triggers, scheduled jobs, or application discipline to keep the copies in step. Analogy: keeping a copy of the recipe taped inside the cupboard door as well as in the book. Faster to read while cooking, but now there are two copies to update when the recipe changes. In an interview: The order matters: normalise first, measure, then denormalise the specific hotspot. Denormalising up front is premature optimisation, and it is much harder to add correctness back than to add speed.

Question 39

Is a highly normalised design always better?

Accepted Answer

No. Normalisation optimises for write correctness and storage, but it costs joins on reads. A design in fifth normal form may require six joins to answer a simple question, which hurts both latency and query readability. The right level depends on workload: transactional systems that write constantly benefit from 3NF or BCNF, while analytical systems that mostly read benefit from denormalised star schemas. There is also a middle ground where a normalised source of truth feeds a denormalised read model. Analogy: storing every ingredient in a separate labelled jar is excellent for a kitchen that restocks constantly, but exhausting if you just want to make one sandwich quickly every day. In an interview: The framing I would use is that normalisation is not a goal, it is a tool for managing update anomalies. If a table is effectively append-only, the anomalies it prevents cannot happen, so the cost buys nothing.

Question 40

What does ACID stand for?

Accepted Answer

ACID is four promises a database makes about a transaction. A, Atomicity: all the steps happen, or none of them do. C, Consistency: the database only moves between states where every rule still holds. I, Isolation: transactions running at the same time do not see each other's half-finished work. D, Durability: once a transaction is committed, its changes survive a crash or power loss. After this the next questions take each letter one at a time with an example. A useful split to remember: atomicity and durability are handled by the recovery system using logs, isolation is handled by the concurrency-control system using locks or MVCC, and consistency is the result you get when the other three hold and your constraints are correct. Analogy: a delivery service promising four things: the whole order or nothing (A), never anything that breaks the rules (C), your order kept separate from everyone else's (I), and once it says delivered it stays delivered (D). In an interview: I list the four in one line each, give the transfer example, then say who enforces what: the recovery manager does A and D, the concurrency manager does I, and C falls out of the rest plus good constraints.

Question 41

Atomicity: what does it mean, with an example?

Accepted Answer

Atomicity means the whole transaction is treated as one single step that cannot be broken in half. Either every change inside it happens, or none of them happen. The database never keeps a half-done result. How the database does this: before it changes a row, it first writes the old value into a private undo log. So at any moment it knows how to put things back the way they were. If the transaction fails partway (a crash, a broken rule, a manual ROLLBACK), the database walks that undo log backwards and reverses every change the transaction made. Example. You move 100 rupees from account A to account B. That is two writes: take 100 off A, then add 100 to B. Suppose the server loses power right after the first write. Without atomicity, A is down 100 and B never got it, so 100 rupees just vanished. With atomicity, when the database restarts it sees this transaction never reached COMMIT, so it uses the undo log to give A its 100 back. The transfer looks like it never started. Analogy: sending a parcel through a machine. Either the full parcel comes out the far end, or it jams and a worker pulls the whole thing back. You never receive half a parcel. In an interview: I say atomicity is all-or-nothing, then use the transfer and point at the crash between the two updates. The mechanism to name is the undo log, which lets the database roll a partial transaction back on restart.

Question 42

Consistency: what does it mean, with an example?

Accepted Answer

Consistency means every transaction takes the database from one valid state to another valid state, and never leaves it in a broken state in between (as seen from outside the transaction). Valid means all the rules still hold: primary keys stay unique, foreign keys still point at rows that exist, CHECK constraints still pass, and any rule your schema describes is still true. Who enforces it: partly the database, through constraints and triggers, and partly your application code. The database can only protect the rules you actually told it about. If the rule 'a balance can never go negative' is not written as a constraint and not checked in code, nothing stops it. Example. Before the transfer, A has 500 and B has 300, so the total is 800. A correct transfer leaves A with 400 and B with 400, total still 800. Now say there is a constraint CHECK (balance >= 0). If a transaction tries to take 1000 off A, the new balance would be -500, the constraint fails, and the database throws the entire transaction away. The saved data still obeys every rule. Analogy: a bank clerk who checks your form before filing it. If the numbers do not add up, they hand it back and file nothing. The cabinet only ever holds correct forms. In an interview: Consistency is 'the database only ever moves between states where every rule holds'. I always add that it is a shared job: constraints and triggers cover what you declared, and the application has to cover the rest.

Question 43

Isolation: what does it mean, with an example?

Accepted Answer

Isolation means that when many transactions run at the same time, each one acts as if it were the only one running. Nobody sees your half-finished work, and nobody corrupts yours. The ideal is that the final result is the same as if the transactions had run one after another, in some order. When isolation is too weak you get bugs. The classic one is a dirty read: transaction 2 reads a value that transaction 1 wrote but has not committed, then transaction 1 rolls back, so transaction 2 acted on data that never truly existed. Example. Transaction 1 is the transfer, in progress. It has taken 100 off A but has not added it to B yet, and has not committed. Right then, transaction 2 runs a report that adds up every account balance. If transaction 2 is allowed to see A's uncommitted lower balance, its total is 100 short, because that 100 is currently nowhere. Isolation stops transaction 2 from seeing that in-between moment: it either waits, or it reads the older snapshot from before transaction 1 started. Databases achieve this with locks or with MVCC, and you choose how strict it is using isolation levels (READ COMMITTED, REPEATABLE READ, SERIALIZABLE, and so on). Analogy: two people editing a shared document. Isolation is like each person editing their own copy and only merging on save, so nobody watches your typos appear letter by letter. In an interview: Isolation means concurrent transactions do not disturb each other, ideally giving the same result as running them one at a time. I give the dirty-read example with the report during a transfer, and I name locks, MVCC and isolation levels as the controls.

Question 44

Durability: what does it mean, with an example?

Accepted Answer

Durability means that once the database has told you a transaction committed, that data is permanent. If the power dies one millisecond later, or the process is killed, the committed changes are still there when the system comes back. How it works: writing the real data files on disk is slow, so the database does it later in the background. But before COMMIT returns, the database writes a short record of the change to a separate append-only file called the write-ahead log, and forces that file all the way onto physical disk (an operation called fsync). That log record is enough to redo the change. On restart, the database replays the log and reapplies any committed change that had not yet reached the data files. Example. The transfer commits and COMMIT returns to your program. Two seconds later the machine loses power. When it boots, the database reads its write-ahead log, sees the committed transfer, and makes sure both the minus 100 on A and the plus 100 on B are written into the data files. Nothing committed is lost. Analogy: writing in a bound ledger in permanent ink and taking a photo of the page. Once it is in the ledger and photographed, a power cut in the office cannot erase it. A whiteboard note would be gone. In an interview: Durability is 'committed means committed, even through a crash'. The mechanism is the write-ahead log plus an fsync before COMMIT returns, and redo recovery replaying that log on restart.

Question 45

How do all four ACID properties work together in one transaction?

Accepted Answer

Run a single bank transfer through all four and watch each one do its job. You wrap the two updates in BEGIN and COMMIT. Atomicity makes those two writes one unit, so a crash in the middle rolls both back. Consistency checks the rules at commit time (no negative balance, no dangling foreign key) and rejects the transaction if any rule breaks. Isolation makes sure that while your transfer runs, no other transaction sees A already debited but B not yet credited. Durability makes sure that once COMMIT returns, the transfer cannot be lost. A simple way to remember who does what inside the database engine: atomicity and durability are the recovery system's job, using logs (an undo log for rollback, a redo log for crash recovery). Isolation is the concurrency-control system's job, using locks or MVCC. Consistency is what you get for free when the other three hold and your constraints and application checks are correct. Analogy: four seatbelts on the same trip. Atomicity stops you being thrown out halfway. Isolation keeps you out of the next seat. Durability keeps you belted after a bump. Consistency is the car staying road-legal. You want all four working at once. In an interview: I run one transfer through all four in order, then give the split: the recovery manager handles A and D with logging, the concurrency manager handles I with locks or MVCC, and C falls out of the other three plus correct rules.

Question 46

What is a transaction?

Accepted Answer

A transaction is a single logical unit of work made up of one or more operations, which the database treats as indivisible, either all of it happens or none of it does. The textbook example is a bank transfer: debiting one account and crediting another are two statements but one business operation, and finishing halfway would destroy money. A transaction begins explicitly or implicitly, and ends with either COMMIT, making its changes permanent, or ROLLBACK, undoing them. Analogy: a wedding ceremony. Both people say yes or the marriage does not happen. There is no state where one person is married and the other is not. In an interview: The key idea is that transaction boundaries are a business decision, not a technical one. The database cannot know that those two updates belong together, the developer has to say so.

Question 47

What are the states of a transaction?

Accepted Answer

A transaction begins in the Active state while its operations execute. When the last operation completes it enters Partially Committed, where changes exist in memory but are not yet guaranteed on disk. If the commit succeeds it moves to Committed and its effects are permanent. If anything fails during Active or Partially Committed it enters Failed, then goes through rollback to Aborted, from which it is either restarted or killed. Analogy: submitting a form online. Filling it in is active. Pressing submit and watching the spinner is partially committed. The confirmation screen is committed. An error message means failed, and the system throws the draft away. In an interview: The interesting state is partially committed, because that is where durability is decided. The transaction is logically finished but a crash at that exact moment must still leave the database consistent, which is what the write-ahead log exists to guarantee.

Question 48

What do COMMIT, ROLLBACK and SAVEPOINT do?

Accepted Answer

COMMIT ends a transaction successfully, making all its changes permanent and visible to other transactions, and releases its locks. ROLLBACK ends it unsuccessfully, undoing every change it made since it began. SAVEPOINT marks an intermediate point inside a transaction so you can roll back partially, back to that marker rather than all the way to the start, which is useful in long procedures where one optional step failing should not discard everything. Analogy: a video game. Commit is saving and quitting. Rollback is quitting without saving. A savepoint is a checkpoint mid-level that you can restart from without replaying the whole level. In an interview: Worth mentioning autocommit: most clients run in autocommit mode where every statement is its own transaction, which is why people are sometimes surprised that they cannot roll back a statement they just ran.

Question 49

How does the DBMS actually implement atomicity and durability?

Accepted Answer

Through a write-ahead log. Before any modified data page is written to disk, a log record describing the change, the transaction id, the page, the old value, and the new value, must be written to durable storage first. That gives both properties. Atomicity comes from the old values, the undo information, which let a crashed transaction be rolled back. Durability comes from the new values, the redo information, which let a committed transaction be replayed even if its data pages never reached disk before the crash. Analogy: a builder who writes down each change in a site diary before making it. If work stops suddenly, the diary tells you both how to undo the half-finished work and how to redo the work that was agreed but not yet built. In an interview: The reason commit feels expensive is exactly this. Commit has to wait for a physical disk flush of the log, which is why group commit and battery-backed write caches exist, they amortise that flush across many transactions.

Question 50

What is a distributed transaction and the two-phase commit protocol?

Accepted Answer

A distributed transaction spans more than one database or resource manager, and all of them must commit or all must abort. Two-phase commit coordinates this. In the prepare phase a coordinator asks every participant whether it can commit; each writes its changes durably and replies yes or no, effectively promising it can honour either outcome. In the commit phase, if every participant said yes the coordinator tells them all to commit; if any said no it tells them all to abort. The weakness is that if the coordinator fails after the prepare phase, participants are blocked holding locks until it recovers. Analogy: a group booking where everyone must travel or nobody does. First the organiser asks each person to hold their seat and confirm they can go. Only when all confirmations are in does the organiser say 'buy the tickets'. In an interview: In modern distributed systems many teams deliberately avoid 2PC because of that blocking window, and use the saga pattern instead, a sequence of local transactions each with a compensating action, trading strict atomicity for availability.

Question 51

Why do we need concurrency control?

Accepted Answer

Running transactions one after another would be correct but unbearably slow, because a transaction waiting on disk would block everyone. So databases interleave transactions to improve throughput and reduce response time. But uncontrolled interleaving lets transactions read each other's half-finished work and overwrite each other's changes, producing results that no serial order could ever produce. Concurrency control is the machinery that keeps interleaving fast while guaranteeing the result is equivalent to some serial execution. Analogy: several chefs sharing one kitchen. Working simultaneously is far faster than taking turns, but without rules about who holds which pan you get two people salting the same soup and one of them adding it twice. In an interview: The formal target is serialisability, the interleaved schedule must produce the same final state as some serial order. Not any particular order, just some valid one.

Question 52

What are the concurrency problems that can occur?

Accepted Answer

There are four classic anomalies. A dirty read is reading data written by a transaction that has not committed and may still roll back. A lost update is when two transactions read the same value, both modify it, and the second write silently overwrites the first. A non-repeatable read is when a transaction reads the same row twice and gets different values because another transaction committed an update in between. A phantom read is when a transaction runs the same range query twice and finds new rows that another transaction inserted. Analogy: dirty read is quoting from a draft that gets retracted. Lost update is two people editing the same document and the second save wiping out the first. Non-repeatable read is a price changing while you are still checking out. Phantom read is new items appearing in the aisle while you are counting stock. In an interview: The distinction to be precise about is that a non-repeatable read concerns existing rows changing, while a phantom read concerns the set of rows changing. That difference is exactly why REPEATABLE READ prevents one and not the other.

Question 53

What is a schedule and what is serialisability?

Accepted Answer

A schedule is the chronological order in which the operations of several concurrent transactions are executed. A serial schedule runs each transaction to completion before starting the next, always correct, but no concurrency. A schedule is serialisable if, although interleaved, it produces the same result as some serial schedule. Serialisability is the correctness criterion for concurrency control, and conflict serialisability is the practical test used because it can be checked mechanically. Analogy: shuffling two decks of cards together. The shuffle is acceptable if the final arrangement could have been produced by simply stacking one whole deck on the other in some order. In an interview: The mechanical test is the precedence graph. Draw a node per transaction and an edge for every conflict in execution order. If the graph has no cycle, the schedule is conflict serialisable and therefore correct.

Question 54

What is the difference between conflict and view serialisability?

Accepted Answer

Conflict serialisability means the schedule can be transformed into a serial one by swapping only non-conflicting adjacent operations. It is checked with a precedence graph and is what real systems enforce, because the test is efficient. View serialisability is a broader notion, it only requires that each transaction reads the same values and that the final writes come from the same transactions as in some serial schedule. Every conflict-serialisable schedule is view serialisable, but not the reverse. Testing view serialisability is NP-complete, so it is theoretical rather than practical. Analogy: conflict serialisability is a strict but quick checklist. View serialisability is the true definition of fairness, but verifying it takes so long that nobody uses it operationally. In an interview: The practical takeaway is that real DBMS implementations enforce conflict serialisability, so they occasionally reject or delay a schedule that would actually have been fine. That is a deliberate trade of a little concurrency for a cheap correctness test.

Question 55

What are shared and exclusive locks?

Accepted Answer

A shared lock, also called a read lock, is taken before reading an item. Several transactions can hold a shared lock on the same item at once, because concurrent reads do not interfere. An exclusive lock, or write lock, is taken before writing, and only one transaction can hold it, with no shared locks allowed at the same time. The compatibility matrix is simple: shared with shared is compatible, everything else conflicts. Analogy: a document in a shared office. Any number of people can read it over each other's shoulders. But only one person can have it on their desk with a pen, and while they do, nobody else can even read it. In an interview: Lock granularity matters too, a lock can be held on a row, a page, or a whole table. Finer granularity gives more concurrency but more locking overhead, so engines use intention locks in a hierarchy to make coarse-grained decisions cheaply.

Question 56

What is two-phase locking?

Accepted Answer

Two-phase locking is a protocol that guarantees conflict serialisability. Every transaction has a growing phase in which it may acquire locks but not release any, and a shrinking phase in which it may release locks but not acquire any. The moment of the last acquisition is the lock point. Basic 2PL guarantees serialisability but can suffer cascading rollbacks. Strict 2PL holds all exclusive locks until commit, which prevents cascading aborts. Rigorous 2PL holds all locks until commit and is what most commercial systems actually implement. Analogy: collecting all the tools you need before starting work, and only putting tools back once you have stopped picking any up. You never go back for another tool after you have started returning them. In an interview: The trade-off is that 2PL guarantees serialisability but does not prevent deadlock, except in the conservative variant which requires knowing every lock in advance. So real systems combine strict 2PL with a deadlock detector.

Question 57

What is a deadlock in a database and how is it handled?

Accepted Answer

A deadlock is when two or more transactions each hold a lock the other needs, so none can proceed. There are three responses. Prevention uses timestamp schemes such as wait-die, where an older transaction waits and a younger one aborts, or wound-wait, where an older transaction forces a younger one to abort. Avoidance requires knowing all locks in advance. Detection is what most systems do: build a wait-for graph, look for a cycle, and abort a victim transaction to break it. Simpler systems just use a lock timeout. Analogy: two people each holding one half of a pair of scissors, each refusing to let go until they get the other half. Someone has to be told to drop theirs. In an interview: The most effective prevention in application code is consistent lock ordering, if every transaction touches tables alphabetically, or always updates the parent before the child, a cycle becomes impossible.

Question 58

What are the SQL isolation levels?

Accepted Answer

The standard defines four. READ UNCOMMITTED allows dirty reads and gives the highest concurrency with the weakest guarantees. READ COMMITTED prevents dirty reads by only reading committed data, and is the default in PostgreSQL, Oracle, and SQL Server. REPEATABLE READ additionally guarantees that rows you have read will not change if you read them again, and is MySQL InnoDB's default. SERIALIZABLE prevents everything including phantoms, behaving as if transactions ran one at a time. Higher levels mean fewer anomalies but less concurrency. Analogy: a dial trading speed against certainty. Turn it up and your view of the data gets more trustworthy but you spend more time waiting for other people to finish. In an interview: In practice READ COMMITTED is the right default for most applications, and where a specific operation needs stronger guarantees the usual answer is an explicit SELECT FOR UPDATE on the rows involved rather than raising the isolation level for everything.

Question 59

How does timestamp-based concurrency control work?

Accepted Answer

Instead of locks, each transaction is given a unique timestamp when it starts, and the system enforces that the schedule is equivalent to running transactions in timestamp order. Every data item keeps a read timestamp and a write timestamp of the last transaction that touched it. If a transaction tries an operation that would violate timestamp order, reading something already written by a younger transaction, or writing something already read by a younger one, it is aborted and restarted with a new timestamp. It is deadlock-free but can cause repeated restarts. Analogy: a queue ticket system where everyone is served strictly in ticket order. If you arrive at the counter out of order you are sent back to take a new ticket rather than being made to wait in a line. In an interview: The Thomas write rule is the elegant part, an obsolete write can simply be discarded rather than causing an abort, because a later write has already overwritten it anyway.

Question 60

What is MVCC, and what is the difference between optimistic and pessimistic concurrency?

Accepted Answer

Multiversion concurrency control keeps several versions of each row rather than overwriting in place. A reader is given the version that was current when its transaction or statement started, so readers never block writers and writers never block readers, which is why PostgreSQL and Oracle handle read-heavy workloads so well. Pessimistic control assumes conflict is likely and locks up front. Optimistic control assumes conflict is rare, lets transactions run freely, and validates at commit time, aborting if a conflict actually occurred. Analogy: MVCC is a document with full version history, you keep reading the version you opened while someone else writes a new one. Pessimistic is locking the file before you start; optimistic is editing freely and being told at save time if someone beat you to it. In an interview: The version-column pattern in that last statement is worth knowing, because it gives optimistic concurrency across HTTP requests where you cannot hold a database lock between the read and the write.

Question 61

What is starvation, and what is lock granularity?

Accepted Answer

Starvation is when a transaction waits indefinitely because the scheduler keeps favouring others, for example a transaction wanting an exclusive lock while a continuous stream of readers keeps renewing shared locks. It is prevented with fair queuing or by ageing waiting transactions up in priority. Lock granularity is the size of the item locked: a row, a page, a table, or the whole database. Fine granularity allows more concurrency but costs more memory and CPU to manage, so engines use intention locks to record interest in a coarse item before locking finer items inside it. Analogy: starvation is queueing at a counter where staff keep serving people who walk in after you. Granularity is whether you reserve one seat, one row, or the whole cinema. In an interview: Lock escalation is the practical gotcha. A statement updating a few thousand rows can trip the escalation threshold and suddenly lock the whole table, which is a classic cause of a bulk job blocking an entire application.

Question 62

What types of failure must a database recover from?

Accepted Answer

Transaction failure happens when a single transaction cannot proceed, a constraint violation, a deadlock victim, or an explicit rollback, and is handled by undoing that transaction. System failure is a crash or power loss that wipes main memory but leaves disk intact, handled by replaying the log. Media failure is disk damage, which the log alone cannot fix and requires restoring from a backup and rolling forward. And there is human or malicious failure, where a valid but wrong statement destroys data, which needs point-in-time recovery. Analogy: a dropped plate, a power cut, a kitchen fire, and a chef who threw out the wrong ingredients. Each needs a different response and only some are fixable from what is still in the room. In an interview: The distinction worth drawing is that the log protects you from system failure, but only backups protect you from media failure, and only point-in-time recovery protects you from someone running the wrong DELETE.

Question 63

What is the write-ahead log and why must it be written first?

Accepted Answer

The write-ahead log, or WAL, is a sequential record of every change made to the database. The WAL rule has two parts: the log record for a change must reach stable storage before the corresponding data page is written, and all log records for a transaction must reach stable storage before it is allowed to commit. The first part guarantees you can always undo a change that made it to disk; the second guarantees you can always redo a change that was committed but had not yet made it to disk. Analogy: writing your intentions in an indelible notebook before acting on them. If you are interrupted, whoever finds the notebook can tell exactly what was finished, what was in progress, and how to put it right. In an interview: The performance insight is that the log turns many random page writes into one sequential write. Data pages can then be flushed lazily in the background, which is why WAL makes databases faster as well as safer.

Question 64

What is the difference between deferred and immediate update?

Accepted Answer

With deferred update, sometimes called no-undo redo, changes are held and only written to the database after the transaction commits. Recovery therefore only ever needs redo, never undo, because uncommitted changes never touched the database. With immediate update, changes are written to the database as they happen, before commit, so recovery needs both redo for committed transactions and undo for uncommitted ones. Immediate update performs better and is what real systems use, at the cost of more complex recovery. Analogy: deferred is writing your essay on scrap paper and copying it into the exam book only when you are sure. Immediate is writing straight into the book with an eraser handy. In an interview: Cascading rollback is the connection back to concurrency control. Strict two-phase locking exists partly so that recovery never has to cascade, because no transaction can read another's uncommitted data in the first place.

DBMS interview preparation

Databases & DBMS basics

ER modelling & design

The relational model & constraints

Normalisation & functional dependency

ACID

Transactions

Concurrency control

Recovery & backup

Indexing, storage & optimisation

Scaling & availability

Database types, NoSQL & scaling

Security & practical database work