Describe Relational Database Schemas and Normalization Techniques DATA-ENGINEERING-OPTIMIZE Practice Questions (Page 1)

Questions 1–5

1. 1foundation · medium

   A database administrator is deciding whether to normalize a large sales-reporting table. Which statement best describes the primary purpose of normalization?

   • ATo reduce data redundancy and improve data integrity by organizing data into well-structured tables
   • BTo increase query performance by duplicating frequently accessed data across multiple tables
   • CTo compress the physical storage footprint by storing all related data in a single wide table
   • DTo enforce security policies by restricting which columns each user can access
   Reveal Solution Discussion Select an answer first
2. 2foundation · medium

   A data modeler is documenting the structure of a new customer-management database. Which set of components best describes a relational database schema?

   • AThe physical files, disk partitions, and storage allocation used to persist the data
   • BTables, columns, rows, keys, and the relationships defined between tables
   • CIndexes, views, stored procedures, and triggers that optimize query performance
   • DThe set of user permissions, roles, and access policies for the database
   Reveal Solution Discussion Select an answer first
4. 3foundation · medium

   An Employee table has EmployeeID as the primary key and columns: DepartmentID, DepartmentName, and DepartmentLocation. Which option correctly identifies the 3NF violation and the appropriate fix?

   • ADepartmentName and DepartmentLocation depend on DepartmentID, not on EmployeeID; move them to a separate Department table
   • BDepartmentID is a foreign key; keep all columns in the Employee table to preserve the relationship
   • CDepartmentLocation is a multi-valued attribute; split it into a separate table to satisfy 3NF
   • DDepartmentName depends on the full primary key; the table already satisfies 3NF
   Reveal Solution Discussion Select an answer first
5. 4foundation · medium

   A data engineer is designing a reporting database for a large retail chain. The database will support daily sales reports that join a fact table with several dimension tables. The reports are read-only and are used by a fixed set of dashboards. The engineer must reduce query response time for these dashboards. Which denormalization strategy best meets this goal while keeping the risk of data anomalies manageable?

   • APre-join the fact and dimension tables into a single wide table that is refreshed nightly from the normalized source.
   • BKeep the schema fully normalized and rely on the database optimizer to choose the most efficient join order.
   • CAdd redundant summary columns, such as year-to-date totals, directly to the dimension tables.
   • DMerge the dimension tables into the fact table so each row contains all descriptive attributes.
   Reveal Solution Discussion Select an answer first
6. 5foundation · medium

   In a relational database, which statement accurately describes the role of a primary key and a foreign key in establishing a relationship between two tables?

   • AThe foreign key in the child table references the primary key in the parent table, enforcing referential integrity between the tables.
   • BThe primary key in the child table references the foreign key in the parent table, enforcing a one-to-many relationship.
   • CThe primary key uniquely identifies each row in the child table, while the foreign key enforces uniqueness in the parent table.
   • DA primary key and a foreign key are interchangeable terms that both uniquely identify a row within a single table.
   Reveal Solution Discussion Select an answer first