Práctica DML sobre a base de datos de empresa téxtil El Desván

Eunciado

La empresa El Desván, que se dedica a la rama textil ha decidido informatizar su gestión de nóminas. Para ello BK programación desarrollará para ellos la base de datos.

Toma de requisitos

El gerente le ha explicado como funciona la gestión de nóminas y Juan, que será quien se encargue de crear el modelo, las tablas y las consultas, ha recogido la siguiente información:

• A cada empleado se le entrega un justificante de nómina al mes. De cada empleado registraremos su código de empleado, nombre, apellidos, número de hijos, cuenta corriente y porcentaje de retención para Hacienda.
• Un empleado puede trabajar en varios Departamentos y en cada uno de ellos realizará una función.
• De un Departamento mantenemos el nombre del mismo y un código de Departamento.
• Los datos de un justificante de nómina son el ingreso total percibido por el empleado y el descuento total aplicado.
• La distinción entre dos justificantes de nómina se hace, además de mediante el código de empleado, mediante el ejercicio fiscal y número de mes al que pertenece.
• Cada justificante de nómina consta de varias líneas y cada línea se identifica por un número de línea del correspondiente justificante. Una línea puede corresponder a un ingreso o a un descuento. En ambos casos se recoge la cantidad (positiva o negativa). En el caso de los descuentos se recoge la base y el porcentaje.

PD. En un momento posterior, el gerente se da cuenta de que también es necesario conocer la fecha de nacimiento de los empleados.

Modelo ER

Con todos estos datos ha llegado al siguiente modelo entidad-relación:

.

Relacional

Este es el grafo relacional:

Creacion de esquema - DDL

También se ha creado la base de datos, con sus tablas y sus correspondientes campos:

 1 create table Empleados (
 2 	codigo		integer(5),
 3 	nombre		varchar(30) not null,
 4 	hijos		integer(2)not null,
 5 	retencion	integer(2) not null,
 6 	cuenta		char(20) not null unique,
 7 	primary key (codigo));
 8 create table Departamentos (
 9 	codigo		integer(5),
10 	nombre		varchar(20) not null unique,
11 	primary key (codigo));
12 create table Trabajan (
13 	cod_emp		integer(5),
14 	cod_dep		integer(5),
15 	funcion		varchar(30) not null,
16 	primary key (cod_emp, cod_dep),
17 	foreign key (cod_emp) references Empleados(codigo),
18 	foreign key (cod_dep) references Departamentos(codigo));
19 create table Just_nominas (
20 	mes		integer(2),
21 	ejercicio	integer(4),
22 	ingreso		integer(8) not null,
23 	descuento	integer(8) not null,
24 	cod_emp		integer(8),
25 	primary key (mes, ejercicio, cod_emp),
26 	foreign key (cod_emp) references Empleados(codigo));
27 create table Lineas (
28 	numero	integer(5),
29 	cantidad	integer(8) not null,
30 	base		integer(8),
31 	porcentaje	integer(2),
32 	mes		integer(2),
33 	ejercicio	integer(4),
34 	cod_emp	integer(5),
35 	primary key (numero, mes, ejercicio, cod_emp),
36 	foreign key (mes, ejercicio, cod_emp) references Just_nominas(mes, ejercicio, cod_emp));

Inserción de datos - DML

Se han insertado los datos necesarios:

  1 insert into Empleados values (00011, 'Juan Ignacio Martinez', 0, 10, '12341234121234567890');
  2 insert into Empleados values (00001, 'José Luis Pérez', 2, 12, '12342233121122334455');
  3 insert into Empleados values (02341, 'Fernando Romero Días', 1, 8, '21341234560987654321');
  4 insert into Empleados values (11223, 'Manuel Lopez Marín', 0, 10, '55443322110099887766');
  5 insert into Empleados values (67890, 'Alfonso Gutierrez Lopez', 1, 12, '12563478001234567890');
  6 insert into Empleados values (00111, 'Encarna Lopez Lopez', 0, 10, '99118822773344665500');
  7 insert into Empleados values (02031, 'Ines Montero Zafra', 1, 8, '42341534129234567890');
  8 insert into Empleados values (09876, 'Rosa Lorite Lopez', 0, 10, '52341234521214567890');
  9 insert into Empleados values (96352, 'Lola Martinez Contreras', 1, 11, '22341224121224567820');
 10 insert into Empleados values (76543, 'Francisca Colate Gonzalez', 3, 7, '12343234121334567893');
 11 insert into Empleados values (73152, 'María Pascual Rojo', 3, 7, '12351234151234567590');
 12 insert into Empleados values (64738, 'Andrés Morales Martín', 3, 7, '22341154116231563690');
 13 
 14 insert into Departamentos values (00001, 'Ventas');
 15 insert into Departamentos values (00002, 'Compras');
 16 insert into Departamentos values (00003, 'Marketing');
 17 insert into Departamentos values (00004, 'Recursos Humanos');
 18 insert into Departamentos values (00005, 'Administración');
 19 insert into Departamentos values (00006, 'Dirección');
 20 
 21 insert into Trabajan values (00001, 00001, 'Vendedor');
 22 insert into Trabajan values (00001, 00003, 'Diseñador');
 23 insert into Trabajan values (02341, 00005, 'Administrativo');
 24 insert into Trabajan values (11223, 00006, 'Asesor Dirección');
 25 insert into Trabajan values (11223, 00005, 'Administrativo');
 26 insert into Trabajan values (11223, 00004, 'Selección de Personal');
 27 insert into Trabajan values (67890, 00002, 'Gestor de compras');
 28 insert into Trabajan values (00111, 00001, 'Vendedor');
 29 insert into Trabajan values (02031, 00001, 'Vendedor');
 30 insert into Trabajan values (09876, 00006, 'Director');
 31 insert into Trabajan values (96352, 00003, 'Publicista');
 32 insert into Trabajan values (96352, 00004, 'Encuestador');
 33 insert into Trabajan values (96352, 00005, 'Secretaria de Dirección');
 34 insert into Trabajan values (76543, 00001, 'Vendedor');
 35 insert into Trabajan values (73152, 00005, 'Administrativo');
 36 insert into Trabajan values (73152, 00003, 'Publicista');
 37 insert into Trabajan values (64738, 00001, 'Vendedor');
 38 insert into Trabajan values (64738, 00004, 'Selección de Personal');
 39 insert into Trabajan values (64738, 00002, 'Gestor de compras');
 40 insert into Trabajan values (64738, 00003, 'Diseñador');
 41 
 42 insert into Just_nominas values (10, 2006, 1200, 200, 00001);
 43 insert into Just_nominas values (11, 2006, 1200, 200, 00001);
 44 insert into Just_nominas values (12, 2006, 1200, 200, 00001);
 45 insert into Just_nominas values (01, 2007, 1200, 200, 00001);
 46 insert into Just_nominas values (10, 2006, 1500, 300, 02341);
 47 insert into Just_nominas values (11, 2006, 1500, 300, 02341);
 48 insert into Just_nominas values (12, 2006, 1500, 300, 02341);
 49 insert into Just_nominas values (01, 2007, 1500, 300, 02341);
 50 insert into Just_nominas values (10, 2006, 1000, 100, 11223);
 51 insert into Just_nominas values (11, 2006, 1000, 100, 11223);
 52 insert into Just_nominas values (12, 2006, 1000, 100, 11223);
 53 insert into Just_nominas values (01, 2007, 1000, 100, 11223);
 54 insert into Just_nominas values (10, 2006, 1200, 200, 67890);
 55 insert into Just_nominas values (11, 2006, 1200, 200, 67890);
 56 insert into Just_nominas values (12, 2006, 1200, 200, 67890);
 57 insert into Just_nominas values (01, 2007, 1200, 200, 67890);
 58 insert into Just_nominas values (10, 2006, 1200, 200, 00111);
 59 insert into Just_nominas values (11, 2006, 1200, 200, 00111);
 60 insert into Just_nominas values (12, 2006, 1200, 200, 00111);
 61 insert into Just_nominas values (01, 2007, 1200, 200, 00111);
 62 insert into Just_nominas values (10, 2006, 1200, 200, 02031);
 63 insert into Just_nominas values (11, 2006, 1200, 200, 02031);
 64 insert into Just_nominas values (12, 2006, 1200, 200, 02031);
 65 insert into Just_nominas values (01, 2007, 1200, 200, 02031);
 66 insert into Just_nominas values (10, 2006, 1200, 200, 09876);
 67 insert into Just_nominas values (11, 2006, 1200, 200, 09876);
 68 insert into Just_nominas values (12, 2006, 1200, 200, 09876);
 69 insert into Just_nominas values (01, 2007, 1200, 200, 09876);
 70 insert into Just_nominas values (10, 2006, 1200, 200, 96352);
 71 insert into Just_nominas values (11, 2006, 1200, 200, 96352);
 72 insert into Just_nominas values (12, 2006, 1200, 200, 96352);
 73 insert into Just_nominas values (01, 2007, 1200, 200, 96352);
 74 insert into Just_nominas values (10, 2006, 1200, 200, 76543);
 75 insert into Just_nominas values (11, 2006, 1200, 200, 76543);
 76 insert into Just_nominas values (12, 2006, 1200, 200, 76543);
 77 insert into Just_nominas values (01, 2007, 1200, 200, 76543);
 78 insert into Just_nominas values (10, 2006, 1200, 200, 73152);
 79 insert into Just_nominas values (11, 2006, 1200, 200, 73152);
 80 insert into Just_nominas values (12, 2006, 1200, 200, 73152);
 81 insert into Just_nominas values (01, 2007, 1200, 200, 73152);
 82 insert into Just_nominas values (10, 2006, 1200, 200, 64738);
 83 insert into Just_nominas values (11, 2006, 1200, 200, 64738);
 84 insert into Just_nominas values (12, 2006, 1200, 200, 64738);
 85 insert into Just_nominas values (01, 2007, 1200, 200, 64738);
 86 
 87 insert into Lineas values (00001, 1200, NULL, NULL, 10, 2006, 00001);
 88 insert into Lineas values (00002, -200, 1200, 10, 10, 2006, 00001);
 89 insert into Lineas values (00001, 1200, NULL, NULL, 11, 2006, 00001);
 90 insert into Lineas values (00002, -200, 1200, 10, 11, 2006, 00001);
 91 insert into Lineas values (00001, 1200, NULL, NULL, 12, 2006, 00001);
 92 insert into Lineas values (00002, -200, 1200, 10, 12, 2006, 00001);
 93 insert into Lineas values (00001, 1200, NULL, NULL, 01, 2007, 00001);
 94 insert into Lineas values (00002, -200, 1200, 10, 01, 2007, 00001);
 95 insert into Lineas values (00001, 1200, NULL, NULL, 10, 2006, 02341);
 96 insert into Lineas values (00002, -200, 1200, 10, 10, 2006, 02341);
 97 insert into Lineas values (00001, 1200, NULL, NULL, 11, 2006, 02341);
 98 insert into Lineas values (00002, -200, 1200, 10, 11, 2006, 02341);
 99 insert into Lineas values (00001, 1200, NULL, NULL, 12, 2006, 02341);
100 insert into Lineas values (00002, -200, 1200, 10, 12, 2006, 02341);
101 insert into Lineas values (00001, 1200, NULL, NULL, 01, 2007, 02341);
102 insert into Lineas values (00002, -200, 1200, 10, 01, 2007, 02341);
103 insert into Lineas values (00001, 1200, NULL, NULL, 10, 2006, 11223);
104 insert into Lineas values (00002, -200, 1200, 10, 10, 2006, 11223);
105 insert into Lineas values (00001, 1200, NULL, NULL, 11, 2006, 11223);
106 insert into Lineas values (00002, -200, 1200, 10, 11, 2006, 11223);
107 insert into Lineas values (00001, 1200, NULL, NULL, 12, 2006, 11223);
108 insert into Lineas values (00002, -200, 1200, 10, 12, 2006, 11223);
109 insert into Lineas values (00001, 1200, NULL, NULL, 01, 2007, 11223);
110 insert into Lineas values (00002, -200, 1200, 10, 01, 2007, 11223);
111 insert into Lineas values (00001, 1200, NULL, NULL, 10, 2006, 67890);
112 insert into Lineas values (00002, -200, 1200, 10, 10, 2006, 67890);
113 insert into Lineas values (00001, 1200, NULL, NULL, 11, 2006, 67890);
114 insert into Lineas values (00002, -200, 1200, 10, 11, 2006, 67890);
115 insert into Lineas values (00001, 1200, NULL, NULL, 12, 2006, 67890);
116 insert into Lineas values (00002, -200, 1200, 10, 12, 2006, 67890);
117 insert into Lineas values (00001, 1200, NULL, NULL, 01, 2007, 67890);
118 insert into Lineas values (00002, -200, 1200, 10, 01, 2007, 67890);
119 insert into Lineas values (00001, 1200, NULL, NULL, 10, 2006, 00111);
120 insert into Lineas values (00002, -200, 1200, 10, 10, 2006, 00111);
121 insert into Lineas values (00001, 1200, NULL, NULL, 11, 2006, 00111);
122 insert into Lineas values (00002, -200, 1200, 10, 11, 2006, 00111);
123 insert into Lineas values (00001, 1200, NULL, NULL, 12, 2006, 00111);
124 insert into Lineas values (00002, -200, 1200, 10, 12, 2006, 00111);
125 insert into Lineas values (00001, 1200, NULL, NULL, 01, 2007, 00111);
126 insert into Lineas values (00002, -200, 1200, 10, 01, 2007, 00111);
127 insert into Lineas values (00001, 1200, NULL, NULL, 10, 2006, 02031);
128 insert into Lineas values (00002, -200, 1200, 10, 10, 2006, 02031);
129 insert into Lineas values (00001, 1200, NULL, NULL, 11, 2006, 02031);
130 insert into Lineas values (00002, -200, 1200, 10, 11, 2006, 02031);
131 insert into Lineas values (00001, 1200, NULL, NULL, 12, 2006, 02031);
132 insert into Lineas values (00002, -200, 1200, 10, 12, 2006, 02031);
133 insert into Lineas values (00001, 1200, NULL, NULL, 01, 2007, 02031);
134 insert into Lineas values (00002, -200, 1200, 10, 01, 2007, 02031);
135 insert into Lineas values (00001, 1200, NULL, NULL, 10, 2006, 09876);
136 insert into Lineas values (00002, -200, 1200, 10, 10, 2006, 09876);
137 insert into Lineas values (00001, 1200, NULL, NULL, 11, 2006, 09876);
138 insert into Lineas values (00002, -200, 1200, 10, 11, 2006, 09876);
139 insert into Lineas values (00001, 1200, NULL, NULL, 12, 2006, 09876);
140 insert into Lineas values (00002, -200, 1200, 10, 12, 2006, 09876);
141 insert into Lineas values (00001, 1200, NULL, NULL, 01, 2007, 09876);
142 insert into Lineas values (00002, -200, 1200, 10, 01, 2007, 09876);
143 insert into Lineas values (00001, 1200, NULL, NULL, 10, 2006, 96352);
144 insert into Lineas values (00002, -200, 1200, 10, 10, 2006, 96352);
145 insert into Lineas values (00001, 1200, NULL, NULL, 11, 2006, 96352);
146 insert into Lineas values (00002, -200, 1200, 10, 11, 2006, 96352);
147 insert into Lineas values (00001, 1200, NULL, NULL, 12, 2006, 96352);
148 insert into Lineas values (00002, -200, 1200, 10, 12, 2006, 96352);
149 insert into Lineas values (00001, 1200, NULL, NULL, 01, 2007, 96352);
150 insert into Lineas values (00002, -200, 1200, 10, 01, 2007, 96352);
151 insert into Lineas values (00001, 1200, NULL, NULL, 10, 2006, 76543);
152 insert into Lineas values (00002, -200, 1200, 10, 10, 2006, 76543);
153 insert into Lineas values (00001, 1200, NULL, NULL, 11, 2006, 76543);
154 insert into Lineas values (00002, -200, 1200, 10, 11, 2006, 76543);
155 insert into Lineas values (00001, 1200, NULL, NULL, 12, 2006, 76543);
156 insert into Lineas values (00002, -200, 1200, 10, 12, 2006, 76543);
157 insert into Lineas values (00001, 1200, NULL, NULL, 01, 2007, 76543);
158 insert into Lineas values (00002, -200, 1200, 10, 01, 2007, 76543);
159 insert into Lineas values (00001, 1200, NULL, NULL, 10, 2006, 73152);
160 insert into Lineas values (00002, -200, 1200, 10, 10, 2006, 73152);
161 insert into Lineas values (00001, 1200, NULL, NULL, 11, 2006, 73152);
162 insert into Lineas values (00002, -200, 1200, 10, 11, 2006, 73152);
163 insert into Lineas values (00001, 1200, NULL, NULL, 12, 2006, 73152);
164 insert into Lineas values (00002, -200, 1200, 10, 12, 2006, 73152);
165 insert into Lineas values (00001, 1200, NULL, NULL, 01, 2007, 73152);
166 insert into Lineas values (00002, -200, 1200, 10, 01, 2007, 73152);
167 insert into Lineas values (00001, 1200, NULL, NULL, 10, 2006, 64738);
168 insert into Lineas values (00002, -200, 1200, 10, 10, 2006, 64738);
169 insert into Lineas values (00001, 1200, NULL, NULL, 11, 2006, 64738);
170 insert into Lineas values (00002, -200, 1200, 10, 11, 2006, 64738);
171 insert into Lineas values (00001, 1200, NULL, NULL, 12, 2006, 64738);
172 insert into Lineas values (00002, -200, 1200, 10, 12, 2006, 64738);
173 insert into Lineas values (00001, 1200, NULL, NULL, 01, 2007, 64738);
174 insert into Lineas values (00002, -200, 1200, 10, 01, 2007, 64738);
175 
176 -- alter table es DDL
177 alter table empleados add fnacimiento date;
178 
179 update empleados set fnacimiento = '1960-02-01' where codigo = 00011;
180 update empleados set fnacimiento = '1964-04-12' where codigo = 00001;
181 update empleados set fnacimiento = '1955-09-25' where codigo = 02341;
182 update empleados set fnacimiento = '1963-12-13' where codigo = 11223;
183 update empleados set fnacimiento = '1967-11-05' where codigo = 67890;
184 update empleados set fnacimiento = '1968-03-15' where codigo = 00111;
185 update empleados set fnacimiento = '1972-02-22' where codigo = 02031;
186 update empleados set fnacimiento = '1975-08-18' where codigo = 09876;
187 update empleados set fnacimiento = '1975-03-09' where codigo = 96352;
188 update empleados set fnacimiento = '1969-03-02' where codigo = 76543;
189 update empleados set fnacimiento = '1973-12-09' where codigo = 73152;
190 update empleados set fnacimiento = '1964-01-20' where codigo = 64738;

Consultas que necesitamos - DML

Todo está listo para que le ayudes. Estas son las consultas que debes crear:

1. Código y nombre de todos los departamentos.
2. Mes y ejercicio de los justificantes de nómina pertenecientes al empleado cuyo código es 1.
3. Código y nombre de los empleados ordenados ascendentemente por nombre.
4. Código y número de cuenta de los empleados cuyo nombre empieze por 'A' o por 'J'.
5. Número de empleados que hay en la base de datos.
6. Nombre y número de hijos de los empleados cuya retención es: 8, 10 o 12.
7. Número de hijos y número de empleados agrupados por hijos, mostrando sólo los grupos cuyo número de empleados sea mayor que 1.
8. Número de hijos, retención máxima, mínima y media de los empleados agrupados por hijos.
9. Nombre y función de los empleados que han trabajado en el departamento 1.
10. Nombre del empleado y nombre del departamento en el que han trabajado empleados que no tienen hijos.
11. Nombre del empleado, mes y ejercicio de sus justificantes de nómina, número de línea y cantidad de las líneas de los justificantes para el empleado cuyo código=1.
12. Nombre del empleado e ingresos totales percibidos agrupados por nombre.
13. Número de empleados cuyo número de hijos es superior a la media de hijos de los empleados.
14. Nombre de los empleados que más hijos tienen o que menos hijos tienen.
15. Nombre de los empleados que no tienen justificante de nóminas.
16. Nombre de los empleados, nombre de los departamentos en los que ha trabajado y función en mayúsculas que ha realizado en cada departamento.
17. Nombre, fecha de nacimiento y nombre del día de la semana de su fecha de nacimiento de todos los empleados.
18. Nombre y edad de los empleados.
19. Nombre, edad y número de hijos de los empleados que tienen menos de 40 años y tienen hijos.
20. Nombre e ingresos percibidos empleado más joven y del más longevo.

Créditos e referencias

• Práctica do banco de recursos de FP da Xunta de Galicia
• Modelo relacional realizado con ERDPlus

Solución

• Solución á práctica DML sobre a base de datos de empresa téxtil El Desván