DP-900 Practice Question: Identify considerations for relational data on Azure
A university's enrollment system stores data in a single table with columns: EnrollmentID, StudentID, StudentName, CourseID, CourseName, and Grade. Students can take multiple courses, and each course has multiple students. The team notices data redundancy: StudentName is repeated for each enrollment of the same student, and CourseName is repeated for each enrollment in the same course. They want to reduce redundancy while preserving the ability to query all enrollments with student and course details. What is the most appropriate design approach?
⚠ Common exam trap
Many exam-takers confuse views with physical schema changes, thinking a view can magically eliminate redundancy without altering table structure, or they mistakenly believe compression is a substitute for proper normalization.
Answer choices
Why each option matters
Answer the question above first, then reveal the full breakdown to understand why each option is right or wrong.
Correct answer & explanation
✓
Normalize the schema by creating separate Students, Courses, and Enrollments tables with foreign keys
Normalizing the schema into separate Students, Courses, and Enrollments tables eliminates data redundancy by storing each student's name and each course's name only once, while using foreign keys to maintain relationships. This preserves the ability to query all enrollments with student and course details via JOIN operations, which is the standard relational database design principle for reducing anomalies and storage overhead.
Answer analysis
Option-by-option breakdown
For each option: why learners choose it and why it is or isn't the right answer here.
- ✗
Keep the single table but use compression to reduce storage
Why it's wrong here
Row and page compression in SQL Server shrink the physical space a table occupies, but they do not alter the logical schema or remove duplicate values. The same student name and course name would still repeat in every enrollment row, so update anomalies remain: renaming a student requires updating all their enrollment tuples. Compression is a storage optimization, not a data-modeling fix, and it leaves the fundamental redundancy and potential inconsistency unresolved.
- ✗
Create a view that mirrors the single table but physically store data in separate normalized tables
Why it's wrong here
This option is technically a two-step solution: it separates data into normalized tables and then exposes a view that resembles the original single table for legacy read-compatibility. However, the view itself does not eliminate any redundancy — the normalization of the underlying physical tables is what actually does that. Because the option's emphasis is on the view rather than on the normalization step, it is an indirect and unnecessarily complex way to achieve the required reduction in redundancy, making normalization alone the better answer.
- ✓
Normalize the schema by creating separate Students, Courses, and Enrollments tables with foreign keys
Why this is correct
Normalization decomposes the unnormalized enrollment table into Student, Course, and Enrollment relations, moving StudentName into Students and CourseName into Courses so each value is stored only once. The Enrollments table then holds only foreign keys (StudentID, CourseID) plus enrollment-specific attributes, eliminating the partial dependencies on composite keys and the transitive dependency of CourseName on CourseID. This design enforces referential integrity via foreign key constraints, preventing orphaned records and reducing update anomalies to a single-row change.
- ✗
Denormalize by adding more columns to the single table
Why it's wrong here
Denormalization deliberately adds redundant columns to avoid joins, but that is the exact opposite of what this scenario needs. A single table already contains repeated student and course information in every enrollment row; introducing more columns, such as adding StudentMajor or InstructorName directly to the table, would duplicate those values for each enrollment the student or course appears in. The result is greater storage overhead, higher risk of update anomalies, and no improvement to data integrity — it directly contradicts the goal of reducing redundancy.
Go deeper
Related to this question
Learn chapter
Data Roles and Core Concepts
Key term
Table
A table is a structured collection of data organized into rows and columns, used in databases and spreadsheets to store and manage information efficiently.
Key term
Relational database
A relational database organizes data into tables with rows and columns, where each table relates to others using unique keys, allowing efficient storage, retrieval, and manipulation of structured information.
About these practice questions
This DP-900 question is part of Courseiva's 820-question bank — original exam-style content with full explanations and wrong-answer analysis, never real exam questions or exam dumps. Learn why practice questions differ from exam dumps →
JA
Written by Johnson Ajibi, MSc IT Security
Senior Network & Security Engineer · founder of Courseiva
This DP-900 practice question is part of Courseiva's free Microsoft certification practice question bank. Courseiva provides original exam-style practice questions with explanations, topic-based practice, mock exams, readiness tracking, and study analytics to help learners prepare for the DP-900 exam.