Base de Datos - Modelo Entidad Relación

Solución Primer Parcial

Fundamentación Teórica. 

Enuncie dos políticas de seguridad para un DBA:

Crear las bases de datos con el usuario postgres.

NO dar permiso de creación de base de datos a los usuarios.

NO dar permiso de creación de usuarios a los usuarios.

LIMITAR el número de conexiones a las BD.

CONCEDER solo privilegios de lectura o escritura sobre ciertas tablas. 

Plantee la Sentencia SQL, que remueva todos los permisos para todos los usuarios de la base de datos “Inventarios”

REVOKE ALL ON DATABASE Inventarios FROM public;

Plantee la Sentencia SQL, para alterar un usuario llamado “atletico” con password “bucaros” y que tenga permisos de LOGIN y además no le permita crear bases de Datos.

ALTER user atletico LOGIN PASSWORD ‘bucaros’ NOCREATEDB;

Plantee las Sentencia SQL, para eliminar el usuario “nanaya” y otra para observar todos los usuarios del sistema.

DROP USER nanaya; -> Para eliminar un usuario.

SELECT * FROM pg_user; -> Para observar todos los usuarios del sistema.

Enuncie los pasos a seguir desde el PG Admin, para no ingresar con el usuario postgres sino con un nuevo usuario llamado “Santiago” y nos conectaremos a la base de datos “Inventarios”.

Clic derecho sobre el nombre del servidor -> propiedades, aparecerá una ventana emergente con la configuración de la conexión, cambie la propiedad username por Santiago y la propiedad Maintenance DB por Inventarios

SQL COMANDOS DDL Y DML

Fundamentación Práctica

Teniendo en cuenta las figuras CONSOLA, JUEGO y CONSOLA_JUEGO, utilizar el lenguaje SQL del Sistema Manejador de Bases de Datos postgresql para:

Crear la base de datos (relacionado con su contexto)

CREATE DATABASE consolas

Crear las tablas (con sus respectivas llaves primarias y foráneas, si es el caso)

CREATE TABLE consola (

id_consola int,

nombre varchar (50),

tipo varchar (15),

marca varchar (50),

PRIMARY KEY (id_consola)

);

CREATE TABLE juego(

id_juego int,

titulo varchar(50),

genero varchar(50),

PRIMARY KEY (id_juego)

);

CREATE TABLE consola_juego(

id_consola int,

id_juego int,

stock int, 

PRIMARY KEY (id_consola, id_juego),

FOREIGN KEY (id_consola) REFERENCES consola(id_consola),

FOREIGN KEY (id_juego) REFERENCES juego(id_juego)

);

Insertar los registros (que aparecen en las figuras)

INSERT INTO consola  VALUES(1,'PS4','Sobremesa','Sony');

INSERT INTO consola(id_consola, nombre, tipo, marca) VALUES(2,'XBOX ONE','Sobremesa','Microsoft');

INSERT INTO consola(id_consola, nombre, tipo, marca) VALUES(3,'3DS','Portatil','Nintendo');

SELECT * FROM consola;

INSERT INTO juego(id_juego, titulo, genero) VALUES(1,'Dark Souls III', 'ROL');

INSERT INTO juego (id_juego, titulo, genero) VALUES(2,'Project Cars', 'Conduccion');

INSERT INTO juego(id_juego, titulo, genero) VALUES(3,'Metal Gear Solid V', 'Accion');

INSERT INTO juego (id_juego, titulo, genero) VALUES(4,'Killer Instinct', 'Lucha');

INSERT INTO juego(id_juego, titulo, genero) VALUES(5,'Metroid Prime', 'Accion');

INSERT INTO juego(id_juego, titulo, genero) VALUES(6,'Dragon Quest VII', 'ROL');

SELECT * FROM juego;

INSERT INTO consola_juego (id_consola, id_juego, stock) VALUES(1,1, 45);

INSERT INTO consola_juego (id_consola, id_juego, stock) VALUES(1,2, 30);

INSERT INTO consola_juego (id_consola, id_juego, stock) VALUES(1,3, 15);

INSERT INTO consola_juego (id_consola, id_juego, stock) VALUES(2,3, 15);

INSERT INTO consola_juego (id_consola, id_juego, stock) VALUES(2,4, 19);

INSERT INTO consola_juego (id_consola, id_juego, stock) VALUES(3,5, 34);

INSERT INTO consola_juego (id_consola, id_juego, stock) VALUES(3,6, 23);

SELECT * FROM consola_juego;

Consulta: Mostrar los tipos de consolas que hay registrados

SELECT tipo FROM consola;

Consulta: Nombre de los juegos que sólo están en 3DS

SELECT titulo FROM juego

WHERE juego.id_juego IN(

SELECT id_juego FROM consola_juego

WHERE consola_juego.id_consola IN(

SELECT id_consola FROM consola

WHERE consola.nombre='3DS')

);

Consulta: Stock total de aquellos juegos cuyo género sea de Acción

SELECT SUM(consola_juego.stock) FROM consola_juego

WHERE consola_juego.id_juego IN(

SELECT id_juego FROM juego

WHERE juego.genero='accion');

Consulta: Titulo de aquellos juegos con stock total que supere las 20 unidades

SELECT juego.titulo FROM juego

WHERE juego.id_juego IN(

SELECT id_juego FROM consola_juego

GROUP BY id_juego

HAVING SUM(consola_juego.stock) > 20);

Realizar un UPDATE – Utilizado para modificar los valores de los campos y registros especificados.

UPDATE juego SET titulo='The Legend of Zelda'

WHERE titulo='Metroid Prime';

Realizar un DELETE – Utilizado para eliminar registros de una tabla de una base de datos

DELETE FROM consola WHERE id_consola=1;

Insertar un registro adicional por cada tabla.

INSERT INTO consola  VALUES(1,'XS','Sobremesa','Sony');

Repaso Parcial - DatabaseNomina

CREATE TABLE temple1 (
nuempl CHAR(6) NOT NULL,
nombre CHAR(12) NOT NULL,
inicial CHAR(1) NOT NULL,
apellido CHAR(15) NOT NULL,
dept CHAR(3) NOT NULL,
tlfn CHAR(4),
feching  DATE NOT NULL,
codtra SMALLINT NOT NULL,
niveduc SMALLINT NOT NULL,
sexo CHAR(1) NOT NULL,
fechnac DATE  NOT NULL,
salario DECIMAL(9,2) NOT NULL
 );
 CREATE TABLE tdepar2 (
numdep CHAR(3) NOT NULL,
nomdep CHAR(36) NOT NULL,
numdirec CHAR(6) NOT NULL
);
 INSERT INTO temple1 ( nuempl, nombre, inicial, apellido, dept, tlfn, feching, codtra, niveduc, sexo, fechnac, salario )
VALUES ( 10, 'CRISTINA', 'I', 'HERNANDEZ', 'A00', 2070, '01.01.1965', 66, 18, 'M', '14.08.1933', 5275 ),
( 20, 'MIGUEL', 'L', 'TAPIA', 'B01', 2140, '10.10.1973', 61, 18, 'H', '02.02.1948', 4125 ),
( 30, 'SALOME', 'A', 'KEMPES', 'C01', 2080, '05.04.1975', 60, 20, 'M', '11.05.1941', 3825 ),
( 50, 'JUAN', 'B', 'GARRIDO', 'E01', 1507, '17.08.1949', 58, 16, 'H', '15.09.1925', 4017 ),
( 60, 'ISIDRO', 'F', 'SUAREZ', 'D11', 2290, '14.09.1973', 55, 16, 'H', '07.07.1945', 3225 ),
( 70, 'EVA', 'D', 'PUENTE', 'D21', 2400, '30.09.1980', 56, 16, 'M', '26.05.1953', 3617 ),
( 90, 'ELENA', 'W', 'HERRANZ', 'E11', 2230, '15.08.1970', 55, 16, 'M', '15.05.1941', 2945 ),
( 100, 'TOMAS', 'Q', 'SOLER', 'E21', 2170, '19.06.1980', 54, 14, 'H', '18.12.1956', 2615 ),
( 110, 'VICENTE', 'G', 'LUENGO', 'A00', 1140, '16.05.1958', 58, 19, 'H', '05.11.1929', 4650 ),
( 120, 'SIMON', '', 'OTERO', 'A00', 2022, '05.12.1963', 58, 14, 'H', '18.10.1942', 2925 ),
( 130, 'DOLORES', 'M', 'QUINTANA', 'C01', 2140, '28.07.1971', 55, 16, 'M', '15.09.1925', 2380 ),
( 140, 'HELIODORA', 'A', 'NIETO', 'C01', 2210, '15.12.1976', 56, 18, 'M', '19.01.1946', 2842 ),
( 150, 'BRUNO', '', 'ALVAREZ', 'D11', 2250, '12.02.1971', 55, 16, 'H', '17.05.1947', 2528 ),
( 160, 'ELISA', 'R', 'PINTO', 'D11', 1307, '11.10.1977', 54, 17, 'M', '12.04.1955', 2225 ),
( 170, 'MATEO', 'J', 'YARZA', 'D11', 2050, '15.09.1978', 54, 16, 'H', '05.01.1951', 2468 ),
( 180, 'MARINA', 'S', 'SANDOVAL', 'D11', 1050, '07.07.1973', 53, 17, 'M', '21.02.1949', 2134 ),
( 190, 'JAIME', 'H', 'WALKER', 'D11', 2100, '27.07.1974', 53, 16, 'H', '25.06.1952', 2045 ),
( 200, 'DAVID', '', 'BONDIA', 'D11', 2340, '03.03.1966', 55, 16, 'H', '29.05.1941', 2774 ),
( 210, 'WENCESLAO', 'T', 'JURADO', 'D11', 2310, '11.04.1979', 52, 17, 'H', '23.02.1953', 1827 ),
( 220, 'JIMENA', 'K', 'LUQUE', 'D11', 2300, '29.08.1968', 55, 18, 'M', '19.03.1948', 2984 ),
( 230, 'JAIME', 'J', 'JIMENEZ', 'D21', 1080, '21.11.1966', 53, 14, 'H', '30.05.1935', 2218 ),
( 240, 'SALVADOR', 'M', 'MARTINEZ', 'D21', 2260, '05.12.1979', 55, 17, 'H', '31.03.1954', 2876 ),
( 250, 'DANIEL', 'S', 'SIERRA', 'D21', 2357, '30.10.1969', 52, 15, 'H', '12.11.1939', 1918 ),
( 260, 'SUSANA', 'P', 'JUNQUERA', 'D21', 2302, '11.09.1975', 52, 16, 'M', '05.10.1936', 1725 ),
( 270, 'MARIA', 'L', 'PEREZ', 'D21', 2217, '30.09.1980', 55, 15, 'M', '23.05.1953', 2738 ),
( 280, 'ENGRACIA', 'R', 'SANCHEZ', 'E11', 2007, '24.03.1967', 54, 17, 'M', '15.05.1941', 2598 ),
( 290, 'JUAN', 'R', 'PALACIOS', 'E11', 2192, '30.05.1980', 42, 12, 'H', '09.07.1946', 1380 ),
( 300, 'PEDRO', 'I', 'SIERRA', 'E11', 3522, '19.06.1972', 48, 14, 'H', '27.10.1936', 1775 ),
( 310, 'MATILDE', 'F', 'SERNA', 'E11', 2130, '12.09.1964', 43, 12, 'M', '21.04.1931', 1574 ),
( 320, 'RAMON', 'V', 'MORAN', 'E21', 7112, '07.07.1965', 52, 16, 'H', '11.08.1932', 1995 ),
( 330, 'WILLY', '', 'LERMA', 'E21', 1132, '23.02.1976', 55, 14, 'H', '18.07.1941', 2537 ),
( 340, 'JAVIER', 'R', 'GIL', 'E21', 1162, '05.05.1947', 54, 16, 'H', '17.05.1926', 2384 ),
( 999, 'ANGEL', 'E', 'MALDONADO', 'E11', '----', '22.03.1988', 69, 20, 'H', '12.11.1965', 7500 );
INSERT INTO tdepar2 ( numdep, nomdep, numdirec )
VALUES ( 'A00', 'CENTRO PROCESOS', 000010 ),
( 'B01', 'PLANIFICACION', 000020 ),
( 'C01', 'INFORMACION', 000030 ),
( 'D01', 'DESARROLLO', '' ),
( 'D11', 'FABRICACION', 000060 ),
( 'D21', 'ADMINISTRACION', 000070 ),
( 'E01', 'SERVICIOS', 000050 ),
( 'E11', 'OPERACIONES', 000090 ),
( 'E21', 'SOFTWARE', 000100 );
select *from tdepar2;
select *from temple1;
SELECT nuempl, nombre, apellido, tlfn , nomdep, numdep
FROM temple1
INNER JOIN tdepar2 ON (temple1.dept = tdepar2.numdep)
WHERE sexo='M' AND (tdepar2.numdep LIKE 'D%' OR tdepar2.numdep LIKE 'E%');
SELECT numdirec, nombre, apellido, codtra, salario
FROM temple1
INNER JOIN tdepar2 ON (temple1.dept = tdepar2.numdep)
WHERE salario > 2000 AND feching > '01/01/1975'
ORDER BY salario DESC;
SELECT apellido, dept, salario
FROM temple1
WHERE dept IN ( 'A00', 'B01', 'C01' , 'D01')
ORDER BY dept, salario DESC;

SELECT salario, nombre
FROM (
SELECT AVG(salario) as salario, COUNT(*) AS nombre, dept
FROM temple1
WHERE NOT codtra > 54
GROUP BY dept
) AS result
WHERE NOT nombre < 3
ORDER BY dept ASC;

SELECT dept, AVG(salario) , COUNT(*)
    FROM temple1
    WHERE codtra <= 54
    GROUP BY dept
    HAVING COUNT(*) > 2
    ORDER BY dept
;

SELECT *
FROM temple1
WHERE dept IN ( 'D11', 'E11' ) AND apellido LIKE 'S%';

Consultas - Primary Key - Foreing Key

drop table if exists libros;
drop table if exists editoriales;

 create table libros(
  codigo serial,
  titulo varchar(40),
  autor varchar(30),
  codigoeditorial smallint,
  primary key(codigo)
 );

 create table editoriales(
  codigo serial,
  nombre varchar(20),
  primary key (codigo)
 );

 insert into editoriales(nombre) values('Emece');
 insert into editoriales(nombre) values('Planeta');
 insert into editoriales(nombre) values('Siglo XXI');

 select *from editoriales;


 insert into libros(titulo,autor,codigoeditorial) values('El aleph','Borges',1);
 insert into libros(titulo,autor,codigoeditorial) values('Martin Fierro','Jose Hernandez',2);
 insert into libros(titulo,autor,codigoeditorial) values('Aprenda PHP','Mario Molina',2);

 select *from libros;

 -- Agregamos una restricción "foreign key" a la tabla "libros":
 alter table libros
   add constraint FK_libros_codigoeditorial
   foreign key (codigoeditorial)
   references editoriales(codigo);

 -- Ingresamos un libro con un código de editorial existente:
 insert into libros(titulo,autor,codigoeditorial) values('Aprenda ASP.Net','Jose Paez',2);

 -- Ingresamos un libro con un código de editorial inexistente:
 insert into libros(titulo,autor,codigoeditorial) values('JSP basico','Tornado Luis',3);
 --Aparece un mensaje de error y no se ejecuta la inserción.


https://www.tutorialesprogramacionya.com/postgresqlya/temarios/descripcion.php?inicio=50&cod=211&punto=53

Ejercicios desarrollados en clase - Abril 16 de 2020

CREATE TABLE MUNICIPIOS1(
id_municipio int,
nombre varchar,
nombre INT,
poblacion int
);


insert into MUNICIPIOS values (10, 'Zapatoca', 20000);
insert into MUNICIPIOS1 values (20, 'Medellin', 400000);
insert into MUNICIPIOS values (30, 'Bogotá', 9000000);
insert into MUNICIPIOS values (40, 'Oiba', 5000);
insert into MUNICIPIOS values (50, 'Cali', 2000000);

select *from MUNICIPIOS1;

SELECT nombre FROM MUNICIPIOS WHERE poblacion>5000 ORDER BY poblacion;

SELECT nombre FROM MUNICIPIOS WHERE poblacion>=5000 ORDER BY nombre;

CREATE TABLE MUNICIPIOS_PRUEBA (
id_municipio int,
nombre varchar,
poblacion int,
primary key (id_municipio)
);

SElect * from MUNICIPIOS1;
SElect * from MUNICIPIOS_PRUEBA;


INSERT INTO MUNICIPIOS1
SELECT * FROM MUNICIPIOS_PRUEBA;

UPDATE MUNI SET nombre='Medellín' WHERE id_municipio=10;

ALTER TABLE MUNICIPIOS_PRUEBA RENAME TO MUNI;

SELECT * FROM MUNI;

DELETE FROM MUNICIPIOS WHERE id_municipio=10;

DELETE FROM MUNICIPIOS1 WHERE id_municipio=20;

drop table MUNICIPIOS1;

SELECT nombre FROM MUNI WHERE poblacion=900000 and id_municipio=20;

Ejercicios en Clase - Abril 2 de 2020

--ejemplo de asignar una consulta a una variable

create or replace function fn_categoria(id int) returns varchar as $$
declare
x varchar;
begin
x=(select cat_nombre from categorias where cat_id=id);
return x;
end;
$$ language plpgsql

select fn_categoria(1);

create table categorias (
cat_id int,
cat_nombre varchar
);

insert into categorias (cat_id,cat_nombre)
values (5,'Hogar');

select * from categorias;

-------------------------------------------------------------------------

Con una función, dependiendo de un parámetro de entrada que podrá ser: cinco o todos, listar de la tabla “categorias” el respectivo número de registros.

create or replace function listarCategorias(opcion varchar,out c1 int, out c2 varchar) returns setof record as $$

begin

if opcion='cinco' then

return query select * from categorias limit 3;

end if;

if opcion='todos' then

return query select * from categorias;

end if;

end;

$$ language plpgsql

select * from listarCategorias('tres');

create table categorias (

cat_id int,

cat_nombre varchar

);

insert into categorias (cat_id,cat_nombre)

values (5,'Hogar');

select * from categorias;

_______________________________________

Crear una función para insertar una categoría, enviando como parámetro el nombre de la categoría, y finalmente mostrar el registro reciente insertado.

create or replace function insertarMostrar(nombre varchar, codigo int, out c1 int, out c2 varchar) returns setof RECORD as $$
begin
insert into categorias(cat_nombre, cat_id) values(nombre, codigo);
return query select * from categorias order by cat_id desc limit 1;
end;
$$ language plpgsql

select*from insertarMostrar('Verduras',2);


drop table categorias;

create table categorias (
cat_id int,
cat_nombre varchar
);

select * from categorias;

________________________________________

Crear un procedimiento almacenado que lea 3 parámetros, el primero será para identificar el caso, el segundo es un parámetro del id y el tercero es la descripción para la tabla prueba, si el número ingresado en el primer parámetro es 1, se deberá realizar una inserción en la tabla pruebas con los parámetros 2 y 3. si el primer parámetro es 2 se debe realizar una actualización del registro correspondiente al id enviado en el parámetro 2 y se debe actualizar la descripción del parámetro 3. Y finalmente si el parámetro 1 es igual a 3, se debe eliminar el registro correspondiente al id enviado en el parámetro 2.

CREATE TABLE prueba
(
idprueba integer,
descripcion varchar(40),
PRIMARY KEY(idprueba)
);

create or replace function abm_prueba(band numeric, cidprueba integer, cdescripcion varchar) returns void as $$
begin
case band
when 1 then
INSERT INTO prueba VALUES (cidprueba, cdescripcion);
when 2 then
update prueba set descripcion=cdescripcion where idprueba=cidprueba;
when 3 then
delete from prueba where idprueba=cidprueba;
end case;
end
$$ language plpgsql;

select abm_prueba(3,2,'PISA');

select * from prueba;

Ejercicio en clase - Abril 1 - Funciones

--Buscar en la tabla productos por el nombre enviando un parámetro

CREATE OR REPLACE FUNCTION prod_funcion2(nom varchar) RETURNS SETOF varchar AS $$
BEGIN
RETURN QUERY SELECT prod_nombre FROM productos WHERE prod_nombre LIKE nom || '%';
END;
$$ LANGUAGE plpgsql;

SELECT prod_funcion2('C');


create table productos (
prod_id int,
 prod_nombre varchar,
 prod_precio decimal
 );

 insert into productos (prod_id, prod_nombre,prod_precio)
  values (30, 'Gel', 5000);

 select *from productos;

___________________________________________________________

CREATE or replace FUNCTION nuevosdatos() RETURNS TABLE(NOMBRE varchar, TOTAL real, FECHA timestamp) AS $$

DECLARE

i record;

BEGIN

drop table med;

create table med(id integer,nombre varchar,total real,fecha timestamp);

FOR i IN SELECT * FROM medicamentos LOOP

insert into med values(i.codigo,i.nombre,i.total,current_timestamp);

END LOOP;

return query select med.nombre,med.total,med.fecha from med;

END;

$$ LANGUAGE plpgsql;

--getpgusername()

--current_user

select * from nuevosdatos()

drop table medicamentos;

create table medicamentos (codigo int, nombre varchar, total real, fecha timestamp);

insert into medicamentos (codigo,nombre,total,fecha)

values (20,'Dolex',400,'1-03-2020');

select * from medicamentos;

select * from med;