EJERCICIO SQL BIBLIOTECA Este ejercicio, de nivel básico, está pensado para iniciarse en la creación de bases de datos, tablas y las restricciones necesarias para garantizar la coherencia mediante la ejecución de consultas mediante lenguaje SQL.

10 de septiembre

2014 BASICO_001_BIBLIOTECA

EJERCICIO SQL BIBLIOTECA 2014 1. Índice de contenido 1. Índice de contenido ..............................................................................................................................2 2. Enunciado y ejercicios propuestos........................................................................................................2 2.1.1. Crear la base de datos Biblioteca............................................................................................3 2.1.2. Crear la tabla libros, con sus restricciones a nivel de tabla. ...................................................3 2.1.3. Crear la tabla usuarios, con sus restricciones a nivel de campo. ...........................................3 2.1.4. Crear la tabla préstamos, con sus restricciones a nivel de tabla. ...........................................3 2.1.5. Crear un script que cree la base de datos y las tablas en una sola consulta. Ejecutarla para comprobar que funciona. ......................................................................................................................3 3. Soluciones .............................................................................................................................................4 3.1.1. Crear la base de datos Biblioteca............................................................................................4 3.1.2. Crear la tabla libros, con sus restricciones a nivel de tabla. ...................................................4 3.1.3. Crear la tabla usuarios, con sus restricciones a nivel de campo. ...........................................4 3.1.4. Crear la tabla préstamos, con sus restricciones a nivel de tabla. ...........................................5 3.1.5. Crear un script que cree la base de datos y las tablas en una sola consulta. Ejecutarla para comprobar que funciona. ......................................................................................................................5 4. Sobre la licencia .................................................................................... ¡Error! Marcador no definido.

2. Enunciado y ejercicios propuestos El Director de un colegio público va a crear una biblioteca para prestar libros a los alumnos del centro. Debido a que quiere tener una base de datos sencilla, el diseño aprobado por la Dirección consta solamente de tres tablas. La tabla “Libros” almacena la información básica de los libros, la tabla “Usuarios” guarda la información personal de los usuarios de la biblioteca y la tabla “Préstamos” almacena los datos relativos a los libros prestados. Los campos de cada tabla y sus restricciones son los siguientes: NOMBRE DEL CAMPO codigolibro titulo editorial autor genero paisautor numeropaginas anoedicion precio

2 de 7

TABLA LIBROS TIPO DE CAMPO RESTRICCIONES Int Clave primaria Varchar (60) No nulo Varchar (25) Nulo Varchar (40) No nulo Varchar (20) No nulo Varchar (20) Nulo Int No nulo. No permitir números negativos o 0 Varchar (5) Nulo Money No nulo. No permitir números negativos o 0

Índice de contenido

EJERCICIO SQL BIBLIOTECA 2014 NOMBRE DEL CAMPO codigousuario nombre apellido1 apellido2 fechanacimiento dni domicilio población provincia

NOMBRE DEL CAMPO numeropedido librocodigo usuariocodigo fechaprestamo fechamaximadevolucion fechadevolucion

TABLA USUARIOS TIPO DE CAMPO RESTRICCIONES Int Clave primaria. Autonumérico Varchar (15) No nulo. Varchar (20) No nulo Varchar (20) Nulo Date No nulo Varchar (12) Nulo Varchar (50) No nulo Varchar (30) Nulo Varchar (20) Nulo

TABLA PRÉSTAMOS TIPO DE CAMPO RESTRICCIONES Int Autonumérico. Clave primaria Int Obtiene datos de la tabla libros. No nulo Int Obtiene los datos de la tabla usuarios. No nulo. Datetime2 No nulo. Valor por defecto: fecha actual Datetime2 No nulo. Campo calculado. Datetime2 No nulo.

Sabiendo que: a) La biblioteca solo tiene un ejemplar de cada libro. b) Que el período de préstamo de un libro es de 15 días naturales. c) Que, tanto el archivo de registros como el de transacciones, tienen un tamaño inicial de 5 MB, crecen de MB en M y tienen un tamaño máximo de 15 MB. d) El colegio tiene alumnos de cero a dieciséis años de edad. usando consultas SQL se pide: 2.1.1. Crear la base de datos Biblioteca. 2.1.2. Crear la tabla libros, con sus restricciones a nivel de tabla. 2.1.3. Crear la tabla usuarios, con sus restricciones a nivel de campo. 2.1.4. Crear la tabla préstamos, con sus restricciones y relaciones a nivel de tabla y campo. 2.1.5. Crear un script que cree la base de datos y las tablas en una sola consulta. Ejecutarla para comprobar que funciona.

3 de 7

Enunciado y ejercicios propuestos

EJERCICIO SQL BIBLIOTECA 2014 3. Soluciones 3.1.1. Crear la base de datos Biblioteca. /*Primero, creamos la base de datos, con un archivo principal y uno de registro de transaciones. ¡Atención a la ruta absoluta de los archivos físicos!*/ CREATE DATABASE basico_001_biblioteca ON PRIMARY (NAME='basico-001-biblioteca', FILENAME = 'C:\Program Files\Microsoft SQL Server\MSSQL12.FORMACION\MSSQL\DATA\basico-001biblioteca.mdf', SIZE = 5 MB, MAXSIZE = 15 MB, FILEGROWTH = 1 MB) log ON (NAME = 'basico-001-biblioteca_log', FILENAME ='C:\Program Files\Microsoft SQL Server\MSSQL12.FORMACION\MSSQL\DATA\basico-001biblioteca.ldf', SIZE = 5 MB, MAXSIZE = 15 MB, FILEGROWTH = 1 MB) COLLATE modern_spanish_ci_as

3.1.2. Crear la tabla libros, con sus restricciones a nivel de tabla. /*Creación de la tabla "libros" con las restricciones a nivel de tabla. El campo AnoEdicion almacena solo el año de la edición del libro. Por eso está definido como varchar. El campo codigolibro es el campo único que identifica cada registro*/ --Recuerda que la sintaxis en "nombre_campo" "tipo_campo",-USE basico_001_biblioteca CREATE TABLE libros (codigolibro INT, titulo VARCHAR(60) NOT NULL, editorial VARCHAR(25) NULL, autor VARCHAR(25) NOT NULL, genero VARCHAR (20) NOT NULL, paisautor VARCHAR(20) NULL, numeropaginas INT NOT NULL, anoedicion VARCHAR(5) NULL, precio MONEY NULL --Añadidas aquí, las restricciones se crean a nivel de tabla, no de campo---Para crear una clave primaria, definimos el campo-CONSTRAINT PK_libros PRIMARY KEY (codigolibro), --Para crear una restricción de tipo check, tenemos que definir el campo y la expresión que tiene que cumplir el campo-CONSTRAINT CK_libro_precio CHECK (precio > 0), CONSTRAINT CK_libro_pagina CHECK (numeropaginas >0))

3.1.3. Crear la tabla usuarios, con sus restricciones a nivel de campo. USE basico_001_biblioteca CREATE TABLE usuarios (codigousuario INT PRIMARY KEY IDENTITY (1,1), nombre VARCHAR(15) NOT NULL, apellido1 VARCHAR(20) NOT NULL, apellido2 VARCHAR(20) NULL, dni VARCHAR(12) NULL, domicilio VARCHAR (50) NOT NULL, poblacion VARCHAR(30) NOT NULL, provincia VARCHAR(20) NULL, fechanacimiento DATE NOT NULL)

4 de 7

www.formacion.jugarenequipo.es

Soluciones

EJERCICIO SQL BIBLIOTECA 2014 3.1.4. Crear la tabla préstamos, con sus restricciones y relaciones a nivel de tabla y campo. USE basico_001_biblioteca CREATE TABLE prestamos (numeropedido INT IDENTITY (1,1), librocodigo INT, usuariocodigo INT, --Los valores por defecto, se definen especificando la condición que tiene que cumplir cada campo-fechaprestamo DATETIME2 DEFAULT GETDATE(), fechamaximadevolucion DATETIME2 DEFAULT GETDATE()+15, fechadevolucion DATETIME2 NULL CONSTRAINT PK_prestamos PRIMARY KEY (numeropedido), CONSTRAINT FK_pre_lib FOREIGN KEY (librocodigo) REFERENCES libros (codigolibro) ON DELETE CASCADE ON UPDATE CASCADE, CONSTRAINT FK_pre_usu FOREIGN KEY (usuariocodigo) REFERENCES usuarios (codigousuario) ON DELETE CASCADE ON UPDATE CASCADE)

3.1.5. Crear un script que cree la base de datos y las tablas en una sola consulta. Ejecutarla para comprobar que funciona. /*Primero, creamos la base de datos, con un archivo principal y uno de registro de transaciones. ¡Atención a la ruta absoluta de los archivos físicos!*/ CREATE DATABASE basico_001_biblioteca ON PRIMARY (NAME='basico-001-biblioteca', FILENAME = 'C:\Program Files\Microsoft SQL Server\MSSQL12.FORMACION\MSSQL\DATA\basico-001biblioteca.mdf', SIZE = 5 MB, MAXSIZE = 15 MB, FILEGROWTH = 1 MB) log ON (NAME = 'basico-001-biblioteca_log', FILENAME ='C:\Program Files\Microsoft SQL Server\MSSQL12.FORMACION\MSSQL\DATA\basico-001biblioteca.ldf', SIZE = 5 MB, MAXSIZE = 15 MB, FILEGROWTH = 1 MB) COLLATE modern_spanish_ci_as GO /*Creación de la tabla "libros" con las restricciones a nivel de tabla. El campo AnoEdicion almacena solo el año de la edición del libro. Por eso está definido como VARCHAR. El campo códido libro es el campo único que identifica cada registro*/ --Recuerda que la sintaxis en "nombre_campo" "tipo_campo",-USE basico_001_biblioteca CREATE TABLE libros (codigolibro INT, titulo VARCHAR(60) NOT NULL, editorial VARCHAR(25) NULL, autor VARCHAR(25) NOT NULL, genero VARCHAR (20) NOT NULL, paisautor VARCHAR(20) NULL, numeropaginas INT NOT NULL, anoedicion VARCHAR(5) NULL, precio MONEY NULL --Añadidas aquí, las restricciones se crean a nivel de tabla, no de campo---Para crear una clave primaria, definimos el campo-CONSTRAINT PK_libros PRIMARY KEY (codigolibro), --Para crear una restricción de tipo check, tenemos que definir el campo y la expresión que tiene que cumplir el campo-CONSTRAINT CK_libro_precio CHECK (precio > 0), CONSTRAINT CK_libro_pagina CHECK (numeropaginas >0)) GO

5 de 7

www.formacion.jugarenequipo.es

Soluciones

EJERCICIO SQL BIBLIOTECA 2014 /*Creación de la tabla "usuarios" con las restricciones a nivel de campo */ USE basico_001_biblioteca CREATE TABLE usuarios (codigousuario INT PRIMARY KEY IDENTITY (1,1), nombre VARCHAR(15) NOT NULL, apellido1 VARCHAR(20) NOT NULL, apellido2 VARCHAR(20) NULL, dni VARCHAR(12) NULL, domicilio VARCHAR (50) NOT NULL, poblacion VARCHAR(30) NOT NULL, provincia VARCHAR(20) NULL, fechanacimiento DATE NOT NULL) GO /*Creación de la tabla préstamos con claves externas a nivel de campo*/ USE basico_001_biblioteca CREATE TABLE prestamos (numeropedido INT IDENTITY (1,1), librocodigo INT, usuariocodigo INT, --Los valores por defecto, se definen especificando la condición que tiene que cumplir cada campo-fechaprestamo DATETIME2 DEFAULT GETDATE(), fechamaximadevolucion DATETIME2 DEFAULT GETDATE()+15, fechadevolucion DATETIME2 NULL CONSTRAINT PK_prestamos PRIMARY KEY (numeropedido), CONSTRAINT FK_pre_lib FOREIGN KEY (librocodigo) REFERENCES libros (codigolibro) ON DELETE CASCADE ON UPDATE CASCADE, CONSTRAINT FK_pre_usu FOREIGN KEY (usuariocodigo) REFERENCES usuarios (codigousuario) ON DELETE CASCADE ON UPDATE CASCADE) GO

6 de 7

www.formacion.jugarenequipo.es

Soluciones

EJERCICIO SQL BIBLIOTECA 2014 4. Sobre la licencia

Este documento, como parte del sitio web www.formacion.jugarenequipo.es, está creado bajo licencia Creative Commons 4.0. Usted es libre: Compartir, distribuir, ejecutar y comunicar públicamente la obra. Hacer copias derivadas de esta obra. Bajo las condiciones siguientes:

Licencia Creative Commons: Creative Commons es una corporación sin ánimo de lucro basada en la idea de que algunas personas pueden no querer ejercer todos los derechos de propiedad intelectual que les permite la ley. Creemos que hay una demanda no satisfecha de un modo seguro que permita decir al mundo la frase ‘Algunos derechos reservados’ o incluso ‘Sin derechos reservados’.

Reconocimiento (Attribution): En cualquier explotación de la obra autorizada por la licencia hará falta reconocer la autoría.

No Comercial (Non commercial): La explotación de la obra queda limitada a usos no comerciales.

Compartir Igual (Share alike): La explotación autorizada incluye la creación de obras derivadas siempre que mantengan la misma licencia al ser divulgadas. Puede acceder al texto completo de la licencia en este enlace.

7 de 7

www.formacion.jugarenequipo.es

Sobre la licencia