MDB101 Taller 01
Contents
Instrucciones[edit]
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[edit]
Crear la tabla con los tipos indicados[edit]
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[edit]
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[edit]
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.
- 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.
- 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');