MDB101 Taller 01

From Wiki-UX.info
Jump to: navigation, search

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

Autores

Retrieved from "http://wiki-ux.info/w/index.php?title=MDB101_Taller_01&oldid=17277"