MDB101 Taller 01

Instrucciones

Subir en su base de datos en Postgres, los datos que se encuentran en el archivo nombrado como RegistroCivil.txt, el cual se ubica en la dirección: REGISTROCIVIL_UTF8.txt. Este es un archivo de texto, para lo cual puede emplear el comando COPY para cargar los datos.

• Datos de configuration del servidor PostgreSQL
  • Procesador: 2x Pentium Xeon 3600 Mhz 2MB L2 Nocona
  • Memoria: 8 GB
  • Disco: 2x76GB 10k, Raid 0 (Stripped)
  • Windows XP x64

• Parametros de configuración PostgreSQL
  • effective_cache_size: 6GB
  • shared_buffers = 512MB
  • work_mem = 32MB
  • maintenance_work_mem = 512MB
  • max_stack_depth = 2 MB

Cargar la tabla de datos

Crear la tabla con los tipos indicados

CREATE TABLE stage_registro (
	cedula			TEXT,
	numero_folio		TEXT,
	cedula_padre		TEXT,
	cedula_madre		TEXT,
	codigo_hospital		TEXT,
	hora_suceso		TEXT,
	fecha_suceso		TEXT,
	genero			TEXT,
	nacionalidad		TEXT,
	defuncion		TEXT,
	pais_del_padre		TEXT,
	pais_de_la_madre	TEXT,
	prov_canton_madre	TEXT,
	fecha_naturalizacion	TEXT,
	apellido1		TEXT,
	apellido2		TEXT,
	nombre			TEXT,
	nombre_padre		TEXT,
	nombre_madre		TEXT,
	lugar_nacimiento	TEXT
);

Cargar la información con el comando COPY

COPY stage_registro
FROM '[PATH]\REGISTROCIVIL_UTF8.txt'
DELIMITERS ';'

• Tiempo de Ejecución: Query OK, 0 rows affected (46.02 sec)

Genere las siguientes sentencias

Tiempo de Ejecución
# Sentencia	# Tuplas retornadas	Tiempo (segundos)
1	0	67.46
2	1	2.08
3	1	4.92
4	721070	9.08
5	721070	3.83
6	721070	1.86
7	24133	4.05
8	36136	8.20
9	36136	8.31
10	31334	4.49
11	501937	4.81
12	501937	6.31
13	360761	28.55

1. Listar toda la información de la tabla RegistroCivil ( o el nombre que usted utilizó para subir los datos) y cuánto tiempo duró la sentencia en ejecutarse.

COPY (SELECT * FROM stage_registro) TO '[PATH]\data.tmp' WITH CSV;

• Tiempo de Ejecución: Query OK, 0 rows affected (67.46 sec)

2. Cuente cuántos registros tiene la tabla RegistroCivil ( o el nombre que usted utilizó para subir los datos) y cuánto tiempo duró la sentencia en ejecutarse.

SELECT COUNT(cedula)
FROM stage_registro;

• Registros: 4278531
• Tiempo de Ejecución: 1 rows fetched (2.08 sec)

3. Cuántos nombres diferentes hay en la tabla y cuánto tiempo duró la sentencia en ejecutarse

SELECT COUNT(a.nombre)
FROM
(
	SELECT DISTINCT nombre
	FROM stage_registro
) AS a;

• Tiempo de Ejecución: 1 rows fetched (4.92 sec)

4. Cuántos nombres diferentes hay en la tabla y cuánto tiempo duró la sentencia en ejecutarse. Debe ordenar la salida de resultados en orden descendente por número de apariciones de nombres.

SELECT DISTINCT nombre, COUNT(nombre)
FROM stage_registro
GROUP BY nombre
ORDER BY 2 DESC;

• Tiempo de Ejecución: 721070 rows fetched (9.081 sec)

5. Cuántos personas por género hay en la tabla y cuánto tiempo duró la sentencia en ejecutarse. Debe ordenar la salida de resultados en orden descendente por número de apariciones de nombres.

SELECT DISTINCT genero, COUNT(nombre)
FROM stage_registro
GROUP BY genero
ORDER BY 2 DESC;

• Tiempo de Ejecución: 721070 rows fetched (3.83 sec)

6. Cuántas veces aparece el nombre "XXXXX" en la tabla y cuánto tiempo duró la sentencia en ejecutarse. Debe sustituir el patrón XXXXX por el nombre que usted desee ubicar.

SELECT nombre, COUNT(nombre) AS total
FROM stage_registro
WHERE nombre IN ( 'ALEJANDRO', 'JOSE' )
GROUP BY nombre
ORDER BY 2 DESC;

nombre	total
JOSE	12550
ALEJANDRO	10092

• Tiempo de Ejecución: 721070 rows fetched (1.86 sec)

7. Cuántos apellidos(1) diferentes hay en la tabla y cuánto tiempo duró la sentencia en ejecutarse. Debe ordenar la salida de resultados en orden descendente por número de apariciones de apellido.

SELECT apellido1, COUNT(apellido1)
FROM stage_registro
GROUP BY apellido1
ORDER BY 2 DESC;

• Tiempo de Ejecución: 24133 rows fetched (4.05 sec)

8. Cuántos apellidos diferentes hay en la tabla y cuánto tiempo duró la sentencia en ejecutarse. Debe ordenar la salida de resultados en orden descendente por número de apariciones de apellido.

SELECT a.apellido, COUNT(a.apellido)
FROM 
(
	SELECT apellido1 AS apellido
	FROM stage_registro
	UNION ALL
	SELECT apellido2 AS apellido
	FROM stage_registro
) AS a
GROUP BY a.apellido
ORDER BY 2 DESC;

• Tiempo de Ejecución: 36136 rows fetched (8.20 sec)

9. Cuántos apellidos diferentes por Genero en la tabla y cuánto tiempo duró la sentencia en ejecutarse. Debe ordenar la salida de resultados en orden descendente por número de apariciones de apellido.

• Alternativa A:

SELECT A.apellido, SUM(A.total) AS total
FROM
(
	SELECT apellido1 AS apellido, count(apellido1) AS total FROM stage_registro
	GROUP BY apellido1
	UNION ALL
	SELECT apellido2, count(apellido2) AS total FROM stage_registro
	GROUP BY apellido2
) A
GROUP BY apellido
ORDER BY 2 DESC;

• Tiempo de Ejecución: 36136 rows fetched (8.42 sec)

• Alternativa B:

SELECT a.apellido, COUNT(a.apellido)
FROM 
(
	SELECT apellido1 AS apellido
	FROM stage_registro
	UNION ALL
	SELECT apellido2 AS apellido
	FROM stage_registro
) AS a
GROUP BY a.apellido
ORDER BY 2 DESC;

• Tiempo de Ejecución: 36136 rows fetched (8.31 sec)

10. Listar todos los diferentes Lugares de Nacimiento registrados con el número de personas registradas nacidas en ese lugar.

SELECT lugar_nacimiento, count(cedula)
FROM stage_registro
GROUP BY lugar_nacimiento;

• Tiempo de Ejecución: 31334 rows fetched (4.49 sec)

11. Listar el número de hijos que tienen cada PADRE. Desplegar la cédula del PADRE y el número de hijos. Cómo validaría la información que esté correcta?

SELECT
	cedula_padre,
	count(cedula) AS num_hijos
FROM stage_registro
WHERE cedula_padre <> '000000000'
GROUP BY cedula_padre;

• Tiempo de Ejecución: 501937 rows fetched (4.81 sec)

• Validación: a = b dónde:

A, n = 4278531:

SELECT COUNT(cedula)
FROM stage_registro
WHERE cedula_padre <> '000000000';

B, n = 4278531:

SELECT
SUM(A.num_hijos) AS total_hijos
FROM
(
	SELECT cedula_padre, count(cedula) AS num_hijos
	FROM stage_registro
	WHERE cedula_padre <> '000000000'
	GROUP BY cedula_padre
) AS A;

12. Listar el número de hijos que tienen cada MADRE. Desplegar la cédula de la MADRE y el número de hijos. Cómo validaría la información que esté correcta?

<source lang="sql">
SELECT
	cedula_madre,
	count(cedula) AS num_hijos
FROM stage_registro
WHERE cedula_madre <> '000000000'
GROUP BY cedula_madre;

• Tiempo de Ejecución: 501937 rows fetched (6.31 sec)

• Validación: misma que 11, pero para cedula_madre

13. Listar el número de hijos que tienen cada padre y cada madre. Desplegar la cédula del padre y el número de hijos, luego la cédula de la madre y el número de hijos. Cómo validaría la información que esté correcta?

• Alternativa A:

SELECT DISTINCT
	PM.cedula_padre,
	P.num_hijos_padre,
	PM.cedula_madre,
	M.num_hijos_madre
FROM stage_registro AS PM
JOIN
(
	SELECT cedula_padre, count(cedula) AS num_hijos_padre
	FROM stage_registro WHERE cedula_padre <> '000000000'
	GROUP BY cedula_padre
) AS P
	ON PM.cedula_padre = P.cedula_padre
JOIN
(	
	SELECT cedula_madre, count(cedula) AS num_hijos_madre
	FROM stage_registro WHERE cedula_madre <> '000000000'
	GROUP BY cedula_madre
) AS M
	ON PM.cedula_madre = M.cedula_madre
WHERE (PM.cedula_padre <> '000000000' OR PM.cedula_madre <> '000000000');

• Tiempo de Ejecución: 360761 rows fetched (52.55 sec)

• Alternativa B, más rápida

SELECT
	PM.cedula_padre,
	P.num_hijos_padre,
	PM.cedula_madre,
	M.num_hijos_madre
FROM 
(
	SELECT DISTINCT
		cedula_padre,
		cedula_madre
	FROM stage_registro
	WHERE cedula_padre <> '000000000' AND cedula_madre <> '000000000'
	) AS PM
JOIN
(
	SELECT cedula_padre, count(cedula) AS num_hijos_padre
	FROM stage_registro WHERE cedula_padre <> '000000000'
	GROUP BY cedula_padre
) AS P
	ON PM.cedula_padre = P.cedula_padre
JOIN
(	
	SELECT cedula_madre, count(cedula) AS num_hijos_madre
	FROM stage_registro WHERE cedula_madre <> '000000000'
	GROUP BY cedula_madre
) AS M
	ON PM.cedula_madre = M.cedula_madre;

• Tiempo de Ejecución: 360761 rows fetched (28.55 sec)

14. Calcule el tamaño en bytes que tiene la tabla RegistroCivil

• Espacio real en disco:

SELECT pg_size_pretty(pg_total_relation_size('stage_registro'));

• Espacio ocupado: 864 MB

• Tamaño de la informacion en caracteres, por ser campo tipo TEXT:

SELECT
	SUM(SZ_cedula) TOTAL_cedula,
	SUM(SZ_numero_folio) TOTAL_numero_folio,
	SUM(SZ_cedula_padre) TOTAL_cedula_padre,
	SUM(SZ_cedula_madre) TOTAL_cedula_madre,
	SUM(SZ_codigo_hospital) TOTAL_codigo_hospital,
	SUM(SZ_hora_suceso) TOTAL_hora_suceso,
	SUM(SZ_fecha_suceso) TOTAL_fecha_suceso,
	SUM(SZ_genero) TOTAL_genero,
	SUM(SZ_nacionalidad) TOTAL_nacionalidad,
	SUM(SZ_defuncion) TOTAL_defuncion,
	SUM(SZ_pais_del_padre) TOTAL_pais_del_padre,
	SUM(SZ_pais_de_la_madre) TOTAL_pais_de_la_madre,
	SUM(SZ_prov_canton_madre) TOTAL_prov_canton_madre,
	SUM(SZ_fecha_naturalizacion) TOTAL_fecha_naturalizacion,
	SUM(SZ_apellido1) TOTAL_apellido1,
	SUM(SZ_apellido2) TOTAL_apellido2,
	SUM(SZ_nombre) TOTAL_nombre,
	SUM(SZ_nombre_padre) TOTAL_nombre_padre,
	SUM(SZ_nombre_madre) TOTAL_nombre_madre,
	SUM(SZ_lugar_nacimiento) TOTAL_lugar_nacimiento
FROM (
	SELECT
		LENGTH(cedula) SZ_cedula,
		LENGTH(numero_folio) SZ_numero_folio,
		LENGTH(cedula_padre) SZ_cedula_padre,
		LENGTH(cedula_madre) SZ_cedula_madre,
		LENGTH(codigo_hospital) SZ_codigo_hospital,
		LENGTH(hora_suceso) SZ_hora_suceso,
		LENGTH(fecha_suceso) SZ_fecha_suceso,
		LENGTH(genero) SZ_genero,
		LENGTH(nacionalidad) SZ_nacionalidad,
		LENGTH(defuncion) SZ_defuncion,
		LENGTH(pais_del_padre) SZ_pais_del_padre,
		LENGTH(pais_de_la_madre) SZ_pais_de_la_madre,
		LENGTH(prov_canton_madre) SZ_prov_canton_madre,
		LENGTH(fecha_naturalizacion) SZ_fecha_naturalizacion,
		LENGTH(apellido1) SZ_apellido1,
		LENGTH(apellido2) SZ_apellido2,
		LENGTH(nombre) SZ_nombre,
		LENGTH(nombre_padre) SZ_nombre_padre,
		LENGTH(nombre_madre) SZ_nombre_madre,
		LENGTH(lugar_nacimiento) SZ_lugar_nacimiento
	FROM stage_registro
) A;

• Tamaño de la informacion: 642 MB, hay un overhead de 34% al ser almacenado en la tabla.

15. Evalue los problemas de Normalización que presenta la información. Coméntelos y sugiera los cambios necesarios.

1. La tabla utiliza un solo tipo de datos: TEXT. Los tipos pueden seleccionarse para la mejor representación de cada uno de los atributos y reducir el espacio que requiere así como brindar las restricciones de dominio implícitas por el tipo de dato.
2. La tabla presenta muchas faltas a las formas normales, especialmente atributos que no dependen de llave primarias y problemas de transitividad.

El siguiente pseudo-código propone una normalización de la información:

TABLA: persona
cedula			NUMERIC(9),
apellido1 		CHAR(13),
apellido2 		CHAR(13),
nombre			CHAR(30),
genero 			CHAR(1),
cedula_padre 		NUMERIC(9),
cedula_madre 		NUMERIC(9),
numero_folio 		NUMERIC(3),
canton_id		SMALLINT,
provincia_id		SMALLINT,
hospital_id		CHAR(3),
fecha_nacimiento	DATE,
hora_nacimiento		TIME,
fecha_naturalizacion	DATE,
pais_id 		CHAR(3),
defuncion		CHAR(1) DEFAULT 'N'

-- cedula_padre LLAVE FORÁNEA -> persona
-- cedula_madre LLAVE FORÁNEA -> persona
-- codigo_hospital LLAVE FORÁNEA -> hospital
-- pais_id LLAVE FORÁNEA -> pais

TABLA pais
pais_id		CHAR(2),
pais_id3	CHAR(3),
pais_idnum	SMALLINT
pais_nombre	CHAR(45),
nacionalidad	CHAR(60)

TABLA provincia
provincia_id		SMALLINT,
pais_id			CHAR(3),
provincia_nombre	CHAR(30)

TABLA canton
canton_id	SMALLINT,
provincia_id	CHAR(30),
canton_nombre	CHAR(30)

-- provincia_id  LLAVE FORÁNEA -> provincia

TABLA hospital
hospital_id	CHAR(3)

17. Crear una tabla con la información de las provincias. Debe cargar los datos de cada provincia (nombre).

CREATE TABLE provincia
(
	provincia_id SMALLINT,
	provincia_nombre CHAR(30)
)

INSERT INTO provincia ( provincia_id, provincia_nombre) VALUES (1,'SAN JOSE');
INSERT INTO provincia ( provincia_id, provincia_nombre) VALUES (2,'ALAJUELA');
INSERT INTO provincia ( provincia_id, provincia_nombre) VALUES (3,'CARTAGO');
INSERT INTO provincia ( provincia_id, provincia_nombre) VALUES (4,'HEREDIA');
INSERT INTO provincia ( provincia_id, provincia_nombre) VALUES (5,'GUANACASTE');
INSERT INTO provincia ( provincia_id, provincia_nombre) VALUES (6,'PUNTARENAS');
INSERT INTO provincia ( provincia_id, provincia_nombre) VALUES (7,'LIMON');

References

• Tuning Your PostgreSQL Server

Autores

• Alejandro Marin Badilla
• Jose Agustin Pla