Microsoft 98-364 Database Fundamentals Free Practice Test — 30 Questions
This deck consolidates key concepts from the provided 30-question practice bank. It focuses on database design, indexing strategies, backup and recovery, referential integrity, SQL query optimization, and stored procedures. Exercises cover clustered vs. non-clustered indexes, full/differential/incremental backups, cascading deletes, normalization and denormalization, star schema, and performance tuning with indexes and caching. Review these topics to strengthen your understanding of core database fundamentals, ensuring you can apply them to scenarios involving data integrity, query execution, and security. Master the distinctions between WHERE and HAVING, joins and subqueries, and transactional locking protocols.
What this Microsoft 98-364 Database Fundamentals practice set measures
This is an analysis of the practice bank, not a claim about the vendor's live exam blueprint. Use it to identify the knowledge, judgment, and recall patterns exercised here, then verify your coverage against the current official exam guide.
Core Concepts of Database Design
The practice bank emphasizes the importance of understanding data models and constraints. Questions explore the relational, hierarchical, and network models, with a focus on the relational model for its flexibility in handling one-to-many and many-to-many relationships through foreign keys and junction tables. Normalization to 3NF reduces redundancy but can complicate queries; denormalization improves read performance at the cost of data integrity. Primary keys enforce uniqueness, CHECK constraints enforce domain rules (e.g., positive prices), and proper relationship design prevents anomalies. Understanding these foundations is critical for schema design.
- Relational model uses tables and foreign keys to represent entities and relationships.
- Normalization minimizes redundancy; denormalization trades redundancy for query speed.
- Primary keys and CHECK constraints enforce entity and domain integrity.
Data Integrity and Referential Actions
Several questions test referential integrity, cascading deletes, and backup strategies. Referential integrity ensures that foreign key values correspond to existing primary keys, preventing orphaned records. Cascading delete automatically removes child records when a parent is deleted. Backup strategies combine full, differential, and incremental backups to balance storage and recovery time: a full backup captures all data, differential captures changes since last full, and incremental captures changes since last backup. Understanding these options is vital for data availability and disaster recovery.
- Cascading delete automatically deletes related child records when a parent is deleted.
- Differential backups include all changes since the last full backup; incremental backups include changes since the last backup of any type.
- Restoring from incremental backups requires the most recent full, then each subsequent incremental in order.
SQL Query Optimization and Execution
Performance optimization is a recurring theme. Indexes speed up data retrieval: clustered indexes physically reorder data (one per table), non-clustered indexes maintain a separate structure. Composite indexes on multiple columns improve queries with filters and groupings. The star schema simplifies analytical queries by reducing joins. SQL clauses like WHERE (row filtering before aggregation) and HAVING (group filtering after aggregation) must be used correctly. Joins (INNER, LEFT, FULL OUTER) handle related data. Using indexes on join columns and WHERE clauses is the most effective tuning strategy.
- Clustered indexes determine physical order; non-clustered indexes provide fast lookups without reordering.
- Composite indexes support multi-column filters and groupings.
- WHERE filters rows before grouping; HAVING filters groups after aggregation.
Stored Procedures, Security, and Backup Strategies
Stored procedures encapsulate SQL logic, reduce network traffic by executing multiple statements in one call, and enhance security by granting procedure execution without direct table access. Multi-factor authentication (MFA) adds a second verification layer, significantly reducing unauthorized access risk even if credentials are compromised. Transaction processing uses two-phase locking to ensure concurrency control: locks block conflicting transactions until release. Understanding these tools helps maintain data integrity and system security.
- Stored procedures improve performance by reducing round trips and allowing execution permissions.
- MFA requires two or more verification factors (password + code from device).
- Two-phase locking prevents dirty reads and ensures serializable transactions.
Practice Microsoft 98-364 Database Fundamentals with real flashcards
Read the prompt, commit to an answer, then flip the card. Move through the deck at your own pace and repeat any topic that does not come back quickly.
Card 1 of 20
1 reviewed this session
Static practice bank
Start the 30-question diagnostic
The complete question bank is embedded in this pre-rendered page. There is no database request or second content download when you begin.
A database administrator is tasked with improving the performance of a large e-commerce database that experiences slow query response times during peak traffic hours. The administrator decides to analyze the execution plans of the most frequently run queries. After reviewing the plans, they notice that several queries are performing full table scans instead of using indexes. What is the most effective strategy the administrator should implement to enhance query performance in this scenario?
Study workflow
Turn one Microsoft 98-364 Database Fundamentals attempt into a study plan
- 1
Review Index Types and Usage
Study clustered vs. non-clustered indexes. Use the practice bank's questions to understand when an index speeds up a query (e.g., filtering by date) and the trade-offs (storage, write overhead). Practice creating indexes with SQL syntax.
- 2
Master SQL Aggregation and Grouping
Focus on queries using GROUP BY, HAVING, and aggregate functions (SUM, AVG, COUNT). Work through the practice questions that ask for total sales per product in a date range. Ensure you can differentiate WHERE from HAVING.
- 3
Practice Designing Relationships
Draw entity-relationship diagrams for scenarios like books-authors (many-to-many) and students-courses. Identify where junction tables and foreign keys are needed. The practice bank has several examples—recreate them.
- 4
Learn Backup and Recovery Strategies
Understand full, differential, and incremental backups. Use the practice question (Q3) to calculate required restore data. Know the order of restoration and when each backup type is used.
- 5
Implement Stored Procedures and Security Measures
Write stored procedure syntax for calculations (e.g., compound interest). Understand the benefits of MFA and two-phase locking. The practice bank includes a procedure for total sales and a question on MFA.
FAQ
Questions about this exam practice page
Clear boundaries on what the bank covers, how to use it, and where official vendor information still matters.
What is the difference between a clustered and a non-clustered index?+
A clustered index physically reorders the table rows based on the indexed column, so there can be only one per table. A non-clustered index creates a separate structure that points to the data rows, allowing multiple per table but potentially slower for range queries.
How does a cascading delete work?+
When referential integrity is enforced with CASCADE delete, deleting a parent record automatically deletes all child records that reference that parent. This prevents orphaned rows. It is defined in the foreign key constraint.
What is the purpose of a stored procedure?+
A stored procedure is a saved collection of SQL statements that can accept parameters, execute complex operations, and return results. It improves performance by reducing network traffic, enhances security by controlling data access, and promotes code reuse.
When should you denormalize a database?+
Denormalization is used to improve read performance for analytical queries by reducing the number of joins. It introduces redundancy and update anomalies, so it is best applied in read-heavy reporting systems, not in OLTP where write consistency is critical.
What is the difference between HAVING and WHERE?+
WHERE filters rows before any aggregation occurs, while HAVING filters groups after the GROUP BY and aggregation. For example, WHERE can exclude rows with null salaries, and HAVING can include only departments with average salary above a threshold.
Build the next review session
Browse another free bank or use the study strategy guide to turn your misses into spaced review.