Bases de Datos - UC3M · 2011. 2. 18. · Definición de Base de Datos Definición: Cualquier soporte para el almacenamiento persistente de datos Necesidad: Las aplicaciones telemáticas

Bases de DatosBases de Datos

Pedro J. Muñoz MerinoPedro J. Muñoz Merinohttp://www.it.uc3m.es/pedmume/http://www.it.uc3m.es/pedmume/

Definición de Base de Datos

● Definición: Cualquier soporte para el almacenamiento persistente de datos

● Necesidad: Las aplicaciones telemáticas necesitan manipular datos, los cuales deben estar presentes a lo largo del tiempo y no sólo en memoria, actualizarse adecuadamente, añadirse, borrarse, ser buscados, etc.

Pedro J. Muñoz Merino Diseño de Aplicaciones Telemáticas2

adecuadamente, añadirse, borrarse, ser buscados, etc. Ejemplos de aplicaciones con esta necesidad:

― Biblioteca que almacena libros, autores, etc.

― Tienda on-line que almacena productos, compradores, etc.

― Aplicaciones sociales que almacenan personas, relaciones, etc.

― Aplicaciones de realidad aumentada que almacenan puntos de interés, personas, etc.

Tipos de Bases de Datos

● Tipos de Bases de Datos

― Relacionales. Los datos se almacenan en un conjunto de tablas. Dichas tablas pueden establecer relaciones entre sí. Son la mayoría de las bases de datos actuales. Ejemplos de las más conocidas:

� Oracle

MySQL


� MySQL

� PostgreSQL

� Microsoft Access

― No Relacionales. Los datos se almacenan mediante otro procedimiento que no son tablas. Por ejemplo en ficheros de texto planos

Tablas en BD: Filas, Columnas, Clave Primaria

LOGIN CONTRASEÑA NOMBRE APELLIDOS DIRECCIÓN TELNO

PM PEDRO PEDRO MUÑOZ C/ ARRIBA 91353



JC JUAN JUAN CASTILLO C/ UNIV. 52436

Relaciones entre Tablas

USUARIOS

SUSCRIPCIONES

LOGIN CONTRASEÑA NOMBRE APELLIDOS DIRECCIÓN TELNO



LOGIN_USUARIO ID_ACTIVIDAD


ACTIVIDADES PABELLONES


ID NOMBRE NOM_PAB DESCRIP INICIO COSTE PLAZAS

1 FUTBOL A AA 17/02/11 23 12

2 TENIS B BB 19/03/11 54 6

PM 2

NOM_PAB LOCALIZACION

A LEGANES

Acceso a Bases de Datos

● Algunas operaciones posibles en BD son:

― Crear y borrar tablas

― Seleccionar una serie de datos en base a unos filtros

― Insertar nuevos datos

― Borrar datos existentes


― Actualizar datos

● Para realizar esas operaciones de manera similar con independencia de la implementación específica -> SQL

● A una base de datos se puede acceder

― Conectándose directamente

― A través de un programa -> API como JDBC

SQL: CREATE DATABASE

● Sintaxis

CREATE {DATABASE | SCHEMA} [IF NOT EXISTS] db_name [create_specification] ...

create_specification:[DEFAULT] CHARACTER SET [=] charset_name

| [DEFAULT] COLLATE [=] collation_name


● Ejemplo― CREATE DATABASE gestor_actividades;

| [DEFAULT] COLLATE [=] collation_name

SQL: DROP DATABASE

● Sintaxis

DROP {DATABASE | SCHEMA} [IF EXISTS] db_name


● Ejemplo― DROP DATABASE gestor_actividades;

SQL: CREATE TABLE

● Sintaxis

CREATE [TEMPORARY] TABLE [IF NOT EXISTS] tbl_name

(create_definition,...)[table_options]


SQL: create_definition

● Sintaxis

create_definition:col_name column_definition| [CONSTRAINT [symbol]] PRIMARY KEY

[index_type] (index_col_name,...) [index_type]| {INDEX|KEY} [index_name] [index_type]

(index_col_name,...) [index_type]


(index_col_name,...) [index_type]| [CONSTRAINT [symbol]] UNIQUE [INDEX|KEY]

[index_name] [index_type] (index_col_name,...) [index_type]

| {FULLTEXT|SPATIAL} [INDEX|KEY] [index_name] (index_col_name,...) [index_type]

| [CONSTRAINT [symbol]] FOREIGN KEY [index_name] (index_col_name,...) reference_definition

| CHECK (expr)

SQL: column_definition

● Sintaxis

column_definition:data_type [NOT NULL | NULL] [DEFAULT

default_value][AUTO_INCREMENT] [UNIQUE [KEY] | [PRIMARY]

KEY][COMMENT 'string'] [reference_definition]


[COMMENT 'string'] [reference_definition]

reference_definition:REFERENCES tbl_name (index_col_name,...) [MATCH FULL | MATCH PARTIAL | MATCH

SIMPLE] [ON DELETE reference_option][ON UPDATE reference_option]

reference_option: RESTRICT | CASCADE | SET NULL | NO ACTION

SQL: CREATE TABLE

● Ejemplos― CREATE TABLE CLIENTS ( LOGIN VARCHAR(16) NOT NULL, PASSWD

VARCHAR(16) NOT NULL, NAME VARCHAR(16) NOT NULL, SURNAME VARCHAR(16) NOT NULL, ADDRESS VARCHAR(16) NOT NULL, PHONE VARCHAR(16) NOT NULL, PRIMARY KEY (LOGIN));

― CREATE TABLE PAVILLIONS ( PAVILLION VARCHAR(16) NOT NULL, LOCATION VARCHAR(16), PRIMARY KEY (PAVILLION));


― CREATE TABLE ACTIVITIES ( ID INT UNSIGNED NOT NULL AUTO_INCREMENT, NAME VARCHAR(16), DESCRIPTION VARCHAR(32), START_DATE VARCHAR(16), COST FLOAT, PAVILLION_NAME VARCHAR(16) NOT NULL, TOTAL_PLACES INT UNSIGNED NOT NULL, OCCUPIED_PLACES INT UNSIGNED NOT NULL, PRIMARY KEY (ID), FOREIGN KEY (PAVILLION_NAME) REFERENCES PAVILLIONS(PAVILLION) DELETE ON CASCADE);

― CREATE TABLE SUBSCRIPTIONS ( CLIENT_LOGIN VARCHAR(16) NOT NULL, ACTIVITY_ID INT UNSIGNED NOT NULL, PRIMARY KEY (CLIENT_LOGIN, ACTIVITY_ID), FOREIGN KEY (CLIENT_LOGIN) REFERENCES CLIENTS(LOGIN), FOREIGN KEY (ACTIVITY_ID) REFERENCES ACTIVITIES(ID));

SQL: DROP TABLE

● Sintaxis

DROP [TEMPORARY] TABLE [IF EXISTS] tbl_name [, tbl_name] ... [RESTRICT | CASCADE]


● Ejemplo― DROP TABLE IF EXIST ACTIVITIES CASCADE;

SQL: ALTER TABLEALTER [IGNORE] TABLE tbl_name alter_specification [, alter_specification] ... alter_specification: table_options | ADD [COLUMN] col_name column_definition[FIRST | AFTER col_name ] | ADD [COLUMN] (col_name column_definition,...) | ADD {INDEX|KEY} [index_name] [index_type] (index_col_name,...) [index_type] | ADD [CONSTRAINT [symbol]] PRIMARY KEY [index_type] (index_col_name,...) [index_type] | ADD [CONSTRAINT [symbol]] UNIQUE [INDEX|KEY] [index_name] [index_type] (index_col_name,...) [index_type] | ADD [FULLTEXT|SPATIAL] [INDEX|KEY] [index_name] (index_col_name,...) [index_type] | ADD [CONSTRAINT [symbol]] FOREIGN KEY [index_name] (index_col_name,...) reference_definition | ALTER [COLUMN] col_name {SET DEFAULT literal | DROP DEFAULT} | CHANGE [COLUMN] old_col_name new_col_name column_definition[FIRST|AFTER col_name] | MODIFY [COLUMN] col_name column_definition[FIRST | AFTER col_name] | DROP [COLUMN] col_name | DROP PRIMARY KEY


● Ejemplo

― ALTER TABLE ACTIVITIES ADD MATERIAL VARCHAR(16);

[FIRST | AFTER col_name] | DROP [COLUMN] col_name | DROP PRIMARY KEY | DROP {INDEX|KEY} index_name | DROP FOREIGN KEY fk_symbol | DISABLE KEYS | ENABLE KEYS | RENAME [TO] new_tbl_name | ORDER BY col_name [, col_name] ... | CONVERT TO CHARACTER SET charset_name [COLLATE collation_name] | [DEFAULT] CHARACTER SET [=] charset_name [COLLATE [=] collation_name] | DISCARD TABLESPACE | IMPORT TABLESPACE

SQL: INSERT

● Sintaxis

INSERT [LOW_PRIORITY | DELAYED | HIGH_PRIORITY] [IGNORE] [INTO] tbl_name [(col_name,...)] {VALUES | VALUE} ({expr | DEFAULT},...),(...),... [ ON DUPLICATE KEY UPDATE col_name=expr [, col_name=expr] ... ]


● Ejemplos― INSERT INTO PAVILLIONS VALUES ('Ignacio Pinedo','Getafe');

― INSERT INTO ACTIVITIES (NAME, DESCRIPTION, START_DATE, COST, PAVILLION_NAME, TOTAL_PLACES, OCCUPIED_PLACES) VALUES ('aerobic','advanced aerobic','3 June','40','Alfredo Di Stefano', '50', '48');

― INSERT INTO ACTIVITIES (NAME, DESCRIPTION, COST) VALUES ('dual bike','competition‘, '25’);

SQL: DELETE

● Sintaxis

DELETE [LOW_PRIORITY] [QUICK] [IGNORE] FROM tbl_name

[WHERE where_condition][ORDER BY ...][LIMIT row_count]


SQL: where_condition y expresiones

● Where_condition es una expresión, sólo se hará la operación para las filas que cumplan que dicha expresión es cierta. Tipos de expresiones simples:

simple_expr: literal | identifier | function_call | simple_expr COLLATE collation_name | param_marker


simple_expr COLLATE collation_name | param_marker | variable | simple_expr || simple_expr | + simple_expr | - simple_expr | ~ simple_expr | ! simple_expr | BINARY simple_expr | (expr [, expr] ...) | ROW (expr, expr [, expr] ...) | (subquery) | EXISTS (subquery) | {identifier expr} | match_expr | case_expr | interval_expr

SQL: expresiones

bit_expr: bit_expr | bit_expr | bit_expr & bit_expr | bit_expr << bit_expr | bit_expr >> bit_expr | bit_expr + bit_expr | bit_expr - bit_expr | bit_expr * bit_expr | bit_expr / bit_expr | bit_expr DIV bit_expr | bit_expr MOD bit_expr | bit_expr % bit_expr | bit_expr ^ bit_expr | bit_expr + interval_expr | bit_expr - interval_expr | simple_expr


simple_expr

predicate: bit_expr [NOT] IN (subquery) | bit_expr [NOT] IN (expr [, expr] ...) | bit_expr [NOT] BETWEEN bit_expr AND predicate | bit_expr SOUNDS LIKE bit_expr | bit_expr [NOT] LIKE simple_expr [ESCAPE simple_expr] | bit_expr [NOT] REGEXP bit_expr | bit_expr

SQL: expresiones

expr: expr OR expr | expr || expr | expr XOR expr | expr AND expr | expr && expr | NOT expr | ! expr | boolean_primary IS [NOT] {TRUE | FALSE | UNKNOWN} | boolean_primaryboolean_primary: boolean_primary IS [NOT] NULL | boolean_primary <=> predicate | boolean_primary comparison_operator predicate | boolean_primary comparison_operator {ALL | ANY} (subquery) |


comparison_operator {ALL | ANY} (subquery) | predicatecomparison_operator: = | >= | > | <= | < | <> | != predicate: bit_expr [NOT] IN (subquery) | bit_expr [NOT] IN (expr [, expr] ...) | bit_expr [NOT] BETWEEN bit_expr AND predicate | bit_expr SOUNDS LIKE bit_expr | bit_expr [NOT] LIKE simple_expr [ESCAPE simple_expr] | bit_expr [NOT] REGEXP bit_expr | bit_expr

SQL: Funciones

● Funciones Escalares― UCASE()

― LCASE()

― MID()

― LEN()

― ROUND()

― NOW()


― NOW()

― FORMAT()

● Funciones de Cálculo― AVG()

― COUNT()

― FIRST()

― LAST()

― MAX()

― MIN()

― SUM()

SQL: DELETE

● Ejemplos

― DELETE FROM CLIENTS WHERE NAME=‘Pedro’ AND SURNAME=‘Muñoz’;

― DELETE FROM CLIENTS;

― DELETE FROM ACTIVITIES WHERE COST


BETWEEN 25 AND 50;

SQL: UPDATE

● Sintaxis

● Ejemplos

UPDATE [LOW_PRIORITY] [IGNORE] table_reference SET col_name1={expr1|DEFAULT} [, col_name2={expr2|DEFAULT}] ... [WHERE where_condition] [ORDER BY ...] [LIMIT row_count]


● Ejemplos

― UPDATE ACTIVITIES SET Description=‘paddle‘, COST=20WHERE Name=‘paddle’;

― UPDATE ACTIVITIES SET Description=‘paddle‘, COST=20

SQL: SELECT

● Sintaxis

SELECT [ALL | DISTINCT | DISTINCTROW ] [HIGH_PRIORITY] [STRAIGHT_JOIN] [SQL_SMALL_RESULT] [SQL_BIG_RESULT] [SQL_BUFFER_RESULT] [SQL_CACHE | SQL_NO_CACHE] [SQL_CALC_FOUND_ROWS] select_expr [, select_expr ...] [FROM table_references


select_expr [, select_expr ...] [FROM table_references [WHERE where_condition] [GROUP BY {col_name | expr | position} [ASC | DESC], ... [WITH ROLLUP]] [HAVING where_condition] [ORDER BY {col_name | expr | position} [ASC | DESC], ...] [LIMIT {[offset,] row_count | row_count OFFSET offset}] [PROCEDURE procedure_name(argument_list)] [INTO OUTFILE 'file_name' export_options | INTO DUMPFILE 'file_name' | INTO var_name [, var_name]] [FOR UPDATE | LOCK IN SHARE MODE]]

SQL: SELECT

● Ejemplos:

― SELECT * FROM ACTIVITIES, PAVILLIONS WHERE ACTIVITIES.COST <="+price+"AND ACTIVITIES.PAVILLION_NAME = PAVILLIONS.PAVILLION;

― SELECT DISTINCT NAME FROM ACTIVITIES, PAVILLIONS WHERE ACTIVITIES.COST <="+price+"AND ACTIVITIES.PAVILLION_NAME = PAVILLIONS.PAVILLION


ACTIVITIES.PAVILLION_NAME = PAVILLIONS.PAVILLION ORDER BY ACTIVITIES.NAME;

― SELECT * FROM ACTIVITIES WHERE NAME LIKE '%tennis’;

― SELECT PAVILLION_NAME, SUM(PLACES) FROM ACTIVITIES GROUP BY PAVILLION_NAME HAVING SUM(PLACES)>100;

JDBC: Drivers

● Obtener el driver del vendedor. Fichero JAR o se puede descomprimir. En la documentación vienen detalles como el nombre de la URL (jdbc:tipobd:nombrebd)

● Cargar el driver

― Class.forName(nombre_del_driver)

Propiedad del sistema jdbc.drivers


― Propiedad del sistema jdbc.drivers

● Obtener una conexión

― DriverManager.getConnection(url_bd)

― DriverManager.getConnection(url_bd, propiedades)

― DriverManager.getConnection(url_bd, usuario, contraseña)

● Establecer tiempo máximo de conexión: DriverManager.setLoginTimeout (segundos)

JDBC: Statements

● Creation of Statements

― createStatement()

― createStatement(p1,p2). TYPE_SCROLL_SENSITIVE, TYPE_SCROLL_INSENSITIVE, TYPE_FORWARD_ONLY

― createStatement(p1, p2, p3)

● Enviar peticiones SQL al servidor a través de un


● Enviar peticiones SQL al servidor a través de un Statement

― executeUpdate (sentencia_SQL),

― executeQuery (sentencia_SQL)

― executeBatch (), addBatch(sentencia_SQL), clearBatch()

― execute (sentencias_SQL)

JDBC: ResultSets

● Moverse por el ResultSet

― next()

― last(), absolute (int), isFirst(), isAfterLast(), moveToInsertRow(), previous(), afterLast(), relative(int), isBeforeFirst(), getRow(), moveToCurrentRow(), first(), beforeFirst(), isLast()

● Leer datos del ResultSet


● Leer datos del ResultSet

― getArray(int)

― getBigDecimal(int), getBoolean(int), getByte(int), getDate(int), getDouble(int), getFloat(int), getInt(int), getLong(int), getShort(int), getString(int), getTime(int)

JDBC: Liberación de recursos, excepciones y valores nulos

● Liberación de recursos

― Connection: close()

― Statement: close()

● Excepción de SQL: Casi todos los métodos de JDBC pueden devolver la excepción SQLExceptions. getNextException(), getErrorCode()


getNextException(), getErrorCode()

● NULL values: Método wasNull()

JDBC: Ejemplo con mysql (I):Query. javapackage activities.model;

import java.sql.*;

public class Query {

Statement stmt;

// Method that creates an Statement object from a Connection object

public Query(Connection con) throws SQLException {

stmt = con.createStatement();

}

// Method to close an Statement object

public void close() throws SQLException {

stmt.close();

}

}


}

// Method to execute SELECT SQL sentences

public ResultSet doSelect(String query) throws Exception {

ResultSet rs = stmt.executeQuery(query);

return rs;

}

// Method to execute INSERT or DELETE SQL sentences

public int doUpdate(String query) throws SQLException {

return stmt.executeUpdate(query);

}

JDBC: Ejemplo con mysql (II): DBInteraction.javapackage activities.model;

import java.sql.*;

import java.util.ArrayList;

public class DBInteraction {

private static final String dblogin = "";

private static final String dbpasswd = "";

Query q;

Connection con;

try {

System.out.println("Trying to connect...");

con = DriverManager.getConnection (url, "root", "admin2009");

System.out.println("Connected!");

}

catch(SQLException ex) {

System.err.print("SQLException: ");


public DBInteraction () throws SQLException {

String url="jdbc:mysql://localhost/sporting_manager";

try {

Class.forName("com.mysql.jdbc.Driver");

}

catch (java.lang.ClassNotFoundException e) {

System.err.print("ClassNotFoundException: ");

System.err.println(e.getMessage());

}

System.err.println(ex.getMessage());

}

q=new Query(con);

}

//Method to close the database Connection

public void close()throws Exception{

q.close();

con.close();

}

JDBC: Ejemplo con mysql (III): DBInteraction.java

public boolean authentication(String login, String pwd)throws Exception{

String list="SELECT * FROM CLIENTS WHERE LOGIN='"+login+"'";

String password=null;

ResultSet rs=q.doSelect(list); //rs will contain the row with login passed as parameter

if (rs.next()){ //Check if the Resultset is empty

password = rs.getString(2);

}

public void delusr(String login) throws Exception{

String delusr="DELETE FROM CLIENTS WHERE LOGIN='"+login+"'";

q.doUpdate(delusr);

}

public ArrayList listactivities(String selection) throwsException{

ArrayList data = new ArrayList();

ResultSet rs=q.doSelect(selection);


}

if (password == null){

return(false);

}

if(password.equals(pwd)){ // In case the password for this login in the table is the same as the one passed as parameter

return(true);

}

else {

return(false);

}

}

while (rs.next()) {

int id = rs.getInt(1);

String name = rs.getString(2);

String description = rs.getString(3);

String initial = rs.getString(4);

float cost = rs.getFloat(5);

String pavname = rs.getString(6);

int total = rs.getInt(7); int occupied = rs.getInt(8);

data.add(new Activity (id, name, description, initial, cost, pavname, total, occupied));

} return (data);

}

Prerrequisitos de esta temática

● Asignatura de Software de Comunicaciones, http://www.it.uc3m.es/pbasanta/SOFTCOM


Trabajo propio del alumno asociado a la sesión

● Tutorial de SQL (básico, avanzado, demo, funciones, quiz): http://www.w3schools.com/sql/default.asp

● Tutorial SQL http://www.firstsql.com/tutor6.htm

● Sentencias SQL en MySQL: http://dev.mysql.com/doc/refman/5.0/en/sql-syntax.html


● Tutorial de JDBC Basics http://download.oracle.com/javase/tutorial/jdbc/basics/index.html

Referencias Extra

● J. L. Weaver, K. Mukhar, and J. Crume, “Beginning J2EE 1.4. From Novice to Professional”, Apress, 2004, Capítulos 6 y 7

● SQL for Web Nerds, http://philip.greenspun.com/sql/

● Tutorial JDBC http://java.sun.com/developer/Books/JDBCTutorial/index.html


html

Documents

Bases de Datos - UC3M · 2011. 2. 18. · Definición de Base de Datos Definición: Cualquier soporte para el almacenamiento persistente de datos Necesidad: Las aplicaciones telemáticas