SQL for Data Analytics · Diagram structure
Diagram structure
Entity Relationship Diagram Structure3 min
Here's the HR database from the video, as a diagram you can click. It has seven tables about a company: its employees, their jobs and dependents, the departments they work in, and where each department is.
Click the countries box to see the first three rows of the countries table.
In the countries box, country_id, country_name, and region_id go down the box, one per line. In the rows under the diagram, the same three names go across the top. They're the columns of the countries table.
A diagram shows what a table keeps track of, like each country's name. It doesn't show any of the data, so it can't tell you which countries are in the table.
The employees table has 40 rows. How many lines are listed under the table's name in the employees box?
In the HR diagram, one column in each box has an asterisk (*) in front of it. The asterisk marks the table's primary key. A primary key's value is different in every row, so it picks out exactly one row. In the employees box it's employee_id, and no two employees have the same one.
Not every diagram uses an asterisk. Some write PK next to the primary key, and some draw a small key. The buttons at the top of the diagram below switch between the three. Every diagram marks the primary key in some way.
Click the countries box, then a few of the other boxes. What's different about the primary key of countries?
How I'd answer it
Every other primary key in the HR database holds numbers. country_id holds two-letter codes, like AR for Argentina and AU for Australia. A primary key can hold any kind of value, as long as no two rows have the same one.
At the end of the video, I looked up what the countries table keeps track of in the diagram, without opening the table. A diagram is also where you check whether a database can answer a request at all, and which tables it would take, before you write any SQL.
These are for your own practice. They aren't graded or turned in.
Which column holds the date an employee was hired? Type the column's name.
The benefits team is mailing open-enrollment packets to every employee with a child. Where in the HR database would that information come from?
An employee asks HR how much their pay has gone up since they started. Can the HR database answer that?
The company is throwing a holiday party in every city where it has an office, and needs a headcount for each city. Which tables would a query for those headcounts need?