Fundamentos de Bases de Datos Relacionales y SQL

Tablas

Campo: Se refiere a la ubicación en un registro en el cual se almacena un tipo específico de datos. Por ejemplo, el REGISTRO-EMPLEADO puede contener campos para almacenar el Apellido, Nombre, Dirección, Ciudad, Estado, CP, Fecha-Contratación, Salario-Actual, Puesto, Departamento, etc. Los campos individuales se caracterizan por su longitud.

Columna: Una columna es un grupo de elementos que se encuentran enlistados hacia arriba y abajo, es decir, de forma vertical.

Fila: Una fila es un grupo de elementos que se encuentran enlistados de un extremo a otro (de izquierda a derecha, es decir, horizontalmente).

Registro: Un registro es un grupo de campos en una fila.

Tabla: Una tabla es un conjunto de filas y columnas.

Variable: El nombre de una variable es la etiqueta que se le asigna a una columna.

Consulta: Conjunto específico de instrucciones para extraer datos particulares. Para extraer datos de una base de datos y presentarlos para su uso.

¿Qué es una base de datos?: Una base de datos es una colección de datos. Un sistema computacional utiliza una base de datos para almacenar y administrar datos.

Relaciones

Entradas: Datos.

Atributos/campos: Características.

Relaciones/claves: Cómo se enlazan los datos.

Claves: Identificadores únicos.

Modelo relacional: Modelo de datos en el cual los datos se encuentran organizados en relaciones (tablas). Este es el modelo implementado en la mayoría de los sistemas administradores de bases de datos modernos.

Relación uno a uno: Asociación entre dos tablas en la cual el valor de la clave principal de cada registro en la tabla primaria corresponde al valor en el campo o campos coincidentes de uno y sólo un registro en la tabla relacionada.

Relación varios a varios: Asociación compleja entre dos conjuntos de parámetros en la cual muchos de los del primer conjunto se pueden relacionar con muchos otros del segundo. Es común utilizar una relación varios a varios para describir una asociación entre dos tablas en la cual un registro de cualquiera de las tablas se puede relacionar con muchos registros de la otra tabla.

Relación padre/hijo: Concerniente o referente a una relación entre los nodos de un árbol (estructura de datos), en la cual el padre se encuentra un paso más cercano a la raíz (es decir, en un nivel superior) que el hijo.

Clave: Una clave principal define una o más columnas que identifican de forma única cada fila de la tabla. Sin embargo, a diferencia de la clave primaria, es posible tener más de una restricción única en una tabla.

Las relaciones (o vínculos) entre las tablas se almacenan como restricciones de claves foráneas/externas.

Clave primaria: Clave que funciona como identificador único de una tupla específica (fila) e identifica de forma única una relación en una base de datos.

Clave foránea: Es una referencia a la clave principal de otra tabla.

Integridad de datos: Consiste en un conjunto de reglas que dictan los tipos de manipulación de datos permitidos en valores referenciados y cómo estas acciones afectan a valores dependientes. La implementación de estas reglas en la base de datos asegura que los datos son consistentes, correctos y que se pueden utilizar en la base de datos.

Clave compuesta: Clave que consiste de 2 o más atributos que identifican de forma única una ocurrencia de entidad. Cada atributo que compone la clave compuesta es una clave simple por méritos propios.

Clave subrogada: Clave que no se genera por los datos de la base de datos sino que es una clave única utilizada como clave principal.

Lenguaje de Manipulación de Datos (DML)

SQL: Es un sublenguaje de base de datos utilizado para realizar consultas, actualizaciones y administrar bases de datos relacionales. Es el estándar para los productos de bases de datos.

DML: Lenguaje de manipulación de datos (DML). En un sistema de gestión de bases de datos (DBMS) es un lenguaje utilizado para insertar datos, actualizarlos y realizar consultas en una base de datos. El lenguaje DML se utiliza para manipular los datos de una base de datos.

SELECT: El comando SELECT es el más utilizado en DML. Permite a los usuarios recuperar información específica de la base de datos.

SELECT * FROM Beca_info WHERE beca_cantidad > 36000

INSERT: El comando INSERT se utiliza para agregar registros a una tabla existente.

INSERT INTO Beca_info VALUES (‘Juan’, ‘Pérez’,12345,2200)

INSERT INTO nombre_tabla(columna1,columna2) VALUES (valor1a,valor1b), (valor2a, valor2b)

UPDATE: El comando UPDATE se puede utilizar para modificar información contenida en una tabla, ya sea datos individuales o grupos de datos.

UPDATE Beca_info SET beca_cantidad = beca_cantidad + 4000 WHERE id_alumno = 12345

UPDATE Universidades SET Cuotas= Cuotas* 2

DELETE: El comando DELETE se utiliza para eliminar registros de una tabla existente.

DELETE FROM Beca_info WHERE id_alumno = 12345

DDL: (Lenguaje de definición de datos): Lenguaje que define todos los atributos y propiedades de una base de datos, especialmente la presentación de los registros, la definición de campos, claves, ubicaciones de archivo y estrategias de almacenamiento.

CREATE: Hay dos modalidades de la sentencia CREATE.

CREATE DATABASE Alumnos

CREATE TABLE beca_info (nombre char(20) not null, apellido char(20) not null, id_alumno int not null, beca_cantidad int not null)

ALTER: Cambia la estructura de la tabla.

ALTER TABLE Beca_info ADD Federal int null

ALTER TABLE Beca_info ALTER apellido VARCHAR(35)

DROP: La sentencia DROP TABLE elimina la tabla y todos sus datos.

DROP TABLE Beca_info

Tipos de Datos

Tipo de dato: Se refiere al conjunto de datos que especifican el rango posible de valores del mismo, las operaciones que se pueden llevar a cabo con los valores y la forma en la cual se almacenan los valores en memoria.

Real, Entero: Tipo entero es cualquier número sin decimales.

Punto flotante: Punto flotante es cualquier número real.

Carácter: Una letra, símbolo de puntuación u otro símbolo o código de control que se representa en una computadora por una unidad (1 byte) de información con la cual no se puede realizar una función matemática.

Booleano: El tipo booleano es únicamente, Verdadero o Falso, 1 o 0.

Apuntador

A continuación se presentan 3 tipos de datos numéricos enteros diferentes:

Byte: Contiene números de 0 a 255 en 1 byte de almacenamiento.

Integer: Contiene números enteros entre -32,768 y 32,767 en 2 bytes de almacenamiento.

Long: Contiene números enteros entre -2,147,483,648 y 2,147,483,647 en 4 bytes de almacenamiento.

Caracteres:

Char (tamaño): Contiene una cadena de longitud fija. El tamaño fijo se especifica entre paréntesis. Contiene un máximo de 8,000 caracteres.

Varchar (tamaño): Contiene una cadena de longitud variable. El tamaño máximo se especifica entre paréntesis. Contiene un máximo de 8,000 caracteres.

Texto: Contiene una cadena de caracteres de longitud variable. El tamaño máximo es de 2 GB de datos de texto.

SQL, T-SQL y Vistas

¿Qué es la sintaxis ANSI SQL?: El Lenguaje de Consulta Estructurado (SQL) se diseñó para administrar bases de datos relacionales.

¿Qué es T-SQL?: Transact-SQL (T-SQL) es una extensión del lenguaje de programación de base de datos SQL. T-SQL es un dialecto sofisticado de SQL que cuenta con herramientas adicionales a las definidas en el estándar ANSI SQL-92.

¿Qué es una vista?: Una consulta guardada que crea una tabla virtual a partir del conjunto resultante de una consulta.

CREATE VIEW graduados AS SELECT id_alumno, nombre_alumno FROM alumnos_inscritos

Creación de vista con condiciones

CREATE VIEW graduados AS SELECT id_alumno, nombre_alumno FROM alumnos_inscritos WHERE Sexo = ‘F’

Funciones

¿Qué es una función?: Cualquier sección de código que se puede invocar (ejecutar) dentro de un programa. Una rutina generalmente tiene un nombre (identificador) asociado al mismo y se ejecuta al hacerse referencia a ese nombre.

Funciones de Agregado

AVG() – Regresa un valor promedio
SELECT AVG (nombre_columna) FROM nombre_tabla

COUNT() – Regresa el número de filas
SELECT COUNT (nombre_columna) FROM nombre_tabla

FIRST() – Regresa el primer valor
SELECT FIRST (nombre_columna) FROM nombre_tabla

LAST() – Regresa el último valor
SELECT LAST (nombre_columna) FROM nombre_tabla

MAX() – Regresa el valor mayor
SELECT MAX (nombre_columna) FROM nombre_tabla

MIN() – Regresa el valor menor
SELECT MIN (nombre_columna) FROM nombre_tabla

SUM() – Regresa la suma
SELECT SUM (nombre_columna) FROM nombre_tabla

_{Función: Comando de una sola palabra que regresa un solo valor.}

_{Funciones de agregado: Funciones que regresan un solo valor calculado de los valores de una columna.}

_{Funciones escalares: Las funciones escalares regresan un solo valor basado en el valor ingresado. Las funciones escalares no requieren de ningún valor ingresado (por ejemplo, la fecha actual).}

_{Funciones definidas por el usuario: Código SQL compacto, escrito por el usuario, que puede aceptar parámetros y regresan ya sea un valor o una tabla.}

_{Procedimiento almacenado: Grupo pre-compilado de sentencias SQL guardadas en una base de datos.}

UCASE() – Convierte el texto de un campo en mayúsculas
SELECT UCASE (nombre_columna) FROM nombre_tabla

LCASE() – Convierte el texto de un campo en minúsculas
SELECT LCASE (nombre_columna) FROM nombre_tabla

MID() – Selecciona los caracteres de un campo de texto
SELECT MID (nombre_columna) FROM nombre_tabla

LEN() – Regresa la longitud de un campo de texto
SELECT LEN (nombre_columna) FROM nombre_tabla

ROUND() – Redondea un campo numérico al número de decimales especificados
SELECT ROUND (nombre_columna, decimales) FROM nombre_tabla

Para crear una función:

CREATE FUNCTION [Nombre función] ([variables de entrada])
RETURNS varchar(10)
AS
BEGIN
DECLARE [variable de salida] varchar(10)
RETURN [variable de salida]
END

Para llamar a una función:

SELECT [Nombre función] ([variables de entrada])

Para eliminar una función:

DROP FUNCTION [Nombre de función]

Procedimientos Almacenados

Procedimientos de almacenamiento: Grupo pre-compilado de sentencias SQL escritas por usuarios y guardadas en una base de datos.

CREATE PROCEDURE [sp_Nombre procedimiento]
AS
SELECT * FROM [Tabla]

CREATE PROCEDURE sp_clase
@campusubicacion varchar(30)
AS
SELECT NombreClase, SeccionClase
FROM SemestreActual
WHERE campus = @campusubicacion

Llamar procedimiento:

EXEC [nombre procedimiento] ([variables entrada])
EXECUTE sp_clase ‘Central’

Consultas Avanzadas

SELECT DISTINCT Profesor FROM Tabla_Calificaciones

SELECT * FROM tabla_calificaciones ORDER BY profesor ASC o DESC

Sub-consultas:

Tipo 1 – Predicado: Constructores lógicos extendidos en la cláusula(s) WHERE (y HAVING) utilizando los operadores AND, OR, LIKE, BETWEEN, AS y TOP (LIMIT). Ejemplo:

SELECT nombre_columna(s)
FROM nombre_tabla
WHERE nombre_columna LIKE patron

SELECT materia
FROM info_clase
WHERE profesor LIKE ‘Sm’

Tipo 2 – Escalar: Consultas autónomas que regresan un solo valor. Las subconsultas escalares se pueden utilizar en expresiones CASE, cláusulas WHERE, ORDER BY y SELECT.

SELECT nombre_columna(s)
FROM nombre_tabla
WHERE variable_1 = (SELECT nombre_columna
FROM nombre_tabla
WHERE nombre_columna= variable_2)

Tipo 3 – Tabla: Consultas anidadas en la cláusula FROM.

SELECT nombre_tabla_1
FROM nombre_tabla_1, (SELECT nombre_columna_2
FROM nombre_tabla_2
WHERE nombre_columna_3 = variable_1)
WHERE nombre_tabla_1.nombre_columna_1 = nombre_tabla_2.nombre_columna_2

UNION: La cláusula UNION combina los resultados de dos consultas SQL en una sola tabla de todas las filas coincidentes. Las dos consultas deben tener el mismo número de columnas y tipos de datos compatibles para poder unirse. Los registros duplicados se remueven de forma automática a menos que se utilice UNION ALL.

SELECT nombre_columna(s) FROM nombre_tabla1
UNION
SELECT nombre_columna(s) FROM nombre_tabla2
No permite duplicados.

SELECT nombre_columna(s) FROM nombre_tabla1
UNION ALL
SELECT nombre_columna(s) FROM nombre_tabla2

JOIN: La palabra clave INNER JOIN arroja las filas en las cuales hay al menos una coincidencia en ambas tablas. JOIN arroja las filas en las cuales el valor en nombre_columna en la nombre_tabla1 coincide con el valor en nombre_columna en nombre_tabla2.

SELECT nombre_columna(s)
FROM nombre_tabla1
INNER JOIN nombre_tabla2
ON nombre_tabla1.nombre_columna=nombre_tabla2.nombre_columna

LEFT JOIN: La palabra clave LEFT JOIN arroja todas las filas de la tabla izquierda (nombre_tabla1), aun si no hay coincidencias en la tabla derecha (nombre_tabla2).

SELECT nombre_columna(s)
FROM nombre_tabla1
LEFT JOIN nombre_tabla2
ON nombre_tabla1.nombre_columna=nombre_tabla2.nombre_columna

RIGHT JOIN: La palabra clave RIGHT JOIN arroja todas las filas de la tabla derecha (nombre_tabla2), aun si no hay coincidencias en la tabla izquierda (nombre_tabla1).

SELECT nombre_columna(s)
FROM nombre_tabla1
RIGHT JOIN nombre_tabla2
ON nombre_tabla1.nombre_columna=nombre_tabla2.nombre_columna

FULL JOIN: La palabra clave FULL JOIN arroja las filas cuando hay una coincidencia en una de las tablas. FULL JOIN arroja un valor aun si sólo una de las tablas tiene un valor, a diferencia de INNER JOIN, donde debe haber una coincidencia en las dos tablas.

SELECT nombre_columna(s)
FROM nombre_tabla1
FULL JOIN nombre_tabla2
ON nombre_tabla1.nombre_columna = nombre_tabla2.nombre_columna

INTERSECT: Combina dos o más sentencias SELECT. INTERSECT es básicamente lo mismo que un operador booleano AND. El operador INTERSECT de SQL toma los resultados de dos consultas y arroja sólo las filas que aparecen en los dos conjuntos de resultados. Se eliminan las filas duplicadas del resultado final a menos que se utilice INTERSECT ALL.

SELECT * FROM clase_info
WHERE calificacion BETWEEN ‘A’ AND ‘C’
INTERSECT
SELECT * FROM clase_info
WHERE calificacion BETWEEN ‘B’ AND ‘D’

INSERT INTO con SELECT: Copiar filas de otras tablas. Una sentencia INSERT también se puede utilizar para recuperar datos de otra tabla, modificarlos si es necesario e insertarlos directamente en la tabla original.

INSERT INTO nombre_tabla1 (columna1, columna2)
SELECT columna3, columna4
FROM nombre_tabla2

CASE: Se puede utilizar CASE en el Lenguaje de Consulta Estructurado (SQL) en donde se pueda utilizar una expresión. Se trata de una sentencia condicional que cuenta con una lógica if/then/else para SELECT, WHERE, HAVING, IN, DELETE, UPDATE, etc.

CASE
WHEN condición THEN resultado_1
ELSE resultado_2
END
CASE la expresión
WHEN la condición THEN resultado_1
ELSE resultado_2
END

UPDATE utilizando CASE

UPDATE clase_info
SET cuota = CASE
WHEN (cuota = ‘D’) THEN cuota * 1.25
ELSE cuota * 0.7
END

Transacciones

TRANSACTION: Un conjunto de una o más sentencias en una sola unidad para llevar a cabo un conjunto de acciones que tienen éxito o fracasan como un todo.

COMMIT: Si todas las sentencias son correctas en una sola transacción, todos los cambios se registran en la base de datos.

ROLLBACK: El proceso de revertir cambios.

A este tipo de transacciones se les llama explícitas:

BEGIN TRAN
UPDATE autores SET au_nombre = ‘Juan’
WHERE au_id = ‘172-32-1176’
UPDATE autores SET au_nombre = ‘Luis’
WHERE au_id = ‘213-46-8915’
COMMIT TRAN

Ejemplo con un chequeo de error:

BEGIN TRAN
INSERT INTO [autores]([autores_id],[autores_anombre],[autores_nombre], [tel],[contrato])
VALUES (‘123-32-1176’, ‘Gates’, ‘Bill’, ‘1-800-BUY-MSFT’, 1)
IF @@ERROR 0 BEGIN ROLLBACK TRAN return 10 END
UPDATE autores SET au_nombre = ‘Johnzzz’ WHERE au_id = ‘172-32-1176’
IF @@ERROR 0 BEGIN ROLLBACK TRAN return 11 END
COMMIT TRAN

Normalización

Normalización: Aplica un cuerpo de técnicas a una base de datos relacional para minimizar la inclusión de información duplicada.
Forma normal: Estructura (organiza) información en bases de datos relacionales para asegurar que la base de datos está apta para consultas y libre de algunas características indeseables como la inserción, actualización y eliminación de anormalidades que pueden llevar a la pérdida de la integridad de los datos. La normalización también fomenta un mantenimiento, almacenamiento y actualización eficientes.
1NF: Primera Forma Normal (1NF) establece reglas básicas para la base de datos: elimina columnas duplicadas de la misma tabla, crea tablas separadas para cada grupo de datos relacionados e identifica cada fila con una columna única (la clave principal).
2NF: Segunda Forma Normal (2NF) = 1NF Plus: Elimina subconjuntos de datos que aplican a varias filas de una tabla y los coloca en una tabla separada. Crea relaciones entre estas tablas nuevas y sus predecesoras a través del uso de claves foráneas.
3NF: Tercera Forma Normal (3NF) = 2NF Plus: Elimina columnas que no son dependientes de una clave principal.
4NF: Cuarta Forma Normal (4NF) = 3NF Plus: No tiene dependencias multivaluadas. Esta forma es mayormente académica y rara vez se usa.
5NF: Quinta Forma Normal (5NF) = 4NF Plus: La quinta forma normal establece que no existen dependencias de relaciones no triviales. Esta forma es mayormente académica y rara vez se usa.

Claves principales: Una clave principal define de manera única una relación dentro de una base de datos.

SQL CREATE PRIMARY KEY

CREATE TABLE Alumnos_NombreEscuela (
ID_Alumno int NOT NULL PRIMARY KEY,
Apellido varchar(255) NOT NULL,
Nombre varchar(255),
Direccion varchar(255),
Ciudad varchar(255) )

Claves foráneas: Una clave foránea/externa en una tabla apunta a la clave principal de otra tabla. Una columna de una clave foránea no debe contener valores NULL.

CREATE TABLE Alumnos_NombreEscuela (
ID_Alumno int NOT NULL PRIMARY KEY,
Apellido varchar(255) NOT NULL,
Nombre varchar(255),
Direccion varchar(255),
CP int(5) NOT NULL FOREIGN KEY)

Integridad de entidad: Se enfoca en una clave principal. La regla es que cada tabla debe tener una clave principal y que la columna o columnas elegidas para ser la clave principal deben ser únicas y no contener valores NULL.
Integridad referencial: Se enfoca en la clave foránea. La regla es que cualquier clave foránea puede estar sólo en uno de dos estados:
Primer estado: El valor de una clave foránea se refiere al valor de una clave principal de una tabla en una base de datos.
Segundo estado: El valor de una clave foránea puede ser NULL.
Integridad de dominio: Especifica que todos los valores disponibles en una base de datos relacional se deben declarar. Un dominio es un conjunto de valores del mismo tipo (tipo de dato). Los dominios, por lo tanto, son un grupo de valores de los cuales se extraen valores.

¿Qué es un índice?: Un listado de palabras clave y datos asociados que apuntan a la ubicación de información más extensa como archivos y registros en un disco o claves de registro en una base de datos.

Índice bitmap: Un índice bitmap almacena los datos en un vector de bits. La forma más común es un árbol-B. Los árboles-B pueden ser estructuras de datos altamente eficientes. A los elementos que están situados en el nivel más bajo del árbol se les conoce como Nodos hoja.
Índice denso: Un índice denso funciona con pares de claves y apuntadores para cada registro. Cada clave tiene un apuntador directamente ligado a un registro.
Índice no denso: Un índice no denso funciona con pares de claves y apuntadores para cada bloque (una secuencia de bytes o bits). Cada clave tiene un apuntador ligado a un bloque de datos. Menos costoso en recursos y menos efectivo (más generalizado) que los índices densos.
Índice invertido: Un índice invertido voltea el valor de una clave, por ejemplo, 12345 se convierte en 54321. Este método resulta útil cuando las claves se establecen en una secuencia en donde los nuevos valores de las claves cambian en cantidades uniformes.

Índices no agrupados: Similares al índice de

un libro. Tiene una palabra clave y un apuntador hacia la ubicación almacenada de la información. Los índices no agrupados pueden utilizar una estructura de árbol-B. Los nodos hoja se componen de páginas de índice o apuntadores. Los índices no agrupados son menos eficientes en búsquedas que los índices agrupados.

CREATE INDEX NombresIndex

ON empleados(Apellido, Nombre)

La sentencia anterior crea un índice en la tabla empleados por Apellido y los duplicados de apellidos se ordenan luego por Nombre.

Índices agrupados: Los índices agrupados son índices que se encuentran en un orden especial para acelerar la recuperación de datos. Estos índices se pueden almacenar en memoria. Los índices agrupados pueden utilizar una estructura de datos de árbol-B. En el árbol-B agrupado, los registros de datos de la tabla subyacente se ordenan en base a sus claves agrupadas.

CREATE CLUSTERED INDEX NombreIndex

ON empleados(Apellido, Nombre)

La sentencia anterior tiene CLUSTERED antes de INDEX, por lo que se crea un índice agrupado. Si se omitiera la palabra CLUSTERED entonces se crearía un índice no agrupado.

________________________________________________________________________

Seguridad (Tipos de ataques)

Bruto: Craqueo forzado de contraseñas de usuario débiles o predeterminadas.

Escalada de privilegios: se le otorgan más privilegios de acceso del necesario a un usuario.

Ataque de inferencia:Técnica de minería de datos en la cual, al analizarlos, el usuario obtiene conocimientos ilegítimos sobre un tema o base de datos.La inferencia ocurre cuando los usuarios son capaces de reunir información en un nivel de seguridad que debería estar disponible únicamente en un nivel de seguridad superior.

Inyección SQL: Permite a un usuario ejecutar código de lenguaje de consulta estructurado (SQL) de forma arbitraria para acceder a la base de datos. Ocurre cuando información ingresada por el usuario no se filtra correctamente o se ejecuta de forma inesperada.

Cuentas de Usuario:Los administradores de bases de datos protegen su información de accesos sin autorización y de intentos internos para exceder los permisos de acceso al bloquear el acceso a la base de datos con nombres de usuario y contraseñas.

GRANT permisos

ON tabla

TO usuario/rol

WITH GRANT OPTION

GRANT: permisos de tabla (SELECT, INSERT, UPDATE, DELETE) o permisos

de base de datos (CREATE TABLE, ALTER DATABASE, GRANT)

CREATE ROLE SuperUser AUTHORIZATION Administrator

Tipos de respaldos

Respaldo completo: se copian o respaldan todos los archivos.

Respaldo incremental: se copian o respaldan sólo los archivos que hayan cambiado desde el último respaldo.

Respaldo diferencial: se copian sólo los archivos que hayan cambiado desde el último respaldo completo.