Tarea obligatoria 3

Modelado de DataWarehouse

El futbol mexicano mantiene una base de datos basada en el siguiente modelo E-R.

 

Pero ahora quiere realizar un proyecto de datawarehousing en el cual almacene el histórico de todas las temporadas y empezar a llevar un registro histórico de todos los jugadores y sus records.

El analista de la FIFA tiene algunas interrogantes que de antemano quiere resolver:

 

Realizar el modelo e-r de negocio, el modelo e-r del dw (paso por paso), con sus respectivas tablas, información de cómo se generan las tablas (a partir de otras, SQL) y al menos 5 tuplas ejemplo.

Solución:

Modelo de negocio (se quedó igual)

 

Modelo del datawarehouse (dejando todos los datos)

 

Modelo del datawarehouse (solamente los datos de interés)

 

 

Queries para alimentar el DW

Se realizan operaciones al inicio de temporada, en cada jornada y al fin de la temporada

###############################################################################################

###Comienza la temporada---

##Agrego una temporada nueva
insert into temporada values ("Apertura",year(curdate()),curdate(), null,-1);
##Actualizo los equipos, debo dejar los anteriores porque ahi estan los totales de todos los goles de la historia
insert into equipo (nombre, total_goles) from select nombre,0 from futbol.equipo where nombre not in (select nombre from futbol_dw.equipo);
##Actualizo el historico de equipo, para saber si el equipo cambio o no de duenio o ciudad
insert into equipo_historico (nombre,mes,anio,duenio,ciudad) select nombre, month(curdate()),year(curdate()),duenio,ciudad from futbol.equipo;

###############################################################################################

## En cada jornada


## Actualizo los datos de todas las jornadas de la temporada actual
replace into jornada (numero,temporada, anio, fecha_inicio, fecha_terminacion, total_goles) select numero, "Apertura", year(curdate()), fecha_inicio,fecha_terminacion,0 from futbol.jornada where numero=(select max(numero) from futbol.jornada);
replace into encuentro (equipo_a,equipo_b,jornada,temporada,anio,goles_a,goles_b) select equipo_a,equipo_b,jornada,"Apertura", year(curdate()),goles_a,goles_b from futbol.encuentro;
## Actualizo los goles de la jornada
create temporary table goles_jornada (total_goles int,jornada int);
insert into goles_jornada select sum(goles_a)+sum(goles_b),jornada from futbol.encuentro group by jornada;
update jornada,goles_jornada set jornada.total_goles=goles_jornada.total_goles where jornada.numero=goles_jornada.jornada;

##Actualizo el total de goles de cada equipo
create temporary table goles_equipo (numgoles int,equipo char(10));
insert into goles_equipo select sum(goles_a),equipo_a from encuentro group by equipo_a;
insert into goles_equipo select sum(goles_b),equipo_b from encuentro group by equipo_b;
create temporary table total_goles_equipo (numgoles int,equipo char(10));
insert into total_goles_equipo select sum(numgoles),equipo from goles_equipo group by equipo;
update equipo,total_goles_equipo set equipo.total_goles=total_goles_equipo.numgoles where equipo.nombre=total_goles_equipo.equipo;

## Actualizo el promedio de goles por jornada por temporada
select avg(total_goles) from jornada where temporada="Apertura" and anio=year(curdate());
update temporada set promedio_goles_jornada=(select avg(total_goles) from jornada where temporada="Apertura" and anio=year(curdate())) where nombre="Apertura" and anio=year(curdate());

###############################################################################################

###Termina la temporada---
##Actualizo la fecha de terminacion de temporada
update temporada set fecha_terminacion=curdate() where nombre="Apertura" and anio=year(curdate());

###############################################################################################

 

 

Ejemplo de simulación con 2 jornadas del futbol

## Inicia la jornada 1

mysql> use futbol;
Database changed

mysql> show tables;
+------------------+
| Tables_in_futbol |
+------------------+
| encuentro |
| equipo |
| jornada |
| juega |
| jugador |
+------------------+
5 rows in set (0.00 sec)

mysql> insert into jornada values (1,'2007-9-30','2007-10-2');
mysql> insert into equipo values ('america','emilio','df');
Query OK, 1 row affected (0.31 sec)

mysql> insert into equipo values ('chivas','vergara','guadalajara');
Query OK, 1 row affected, 1 warning (0.22 sec)

mysql> insert into equipo values ('pumas','unam','df');
Query OK, 1 row affected (0.13 sec)

mysql> insert into equipo values ('franja','abraham','puebla');
Query OK, 1 row affected (0.17 sec)

mysql> select * from equipo;
+---------+---------+------------+
| nombre | ciudad | duenio |
+---------+---------+------------+
| america | emilio | df |
| chivas | vergara | guadalajar |
| franja | abraham | puebla |
| pumas | unam | df |
+---------+---------+------------+
4 rows in set (0.00 sec)

mysql>
mysql> insert into encuentro values (1,'pumas','america',3,3);
Query OK, 1 row affected (0.16 sec)

mysql> insert into encuentro values (1,'franja','chivas',2,1);
Query OK, 1 row affected (0.17 sec)

mysql> select * from encuentro;
+---------+----------+----------+---------+---------+
| jornada | equipo_a | equipo_b | goles_a | goles_b |
+---------+----------+----------+---------+---------+
| 1 | franja | chivas | 2 | 1 |
| 1 | pumas | america | 3 | 3 |
+---------+----------+----------+---------+---------+
2 rows in set (0.00 sec)

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

## Inicia la jornada 2

mysql> insert into jornada values (2,'2007-10-7','2007-10-10');
Query OK, 1 row affected (0.13 sec)

mysql> insert into encuentro values (2,'chivas','america',1,2);
Query OK, 1 row affected (0.19 sec)

mysql> insert into encuentro values (2,'pumas','franja',1,1);
Query OK, 1 row affected (0.19 sec)

mysql> select * from jornada;
+--------+--------------+-------------------+
| numero | fecha_inicio | fecha_terminacion |
+--------+--------------+-------------------+
| 1 | 2007-09-30 | 2007-10-02 |
| 2 | 2007-10-07 | 2007-10-10 |
+--------+--------------+-------------------+
2 rows in set (0.00 sec)

mysql> select * from encuentro;
+---------+----------+----------+---------+---------+
| jornada | equipo_a | equipo_b | goles_a | goles_b |
+---------+----------+----------+---------+---------+
| 1 | franja | chivas | 2 | 1 |
| 1 | pumas | america | 3 | 3 |
| 2 | chivas | america | 1 | 2 |
| 2 | pumas | franja | 1 | 1 |
+---------+----------+----------+---------+---------+
4 rows in set (0.00 sec)

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

## Se inicia la temporada y se pasa al dw la jornada 1


mysql> use futbol_dw;
Database changed

mysql> show tables;
+---------------------+
| Tables_in_futbol_dw |
+---------------------+
| encuentro |
| equipo |
| equipo_historico |
| jornada |
| temporada |
+---------------------+
5 rows in set (0.00 sec)

mysql>


mysql> insert into temporada values ("Apertura",year(curdate()),curdate(), null,-1);
Query OK, 1 row affected (0.17 sec)


mysql> insert into equipo (nombre, total_goles) select nombre,0 from futbol.equipo where nombre not in (select nombre from futbol_dw.equipo);
Query OK, 4 rows affected (0.23 sec)
Records: 4 Duplicates: 0 Warnings: 0

mysql> select * from temporada;
+----------+------+--------------+-------------------+------------------------+
| nombre | anio | fecha_inicio | fecha_terminacion | promedio_goles_jornada |
+----------+------+--------------+-------------------+------------------------+
| Apertura | 2007 | 2007-09-29 | NULL | -1 |
+----------+------+--------------+-------------------+------------------------+
1 row in set (0.00 sec)

mysql> select * from equipo;
+---------+-------------+
| nombre | total_goles |
+---------+-------------+
| america | 0 |
| chivas | 0 |
| franja | 0 |
| pumas | 0 |
+---------+-------------+
4 rows in set (0.00 sec)

mysql>
mysql> insert into equipo_historico (nombre,mes,anio,duenio,ciudad) select nombre, month(curdate()),year(curdate()),duenio,ciudad from futbol.equipo;
Query OK, 4 rows affected (0.19 sec)
Records: 4 Duplicates: 0 Warnings: 0

mysql> select * from equipo_historico;
+---------+-----+------+---------+------------+
| nombre | mes | anio | ciudad | duenio |
+---------+-----+------+---------+------------+
| america | 9 | 2007 | emilio | df |
| chivas | 9 | 2007 | vergara | guadalajar |
| franja | 9 | 2007 | abraham | puebla |
| pumas | 9 | 2007 | unam | df |
+---------+-----+------+---------+------------+
4 rows in set (0.00 sec)

mysql> replace into jornada (numero,temporada, anio, fecha_inicio, fecha_terminacion, total_goles) select numero, "Apertura", year(curdate()), fecha_inicio,fecha_terminacion,0 from futbol.jornada where numero=(select max(numero) from futbol.jornada);
Query OK, 1 row affected (0.14 sec)
Records: 1 Duplicates: 0 Warnings: 0

mysql> select * from jornada;
+--------+-----------+------+--------------+-------------------+-------------+
| numero | temporada | anio | fecha_inicio | fecha_terminacion | total_goles |
+--------+-----------+------+--------------+-------------------+-------------+
| 1 | Apertura | 2007 | 2007-09-30 | 2007-10-02 | 0 |
+--------+-----------+------+--------------+-------------------+-------------+
1 row in set (0.00 sec)

mysql> replace into encuentro (equipo_a,equipo_b,jornada,temporada,anio,goles_a,goles_b)
select equipo_a,equipo_b,jornada,"Apertura", year(curdate()),goles_a,goles_b from futbol.encuentro;
Query OK, 2 rows affected (0.22 sec)
Records: 2 Duplicates: 0 Warnings: 0

mysql> select * from encuentro;
+----------+----------+---------+-----------+------+---------+---------+
| equipo_a | equipo_b | jornada | temporada | anio | goles_a | goles_b |
+----------+----------+---------+-----------+------+---------+---------+
| franja | chivas | 1 | Apertura | 2007 | 2 | 1 |
| pumas | america | 1 | Apertura | 2007 | 3 | 3 |
+----------+----------+---------+-----------+------+---------+---------+
2 rows in set (0.00 sec)

mysql> create temporary table goles_jornada (total_goles int,jornada int);
Query OK, 0 rows affected (0.25 sec)

mysql> insert into goles_jornada select sum(goles_a)+sum(goles_b),jornada from futbol.encuentro group by jornada;
Query OK, 1 row affected (0.20 sec)
Records: 1 Duplicates: 0 Warnings: 0

mysql> select * from goles_jornada;
+-------------+---------+
| total_goles | jornada |
+-------------+---------+
| 9 | 1 |
+-------------+---------+
1 row in set (0.00 sec)

mysql> update jornada,goles_jornada set jornada.total_goles=goles_jornada.total_goles where jornada.numero=goles_jornada.jornada;
Query OK, 1 row affected (0.09 sec)
Rows matched: 1 Changed: 1 Warnings: 0

mysql> select * from jornada;
+--------+-----------+------+--------------+-------------------+-------------+
| numero | temporada | anio | fecha_inicio | fecha_terminacion | total_goles |
+--------+-----------+------+--------------+-------------------+-------------+
| 1 | Apertura | 2007 | 2007-09-30 | 2007-10-02 | 9 |
+--------+-----------+------+--------------+-------------------+-------------+
1 row in set (0.00 sec)

 

mysql> create temporary table goles_equipo (numgoles int,equipo char(10));
Query OK, 0 rows affected (0.33 sec)

mysql> insert into goles_equipo select sum(goles_a),equipo_a from encuentro group by equipo_a;
Query OK, 2 rows affected (0.08 sec)
Records: 2 Duplicates: 0 Warnings: 0

mysql> insert into goles_equipo select sum(goles_b),equipo_b from encuentro group by equipo_b;
Query OK, 2 rows affected (0.19 sec)
Records: 2 Duplicates: 0 Warnings: 0

mysql> create temporary table total_goles_equipo (numgoles int,equipo char(10));
Query OK, 0 rows affected (0.33 sec)

mysql> insert into total_goles_equipo select sum(numgoles),equipo from goles_equipo group by equipo;
Query OK, 4 rows affected (0.20 sec)
Records: 4 Duplicates: 0 Warnings: 0

mysql> select * from total_goles_equipo;
+----------+---------+
| numgoles | equipo |
+----------+---------+
| 3 | america |
| 1 | chivas |
| 2 | franja |
| 3 | pumas |
+----------+---------+
4 rows in set (0.00 sec)

mysql> update equipo,total_goles_equipo set equipo.total_goles=total_goles_equipo.numgoles where equipo.nombre=total_goles_equipo.equipo;
Query OK, 4 rows affected (0.16 sec)
Rows matched: 4 Changed: 4 Warnings: 0

mysql> select * from equipo;
+---------+-------------+
| nombre | total_goles |
+---------+-------------+
| america | 3 |
| chivas | 1 |
| franja | 2 |
| pumas | 3 |
+---------+-------------+
4 rows in set (0.00 sec)

mysql> update temporada set promedio_goles_jornada=(select avg(total_goles) from jornada where temporada="Apertura" and anio=year(curdate())) where nombre="Apertura" and anio=year(curdate());
Query OK, 1 row affected (0.19 sec)
Rows matched: 1 Changed: 1 Warnings: 0

mysql> select * from temporada;
+----------+------+--------------+-------------------+------------------------+
| nombre | anio | fecha_inicio | fecha_terminacion | promedio_goles_jornada |
+----------+------+--------------+-------------------+------------------------+
| Apertura | 2007 | 2007-09-05 | NULL | 9 |
+----------+------+--------------+-------------------+------------------------+
1 row in set (0.00 sec)

mysql>

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

## Se pasa al dw la jornada 2

mysql>
mysql> replace into jornada (numero,temporada, anio, fecha_inicio, fecha_terminacion, total_goles) select numero, "Apertura", year(curdate()), fecha_inicio,fecha_terminacion,0 from futbol.jornada where numero=(select max(numero) from futbol.jornada);
Query OK, 1 row affected (0.19 sec)
Records: 1 Duplicates: 0 Warnings: 0

mysql> select * from jornada;
+--------+-----------+------+--------------+-------------------+-------------+
| numero | temporada | anio | fecha_inicio | fecha_terminacion | total_goles |
+--------+-----------+------+--------------+-------------------+-------------+
| 1 | Apertura | 2007 | 2007-09-30 | 2007-10-02 | 9 |
| 2 | Apertura | 2007 | 2007-10-07 | 2007-10-10 | 0 |
+--------+-----------+------+--------------+-------------------+-------------+
2 rows in set (0.00 sec)

mysql> replace into encuentro (equipo_a,equipo_b,jornada,temporada,anio,goles_a,goles_b) select equipo_a,equipo_b,jornada,"Apertura", year(curdate()),goles_a,goles_b from futbol.encuentro;
Query OK, 6 rows affected (0.14 sec)
Records: 4 Duplicates: 2 Warnings: 0

mysql> select * from encuentro;
+----------+----------+---------+-----------+------+---------+---------+
| equipo_a | equipo_b | jornada | temporada | anio | goles_a | goles_b |
+----------+----------+---------+-----------+------+---------+---------+
| chivas | america | 2 | Apertura | 2007 | 1 | 2 |
| franja | chivas | 1 | Apertura | 2007 | 2 | 1 |
| pumas | america | 1 | Apertura | 2007 | 3 | 3 |
| pumas | franja | 2 | Apertura | 2007 | 1 | 1 |
+----------+----------+---------+-----------+------+---------+---------+
4 rows in set (0.00 sec)

mysql>

mysql> create temporary table goles_jornada (total_goles int,jornada int);
Query OK, 0 rows affected (0.25 sec)

mysql> update jornada,goles_jornada set jornada.total_goles=goles_jornada.total_goles where jornada.numero=goles_jornada.jornada;
Query OK, 1 row affected (0.36 sec)
Rows matched: 2 Changed: 1 Warnings: 0

mysql> select * from jornada;
+--------+-----------+------+--------------+-------------------+-------------+
| numero | temporada | anio | fecha_inicio | fecha_terminacion | total_goles |
+--------+-----------+------+--------------+-------------------+-------------+
| 1 | Apertura | 2007 | 2007-09-30 | 2007-10-02 | 9 |
| 2 | Apertura | 2007 | 2007-10-07 | 2007-10-10 | 5 |
+--------+-----------+------+--------------+-------------------+-------------+
2 rows in set (0.00 sec)

mysql>create temporary table goles_equipo (numgoles int,equipo char(10));

mysql> insert into goles_equipo select sum(goles_a),equipo_a from encuentro group by equipo_a;
Query OK, 3 rows affected (0.09 sec)
Records: 3 Duplicates: 0 Warnings: 0

mysql> insert into goles_equipo select sum(goles_b),equipo_b from encuentro group by equipo_b;
Query OK, 3 rows affected (0.05 sec)
Records: 3 Duplicates: 0 Warnings: 0

mysql> create temporary table total_goles_equipo (numgoles int,equipo char(10));

mysql> insert into total_goles_equipo select sum(numgoles),equipo from goles_equipo group by equipo;
Query OK, 4 rows affected (0.20 sec)
Records: 4 Duplicates: 0 Warnings: 0

mysql> select * from total_goles_equipo;
+----------+---------+
| numgoles | equipo |
+----------+---------+
| 5 | america |
| 2 | chivas |
| 3 | franja |
| 4 | pumas |
+----------+---------+
4 rows in set (0.00 sec)

mysql> update equipo,total_goles_equipo set equipo.total_goles=total_goles_equipo.numgoles where equipo.nombre=total_goles_equipo.equipo;
Query OK, 4 rows affected (0.11 sec)
Rows matched: 4 Changed: 4 Warnings: 0

mysql> select * from equipo;
+---------+-------------+
| nombre | total_goles |
+---------+-------------+
| america | 5 |
| chivas | 2 |
| franja | 3 |
| pumas | 4 |
+---------+-------------+
4 rows in set (0.00 sec)

mysql>
mysql>
mysql>
mysql> select avg(total_goles) from jornada where temporada="Apertura" and anio=year(curdate());
+------------------+
| avg(total_goles) |
+------------------+
| 7.0000 |
+------------------+
1 row in set (0.00 sec)

mysql> update temporada set promedio_goles_jornada=(select avg(total_goles) from jornada where temporada="Apertura" and anio=year(curdate())) where nombre="Apertura" and anio=year(curdate());
Query OK, 1 row affected (0.14 sec)
Rows matched: 1 Changed: 1 Warnings: 0

mysql> select * from temporada;
+----------+------+--------------+-------------------+------------------------+
| nombre | anio | fecha_inicio | fecha_terminacion | promedio_goles_jornada |
+----------+------+--------------+-------------------+------------------------+
| Apertura | 2007 | 2007-09-05 | NULL | 7 |
+----------+------+--------------+-------------------+------------------------+
1 row in set (0.00 sec)

mysql>

 

## Se cierra la temporada

mysql> update temporada set fecha_terminacion=curdate() where nombre="Apertura" and anio=year(curdate());
Query OK, 1 row affected (0.14 sec)
Rows matched: 1 Changed: 1 Warnings: 0

mysql> select * from temporada;
+----------+------+--------------+-------------------+------------------------+
| nombre | anio | fecha_inicio | fecha_terminacion | promedio_goles_jornada |
+----------+------+--------------+-------------------+------------------------+
| Apertura | 2007 | 2007-09-05 | 2007-10-11 | 7 |
+----------+------+--------------+-------------------+------------------------+
1 row in set (0.00 sec)