This blog post is the second part about relational databases.
The first part with the casus and the first three questions can be found in: “Relational databases – ER diagram part 1“.
The remaining questions in this post are:
4. How many people are single parents?
5. How many households have at least one member unemployed?
6. How many households have a total income less than the norm for receiving social benefits?
Answer question 4
To answer this question we need to introduce three more tables.
Entity family
Each family is registered with a creation date.
This new table can have the following columns:
- family (id_family, creationDate)
Entity familyComposition
This entity contains data about who is part of a family and the role within the family.
Also it has divorce information of family members.
This table can look like this:
- familyComposition (id_familyComposition, id_family, id_humanBeing, id_familyRole, divorced)
Entity familyRole
This entity is for the role a person has in the family.
A person can be a father, mother, child, girlfriend or a boyfriend.
Let’s decide that when a couple has no children, they have the roles of girlfriend and boyfriend.
The table can look like this:
- familyRole (id_familyRole, familyRoleName)
Here is the relationship amongst the above entities:
A family has one or more family compositions.
To become a family, there are at least two people involved, this could be with or without children.
The human being entity is placed on the right side of the familyComposition entity.
It is visible in the full ER diagram at the end of this page.
A human being has zero or more family compositions.
A human being does not need to be in any family (hence the zero annotation).
Think of a moved in immigrant whose partner is still in the home country.
Or a person who grew up in a different municipality and is moved to this municipality with no family.
But a human being can be in more than one family (the crow’s feet “more” annotation).
For example someone was married and later got divorced, that person can start a new family.
Or a child within a family who grows up and start his or her own family in his or her birthplace.
Family role has zero or more family compositions.
It is common sense that a family role like “father” more than one family have it.
The minimum is set to zero, this could be a one (using of a line annotation).
In that case for each family role there are records found in the family composition table.
For a new database with limited data this could be zero.
This means a family role doesn’t need to exists (yet) in the family composition table.
A SQL query that would get the answer to question 4 would be:
SELECT COUNT(*) as 'NoOfSingleParents' FROM familyComposition JOIN familyRole on familyRole.id_familyRole = familyComposition.id_familyRole WHERE (familyRoleName = 'Mother' OR familyRoleName = 'Father') GROUP BY id_humanBeing HAVING divorced = 1 AND id_family = ( SELECT TOP 1 FC.id_family FROM familyComposition FC JOIN family F ON F.id_family = FC.id_family WHERE FC.id_humanBeing = familyComposition.id_humanBeing ORDER BY creationDate DESC )
The data we get back look like this:
| NoOfSingleParents |
| 31 |
A short explanation of the query:
The query first retrieves all the human beings in a family who is a parent.
A human being can have more than one family.
For example being divorced twice with children in former marriages and now have a new partner.
This person is not regarded as a single parent.
The query filters out human beings who are not divorced at all.
It also filters out those who were divorced before but have a partner now.
This is based on a check on the latest creation date of the family the person is in.
So we have records of human beings who are divorced and are a parent without a (registered) partner.
Answer question 5
How many households have at least one member unemployed?
To answer this question we need to introduce three more entities.
Entity employee
In the employee table we keep track of the employee’s start date and end date, his or her monthly net income and the company the employee works for.
The table can look like this:
- employee (id_employee, id_humanBeing, id_company, startDate, endDate, monthlyNetIncome)
Entity company
The table can look like this:
- company (id_company, companyName, creationDate, active)
Entity freelancer
This table is a duplicate of the employee table, except for missing the link to company.
Possible columns:
- freelancer (id_freelancer, startDate, endDate, id_humanBeing, monthlyNetIncome)
Here is the relationship amongst the above entities:
The freelancer and the employee table are both connected to humanBeing table.
This is visible in the full ER diagram at the bottom of this page.
A human being is zero or more freelancer and a human being is zero or more employee.
A human being can be a freelancer (zero annotation). The majority of people are just employees.
A human being can do freelance work, stop with it and start freelancing again (more annotation).
This also applies to being an employee.
People work for more than one company at the same time or one after another, hence the more annotation.
For the answer of question 4, we need the humanBeing table, employee and freelancer table.
This is to see how many human beings living in an address (houdehold) is either employed or is a freelancer.
A SQL query could look like this:
SELECT COUNT(NoUnemployedHumanBeingPerAddress) as 'NoOfHouseholdsUnemployed' FROM ( SELECT COUNT(id_address) as NoUnemployedHumanBeingPerAddress FROM humanBeing WHERE died = FALSE AND inactive = FALSE GROUP BY id_address HAVING ( (humanBeing.id_humanBeing NOT IN (SELECT id_humanBeing FROM employee) AND humanBeing.id_humanBeing NOT IN (select id_humanBeing from freelancer)) OR (SELECT TOP 1 employee.id_humanBeing FROM employee WHERE employee.id_humanBeing = humanBeing.id_humanBeing AND employee.endDate IS NOT NULL AND employee.endDate < GETDATE() ORDER BY employee.startDate DESC) IS NOT NULL OR (SELECT TOP 1 freelancer.id_humanBeing FROM freelancer WHERE freelancer.id_humanBeing = freelancer.id_humanBeing AND freelancer.endDate IS NOT NULL AND freelancer.endDate < GETDATE() ORDER BY freelancer.startDate DESC) IS NOT NULL ) ) ) WHERE NoUnemployedHumanBeingPerAddress > 0
The result of the above query look like this:
| NoOfHouseholdsUnemployed |
| 12 |
The query checks per address to see the employment status of the inhabitants of an address.
Employed inhabitants are filtered out in the HAVING part of the query.
Eventually the total of the households is counted, which has one or more unemployed members.
Answer question 6
How many households have a total income less than the norm for receiving social benefits?
To answer this question we need the sum on the monthlyNetIncome column per address.
Also we need the norm income. Let’s decide the norm income is 1000 euro a month.
With less than 1000 euro a month, the household is eligible to receive social benefits.
The SQL query might look like this:
SELECT COUNT (MonthlyNetIncomePerAddress) as 'NoOfHouseholdsEligible'
FROM
(
SELECT SUM(COALESCE(currentEmployee.monthlyNetIncome,0) + COALESCE(currentFreelancer.monthlyNetIncome,0)) as MonthlyNetIncomePerAddress
FROM humanBeing
LEFT JOIN employee ON employee.id_humanBeing = humanBeing.id_humanBeing AND
employee.id_employee IN (SELECT employee.id_employee
FROM employee
WHERE employee.id_humanBeing = humanBeing.id_humanBeing
AND employee.endDate IS NULL
ORDER BY employee.startDate DESC)
LEFT JOIN freelancer ON freelancer.id_humanBeing = humanBeing.id_humanBeing AND
freelancer.id_freelancer IN (SELECT freelancer.id_freelancer
FROM freelancer
WHERE freelancer.id_humanBeing = humanBeing.id_humanBeing
AND freelancer.endDate IS NULL
ORDER BY freelancer.startDate DESC)
WHERE died = FALSE AND inactive = FALSE
GROUP BY id_address
)
WHERE MonthlyNetIncomePerAddress < 1000
The result of the above query can look like this:
| NoOfHouseholdsEligble |
| 5 |
The query gets the monthly net income of a human being from the employee and freelancer table.
A human being can be doing both, freelancing and working for one or more companies.
Eventually the SUM is calculated for all the inhabitants of an address for the monthly net income per household.
With the COUNT the number of households are counted that has less than 1000 euros a month net income.
Finally, here is the full ER diagram:
