Base de datos practicas5

Enunciado

A base de datos practicas5 está creada con fins pedagóxicos e didácticos para realizar exemplos de consultas.

Tarefa

Executa o seguinte script para mediante DDL xenerar o esquema da base de datos e con DML cargar con datos as diferentes táboas.

  1 -- MySQL Administrator dump 1.4
  2 --
  3 -- ------------------------------------------------------
  4 -- Server version	5.1.36-community-log
  5 
  6 
  7 /*!40101 SET @OLD_CHARACTER_SET_CLIENT=@@CHARACTER_SET_CLIENT */;
  8 /*!40101 SET @OLD_CHARACTER_SET_RESULTS=@@CHARACTER_SET_RESULTS */;
  9 /*!40101 SET @OLD_COLLATION_CONNECTION=@@COLLATION_CONNECTION */;
 10 /*!40101 SET NAMES utf8 */;
 11 
 12 /*!40014 SET @OLD_UNIQUE_CHECKS=@@UNIQUE_CHECKS, UNIQUE_CHECKS=0 */;
 13 /*!40014 SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0 */;
 14 /*!40101 SET @OLD_SQL_MODE=@@SQL_MODE, SQL_MODE='NO_AUTO_VALUE_ON_ZERO' */;
 15 
 16 
 17 --
 18 -- Create schema sql_dml_empleados
 19 --
 20 
 21 CREATE DATABASE IF NOT EXISTS practicas5;
 22 USE practicas5;
 23 
 24 
 25 CREATE TABLE `provincia` (
 26   `id_provincia` smallint(6) DEFAULT NULL,
 27   `provincia` varchar(30) DEFAULT NULL,
 28   PRIMARY KEY (`id_provincia`)
 29 ) ENGINE=InnoDB  DEFAULT CHARSET=latin1 COLLATE=latin1_spanish_ci;
 30 
 31 INSERT INTO `provincia` (`id_provincia`, `provincia`)
 32 VALUES
 33 	(2,'Albacete'),
 34 	(3,'Alicante/Alacant'),
 35 	(4,'Almería'),
 36 	(1,'Álava/Araba'),
 37 	(33,'Asturias'),
 38 	(5,'Ávila'),
 39 	(6,'Badajoz'),
 40 	(7,'Balears, Illes'),
 41 	(8,'Barcelona'),
 42 	(48,'Bizkaia'),
 43 	(9,'Burgos'),
 44 	(10,'Cáceres'),
 45 	(11,'Cádiz'),
 46 	(39,'Cantabria'),
 47 	(12,'Castellón/Castelló'),
 48 	(51,'Ceuta'),
 49 	(13,'Ciudad Real'),
 50 	(14,'Córdoba'),
 51 	(15,'Coruña, A'),
 52 	(16,'Cuenca'),
 53 	(20,'Gipuzkoa'),
 54 	(17,'Girona'),
 55 	(18,'Granada'),
 56 	(19,'Guadalajara'),
 57 	(21,'Huelva'),
 58 	(22,'Huesca'),
 59 	(23,'Jaén'),
 60 	(24,'León'),
 61 	(27,'Lugo'),
 62 	(25,'Lleida'),
 63 	(28,'Madrid'),
 64 	(29,'Málaga'),
 65 	(52,'Melilla'),
 66 	(30,'Murcia'),
 67 	(31,'Navarra'),
 68 	(32,'Ourense'),
 69 	(34,'Palencia'),
 70 	(35,'Palmas, Las'),
 71 	(36,'Pontevedra'),
 72 	(26,'Rioja, La'),
 73 	(37,'Salamanca'),
 74 	(38,'Santa Cruz de Tenerife'),
 75 	(40,'Segovia'),
 76 	(41,'Sevilla'),
 77 	(42,'Soria'),
 78 	(43,'Tarragona'),
 79 	(44,'Teruel'),
 80 	(45,'Toledo'),
 81 	(46,'Valencia/València'),
 82 	(47,'Valladolid'),
 83 	(49,'Zamora'),
 84 	(50,'Zaragoza');
 85 
 86 
 87 
 88 --
 89 -- Definition of table `departamento`
 90 --
 91 
 92 DROP TABLE IF EXISTS `departamento`;
 93 CREATE TABLE `departamento` (
 94   `codigo` tinyint(3) unsigned NOT NULL AUTO_INCREMENT,
 95   `nome` char(25) COLLATE latin1_spanish_ci DEFAULT NULL,
 96   `tipo` enum('H','B','A') NOT NULL,
 97   `cidade` varchar(35) COLLATE latin1_spanish_ci DEFAULT NULL,
 98   `id_provincia` smallint(6) DEFAULT NULL,
 99   PRIMARY KEY (`codigo`),
100    CONSTRAINT `fk_departamento_provincia` FOREIGN KEY (`id_provincia`) REFERENCES `provincia` (`id_provincia`) ON DELETE NO ACTION ON UPDATE NO ACTION
101 ) ENGINE=InnoDB  DEFAULT CHARSET=latin1 COLLATE=latin1_spanish_ci;
102 
103 --
104 -- Dumping data for table `departamento`
105 --
106 
107 /*!40000 ALTER TABLE `departamento` DISABLE KEYS */;
108 INSERT INTO `departamento` (`codigo`,`nome`,`tipo`,`cidade`,`id_provincia`) VALUES 
109  (1,'Central','H','Lugo',27),
110  (2,'Oficina1','H','Monforte',27),
111  (3,'Oficina2','B','Ferrol',15),
112  (4,'Oficina3','H','Vigo',36),
113  (5,'Oficina4','A','Ourense',32),
114  (6,'Oficina5','A','Villalba',27),
115  (7,'Oficina6','H','Ourense',32),
116  (8,'Oficina7','H','Lugo',27),
117  (9,'Oficina8','A','Coruña',15),
118  (10,'Oficina9','B','Villalba',28);
119 /*!40000 ALTER TABLE `departamento` ENABLE KEYS */;
120 
121 
122 --
123 -- Definition of table `empregado`
124 --
125 
126 DROP TABLE IF EXISTS `empregado`;
127 CREATE TABLE `empregado` (
128   `dni` int(11) NOT NULL,
129   `apelidos` varchar(45) COLLATE latin1_spanish_ci NOT NULL,
130   `nome` varchar(30) COLLATE latin1_spanish_ci NOT NULL,
131   `departamento` tinyint(3) unsigned DEFAULT NULL,
132   `salario_bruto` decimal(9,2) unsigned DEFAULT NULL,
133   `dni_xefe` int(11) DEFAULT NULL,
134   PRIMARY KEY (`dni`),
135   KEY `fk_empregado_departamento` (`departamento`),
136   KEY `fk_empregado_empregado1` (`dni_xefe`),
137   CONSTRAINT `fk_empregado_departamento` FOREIGN KEY (`departamento`) REFERENCES `departamento` (`codigo`) ON DELETE NO ACTION ON UPDATE NO ACTION,
138   CONSTRAINT `fk_empregado_empregado1` FOREIGN KEY (`dni_xefe`) REFERENCES `empregado` (`dni`) ON DELETE NO ACTION ON UPDATE NO ACTION
139 ) ENGINE=InnoDB DEFAULT CHARSET=latin1 COLLATE=latin1_spanish_ci;
140 
141 --
142 -- Dumping data for table `empregado`
143 --
144 
145 /*!40000 ALTER TABLE `empregado` DISABLE KEYS */;
146 INSERT INTO `empregado` (`dni`,`apelidos`,`nome`,`departamento`,`salario_bruto`,`dni_xefe`) VALUES 
147  (12549563,'Case Rodriguez','Fernanda',4,'35520.00',54528788),
148  (12852654,'Martinez Iglesias','Benito',1,'25000.00',33456852),
149  (15245258,'Nuñez Bernardez','Antonia',2,'42000.00',33987456),
150  (33123456,'Fernandez Lopez','Jose Luis',1,'160000.00',NULL),
151  (33147258,'Ruiz Macias','Dario',10,'32000.00',33456852),
152  (33219853,'Hernandez Valin','Valentina',3,'26000.00',33963258),
153  (33251256,'Martinez Diaz','Carlos',2,'31500.00',33987456),
154  (33254916,'Iglesias Dominguez','Adolfo',NULL,'52500.00',33123456),
155  (33322541,'Villar Bernal','Teolindo',5,'17500.00',58745124),
156  (33257964,'Villar Bernal','Rosario',4,'25000.00',54528788),
157  (33365846,'Quiroga Juarez','Francisco',3,'36500.00',33963258),
158  (33456852,'Fernandez Diaz','Julian',1,'85400.00',33123456),
159  (33558994,'Abelleira Carrion','Dorinda',6,'19500.00',58745124),
160  (33625566,'Garcia Perez','Adrian',5,'21500.00',58745124),
161  (33845215,'Sanchez Rodirguez','Maria',3,'35400.00',33963258),
162  (33891564,'Aguiar Lopez','Luis',9,'35000.00',54528788);
163 INSERT INTO `empregado` (`dni`,`apelidos`,`nome`,`departamento`,`salario_bruto`,`dni_xefe`) VALUES 
164  (33963258,'Bernardez Macia','Luisa',8,'65200.00',33456852),
165  (33987456,'Porto Novo','Begoña',9,'52000.00',33456852),
166  (54528788,'Canedo Tellez','Angeles',4,'58500.00',33456852),
167  (58745124,'Cendan Villa','Lorenzo',7,'65350.00',33456852);
168 /*!40000 ALTER TABLE `empregado` ENABLE KEYS */;
169 
170 
171 --
172 -- Definition of table `irpf`
173 --
174 
175 DROP TABLE IF EXISTS `irpf`;
176 CREATE TABLE `irpf` (
177   `limite_inferior` decimal(10,2) unsigned NOT NULL,
178   `limite_superior` decimal(10,2) unsigned NOT NULL,
179   `tipo_imposto` decimal(4,2) NOT NULL,
180   PRIMARY KEY (`tipo_imposto`)
181 ) ENGINE=InnoDB DEFAULT CHARSET=latin1 COLLATE=latin1_spanish_ci;
182 
183 --
184 -- Dumping data for table `irpf`
185 --
186 
187 /*!40000 ALTER TABLE `irpf` DISABLE KEYS */;
188 INSERT INTO `irpf` (`limite_inferior`,`limite_superior`,`tipo_imposto`) VALUES 
189  ('0.00','17707.00','15.75'),
190  ('17707.00','33007.00','21.00'),
191  ('33007.00','53407.00','27.00'),
192  ('53407.00','120000.00','30.00'),
193  ('120000.00','175000.00','35.00'),
194  ('175000.00','300000.00','42.00'),
195  ('300000.00','99999999.99','55.00');
196 /*!40000 ALTER TABLE `irpf` ENABLE KEYS */;
197 
198 
199 /*!40101 SET SQL_MODE=@OLD_SQL_MODE */;
200 /*!40014 SET FOREIGN_KEY_CHECKS=@OLD_FOREIGN_KEY_CHECKS */;
201 /*!40014 SET UNIQUE_CHECKS=@OLD_UNIQUE_CHECKS */;
202 /*!40101 SET CHARACTER_SET_CLIENT=@OLD_CHARACTER_SET_CLIENT */;
203 /*!40101 SET CHARACTER_SET_RESULTS=@OLD_CHARACTER_SET_RESULTS */;
204 /*!40101 SET COLLATION_CONNECTION=@OLD_COLLATION_CONNECTION */;
205 /*!40101 SET CHARACTER_SET_CLIENT=@OLD_CHARACTER_SET_CLIENT */;

Diagrama E-R

As táboas desta base de datos móstranse no seguinte diagrama entidade relación deseñado con MySQL Workbench.

Descrición

Descríbense a continuación as táboas.

Táboa empregado

A columna departamento é unha clave foránea que contén o código do departamento no que traballa o empregado, e fai referencia á columna codigo da táboa departamento.

Os valores que toma a columna departamento teñen que coincidir cos que toma a columna codigo da táboa departamento, ou ser NULL no caso que o empregado non teña asignado ningún departamento.

A columna dni_xefe é outra clave foránea que contén o dni doutro empregado que sería o seu xefe, ou o valor NULL no caso que non tivera xefe.

Táboa departamento

A columna id_provincia é unha clave foránea que fai referencia á columna id_provincia da táboa provincia.

Táboa irpf

Contén a porcentaxe de imposto que hai que aplicarlle a cada empregado, en función do seu salario bruto, dependendo dos límites entre os que se atope.