Proyectos. Práctica 2

Diseño y manipulación de datos

Débora Rubio Soliva - Proyectos

1. Realizar el diseño de una base de datos, con al menos 5 tablas.

a) Realizar el dibujo de las tablas y las relaciones con SQL Power Arquitecte.

b) Obtener el código SQL que genera el programa. Modificarlo si algo no es correcto y ejecutarlo en la base de datos topo, para crear las tablas y las relaciones.

CREATE SEQUENCE filmopedia.genero_id_genero_seq;

CREATE TABLE filmopedia.Genero ( TipoGenero VARCHAR NOT NULL, ID_genero INTEGER NOT NULL DEFAULT nextval('filmopedia.genero_id_genero_seq'), CONSTRAINT genero_pk PRIMARY KEY (TipoGenero));

ALTER SEQUENCE filmopedia.genero_id_genero_seq OWNED BY filmopedia.Genero.ID_genero;

CREATE SEQUENCE filmopedia.oscar_id_oscar_seq;

CREATE TABLE filmopedia.Oscar ( TipoOscar VARCHAR NOT NULL, ID_oscar INTEGER NOT NULL DEFAULT nextval('filmopedia.oscar_id_oscar_seq'), CONSTRAINT oscar_pk PRIMARY KEY (TipoOscar));

ALTER SEQUENCE filmopedia.oscar_id_oscar_seq OWNED BY filmopedia.Oscar.ID_oscar;

CREATE TABLE filmopedia.Director ( nombre_director VARCHAR NOT NULL, edad INTEGER NOT NULL, pais VARCHAR(15) NOT NULL, CONSTRAINT director_pk PRIMARY KEY (nombre_director));

CREATE TABLE filmopedia.Pelicula ( titulo VARCHAR NOT NULL, director VARCHAR NOT NULL, genero VARCHAR NOT NULL, oscar VARCHAR NOT NULL, year INTEGER NOT NULL, CONSTRAINT pelicula_pk PRIMARY KEY (titulo));

2

Débora Rubio Soliva - Proyectos

CREATE TABLE filmopedia.Actor ( nombre_actor VARCHAR NOT NULL, pais VARCHAR(15) NOT NULL, sueldo INTEGER NOT NULL, CONSTRAINT actor_pk PRIMARY KEY (nombre_actor));

CREATE TABLE filmopedia.Rel_actor_oscar ( nombre_actor VARCHAR NOT NULL, TipoOscar VARCHAR NOT NULL, CONSTRAINT rel_actor_oscar_pk PRIMARY KEY (nombre_actor, TipoOscar));

CREATE TABLE filmopedia.Rel_actor_pelicula ( nombre_actor VARCHAR NOT NULL, titulo VARCHAR NOT NULL, CONSTRAINT rel_actor_pelicula_pk PRIMARY KEY (nombre_actor, titulo));

ALTER TABLE filmopedia.Pelicula ADD CONSTRAINT genero_pelicula_fkFOREIGN KEY (genero)REFERENCES filmopedia.Genero (TipoGenero)ON DELETE NO ACTIONON UPDATE NO ACTIONNOT DEFERRABLE;

ALTER TABLE filmopedia.Pelicula ADD CONSTRAINT oscar_pelicula_fkFOREIGN KEY (oscar)REFERENCES filmopedia.Oscar (TipoOscar)ON DELETE NO ACTIONON UPDATE NO ACTIONNOT DEFERRABLE;

ALTER TABLE filmopedia.Rel_actor_oscar ADD CONSTRAINT oscar_rel_actor_oscar_fkFOREIGN KEY (TipoOscar)REFERENCES filmopedia.Oscar (TipoOscar)ON DELETE NO ACTIONON UPDATE NO ACTIONNOT DEFERRABLE;

ALTER TABLE filmopedia.Pelicula ADD CONSTRAINT director_pelicula_fkFOREIGN KEY (director)REFERENCES filmopedia.Director (nombre_director)ON DELETE NO ACTIONON UPDATE NO ACTIONNOT DEFERRABLE;

ALTER TABLE filmopedia.Rel_actor_pelicula ADD CONSTRAINT pelicula_rel_actor_pelicula_fkFOREIGN KEY (titulo)REFERENCES filmopedia.Pelicula (titulo)ON DELETE NO ACTIONON UPDATE NO ACTIONNOT DEFERRABLE;

ALTER TABLE filmopedia.Rel_actor_pelicula ADD CONSTRAINT actor_rel_actor_pelicula_fkFOREIGN KEY (nombre_actor)REFERENCES filmopedia.Actor (nombre_actor)ON DELETE NO ACTIONON UPDATE NO ACTIONNOT DEFERRABLE;

ALTER TABLE filmopedia.Rel_actor_oscar ADD CONSTRAINT actor_rel_actor_oscar_fkFOREIGN KEY (nombre_actor)REFERENCES filmopedia.Actor (nombre_actor)ON DELETE NO ACTIONON UPDATE NO ACTIONNOT DEFERRABLE;

3

Débora Rubio Soliva - Proyectos

c) En el diseño se deben utilizar tablas e integridad referencial, es decir, si en una tabla se ha de poner el tipo de trabajo, esa tabla debe hacer referencia a otra donde se listen los tipos de trabajo posibles, y lo mismo para el aparato utilizado.

2. Debe haber al menos una relación muchos a muchos.La relación muchos a muchos en este ejemplo creado es en la relación de las

tablas Actor/Oscar, ya que un actor puede tener muchos Oscars y un mismo Oscar lo pueden tener muchos actores; para ello se ha necesitado una tabla nueva que interrelaciona estas dos tablas con los campos identificativos de cada tabla.

Lo mismo sucede con las tablas Actor y Película, ya que muchos actores pueden aparecer en muchas películas y a su vez, muchas películas pueden contar con la participación de muchos actores.

3. Insertar, al menos, tres registros por tabla.

Los podríamos insertar directamente en PgAdmin III o desde la consola de Windows. Se ha hecho mediante el segundo método, siempre insertando en los campos que se necesiten en las tablas donde exista integridad referencial:

insert into filmopedia.Genero(TipoGenero) values ('Acción');insert into filmopedia.Genero(TipoGenero) values ('Drama');insert into filmopedia.Genero(TipoGenero) values ('Romance');insert into filmopedia.Genero(TipoGenero) values ('Aventura');insert into filmopedia.Genero(TipoGenero) values ('Comedia');

insert into filmopedia.Oscar(TipoOscar) values ('Nominado');insert into filmopedia.Oscar(TipoOscar) values ('Mejor pelicula');insert into filmopedia.Oscar(TipoOscar) values ('Mejor actor');insert into filmopedia.Oscar(TipoOscar) values ('Mejor director');

4

Débora Rubio Soliva - Proyectos

insert into filmopedia.Director(nombre_director,edad,pais) values ('John Avildsen',7,'EEUU');insert into filmopedia.Director(nombre_director,edad,pais) values ('James Cameron',6,'Canadá');insert into filmopedia.Director(nombre_director,edad,pais) values ('Ridley Scott',7,'UK');

insert into filmopedia.Pelicula(titulo,director,genero,oscar,year) values ('Rocky','John Avildsen','Acción','Mejor pelicula',1976);insert into filmopedia.Pelicula(titulo,director,genero,oscar,year) values ('Titanic','James Cameron','Romance','Mejor pelicula',1997);insert into filmopedia.Pelicula(titulo,director,genero,oscar,year) values ('Gladiator','Ridley Scott','Aventura','Mejor pelicula',2000);

insert into filmopedia.Actor(nombre_actor,pais,sueldo) values('Sylvester Stallone','EEUU',150000);insert into filmopedia.Actor(nombre_actor,pais,sueldo) values('Leonardo DiCaprio','EEUU',350000);insert into filmopedia.Actor(nombre_actor,pais,sueldo) values('Rusell Crowe','Nueva Zelanda',200000);

insert into filmopedia.Rel_actor_oscar(nombre_actor,TipoOscar) values ('Sylvester Stallone','Nominado');insert into filmopedia.Rel_actor_oscar(nombre_actor,TipoOscar) values ('Leonardo DiCaprio','Nominado');insert into filmopedia.Rel_actor_oscar(nombre_actor,TipoOscar) values ('Rusell Crowe','Mejor actor');

insert into filmopedia.Rel_actor_pelicula(nombre_actor,titulo) values ('Sylvester Stallone','Rocky');insert into filmopedia.Rel_actor_pelicula(nombre_actor,titulo) values ('Leonardo DiCaprio','Titanic');insert into filmopedia.Rel_actor_pelicula(nombre_actor,titulo) values ('Rusell Crowe','Gladiator');

4. Realizar varios actualizados de tablas, por lo menos tres. Añadimos una columna nueva, donde calculará el iva que tiene que pagar cada

actor de su sueldo:

La edad de cada uno de los directores la actualizamos y la multiplicamos por 10:

Cambiamos el nombre del país de cada actor:

5

Débora Rubio Soliva - Proyectos

5. Realizar al menos seis consultas donde intervenga más de una tabla, al menos una de cada una de las siguientes: cross join, left join.

Mostramos un ejemplo de cada uno de las siguientes consultas: - Cross join

- Left join

6. Realizar una vista que enlace varias tablas.

En esta vista, queremos juntar el nombre del actor, la película en la que participó y si por esa película se llevó algún tipo de galardón. Por lo que de la tabla

6

Débora Rubio Soliva - Proyectos

Rel_actor_oscar (RAO) obtendremos el galardón, de la tabla Rel_actor_pelicula (RAP) la película donde participó y ambas las relacionamos por el nombre, común.

7