Exam 70-768: Developing SQL Data Models Practice Test
Exam 70-768: Developing SQL Data Models concerns relational database design and SQL Server data-model implementation. The available practice material uses concepts such as keys and constraints, normalization, identity columns, data types, indexes, joins, and views. These topics connect logical data requirements to structures that support accurate storage, enforce relationships, and make information easier to query.
Exam overview
About the Exam 70-768: Developing SQL Data Models Exam
Modeling decisions affect data integrity, update behavior, and query access. Practice with primary and composite keys, unique constraints, and foreign keys to distinguish row identification from relationship enforcement. Normalization scenarios call for recognizing redundancy and update anomalies, then separating entities so shared attributes are maintained consistently. SQL Server implementation concepts include choosing suitable column types, using identity properties for generated values, and selecting clustered or nonclustered indexes based on access patterns and existing table structure. Query-related scenarios may require joining tables with equality or range predicates and using views to present selected data without duplicating it. These skills support translating requirements into maintainable relational structures and evaluating the consequences of design choices for inserts, updates, and lookups.
Sample Practice Questions
Review a selection from this exam's published US Practice Tests practice bank.
A table's primary key consists of OrderID and LineNumber. Which statement best describes this key?
Show answer
Correct answer: BA composite primary key uses multiple columns together to identify a row uniquely. The combination must be unique, and primary-key columns cannot be NULL.
An application must represent a value that can have one of several named states, such as Draft, Approved, or Rejected. The allowed states may be added over time and need descriptive attributes. Which modeling approach is most flexible?
Show answer
Correct answer: AA lookup table provides a controlled set of state values, can store descriptions or other attributes, and can be extended by adding rows. A foreign key can ensure that each stored state refers to a defined value.
A SQL Server table needs a generated identifier that increases automatically when a new row is inserted. Which column property is designed for this purpose?
Show answer
Correct answer: AThe IDENTITY property generates numeric values for inserted rows according to a seed and increment. CHECK validates a condition, SPARSE can optimize storage of NULL values, and ROWGUIDCOL identifies a column designated for row GUID values but does not itself generate them.
A database stores an employee's department name in every employee row. Department names are sometimes updated, and the change must be made in many rows. Which normalization improvement most directly addresses this update anomaly?
Show answer
Correct answer: ASeparating department facts into a Departments table avoids repeatedly storing and updating the same department name. Employees can reference the department through a foreign key.
A SQL Server model needs to ensure that every value in a column matches a row in a separate lookup table. What is the most direct way to enforce this rule?
Show answer
Correct answer: AA foreign key enforces referential integrity by requiring each non-NULL referencing value to match a key in the referenced table.
A SQL Server query must combine rows from two tables based on a relationship that is not equality, such as a date falling between a start date and an end date. Which join condition supports this?
Show answer
Correct answer: AThe ON clause can use comparison predicates such as BETWEEN or paired inequalities, so matching need not be based on equality. A foreign key is a schema constraint and is not required for the query join itself.
A SQL Server table stores one row per customer. Which constraint is the most direct way to ensure that each customer has a unique, non-null identifier?
Show answer
Correct answer: BA primary key uniquely identifies each row and does not allow NULL values. CHECK and DEFAULT constraints serve other purposes, while a foreign key enforces a relationship to another table.
How to use this practice test
The free US Practice Tests practice bank contains 50 questions; each session presents up to 20 randomly selected questions. Answer a session to see results, then use missed or uncertain items to identify concepts to revisit, such as key design, normalization, constraints, index selection, and joins.
Preparation tips
- For each key scenario, distinguish a single-column key from a composite key and check whether uniqueness and non-nullability are both required.
- Practice tracing update anomalies caused by repeated attributes, then sketch a normalized design that stores each fact in an appropriate table.
- Match SQL Server column requirements to data types and properties, including variable-length large character data and automatically generated identifiers.
- Compare clustered and nonclustered index choices against the table’s existing index and the lookup pattern described.
- Write join predicates for both equality relationships and range conditions, and distinguish a view from a separate copied data set.