Relational databases – ER diagram Part 2

Relational databases – ER diagram Part 2

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:

Comments

comments

Leave a Reply