From Wiki-UX.info

MDB101 Taller 01
Jump to: navigation, search

MDB101 Taller 01

Contents

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 retornadasTiempo (segundos)
1067.46
212.08
314.92
47210709.08
57210703.83
67210701.86
7241334.05
8361368.20
9361368.31
10313344.49
115019374.81
125019376.31
1336076128.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;
nombretotal
JOSE12550
ALEJANDRO10092
  • 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

Autores

Retrieved from "http://wiki-ux.info/wiki/MDB101_Taller_01"
This page was last modified on 20 October 2016, at 02:58. This page has been accessed 943 times.