edu.wright.hendrix11.familyTree.dataBean.mining

Class BirthsAndDeathsDataBean

java.lang.Object

edu.wright.hendrix11.familyTree.dataBean.mining.BirthsAndDeathsDataBean

public class BirthsAndDeathsDataBean
extends Object

This class queries the database for the number of births and deaths per unit (the unit varies per method).

Author:

Joe Hendrix

Constructor Summary

Constructors

Constructor and Description
BirthsAndDeathsDataBean()

Method Summary

Modifier and Type	Method and Description
Map<String,Integer[]>	perDecade() Returns a map of decades (as a String) to an integer array of births and deaths in that decade.
Map<String,Integer[]>	perDecadeClean() Returns a map of decades (as a String) to an integer array of births and deaths in that decade.
Map<String,Integer[]>	perDecadeCombined() Returns a map of decades (as a String) to an array integer of births and deaths in that decade.
Map<String,Integer[]>	perMonth() Returns a map of months (as a String) to an integer array of births and deaths in that month.

Methods inherited from class java.lang.Object

equals, getClass, hashCode, notify, notifyAll, toString, wait, wait, wait

Constructor Detail

BirthsAndDeathsDataBean

public BirthsAndDeathsDataBean()

Method Detail

perDecadeCombined

public Map<String,Integer[]> perDecadeCombined()

Returns a map of decades (as a String) to an array integer of births and deaths in that decade. Internally, this is a LinkedHashMap. The number of births is in the zeroth element, and the number of deaths is in the next element.

The method gets its values from PER_DECADE_COMBINED_VIEW, which is just a sum of the union of PER_DECADE_CLEAN_VIEW and PER_DECADE_VIEW.

The SQL query that defines the view is as follows:

 SELECT SUM( BIRTHS ) BIRTHS,
 	   SUM( DEATHS ) DEATHS,
 	   DECADE
 FROM
 	(SELECT * FROM PER_DECADE_VIEW
 	UNION
 	SELECT * FROM PER_DECADE_CLEAN_VIEW)
 GROUP BY DECADE
 ORDER BY DECADE
 

Returns:

a map of decades to an integer array of births and deaths in that decade

perDecadeClean

public Map<String,Integer[]> perDecadeClean()

Returns a map of decades (as a String) to an integer array of births and deaths in that decade. Internally, this is a LinkedHashMap. The number of births is in the zeroth element, and the number of deaths is in the next element.

The method gets its values from PER_DECADE_CLEAN_VIEW, which estimates the birth year and death year where the other is not known. It assumes an average age.

The SQL query that defines the view is as follows:

 SELECT NVL( BIRTH.COUNT,0 ) BIRTHS,
 	   NVL( DEATH.COUNT,0 ) DEATHS,
 	   NVL( BIRTH.DECADE,DEATH.DECADE ) DECADE
 FROM
 	(SELECT COUNT( * ) COUNT,
 		  ( D.YEAR - AVERAGE - MOD( D.YEAR - AVERAGE,10 ) ) DECADE
 	FROM EVENT D,
 		(SELECT AVG(AGE) AVERAGE FROM AGE_VIEW)
 	WHERE D.TYPE = 'death'
 	AND D.YEAR IS NOT NULL
 	AND ( D.PERSON_ID NOT IN
 		(SELECT PERSON_ID FROM EVENT WHERE TYPE = 'birth')
 	OR EXISTS
 		(SELECT * FROM EVENT B WHERE B.TYPE = 'birth' AND B.YEAR IS NULL AND B.PERSON_ID=D.PERSON_ID) )
 	GROUP BY( D.YEAR - AVERAGE - MOD( D.YEAR - AVERAGE,10 ) ) ) BIRTH
 FULL OUTER JOIN
 	(SELECT COUNT( * ) COUNT,
 		  ( B.YEAR + AVERAGE - MOD( B.YEAR + AVERAGE ,10 ) ) DECADE
 	FROM EVENT B, (SELECT AVG(AGE) AVERAGE FROM AGE_VIEW)
 	WHERE B.TYPE = 'birth'
 	AND B.YEAR IS NOT NULL
 	AND( B.PERSON_ID NOT IN
 	   (SELECT PERSON_ID FROM EVENT WHERE TYPE = 'death')
 	OR EXISTS
 	   (SELECT * FROM EVENT D WHERE D.TYPE = 'death' AND D.YEAR IS NULL AND D.PERSON_ID=B.PERSON_ID) )
 	GROUP BY( B.YEAR + AVERAGE - MOD( B.YEAR + AVERAGE ,10 ) ) ) DEATH
 ON BIRTH.DECADE = DEATH.DECADE
 ORDER BY DECADE
 

Returns:

a map of decades to an integer array of births and deaths in that decade

perDecade

public Map<String,Integer[]> perDecade()

Returns a map of decades (as a String) to an integer array of births and deaths in that decade. Internally, this is a LinkedHashMap. The number of births is in the zeroth element, and the number of deaths is in the next element.

The method gets its values from PER_DECADE_VIEW, which is only contains the subset of births and deaths where either the birth year or death year are known.

The SQL query that defines the view is as follows:

 SELECT NVL( BIRTH.COUNT,0 ) BIRTHS,
 	   NVL( DEATH.COUNT,0 ) DEATHS,
 	   NVL( BIRTH.DECADE,DEATH.DECADE ) DECADE
 FROM
 	(SELECT COUNT( * ) COUNT,
 		  ( YEAR - MOD( YEAR,10 ) ) DECADE
 	FROM EVENT
 	WHERE EVENT.TYPE = 'birth'
 	AND EVENT.YEAR IS NOT NULL
 	GROUP BY( YEAR - MOD( YEAR,10 ) ) ) BIRTH
 FULL OUTER JOIN
 	(SELECT COUNT( * ) COUNT,
 		  ( YEAR - MOD( YEAR,10 ) ) DECADE
 	FROM EVENT
 	WHERE EVENT.TYPE = 'death'
 	AND EVENT.YEAR IS NOT NULL
 	GROUP BY( YEAR - MOD( YEAR,10 ) ) ) DEATH
 ON BIRTH.DECADE = DEATH.DECADE
 ORDER BY DECADE
 

Returns:

a map of decades to an integer array of births and deaths in that decade

perMonth

public Map<String,Integer[]> perMonth()

Returns a map of months (as a String) to an integer array of births and deaths in that month. Internally, this is a LinkedHashMap. The number of births is in the zeroth element, and the number of deaths is in the next element.

The method gets its values from PER_MONTH_VIEW, which is only contains the subset of births and deaths where the month known.

The SQL query that defines the view is as follows:

 SELECT NVL( BIRTH.COUNT,0 ) BIRTHS,
 	   NVL( DEATH.COUNT,0 ) DEATHS,
 	   NVL( BIRTH.MONTH,DEATH.MONTH ) MONTH
 FROM
 	(SELECT COUNT( * ) COUNT,
 		    MONTH
 	FROM EVENT
 	WHERE TYPE = 'birth'
 		AND MONTH IS NOT NULL
 	GROUP BY MONTH) BIRTH
 FULL OUTER JOIN
 	(SELECT COUNT( * ) COUNT,
 		    MONTH
 	FROM EVENT
 	WHERE TYPE = 'death'
 		AND MONTH IS NOT NULL
 	GROUP BY MONTH) DEATH
 ON BIRTH.MONTH = DEATH.MONTH
 ORDER BY MONTH
 

Returns:

a map of months to an integer array of births and deaths in that month