Transcript RESOLVIENDO RELACIONES MUCHOS A MUCHOS

SICI-4030
Base de Datos
Prof. Nelliud D. Torres
SQL - Introductorio - DDL
OBJETIVOS
• DEFINICIÓN SQL
– DATA DEFINITION LANGUAGE (DDL)
– DATA MANIPULATION LANGUAGE
(DML)
– DATA CONTROL LANGUAGE (DCL)
– HISTORIA
• BENEFICIOS Y PROPÓSITOS
DEL SQL
• MEDIO AMBIENTE DEL SQL
• COMANDO CREATE
– SCHEMA
– TIPO DE DATOS
– CONSTRAINT - PRIMARY AND
FOREIGN KEY
– PASOS PARA CREAR UNA TABLA
– CREANDO TABLAS CON DIFERENTES
APLICACIONES
• SQL CONSTRAINTS
– EJEMPLOS
– NOT NULL
– COMPOSED PRIMARY KEYS
– CONTROLLING VALUES
– ENSURING DATA INTEGRTY
• CREACIÓN DE SECUENCIAS
• EDITAJE DE COMANDOS
• OTROS MANDATOS DE SQL
–
–
–
–
–
–
–
–
–
–
–
–
INSERT INTO
COMMIT
TRUNCATE TABLE
DROP TABLE
EJERCICIO
ALTER TABLE
ROLLBACK
DESC TABLA
SET ECHO ON/OFF
SPOOL FILENAME.TXT
REM
SPOOL
DEFINICIÓN SQL
Volver a
los
Objetivos
DEFINICIÓN - SQL
• SQL (Structured Query Language) – Se
podría traducir como “lenguaje estructurado
para consultas”.
• Es el lenguaje más importante para el manejo
de base de datos relacionales.
• Forma parte del DML (Data Manipulation
Language) y lo desarrolló IBM a principios de
los 70.
• Es el estándar para el “relational database
management systems”(RDBMS)
Pag: 289
DEFINICIÓN
• Se compone de tres partes:
– Data Definition Language (DDL)– Instrucciones
que crean una base de datos, tabla o índices
entre otras cosas.
– Data Manipulation Language (DML) –
Instrucciones que añaden, eliminan o modifican
instancias (records) y las instrucciones que sirven
para realizar consultas (queries) a la base de
datos.
– Data Control Language (DCL) – Instrucciones
relacionadas a la administración de la base de
datos (cuentas, privilegios, accesos, tunning, etc.)
Historia del SQL
• 1970 – E. Codd develops relational database
concept
• 1974-1979 – System R with Sequel (later SQL)
created at IBM Research Lab
• 1979 – Oracle markets first relational DB with
SQL
• 1986 – ANSI SQL standard released
• 1989, 1992, 1999, 2003 – Major ANSI standard
updates
• Hoy día – SQL se utiliza por todos los
vendedores de bases de datos
Pag: 291
BENEFICIOS Y PROPÓSITOS
DEL SQL
Volver a
los
Objetivos
Beneficios de Utilizar un Lenguaje
Relacional Estandarizado
•
•
•
•
•
Reduce costos de entrenamiento
Mayor productividad
Portabilidad de la aplicación
Longetividad de la aplicación
Reduce la dependencia de un sólo
vendedor
• Comunicación que cruza sistemas
Pag: 293
Propósito del SQL Standard
• Especificar sintaxis y semántica
para la definición y manipulación
de los datos.
• Definir estructuras de datos
• Capacitar la portabilidad
• Permitir crecimiento y/o mejoras a
los estándares
MEDIO AMBIENTE DEL SQL
Volver a
los
Objetivos
Medio ambiente del SQL
• Catalog
– Un conjunto de schemas que constituye la
descripción de la base de datos.
• Schema
– La estructura que contiene descripciones de
objetos creados por un usuario (tablas, views y
restricciones (constraints))
• Data Definition Language (DDL)
– Comandos que definen una base de datos,
incluyendo su creación, alteración y eliminación
de tablas y establecer limitaciones (constraints).
Pag: 294
Medio ambiente del SQL
• Data Manipulation Language (DML)
– Comandos que mantienen e interrogan una
base de datos (query)
• Data Control Language (DCL)
– Comandos que controlan la base de datos,
incluyendo la administración de privilegios y
asegurando (committing) data (eliminando o
guardando transacciones)
Pag: 294
Figure 7-1
A simplified schematic of a typical SQL environment, as
described by the SQL-2003 standard
Pag: 294
Figure 7-4
DDL, DML, DCL, and the database development process
Pag: 297
COMANDO CREATE
Volver a
los
Objetivos
Creación de una base de datos con SQL
• Data Definition Language (DDL)
• Instrucciones más importantes con la declaración
CREATE:
– CREATE SCHEMA – Define una porción de la base de
datos que le pertenece a un usuario en particular.
– CREATE TABLE – Define una tabla con sus columnas.
– CREATE VIEW – Define una tabla lógica de uno o más
views
• Otras declaraciones de CREATE: CHARACTER
SET, COLLATION, TRANSLATION, ASSERTION,
DOMAIN
SCHEMA
• Antes de poder crear tablas en la base de datos,
necesitamos crear esquemas para los usuarios.
• Son como cuentas o áreas en donde cada
usuario crea y maneja sus tablas
independientemente.
• A nivel de producción se define un schema que
comparten muchos usuarios.
• El administrador de la base de datos es el que
crea los esquemas y las cuentas.
Enunciado CREATE TABLE
• Su formato básico es:
CREATE TABLE nombre_tabla (
atributo1 tipoDeDato1,
atributo2 tipoDeDato2,
atributo3 tipoDeDato3,
etc.);
Tipos de datos en SQL
Pag: 295
Tipo - NUMBER
• Se utiliza para valores numéricos enteros y reales.
Su formato es number (o,d) en donde o = cantidad
de dígitos enteros y d = cantidad de dígitos
decimales.
• Valor máximo de o =38.
• Ejemplos:
– NUMBER(8) - Un entero de ocho dígitos.
– NUMBER(5,2) - Número de cinco dígitos, de los cuales
dos son decimales. OJO 5,2 no puede almacenar un
valor mayor de 999.99. Si se trata de guardar un número
mayor, va a generar un error.
• Derivados de number:
–
–
–
–
int[eger] dec[imal] smallint real -
Tipo - CHAR
• Se utiliza para especificar una cantidad de
caracteres FIJOS. Su formato es CHAR(n)
donde n tiene un máximo de 255 (en Oracle 8
es de 2000)
• En caso de que el valor sea menor que la
cantidad definida, se rellena de espacios en
blanco. (consume memoria)
• Ejemplos:
– CHAR(5) - Un string de cinco caracteres.
– CHAR - Es el default y sólo almacenaría un carácter.
Tipo - VARCHAR2
• Se utiliza para especificar una cantidad de
caracteres VARIABLES. Su formato es
VARCHAR(n) donde n puede tener un valor máximo
de 2000 (4000 en Oracle 8).
• Si el dato ocupa menos posiciones de las definidas,
estas no se rellenan con espacios en blanco y por lo
tanto no utiliza memoria.
• Ejemplos:
– VARCHAR2(5) - Un string de cinco caracteres.
– VARCHAR2 - Es el default y su tamaño es de largo
variable.
Tipo - DATE
• Se utiliza para especificar fecha y hora.
Almacena la fecha en formato dd-mmm-yy
hh:mm:ss.ss o dd-mmm-yyyy hh:mm:ss.ss. Se
requiere que la fecha se especifique entre
comillas sencillas.
• Ejemplos:
– ’08-mar-07’ es válido si se guardo en el atributo con
este formato.
– ’08-mar-2007’ - También es válido.
OJO Si el Oracle se instaló en español, los meses
cambian de acuerdo al idioma.
Tipo - LONG
• Permite almacenar caracteres hasta 2GB.
Solo se permite a cada tabla un máximo
de una columna de este tipo.
Tipo - BOOLEAN
• Se utiliza para especificar valores cierto o falso.
Puede contener TRUE, FALSE o NULL.
• Nota: En Oracle-SQL no existe el tipo de dato
buleano. Sin embargo se puede representar
con char(1) o number(1).
• Hay muchos otros tipos de datos tales como
LONG, RAW, LONG RAW, etc. Por ahora
vamos a trabajar con los que se han explicado
hasta el momento.
CONSTRAINT
• Sirve para especificar restricciones o
condiciones a los atributos.
• Nos permite especificar los primary keys
(PK).
• También permite especificar los foreign
keys.
• A cada CONSTRAINT se le identifica con
un nombre el cual debe ser alusivo a su
función o propósito.
CONSTRAINT - PRIMARY KEY
• Se puede declarar en forma directa al declarar el atributo o
por medio del constraint.
• EJEMPLOS:
Declaración directa,
CREATE TABLE tabla1 (
atributo1
number(7) PRIMARY KEY,
atributo2
varchar2(5),
etc.);
no se define nombre
del constraint
Declaración indirecta (constraint)
CREATE TABLE tabla2 (
atributo1
number(7),
atributo2
varchar2(5),
CONSTRAINT nombre-const PRIMARY KEY(atributo1)
);
CONSTRAINT - PRIMARY
KEY(cont.)
• Para definir un Primary Key compuesto (dos atributos
o más), se declara el constraint de la siguiente forma:
CREATE TABLE tabla2 (
atributo1 number(7),
atributo2 varchar2(5),
atributo3 number(5),
CONSTRAINT nombre-con PRIMARY KEY (atributo1,atributo2)
);
CONSTRAINT - FOREIGN KEY
• Se puede declarar en forma directa al declarar el atributo o por medio del
constraint.
• EJEMPLO-1:
CREATE TABLE tabla1 (
atributo1
number(7) PRIMARY KEY,
atributo2
varchar2(5),
etc.);
CREATE TABLE tabla2 (
atributo3
number(7),
atributo4
varchar2(5),
atribut05
number(7)
FOREIGN KEY tabla1(atributo1),
CONSTRAINT nombre-const PRIMARY KEY(atributo3)
);
CONSTRAINT - FOREIGN KEY (Cont.)
• Declaración por medio del constraint.
• EJEMPLO-2:
CREATE TABLE tabla1 (
atributo1
number(7) PRIMARY KEY,
atributo2
varchar2(5),
etc.);
CREATE TABLE tabla2 (
atributo3
number(7),
atributo4
varchar2(5),
atributo5
number(7),
CONSTRAINT keytabla2
PRIMARY KEY(atributo3)
CONSTRAINT foreignkeytabla1 FOREIGN KEY atributo5
REFERENCES tabla1(atributo1)
);
Pasos en la creación de la
tabla:
Creación de una Tabla
Figure 7-5 General syntax for CREATE TABLE
1. Identificar los tipos de datos
para los atributos
2. Identificar atributos que no
pueden ser nulos
3. Identificar columnas que
deben ser únicas (candidatas
a PK)
4. Identifica primary key–
foreign key mates
5. Determinar los valores
default
6. Identificar limitaciones
(constraints) en las columnas
(domain specifications)
7. Crear la tabla e índices
asociados
Pag: 299
EJECUTANDO COMANDOS DE
SQL CON DIFERENTES
APLICACIONES
• Existen varias formas de ejecutar los comandos en SQL
que varían de acuerdo a la herramienta que se utilice.
• Algunos ejemplo son:
• Oracle 10g use SQL*Plus or SQL*Plus Worksheet
• Create queries in SQL view in Microsoft Access
• Commands are typed at prompt in MySQL
A Guide to SQL, Seventh Edition
A Guide to SQL, Seventh Edition
A Guide to SQL, Seventh Edition
SQL CONSTRAINTS
Volver a
los
Objetivos
SQL Constraints
• NOT NULL constraint
– Se asegura de que la columna no acepte valores nulos.
• UNIQUE constraint
– Se asegura de que el valor en la columna sea único con
respecto a los demás valores en la misma columna.
• DEFAULT constraint
– Asigna una valor por defecto cuando se va a insertar una
nueva fila.
• CHECK constraint
– Valida la data cuando el valor del atributo se entra.
Database Systems: Design, Implementation, & Management, 7th Edition, Rob & Coronel
Los próximos slides utilizan comandos de
SQL para crear las tablas que se ven en
este ERD
Figure 7-6 SQL database definition commands for Pine Valley Furniture
Overall table
definitions
Pag: 300
Figure 7-6 SQL database definition commands for Pine Valley Furniture
Figure 7-6 SQL database definition commands for Pine Valley Furniture
Figure 7-6 SQL database definition commands for Pine Valley Furniture
Figure 7-6 SQL database definition commands for Pine Valley Furniture
Defining attributes and their data types
Non-nullable specification
Identifying primary key
Primary keys
can never have
NULL values
Non-nullable specifications
Primary key
Some primary keys are composite–
composed of multiple attributes
Controlling the values in attributes
Default value
Domain constraint
Identifying foreign keys and establishing relationships
Primary key of
parent table
Foreign key of
dependent table
Data Integrity Controls
• Referencia de integridad – Son constraints
que aseguran que los valores del FK de una
tabla deben parear los valores del PK de una
tabla en una relación 1:M.
• Restringe:
– Eliminación de records primarios
– Actualización de records primarios
– Insertar records dependientes
Pag: 303
Figure 7-7 Ensuring data integrity through updates
Relational integrity is enforced via the primary-key to foreign-key match
Pag: 303
No permite que el PK de la tabla CUSTOMER se modifique.
Si se cambia el PK de CUSTOMER, se hace lo mismo con
sus correspondientes FK.
Si se cambia el PK de CUSTOMER, se pone NULL sus
correspondientes FK.
Si se cambia el PK de CUSTOMER, se le pone un
valor predeterminado a sus correspondientes FK.
Pag: 303
CREACIÓN DE SECUENCIAS
Volver a
los
Objetivos
Creación de Secuencias
• Se utilizan cuando vas a definir un PK como
autonumber para que genere números
automáticamente.
• En SQL 2003 se hizo una modificación para
poder incluirlo dentro del mandato CREATE.
• Ejemplo:
CREATE TABLE CUSTOMER_T
(customer_id INTEGER GENERATED ALWAYS AS
IDENTITY
(START WITH 1
INCREMENT BY 1
MINVALUE 1
MAXVALUE 10000
NOCYCLE),
customer_name
VARCHAR (25) NOT NULL,
CONSTRAINT customer_pk PRIMARY KEY
(customer_id)
Pag: 305
);
Creación de Secuencias (cont.)
• También se puede poner como una directiva
aparte. Por ejemplo:
• CREATE SEQUENCE nombre
INCREMENT BY n
START WITH n
MAXVALUE n | NOMAXVALUE
CYCLE | NOCYCLE
CACHE n | NOCACHE
Creación de Secuencias (cont.)
• INCREMENT BY n - Indica de cuanto en cuanto vamos
contando en "n“
• START WITH n - Indica el primer valor en "n“
• MAXVALUE n - Indica el valor mayor posible en "n"
• NOMAXVALUE - Por default el valor mayor es 10^27
• CYCLE - Permite recomenzar la secuencia.
• NOCYCLE - Fuerza a que de error si se trata de usar un
número menor al especificado en START WITH o de un
número mayor al especificado en MAXVALUE.
• CACHE n - Especifica cuantos valores se pre localizarán en
el Oracle Server y los mantiene en un lugar de memoria del
servidor de la base de datos. Por default mantiene 20
valores.
Creación de Secuencias (Ejemplo - 2)
CREATE SEQUENCE s_dept_id
INCREMENT BY 1
START WITH 50
MAXVALUE 99999999
NOCYCLE
NOCACHE;
Podemos ver el valor actual de la secuencia de la siguiente forma
SELECT s_dept_id.CURRVAL FROM dual;
Usando la secuencia podemos insertar datos usando el próximo número
de la secuencia de la siguiente forma:
INSERT INTO s_dept VALUES (s_dept_id.NEXTVAL, 'Finance', 2);
Información sobre las secuencias
• Podemos conseguir información sobre los views creados en la
tablas del diccionario de datos que se llama
USER_SEQUENCES.
• Escribe el siguiente mandato de SQLPlus para que veas los
campos de la tablas USER_SEQUENCES:
DESCRIBE user_sequences
•
También puedes hacer consultas sobre esa tabla, por
ejemplo:
SELECT sequence_name, min_value, max_value,
increment_by, last_number FROM user_sequences;
Ejemplo de CREATE TABLE y
CREATE SEQUENCE
CREATE TABLE EJEMPLO (
eje_id
INTEGER
NOT NULL,
eje_name
VARCHAR2(20)
NOT NULL,
eje_date
DATE
DEFAULT SYSDATE,
eje_dateExpires
DATE,
PRIMARY KEY( acc_id )
);
CREATE SEQUENCE eje_id
INCREMENT BY 1
START WITH 1
NOMAXVALUE
NOCYCLE;
EDITAJE DE COMANDOS Y
MANEJO DE ERRORES EN
DIFERENTES APLICACIONES
Volver a
los
Objetivos
Editaje de Comandos – Oracle SQL*Plus
Comandos con errores – Oracle SQL*Plus
Comandos con errores – Oracle SQL*Plus
Editando Comandos en MySQL
• Los comandos más recientes se almacenan en
memoria en un área llamada “statement history”.
• Se puede editar los comandos en el “statement
history” al utilizar los siguientes comandos de
editaje que se muestran en el próximo slide.
A Guide to SQL, Seventh Edition
Editaje de Comandos - MySQL
A Guide to SQL, Seventh Edition
Editando Comandos en MySQL
• Presione Up arrow para ir a la primera línea
• Dele Enter para moverse a la próxima línea si esta
correcta la primera
• Utilice las flechas( ) para moverse al texto que
necesita corrección.
• Al terminar de corregir la línea, presione Enter.
• Hasta que no se de Enter, el cambio a esa línea no
será efectivo.
OTROS MANDATOS DE SQL
Volver a
los
Objetivos
INSERT INTO
• Nos permite entrar datos en una tabla ya creada.
• Ejemplo: Si tenemos la siguiente definición de una
tabla:
CREATE TABLE tabla1(
atributo1 number(7) PRIMARY KEY,
atributo2 varchar2(5),
);
• Para insertar un record sería de la siguiente forma:
INSERT INTO tabla1(atributo1, atributo2)
VALUES (1234567,’abcde’);
Pag: 304
COMMIT
• Este comando confirma (ejecuta
físicamente) una operación de INSERT,
UPDATE o DELETE en una tabla.
• Cuando ejecutamos esa instrucción en
una tabla, esta no se altera físicamente
(sino lógicamente) hasta que no se reciba
el comando COMMIT;
• Esto nos permite revertir comando en las
bases de datos.
TRUNCATE TABLE
• Este comando nos permite eliminar los
datos de la tabla únicamente. Su
estructura se mantiene.
• Su formato es: TRUNCATE TABLE
tabla1;
• OJO  Al eliminar los datos de diferentes
tablas, estos deben eliminarse en un
orden en particular.
• ¿Cuál es ese orden y porque?
DROP TABLE
• Este comando nos permite eliminar los
datos de la tabla y su estructura.
• Su formato es: DROP TABLE tabla1;
• OJO  Al eliminar las tablas de un
sistema, estas deben eliminarse en un
orden en particular.
• ¿Cuál es ese orden y porque?
Pag: 304
EJERCICIO DE PRACTICA - 1
De acuerdo al siguiente ERD
EJERCICIO DE PRACTICA - 2
Y los
siguientes
valores:
EJERCICIO DE PRACTICA - 3
• Desarrolle el script que crea las tablas.
• Desarrolle el script que entre los datos
(INSERT INTO).
• Desarrolle el script que elimine los datos y
borre las tablas (DROP TABLE).
EJERCICIO DE PRACTICA - 4
• Código de ejemplo de creación de la tabla DEPARTAMENTO:
REM ***************************************************************
REM *
Creacion de la tabla DEPARTAMENTO
*
REM ***************************************************************
CREATE TABLE DEPARTAMENTO (
dep_numero
NUMBER(1),
dep_nombre
VARCHAR2(25) NOT NULL,
dep_localización VARCHAR2(15),
CONSTRAINT dep_numero_pk
PRIMARY KEY (dep_numero)
);
EJERCICIO DE PRACTICA - 5
• Código de ejemplo de crear datos en la tabla
DEPARTAMENTO:
REM ***************************************************************
REM * Datos de la tabla DEPARTMENTO
*
REM ***************************************************************
INSERT INTO DEPARTAMENTO VALUES (2,'Physics','Edificio 100');
INSERT INTO DEPARTAMENTO VALUES (5,'Computer Science','Edificio 100');
INSERT INTO DEPARTAMENTO VALUES (7,'Geology','Edificio 200');
COMMIT;
EJERCICIO DE PRACTICA - 6
• Código de ejemplo para eliminar la tabla
DEPARTAMENTO:
DROP TABLE DEPARTAMENTO;
OTROS MANDATOS DE SQL
- ALTER TABLE
• Permite añadir y modificar definiciones de atributos de tablas que ya
estan creadas.
• Ejemplo: Tenemos la siguiente definición de una tabla:
CREATE TABLE tabla1(
atributo1 number(7) PRIMARY KEY,
atributo2 varchar2(5),
);
• Si deseamos añadir un atributo adicional, se puede escribir el siguiente
mandato:
ALTER TABLA tabla1 ADD (
atributo3
number(5)
);
• Si deseamos cambiar el tamaño del atributo3, lo podemos hacer con el
siguiente mandato:
ALTER TABLA tabla1 MODIFY (
atributo3
number(8)
);
Pag: 303
OTROS MANDATOS DE SQL
- ROLLBACK
• Nos permite eliminar una operación de
INSERT, UPDATE o DELETE en una tabla.
• Trabaja de una forma parecida al UNDO del
ambiente gráfico.
• Elimina esas operaciones, siempre y cuando
no se haya ejecutado el mandato: COMMIT
• Su formato es: ROLLBACK;
OTROS MANDATOS DE SQL
- DESC tabla
• Nos muestra una descripción general de
tabla.
• No termina con el punto y coma (;) como los
demás mandatos.
• Este mandato pertenece a SQL Plus.
• Su formato es: DESC tabla
OTROS MANDATOS DE SQL
- DESC (EJEMPLO)
• Ejemplo de DESC.
OTROS MANDATOS DE SQL
- SET ECHO ON/OFF
• Cuando se está ejecutando un script de
SQL, este comando (SET ECHO ON)
permite ver en pantalla las instrucciones
según se van ejecutando.
• Escribiendo SET ECHO OFF desactiva la
opción.
• Este comando se pueden dar
interactivamente en pantalla o incluirlos
como parte de un script.
OTROS MANDATOS DE SQL - REM
• Permite poner comentarios dentro del script.
• Su formato es similar al formato de
comentario del Basic clásico.
• Después de REM se puede poner lo que
programador desee.
• También se puede utilizar dos guiones
seguidos (--) para sustituir la palabra REM.
• Formato:
REM comentarios
OTROS MANDATOS DE SQL
- SPOOL filename.txt
• Envía el resultado que sale en el command
Prompt al archivo que especifique el usuario.
• El resultado sale tanto en pantalla como en
el archivo.
• Lo guarda en un directorio dentro de Oracle.
• El directorio podría ser:
• Se puede indicar el Path como parte del
nombre del archivo.
REFERENCIAS
• Modern Database Management 8th Edition,
Jeffrey A. Hoffer, Mary B. Prescott, Fred R.
McFadden
• Database Systems: Design, Implementation,
and Management, Seventh Edition, Rob and
Coronel
• Dra. María De Jesús - Universidad
Interamericana, Recintro Metro
• A Guide to SQL, Seventh Edition
A