Tarea: Manipulación de datos “Biblioteca”

Jhon Michael Diaz Arango

LINKS VIDEOS:

Video parte 1: https://youtu.be/1n5sW-r6QCg

video parte 2: https://youtu.be/2XjES4C_YBk

/* punto 1 */

Insert into tblusuario values(8888,'Carlos','5555','515',1,'Vigente')

Insert into tblPertenece values(8888,1)

Insert into tblMaterial values('libro el Buen vendedor',30000,2013,1,1)

Insert into tblEjemplar values(8,'disponible')

Insert into tblReserva values(getdate(),8888,8)

/* punto 2 */

create table TBL_datos

(

Cod_datos int identity (1,1),

Fecha datetime default getdate() not null,

Cedula int not null,

Cod_Material int not null,

Estado varchar(100),

Primary key (Cod_datos),

Foreign Key (Cedula) references

tblUsuario (Cedula) on delete cascade on update cascade,

Foreign Key (Cod_Material) references

tblMaterial (Cod_Material) on delete cascade on update cascade

)

Insert into TBL_datos values(getdate(),8888,8,'Reserva activa')

select * from TBL_datos

/* punto 3*/

select distinct(tblMaterial.Cod_material), tblMaterial.Nombre_material, tblMaterial.Valor, tblMaterial.año, tblMaterial.CodTipo_Material, tblMaterial.cantidad

from tblmaterial, tblusuario,tblreserva,tbldevolucion,tblprestamo

where

tblreserva.Cedula = tblusuario.Cedula

and tblprestamo.Cod_Prestamo != tbldevolucion.Num_Prestamo

and (tblusuario.Cod_Tipo = '2' or tblusuario.Cod_Tipo = '3')

/* punto 4 */

select count(tblMaterial.Nombre_material) as Cantidad_Prestamos, tblMaterial.Nombre_material

from tblmaterial, tblprestamo

where

tblPrestamo.Cod_Material = tblMaterial.Cod_material

and 6 > 2.5

group by tblMaterial.Nombre_material

/* punto 5*/

select tblusuario.Cedula, tblusuario.Nombre, tblusuario.telefono, tblusuario.Direccion, tblusuario.Cod_Tipo, tblusuario.Estado_usuario

from tblusuario, tbldependencia

where tblusuario.Cod_Tipo = 1 and tblusuario.Cod_Tipo = 2 and tblusuario.Cod_Tipo = 3

and tblusuario.Cod_Tipo = 4 and tblusuario.Cod_Tipo = 5 and tblusuario.Cod_Tipo = 6

/* punto 6 */

Update tblEjemplar

set estado='reservado'

where Cod_Material=4

/* punto 7 */

Update tblMaterial

set Valor = 1 +(-5/100)

where tblMaterial.año < 2000

and tblMaterial.CodTipo_Material = 1

and tblMaterial.CodTipo_Material = 2

and tblMaterial.CodTipo_Material = 3

/* punto 8 */

Update tblusuario

set Estado_usuario='Vigente'

where Cod_Tipo=1

and Nombre = 'Carlos'

and Nombre = 'Camilo'

and Nombre = 'Camila'

/* punto 9 */

delete from tblReserva

where Cedula = 124

and Cedula = 127

and Cedula = 8888

/* punto 10 */

delete tblPrestamo from tblPrestamo, tblMaterial

where tblMaterial.CodTipo_Material = 1

Base de datos biblioteca

/*Creacion Base de Datos*/

create database DBBiblioteca2016

use DBBiblioteca2016

/*Crear tabla Tipo_usuario*/

create table tblTipo_Usuario

(

Cod_tipo int identity (1,1),

Nom_Tipo varchar(30) not null,

Primary Key (Cod_tipo)

)

/*Datos Para Tipo de usuario*/

Insert into tblTipo_Usuario values('Estudiante')

Insert into tblTipo_Usuario values('Profesor')

Insert into tblTipo_Usuario values('Directivo')

Insert into tblTipo_Usuario values('Empleado')

Select * from tblTipo_Usuario

/*Crear tabla usuario*/

create table tblusuario

(

Cedula int,

Nombre varchar(30)not null,

telefono int not null,

Direccion varchar(30) not null,

Cod_Tipo int not null,

Estado_usuario varchar(30) not null,

Primary Key (Cedula),

Foreign key (Cod_Tipo)references

tblTipo_Usuario (Cod_Tipo) on delete cascade on update cascade

)

/*Datos para tabla usuario*/

Insert into tblusuario values(123,'Daniela','9541','751',1,'Vigente')

Insert into tblusuario values(124,'Camilo','9542','752',2,'Betado')

Insert into tblusuario values(125,'Ramon','9543','753',3,'Vigente')

Insert into tblusuario values(126,'Daniel','9541','751',1,'Vigente')

Insert into tblusuario values(127,'Camila','9542','752',2,'Betado')

Insert into tblusuario values(128,'Clara','9543','753',3,'Vigente')

Insert into tblusuario values(129,'Daniel','9541','751',1,'Vigente')

Insert into tblusuario values(130,'Camila','9542','752',2,'Betado')

Insert into tblusuario values(131,'Ramiro','9543','753',3,'Vigente')

Select * from tblTipo_Usuario

/*Crear Tabla dependencia*/

Create table tbldependencia

(

Cod_Dependencia int identity (1,1),

Nombre_Dependencia varchar(30)not null,

Ubicacion varchar(30)not null,

Primary key (Cod_Dependencia)

)

/*Datos para Dependencia*/

Insert into tbldependencia values('Judicial','B1_103')

Insert into tbldependencia values('Sistemas','B5_203')

Insert into tbldependencia values('Administración','B3_106')

Insert into tbldependencia values('Educación','B3_107')

Insert into tbldependencia values('Agroambiental','B5_101')

Insert into tbldependencia values('electronica','B1_101')

/*Crear tabla Pertenece*/

create table tblPertenece

(

Cedula int,

Cod_Dependencia int not null,

Primary key (Cedula,Cod_Dependencia),

Foreign Key (Cedula) references

tblusuario (Cedula) on delete cascade on update cascade,

Foreign Key (Cod_Dependencia) references

tblDependencia (Cod_Dependencia)on delete cascade on update cascade

)

/*Datos para Pertenece*/

Insert into tblPertenece values(123,1)

Insert into tblPertenece values(124,2)

Insert into tblPertenece values(125,3)

Insert into tblPertenece values(126,1)

Insert into tblPertenece values(127,2)

Insert into tblPertenece values(128,3)

/*crear tabla Tipo Material*/

create table tblTipo_Material

(

CodTipo_Material int identity (1,1),

NombreTipo_Material varchar(30)not null,

CantidadTipo_Material int not null,

Primary key(CodTipo_Material),

)

Insert into tblTipo_Material values('Libro',5000)

Insert into tblTipo_Material values('Revistas',4000)

Insert into tblTipo_Material values('Periodico',3000)

Insert into tblTipo_Material values('Audiovisual',3000)

/*Crear tabla Material*/

create table tblMaterial

(

Cod_material int identity (1,1),

Nombre_material varchar (30) not null,

Valor int not null,

año int not null,

CodTipo_Material int not null,

check (Valor between 1000 and 200000),

check (año between 1930 and 2012),

cantidad int check (cantidad between 1 and 20),

Primary Key (Cod_material),

foreign key (CodTipo_Material) references

tblTipo_Material (CodTipo_Material) on delete cascade on update cascade

)

/*Crear Index Unico para Nombre del material */

create unique index Nombre_material on tblMaterial (Nombre_material)

/*Datos para Material*/

Insert into tblMaterial values('Fundamentos de Bases de datos',50000,1989,1,4)

Insert into tblMaterial values('Modelos de bases de datos',50000,1990,1,2)

Insert into tblMaterial values('Nueva tecnología',70000,1990,2,2)

Insert into tblMaterial values('colombiano',90000,2012,3,5)

Insert into tblMaterial values('Sql 2008',50000,1999,1,2)

Insert into tblMaterial values('Al día',70000,1990,2,2)

Insert into tblMaterial values('Matemáticas operativas',90000,1991,1,2)

/*Crear tabla Ejemplar*/

create table tblEjemplar

(

Num_Ejemplar int identity (1,1),

Cod_Material int,

estado varchar (30) not null,

check (estado ='Prestado'OR estado ='Disponible'OR estado ='En reparacion'OR estado ='Reservado'),

Primary key(Num_Ejemplar,Cod_Material),

Foreign key (Cod_Material) references

tblMaterial (Cod_Material)on delete cascade on update cascade

)

/*Datos para Ejemplar*/

Insert into tblEjemplar values(1,'disponible')

Insert into tblEjemplar values(2,'disponible')

Insert into tblEjemplar values(3,'disponible')

Insert into tblEjemplar values(1,'disponible')

Insert into tblEjemplar values(2,'disponible')

Insert into tblEjemplar values(3,'disponible')

/*Crear Tabla Prestamo*/

create table tblPrestamo

(

Cod_Prestamo int identity (1,1),

Fecha_Entrega datetime not null default getdate(),

Fecha_Devolucion datetime not null,

Cod_Material int not null,

Num_Ejemplar int not null,

Cedula int not null,

Primary Key(Cod_Prestamo),

Foreign Key (Num_Ejemplar,Cod_Material) references

tblEjemplar (Num_Ejemplar,Cod_Material) on delete cascade on update cascade,

Foreign Key (Cedula) references

tblUsuario (Cedula) on delete cascade on update cascade

)

/*Datos para Prestamo*/