Prácticas en AR y SQL Primera Parte

(1)

Requerimientos:

Para poder llevar a cabo la práctica en SQL, es necesario que tenga instalado un SGBD que utilice MySQL con la bases de datos ESCUELA, con la siguiente estructura y datos:

Base da Datos ESCUELA

CREATE TABLE IF NOT EXISTS `alumnos` ( `NOMBRE` varchar(30) NOT NULL,

`COLONIA` varchar(30) NOT NULL, `EDAD` int(2) NOT NULL

) ENGINE=InnoDB DEFAULT CHARSET=latin1;

Tabla (Entidad) ALUMNOS

INSERT INTO `alumnos` (`NOMBRE`, `COLONIA`, `EDAD`) VALUES

('Manuel', 'La Vid', 15), ('José', 'Insurgentes', 14), ('María', 'La Soledad', 14), ('Jessica', 'Miramar', 15);

CREATE TABLE IF NOT EXISTS `docentes` ( `NOMBRE` varchar(30) NOT NULL,

`COLONIA` varchar(30) NOT NULL, `EDAD` int(2) NOT NULL

INSERT INTO `docentes` (`NOMBRE`, `COLONIA`, `EDAD`) VALUES

('Jesús', 'Compostela', 35), ('Mario', 'La Marina', 40), ('Lupita', 'La Soledad', 32), ('Jessica', 'La Vid', 25); Tabla (Entidad) DOCENTES

(2)

Requerimientos:

Para poder llevar a cabo la práctica en SQL, es necesario que tenga instalado un SGBD que utilice MySQL con la bases de datos BANCOS, con la siguiente estructura y datos:

Base da Datos BANCOS

Tabla (Entidad) DEPOSITO

Tabla (Entidad) PRESTAMO

IMPORTANTE: Para crear la base de datos y las tablas, seguir las instrucciones del docente

CREATE TABLE IF NOT EXISTS `deposito` ( `Nombre_sucursal` varchar(50) NOT NULL, `Numero_cuenta` int(10) NOT NULL, `Nombre_cliente` varchar(50) NOT NULL, `Saldo` int(10) NOT NULL

INSERT INTO `deposito` (`Nombre_sucursal`, `Numero_cuenta`, `Nombre_cliente`, `Saldo`) VALUES

('Downtown', 101, 'Johnson', 500), ('Mianus', 215, 'Smith', 700), ('Perryridge', 102, 'Hayes', 400), ('Round Hill', 305, 'Turner', 350), ('Perryridge', 201, 'Williams', 900), ('Redwood', 222, 'Lindsay', 700), ('Brigthon', 217, 'Green', 750), ('Downtown', 105, 'Green', 850);

CREATE TABLE IF NOT EXISTS `prestamo` ( `Nombre_sucursal` varchar(50) NOT NULL, `Numero_prestamo` int(10) NOT NULL, `Nombre_cliente` varchar(50) NOT NULL, `Cantidad` int(10) NOT NULL

INSERT INTO `prestamo` (`Nombre_sucursal`, `Numero_prestamo`, `Nombre_cliente`, `Cantidad`) VALUES

(3)

Fundamento teórico:

UNION

Ésta operación selecciona las tuplas que cumplen con ciertas condiciones en las relaciones que la conforman.

Se representa por .

En general debemos asegurar que las uniones se toman entre relaciones compatibles; por lo tanto, para que una operación de unión

sea válida, se deben cumplir dos condiciones:

1. Las relaciones R y S deben tener el mismo número de atributos.

2. Los dominios del atributo iésimo de S y del atributo iésimo de R deben ser los mismos

Se eliminan las tuplas duplicadas en el resultado obtenido.

R

S

Ejemplo:

Nombre Colonia Edad

Manuel La Vid 15 José Insurgentes 14 María La Soledad 14 Jessica Miramar 15

Jesús Compostela 35 Mario La Marina 40 Lupita La Soledad 32 Jessica La Vid 25

ALUMNOS DOCENTES

Manuel La Vid 15 José Insurgentes 14 María La Soledad 14 Jessica Miramar 15 Jesús Compostela 35 Mario La Marina 40 Lupita La Soledad 32 Jessica La Vid 25

(4)

Ejemplos: Nombre Manuel José María Jessica Jesús Mario Lupita NOTA: En este ejemplo sólo

se muestra una sola vez

Jessica.

π

_Nombre

(

ALUMNOS U DOCENTES

)

[Π_{Nombre}(ALUMNOS)_]

∪

_[Π

{Nombre}(DOCENTES)]

Mostrar todos los nombres de alumnos y docentes.

Sintaxis en Relational Algebra Translator (RAT):

π_Nombre,Edad(σ_Edad_{≥ 15}(ALUMNOS)) U π_Nombre,Edad(σ_Edad_{≥ 15}(DOCENTES))

Mostrar el nombre y la edad de alumnos y docentes que tengan una edad mayor o igual a 15 años.

Sintaxis en RAT:

[Π_{{Nombre,Edad}}(σ

{Edad ≥ 15}(ALUMNOS))]

∪

[ Π{Nombre,Edad}(σ{Edad ≥ 15}(DOCENTES))]

Nombre Edad Manuel 15 Jessica 15 Jesús 35 Mario 40 Lupita 32 Jessica 25 NOTA: En este ejemplo se

muestra a Jessica debido a que se proyecta Nombre y

Edad, el cual no es una tupla duplicada por tener diferente edad.

Fundamento teórico:

UNION

Instrucción en SQL: Instrucción en SQL:

(5)

Fundamento teórico:

PRODUCTO CARTESIANO

Ésta operación se utiliza para combinar relaciones (entidades).

Entrega una relación cuyo esquema corresponde a una combinación de todas las tuplas de R con cada una de las tuplas de S, y sus atributos corresponden a los de R seguidos por los de S.

Consideraciones:

1. Número de columnas de R x S = Columnas de R + Columnas de S.

2. Número de tuplas de R x S = Tuplas de R * Tuplas de S. Para evitar ambigüedades se debe usar la sintaxis: entidad.atributo

Ejemplo:

Manuel La Vid 15

José Insurgentes 14

María La Soledad 14

Jessica Miramar 15

Jesús Compostela 35

Mario La Marina 40

Lupita La Soledad 32

Jessica La Vid 25 ALUMNOS

DOCENTES

ALUMNOS DOCENTES

NOTA: En este ejemplo se muestran todos los atributos de cada ENTIDAD, dado que no se tiene una proyección (π) o selección (σ).

R

x

S

x

Nombre Colonia Edad Nombre Colonia Edad

Manuel La Vid 15 Jesús Compostela 35

José Insurgentes 14 Jesús Compostela 35

María La Soledad 14 Jesús Compostela 35

Jessica Miramar 15 Jesús Compostela 35

Manuel La Vid 15 Mario La Marina 40

José Insurgentes 14 Mario La Marina 40

María La Soledad 14 Mario La Marina 40

Jessica Miramar 15 Mario La Marina 40

Manuel La Vid 15 Lupita La Soledad 32

José Insurgentes 14 Lupita La Soledad 32

María La Soledad 14 Lupita La Soledad 32

Jessica Miramar 15 Lupita La Soledad 32

Manuel La Vid 15 Jessica La Vid 25

José Insurgentes 14 Jessica La Vid 25

María La Soledad 14 Jessica La Vid 25

(6)

Ejemplos:

σ_{ALUMNOS.Nombre = DOCENTES.Nombre} (ALUMNOS X DOCENTES)

σ_{{ALUMNOS.Nombre=DOCENTES.Nombre}}(ALUMNOS×DOCENTES)

Mostrar todas las tuplas en donde el nombre del alumno sea igual al nombre del docente.

π_{ALUMNO.Nombre, ALUMNO.Edad,DOCENTE.Nombre,DOCENTE.Edad}(σ_{ALUMNO.Colonia}_{= ‘La Vid’}_∧_{DOCENTE.Colonia =}

‘La Vid’ (ALUMNOS x DOCENTES))

Mostrar el nombre y la edad de todos los alumnos y docentes que habiten en la colonia “La Vid”.

Π_{_ALUMNOS_.Nombre,_ALUMNOS_.Edad,_DOCENTES_.Nombre,_DOCENTES_.Edad}(σ

{ALUMNOS.Colonia='La Vid'

∧DOCENTES.Colonia='La Vid'}(ALUMNOS×DOCENTES))

Fundamento teórico:

PRODUCTO CARTESIANO

Nombre Colonia Edad Nombre Colonia Edad

Jessica Miramar 15 Jessica La Vid 25 Nombre Edad Nombre Edad

Manuel 15 Jessica 25

Sintaxis en RAT: Sintaxis en RAT:

Instrucción en SQL: Instrucción en SQL:

(7)

Nombre_sucursal Numero_cuenta Nombre_cliente Saldo

Downtown 101 Johnson 500

Mianus 215 Smith 700

Perryridge 102 Hayes 400

Round Hill 305 Turner 350

Perryridge 201 Williams 900

Redwood 222 Lindsay 700

Brigthon 217 Green 750

Downtown 105 Green 850

Nombre_sucursal Numero_prestamo Nombre_cliente Cantidad

Downtown 17 Jones 1000

Redwood 23 Smith 2000

Perryridge 15 Hayes 1500

Downtown 14 Jackson 1500

Mianus 93 Curry 500

Round Hill 11 Turner 900

Pownal 29 Williams 1200

North Town 16 Adams 1300

Downtown 18 Johnson 2000

Perryridge 25 Glen 2500

Brigthon 10 Brooks 2200

Para los ejercicios de este tercer bloque, las tablas (entidades) a utilizar son las siguientes:

DEPOSITO

PRESTAMO

Operaciones a utilizar:

Proyección, Selección, Unión

y

Producto Cartesiano

(8)

III.1 Seleccionar las tuplas de la relación PRESTAMO en donde la sucursal Nombre_sucursal) sea Perryridge.

Algebra Relacional

σ

_{Nombre_sucursal}

_=‘

_Perryridge

_’

(

PRESTAMO

)

RAT:

SQL:

SELECT* FROMPRESTAMO WHERE Nombre_sucursal = 'Perryridge'

σ

_{{Nombre_sucursal='Perryridge'}}(PRESTAMO)

(9)

III.2 Seleccionar las tuplas de la relación PRESTAMO en donde la sucursal Nombre_sucursal) sea Perryridge y que tengan un préstamo (Cantidad) mayor a 2000.

Algebra Relacional

σ

_{Nombre_sucursal}

_=‘

_Perryridge

_’

_∧

_{Cantidad>2000}

(

PRESTAMO

)

RAT:

SQL:

SALIDA

SELECT* FROMPRESTAMO WHERE Nombre_sucursal = 'Perryridge' AND Cantidad > 2000

(10)

III.3 Obtener un listado de los clientes (Nombre_cliente) que tengan considerado un depósito, un préstamo o ambos.

DEPOSITO Algebra Relacional

πNombre_cliente(DEPOSITO) RAT:

Π{Nombre_cliente}(DEPOSITO)

SQL

SELECT Nombre_cliente FROM DEPOSITO

PRESTAMO Algebra Relacional

πNombre_cliente(PRESTAMO) RAT:

Π{Nombre_cliente}(PRESTAMO)

SQL

SELECT Nombre_cliente FROM PRESTAMO Si realizamos las acciones por separado en cada entidad,

tendremos lo siguiente: Si realizamos las acciones con la operación UNION tendremos lo_siguiente:

NOTA:Se detectan las tuplas repetidas

UNION

Algebra Relacional

π Nombre_cliente(DEPOSITO) ∪ π Nombre_cliente(PRESTAMO)

RAT:

SQL

SALIDA

[Π_{{Nombre_cliente}}(DEPOSITO)]∪[Π

{Nombre_cliente}(PRESTAMO)]

SELECTNombre_clienteFROMDEPOSITO UNIONSELECT

(11)

III.4 Mostrar el nombre de aquellos clientes (Nombre_cliente) que pertenezcan a la sucursal Perryridge (Nombre_sucursal), que tengan un préstamo, un depósito o ambos, en las entidades DEPOSITO y PRESTAMO.

Algebra Relacional

RAT:

SQL:

SALIDA

[

Π

_{{Nombre_cliente}}(σ

{Nombre_sucursal='Perryridge'}(DEPOSITO))]∪[Π{Nombre_cliente}(σ{Nombre_sucursal='Perryridge'}(PRESTAMO))]

SELECTNombre_clienteFROMDEPOSITO WHERENombre_sucursal = 'Perryridge' UNIONSELECTNombre_cliente FROM

PRESTAMO WHERENombre_sucursal = 'Perryridge'

(12)

III.5 Obtener todos los clientes que tengan un saldo (Saldo) menor al préstamo (Cantidad) registrado.

SALIDA

Algebra Relacional

RAT:

SQL:

Π_{_DEPOSITO_{.Nombre_cliente}}(σ

(13)

III.6 Mostar todos aquellos clientes (Nombre_cliente) que tengan registrado un préstamo (Cantidad) y tengan registrado un saldo (Saldo) registro.