View
228
Download
4
Embed Size (px)
Citation preview
ADMINISTRACIÓN DE BASE DE DATOS - I
Material de Trabajo Elaborado por:
Ing. Oscar Alberto Barnett Contreras
Implementación de una Base de Datos SQL SERVER 2008
UNIVERSIDAD NACIONAL MAYOR DE SAN MARCOS
Facultad de Ingeniería Industrial - CEUPS Informática
Curso: Administración de Base de Datos - I
Docente: Ing. Oscar Alberto Barnett Contreras – [email protected] Página 2 de 72
Capítulo I - PRINCIPIOS DE LAS BASE DE DATOS
RELACIONALES
Modelo Lógico
Bajo el enfoque E/R, desde el punto de vista lógico (conceptual)
existen los siguientes conceptos básicos:
Entidad.- Representa una abstracción de la realidad con existencia independiente, es decir, que se diferencia unívocamente de cualquier otro
objeto o cosa que sea, incluso, del mismo tipo. Puede ser de tipo concreto
(tangible) como una persona, un documento, un objeto, un edificio; ó
puede ser abstracta (conceptual) como una transacción bancaria, un curso,
una cuenta de aportes individual, un proceso de matrícula, la asistencia al
trabajo de un personal, etc.
Ejemplo de entidades concretas:
EMPLEADO, ALUMNO, SOCIO, CLIENTE, etc.
BOLETA_PAGO, CARNÉ, CUENTA_INDIVIDUAL, RECETA_ MEDICA.
COMPUTADOR, LIBRO, PUESTO_COMERCIAL, MEDICAMENTO, etc.
OFICINA, AULA, COOPERATIVA, FARMACIA, etc.
Ejemplo de entidades abstractas:
ASISTENCIA, MATRICULA, APORTE, VENTA, etc.
RESERVA_VUELO, ALQUILER_VIDEO, COMPRA_VENTA_MONEDA, etc.
Conjunto de entidades.- Es una colección de entidades que comparten los
mismos atributos o características (¡pero no los mismos valores en esos atributos!).
Ejemplos:
Todos los empleados que laboran en una empresa, comparten sus
atributos: Nombre, Número de Seguro Social, Edad, Cargo...
Todas las oficinas sucursales de una empresa, comparten los atributos: Ciudad, Director, Área, Región…
Todas las boletas de pago de los empleados, comparten sus
atributos: Nro_Boleta, Período_Pago, Descuento, Bonificación,
MontoBruto…
Todos los productos de un almacén, comparten los atributos: Código
de Familia, Código de Producto, Precio Unitario, Existencias…
Atributo.- Representa una característica significativa de la Entidad que
puede valorarse. Una entidad dentro de un conjunto de entidades, tiene
valores específicos asignados para cada uno de sus atributos, de esta
forma, es posible su identificación unívoca.
UNIVERSIDAD NACIONAL MAYOR DE SAN MARCOS
Facultad de Ingeniería Industrial - CEUPS Informática
Curso: Administración de Base de Datos - I
Docente: Ing. Oscar Alberto Barnett Contreras – [email protected] Página 3 de 72
Ejemplo:
A la colección de entidades EMPLEADO, con el siguiente conjunto de
atributos en común (Num_Empl, Nombre, Edad, Cuota, CUSPP), pertenecen
las entidades:
(1, Oscar Barnett , 50, 14000, 123881OBCNT1)
(2, Judith Jiménez, 30, 15000, 265341JJMNG1) (3, Blanca Yaya , 40, 15000, 157183BYGMZ2)
(4, William León , 50, 19000, 114768WLVLQ3)
...
Cada una de las entidades pertenecientes a este conjunto se diferencia de
las demás por el valor de sus atributos. Nótese que dos o más entidades
diferentes pueden tener los mismos valores en algunos atributos, pero nunca en todos a la vez.
Atributos identificativos.- Son aquellos que permiten diferenciar a una
instancia de la entidad de otra distinta. Por ejemplo, el atributo
identificativo que distingue a un empleado de otro es su número de
empleado: Num_Empl. También podría ser el atributo CUSPP.
Dominio de atributo.- Hace referencia al tipo de dato que será
almacenado o a restricciones en los valores que el atributo puede tomar
(Cadenas de caracteres, números, solo dos letras, solo números mayores
que cero, solo números enteros...).
Indicador NULO (NULL).- Se usa cuando una entidad no tiene valor para un atributo dado, bien sea porque el valor no se conoce, no existe o no se
sabe nada respecto del mismo.
Ejemplo de atributos de diversas entidades (identificadores en negrita):
CLIENTE: Num_Clie, Nombre, Estado, RUC, etc.
SOCIO: ID_Socio, Apellido, Nombre, Giro, Nro_Puesto, etc. ORDEN_COMPRA: Num_Orden, Fecha_Orden, Fecha_Entrega,
Importe, etc.
MEDICAMENTO: Codigo, Nombre, Nro_Lote, Fecha_Vencimiento, etc.
OFICINA: ID_Oficina, Ciudad, Region, Objetivo, etc.
ASISTENCIA: Num_Empl, Hora_Ingreso, Hora_Salida, etc.
MATRICULA: ID, Fecha, ID_Alumno, Grado, Seccion, Turno.
PRODUCTO: ID_Fab, ID_Producto, Nombre, Precio, Existencia, etc. APORTE: ID_Socio, Fecha, Importe, Observacion, etc.
Relación.- Describe cierta dependencia entre entidades o permite la
asociación de las mismas.
Ejemplo:
Dadas dos entidades "Oficina.11" y "Empleado.Oscar Barnett", es posible relacionar que en la oficina 11 se encuentra laborando el empleado
de nombre Oscar Barnett.
Una Relación tiene sentido al expresar las entidades que relaciona.
En el ejemplo anterior, un EMPLEADO (entidad), labora en (relación) una
OFICINA (entidad).
UNIVERSIDAD NACIONAL MAYOR DE SAN MARCOS
Facultad de Ingeniería Industrial - CEUPS Informática
Curso: Administración de Base de Datos - I
Docente: Ing. Oscar Alberto Barnett Contreras – [email protected] Página 4 de 72
Conjunto de Relaciones.- Consiste en una colección, o conjunto, de
relaciones de la misma naturaleza.
Ejemplo:
Dados los conjuntos de entidades "OFICINA" y "EMPLEADO", todas
las relaciones de la forma OFICINA-EMPLEADO, permiten obtener la
información de las oficinas y sus respectivos empleados.
La dependencia o asociación entre los conjuntos de entidades es llamada
PARTICIPACIÓN. En el ejemplo anterior los conjuntos de entidades
"OFICINA" y "EMPLEADO" participan en el conjunto de relaciones OFICINA-
EMPLEADO.
Se llama GRADO del conjunto de relaciones a la cantidad de conjuntos de
entidades participantes en la relación.
Restricciones.- Son reglas que deben mantener los datos almacenados en
la base de datos, con el objetivo de mantener la coherencia y consistencia.
Cardinalidad.- Dado un conjunto de relaciones en el que participan dos o
más conjuntos de entidades, la correspondencia de cardinalidad indica el
número de entidades con las que puede estar relacionada una entidad dada. Dado un conjunto de relaciones binarias y los conjuntos de entidades A y B,
la correspondencia de cardinalidades puede ser:
Uno a uno: Una entidad de A se relaciona únicamente con una
entidad en B y viceversa.
Uno a varios: Una entidad en A se relaciona con cero o muchas
entidades en B. Pero una entidad en B se relaciona con una única entidad en A.
Varios a varios: Una entidad en A se puede relacionar con 0 o
muchas entidades en B y viceversa.
Claves.- Es un subconjunto del conjunto de atributos comunes en una
colección de entidades, que permite identificar unívocamente cada una de
las entidades pertenecientes a dicha colección. Asimismo, permiten distinguir entre sí las relaciones de un conjunto de relaciones.
Dentro de los conjuntos de entidades existen los siguientes tipos de claves:
Superclave: Es un subconjunto de atributos que permite distinguir
unívocamente cada una de las entidades de un conjunto de
entidades. Si se añade un atributo al anterior subconjunto, el
resultado seguirá siendo una superclave.
Clave candidata: Dada una superclave, si ésta deja de serlo quitando únicamente uno de los atributos que la componen, entonces
ésta es una clave candidata.
Clave primaria: Es una clave candidata, elegida por el diseñador de
la base de datos, para identificar unívocamente las entidades en un
conjunto de entidades.
Los valores de los atributos de una clave, no pueden ser todos iguales para dos o más entidades.
Conjunto de Relaciones R
Para poder distinguir unívocamente las relaciones en un conjunto de
relaciones R, se deben considerar dos casos:
UNIVERSIDAD NACIONAL MAYOR DE SAN MARCOS
Facultad de Ingeniería Industrial - CEUPS Informática
Curso: Administración de Base de Datos - I
Docente: Ing. Oscar Alberto Barnett Contreras – [email protected] Página 5 de 72
R NO tiene atributos asociados: En este caso, se usa como clave
primaria de R la unión de las claves primarias de todos los conjuntos
de entidades participantes.
R tiene atributos asociados: En este caso, se usa como clave
primaria de R la unión de los atributos asociados y las claves
primarias de todos los conjuntos de entidades participantes.
Si el conjunto de relaciones, R, sobre las que se pretende determinar la clave primaria está compuesto de relaciones binarias, con los conjuntos de
entidades participantes A y B, se consideran los siguientes casos, según sus
cardinalidades:
R es de uno a uno de A a B entonces se toma cualquiera de las dos
claves primarias (A ó B), como clave primaria de R.
R es de uno a muchos de A a B entonces se toma sólo la clave primaria del lado uno, como clave primaria de R.
R es de muchos a muchos de A a B entonces se toma la unión de
los atributos que conforman las claves primarias de A y de B, como
clave primaria de R.
Modelo Físico
Desde el punto de vista físico (implementación), una base de datos estará constituida, entre otros elementos por:
Tabla.- Es la implementación de un conjunto de entidades.
Ejemplos:
Tabla CLIENTE
Tabla SOCIO
Tabla ORDENES Tabla MEDICAMENTO
Tabla OFICINA
Tabla MATRICULA
Columna.- Es la implementación de los atributos de un conjunto de
entidades. Ejemplos en diversos conjuntos de entidades:
CLIENTE : (ID_Cliente, Nombre, Estado)
SOCIO : (Num_Socio, Apellidos, Nombre, Giro, Nro_Puesto)
ORDENES : (ID_Orden, Fecha, Importe)
MEDICAMENTO: (Codigo, Nombre, Nro_Lote, Fecha_Vencimiento)
OFICINA : (Oficina, Ciudad, Region, Objetivo)
MATRICULA : (ID, Fecha, Id_Alumno, Grado, Sección, Turno)
Fila.- Denominada también REGISTRO es la implementación de una
entidad específica (instancia de entidad).
Ejemplos de fila en diversas tablas:
CLIENTE : (21, “BARNETT”, “CA”) SOCIO : (“001”, “BARNETT”, “OSCAR”, “INFORMÁTICA”, 35)
ORDENES : (2301, 2/23/08, 104.75)
MEDICAMENTO: (101, SALBUTAMOL, “001435”, 12/31/11)
UNIVERSIDAD NACIONAL MAYOR DE SAN MARCOS
Facultad de Ingeniería Industrial - CEUPS Informática
Curso: Administración de Base de Datos - I
Docente: Ing. Oscar Alberto Barnett Contreras – [email protected] Página 6 de 72
OFICINA : (11, “Atlanta”, “Este”, 650000)
MATRICULA : (1, 02/25/10, “A175”, “6to”, “A”, “DIURNO”)
Por otro lado, existen conceptos de funcionalidad que se asignan a las
columnas de una tabla, con la finalidad de permitir un manejo eficiente de
los datos almacenados en ellas:
Clave Principal.- Denominada también “Clave primaria (PK)”. Es aquella
columna cuyo valor es único y obligatorio para cada fila de la tabla. Una
tabla sólo puede tener una clave principal.
Ejemplo: En la tabla ORDENES_DE_COMPRA, la columna ID_ORDEN (Nro.
de la Orden de Compra). Ejemplo: En la tabla CLIENTES, la columna ID_CLIENTE (código asignado al
cliente).
Clave Alterna.- Denominada también “Clave Candidata (AK)”. Es aquella
columna cuyo valor es único y obligatorio para cada fila de la tabla. Es
similar a la clave principal y puede ser empleada como tal, si fuera
necesario.
Ejemplo: En la tabla CLIENTES, la columna RUC (código de contribuyente
del cliente).
Ejemplo: En la tabla EMPLEADOS, la columna CUSPP (código AFP) del
empleado.
Clave Externa.- Denominada también “Clave Foránea (FK)”. Es aquella
columna cuyo valor corresponde con el dominio (conjunto de valores) de la
clave principal de otra tabla (denominada tabla “padre”). Esta clave permite
relacionar las filas de dos tablas (la tabla “padre” y la tabla “hijo”).
Ejemplo: Entre las tablas CLIENTES (“padre”) y ORDENES (“hijo”), se puede establecer una relación mediante las columnas
CLIENTES.ID_CLIENTE y ORDENES.ID_CLIENTE.
Ejemplo: Entre las tablas EMPLEADOS (“padre”) y CLIENTES (“hijo”), se
puede establecer la relación mediante las columnas
EMPLEADOS.ID_EMPLEADO y CLIENTES.ID_EMPLEADO.
Clave de Índice.- denominada también “Entrada Inversa (IR)”. Es aquella
columna cuyos valores serán usados para permitir eficiencia en
ordenamientos virtuales de las filas en la tabla ó también para el acceso
rápido a dichas filas.
Ejemplo: En la tabla EMPLEADOS, un índice en la columna Apellidos puede permitir ordenar alfabéticamente las filas de datos, para una
consulta o un listado impreso.
Ejemplo: En la tabla ORDENES, un índice en la columna Fecha, permitiría
ubicar rápidamente las filas de datos que correspondan a una
cierta fecha.
UNIVERSIDAD NACIONAL MAYOR DE SAN MARCOS
Facultad de Ingeniería Industrial - CEUPS Informática
Curso: Administración de Base de Datos - I
Docente: Ing. Oscar Alberto Barnett Contreras – [email protected] Página 7 de 72
Proceso de Normalización
La tarea de un Diseñador de Base de Datos consiste en estructurar
los datos de forma que se eliminen duplicaciones innecesarias y se
proporcione una búsqueda rápida para la información necesaria. El proceso
de perfeccionar tablas, columnas, claves y relaciones para crear una base
de datos eficaz se denomina Normalización.
Es un proceso complejo formado por reglas específicas y distintos niveles de intensidad. La definición completa de Normalización es: “El
proceso de descartar la repetición de grupos, minimizar la
redundancia, eliminar claves compuestas para la dependencia
parcial y separar los atributos que no sean de la clave”. En términos
generales, las reglas de normalización se pueden resumir en una sola frase:
"Cada atributo (columna) debe ser una realidad de la clave, toda la clave y
nada más que la clave".
Cada tabla debe describir sólo un tipo de entidad (como una persona, un
lugar, un pedido de cliente o un producto).
Grados de normalización
Existen básicamente tres niveles de normalización: Primera Forma
Normal (1NF), Segunda Forma Normal (2NF) y Tercera Forma Normal
(3NF). Cada una de estas formas tiene sus propias reglas. Cuando una base de datos se conforma a un nivel, se considera
normalizada a esa forma de normalización. No siempre es una buena
idea tener una base de datos conformada en el nivel más alto de
normalización, puede llevar a un nivel de complejidad que pudiera ser
evitado si estuviera en un nivel más bajo de normalización.
En la tabla siguiente se describe brevemente en qué consiste cada una de las reglas, y posteriormente se explican con más detalle.
Regla Descripción
Primera Forma Normal (1FN) Incluye la eliminación de todos los
grupos repetidos.
Segunda Forma Normal (2FN)
Asegura que todas las columnas que
no son clave sean completamente
dependientes de la clave primaria
(PK).
Tercera Forma Normal (3FN)
Elimina cualquier dependencia
transitiva. Una dependencia
transitiva es aquella en la cual las
columnas que no son clave son dependientes de otras columnas que
tampoco son clave.
UNIVERSIDAD NACIONAL MAYOR DE SAN MARCOS
Facultad de Ingeniería Industrial - CEUPS Informática
Curso: Administración de Base de Datos - I
Docente: Ing. Oscar Alberto Barnett Contreras – [email protected] Página 8 de 72
Dependencia Funcional (DF)
La idea de Dependencia Funcional como una RESTRICCIÓN es
intuitivamente atrayente. Por ejemplo, parece sensato decir que su Sueldo
depende de su Rendimiento o que su Modo de ser depende de su
Educación. La dependencia puede ser de dos formas, una se refiere a los
atributos de una misma entidad y la otra se refiere a las asociaciones entre
entidades. Cuando modelamos, el problema de la elección de las entidades se
origina en el hecho de que el valor de un atributo de la entidad puede
determinar completamente el valor de otros atributos. Tales dependencias
nos obligan a evitar determinadas agrupaciones de atributos.
Definición Un atributo B tiene dependencia funcional de otro atributo A si, cada
vez que se repite un valor de A; B necesariamente toma el mismo valor. Se representa A B y se lee “A define a B”
En otras palabras si se conoce el valor de A entonces, el valor de B
queda determinado.
Ejemplo: En la entidad EMPLEADO, el valor del Apellido (B) es dependiente
funcional del valor de Código (A).
Ejemplo: En la entidad FACTURA, el valor del Importe (B) es dependiente
funcional del valor de Nro_Factura (A).
Ejemplo: En la entidad SOCIO, el valor del Giro (B) es dependiente
funcional del valor de ID_SOCIO (A).
Ejemplo: En la entidad CLIENTE, el valor de Dirección (B) es dependiente
funcional del valor de RUC (A).
Ejemplo: En la entidad EMPLEADO, el valor de Remuneracion (B) es
dependiente funcional del valor de COD_SEGURO (A).
Definición
Cuando una entidad tiene una clave primaria compuesta (por 2 o
más atributos) y los demás atributos dependen de la clave como conjunto y
no tan solo de alguno de los atributos de la clave, se dice que el atributo tiene dependencia funcional completa (DFC) de la clave primaria
compuesta.
Ejemplo: En la entidad DETALLE_FACTURA, el valor de Cantidad (B) es
dependiente funcional del valor de la clave principal (PK)
compuesta Nro_Orden+Código_Producto (A).
Ejemplo: En la entidad TRANSACCION_CUENTA, el valor de Fecha (B) es dependiente funcional del valor de la clave principal (PK)
compuesta Nro_Cuenta+Nro_Transacción (A).
Ejemplo: En la entidad PRODUCTO, el valor de PrecioUnitario (B) es
dependiente funcional del valor de la clave principal (PK)
compuesta CódigoFamilia+CódigoProducto (A).
UNIVERSIDAD NACIONAL MAYOR DE SAN MARCOS
Facultad de Ingeniería Industrial - CEUPS Informática
Curso: Administración de Base de Datos - I
Docente: Ing. Oscar Alberto Barnett Contreras – [email protected] Página 9 de 72
Ejemplo: En la entidad PAQUETE_CONFECCION, el valor de Cantidad (B) es
dependiente funcional del valor de la clave principal (PK)
compuesta Nro_OrdenTrabajo+Código_Paquete (A).
Dependencia Transitiva
Si existe una DF de A en B (“A define a B”) y de B en C (“B define a
C”), entonces, existe una dependencia funcional de A en C (“A define a C”)
que se llamará Dependencia Transitiva y se representa así:
Si A B y B C, entonces A C
Ejemplo: En la entidad FACTURA, si el valor de Direccion_Cliente (C)
depende del valor de Codigo_Cliente (B), el valor de
Codigo_Cliente (B) depende del valor de Nro_Factura (A); y
además el valor de Dirección_Cliente (C) depende del valor de
Nro_Factura (A).
Entonces existe Dependencia Transitiva de Dirección_Cliente
respecto a Nro_Factura, por intermedio de Codigo_Cliente.
Aplicando La Normalización
Primera Forma Normal
La regla de la Primera Forma Normal establece que las columnas
repetidas deben eliminarse y colocarse en tablas separadas. Procesar la base de datos según la Primera Forma Normal resuelve el problema de los
encabezados de columna múltiples.
Posiblemente para almacenar datos de Órdenes de Compra, los
diseñadores de bases de datos inexpertos crearán algo similar a la siguiente
tabla:
ID_ORDEN FECHA ID_CLIENTE NOM_CLIENTE ESTADO NUM_ITEM DESC_ITEM CANT PRECIO
2301 23/2/10 101 BARNETT CA 3786 RED 3 35.00
2301 23/2/10 101 BARNETT CA 4011 RAQUETA 6 65.00
2301 23/2/10 101 BARNETT CA 9132 PAQ-3 8 4.75
2302 25/2/10 107 JIMENEZ WI 5794 PAQ-6 4 5.00
2303 27/2/10 110 YAYA MI 4011 RAQUETA 2 65.00
2303 27/2/10 110 YAYA MI 3141 FUNDA 2 10.00
Una y otra vez, crearán columnas que representen los mismos
datos. La Normalización ayuda a clarificar la base de datos y a organizarla en partes más pequeñas y más fáciles de entender. En lugar de tener que
entender una tabla gigantesca y monolítica que tiene diferentes aspectos,
sólo tenemos que entender los objetos pequeños y más tangibles, así como
las relaciones que guardan con otros objetos también pequeños.
Segunda Forma Normal
La regla de la Segunda Forma Normal establece que todas las dependencias funcionales parciales se deben eliminar y separar dentro de
sus propias tablas. Una dependencia funcional parcial es un término que
describe a aquellos datos que no dependen de toda la clave primaria
compuesta de la tabla para ser identificados.
Una vez alcanzado el nivel de la Segunda Forma Normal, se
controlan la mayoría de los problemas de lógica. Podemos insertar un
registro sin un exceso de datos en la mayoría de las tablas.
UNIVERSIDAD NACIONAL MAYOR DE SAN MARCOS
Facultad de Ingeniería Industrial - CEUPS Informática
Curso: Administración de Base de Datos - I
Docente: Ing. Oscar Alberto Barnett Contreras – [email protected] Página 10 de 72
Tercera Forma Normal
Una tabla está normalizada en esta forma si todas las columnas que
no son clave son funcionalmente dependientes por completo de la clave
primaria y no hay dependencias funcionales transitivas. Como ya se explicó
anteriormente, una dependencia transitiva es aquella en la cual existen
columnas que no son clave que dependen de otras columnas que tampoco
son clave.
Cuando las tablas están en la Tercera Forma Normal se previenen
errores de lógica cuando se insertan o borran registros. Cada columna en
una tabla está identificada de manera única por la clave primaria, y no debe
haber datos repetidos. Esto provee un esquema limpio y elegante, que es
fácil de trabajar y expandir.
Ejemplo
Un dato sin normalizar no cumple con ninguna regla de normalización. Para
explicar con un ejemplo en qué consiste cada una de las reglas, vamos a
considerar los datos de la siguiente tabla.
ORDENES
ID_ORDEN FECHA ID_CLIENTE NOM_CLIENTE ESTADO NUM_ITEM DESC_ITEM CANT PRECIO
2301 23/2/10 101 BARNETT CA 3786 RED 3 35.00
2301 23/2/10 101 BARNETT CA 4011 RAQUETA 6 65.00
2301 23/2/10 101 BARNETT CA 9132 PAQ-3 8 4.75
2302 25/2/10 107 JIMENEZ WI 5794 PAQ-6 4 5.00
2303 27/2/10 110 YAYA MI 4011 RAQUETA 2 65.00
2303 27/2/10 110 YAYA MI 3141 FUNDA 2 10.00
Al examinar estos registros, podemos darnos cuenta que contienen un
grupo repetido para NUM_ITEM, DESC_ITEM, CANT y PRECIO. La 1FN prohíbe los grupos repetidos, por lo tanto tenemos que convertir a la
primera forma normal. Los pasos a seguir son:
1. Tenemos que eliminar los grupos repetidos.
2. Tenemos que crear una nueva tabla con la PK de la tabla base y el
grupo repetido.
Los registros quedan ahora conformados en dos tablas que llamaremos ORDENES y ARTICULOS_ORDENES
- ORDENES
ID_ORDEN FECHA ID_CLIENTE NOM_CLIENTE ESTADO
2301 23/2/10 101 BARNETT CA
2302 25/2/10 107 JIMENEZ WI
2303 27/2/10 110 YAYA MI
- ARTICULOS_ORDENES
ID_ORDEN NUM_ITEM DESC_ITEM CANT PRECIO
2301 3786 RED 3 35.00
2301 4011 RAQUETA 6 65.00
2301 9132 PAQ-3 8 4.75
2302 5794 PAQ-6 4 5.00
2303 4011 RAQUETA 2 65.00
2303 3141 FUNDA 2 10.00
UNIVERSIDAD NACIONAL MAYOR DE SAN MARCOS
Facultad de Ingeniería Industrial - CEUPS Informática
Curso: Administración de Base de Datos - I
Docente: Ing. Oscar Alberto Barnett Contreras – [email protected] Página 11 de 72
Ahora procederemos a aplicar la segunda formal normal, es decir,
tenemos que eliminar cualquier columna no clave que no dependa de la
clave primaria de la tabla. Los pasos a seguir son:
1. Determinar cuáles columnas que no son clave no dependen de la clave
primaria de la tabla.
2. Eliminar esas columnas de la tabla base.
3. Crear una segunda tabla con esas columnas y la(s) columna(s) de la PK de la cual dependen.
La tabla ORDENES está en 2FN. Cualquier valor único de ID_ORDEN
determina un sólo valor para cada columna. Por lo tanto, todas las
columnas son dependientes de la clave primaria ID_ORDEN.
Por su parte, la tabla ARTICULOS_ORDENES no se encuentra en 2FN
ya que las columnas PRECIO y DESC_ITEM son dependientes de NUM_ITEM, pero no son dependientes de ID_ORDEN. Lo que hacemos a continuación es
eliminar estas columnas de la tabla ARTICULOS_ORDENES y crear una tabla
ARTICULOS con dichas columnas y la clave primaria de la que dependen.
Las tablas quedan ahora de la siguiente manera.
- ARTICULOS_ORDENES ID_ORDEN NUM_ITEM CANT
2301 3786 3
2301 4011 6
2301 9132 8
2302 5794 4
2303 4011 2
2303 3141 2
- ARTICULOS NUM_ITEM DESC_ITEM PRECIO
3786 RED 35.00
4011 RAQUETA 65.00
9132 PAQ-3 4.75
5794 PAQ-6 5.00
4011 RAQUETA 65.00
3141 FUNDA 10.00
La tercera forma normal nos dice que tenemos que eliminar
cualquier columna no clave que sea dependiente de otra columna no clave.
Los pasos a seguir son:
1. Determinar las columnas que son dependientes de otra columna no
clave. 2. Eliminar esas columnas de la tabla base.
3. Crear una segunda tabla con esas columnas y con la columna no clave
de la cual son dependientes.
Al observar las tablas que hemos creado, nos damos cuenta que
tanto la tabla ARTICULOS, como la tabla ARTICULOS_ORDENES se
encuentran en 3FN. Sin embargo la tabla ORDENES no lo está, ya que
NOM_CLIENTE y ESTADO son dependientes de ID_CLIENTE, y esta columna no es la clave primaria.
Para normalizar esta tabla, moveremos las columnas no clave y la
columna clave de la cual dependen dentro de una nueva tabla CLIENTES.
Las nuevas tablas CLIENTES y ORDENES se muestran a continuación.
UNIVERSIDAD NACIONAL MAYOR DE SAN MARCOS
Facultad de Ingeniería Industrial - CEUPS Informática
Curso: Administración de Base de Datos - I
Docente: Ing. Oscar Alberto Barnett Contreras – [email protected] Página 12 de 72
- ORDENES ID_ORDEN FECHA ID_CLIENTE
2301 23/2/10 101
2302 25/2/10 107
2303 27/2/10 110
- CLIENTES
ID_CLIENTE NOM_CLIENTE ESTADO
101 BARNETT CA
107 JIMENEZ WI
110 YAYA MI
Práctica de Normalización
Partiendo del siguiente archivo plano no normalizado, debe realizar el
proceso de normalización para una pequeña biblioteca:
CodLibro Titulo Autor Editorial NombreLector FechaDev
1001 Oracle 11g Oscar Barnett UNMSM Pérez Gómez, Juan 15/04/2010
1004 SPSS V17 Blanca Yaya MACRO Ríos Terán, Ana 17/04/2010
1005 SQL Server 2010 Oscar Barnett UNMSM Roca, René 16/04/2010
1006 Oracle 11g Judith Jiménez Oracle Corp. García Roque, Luis 20/04/2010
1007 VB NET 2010 Julio Córdova UNMSM Pérez Gómez, Juan 18/04/2010
UNIVERSIDAD NACIONAL MAYOR DE SAN MARCOS
Facultad de Ingeniería Industrial - CEUPS Informática
Curso: Administración de Base de Datos - I
Docente: Ing. Oscar Alberto Barnett Contreras – [email protected] Página 13 de 72
Uso Del CASE Erwin Para Diseñar Base De Datos
AllFusion Erwin 7 es una herramienta CASE (Ingeniería de Información Asistida por Computadora), que permite modelar información de
manera inteligente. Las siglas Erwin significan Entidad Relación para Windows.
Básicamente con Erwin podemos modelar, refinar, transformar, gestionar y documentar plataformas contenedoras de datos.
Para poder ingresar al software debemos realizar lo siguiente:
UNIVERSIDAD NACIONAL MAYOR DE SAN MARCOS
Facultad de Ingeniería Industrial - CEUPS Informática
Curso: Administración de Base de Datos - I
Docente: Ing. Oscar Alberto Barnett Contreras – [email protected] Página 14 de 72
La interface principal del CASE Erwin se muestra a continuación:
Barra de Titulo
Menú Principal Toolbar estándar
Panel de Registro Panel de Avisos
Ventana de Diagrama
Explorador de Objetos
UNIVERSIDAD NACIONAL MAYOR DE SAN MARCOS
Facultad de Ingeniería Industrial - CEUPS Informática
Curso: Administración de Base de Datos - I
Docente: Ing. Oscar Alberto Barnett Contreras – [email protected] Página 15 de 72
Diseño de Entidades
En el Toolbox de Erwin se debe hacer clic sobre el icono Entity y luego en el área en blanco. Aparecerá un objeto que representa un conjunto de
entidades y donde se digita el nombre del mismo, tal como se muestra a continuación:
A continuación se debe digitar sus correspondientes
atributos. La PK se inserta en el primer recuadro, los
atributos no claves en el recuadro inferior, como se muestra en la imagen del lado:
Posteriormente se puede editar el objeto entidad para establecer propiedades de
cada elemento, tal como se muestra en la imagen del lado:
Entidad
UNIVERSIDAD NACIONAL MAYOR DE SAN MARCOS
Facultad de Ingeniería Industrial - CEUPS Informática
Curso: Administración de Base de Datos - I
Docente: Ing. Oscar Alberto Barnett Contreras – [email protected] Página 16 de 72
Entonces se muestra la siguiente ventana donde se puede establecer el
dominio, las restricciones y el icono de cada atributo:
Para establecer el tipo de letra,
tamaño, color, color de línea y color de relleno para la entidad se debe hacer clic con
el botón derecho sobre el objeto entidad y seleccionar Object Font & Color como se
muestra:
Restricciones
Dominio
Icono
UNIVERSIDAD NACIONAL MAYOR DE SAN MARCOS
Facultad de Ingeniería Industrial - CEUPS Informática
Curso: Administración de Base de Datos - I
Docente: Ing. Oscar Alberto Barnett Contreras – [email protected] Página 17 de 72
Relacionar Entidades
Para relacionar dos o más entidades debemos de ubicar en el Erwin Toolbox los
botones de relación (RelationShip) como se muestra al lado:
Identifying relationship, relación de identificación.
Many-To-Many relationship, relación muchos a muchos.
Non-Identifying relationship, relación de no identificación.
Para poder establecer la relación entre dos entidades se debe hacer
clic sobre el botón de relación pertinente, un siguiente clic en la tabla padre y luego un clic en la tabla hijo, por ejemplo:
Avanzando con el ejemplo, se tiene definido el modelo lógico:
UNIVERSIDAD NACIONAL MAYOR DE SAN MARCOS
Facultad de Ingeniería Industrial - CEUPS Informática
Curso: Administración de Base de Datos - I
Docente: Ing. Oscar Alberto Barnett Contreras – [email protected] Página 18 de 72
Para establecer el nombre del diagrama del modelo, sobre el área en blanco debemos hacer clic
derecho, seleccionar la opción Stored Display Settings… como se muestra en las imágenes:
Luego de establecer los parámetros deseados en las fichas de la
ventana anterior, se pulsa el botón OK. En el ejemplo se muestra el diagrama con el nombre establecido como ‘BarnettLogico’:
UNIVERSIDAD NACIONAL MAYOR DE SAN MARCOS
Facultad de Ingeniería Industrial - CEUPS Informática
Curso: Administración de Base de Datos - I
Docente: Ing. Oscar Alberto Barnett Contreras – [email protected] Página 19 de 72
La opción New sirve para adicionar una nueva ficha y la opción
Rename sirve para renombrar la ficha.
En el ejemplo, se ha agregado una ficha Display2 y se ha renombrado como ‘BarnettFísico’, tal como se muestra a continuación:
Para poder establecer y o modificar la cardinalidad de las tablas
relacionadas se hace clic en el Menú Model \ RelationShip…, o clic derecho
en la línea de relación, seleccionar RelationShip Properties y aparece la
ventana que se muestra a continuación:
UNIVERSIDAD NACIONAL MAYOR DE SAN MARCOS
Facultad de Ingeniería Industrial - CEUPS Informática
Curso: Administración de Base de Datos - I
Docente: Ing. Oscar Alberto Barnett Contreras – [email protected] Página 20 de 72
Notación Lógica y Física
Primero se debe hacer clic sobre el Menú Model \ Model Properties,
clic sobre la ficha Notation encontramos tres metodologías:
Integration Definition for Information Modeling IDEF1X, Definición
de Integración para un Modelo de Información.
Information Engineering (IE), Ingeniería de la Información.
UNIVERSIDAD NACIONAL MAYOR DE SAN MARCOS
Facultad de Ingeniería Industrial - CEUPS Informática
Curso: Administración de Base de Datos - I
Docente: Ing. Oscar Alberto Barnett Contreras – [email protected] Página 21 de 72
Dimencional Modeling (DM), Modelo Dimensional.
Propiedades de un objeto Entidad
En el modelo Lógico, para establecer la definición de un objeto
entidad se debe hacer clic derecho sobre el objeto y seleccionar la opción
Entity Properties… tal como se
muestra:
Luego aparecerá la siguiente ventana:
En la ficha Definition
se puede digitar una definición asociada a la
entidad, de tal manera que cada tabla podría tener una
definición pre-establecida.
UNIVERSIDAD NACIONAL MAYOR DE SAN MARCOS
Facultad de Ingeniería Industrial - CEUPS Informática
Curso: Administración de Base de Datos - I
Docente: Ing. Oscar Alberto Barnett Contreras – [email protected] Página 22 de 72
Para establecer un Icono a un determinado objeto entidad se puede
hacer clic derecho sobre el objeto, seleccionar la opción Entity Properties… y luego la ficha Icon tal como se muestra:
Niveles de Visualización de las Entidades
En el Menú Format \ Display Level, se encuentran las opciones de niveles de visualización:
Entity.- Muestra solo los títulos de los objetos entidad.
Atribute.- Muestra los objetos entidad con sus atributos.
Se debe hacer click sobre el boton Import
para importar o seleccionar un Icono
UNIVERSIDAD NACIONAL MAYOR DE SAN MARCOS
Facultad de Ingeniería Industrial - CEUPS Informática
Curso: Administración de Base de Datos - I
Docente: Ing. Oscar Alberto Barnett Contreras – [email protected] Página 23 de 72
Primary Key.- Muestra los objetos entidad con sus Claves Primarias y
Foráneas (Externas).
Definition.- Muestra las definiciones de los objetos entidad.
Icon.- Muestra los iconos de los objetos entidad
UNIVERSIDAD NACIONAL MAYOR DE SAN MARCOS
Facultad de Ingeniería Industrial - CEUPS Informática
Curso: Administración de Base de Datos - I
Docente: Ing. Oscar Alberto Barnett Contreras – [email protected] Página 24 de 72
INSTALACIÓN BÁSICA DE UNA INSTANCIA SQL
SERVER 2008
Luego de haber decidido la Edición a instalar y de verificar los
requerimientos de Hardware y Software de acuerdo a lo indicado en el Web Site de Microsoft, inserte el DVD de instalación en la Unidad Lectora.
A continuación se mostrará, en forma sintetizada, la secuencia de instalación para la Edición Estándar de SQL Server 2008 (Inglés) en una
plataforma Windows Server 2003 Release 2, dentro de una red LAN sin dominio:
1. Se carga SQL Server Installation Center, con la ficha Planning donde debe revisar la información acerca de requerimientos para la instalación,
recomendaciones de seguridad y
realizar un chequeo de la
configuración del sistema. Haga
clic en el link
“System Configuration
Checker”
2. Verificar que todo
es correcto y
pulsar el botón OK para retornar
al Installation Center.
UNIVERSIDAD NACIONAL MAYOR DE SAN MARCOS
Facultad de Ingeniería Industrial - CEUPS Informática
Curso: Administración de Base de Datos - I
Docente: Ing. Oscar Alberto Barnett Contreras – [email protected] Página 25 de 72
3. Haga clic en la ficha
Installation, luego en el link New SQL
Server stand-alone Installation, para
iniciar el proceso.
4. Aguarde unos segundos mientras se completa la inicialización.
5. Se inicia una
comprobación de
errores para el proceso de copia de
los archivos de soporte. No debe
ocurrir ninguna falla para poder
continuar. Al terminar pulsar el
botón OK.
UNIVERSIDAD NACIONAL MAYOR DE SAN MARCOS
Facultad de Ingeniería Industrial - CEUPS Informática
Curso: Administración de Base de Datos - I
Docente: Ing. Oscar Alberto Barnett Contreras – [email protected] Página 26 de 72
6. Debe digitar la clave
del producto y pulsar
el botón Next >
7. Ahora acepte los
términos de licencia, activando el
CheckBox I accept
the licence terms. Pulsar botón Next >
8. En el cuadro de
instalación de archivos de soporte,
pulsar el botón Install.
UNIVERSIDAD NACIONAL MAYOR DE SAN MARCOS
Facultad de Ingeniería Industrial - CEUPS Informática
Curso: Administración de Base de Datos - I
Docente: Ing. Oscar Alberto Barnett Contreras – [email protected] Página 27 de 72
9. Debe seleccionar las características de
SQL server 2008 que desea instalar.
Asegúrese de instalar
los servicios que en algún momento vaya
a utilizar, si está totalmente seguro
que no va a usar un servicio específico
cómo Analysis
Services, limpie la casilla de verificación
junto a él, y pulsar el botón Next >
10. Determine si la instancia que va instalar es una instancia por default o
nombrada, en el segundo caso tendrá
que asignar a ésta un nombre con el
cual la reconocerá en el futuro; si la
instancia es por
default, la forma de conectarse a ésta
desde servidores o equipos clientes
remotos, será por medio del Nombre
de la PC o de la dirección IP de la
misma. Pulsar el
botón Next >
11. Verifique si tiene el espacio libre en el
disco donde se instalará el producto,
luego pulsar en el
botón Next >
UNIVERSIDAD NACIONAL MAYOR DE SAN MARCOS
Facultad de Ingeniería Industrial - CEUPS Informática
Curso: Administración de Base de Datos - I
Docente: Ing. Oscar Alberto Barnett Contreras – [email protected] Página 28 de 72
12. Configurar las cuentas con las cuales se ejecutará el servicio; la
recomendación es utilizar diferentes cuentas, sin embargo, en la imagen puede observar cómo
una cuenta es utilizada para ejecutar más de un
servicio, con el botón
intermedio podría seleccionar la opción
para utilizar la misma cuenta para todos los
servicios, en cuyo caso solamente tendrá que
escribir credenciales una vez, pero no estará
cumpliendo con buenas
prácticas de seguridad. Después de configurar
las cuentas pulsar en la pestaña Collation.
13. En Collation, observe
los métodos de
ordenamiento que van a ser utilizados tanto para
SQL Server cómo para Analysis Services; es
importante que tenga un cuenta si existen
regulaciones en su organización acerca del
tipo de ordenamiento a
utilizar, y de no ser así, busque que tanto las
bases de datos, cómo Analysis Services
tengan modelos de ordenamiento similares para evitar problemas cuando estos dos
componentes se conecten entre sí, luego pulsar en el botón Next >
14. Ahora configure el
Modo de autenticación para el inicio de sesión:
Windows ó Mixto. En el segundo caso, es
recomendable que establezca una
contraseña inicial para
el login sa. Asimismo agregue las cuentas de
usuario, al menos la cuenta de usuario
Windows actual, como
UNIVERSIDAD NACIONAL MAYOR DE SAN MARCOS
Facultad de Ingeniería Industrial - CEUPS Informática
Curso: Administración de Base de Datos - I
Docente: Ing. Oscar Alberto Barnett Contreras – [email protected] Página 29 de 72
administrador. Luego pulsar el botón Next >
15. Finalmente se mostrará un cuadro resumen de todas las opciones y parámetros seleccionados y configurados. Pulsar el botón Install para que
la instalación inicie. Al terminar, verifique el resumen final de la instalación y pulsar el botón Close para cerrar.
Cargar Microsoft SQL Server Management Studio 2008
Ahora debe especificar las Credenciales para solicitar un inicio de sesión, empleando una cuenta configurada en la instalación del producto, por
ejemplo Autenticación Mixta; Server=SERVIDOR; User=sa; Password=123; y pulsar el botón Connect
UNIVERSIDAD NACIONAL MAYOR DE SAN MARCOS
Facultad de Ingeniería Industrial - CEUPS Informática
Curso: Administración de Base de Datos - I
Docente: Ing. Oscar Alberto Barnett Contreras – [email protected] Página 30 de 72
Si todo es correcto, se muestra la siguiente pantalla de una sesión
iniciada con el servidor conectado:
Puede empezar a trabajar empleando el Explorador de Objetos o sino
Cerrar la Ventana de aplicación para salir de SQL Server. Recuerde que posiblemente algunos servicios continúan iniciados (según haya configurado
en la instalación) por lo que, si desea detenerlos puede emplear las
Herramientas Administrativas de Windows, como lo haría con cualquier otro servicio.
UNIVERSIDAD NACIONAL MAYOR DE SAN MARCOS
Facultad de Ingeniería Industrial - CEUPS Informática
Curso: Administración de Base de Datos - I
Docente: Ing. Oscar Alberto Barnett Contreras – [email protected] Página 31 de 72
Generación de una base de datos SQL Server
2008
Completar el Modelo Físico
Para nombrar un objeto Relationship, se hace clic derecho sobre
la relación y selecciona Relationship Properties…
Es importante definir el nombre de las restricciones de clave externa
(FK) para el ulterior proceso de generación de la base de datos. Para ello, en la ficha General se digita el nombre en la casilla correspondiente Foreign Key
Constraint Name:
UNIVERSIDAD NACIONAL MAYOR DE SAN MARCOS
Facultad de Ingeniería Industrial - CEUPS Informática
Curso: Administración de Base de Datos - I
Docente: Ing. Oscar Alberto Barnett Contreras – [email protected] Página 32 de 72
Ahora es necesario completar el modelamiento a nivel físico, de
acuerdo a los parámetros siguientes:
Además, se desea establecer algunas restricciones (constraint) en el modelo físico:
1. La fecha de una orden siempre
es menor a la fecha actual.
UNIVERSIDAD NACIONAL MAYOR DE SAN MARCOS
Facultad de Ingeniería Industrial - CEUPS Informática
Curso: Administración de Base de Datos - I
Docente: Ing. Oscar Alberto Barnett Contreras – [email protected] Página 33 de 72
2. El precio de un artículo siempre
debe ser positivo.
3. La cantidad de un artículo en una orden siempre debe ser positiva.
4. El cliente más frecuente es
‘Barnett’.
UNIVERSIDAD NACIONAL MAYOR DE SAN MARCOS
Facultad de Ingeniería Industrial - CEUPS Informática
Curso: Administración de Base de Datos - I
Docente: Ing. Oscar Alberto Barnett Contreras – [email protected] Página 34 de 72
Ahora, en el Menú Tools\Forward Engineer\Schema Generation…
se muestra la siguiente ventana de diálogo:
Pulsa el botón Preview… para completar el Script con las
instrucciones pertinentes:
UNIVERSIDAD NACIONAL MAYOR DE SAN MARCOS
Facultad de Ingeniería Industrial - CEUPS Informática
Curso: Administración de Base de Datos - I
Docente: Ing. Oscar Alberto Barnett Contreras – [email protected] Página 35 de 72
A continuación, de acuerdo a la versión de SQL Server disponible,
seguir una de las siguientes secuencias:
Versión 2005 de SQL Server
Pulsar el botón Generate... para establecer la conexión correcta con el servidor SQL Server que se desea, para ello debe digitar los parámetros
correctos de conexión:
User Name sa (Para autenticación Mixta)
Password <*******>
Database Master
Server Name <Nombre del Servidor>
Pulsar el botón Connect… para que se genere la base de datos en el
servidor SQL Server 2005, al finalizar cerrar la ventana de diálogo y verificar en el servidor la existencia de la base de datos creada. Luego puede salir de
ERWIN.
Versión 2008 de SQL Server
Pulsa en el botón para guardar el archivo con el Script completo, creando un archivo de extensión .sql
Luego pulsa el botón Close y a continuación el botón OK para cerrar
la ventana de diálogo.
UNIVERSIDAD NACIONAL MAYOR DE SAN MARCOS
Facultad de Ingeniería Industrial - CEUPS Informática
Curso: Administración de Base de Datos - I
Docente: Ing. Oscar Alberto Barnett Contreras – [email protected] Página 36 de 72
Ahora debe iniciar Microsoft SQL Server Management Studio y
conectarse a su servidor instalado:
Pulsar el botón Connect para completar la conexión:
UNIVERSIDAD NACIONAL MAYOR DE SAN MARCOS
Facultad de Ingeniería Industrial - CEUPS Informática
Curso: Administración de Base de Datos - I
Docente: Ing. Oscar Alberto Barnett Contreras – [email protected] Página 37 de 72
Ahora en el Menú File \ Open \ File… seleccionar el archivo de script
guardado anteriormente (EjemBarnett.sql) y pulsar el botón Open:
Ahora que el script está cargado, debe pulsar el botón Execute
UNIVERSIDAD NACIONAL MAYOR DE SAN MARCOS
Facultad de Ingeniería Industrial - CEUPS Informática
Curso: Administración de Base de Datos - I
Docente: Ing. Oscar Alberto Barnett Contreras – [email protected] Página 38 de 72
La base de datos será creada de acuerdo a las instrucciones del script
generado por Erwin:
Verificar la BD para asegurase que contiene todos los objetos que se
diseñaron con ERWIN:
La base de datos BARNETT ha sido creada
UNIVERSIDAD NACIONAL MAYOR DE SAN MARCOS
Facultad de Ingeniería Industrial - CEUPS Informática
Curso: Administración de Base de Datos - I
Docente: Ing. Oscar Alberto Barnett Contreras – [email protected] Página 39 de 72
Es conveniente crear un objeto Diagrama en SQL Server para
comprobar las relaciones entre las tablas:
Ahora que todo es correcto, puede salir de Erwin.
Práctica de Laboratorio – Modelamiento BD 1. Elabore un modelo en Erwin el cual esté asociado a un proceso de
planillas, considerando solo objetos Entidad. (Claves y tipos de datos para los atributos)
2. Relacione usted los objetos Entidad. 3. Represente las relaciones utilizando la metodología Ingeniería de la
Información (IE). 4. Establezca una definición para cada objeto Entidad
5. Establezca un Icono para cada objeto Entidad
6. Muestre usted el modelo con los cinco niveles de visualización ya explicados.
7. Genere desde ERWIN la base de datos para un servidor SQL Server 2008.
8. Elabore nuevamente los siete puntos anteriores, pero esta vez para un proceso productivo.
Contexto del Caso De Estudio
La empresa “Comercial BARNETT SAC” comercializa productos del rubro Ferretería a través de una serie de oficinas de ventas distribuidas en
diferentes regiones geográficas del país (una sola oficina por ciudad en cada
región), donde cada oficina es identificada por un Nro. de oficina (número entero). Cada oficina está a cargo de un Director el cual, por política de la
empresa, es siempre un empleado del Área de Ventas y que, ocasionalmente, puede tener a su cargo más de una oficina. Este Director es responsable de
que la oficina cumpla con el Objetivo de ventas mensual que se haya establecido para dicha oficina. Cabe señalar que por política de la empresa,
todas las transacciones monetarias se expresan en dólares USA.
UNIVERSIDAD NACIONAL MAYOR DE SAN MARCOS
Facultad de Ingeniería Industrial - CEUPS Informática
Curso: Administración de Base de Datos - I
Docente: Ing. Oscar Alberto Barnett Contreras – [email protected] Página 40 de 72
Asimismo, cada oficina dispone de empleados convocados mediante
contrato por tiempo indefinido, (cuya fecha determina su Tiempo de Servicio),
y cumplen labor como personal de ventas. Cada empleado es identificado por un Nro. de empleado (número entero) y cuenta con su propia Cartera de
Clientes a los cuales atiende registrando sus pedidos; de esa manera los empleados pueden cumplir con una cuota de ventas mensual que se les
asigna. Un empleado del área de Ventas puede tener los siguientes Cargos ó Títulos: Representante de Ventas, Director de Ventas, Vicepresidente de
Ventas.
Los clientes son identificados mediante un Nro. de cliente (número entero) y a cada uno se le asigna un monto límite para ventas al crédito,
este monto no puede ser excedido bajo responsabilidad del empleado que lo atiende. Cada cliente tiene un personal para contacto con quien los
empleados de venta realizan tratos comerciales, por lo que es importante disponer del número telefónico de tales contactos para comunicarse con
ellos. Los productos son suministrados por varios proveedores los cuales,
muchas veces, utilizan códigos similares para sus productos por lo que, para
identificarlos se emplea un primer código de familia (3 caracteres) y además un código de producto (5 caracteres). Es importante conocer en todo
momento la existencia de los productos para atender los pedidos de los clientes, así como controlar la cantidad mínima de existencias para procesar
la reposición de productos cuando sea necesario, siguiendo la política de inventarios de la empresa.
REQUERIMIENTOS DE INFORMACION
Se necesita mantener actualizada la información sobre las oficinas, empleados, clientes y productos, así como registrar los pedidos de los
clientes atendidos por los empleados de manera que se pueda suministrar información sobre las actividades relacionadas con el área de Ventas:
Listar las oficinas existentes ordenadas por su Objetivo y/o Ventas.
Listar los empleados en orden alfabético por nombre.
Listar los clientes ordenados por Límite de crédito.
Listar los productos ordenados por familia y/o precio.
Listar las oficinas de una determinada región y/o ciudad.
Listar las oficinas cuya Venta es menor a un cierto monto.
Listar los empleados que tienen un cierto título.
Listar los empleados asignados a una cierta oficina.
Listar los empleados que han sido contratados en un período cualquiera.
Listar al Director de una oficina determinada.
Listar las oficinas que cumplen o no cumplen con su objetivo.
Listar los empleados que cumplen o no cumplen con su cuota.
Listar los clientes que no han realizado pedidos hasta la fecha.
Listar los productos que deben ser reabastecidos.
Listar los productos clasificados por rangos de precio.
Listar los pedidos realizados por cada cliente.
Listar los pedidos atendidos por cada empleado.
Calcular las comisiones por venta a cada empleado.
Calcular descuentos a los clientes por volumen de pedido.
Calcular los precios límite de los productos.
etc., etc.
NOTA: Cada pedido solo consta de un único producto.
UNIVERSIDAD NACIONAL MAYOR DE SAN MARCOS
Facultad de Ingeniería Industrial - CEUPS Informática
Curso: Administración de Base de Datos - I
Docente: Ing. Oscar Alberto Barnett Contreras – [email protected] Página 41 de 72
Objetos De Un Servidor Sql Server 2008
Existen cuatro partes posibles en el nombre de un objeto:
Nombre del servidor
Nombre de la BD
Nombre del propietario del objeto
Identificador del objeto
Por lo que el nombre completo ó absoluto de un objeto tabla “Ventas”
que pertenece al propietario “dbo” en la base de datos “Ventas” del servidor “SERVIDOR”, sería: SERVIDOR.Ventas.dbo.Oficinas
Ejemplos Los siguientes nombres son válidos en diversas circunstancias y tipos
de objeto:
SERVIDOR.Ventas...Oficinas SERVIDOR...dbo.Oficinas
SERVIDOR…Oficinas Ventas.dbo.Oficinas
Ventas...Oficinas
dbo.Oficinas Oficinas
Ejemplo: --CONOCE EL NOMBRE DEL SERVIDOR
SELECT * FROM sys.servers; --DIVERSAS CONSULTAS DE SELECCIÓN
SELECT * FROM SERVIDOR.Ventas.dbo.Oficinas; SELECT * FROM Ventas.dbo.Oficinas;
SELECT * FROM Ventas..Oficinas;
SELECT * FROM Oficinas
Objetos De Una Base De Datos Sql Server
DATABASE DIAGRAMAS (DIAGRAMAS DE BASE DE DATOS)
Son representaciones gráficas de la base de datos que pueden contener: tablas, vistas; en las cuales se pueden realizar también operaciones
con ellas. Para crear un diagrama basta con seleccionar la opción New
Database Diagrams.. del menú contextual de la carpeta “Database Diagrams”
TABLES (TABLAS) Es el objeto de almacenamiento permanente de datos. Tiene una
estructura tabular (filas y columnas) y se crea mediante la opción New Table.. del menú contextual de la carpeta “Tables”
VIEWS (VISTAS) La vista constituye el elemento de consulta básico en la base de datos,
pues tiene la capacidad de mostrar datos provenientes de las tablas y/o vistas existentes no solo en la base de datos actual sino también de otras bases de
datos. Para crear una vista basta con seleccionar la opción New View.. del menú contextual de la carpeta “Views”
SYNONYMS (SINONIMOS)
UNIVERSIDAD NACIONAL MAYOR DE SAN MARCOS
Facultad de Ingeniería Industrial - CEUPS Informática
Curso: Administración de Base de Datos - I
Docente: Ing. Oscar Alberto Barnett Contreras – [email protected] Página 42 de 72
Son nombres alternativos para tablas, vistas, procedimientos
almacenados y otros objetos, que permite referirse a uno de estos objetos con
más de un nombre. Ejemplo.- Si en el servidor “SERVIDOR” existe una BD “Ventas” en la que
existe una tabla “Clientes” y se desea crear un nombre sinónimo para dicha tabla:
Se elige la opción New Synonym.. en el menú contextual del objeto Synonims de la BD Ventas.
Se configura el sinónimo de manera adecuada:
SERVICES BROKER (SERVICIO INTERMEDIARIO) Proporciona colas y mensajería como parte del motor de base de datos,
de tal manera que se puedan crear aplicaciones distribuidas entre servidores con mayor grado de confiabilidad. Los programadores de aplicaciones que
usan Service Broker pueden distribuir las cargas de trabajo de datos en varias
bases de datos sin tener que programar complejas funciones internas de comunicación y mensajería. Se compone de siete subcarpetas:
Messages Type, Tipos de Mensaje
Contracts, Contratos
Queues, Colas
Services, Servicios
Routes, Rutas
Remote Service Binding, Enlace de servicio remoto
Priority Broker, Nivel Prioritario
Las opciones de trabajo están incorporadas en el menú contextual de
cada una de las subcarpetas.
STORAGE (ALMACENAMIENTO)
El “almacenamiento” permite emplear el servicio de búsqueda, filtro e indización de texto completo como parte de las tareas del motor de base de
datos.
Esta carpeta formada por cuatro subcarpetas:
Full Text Catalogs, Catálogos de texto
Partition Schemes, Esquema de partición
Partition Functions, Funciones de partición
Full Text StopLists, Lista de texto irrelevante
UNIVERSIDAD NACIONAL MAYOR DE SAN MARCOS
Facultad de Ingeniería Industrial - CEUPS Informática
Curso: Administración de Base de Datos - I
Docente: Ing. Oscar Alberto Barnett Contreras – [email protected] Página 43 de 72
Cada una de ellas representa una parte en la arquitectura para
búsqueda de texto en SQL Server.
SECURITY (SEGURIDAD)
Aquí se encuentran los objetos relacionados al control de acceso y permisos en una base de datos. Para ello existen siete subcarpetas:
Users, Usuarios
Roles, Funciones (permisos)
Schemas, Esquemas
Asymetric Keys, Claves Asimétricas
Certificates, Certificados
Symetric Keys, Claves Simétricas
Database Audit Specifications, Especificaciones de Auditoría de base
de datos
En cada una de ellas se pueden crear objetos para controlar la actividad dentro de la base de datos, como un complemento a la seguridad del
servidor.
PROGRAMMABILITY (PROGRAMACION)
Aquí se encuentran varias subcarpetas referidas a los objetos de programación:
Stored Procedures, Procedimientos Almacenados (código que
automatiza una tarea)
Functions, Funciones (código que calcula y retorna valor)
Databases Triggers, Desencadenantes (PA automatizados)
Assemblies, Ensamblados NET (incorporados con CREATE ASSEMBLY)
Types, tipos (datos de sistema, de usuario, Colecciones esquema XML)
Rules, reglas (restricción de valor). Se prefiere constraint CHECK
Defaults, Valores predeterminados. Se prefiere constraint DEFAULT
Plan Guides, Plan
Configuración del Servidor con instrucciones Set
Permite configurar diversos comportamientos en el servidor, por ejemplo para adaptarlo al estándar SQL 92. Algunas de estas opciones son las
siguientes:
SET ANSI_WARNINGS {ON|OFF}, Maneja advertencias requeridos por el estándar ANSI, por ejemplo:
CREATE TABLE PruebaWarning(Col1 int NULL) INSERT INTO PruebaWarning(Col1)VALUES(5)
INSERT INTO PruebaWarning(Col1)VALUES(Null)
INSERT INTO PruebaWarning(Col1)VALUES(7) PRINT 'warnings on'
SET ANSI_WARNINGS ON SELECT SUM (Col1) FROM PruebaWarning
PRINT 'warnings off' SET ANSI_WARNINGS OFF
SELECT SUM (Col1) FROM PruebaWarning
UNIVERSIDAD NACIONAL MAYOR DE SAN MARCOS
Facultad de Ingeniería Industrial - CEUPS Informática
Curso: Administración de Base de Datos - I
Docente: Ing. Oscar Alberto Barnett Contreras – [email protected] Página 44 de 72
Resultado de ejecución:
warnings on
Warning: Null value is eliminated by an aggregate or other SET operation.
(1 row(s) affected) warnings off
(1 row(s) affected)
SET ANSI_PADDING {ON|OFF}, controla el efecto de los espacios en blanco o
ceros finales cuando se insertan valores en columnas de longitud fija o variable. El tipo específico de dato determina el comportamiento final.
Ejemplo: Char(n) NOT NULL; ON|OFF rellena con espacios en blanco hasta
completar el tamaño de la columna. Char(n) NULL, ON rellena con espacios en blanco hasta completar el
tamaño de la columna; OFF recorta todos los espacios en blanco a la
derecha. Varchar(n), ON; No recorta ni rellena valores; OFF recorta espacios en
blanco a la derecha pero no rellena Ejemplo:
--PRUEBA COMANDO SET_PADDING PRINT 'Prueba con ANSI_PADDING ON'
SET ANSI_PADDING ON; CREATE TABLE tabla1 (
ColChar CHAR(18) NULL,
ColVarchar VARCHAR(18) NULL, ColVarbinary VARBINARY(8));
INSERT INTO tabla1 VALUES ('No blancos', 'No blancos', 0x00ee); INSERT INTO tabla1 VALUES ('Rellena blanco ', 'Rellena blanco ', 0x00ee00);
SELECT 'CHAR' = '>' + ColChar + '<', 'VARCHAR'='>' + ColVarchar +
'<',ColVarbinary
FROM tabla1; PRINT 'Prueba con ANSI_PADDING OFF';
SET ANSI_PADDING OFF;
CREATE TABLE tabla2 ( ColChar CHAR(18) NULL,
ColVarchar VARCHAR(18) NULL, ColVarbinary VARBINARY(8));
INSERT INTO tabla2 VALUES ('No blancos', 'No blancos', 0x00ee); INSERT INTO tabla2 VALUES ('Rellena blanco ', 'Rellena blanco ', 0x00ee00);
SELECT 'CHAR' = '>' + ColChar + '<', 'VARCHAR'='>' + ColVarchar + '<',ColVarbinary
FROM tabla2;
UNIVERSIDAD NACIONAL MAYOR DE SAN MARCOS
Facultad de Ingeniería Industrial - CEUPS Informática
Curso: Administración de Base de Datos - I
Docente: Ing. Oscar Alberto Barnett Contreras – [email protected] Página 45 de 72
DROP TABLE tabla1
DROP TABLE tabla2
Resultado de la ejecución:
Asimismo hay más comandos de configuración tales como:
SET ANSI_NULLS {ON|OFF}, controla si se puede utilizar el operador de
igualdad con NULL, por ejemplo: WHERE Col1= NULL para determinar si la
columna tiene un contenido NULL.
SET IMPLICIT_TRANSACTIONS {ON|OFF}, determina si las instrucciones CREATE, SELECT, INSERT y UPDATE inician transacciones automáticamente
cada vez que se ejecuten, en cuyo caso se debe confirmar (COMMIT) o revertir (ROLLBACK) explícitamente la instrucción.
SET ANSI_DEFAULTS {ON|OFF}, permite establecer una compatibilidad
completa con ANSI, pues combina una serie de instrucciones:
SET ANSI_NULLS ON
SET ANSI_NULL_DDFLT_ON ON
SET ANSI_PADDING ON
SET ANSI_WARNINGS ON
SET CURSOR_CLOSE_ON_COMMIT ON
SET IMPLICIT_TRANSACTIONS ON
SET QUOTED_IDENTIFIER ON
Configuración De Una Base De Datos
Cuando desee modificar la configuración de una BD puede emplear el
comando ALTER TABLE:
ALTER TABLE <NombreBD> SET
{SINGLE_USER|RESTRICTED_USER|MULTI_USER} | {READ_ONLY | READ_WRITE} |
AUTO_SHRINK {ON|OFF} ANSI_NULLS {ON|OFF} |
ANSI_WARNINGS {ON|OFF} | QUOTED_IDENTIFIERS {ON|OFF} |
RECURSIVE _TRIGGERS {ON|OFF} |
RECOVERY {FULL|BULK_LOGGED|SIMPLE}
UNIVERSIDAD NACIONAL MAYOR DE SAN MARCOS
Facultad de Ingeniería Industrial - CEUPS Informática
Curso: Administración de Base de Datos - I
Docente: Ing. Oscar Alberto Barnett Contreras – [email protected] Página 46 de 72
Y otras opciones más adicionales, cada una de las cuales se establece
con carácter permanente.
También puede emplear el procedimiento almacenado sp_dboption. Ejemplo:
--MODIFICA CONFIGURACION DE UNA BD -- MUESTRA OPCIONES DISPONIBLES
exec sp_dboption 'Ventas' --USANDO PARAMETROS POR NOMBRE
exec sp_dboption @dbname='Ventas',@optname='ANSI Nulls',
@optvalue='TRUE' --USANDO PARAMETROS POR POSICION
exec sp_dboption 'Ventas','ANSI Warnings','TRUE'
También es posible que en una BD que ha sido migrada desde versiones anteriores, necesite mantener compatibilidad en la nueva plataforma
SQL Server 2008. Para ello puede emplear el procedimiento almacenado
sp_dbcmmptlevel. Por ejemplo: -- AJUSTA COMPATIBILIDAD CON DIVERSAS VERSIONES
-- sql server 2000 exec sp_dbcmptlevel 'Ventas', 80
-- sql server 2005 exec sp_dbcmptlevel 'Adventure Works',90
-- sql server 2008 exec sp_dbcmptlevel 'NeoBARNETT',100
UNIVERSIDAD NACIONAL MAYOR DE SAN MARCOS
Facultad de Ingeniería Industrial - CEUPS Informática
Curso: Administración de Base de Datos - I
Docente: Ing. Oscar Alberto Barnett Contreras – [email protected] Página 47 de 72
Capítulo II - COMANDOS TRANSACT SQL
COMANDOS DDL
Sirven para manipular los objetos de la base de datos
Create Database
Esta instrucción permite crear una nueva base de datos
Formato:
Create database <nombre base_de_datos>
Ejemplo:
USE master
GO
CREATE DATABASE Sales ON
( NAME = Sales_dat, FILENAME = 'c:\mssql7\data\saledat.mdf',
SIZE = 10, MAXSIZE = 50,
FILEGROWTH = 5 )
LOG ON
( NAME = 'Sales_log', FILENAME = 'c:\mssql7\data\salelog.ldf',
SIZE = 5MB, MAXSIZE = 25MB,
FILEGROWTH = 5MB ) GO
Alter database
Esta instrucción permite modificar una base de datos .
Formato:
Alter Database <nombre base_datos>
Drop Database
Esta instrucción permite eliminar una base de datos
Formato:
Drop Database <nombre base_datos>
UNIVERSIDAD NACIONAL MAYOR DE SAN MARCOS
Facultad de Ingeniería Industrial - CEUPS Informática
Curso: Administración de Base de Datos - I
Docente: Ing. Oscar Alberto Barnett Contreras – [email protected] Página 48 de 72
Create Table
Esta instrucción permite crear una tabla
CREATE TABLE NombreBaseDatos.[propietario].nombreTabla (<Definición_Columnas>)
<Restricción_Tabla> Ejemplo:
CREATE TABLE publishers
(
pub_id char(4) NOT NULL CONSTRAINT UPKCL_pubind PRIMARY KEY CLUSTERED
CHECK (pub_id IN ('1389', '0736', '0877', '1622', '1756') OR pub_id LIKE '99[0-9][0-9]'),
pub_name varchar(40) NULL, city varchar(20) NULL,
state char(2) NULL,
country varchar(30) NULL DEFAULT('USA')
)
Alter Table
Permite modificar una tabla.
Ejemplo:
CREATE TABLE doc_exa ( column_a INT) GO
ALTER TABLE doc_exa ADD column_b VARCHAR(20) NULL GO
CREATE TABLE doc_exe ( column_a INT CONSTRAINT column_a_un UNIQUE)
GO
ALTER TABLE doc_exe ADD column_b INT IDENTITY
CONSTRAINT column_b_pk PRIMARY KEY, column_c INT NULL
CONSTRAINT column_c_fk REFERENCES doc_exe(column_a),
column_d VARCHAR(16) NULL CONSTRAINT column_d_chk
CHECK
(column_d IS NULL OR column_d LIKE "[0-9][0-9][0-9]-[0-9][0-9][0-9][0-9]" OR
column_d LIKE "([0-9][0-9][0-9]) [0-9][0-9][0-9]-[0-9][0-9][0-9][0-9]"),
column_e DECIMAL(3,3) CONSTRAINT column_e_default
DEFAULT .081
GO
UNIVERSIDAD NACIONAL MAYOR DE SAN MARCOS
Facultad de Ingeniería Industrial - CEUPS Informática
Curso: Administración de Base de Datos - I
Docente: Ing. Oscar Alberto Barnett Contreras – [email protected] Página 49 de 72
Drop Table
Permite eliminar una tabla
Formato:
Drop Table <nombre tabla>
Ejemplo: DROP TABLE doc_exe
COMANDOS DML Se emplean para manipular los datos almacenados en la base de datos.
Insert Into
Esta instrucción permite insertar un registro o fila en una tabla o un conjunto
de tablas.
Formato:
Insert Into <Tabla> (columnas) Values (Lista de Valores)
Ejemplos:
IF EXISTS(SELECT TABLE_NAME FROM INFORMATION_SCHEMA.TABLES
WHERE TABLE_NAME = 'T1')
DROP TABLE T1 GO
CREATE TABLE T1
( column_1 int identity, column_2 varchar(30)
CONSTRAINT default_name DEFAULT ('column default'), column_3 int NULL,
column_4 varchar(40)
) GO
INSERT INTO T1 (column_4) VALUES ('Explicit value')
GO INSERT INTO T1 (column_2,column_4)
VALUES ('Explicit value', 'Explicit value')
GO INSERT INTO T1 (column_2,column_3,column_4)
VALUES ('Explicit value',-44,'Explicit value') GO
SELECT * FROM T1
UNIVERSIDAD NACIONAL MAYOR DE SAN MARCOS
Facultad de Ingeniería Industrial - CEUPS Informática
Curso: Administración de Base de Datos - I
Docente: Ing. Oscar Alberto Barnett Contreras – [email protected] Página 50 de 72
Update
Esta instrucción permite actualizar y modificar uno o un conjunto de
registros. Formato:
Update <tabla> Set (lista de campos) Where [condición)
Ejemplos:
CREATE TABLE s (ColA INT, ColB DECIMAL(10,3)) GO
CREATE TABLE t (ColA INT PRIMARY KEY, ColB DECIMAL(10,3)) GO
INSERT INTO s VALUES(1, 10.0) INSERT INTO s VALUES(1, 20.0)
INSERT INTO t VALUES(1, 0.0)
GO UPDATE t
SET t.ColB = t.ColB + s.ColB FROM t INNER JOIN s ON (t.ColA = s.ColA)
GO
--MODIFICA EL IDENTIFICADOR DE VENDEDOR PARA TODOS LOS --CLIENTES QUE TENGAN EL VENDEDOR PEREZ.
UPDATE Clientes SET Vendedor="GUTI"
WHERE Vendedor="PEREZ"
--AUMENTA EL PRECIO AL POR MENOR DE TODOS LOS ARTICULOS EN 5%
UPDATE Articulos SET Minorista=Minorista*1.05
Delete
Esta instrucción permite eliminar uno o un conjunto de registros contenidos
en una tabla.
Formato:
Delete from <tabla> where [condición]
Ejemplos:
--ELIMINA AL AUTOR DE APELLIDO McBadden
USE pubs DELETE FROM authors
WHERE au_lname = 'McBadden'
--ELIMINA TODOS LOS CLIENTES QUE VIVAN EN SEVILLA
DELETE FROM Clientes WHERE Provincia = "Se"
UNIVERSIDAD NACIONAL MAYOR DE SAN MARCOS
Facultad de Ingeniería Industrial - CEUPS Informática
Curso: Administración de Base de Datos - I
Docente: Ing. Oscar Alberto Barnett Contreras – [email protected] Página 51 de 72
COMANDO SELECT Para implementar las consultas se emplea la Ventana de Consulta (New
Query) En ella se puede digitar el código T-SQL
deseado, generando un texto “esquematizado”, al estilo de la
plataforma de desarrollo NET.
SELECT Es el comando que permite seleccionar datos de las tablas y/o consultas de la base de datos. Para ello dispone de una extensa sintaxis, de la
cual se muestra algunas de sus clausulas principales:
PARA UNA SOLA TABLA / VISTA SELECT <Lista de Columnas a Mostrar>
FROM <Nombre Tabla /Vista> WHERE <Expresión Lógica Filtro de Filas>
ORDER BY <Expresión Para Ordenar Filas> GROUP BY <Expresión de Agrupamiento>
HAVING <Expresión Lógica Filtro de Grupos>
PARA DOS TABLAS / VISTAS
SELECT <Lista de Alias.Columnas a Mostrar> FROM <Tabla1 [Alias1] {INNER|LEFT|RIGHT|FULL} JOIN Tabla2
[Alias2] ON <Expresión de Enlace de Tablas>
WHERE <Expresión Lógica Filtro de Filas> ORDER BY <Expresión Para Ordenar Filas>
GROUP BY <Expresión de Agrupamiento>
HAVING <Expresión Lógica Filtro de Grupos>
CLAUSULA LIKE.- Para encontrar coincidencias entre cadenas de texto. CLAUSULA DISTINCT.- Para omitir registros que contienen valores duplicados
en las columnas seleccionadas. CLAUSULA IN.- Permite seleccionar ítems dentro de un rango.
SELECT DE FECHAS.- Se emplea la comilla como delimitador de fechas. Además se dispone de cinco funciones para operaciones matemáticas y
estadísticas en la BD:
1. SUM, sumar 2. COUNT, contar 3. AVG, promedio
4. MAX, valor mayor 5. MIN, valor menor
UNIVERSIDAD NACIONAL MAYOR DE SAN MARCOS
Facultad de Ingeniería Industrial - CEUPS Informática
Curso: Administración de Base de Datos - I
Docente: Ing. Oscar Alberto Barnett Contreras – [email protected] Página 52 de 72
Formato:
SELECT [ ALL | DISTINCT ] [ TOP n [PERCENT] [ WITH TIES] ]
<listaSelección> <listaSelección> ::=
* | { nombreTabla | nombreVista | aliasTabla }.*
| { nombreColumna | expresión | IDENTITYCOL |
ROWGUIDCOL }
[ [AS] aliasColumna ]
| aliasColumna = expresión } [,...n]
Ejemplos:
--SELECCIONAR TODOS LOS REGISTROS DE UNA TABLA CON TODOS SUS --CAMPOS
SELECT *
FROM Ventas
--SELECCIONAR TODOS LOS REGISTROS, PERO SOLO UNOS CAMPOS
SELECT Articulo, Descripcion, [Precio Venta]
FROM Ventas
--CREA UNA CONSULTA CON EL APELLIDO Y EL NOMBRE DE LOS --CLIENTES, SEPARADOS POR UNA COMA, EN UN SOLO CAMPO
SELECT Apellido + ', ' + Nombre
FROM Clientes
--SELECCIONA LOS CLIENTES ORDENANDOLOS POR APELLIDO (DE
FORMA 'DESCENDENTE) Y POR EL NOMBRE (DE FORMA ASCENDENTE)
SELECT * FROM Clientes
ORDER BY Apellido DESC, Nombre
--SELECCIONA TODOS LOS CAMPOS DE LOS CLIENTES QUE SE
APELLIDEN 'Perez
SELECT * FROM Clientes
WHERE Apellido = ‘Perez’
--SELECCIONA TODOS LOS CAMPOS DE LOS ARTÍCULOS CON FECHA --MAYOR QUE 15/12/09
SELECT * FROM Articulos
WHERE Fecha > '15/12/09'
UNIVERSIDAD NACIONAL MAYOR DE SAN MARCOS
Facultad de Ingeniería Industrial - CEUPS Informática
Curso: Administración de Base de Datos - I
Docente: Ing. Oscar Alberto Barnett Contreras – [email protected] Página 53 de 72
--SELECCIONA TODOS LOS CAMPOS DE LOS CLIENTES QUE SEAN --
GALLEGOS
SELECT *
FROM Clientes WHERE Provincia IN ('C', 'Lu', 'Or', 'Po')
--SELECCIONA LOS CLIENTES CON FECHA DE ENERO DE 2009
SELECT * FROM Clientes
WHERE Fecha BETWEEN '1/01/09' AND '31/01/09'
--SELECCIONA LOS CLIENTES CON APELLIDOS QUE NO ESTÉN ENTRE LA -- M Y LA P
SELECT *
FROM Clientes
WHERE Apellido NOT BETWEEN 'M' AND 'P'
--SELECCIONA LOS CLIENTES QUE SE APELLIDEN MARTINEZ DE LAS PROVINCIAS DE PONTEVEDRA Y ORENSE
SELECT *
FROM Clientes
WHERE Apellido = 'Martinez' AND Provincia IN ('Po', 'OR')
FUNCIONES DE AGREGACIÓN Y ESTÁNDAR
--CREA UN RECORDSET CON UN ÚNICO REGISTRO CON EL STOCK --MÍNIMO, 'EL MÁXIMO Y EL MEDIO DE LOS ARTÍCULOS ASÍ COMO EL
--COSTE TOTAL 'DE LAS EXISTENCIAS
SELECT Min(Stock), Max(Stock), Avg(Stock), Sum(Stock * Precio) FROM Articulos
--CREA UNA CONSULTA COMPUESTA DE DOS CAMPOS, UNA CANTIDAD Y --LA RAIZ CUADRADA DE ESA CANTIDAD. A ESTOS CAMPOS LES ASIGNA
--UN 'NOMBRE ALTERNATIVO
SELECT Cantidad AS Cant1, SQRT(Cantidad) AS Cant2 FROM Ventas
--SELECCIONA TODOS LOS CAMPOS DE LOS CLIENTES QUE TENGAN UN
--APELLIDO QUE NO EMPIECE POR UNA VOCAL Y QUE LA TERCERA LETRA
--SEA UNA n
SELECT * FROM Clientes
WHERE LEFT(Apellido,1) = '[!aeiou]?n*'
UNIVERSIDAD NACIONAL MAYOR DE SAN MARCOS
Facultad de Ingeniería Industrial - CEUPS Informática
Curso: Administración de Base de Datos - I
Docente: Ing. Oscar Alberto Barnett Contreras – [email protected] Página 54 de 72
--CREA UN IDENTIFICADOR DE CLIENTE UTILIZANDO LAS 3 PRIMERAS
--LETRAS DE LOS CAMPOS APELLIDOS Y NOMBRE Y PONE TODO EN
--MAYUSCULAS
SELECT UPPER(LEFT(Apellidos, 1, 3)) + UPPER(LEFT(Nombre, 1, 3)) FROM Clientes
--SELECCIONA TODOS LOS CAMPOS DE LOS CLIENTES QUE TENGAN
--COMO 'SEGUNDA LETRA DEL APELLIDO, UNA LETRA A, B O C
SELECT *
FROM Clientes WHERE SUBSTRING(Apellido, 2, 1) LIKE '[a-c]'
CONSULTAS CON GRUPOS Y FILTROS HAVING --OBTIENE EL TOTAL DE VENTAS DE LAS PROVINCIAS
SELECT Provincia, SUM(VentasTotales)
FROM Clientes
GROUP BY Provincia
--OBTIENE EL TOTAL DE VENTAS DE LAS PROVINCIAS QUE TENGAN MAS --DE 10 CLIENTES Y LA VENTA MAXIMA A UN SOLO CLIENTE NO SEA
--SUPERIOR A 1000
SELECT Provincia, SUM(VentasTotales)
FROM Clientes GROUP BY Provincia
HAVING COUNT(*) > 10 AND MAX(VentasTotales) < 1000
COMBINACION DE TABLAS – CONSULTAS MULTITABLA
--SELECCIONAR TODOS LOS CAMPOS DE LA TABLA VENTAS Y LOS -- CAMPOS ‘DESCRIPCION Y MINORISTA DE LA TABLA DE ARTICULOS,
--RELACIONANDO AMBAS TABLAS POR EL CODIGO DEL ARTICULO.
SELECT Ventas.*, Articulos.Descripcion, Articulos.Minorista
FROM Ventas, Articulos WHERE Ventas.Articulo = Articulos.Articulo
--CALCULAR EL PRECIO TOTAL DE LOS ARTICULOS. ESTA CONSULTA
-- CREA UN CAMPO CON EL RESULTADO DE LA MULTIPLICACION
SELECT Articulos.Minorista * Ventas.Cantidad FROM Articulos, Ventas
WHERE Ventas.Articulo = Articulos.Articulo
UNIVERSIDAD NACIONAL MAYOR DE SAN MARCOS
Facultad de Ingeniería Industrial - CEUPS Informática
Curso: Administración de Base de Datos - I
Docente: Ing. Oscar Alberto Barnett Contreras – [email protected] Página 55 de 72
--OBTIENE UNA NUEVA TABLA, CORREOS, CON LAS DIRECCIONES DE LOS
--CLIENTES
SELECT CS.Apellido + ', ' + CS.Nombre AS Nombre,
CS.Direccion, CP.Ciudad,
CP.Provincia, CS.[Codigo Postal]
INTO Correos
FROM Clientes CS, [Codigo Postal] CP WHERE CS.[Codigo Postal] = CP.Codigo
--NO SE SELECCIONAN LOS REGISTROS QUE TENGAN IGUALES TODOS
-- LOS CAMPOS EN AMBAS TABLAS
SELECT DISTINCTROW Articulo FROM Articulos, Ventas
WHERE Articulos.Articulo = Ventas.Articulo
--SE GENERA UN RECORDSET CON TODOS LOS REGISTROS DE LA TABLA
--CLIENTES, EXISTA O NO EL VENDEDOR ASOCIADO, EN LA QUE NO --SALDRÁN REFLEJADOS LOS VENDEDORES QUE NO TIENEN NINGÚN
--CLIENTE ASIGNADO
SELECT CS.Apellido, CS.Nombre, VN.Apellido, VN.Nombre
FROM Clientes CS LEFT JOIN Vendedores VN ON CS.Vendedor = VN.Vendedor
EJEMPLOS DE CONSULTAS EN LA BD VENTAS
--MUESTRA NRO DE CLIENTE, EMPRESA Y CIUDAD DE TODOS LOS -- CLIENTES
SELECT Num_Clie, Empresa, Ciudad
FROM CLIENTES
--MUESTRA TODOS LOS DATOS DE LOS 10 PRIMEROS CLIENTES
SELECT TOP 10 *
FROM CLIENTES
--MUESTRA LOS 10 PRIMEROS CLIENTES, ORDENANDO POR LIMITE -- DE CRÉDITO
SELECT TOP 10 *
FROM CLIENTES
ORDER BY Limite_Credito
--MUESTRA TODOS LOS DATOS DE LOS CLIENTES CUYO LIMITE -- DE CRÉDITO ES MAYOR A 50000
SELECT *
FROM CLIENTES WHERE Limite_Credito > 50000
UNIVERSIDAD NACIONAL MAYOR DE SAN MARCOS
Facultad de Ingeniería Industrial - CEUPS Informática
Curso: Administración de Base de Datos - I
Docente: Ing. Oscar Alberto Barnett Contreras – [email protected] Página 56 de 72
--MUESTRA CÓDIGO, PRECIO E IMPUESTO IGV DE TODOS
-- LOS PRODUCTOS
SELECT Id_Fab, Id_Producto, Precio, Precio * 19/100 AS ‘IGV’
FROM Productos
--MUESTRA TODOS LOS DATOS DE CLIENTES CUYA EMPRESA --INICIA CON LETRA C
SELECT * FROM clientes
WHERE empresa LIKE 'C%'
--MUESTRA LOS CLIENTES CUYA EMPRESA TIENE C COMO -- SEGUNDA LETRA
SELECT *
FROM clientes
WHERE empresa LIKE '_c%'
--MUESTRA PRODUCTOS CUYA DESCRIPCIÓN TIENE --LETRA C EN CUALQUIER POSICIÓN
SELECT *
FROM Productos
WHERE Descripcion LIKE '%c%'
--MUESTRA PRODUCTOS CUYO CÓDIGO DE FABRICA ES 'REI' Ó 'FEA'
SELECT * FROM Productos
WHERE Id_Fab IN ('FEA','REI')
--MUESTRA EL TOTAL PEDIDO EL 12 DE OCTUBRE DEL 2006
SELECT SUM(Importe) AS 'Total Importe'
FROM Pedidos WHERE (Fecha_pedido = '10-12-2006')
--MUESTRA CANTIDAD DE PEDIDOS AGOTADOS
SELECT COUNT(Id_Fab) AS 'Nro. Agotados'
FROM Productos
WHERE Existencias=0;
--MUESTRA EL PRECIO PROMEDIO DE LOS PRODUCTOS --CON EXISTENCIAS MAYORES AL MINIMO
SELECT AVG(Precio)
FROM Productos
WHERE Existencias > Cant_Min
UNIVERSIDAD NACIONAL MAYOR DE SAN MARCOS
Facultad de Ingeniería Industrial - CEUPS Informática
Curso: Administración de Base de Datos - I
Docente: Ing. Oscar Alberto Barnett Contreras – [email protected] Página 57 de 72
--MUESTRA EL VALOR DE INVENTARIO DE TODOS LOS PRODUCTOS
SELECT SUM(Existencias*Precio) AS 'Inventario' FROM Productos;
--MUESTRA EL VALOR DE INVENTARIO DE TODOS LOS
--PRODUCTOS AGRUPADOS POR CODIGO DE FABRICA
SELECT Id_Fab, SUM(Existencias*Precio) AS 'Inventario Parcial'
FROM Productos GROUP BY Id_Fab;
--MUESTRA EL VALOR DE INVENTARIO DE TODOS LOS
--PRODUCTOS AGRUPADOS POR CODIGO DE FABRICA CUYO --VALOR SEA MAYOR A 100000
SELECT Id_Fab, SUM(Existencias*Precio) AS 'Inventario Parcial'
FROM Productos
GROUP BY Id_Fab HAVING SUM(Existencias*Precio) > 100000;
--MUESTRA LOS TITULOS QUE PUEDEN TENER LOS EMPLEADOS
SELECT DISTINCT titulo
FROM repventas
GROUP BY titulo;
--CUENTA LOS TITULOS QUE PUEDEN TENER LOS EMPLEADOS
SELECT COUNT (DISTINCT Titulo) FROM RepVentas
--MUESTRA PEDIDOS REALIZADOS ENTRE 15 DE ENERO Y
--15 DE FEBRERO 2007
SELECT Num_pedido, convert(varchar,Fecha_pedido,103) AS 'Fecha'
FROM Pedidos WHERE Fecha_pedido BETWEEN '01-15-2007' AND '02-15-2007'
SUBCONSULTAS
Una subconsulta viene a ser una consulta embebida dentro de otra, a
la cual se denomina consulta principal ó consulta primaria. Normalmente se encuentran en las cláusulas de filtro (WHERE ó HAVING).
Se debe insertar entre paréntesis y debe cumplir algunas reglas: 1. No debe retornar más de una columna, pero sí varias filas
2. No debe emplear cláusula UNION
3. Puede emplear columnas de la tabla de la consulta principal.
Muchas veces una subconsulta puede ser reemplazada por consultas multitabla.
Ejemplos (EMPLEANDO LA BD VENTAS DE CLASE)
UNIVERSIDAD NACIONAL MAYOR DE SAN MARCOS
Facultad de Ingeniería Industrial - CEUPS Informática
Curso: Administración de Base de Datos - I
Docente: Ing. Oscar Alberto Barnett Contreras – [email protected] Página 58 de 72
--MUESTRA EL NOMBRE DE LOS EMPLEADOS CUYA CUOTA
--ES MAYOR O IGUAL AL MONTO DEL OBJETIVO DE LA
--CIUDAD DE ATLANTA
SELECT nombre FROM repventas
WHERE cuota>=(SELECT objetivo FROM oficinas
WHERE ciudad='Atlanta')
--MUESTRA LOS CLIENTES QUE SON ATENDIDOS POR
--EL EMPLEADO 'Bill Adams'
SELECT empresa FROM clientes
WHERE rep_clie=(SELECT num_empl FROM repventas
WHERE nombre='Bill Adams')
--MUESTRA LOS EMPLEADOS DE LAS OFICINAS QUE HAN
--CUMPLIDO SU OBJETIVO
SELECT nombre FROM repventas
WHERE oficina_rep in (SELECT oficina
FROM oficinas WHERE ventas>objetivo)
--MUESTRA LOS CLIENTES QUE HAN PEDIDO UN DETREMINADO
--PRODUCTO DENTRO DE UN CIERTO PERIODO
SELECT empresa FROM clientes
WHERE num_clie in (SELECT distinct clie
FROM pedidos WHERE fab='ACI'
and producto like '4100%' and fecha_pedido between '01-oct-2006' and '31-oct-2006')
--MUESTRA LOS PRODUCTOS QUE SE ENCUENTRAN EN
--PEDIDO DE AL MENOS 25000
SELECT distinct descripcion
FROM productos WHERE exists(SELECT num_pedido
FROM pedidos WHERE producto=id_producto
and fab=id_fab and importe>=25000)
UNIVERSIDAD NACIONAL MAYOR DE SAN MARCOS
Facultad de Ingeniería Industrial - CEUPS Informática
Curso: Administración de Base de Datos - I
Docente: Ing. Oscar Alberto Barnett Contreras – [email protected] Página 59 de 72
--MUESTRA EMPLEADOS CON PEDIDO REGISTRADO DE AL MENOS
--10% DE SU CUOTA
SELECT nombre
FROM repventas WHERE (0.10*cuota)< any (SELECT importe
FROM pedidos WHERE rep=num_empl)
--EJEMPLO EN LA BD NORTHWIND DE SQL SERVER --MUESTRA PRODUCTOS CUYO PRECIO DE VENTA EN UN
--PEDIDO ES MENOR AL 50% DE SU PRECIO EN INVENTARIO
SELECT productid, productname FROM products p
WHERE unitprice < any (SELECT od.unitprice FROM [order details] od
WHERE od.productid=0.5*p.productid)
--MUESTRA LAS OFICINAS DONDE TODOS LOS EMPLEADOS
--HAN VENDIDO POR MONTO MAYOR AL 50% DEL OBJETIVO
SELECT ciudad, objetivo FROM oficinas
WHERE (0.50*objetivo)< all (SELECT ventas
FROM repventas WHERE oficina_rep=oficina)
EJERCICIOS CON LA BD VENTAS 1.- Muestre los pedidos y sus respectivos clientes
2.- Obtenga
a) Pedidos sin clientes
b) Clientes sin pedidos
3.- Muestre
a) Límites de importes de pedidos
b) Límites de importes de pedidos 2007
c) Cantidad pedidos con importe menor al importe
promedio de pedidos
d) Clientes cuya empresa inicia con a
e) Muestre el impuesto igv promedio de pedidos de clientes
4.- Pedidos que corresponden al día y mes actuales
5.- Crear tablas y llenar con pedidos de importe > importe promedio y con
pedidos de importe < al importe promedio
6.-Mostrar el importe incrementado en 10% para los pedidos correspondientes
al primer semestre y en 5% para los pedidos del segundo semestre del año
2007
7.- Muestra el total de importe de pedidos por cada año
8.- Reasignar los pedidos del cliente ‘Zetacorp’ al cliente ‘Orion corp.’
9.- Eliminar al cliente ‘Zetacorp’
UNIVERSIDAD NACIONAL MAYOR DE SAN MARCOS
Facultad de Ingeniería Industrial - CEUPS Informática
Curso: Administración de Base de Datos - I
Docente: Ing. Oscar Alberto Barnett Contreras – [email protected] Página 60 de 72
Capítulo III - MANEJO DE VISTAS DE DATOS
Creación de Vistas
Una vista es una estructura lógica la cual muestra información de
varias tablas relacionadas, a este objeto también se le denomina “Tabla Virtual”.
CREATE VIEW
Esta instrucción permite crear una vista estructura la cual contendrá
registros de varias tablas relacionadas, la data se obtiene con una instrucción de selección.
Create View <nombre> as instrucción de selección
Ejemplo:
USE pubs IF EXISTS (SELECT TABLE_NAME FROM INFORMATION_SCHEMA.VIEWS
WHERE TABLE_NAME = 'accounts') DROP VIEW accounts
GO
CREATE VIEW accounts (title, advance, amt_due)
WITH ENCRYPTION AS
SELECT title, advance, price * royalty * ytd_sales FROM titles
WHERE price > 5 GO
ALTER VIEW
Esta instrucción permite modificar la estructura de una vista ya creada
Formato:
Alter View <nombre_vista> as instrucción de selección
DROP VIEW
Esta instrucción permite eliminar una vista ya creada
Formato:
Drop View <nombre_vista>
UNIVERSIDAD NACIONAL MAYOR DE SAN MARCOS
Facultad de Ingeniería Industrial - CEUPS Informática
Curso: Administración de Base de Datos - I
Docente: Ing. Oscar Alberto Barnett Contreras – [email protected] Página 61 de 72
Ejemplos: (EMPLEAR LA BD NEPTUNO)
--VISTA CON SUBCONSULTA
--VISTA DE LOS CLIENTES CON PEDIDOS REALIZADOS EN 1996
CREATE VIEW ClientesConPedidos1996 AS
SELECT clientes.NombreCompañía, Clientes.Teléfono
FROM Clientes WHERE exists(SELECT * from pedidos
WHERE pedidos.Idcliente=clientes.Idcliente AND Year(FechaPedido)=1996);
--PRUEBA LA VISTA
SELECT * FROM ClientesConPedidos1996;
--OTRA PRUEBA
SELECT NombreCompañía FROM ClientesConPedidos1996:
--VISTA DE TABLAS COMBINADAS
-- VISTA Pedidos1996 DE LOS PEDIDOS DEL AÑO 1996 UTILIZANDO LAS -- TABLAS PEDIDOS, CLIENTES y EMPLEADOS
CREATE VIEW Pedidos1996 AS
SELECT Idpedido, FechaPedido, pedidos.Cargo, Nombrecompañía AS ‘clientes’,
Nombre+Apellidos AS ‘empleado’, FechaEnvío,
FechaEntrega FROM pedidos
INNER JOIN clientes ON pedidos.Idcliente=clientes.Idcliente
INNER JOIN empleados ON pedidos.Idempleado=empleados.Idempleado WHERE year(FechaPedido)=1996;
--PRUEBA LA VISTA
SELECT * FROM pedidos1996;
--OTRA PRUEBA --VISUALIZA LOS CAMPOS IDPEDIDO, CARGO, CLIENTES
--DE LA VISTA PEDIDOS1996
SELECT Idpedido, Cargo, Clientes FROM pedidos1996;
UNIVERSIDAD NACIONAL MAYOR DE SAN MARCOS
Facultad de Ingeniería Industrial - CEUPS Informática
Curso: Administración de Base de Datos - I
Docente: Ing. Oscar Alberto Barnett Contreras – [email protected] Página 62 de 72
-- CREAR UNA VISTA CON EL NOMBRE ProdCatProv
--QUE PERMITA VISUALIZAR LOS CAMPOS O COLUMNAS
--NombreProducto y PrecioUnidad DE LA TABLA PRODUCTOS --NombreCategoria DE LA TABLA CATEGORIAS
--NombreCompañia DE LA TABLA PROVEEDORES
CREATE VIEW ProdCatProv AS
SELECT NombreProducto,
PrecioUnidad, categorías.NombreCategoría,
proveedores.NombreCompañía FROM productos
INNER JOIN categorías ON productos.idcategoría = categorías.idcategoría INNER JOIN proveedores ON productos.idproveedor =
proveedores.idproveedor;
--PRUEBA LA VISTA SELECT * from ProdCatProv;
--CREAR UNA VISTA EmpleadoPedido QUE MUESTRE
--DATOS DEL PEDIDO Y EL EMPLEADO CORRESPONDIENTE
CREATE VIEW EmpleadoPedido
AS SELECT pedidos.idpedido, apellidos+nombre AS Emple, empleados.cargo
FROM empleados INNER JOIN pedidos on empleados.idempleado=pedidos.idempleado;
--PRUEBA LA VISTA
SELECT * from empleadopedido;
--EJEMPLO DE UNA VISTA QUE UNE TRES TABLAS
CREATE VIEW CurPorCic( Id, Ciclo, Curso, Profesor, Horario, V_M, Estado
) AS
SELECT CP.IdCursoProg,
CP.IdCiclo, C.NomCurso,
P.ApeProfesor + Space(1) + P.NomProfesor, CP.Horario, Convert(VarChar(3),CP.Vacantes) + '/' + Convert(VarChar(3), CP.Matriculados),
Case CP.Activo
when 0 then 'Cancelado'
when 1 then 'Activo' End
FROM Profesor P INNER JOIN CursoProgramado CP ON P.IdProfesor = CP.IdProfesor INNER JOIN Curso C
ON CP.IdCurso = C.IdCurso;
--PRUEBA LA VISTA
SELECT * FROM CurPorCic;
UNIVERSIDAD NACIONAL MAYOR DE SAN MARCOS
Facultad de Ingeniería Industrial - CEUPS Informática
Curso: Administración de Base de Datos - I
Docente: Ing. Oscar Alberto Barnett Contreras – [email protected] Página 63 de 72
Capítulo IV - OBJETOS DE PROGRAMACIÓN
PROCEDIMIENTOS ALMACENADOS
Un procedimiento almacenado es un conjunto de instrucciones transact sql las cuales se ejecutan siguiendo una secuencia lógica, los
procedimientos almacenados pueden recibir y devolver parámetros.
Formato:
CREATE PROC[EDURE] nombreProcedimiento
@parámetro tipoDatos} [VARYING] [= predeterminado] [OUTPUT] AS
instrucciónSQL [...n]
Ejemplos: (DEBE DISPONER DE LA BD VENTAS IMPLEMENTADA EN CLASE)
--PROCEDIMIENTO ALMACENADO SIN PARAMETROS
--pa_infopedidos QUE MUESTRA NUMERO DE PEDIDO, NOMBRE DEL --CLIENTE Y NOMBRE DEL EMPLEADO, DE TODOS LOS PEDIDOS
CREATE PROCEDURE pa_infopedidos
AS SELECT num_pedido, empresa, Nombre
FROM (clientes INNER JOIN pedidos ON clientes.num_clie = pedidos.clie) INNER JOIN repventas
ON pedidos.rep = repventas.num_empl
GO EXECUTE pa_infopedidos;
--PROCEDIMIENTO ALMACENADO CON PARAMETRO DE ENTRADA
--pa_infocliente QUE ACEPTA UN NUMERO DE CLIENTE --Y MUESTRA SU NOMBRE Y LIMITE DE CREDITO
CREATE PROCEDURE pa_infocliente @num_clie int AS
SELECT empresa, limite_credito
FROM clientes WHERE num_clie=@num_clie;
EXECUTE pa_infocliente 2123;
--PROCEDIMIENTO ALMACENADO CON PARAMETRO DEFAULT --pa_infoejecutivos QUE ACEPTA UN TITULO PARA
--MOSTRAR LOS DATOS DE LOS EJECUTIVOS CORRESPONDIENTES
-- O SINO MUESTRA DATOS DE LOS DIRECTORES
CREATE PROCEDURE pa_infoejecutivos @titulo varchar(10) = 'Dir%' AS
SELECT num_empl, nombre, Titulo FROM repventas
WHERE Titulo like @titulo; EXECUTE pa_infoejecutivos;
UNIVERSIDAD NACIONAL MAYOR DE SAN MARCOS
Facultad de Ingeniería Industrial - CEUPS Informática
Curso: Administración de Base de Datos - I
Docente: Ing. Oscar Alberto Barnett Contreras – [email protected] Página 64 de 72
EXECUTE pa_infoejecutivos 'Rep Ventas';
--PROCEDIMIENTO ALMACENADO CON DOS PARAMETROS DE ENTRADA --EMPLEANDO LA BD PUBS DE SQL SERVER
--pa_infoautor QUE ACEPTA EL NOMBRE Y APELLIDOS DE UN AUTOR
--PARA MOSTRAR EL TITULO Y EDITOR DE CADA UNO DE LOS --LIBROS QUE EL AUTOR HA PUBLICADO
CREATE PROCEDURE pa_infoautor @ape varchar(40), @nom varchar(20) AS
SELECT au_lname, au_fname, title, pub_name FROM authors, titles, publishers, titleauthor
WHERE au_fname=@nom AND au_lname=@ape
AND authors.au_id=titleauthor.au_id AND titles.title_id=titleauthor.title_id
AND titles.pub_id=publishers.pub_id;
--PRUEBA EL PA EXECUTE pa_infoautor 'Karsen', 'Livia';
--PROCEDIMIENTO ALMACENADO CON PARAMETRO DEFAULT
--pa_infoautor2 QUE ACEPTA EL NOMBRE DE UN EDITOR --PARA MOSTRAR EL NOMBRE DE LOS AUTORES QUE HAN
--PUBLICADO UN LIBRO CON ESE EDITOR
--O SINO MUESTRA LOS AUTORES DEL EDITOR 'Algodata Infosystems'
CREATE PROCEDURE pa_infoautor2 @editor varchar(40)='Algodata Infosystems'
AS SELECT au_lname, au_fname, pub_name
FROM authors a, titles t, publishers p, titleauthor ta WHERE p.pub_name=@editor
AND a.au_id=ta.au_id
AND t.title_id=ta.title_id AND t.pub_id=p.pub_id
GO EXECUTE pa_infoautor2 'Binnet & Hardley';
--PROCEDIMIENTO ALMACENADO QUE MUESTRA INFORMACION
--SOBRE LOS LIBROS DE UN DETERMINADO AUTOR IF EXISTS (SELECT name FROM sysobjects
WHERE name = 'au_info' AND type = 'P')
DROP PROCEDURE au_info GO
CREATE PROCEDURE au_info @lastname varchar(40), @firstname varchar(20)
AS SELECT au_lname, au_fname, title, pub_name
FROM authors a INNER JOIN titleauthor ta
ON a.au_id = ta.au_id INNER JOIN titles t ON t.title_id = ta.title_id INNER JOIN publishers p
ON t.pub_id = p.pub_id WHERE au_fname = @firstname
UNIVERSIDAD NACIONAL MAYOR DE SAN MARCOS
Facultad de Ingeniería Industrial - CEUPS Informática
Curso: Administración de Base de Datos - I
Docente: Ing. Oscar Alberto Barnett Contreras – [email protected] Página 65 de 72
AND au_lname = @lastname;
--EL PROCEDIMIENTO ALMACENADO au_info SE PUEDE EJECUTAR DE --ESTAS FORMAS:
EXECUTE au_info 'Dull', 'Ann' -- O
EXECUTE au_info @lastname = 'Dull', @firstname = 'Ann' -- O
EXECUTE au_info @firstname = 'Ann', @lastname = 'Dull'
-- O EXEC au_info 'Dull', 'Ann'
-- O EXEC au_info @lastname = 'Dull', @firstname = 'Ann'
-- O EXEC au_info @firstname = 'Ann', @lastname = 'Dull'
--O SI ESTE PROCEDIMIENTO ES LA PRIMERA INSTRUCCIÓN DEL -- PROCESO POR LOTES:
au_info 'Dull', 'Ann'
-- O au_info @lastname = 'Dull', @firstname = 'Ann'
-- O au_info @firstname = 'Ann', @lastname = 'Dull'
--PROCEDIMIENTO ALMACENADO QUE MUESTRA INFORMACION
--SOBRE LOS LIBROS Y EDITOR DE UN DETERMINADO AUTOR
--CUYO APELLIDO INICIA CON LETRA D IF EXISTS (SELECT name FROM sysobjects
WHERE name = 'au_info2' AND type = 'P') DROP PROCEDURE au_info2
GO CREATE PROCEDURE au_info2
@lastname varchar(30) = 'D%', @firstname varchar(18) = '%'
AS
SELECT au_lname, au_fname, title, pub_name FROM authors a INNER JOIN titleauthor ta
ON a.au_id = ta.au_id INNER JOIN titles t ON t.title_id = ta.title_id INNER JOIN publishers p
ON t.pub_id = p.pub_id WHERE au_fname LIKE @firstname
AND au_lname LIKE @lastname GO
--EL PROCEDIMIENTO ALMACENADO au_info2 SE PUEDE EJECUTAR --EN MUCHAS COMBINACIONES. AQUÍ SE MUESTRAN SÓLO ALGUNAS
--COMBINACIONES:
EXECUTE au_info2 -- O
EXECUTE au_info2 'Wh%'
-- O EXECUTE au_info2 @firstname = 'A%'
-- O EXECUTE au_info2 '[CK]ars[OE]n'
UNIVERSIDAD NACIONAL MAYOR DE SAN MARCOS
Facultad de Ingeniería Industrial - CEUPS Informática
Curso: Administración de Base de Datos - I
Docente: Ing. Oscar Alberto Barnett Contreras – [email protected] Página 66 de 72
-- O
EXECUTE au_info2 'Hunter', 'Sheryl'
-- O EXECUTE au_info2 'H%', 'S%'
--OTROS EJEMPLOS (EMPLEA LA BD MATRICULA):
1. if exists(SELECT name FROM sysobjects
WHERE name='sp_ins_alu' and type ='p')
begin DROP PROCEDURE sp_ins_alu
end CREATE PROCEDURE sp_ins_alu @id char(8), @ape char(30),@nom char(30)
AS INSERT INTO alumno(idalumno,apealumno,nomalumno)
VALUES (@id,@ape,@nom)
2. if exists(SELECT name FROM sysobjects
WHERE name='sp_list_alu' and type ='p') begin
DROP PROCEDURE sp_list_alu end
CREATE PROCEDURE sp_list_alu AS
SELECT apealumno, fecmatricula
FROM alumno a inner join matricula m ON a.idalumno=m.idalumno
3. CREATE PROCEDURE sp_Busca_alu @id Char(8)
AS SELECT idalumno, apealumno, nomalumno
FROM alumno WHERE (Idalumno = @id)
4. if exists(SELECT name FROM sysobjects WHERE name='sp_act_alu' and type ='p')
begin DROP PROCEDURE sp_act_alu
end CREATE PROCEDURE sp_act_alu @id char(8), @ape char(30),@nom char(30)
AS UPDATE alumno SET idalumno = @id ,
apealumno = @ape ,
nomalumno = @nom WHERE idalumno = @id
5. if exists(SELECT name FROM sysobjects
WHERE name='sp_eli_alu' and type ='p') begin
DROP PROCEDURE sp_eli_alu
end CREATE PROCEDURE sp_eli_alu @id char(8)
AS DELETE FROM alumnos
UNIVERSIDAD NACIONAL MAYOR DE SAN MARCOS
Facultad de Ingeniería Industrial - CEUPS Informática
Curso: Administración de Base de Datos - I
Docente: Ing. Oscar Alberto Barnett Contreras – [email protected] Página 67 de 72
WHERE idalumno=@id
FUNCIONES DEFINIDAS POR EL USUARIO (FDU)
Es un conjunto de instrucciones parecido al procedimiento
almacenado, con la diferencia que puede retornar un valor calculado como resultado de su ejecución.
También puede recibir parámetros de trabajo para definir su proceso de cálculo. El valor final que se retorna, debe ser indicado mediante una
instrucción RETURN necesariamente.
CREATE FUNC[TION] NombreFuncionUsuario (@parámetro TipoDato) RETURNS <TipoDatoRetorno>
AS
BEGIN …
Instrucciones SQL ...
RETURN <ExpRetorno> END
Ejemplos (EN LA BD VENTAS DE CLASE)
--FDU QUE RETORNA VALOR ESCALAR --CREA UNA FDU FunEmail() QUE GENERA UNA CUENTA DE CORREO
--ELECTRONICO PARA UN REP DE VENTAS EN hotmail.com
CREATE FUNCTION FunEmail (@Num_Empl int) RETURNS varchar(60)
AS
BEGIN DECLARE @cuenta varchar(60)
SELECT @cuenta = Nombre + ' @hotmail.com' FROM REPVENTAS
WHERE Num_Empl=@Num_Empl RETURN @cuenta
END
GO
--PRUEBA FDU FunEmail() SELECT dbo.FunEmail(102) as 'Cuenta Email'
GO
--OTRA PRUEBA DECLARE @C varchar(60)
SET @C = dbo.FunEmail(102)
SELECT @C AS 'Cuenta Email' GO
--ELIMINA FDU Funemail
DROP FUNCTION FunEmail GO
UNIVERSIDAD NACIONAL MAYOR DE SAN MARCOS
Facultad de Ingeniería Industrial - CEUPS Informática
Curso: Administración de Base de Datos - I
Docente: Ing. Oscar Alberto Barnett Contreras – [email protected] Página 68 de 72
--EJEMPLO DE FDU QUE RETORNA VALORES TABLE
--CREA FDU FunRepNY QUE LISTA LOS REP VENTAS DE --UNA DETERMINADA OFICINA
CREATE FUNCTION FunRepNY (@ofi int)
RETURNS table AS
RETURN (SELECT Num_Empl, Nombre FROM REPVENTAS
where Oficina_Rep=@Ofi) GO
--PRUEBA FDU FunRepNY()
SELECT * FROM dbo.FunRepNY(11)
GO --OTRA PRUEBA
SELECT *
FROM dbo.FunRepNY(11) WHERE Nombre like 'S%'
GO
--EJEMPLO DE FDU QUE SE EMPLEA PARA CREAR --COLUMNA CALCULADA EN UNA TABLA
CREATE FUNCTION FunJornal(@HL int, @TH MONEY) RETURNS MONEY
AS BEGIN
RETURN (@HL * @TH) END
GO
--PRUEBA FDU FunJornal()
--CREA TABLA PLANILLA CON COLUMNA CALCULADA CREATE TABLE PLANILLA
(cod int,hor_lab int, tar_hor money, Jornal as dbo.FunJornal(hor_lab,tar_hor)) GO
--AGREGA FILA EN PLANILLA
INSERT INTO Planilla VALUES (1,12,15.00)
GO
--MUESTRA DATOS DE FILA SELECT *
FROM PLANILLA GO
UNIVERSIDAD NACIONAL MAYOR DE SAN MARCOS
Facultad de Ingeniería Industrial - CEUPS Informática
Curso: Administración de Base de Datos - I
Docente: Ing. Oscar Alberto Barnett Contreras – [email protected] Página 69 de 72
DESENCADENADORES (TRIGGERS) EN SQL SERVER
Un desencadenador es un conjunto de instrucciones que se ejecuta automáticamente al momento de realizar una inserción, eliminación o
actualización en una tabla. A diferencia de los procedimientos
almacenados y funciones de usuario, no admiten parámetros de trabajo, ni retornan resultado alguno
Sentencias:
Create Trigger.- Crea un desencadenador
Alter Trigger.- Modifica un desencadenador
Drop Trigger.- Elimina un desencadenador
Ejemplos (EMPLEANDO LA BD VENTAS IMPLEMENTADA EN CLASE)
--TRIGGER DE ACTUALIZACIÓN
--CUANDO SE AGREGA UN NUEVO PEDIDO A LA TABLA PEDIDOS --ESTOS DOS CAMBIOS DEBEN TENER LUGAR:
-- 1.- LA COLUMNA VENTAS DEL VENDEDOR QUE ATENDIO
-- EL PEDIDO DEBE AUMENTAR EN EL IMPORTE DEL PEDIDO -- 2.- EL VALOR DE EXISTENCIAS PARA EL PRODUCTO ORDENADO
-- DEBERIA DISMINUIR EN LA CANTIDAD SOLICITADA
CREATE TRIGGER TR_NUEVOPEDIDO ON PEDIDOS
AFTER INSERT AS
UPDATE REPVENTAS SET VENTAS=VENTAS + INSERTED.IMPORTE
FROM REPVENTAS, INSERTED WHERE REPVENTAS.NUM_EMPL=INSERTED.REP UPDATE PRODUCTOS SET EXISTENCIAS = EXISTENCIAS - INSERTED.CANT
FROM PRODUCTOS, INSERTED
WHERE PRODUCTOS.ID_FAB = INSERTED.FAB
AND PRODUCTOS.ID_PRODUCTO=INSERTED.PRODUCTO GO
--DATO ACTUAL EN REP_VENTAS
SELECT * FROM REPVENTAS --Num_empl Nombre Edad Oficina_Rep Titulo Contrato Director Cuota Ventas
--101 Dan Roberts 45 12 Rep Ventas 1996-10-20 104 300000 305673
--DATO ACTUAL EN PRODUCTOS SELECT * FROM PRODUCTOS
-- Id_Fab Id_Producto Descripcion Precio existencias Cant_Min -- ACI 41001 Articulo tipo 1 55.00 277 0
--PEDIDO A REGISTRAR EN TABLA PEDIDO
--EMPLEADO 101, PROD 'ACI' + '41001' CANTIDAD 7 IMPORTE 385.00 INSERT INTO PEDIDOS
VALUES (113071, getdate(), 2101, 101, 'ACI', '41001', 7, 385.00)
GO --VERIFICA PEDIDO INSERTADO
UNIVERSIDAD NACIONAL MAYOR DE SAN MARCOS
Facultad de Ingeniería Industrial - CEUPS Informática
Curso: Administración de Base de Datos - I
Docente: Ing. Oscar Alberto Barnett Contreras – [email protected] Página 70 de 72
SELECT * FROM PEDIDOS
GO
--SE ESPERA QUE LOS NUEVOS DATOS DE REP_VENTAS 101 SEAN --Num_empl Nombre Edad Oficina_Rep Titulo Contrato Director Cuota Ventas
-- 101 Dan Roberts 45 12 Rep Ventas 1996-10-20 104 300000 306058 SELECT * FROM REPVENTAS GO
--SE ESPERA QUE LOS NUEVOS DATOS DE PRODUCTOS SEAN
-- Id_Fab Id_Producto Descripcion Precio existencias Cant_Min -- ACI 41001 Articulo tipo 1 55.00 270 0
SELECT * FROM PRODUCTOS GO
--EJEMPLO DE INTEGRIDAD REFERENCIAL PARA LA RELACION
--ENTRE TABLAS OFICINAS / REPVENTAS, MOSTRANDO UN MENSAJE --CUANDO UNA ACTUALIZACION DE LA TABLA REPVENTAS FALLA
CREATE TRIGGER TR_ACTUALIZA_REPVENTAS ON REPVENTAS
AFTER INSERT,UPDATE AS
IF((SELECT OFICINAS.VENTAS FROM OFICINAS, INSERTED
WHERE OFICINAS.OFICINA=INSERTED.OFICINA_REP)=0) BEGIN
PRINT('Numero de oficina inválido')
ROLLBACK TRANSACTION END
--DATO ACTUAL EN OFICINAS
SELECT * FROM OFICINAS --Oficina Ciudad Region Dir Objetivo Ventas
-- 11 575000 692637
-- 12 800000 735042 -- 13 350000 367911
-- 21 725000 835915 -- 22 300000 186042
--DATO ACTUAL EN REP_VENTAS
SELECT * FROM REPVENTAS --Num_empl Nombre Edad Oficina_Rep Titulo Contrato Director Cuota Ventas
-- 102 Sue Smith 48 21 Rep Ventas 1996-10-12 108 350000 474050
--SE MODIFICARA A LA EMPLEADA SUE SMITH ASIGNANDOLA --A LA OFICINA 15
UPDATE REPVENTAS SET Oficina_Rep= 21
WHERE Num_Empl = 102 GO
-- VERIFICA LA ACTUALIZACIÓN SELECT * FROM REPVENTAS --Num_empl Nombre Edad Oficina_Rep Titulo Contrato Director Cuota Ventas
-- 102 Sue Smith 48 21 Rep Ventas 1996-10-12 108 350000 474050 --SE HA VERIFICADO QUE NO SE REALIZÓ LA ACTUALIZACION
UNIVERSIDAD NACIONAL MAYOR DE SAN MARCOS
Facultad de Ingeniería Industrial - CEUPS Informática
Curso: Administración de Base de Datos - I
Docente: Ing. Oscar Alberto Barnett Contreras – [email protected] Página 71 de 72
--AHORA SE MODIFICARÁ A LA EMPLEADA SUE SMITH A LA OFICINA 13 UPDATE REPVENTAS
SET Oficina_Rep= 13 WHERE Num_Empl = 102
GO SELECT * FROM REPVENTAS --Num_empl Nombre Edad Oficina_Rep Titulo Contrato Director Cuota Ventas
-- 102 Sue Smith 48 13 Rep Ventas 1996-10-12 108 350000 474050 --SI SE REALIZÓ LA ACTUALIZACIÓN
--OTROS EJEMPLOS
1. CREATE TRIGGER tr_act_detaven
ON detafact
FOR insert
AS
UPDATE detafact SET subtot=pre_venta * cant
2. CREATE TRIGGER tr_act_fact
ON detafact
FOR insert
AS
UPDATE facturas SET subtotal=(SELECT sum(subtot)
FROM detafact
WHERE facturas.idfact=detafact.idfact)
UPDATE facturas SET igv =subtotal* 0.19
UPDATE facturas SET total =subtotal + igv
3. IF EXISTS(SELECT NAME FROM SYSOBJECTS WHERE
NAME='TR_INS_DIST')
BEGIN
DROP TRIGGER TR_INS_DIST
END
CREATE TRIGGER TR_INS_DIST
ON DISTRITO
FOR INSERT
AS
IF @@ROWCOUNT=0 RETURN
PRINT 'REGISTRO INSERTADO CON EXITO'
ALTER TABLE DISTRITO
DISABLE TRIGGER TR_INS_DIST;
UNIVERSIDAD NACIONAL MAYOR DE SAN MARCOS
Facultad de Ingeniería Industrial - CEUPS Informática
Curso: Administración de Base de Datos - I
Docente: Ing. Oscar Alberto Barnett Contreras – [email protected] Página 72 de 72
4. IF EXISTS(SELECT NAME
FROM MASTER.DBO.SYSOBJECTS
WHERE NAME='TR_TABLA_PROT' AND TYPE='TR')
BEGIN
DROP TRIGGER TR_TABLA_PROT
END
CREATE TRIGGER TR_TABLA_PROT
ON DISTRITO
FOR INSERT, UPDATE, DELETE
AS
RAISERROR ('Mensaje de trigger: tabla protegida', 16, 1)
ROLLBACK TRANSACTION;
5. –EMPLEAR LA BD PUBS DE SQL SERVER
--CREA TRIGGER DE EJECUCION CONDICIONAL
--SI ACTUALIZA LAS COLUMNAS stor_id ó ord_num
-- DE LA TABLA SALES, SE INTERRUMPE LA TRANSACCION
CREATE TRIGGER TR_PRUEBA
ON SALES
AFTER INSERT,UPDATE
AS
IF UPDATE(stor_id) or UPDATE(ord_num)
BEGIN
PRINT ('ACTUALIZACION NO VALIDA...DESCARTANDO...')
ROLLBACK TRANSACTION
END
GO
--DATOS ACTUALES TABLA SALES
SELECT * from sales WHERE stor_id='6380'
-- PRUEBA TRIGGER
INSERT INTO SALES(stor_id,ord_num,ord_date,qty,payterms,title_id)
VALUES('6380','eet','12/12/2007',2,'wer','BU1032')
--DATOS ACTUALES TABLA SALES
SELECT * from sales WHERE stor_id='6380'
--NO SE REALIZÓ LA ACTUALIZACIÓN
*=*=*=*=*=*