EJERCICIO SQL 10 de septiembre

BIBLIOTECA 2014
Este ejercicio, de nivel básico, está pensado para iniciarse en la
BASICO_001_BIBLIOTECA
creación de bases de datos, tablas y las restricciones necesarias
para garantizar la coherencia mediante la ejecución de consultas
mediante lenguaje SQL.
 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:
TABLA LIBROS
NOMBRE DEL CAMPO TIPO DE CAMPO RESTRICCIONES
codigolibro Int Clave primaria
titulo Varchar (60) No nulo
editorial Varchar (25) Nulo
autor Varchar (40) No nulo
genero Varchar (20) No nulo
paisautor Varchar (20) Nulo
numeropaginas Int No nulo. No permitir números negativos o 0
anoedicion Varchar (5) Nulo
precio Money No nulo. No permitir números negativos o 0

2 de 7 Índice de contenido
 EJERCICIO SQL BIBLIOTECA 2014
TABLA USUARIOS
NOMBRE DEL CAMPO TIPO DE CAMPO RESTRICCIONES
codigousuario Int Clave primaria. Autonumérico
nombre Varchar (15) No nulo.
apellido1 Varchar (20) No nulo
apellido2 Varchar (20) Nulo
fechanacimiento Date No nulo
dni Varchar (12) Nulo
domicilio Varchar (50) No nulo
población Varchar (30) Nulo
provincia Varchar (20) Nulo

TABLA PRÉSTAMOS
NOMBRE DEL CAMPO TIPO DE CAMPO RESTRICCIONES
numeropedido Int Autonumérico. Clave primaria
librocodigo Int Obtiene datos de la tabla libros. No nulo
usuariocodigo Int Obtiene los datos de la tabla usuarios. No nulo.
fechaprestamo Datetime2 No nulo. Valor por defecto: fecha actual
fechamaximadevolucion Datetime2 No nulo. Campo calculado.
fechadevolucion 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-001-
biblioteca.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-001-
biblioteca.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-001-
biblioteca.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-001-
biblioteca.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