Diferencias

Muestra las diferencias entre dos versiones de la página.

Enlace a la vista de comparación

materias:creacion_de_tablas [2011/03/10 17:17]
caolarte [Operaciones del DDL]
materias:creacion_de_tablas [2011/03/10 17:18] (actual)
caolarte
Línea 1: Línea 1:
  
 +=== Ingresar a Postgres ===
 +Primero iniciar una sesión remota en el servidor:
 +  ssh username@192.168.250.2
 +
 +Con el siguiente comando se abre una consola SQL:
 +    psql -U bd01 -d bd01
 +
 +
 +
 +
 +
 +
 +===== Operaciones del DDL =====
 +=== Running Example ===
 +Asuma el siguiente esquema de relaciones para un sistema muy simple de registro de libros en una biblioteca.
 +  
 +   paises (id*, nombre)
 +   editoriales (id*, nombre, pai_id)
 +   libros (isbn*,titulo,edi_id)
 +
 +=== Script de Creación de Tablas ===
 +
 +  CREATE TABLE PAISES
 +    (ID SERIAL NOT NULL,
 +     NOMBRE VARCHAR(15) NOT NULL);
 +
 +  CREATE TABLE EDITORIALES
 +    (ID SERIAL NOT NULL,
 +     NOMBRE VARCHAR(20) NOT NULL,
 +     PAI_ID INT NOT NULL);
 +
 +  CREATE TABLE LIBROS
 +    (ISBN VARCHAR(20) NOT NULL,
 +     TITULO VARCHAR(50) NOT NULL,
 +     EDI_ID INT NOT NULL);
 +
 +Algunos otros tipos de datos no utilizados en este ejemplo:
 +   * Timestamp: Fecha y Hora
 +   * Double: Flotante de 8 bytes.
 +   * Float: Flotante de 4 bytes.
 +   * Text: Cadena de caracteres.
 +   * Boolean: true/false
 +
 +La lista completa de tipos se encuentra en: [[http://www.postgresql.org/docs/8.4/static/datatype.html]]
 +
 +
 +=== Algunas Operaciones Sobre Tablas ===
 +== Describir una tabla ==
 +
 +   \d LIBROS;
 +
 +== Listar todas las tablas de la base de datos ==
 +   \dt
 +
 +== Adicionar una Columna ==
 +  ALTER TABLE LIBROS  ADD NUMPAGS int;
 +
 +== Renombrar una Tabla ==
 +  ALTER TABLE EDITORIALES RENAME TO EDITORES;
 +
 +== Adicionar una Columna ==
 +  ALTER TABLE LIBROS  ADD FECHA DATE;
 +
 +== Renombrar una Columna ==
 +  ALTER TABLE LIBROS RENAME FECHA TO FECHAPUB;
 +
 +== Eliminar una Columna ==
 +Supongamos que adicionamos una columna a País:
 +  ALTER TABLE PAISES ADD INDICATIVO INT;
 +
 +Si la queremos eliminar:
 +  ALTER TABLE PAISES DROP COLUMN INDICATIVO;
 +
 +== Eliminar una Tabla ==
 +Creemos una tabla de prueba:
 +  CREATE TABLE TEMP (COD INT);
 +
 +Para eliminarla:
 +  DROP TABLE TEMP;
 +
 +== Valores por Defecto ==
 +Supongamos que se debe adicionar una columna con la fecha de registro del libro que por lo general es la fecha actual del sistema. Esto se puede realizar así:
 +
 +  ALTER TABLE LIBROS ADD FECHAREGISTRO DATE;
 +
 +Luego especificamos que el valor por defecto:
 +  ALTER TABLE LIBROS ALTER COLUMN FECHAREGISTRO SET DEFAULT NOW();
 +
 +
 +=== Script de Restricciones (Constraints) ===
 +
 +Después de todas las modificaciones anteriores, el esquema de la base de datos luce de la siguiente manera:
 +
 +      Listado de relaciones
 +    Esquema |  Nombre  | Tipo  | Dueño 
 +   ---------+----------+-------+-------
 +    public  | editores | tabla | bd01
 +    public  | libros   | tabla | bd01
 +    public  | paises   | tabla | bd01
 +   (3 filas)
 +
 +
 +                                     Tabla «public.paises»
 +    Columna |         Tipo          |                         Modificadores                         
 +   ---------+-----------------------+---------------------------------------------------------------
 +    id      | integer               | not null valor por omisión nextval('paises_id_seq'::regclass)
 +    nombre  | character varying(15) | not null
 +
 +
 +                                      Tabla «public.editores»
 +    Columna |         Tipo          |                           Modificadores                            
 +   ---------+-----------------------+--------------------------------------------------------------------
 +    id      | integer               | not null valor por omisión nextval('editoriales_id_seq'::regclass)
 +    nombre  | character varying(20) | not null
 +    pai_id  | integer               | not null
 +
 +                    Tabla «public.libros»
 +       Columna    |         Tipo          |      Modificadores      
 +   ---------------+-----------------------+-------------------------
 +    isbn          | character varying(20) | not null
 +    titulo        | character varying(50) | not null
 +    edi_id        | integer               | not null
 +    numpags       | integer               | 
 +    fechapub      | date                  | 
 +    fecharegistro | date                  | valor por omisión now()
 +
 +El siguiente paso es definir las llaves primarias:
 +
 +  ALTER TABLE PAISES ADD CONSTRAINT PAI_PK PRIMARY KEY (ID);
 +  ALTER TABLE EDITORES ADD CONSTRAINT EDI_PK PRIMARY KEY (ID);
 +  ALTER TABLE LIBROS ADD CONSTRAINT LIB_PK PRIMARY KEY (ISBN);
 +
 +Luego se deben crear las llaves foráneas:
 +  ALTER TABLE EDITORES ADD CONSTRAINT 
 +   EDI_PAI_FK FOREIGN KEY (PAI_ID) REFERENCES PAISES (ID);
 +  ALTER TABLE LIBROS ADD CONSTRAINT 
 +   LIB_EDI_FK FOREIGN KEY (EDI_ID) REFERENCES EDITORES (ID);
 +  
 +También es posible definir atributos cuyos valores no se pueden repetir. Por ejemplo, no deberían existir dos países con el mismo nombre. Esto se puede especificar por medio de llaves de unicidad:
 +
 +  ALTER TABLE PAISES ADD CONSTRAINT 
 +   PAI_NOM_UK UNIQUE (NOMBRE);
 +
 +Finalmente, los valores que pueden tomar algunos atributos deben estar delimitados. Por ejemplo, el número de páginas de un libro no debería ser negativo. Esto se puede realizar con restricciones de chequeo:
 +  ALTER TABLE LIBROS ADD CONSTRAINT 
 +   LIB_PAGS_CK CHECK (NUMPAGS > 0);
 +
 +
 +  
 +===== Operaciones del DML =====
 +=== Inserción de Datos ===
 +   INSERT INTO PAISES (NOMBRE) VALUES ('COLOMBIA'), ('ALEMANIA');
 +
 +Note que no es necesario especificar un valor para el campo ID puesto que fue declarado como una secuencia (Tipo de dato SERIAL). 
 +
 +Ahora las otras tablas:
 +   INSERT INTO EDITORES (NOMBRE,PAI_ID) VALUES ('NORMA',1);
 +   INSERT INTO EDITORES (NOMBRE,PAI_ID) VALUES ('SPRINGER',2);
 +   INSERT INTO LIBROS (ISBN,TITULO,FECHAPUB,EDI_ID) VALUES ('9783540443285','Programme Evaluation and Treatment Choice','2003-01-01',2);
 +   INSERT INTO LIBROS (ISBN,TITULO,FECHAPUB,NUMPAGS,EDI_ID) VALUES ('9789588294780','Confieso','2011-01-11',152,1);
 +
 +=== Consultado Información ===
 +   SELECT * FROM LIBROS;
 +   SELECT * FROM PAISES;
 +   SELECT * FROM EDITORES ORDER BY NOMBRE;
 +   SELECT ISBN, TITULO FROM LIBROS WHERE NUMPAGS > 10;
 +
 +=== Actualizando Datos ===
 +  UPDATE LIBROS SET NUMPAGS=199 WHERE ISBN='9783540443285';
 +
 +=== Eliminando Datos ===
 +Insertemos primero un país 'no válido' :
 +   INSERT INTO PAISES (NOMBRE) VALUES ('XXX');
 +
 +Ahora lo eliminamos:
 +   DELETE FROM PAISES WHERE ID = 3;
 +
 +
 +===== Scripts Completos =====
 +Para ejecutar un archivo con sentencias SQL se utiliza:
 +   \i file.sql
 +
 +El archivo {{:materias:ejemplo.zip}} contiene un ejemplo completo. Utilizar:
 +   \i contactos.sql
 +
 +
 +
 +
 +
 +===== Taller =====
 +   - Completar el esquema de bases de datos anterior para considerar los autores de los libros y las materias afines a cada libro. 
 +   - Dado el siguiente esquema E/R, generar el script de tablas. 
 +{{:materias:er-ejemplo.png}}
 
materias/creacion_de_tablas.txt · Última modificación: 2011/03/10 17:18 por caolarte
Recent changes RSS feed Donate Powered by PHP Valid XHTML 1.0 Valid CSS Driven by DokuWiki