Transcript Conceptos de Bases de Datos Relacionales

Conceptos de Bases de
Datos Relacionales
Parte 1
Servidor de Bases de Datos
• Servidor que es capaz de administrar múltiples bases de
datos
Base de Datos
• Una base de datos relacional almacena conjuntos de datos
que están relacionados unos con otros.
• Las Tablas son la estructura primaria de una base de datos
relacional.
• Las tablas están constituidas por:
– Columnas (columns)
– Filas (rows)
Objetos de una Base de Datos
•
•
•
•
•
•
Tables, almacena datos
Views, simplifica y/o restringe acceso a datos
Indexes, mejora acceso a la base de datos
Defaults, define valores por omisión
Rules, restinge valores que se aceptan
Stored procedures, comandos sql y de control con llamado
explícito
• Triggers, comandos sql y de control con llamado implícito
SQL (Structured Query Language)
• SQL lenguaje usado para definir, manipular, y controlar
bases de datos relacionales
– Definido por ANSI (American National Standards Institute)
• Comandos SQL se pueden dividir en tres categorías:
– DDL (Data definition language)
• create, alter, drop
–
DML (Data manipulation language)
• select, insert, update, delete
–
DCL (Data control language)
• grant, revoke
Extensiones SQL
• Son cosas adicionales al standard ANSI SQL
• Están diseñadas para simplificar la administración de las
bases de datos
• FIPS flagger
– Útiles para los usuarios que solamente usan ANSIcompliant SQL
– Envía mensajes a los usuarios cuando ejecutan un
comando que no cumpla
– Sintaxis:
set fipsflagger { on | off }
System Procedures
• Procedimientos del Sistema instalados automáticamente
que facilitan la manipulación de los Servidores de Bases de
Datos
• En el caso de Sybase y SQL-servidor, todos los system
procedures comienzan con con “sp_”
• Ejemplos:
– sp_help da información de todos los objetos en una base de
datos
– sp_who da información de los usuarios que están trabajando
en el servidor de bases de datos
Relación Cliente/Servidor
• Cliente – Programa que acepta requerimientos de un
usuario y los envía a un servidor; también recibe respuestas
del servidor y se las muestra al usuario
• Servidor – Programa que procesa requerimientos del
cliente y le retorna resultados
Conexión al Servidor
• Para conectarse a un servidor a través de un cliente se
necesita:
– Un login válido
– Un password válido
• Una conexión simple se conoce como “session”
• Base de datos default:
– Base de datos que se coloca en uso cuando un usuario se
conecta al servidor
– Determinada según el perfil del usuario
– Características fijadas por el Administrador
Funcionalidad en el Cliente
• Un cliente debe proporcionar las siguientes facilidades:
–
–
–
–
–
Iniciar el cliente y conectarse al servidor
Ejecutar comandos y batches
Navegar por una base de datos
Editar comandos
Crear y ejecutar scripts
• Ejemplos:
–
–
–
–
Cliente isql
Cliente Advantage
Cliente Oracle
Cliente Manager de Microsoft
Creación de Tablas
Diseño de Tablas
• Una Tabla es un objeto de base de datos que almacena
datos en filas y columnas
• Antes de crear una tabla, se debe diseñar su estructura:
1. Darle nombre a la tabla y a cada columna
2. Seleccionar un tipo de dato para cada columna
3. Escoger las propiedades de cada columna
Datatype
• Un tipo de datos (datatype) es un atributo asignado a una
columna que especifica el tipo de información que puede
almacenarse en esa columna
Seleccionar Datatypes
• Para seleccionar el datatype de una columna:
1. Determinar el conjunto posible de valores
2. Determinar la precisión requerida
3. Encontrar el datatype que:
–
–
–
–
Pueda guardar todos los posibles valores
Proporcione la exactitud y precisión requeridas
Use eficientemente el almacenamiento
Facilite el futuro crecimiento
Categorías de Datatypes
• Números exactos
Números con exactitud y precisión predecibles
• Números aproximados
Números con valores dependiendo de exactitud y precisión del
almacenamiento
• Money
Valores para pesos
• Date y time
Valores de fechas y horas
• Character
Para strings caracteres
• Binary
Información binaria (como imágenes, video y bits)
Ejemplos de Datatypes por Categorías
• Números exactos
integer, numeric (p,s), decimal (p,s)
• Números aproximados
float (p), real, double precision (dependientes de máquina)
• Money
money
• Date y time
datetime
• Character
char (n), varchar (n), text
• Binary
bit, binary, varbinary, image
Datatypes definidos por el Usuario
• Un datatype definido por el usuario es un datatype creado
por el usuario de un datatype del sistema
• Muy útil para asegurar consistencia de múltiples columnas
que almacenan el mismo conjunto de valores
Propiedades de las Columnas
• Una columna puede tener una de las siguientes
propiedades:
– NULL
– NOT NULL
– IDENTITY
• Si no se especifica, generalmente se asume NOT NULL
Valores tipo NULL
• Un NULL representa un valor desconocido o que no aplica
– Para valores numéricos, NULL no es igual a 0
– Para caracteres, NULL no es igual a '' '' (caracter en
blanco)
• NULL no se considera considera menor que, mayor que, o
igual a cualquier otro valor
• Dos NULL no se consideran iguales
Propiedades de NULL
• Una columna con la propiedad NULL permite NULLs
– Esto se conoce como nullable column
• Una columna con la propiedad NOT NULL no permite
NULLs
• Una columna que no se le especifique esta propiedad
asume que permite NULLs
Propiedad IDENTITY
• La propiedad IDENTITY hace que el sistema asigne
automáticamente valores únicos a cada fila
• Cuando se insertan datos en una tabla, el servidor
automáticamente asigna un número secuencial a la columna
que tiene la propiedad de IDENTITY
Reglas para columnas con IDENTITY
• Sólo puede existir una por tabla
• Debe de ser de tipo numérico sin posiciones decimales
– Ejemplo: numeric(5,0)
• No se puede actualizar
• No acepta valores tipo NULL
• Inicia por default en 1
– Se puede asignar un valor de arranque
Crear Tablas
• Sintaxis Simplificada para create :
CREATE TABLE table_name (
column_name datatype
...
column_name datatype
[ NULL | NOT NULL | IDENTITY ] ,
[ NULL | NOT NULL | IDENTITY ] )
Crear Tabla
create table empleado (
emp_id
numeric(4,0) IDENTITY,
apellido
varchar(30) NOT NULL,
nombre
varchar(30) NOT NULL,
e_mail
char(6)
departamento
typ_ch_deptid
NULL,
)
Borrar Tablas
• Sintaxis Simplificada para drop:
DROP TABLE table_name
• Ejemplo:
drop table empleado
Recomendaciones para desarrollo
• Crear todos los objetos (incluyendo tablas) en scripts y
grabarlos
– Facilita la recreación de los objetos
– Sirve como material permanente de referencia
– Especialmente importante para tablas
• Especificar una propiedad para cada columna
• Usar tipos de datos de usuario para columnas que
almacenen el mismo conjunto de valores
Cláusula default
• La cláusula default hace que el servidor dé un valor cuando el
valor no se proporciona explícitamente al hacer un insert
• Sintaxis Simplificada :
create table table_name (
column_name datatype
...)
default default_value [property],
Modificación de cláusulas default
• Sintaxis Simplificada:
alter table table_name
replace column_name default default_value
• A pesar de que la sintaxis usa la palabra “replace”, el
comando alter table se puede usar para añadir cláusulas
default a una tabla
Ejemplo de modificación de cláusulas default
alter table publica
replace ciudad default “Cali"
alter table publica
replace depto default “Valle”
Tablas y Permisos
• Para permitir que otros usuarios puedan consultar,
manipular los datos o hacer referencia a una tabla, se deben
dar permisos
• Sintaxis Simplificada:
grant { select | insert | update | delete | reference | all }
on table_name
to user_list
• Ejemplo:
grant select on empleado to juan, sandra, pablo
Adición de Columnas
• Después de creada una tabla, se pueden añadir columnas
• Algunos DBMS no permiten borrar directamente una
columna de una tabla
– Para borrar una columna, se debe borrar y volver a crear la tabla
• Sintaxis para añadir una columna a una tabla:
alter table table_name
add column_name datatype NULL
[, column_name datatype NULL ...]
• Todas las columnas añadidas deben tener la propiedad
NULL.
Adicionar Columnas
alter table editor
add address varchar(40) NULL,
country varchar(20) NULL
Copiar Tablas
• select into es una extensión que se usa para crear una
copia de una tabla existente
Sintaxis de select into
• Sintaxis Simplificada :
select column_list into new_table_name
from old_table_name
[where condition]
Ejemplos de select into
• Ejemplo de copiado de todas las columnas:
select * into titles_copy
from titles
• Ejemplo de copiado de algunas columnas :
select title_id, title, type
into titles_copy
from titles
Ejemplos de select into
• Ejemplo que copia algunos datos:
select * into titles_copy
from titles
where price > $12.00
• Ejemplo que no copia datos:
– Porque la cláusula where siempre da falso, se copia la
estructura de la tabla pero no sus datos
select * into titles_copy
from titles
where 1 = 2
Data Manipulation Language
• DML es uno de los tres grupos principales de
sentencias SQL
• Comprende cuatro sentencias:
–
–
–
–
insert añade datos a una tabla
update actualiza datos existentes en una tabla
delete borra datos de una tabla
select consulta datos de una tabla
Sintaxis para insert
• sintaxis Simplificada:
insert [into] table_name [(column_list)]
{ values (value_list) | select_statement }
• Ejemplos:
insert into editor
values ("736", “Nuevas BD", “Pasto",
“Nariño")
insert into editor (nombre, pub_id)
values (“Prensa Actual", "2003”)
Extensión SQL para insert
• Se pueden insertar múltiples filas en un solo insert
utilizando un select embebido
• Ejemplo:
insert into editor
select * from pub..editor
• Las columnas en las que se van a insertar datos deben
tener los mismos tipos de datos que resulten de las
columnas del query
Sintaxis para update
• Sintaxis Simplificada :
update table_name
set column1 = { expression | select_statement }
[, column2 = { expression | select_statement } ...]
[from table_list]
[where condition]
• Ejemplos:
update titles
set price = price * $1.25
update authors
set address = "1224 Mason Ct.",
phone = "415 773-2882"
where au_id = "427-17-2319"
Extensión para update
• Se pueden encadenar condiciones en la cláusula
where de un update
• Ejemplo:
update titulo
set precio = precio * $0.90
from titulo t, editor p
where t.pub_id = p.pub_id
and p.state = “Nariño"
Extensión case
• La expresión case es una extensión SQL que puede
estar embebida en un update
• case permite el retorno condicional de un valor de dos
o más posibles
• Dos estructuras sintácticas para case
– Basada en expresiones
– Basada en condiciones
Sintaxis para when expression
• Sintaxis:
case expression
when expression then expression
[when expression then expression ...]
[else expression]
end
• Ejemplo:
update titulo
set tipo =
case tipo
when "mod_cook" then "modcook"
when "trad_cook" then "tradcook"
when "popular_comp" then "popularcomp"
end
where tipo in ("mod_cook", "trad_cook",
"popular_comp")
Sintaxis para
when condition
• Sintaxis:
case
when condition then expression
[when condition then expression ...]
[else expression]
end
• Ejemplo:
update titulo
set precio =
case
when tipo = "business" then precio
when tipo = "mod_cook" then precio
when tipo = "trad_cook" and pub_id
then precio * $1.07
when tipo = "trad_cook" and pub_id
then precio * $1.10
else precio
end
* $1.03
* $1.05
= "0736"
= "0877"
Sin la cláusula else
• Si ninguna de las condiciones del case se cumple y no
existe un else, case retorna NULL
• Ejemplo:
update titulo
set precio
case tipo
when
when
when
end
=
"trad_cook" then precio * 1.07
"mod_cook" then precio * 1.03
"business" then precio * 1.05
Reglas para case
• Solo puede retornar valores
– A diferencia del case de la mayoría de los lenguajes de
programación, el case del SQL no ejecuta sentencias
• Se puede utilizar en cualquier sitio donde se pueda
escribir una expresión
• Debe retornar un valor compatible con el tipo de dato
• Al menos una cláusula (la cláusula when o la cláusula
else) deben retornar un valor no NULL
Sintaxis para delete
• Sintaxis Simplificada:
delete [from] table_name
[where condition]
• Ejemplos:
delete from editor
delete from titulo
where tipo = "business"
delete from autor
where au_fname = "Luis"
and au_lname = “Caicedo"
Extensión para delete
• Se pueden encadenar condiciones en la cláusula
where de un delete
• Sintaxis simplificada :
delete table_name
from table_list
[where join_condition]
• Ejemplo
delete titles
from titles t, publishers p
where t.pub_id = p.pub_id
and p.state = "CA"
truncate table
• truncate table borra todas las filas de una tabla
– La operación se ejecuta más rápido que la
correspondiente sentencia delete
– El comamdo no se puede usar para borrar algunas filas
de la tabla
• Sintaxis simplificada:
truncate table table_name
• Ejemplo:
truncate table publishers
Registro de Transacciones (Log)
• El registro de transacciones se utiliza para recuperar los datos
en el evento de un error del usuario o falla del sistema
• Actúa ante cada insert, update, y delete
• Algunas operaciones no se registran
– Ejemplo de operaciones sin registro:
• select into
• truncate table
Sentencia select
• La sentencia select consulta datos
• Sintaxis simplificada:
select column_list
from table_name
[where condition]
[group by column_list]
[having condition]
[order by column_list]
• Ejemplo:
select type, avg(price)
from titles
where type not in ("mod_cook", "trad_cook")
group by type
having avg(price) > $12.00
order by type
select y case
• case permite el retorno condicional de valores
• case puede estar embebido en sentencias select
– Ejemplo:
select title, "Contract Status" =
case contract
when 1 then "Contract"
when 0 then "No contract"
end
from titles
title
----...
But Is It User Friendly?
The Psychology of Computer...
Secrets of Silicon Valley
...
Contract Status
--------------Contract
No contract
Contract
Vista
• Una vista es un objeto de base de datos que consiste de una
sentencia select almacenada
• Usada para:
– Simplificar la construcción de consultas complejas
– Simplificar al usuario la percepión de la base de datos
– Limitar el acceso a los datos
Crear y borrar vistas
• Sintaxis Simplificada para crear una vista :
create view view_name [(column1 [, column2...])
as
select_statement
[with check option]
• Ejemplo:
create view vw_california_authors
as
select au_id, au_lname, au_fname, state, phone
from authors
where state = "CA"
• Sintaxis simplificada para borrar una vista:
drop view view_name
• Ejemplo:
drop view vw_california_authors
Vistas y permisos
• Para permitir que otros utilicen una vista, se deben dar
permisos:
grant [ select | insert | update | delete | all ]
on view_name
to user_list
• Ejemplo:
grant select
on vw_california_authors
to clerk1, clerk2, clerk3, clerk4
Consultar datos usando vistas
• Para consultar datos usando vistas, usar el nombre de la vista
como si se tratara de una tabla
• Ejemplo:
select * from vw_california_authors
au_id
au_lname
-----------172-32-1176 White
213-46-8915 Green
238-95-7766 Carson
267-41-2394 O'Leary
274-80-9391 Straight
...
(15 rows affected)
au_fname
-------Johnson
Marjorie
Cheryl
Michael
Dick
state
----CA
CA
CA
CA
CA
phone
----408 496-7223
415 986-7020
415 548-7723
408 286-2428
415 834-2919
Modificar datos usando vistas
• Se pueden modificar datos usando vistas si:
– Todas las columnas involucradas están en una tabla
– Todas las columnas no usadas en la vista ni permiten NULLs,
contienen la cláusula default o contienen columnas con IDENTITY
– Ejemplo:
insert into vw_california_authors
values ("123-54-8808", "Chen", "Rose", "CA",
"415 239-6467")
• Se pueden actualizar datos usando vistas si:
– Todas las columnas involucradas están en una tabla
• Se pueden borrar datos usando vistas si:
– La vista está basada en una sola tabla
– Ejemplo:
delete from vw_california_authors
where au_lname = "Hunter"
Usar vistas para simplificar el acceso
• Las vistas pueden simplificar el acceso a los datos
almacenando las consultas complejas (tales como
joins)
• Ejemplo:
create view vw_pub_title_join (publisher, title)
as
select pub_name, title
from publishers, titles
where publishers.pub_id = titles.pub_id
go
select * from vw_pub_title_join
pub_name
-------New Age Books
New Age Books
...
title
----You Can Combat Computer Stress
Is Anger the Enemy?
Usar vistas para restringir acceso
• Una vista puede permitir a los usuarios acceder a una parte de la
tabla cuando el acceso al contenido total de la tabla está restringido
create view vw_california_authors
as
select au_id, au_lname, au_fname, state, phone
from authors
where state = "CA"
go
grant all on vw_california_authors to public
revoke all on authors from public
Opción with check
• with check option restringe cómo se pueden modificar las
filas. Una operación falla si:
– Tratar de insertar filas que las vista no puede ver
– Tratar de modificar filas que las vista no puede ver
• Ejemplo:
create view vw_kansas_authors
as
select au_id, au_lname, au_fname, state, phone
from authors
where state = "KS"
with check option
• Ejemplo de modificación que da error:
update vw_kansas_authors
set state = "MO" where state = "KS"
Reglas para vistas
• La definición de una vista puede incluir:
– Una cláusula where
– Una columna con valores calculados (ej. precio * cant)
– Funciones Agregadas, una cláusula group by, y una cláusula
having
– distinct
– Un join
– Otra vista (hasta 16 niveles)
• Una definición de una vista no puede incluir:
– Una cláusula order by
– Una cláusula compute o compute by
– select into
Ejemplos con vistas
• Crear dos tablas:
select * into mytitles
from pubs2..titles
select * into mypublishers
from pubs2..publishers
• Crear vista que muestra titles y sus publishers vía un join:
create view vw_titles_pubs (title, publisher)
as
select title, pub_name
from mytitles, mypublishers
where mytitles.pub_id = mypublishers.pub_id
• Usar la vista para mostrar los titles y sus publishers:
select * from vw_titles_pubs
• Ejecute las siguientes sentencias, cuáles pueden o no fallar.
Si la Sentencia falla, explique por qué:
select price from vw_titles_pubs
_______________________________
update vw_titles_pubs
set publisher = "New Millenium Books"
where publisher = "New Age Books"
_______________________________
insert into vw_titles_pubs
values ("Life in Houston", "TexTitles")
________________________________
• Borrar los objetos creados:
drop view vw_titles_pubs
drop table mytitles, mypublishers
Table scan
• Un table scan es una búsquda en donde se leen todas las
filas de una tabla
• Una tabla que no tenga índices creados, solamente puede
hacer búsquedas a través de un table scan
Indices
• Un índice es un objeto de base de datos que ayuda al
servidor a encontrar un dato más rápidamente
Estructura de un Indice : Caso de Estudio
authors table
(data pages)
Index pages
key
key
row
ptr
pg
ptr
Bennet
PAGE 1001
1421, 1
1007
Karsen
Smith
1876, 1
1242, 1
row
ptr
pg
ptr
Bennet
Greane
Hunter
PAGE 1007
1421, 1
1132
1242, 4
1133
1242, 1
1127
Karsen
PAGE 1305
1876, 1
1311
1305
1062
PAGE 1132
Bennet
1421, 1
Chan
1129, 3
Dull
1409, 1
Edwards
1018, 5
PAGE 1133
Greane
1242, 4
Green
1421, 2
Greene
1409, 2
PAGE 1127
Hunter
1242, 1
(more pages)
create index idx_authors_2
on authors(au_lname)
Jenkins
1241, 4
(more pages)
10
11
12
13
PAGE 1241
O’Leary
Ringer
White
Jenkins
PAGE 1242
14
Hunter
15
Smith
Ringer
16
17
Greane
PAGE 1421
18
Bennet
19
Green
Ringer
20
PAGE 1409
21
Dull
22
Greene
White
23
(more pages)
Crear y borrar índices
• Sintaxis simplificada para create :
create [unique] [ clustered | nonclustered ]
index index_name
on table_name (column1 [, column2] ... )
• Ejemplo:
create clustered index idx_c_titles_1
on titles (title_id)
• Sintaxis Simplicada para drop :
drop index table_name.index_name
• Ejemplo:
drop index titles.idx_c_titles_1
Atributos de los índices
• Tres atributos describen cada índice
– El número de columnas sobre las cuales se declara el índice
• Una columna – índice no-compuesto
• Múltiples columnas - índice compuesto
– Si el índice acepta o no valores duplicados
• Se permiten valores duplicados - índice no-único
• No se permiten valores duplicados - índice único
– Si están o no ordenados los datos en la tabla por el concepto del
índice cuando la tabla se crea
• Datos ordenados durante la creación - índice cluster
• Datos no ordenados durante la creación - índice no-cluster
Indice no-compuesto
• Un índice no-compuesto es un índice creado sobre una
columna
– Ejemplo:
create index idx_authors_2
on authors(state)
• Apropiado cuando las consultas se hacen frecuentemente
sobre una sola columna
– Ejemplo:
select * from authors
where state = "UT"
Indice compuesto
• Un índice compuesto es un índice creado sobre dos o
más columnas
– Ejemplo:
create index idx_authors_3
on authors(au_lname, au_fname)
• Apropiado cuando las consultas se hacen sobre
múltiples columnas
– Ejemplo:
select * from authors
where au_lname = "Ringer" and au_fname = "Anne"
Indice no-único
• Un Indice No-único es un índice que permite valores
duplicados
– Ejemplo:
create index idx_authors_2
on authors(state)
• Apropriado cuando las consultas se hacen sobre
valores duplicados
– Ejemplo:
select * from authors
where state = "UT"
Indice único
• Un índice único es un índice que no permite valores
duplicados
– Ejemplo:
create unique index idx_u_authors_1
on authors(au_id)
• Apropiado cuando cada valor en la columna indizada debe
ser único
– Ejemplo:
select * from authors
where au_id = "213-46-8915"
• Puede ser creado solamente sobre columnas que no tengan
valores duplicados
Indice no-cluster
• Un índice no-cluster es un índice que utiliza un concepto de
ordenamiento diferente a como se realizó el almacenamiento
de la tabla
– Ejemplo:
create nonclustered index idx_authors_4
on authors(state)
• Una tabla puede tener muchos índices no-cluster
• Apropiado para:
– Tablas que ya tienen un índice cluster
Estructura de un índice no-cluster
Index pages
root level
intermediate level
row
ptr
key
key
row
ptr
pg
ptr
Bennet
PAGE 1001
1421, 1
1007
Karsen
Smith
1876, 1
1242, 1
pg
ptr
Bennet
Greane
Hunter
PAGE 1007
1421, 1
1132
1242, 4
1133
1242, 1
1127
Karsen
PAGE 1305
1876, 1
1311
1305
1062
leaf level
key
row ptr
PAGE 1132
Bennet
1421, 1
Chan
1129, 3
Dull
1409, 1
Edwards
1018, 5
PAGE 1133
Greane
1242, 4
Green
Greene
1421, 2
1409, 2
PAGE 1127
Hunter
1242, 1
(more pages)
create index
idx_authors_2
on authors(au_lname)
Jenkins
1241, 4
(more pages)
authors table
(data pages)
PAGE 1241
10
O’Leary
11
Ringer
White
12
13
Jenkins
PAGE 1242
14
Hunter
15
Smith
Ringer
16
17
Greane
PAGE 1421
18
Bennet
19
Green
Ringer
20
PAGE 1409
21
Dull
22
Greene
White
23
(more pages)
Indice cluster
• Un índice cluster es un índice que, cuando se crea, indica
cómo están físicamente almacenados los datos en la tabla
– Ejemplo:
create clustered index idx_c_authors_1
on authors(au_id)
• Una tabla sólo puede contener un índice cluster
• Típicamente mejora el rendimientos de las consultas que se
hacen a una tabla
– Consultas con valor único (where state = ''CA'')
– Consultas por rango de valores (where price > $10.00)
• Puede dismuir el rendimiento en operaciones de modificación
de los datos de una tabla
– Esta reducción se debe a que se deben almacenar físicamente
los datos ordenados
Etructura de un índice cluster
Index pages
root level
intermediate level
key
pg ptr
PAGE 1007
key
pg ptr
PAGE 1001
Bennet
1007
Karsen
Smith
1305
1062
Bennet
Greane
Hunter
1241
1242
1421
PAGE 1305
Karsen
1409
(more pages)
create clustered index
idx_authors_2
on authors(au_lname)
authors table
(leaf/data pages)
PAGE 1241
10
Bennet
11
Chan
Dull
12
13
Edwards
PAGE 1242
14
Greane
15
Green
Greene
16
17
PAGE 1421
18
Hunter
19
Jenkins
20
PAGE 1409
21
Karsen
22
O'Leary
Ringer
23
(more pages)
Escritura de consultas que usen índices
• Los índice se usan solamente cuando una consulta hace
referencia a columna(s) indizada(s) en la cláusula where
• Si una tabla:
–
–
–
–
Tiene dos columnas, y
Cualquiera puede identificar una fila desada, y
Una columna está indizada mientras la otra no, entonces
Se debe utilizar la columna indizada en la cláusula where de
la consulta
Ejemplo usando índices
• crear una tabla:
create table novels (
book_id int,
title varchar(40),
author varchar(40)
)
• Insertar tres filas:
insert into novels
values (1, "Congo", "M Crichton")
insert into novels
values (2, "The Client", "J Grisham")
insert into novels
values (3, "Jurassic Park", "M Crichton")
• Ejecutar las sentencias (una fallará):
create clustered index idx_c_novels_1 on
novels(book_id)
create unique index idx_u_novels_2 on
novels(author)
• ¿Qué sentencia falla? ¿Por qué?
________________________________________
• Borrar los objetos creados:
drop index novels.idx_c_novels_1
drop table novels
Prácticas recomendadas para desarrollo
• Crear todos los objetos en scripts y guardarlos
– Agrupar las sentencias por objetos mas que por funciones
• Crear la primer tabla y todos los objetos (vistas, índices, y así
sucesivamente) asociados con esa tabla
• crear la segunda tabla y todos sus objetos asociados
• No crear primero todas las tablas, luego todas las vistas, luego
todos los índices, y así sucesivamente
• Dar los permisos a un objeto, al final del script que lo
define
– Primero retirar todos los permisos grupales, y luego dar los
permisos individuales
Tabla temporal
• Una tabla temporal es a tabla que eventualmente la borra el
servidor sin que se ejecute la sentencia drop table
• Típicamente existen dos tipos de tablas temporales:
– Compartidas (Shareable)
– De sesión específica
Uso de tablas temporales
• Uso de tablas temporales:
– Por el usuario:
• Para guardar resultados de consultas muy complejas
• Para almacenar resultados intermedios (por ejemplo de stored
procedure)
– Por el sistema:
• Para almacenar resultados intermedios de consultas que
requieren ordenamiento o eliminación de valores
• Para almacenar resultados intermedios de consultas que tienen
la cláusula compute o agregadas
Tablas del sistema
• Una tabla del sistema es una tabla creada y
mantenida por el servidor que almacena
información acerca del servidor o de sus bases
de datos
Integridad de datos
• Tres tipos de integridad ayudan a garantizar que los datos
son correctos
• integridad a nivel de dominio
– Asegura que cualquier valor en una columna está dentro del
conjunto de valores válidos para esa columna
• integridad a nivel de entidad
– Asegura que cualquier fila de una tabla puede identificarse
inequívocamente
• integridad referencial
– Asegura que un determinado valor no puede escribirse en una
tabla si ese valor no existe en otra tabla
Dos métodos para forzar integridad
Domain
Integrity
Entity Integrity
Referential
Integrity
Constraints
Check
constraints
Primary key
constraints,
unique
constraints
References
constraints
Database
Objects
Rules
Indexes
Triggers
Constraint
• Un constraint es un elemento de la sentencia create table
que forza uno de los tres tipos de integridad de datos
– Declarada a nivel de columna o de tabla
• Los constraints también se conocen como “declarative
integrity”
Integridad a nivel de dominio
• La integridad a nivel de Dominio asegura que cualquier valor
en una columna está dentro del conjunto de valores
aceptables para esa columna
Restricción Check
• Un check constraint forza integridad a nivel de Dominio
durante las adiciones y actualizaciones
– Si una sentencia contiene un valor que no concuerda con los
permitidos, la sentencia falla
Restricción check a nivel de columna
• Sintaxis Simplificada:
create table table_name (
column_name datatype
[ NULL | NOT NULL | IDENTITY ]
[constraint constraint_name]
check (condition_using_column_name) ,
...
column_name datatype
[ NULL | NOT NULL |
IDENTITY ]
)
• Ejemplo:
create table publishers (
pub_id
char(4)
NOT NULL
constraint chk_pub_id
check (pub_id like "99[0-9][0-9]"),
pub_name
varchar(40) NULL,
city
varchar(20) NULL,
state
char(2)
NULL
)
Nombre de restricción a nivel de columna
• Si un usuario no da el nombre de una restricción a nivel de
columna, el servidor genera uno
• Para simplificar la administración, el usuario debería escribir el
nombre de todos los constraints
– El nombre de un Constraint debe ser único a nivel de la base de
datos
Restricción a nivel de tabla
• Sintaxis Simplificada:
create table table_name (
column_name datatyp
[ NULL | NOT NULL | IDENTITY ] ,
...
column_name datatype
[ NULL | NOT NULL | IDENTITY ],
[constraint constraint_name]
check (condition_involving_any_column_or_columns)
)
• Ejemplo:
create table discounts (
discounttype varchar(40)
NOT NULL,
stor_id
char(4)
lowqty
smallint
NULL,
highqty
smallint
NULL,
discount
float
NOT NULL,
constraint chk_low_high
check (lowqty <= highqty)
)
NULL,
Restricciones y defaults
• Los servidores permiten que una tabla tenga default y check
sin que haya contradicción
• Ejemplo (cualquier insert que use el default fallará):
create table publishers (
pub_id
char(4)
NOT NULL,
pub_name
varchar(40)
NULL,
city
varchar(20) default "Tulsa"
NULL,
state
char(2)
default "OK"
NULL,
constraint chk_state
check (state in ("CA", "OR", "WA", "NV"))
)
insert into publishers (pub_id, pub_name)
values ("9909", "Failed Imprints")
Ejemplos de restricción
• Crear una tabla con restricción check y default:
create table employees (
emp_id
int
constraint chk_emp_id
check (emp_id > 500),
dept_id
int default 1300,
name
varchar(40)
)
• Ejecutar estas sentencias (una fallará):
insert into employees (emp_id, dept_id, name)
values (252, 1200, "Mike Yates")
insert into employees (emp_id, dept_id, name)
values (719, 1500, "David Benton")
insert into employees (emp_id, name)
values (801, "Alistair Stewart")
¿Cuál fallará? ¿Por qué?
_______________________________________________
Verificar que dos de los tres inserts se han ejecutado:
select * from employees
Ninguno de los inserts contiene el valor 1300, sin embargo una
de las filas de la tabla contiene el valor 1300. ¿Cuándo y cómo
se insertó este valor?
________________________________________________
Borrar los objetos creados de la base de datos:
drop table employees
Integridad a nivel de entidad
• La integridad a nivel de entidad asegura que cada fila de una
tabla está identificada de manera inequívoca
Primary key
• Combinación de una o más columnas que identifican de
manera inequívoca una fila en una tabla
Restricción primary key
• Una restricción primary key asegura integridad a nivel de
entidad
• Crea un índice único sobre la columna(s) especificada(s)
• Se asegura integridad en los inserts y updates
• Sólo puede existir una llave primaria por tabla
Indices creados por primary key
•
•
•
•
Unico
Cluster
No permite valores NULL en la(s) columna(s)
No se puede borrar usando la sentencia drop
index
Primary key a nivel de columna
• Sintaxis Simplificada:
create table table_name (
column_name datatype
[ NOT NULL | IDENTITY ]
[constraint constraint_name]
primary key [clustered | nonclustered],
...
column_name datatype
[ NULL | NOT NULL | IDENTITY ]
)
• Ejemplo:
create table publishers (
pub_id
char(4)
NOT NULL
constraint pky_c_pub_id
primary key,
pub_name
varchar(40) NULL,
city
varchar(20) NULL,
state
char(2)
NULL
)
Primary key a nivel de tabla
• Sintaxis Simplificada:
create table table_name (
column_name datatype
[ NULL | NOT NULL | IDENTITY
...
column_name datatype
[ NULL | NOT NULL | IDENTITY
[constraint constraint_name]
primary key (column1 [, column2...])
)
• Ejemplo:
create table sales (
stor_id
char(4)
NOT NULL,
ord_num
varchar(20) NOT NULL,
date
datetime
NOT NULL,
constraint pky_c_storid_ordnum
primary key (stor_id, ord_num)
)
],
]
Restricción unique
•
•
•
•
Una restricción unique forza integridad a nivel de entidad
Crea un índice único sobre la columna(s) specificada(s)
Asegura integridad durante los inserts y updates
Las tablas pueden tener más de una restricción tipo unique
Indices creados con restrición unique
•
•
•
•
Unico
No-cluster por default
Permite el uso de valores tipo NULL
No se puede borrar usando la sentencia drop
index
Restricción unique a nivel de columna
• Sintaxis Simplificada:
create table table_name (
column_name datatype [ NULL | NOT NULL | IDENTITY
[constraint constraint_name]
unique [clustered | nonclustered],
...
column_name datatype [ NULL | NOT NULL | IDENTITY
)
• Ejemplo:
create table publishers (
pub_id
char(4)
NOT NULL,
pub_name
varchar(40) NULL
constraint unq_pub_name
unique,
city
varchar(20) NULL,
state
char(2)
NULL
)
]
]
Restricción unique a nivel de tabla
• Sintaxis Simplificada:
create table table_name (
column_name datatype [ NULL | NOT NULL | IDENTITY
...
column_name datatype [ NULL | NOT NULL | IDENTITY
[constraint constraint_name]
unique (column1 [, column2...])
)
• Ejemplo:
create table sales (
stor_id
char(4)
NOT NULL,
ord_num
varchar(20) NOT NULL,
date
datetime
NOT NULL,
constraint unq_stor_id_ord_num
unique (stor_id, ord_num)
)
],
]
Restricciones primary key vs unique
What is the
default type of
index?
How many
NULL values
does it allow?
Can you have
more than one
per table?
Primary Key
Constraint
Clustered
None
No
Unique
Constraint
Nonclustered
One
Yes
Integridad referencial
• Integridad referencial asegura un valor no se puede
almacenar en una tabla a menos que él exista en otra tabla (o
es NULL)
• Ejemplo:
– A publisher ID cannot be entered in the titles table unless it
exists in the publishers table
Foreign key
• Una foreign key es una combinación de una o más
columnas que corresponde(n) a la llave primaria de otra
tabla
Restricción referencial
• Una restricción referencial forza integridad referencial
• La integritdad se asegura en inserts, updates, y deletes
– Si un insert o update contiene valores de foreign key que no
existen en la(s) column(s) primary key, la sentencia falla
– Si un update o delete va a quitar un valor de primary key que
tiene valores foreign key existentes, la sentencia falla
Restricción referencial a nivel de columna
• Sintaxis Simplificada:
create table table_name (
column_name datatype [ NULL | NOT NULL
[constraint constraint_name]
references table_name
(primary_key_column),
...
column_name datatype [ NULL | NOT NULL
)
• Ejemplo:
create table titles (
title_id
char(6)
NOT NULL,
title
varchar(80)
NULL,
pub_id
char(4)
NULL
constraint ref_pub_id
references publishers (pub_id),
notes
varchar(200)
NULL
)
| IDENTITY ]
| IDENTITY ]
Restricción referencial a nivel de tabla
• Sintaxis Simplificada:
create table table_name (
column_name datatype
[ NULL | NOT NULL
...
column_name datatype
[ NULL | NOT NULL
[constraint constraint_name]
foreign key
(list_of_columns_from_this_table)
references table_name
(primary_key_column_list) )
| IDENTITY ],
| IDENTITY ],
• Ejemplo:
create table salesdetail (
stor_id
char(4)
NOT NULL,
ord_num
varchar(20) NOT NULL,
title_id
char(6)
NOT NULL,
qty
smallint
NOT NULL,
discount
float
NOT NULL,
constraint ref_sales
foreign key (stor_id, ord_num)
references sales (stor_id, ord_num) )
Reglas para restriciones referenciales
• La(s) columna(s) en la tabla padre que es referenciada en una
tabla hija debe cumplir una de las siguientes condiciones:
– Tener una restricción primary key constraint
– Tener una restricción A unique constraint
– Tener indice unico creado con el comando create index
• Una restricción referencial se puede referir a columnas dentro
de la misma tabla
– Ejemplo:
create table employees (
emp_id
char(10)
NOT NULL,
name
varchar(40) NOT NULL,
manager
char(10)
NOT NULL
constraint ref_manager
references employees(emp_id)
)
Restricciones sobre valores referenciales
• Modificación de valores sobre una primary key:
– No se permite hacer update o delete si el valor existe en
una foreign key
– Se permite hacer update o delete si el valor no existe en
una foreign key
• Modificación de valores sobre foreign key:
– No se permite insert o update si el nuevo valor no existe
en la correspondiente primary key
• Borrado de tablas con primary keys:
– No se pueden borrar una tabla si su primary key está
referenciada en una foreign key
Ejemplos de restricción referencial
• Crear la tabla customers con una primary key:
create table customers (
customer_id int
constraint pk_cust primary key,
name
varchar(40)
)
• Crear una tabla orders de tal forma que una orden no se
pueda insertar a menos que exista un ID válido en customer.
Usar una foreign key (customer_id) que se refiera a la
primary de la otra tabla (customers.customer_id):
create table orders (
item_name
varchar(20),
customer_id int
references customers (customer_id)
)
• Insertar dos filas en customers:
insert into customers values (101, "Liz Shaw")
insert into customers values (102, "Jo Grant")
• Ejecutar estas sentencias (una fallará):
insert into orders values ("widget", 102)
insert into orders values ("gadget", 105)
• ¿Cuál insert falla? ¿Por qué?
_____________________________________
• Ejecutar estas sentencias (una fallará):
delete from customers where name = "Liz Shaw"
delete from customers where name = "Jo Grant"
• ¿Cuál delete falla? ¿Por qué?
____________________________________
• Borrar los objetos de base de datos creados:
drop table orders
drop table customers
Adición de restricciones
• Sintaxis simplificada para añadir:
alter table table_name
add constraint constraint_name
{ check (condition) | primary key (column_list)
| unique (column_list) |
foreign key (column_list) references table
(column_list) }
• Ejemplos:
alter table roysched
add constraint chk_hirange_lorange
check (hirange > lorange)
alter table publishers
add constraint pky_pub_id
primary key (pub_id)
alter table titles
add constraint ref_pub_id
foreign key (pub_id)
references publishers(pub_id)
Borrado de restricciones
• Sintaxis simplificada para borrado:
alter table table_name
drop constraint constraint_name
• Ejemplos:
alter table roysched
drop constraint chk_hirange_lorange
alter table publishers
drop constraint pky_pub_id
alter table publishers
drop constraint unq_pub_name
alter table titles
drop constraint ref_pub_id
Ejemplos de restricciones
• Crear una table employees:
create table employees (
emp_id
int,
name
varchar(40),
salary
money
)
• Añadir una nueva restricción a la tabla:
alter table employees
add constraint chk_salary
check (salary between $1 and $70000)
• Tratar de insertar un empleado con un dato de
salario inválido:
insert into employees
values (01, "Sarah Jane Smith", 90000)
• Borrar los objetos de base de datos creados:
drop table employees
exec sp_dropmessage 20001
Default
• Un default es un objeto de base de datos que
proporciona un valor a una columna cuando en un
insert no se especifica un valor para esa columna
• Se puede aplicar a una o más columnas
Crear y asignar defaults
• Sintaxis simplificada para Create:
create default default_name as
constant_expression
• Ejemplo:
create default def_state as
"CA"
• Asignación del default
Reglas para asignar defaults
• Una columna solamente puede tener un default
– Si se intenta asignar un default a una columna que ya
tiene un default, ese intento fallará
• Una columna con una cláusula default no se le
puede asignar un default
• Cuando un default se asigna, los datos en la tabla
no se ven afectados
– Solamente aplica a los datos que se inserten después
de que se asigne el default
Quitar y borrar defaults
• Quitar un default
• Sintaxis simplificada para borrado:
drop default default_name
• Ejemplo:
drop default def_state
Rule
• Una rule es un objeto de base de datos que no permite
inserts y updates si la regla no se cumple
– Como la restricción check, se puede utilizar para forzar
integridad a nivel de dominio
• Una rule se puede asignar a una o más columnas
Crear reglas
• Sintaxis simplificada:
create rule rule_name as
condition_expression
• Ejemplos:
create rule rul_state as
@state in ("CA", "CO", "WA")
create rule rul_discount_range as
@discount between 0 and 20
create rule rul_file_date as
@date < 12 or @date > 22
create rule rul_pub_id as
@pub_id in ("1389", "0736", "0877") or
@pub_id like "99[0-9][0-9]"
Asignar reglas
• Sintaxis simplificada:
sp_bindrule rule_name, object_name
• Ejemplo:
sp_bindrule rul_state, "publishers.state"
• Una columna solamente puede tener una rule
– Si se asigna una regla a una columna, cualquier regla
asignada con anterioridad pierde validez
• Una columna puede tener una restricción y una rule
asignadas a ella
– Los valores deben cumplir ambas condiciones: la restricción
y la rule en operaciones de inserción o actualización
• Cuando se asigna una regla, los valore existentes en la
tabla no se ven afectados
– Solamente aplica a los datos que inserten después de que la
regla se asigna
Desasignar y borrar reglas
• Desasignar reglas
• Sintaxis simplificada para drop :
drop rule rule_name
• Ejemplo:
drop rule rul_state
Rules y defaults
• Crear una tabla:
create table employees (
emp_id
int,
name
varchar(20)
)
• Crear una regla: los ID de los empleados debe ser
mayor que 10:
create rule rul_employee
as @x > 10
• Crear un default de 5 para ID de empleado:
create default def_employee
as 5
• Asignar nuevos default y rule a la tabla:
sp_bindrule rul_employee,
"employees.emp_id"
exec sp_bindefault def_employee,
"employees.emp_id"
• Ejecutar estas sentencias (una fallará):
insert into employees (emp_id, name)
values (20, "Liz Shaw")
insert into employees (name)
values ("Jo Grant")
• ¿Cuál insert falla? ¿Por qué?
• Desasignar el default y la rule:
sp_unbindefault "employees.emp_id"
exec sp_unbindrule "employees.emp_id"
• Borrar los objetos de base de datos:
drop default def_employee
drop rule rul_employee
drop table employees
Datatypes definidos por el usuario
• Defaults y rules se pueden asignar a datatypes definidos por
el usuario
• Las columnas que usen esos datatype aplican el default y
rule que tenga asignado ese datatype
• Si el default o rule cambian, se aplican de inmediato
– Los datos existentes no se afectan por los cambios
Precedencia de columna y datatype
• Si una columna:
– Tiene asignado un default, y
– Usa un datatype definido por el usuario que tiene un
default diferente, entonces
– El default asignado a la columna tiene precedencia
• Similarmente, si una columna:
– Tiene una rule, y
– Usa un datatype definido por el usuario que tiene una
rule diferente, entonces
– La rule asignada a la columna tiene precedencia
Métodos para garantizar integridad de datos
• Dos métodos para implementar integridad de datos
Domain
Integrity
Entity
Integrity
Referential
Integrity
Constraints
Check
constraints
Primary key
References
constraint,
constraints
unique constraint
Database
Objects
Rules
Indexes
Triggers
Resumen de restricciones
• Son exigidos en el estándar ANSI
• Son específicos a la tabla en que se crean y no son
reutilizables
• No se pueden asociar a datatype definidos por el
usuario
• Se crean al crear las tablas (o al modificarlas)
• Fácilmente se pueden implementar checks
multicolumna
Resumen de defaults y rules
•
•
•
•
Son extensiones SQL
No son específicos de una tabla y son reutilizables
Se pueden asociar a datatypes de usuario
No se crean al crear la tabla y requieren de etapas
adicionales (creación y asignación)
• No pueden implementar checks multicolumna
Selección de un método
• Usar constraints:
–
–
–
–
Para implementar integridad una tabla
Para cumplir con el ANSI standard
Para restringir el dominio de una columna
Para definir checks entre dos columnas de una fila
• Usar objetos de base de datos:
– Para implementar integridad que aplica a más de una
columna, tabla o base de datos
– Para asignar rules o defaults datatypes de usuario