Cardinality in Data Modeling | Dataford Interview Questions

Can you explain your experience with data modeling and handling cardinality in your projects for KPMG clients?

Use the supplied client, project, and project assignment tables to demonstrate this with a query. Return every client, including clients without projects, while avoiding inflated counts caused by one-to-many relationships.

Output

One row per client with client_name, project_count, assignment_count, and total_hours
Include clients with no related projects or assignments, using zero totals
Order by total_hours descending, then client_name ascending

Schema

clients

Column	Type	Description
client_idPK	INT	Unique client identifier
client_name	VARCHAR(100)	Client name

projects

Column	Type	Description
project_idPK	INT	Unique project identifier
client_id	INT	Client associated with the project
project_name	VARCHAR(120)	Project name

project_assignments

Column	Type	Description
assignment_idPK	INT	Unique assignment identifier
project_id	INT	Project receiving the assignment
assignee_name	VARCHAR(100)	Assigned team member
hours	DECIMAL(10,2)	Hours recorded for the assignment

Your question is Cardinality in Data Modeling. Start with the requirements and the three tables in the Question tab.

Run and submit as often as you like. When you're ready, talk me through your approach or go straight to the code.

Problem