Interview Question of the Week #037 – What are the Properties of Relational Tables?

The other day an interview question sent me straight back to college. My professors liked asking about the properties of relational tables. The list is still useful, especially when a candidate can say which parts SQL Server enforces for them and which parts require a deliberate schema choice.

Question: What are the properties of a relational table?

Answer: The six properties from my original answer are:

1. Values are atomic for the chosen domain. A column holds one value of the kind being modeled, rather than a repeating group that should be related rows. “Atomic” does not mean every value lacks internal parts: a date can still be one date.
2. Values in one column come from one domain. SQL Server’s data type is part of this rule. CHECK, NOT NULL, and other constraints may be needed to express the narrower business domain.
3. Each row is unique in a relation. In SQL Server, a table can store duplicate rows when no key prevents them. Declare the intended primary key or unique constraint; the theory alone will not enforce it.
4. Column sequence has no relational meaning. Refer to columns by name. A display or export can choose an order, but that presentation choice is separate from the logical relation.
5. Row sequence has no relational meaning. Use ORDER BY when a result must arrive in a particular order. An index does not promise an unstated display order.
6. Column names are unique within their table. Names identify attributes within that table; the same column name may occur in another table.

The interview follow-up matters more than reciting the list: can a SQL Server table without a key hold two identical rows? Yes. A query result can also preserve duplicates until you ask for DISTINCT or a grouping operation. The relational model gives us a clean target; the actual table definition and query determine what the engine guarantees.

Published by Pinal Dave on SQLAuthority. More of my work at pinaldave.com.