Preview Questions Data Modeling Concepts | Role Specific Skill

Question 1: Your database has tables with high update anomalies. What is your normalization approach?

Which action should you take?

Choose only one option

Normalize up to Third Normal Form (3NF)

Normalize up to Second Normal Form (2NF)

Normalize up to Fourth Normal Form (4NF)

Use Boyce-Codd Normal Form (BCNF)

Question 2: How do you find departments where the total salary expense is above the company average?

Which action should you take?

Choose only one option

Use a nested query with SUM() and AVG()

Use a window function with PARTITION BY and SUM()

Use a CTE with aggregate functions

Use a subquery with SUM() and AVG()

Question 3: You need to represent the relationship between use cases and actors. Which UML diagram do you use?

Which action should you take?

Choose only one option

Use a use case diagram

Use a class diagram

Use a sequence diagram

Use an activity diagram

Question 4: You need to retrieve the top 3 salespeople based on their total sales. What SQL statement do you use?

Which action should you take?

Choose only one option

Use a window function with RANK()

Use a nested query with ORDER BY and LIMIT

Use a CTE with ROW_NUMBER()

Use a subquery with GROUP BY

Question 5: How do you model the hierarchy and relationships of classes in a system?

Which action should you take?

Choose only one option

Use a class diagram

Use a sequence diagram

Use a state diagram

Use a component diagram

Question 6: What strategy would you use to optimize query performance in a dimensional model?

Which action should you take?

Choose only one option

Create indexes on frequently queried columns to speed up data retrieval

Increase the database server's memory capacity

Normalize all dimension tables to reduce redundancy

Limit the number of concurrent users accessing the database