SQL Server Interview Topic 16: Data Modeling and Normalization
This topic checks whether you can design tables before writing queries. Good SQL starts with good table design.
You should be able to explain why data is split into multiple tables and how those tables connect.
Q115. What is data modeling?
Quick interview answer:
Data modeling means planning tables, columns, keys, and relationships before building the database. It helps the database match the real business. In a school system, students, teachers, classes, exams, and marks should be modeled as separate but connected parts.
Study in detail: Best Practices and Design - This article explains table design decisions.
Q116. What is normalization?
Quick interview answer:
Normalization means organizing data to reduce repetition and avoid update mistakes. For example, instead of storing teacher name repeatedly in every student row, we keep teacher details in a separate table and connect it with a key.
Study in detail: Best Practices and Design - This lesson explains clean table design.
Q117. What is denormalization?
Quick interview answer:
Denormalization means storing some repeated or summary data intentionally to improve read performance or simplify reports. It should be done carefully because repeated data can become inconsistent.
Study in detail: Advanced Patterns - This article explains practical design trade-offs.
Q118. What is a lookup table?
Quick interview answer:
A lookup table stores fixed or repeated reference values. For example, class sections, exam types, or fee categories can be stored in lookup tables. This keeps data consistent and avoids spelling differences.
Study in detail: Create Database and Tables - This lesson explains tables using school examples.
Q119. How do you decide one-to-many relationships?
Quick interview answer:
A one-to-many relationship exists when one row in a table can connect to many rows in another table. For example, one student can have many exam marks. The many-side table stores the foreign key.
Study in detail: Foreign Keys - This article explains table relationships clearly.
In design questions, use real nouns first: Student, Exam, Marks. Then explain keys and relationships.