SQL for Data Analytics · Reading relationships
Reading relationships
Representing Relationships5 min
A note on this video
The diagram in the video has small circles and pairs of short lines near the ends of its lines. In the video I set them aside, and the diagrams on this site leave them out. Here, the "one" end of a line has a single short line across it, and the "many" end has a crow's foot.
Here's the HR database from the video. Hover over the line between jobs and employees to light it up.
The end of the line at employees splits into three. This end is a crow's foot, and it means many. The end at jobs has a single short line across it, and it means one. As in the video, read the line once starting from each table:
- Start at jobs. The end at employees is a crow's foot, so a job has many employees.
- Start at employees. The end at jobs is a single line, so an employee has one job.
Jobs and employees have a one-to-many relationship. It's the same kind of relationship as the ones you joined in Northwind.
Hover over the line between countries and locations. Which of these reads the line correctly?
In the video, I pointed out that the dependents table has an employee_id column. A dependent has one spot for an employee_id, so a dependent belongs to one employee. Nothing stops many dependents from having the same employee_id, so an employee can have many dependents.
Hover over the line between employees and dependents. It lights up employee_id in both boxes. In the dependents table, employee_id is a foreign key. It points at the primary key of the employees table.
Every line in the diagram connects a foreign key in one table to the primary key it points at.
The diagram says an employee can have many dependents. Run this to count each employee's dependents.
No employee in the HR database has more than one dependent. Is the diagram wrong to put a crow's foot at the dependents end of the line?
How I'd answer it
No. The diagram shows what the tables allow. A dependent has one spot for an employee_id, and nothing stops two dependents from having the same one. If an employee added a second child tomorrow, nothing about the tables would have to change.
One line in the HR diagram starts and ends at employees. It connects manager_id to employee_id. An employee's manager is another employee, so manager_id holds the manager's employee_id.
Run this to see the first few employees and their managers.
Steven King's manager_id is NULL. Is that a mistake in the data?
How I'd answer it
Probably not. His job_id is 4, and job 4 in the jobs table is President. The president is at the top of the company, so there's no one for him to report to, and NULL is how the table says he has no manager. He's the only one of the 40 employees with no manager. A NULL manager_id on anyone else would be worth asking about.