coleccion de mariposas

Universidad Nacional Autónoma de Honduras

UNAH

Facultad de Ingeniería

Departamento de Ingeniería en sistemas

Base de Datos II

Proyecto Colección de Mariposas

Catedrático:

Ing. Eduardo Gross

Integrantes:

Claudia Melissa Espinal 20061007063

Juan José López 20041008352

Sección:

14:01

Tegucigalpa M.D.C. 6 de Diciembre del 2010

Bases de Datos II Claudia Melissa Espinal Juan Jose Lopez

1

INDICE

Pág.

Introducción………………………………………………2

Objetivos…………………………………………………..3

Análisis completo del enunciado………………4

Modelo Conceptual…………………….……………5

Análisis de todos los tipos de entidad

Análisis de todos los tipos de interrelación

Modelo relacional……………………………………11

Generar cada tabla con todas las relaciones

Normalizar el modelo

Diccionario de datos

Construcción y el uso de la base de datos

Manipulación de la base de datos

Creación del programa

Conclusiones…………………………………..………26


2

INTRODUCCION

En el presente informe se documento la resolucion del

segundo problema asignado en clase de Bases de Datos II,

llamado “Colección de Mariposas”, se presentara desde la

definicion del problema hasta la creacion de la base de

datos como una de las propuesta a la resolucion de este

ejercicio.

Para este segundo ejercicio se seguira utilizadon el motor de

bases de datos SQLServer 2005 y la plataforma de

programacion Visual Studio.Net 2005.


3

OBJETIVOS

Análisis y resolución del problema que permita la

construcción de un modelo entidad-relación y modelo

conceptual.

Práctica de la manipulación de la base de datos a

través de la realización de consultas.

Crear una aplicación que cumpla con todos los

requerimientos presentados.


4

ANALISIS COMPLETO DEL ENUNCIADO

El ejercicio asignado nos plantea el problema de que se desea crear una base de datos en

la cual se almacene información referente a las mariposas que son objeto de estudio como

también lo son para pertenecer en una colección de estas. Se debe mantener información

de las mariposas capturadas, fecha y lugar de captura del ejemplar así como la persona

que ha hecho la captura.

Actualmente una de las principales tareas de los biólogos es el estudio de la población de

las especies naturales que pueblan España, siendo uno de los insectos unos de los reinos

más estudiados, y dentro de este, el orden de los lepidópteros, mariposas mas

concretamente.

El problema da a conocer ciertos requisitos que se deberán cumplir para la realización del

mismo.

Estos requisitos son los siguientes:

Se considera que un ejemplar de mariposa pertenece a una única especie. Una

especie pertenece a un único género y un género a una única familia natural.

El nombre científico de la especie de mariposas es único, pero el nombre común

que tiene una especie puede variar según la zona geográfica donde se encuentra la

mariposa. Si bien dentro de una determinada zona el nombre común de una

especie es único.

Ya sea para su observación o para formar parte de una colección, la mariposa ha

de ser capturada primero. La captura de cada ejemplar la realiza solo una persona.

Una determinada persona solo puede ser propietaria de una colección, y los

ejemplares de mariposa que pertenecen a esta colección pueden haber sido

capturados por otras personas.

Se desea mantener información de las familias, géneros y especies a las que

pertenecen los ejemplares de mariposas, independientemente de que haya sido

capturado algún ejemplar de los mismos.

Una mariposa solo puede pertenecer a una colección y una colección estará al

menos formada por un ejemplar de mariposa (como es lógico).

El nombre de una zona geográfica donde es capturado un ejemplar es único; es

decir, se considera que no existen dos zonas geográficas con el mismo nombre; no

siendo de interés mantener información añadida sobre los lugares en donde las

mariposas son capturadas.


5

MODELO CONCEPTUAL

MODELO ENTIDAD-RELACION

FEC_INICIO

EJEMPLAR

ESPECIE

ZONA_GEOGRAFICA

MARIPOSA_OBSERVACION

MARIPOSA_COLLECCION

COLECCION

GENERO

FAMILIA

PERSONA

1

*

ID

ZONA

PARA

ID_MARIPOSA

TIEMPO_OBSERVACION

NOMBRE_COMUN

NOMBRE_ESPECIE ID

NOMBRE_CIENTIFICO

FEC_CAPTURA

ID GENERO

ID

ID FAMILIA

ID NOMBRES

APELLIDOS DNI

ID NOMBRE

VALOR_ESTIMADO

ID_MARIPOSA PRECIO

tiene

pertenece

pertenece pertenece

Fue_capturado_en captura

tiene

pertenece

1

*

1

*

1

*

*

1

1

1 *

*

1

1

*

*

1

1

1


6

ANALISIS DE TODOS LOS TIPOS DE ENTIDAD

Familia: es la entidad donde se prentende guardar la informacion del tipo de familia al que

pertenece una mariposa, esta posee atributos como ser un id que identifica a la familia y

un campo familia, el cual nos dice cual es la familia.

Especie: es la entidad donde se especifica o da a conocer la especie a la cual pertenece una

mariposa, esta entidad tiene atributos como un id que sirve para identificar la especie y un

campo llamado especie que muestra las especies existentes.

Genero: esta entidad muestra el genero al cual puede pertenecer una mariposa, tiene

atributos como un id el cual identifica a cada uno de los generos existenntes, tambien esta

el atributo genero el que especifica que tipo de genero es la mariposa.

Ejemplar: es la entidad que muestra cual es el ejemplar de mariposa existente ya que

puede ser un ejemplar para colección o bien un ejemplar para observacion. Esta entidad

tiene bien un atributo llamado id para identificar el ejemplar y otro atributo llamado fecha

de captura que es la fecha en la que fue capturada la mariposa.

Zona_geografica: esta entidad muestra la zona geografica en la que fue la captura de la

mariposa, tiene atributos como ser un id para identificar la zona y un campo llamado zona

el cual muestra la zona de la captura.

Persona: esta entidad es la que refleja la persona quien realizo la captura de la mariposa,

en esta se guarda los datos de la persona para ello tiene campos como ser un id para

identificar a la persona como unica, un campo nombre que es el nombre de la persona, un

campo apellidos que guarda los apellidos de la persona y un campo DNI que es el numero

de identidad de la persona.


7

Colección: es la entidad para identificar la colección de mariposas existentes, tiene

atributos como fecha de inicio para saber la fecha cuando inicio, un campo nombre el cual

es el nombre de la colección, un atributo valor estimado que es el valor en dinero de

cuanto es el precio de la colección y tambien tiene un id para identificar a la misma.

Mariposa_coleccion:es una entidad debil dado que sino existen ejemplares no existen

mariposas para colección. Sirve para guardar todas aquellas mariposas que fueron

capturadas y estan en una colección, tiene atributos como ser un id de la mariposa

capturada y el precio de la misma.

Mariposa_observacion: es una entidad debil dado que sino existen ejemplares no existen

mariposas para observacion. En esta entidad se guardan todas aquellas mariposas que

solo son para observacion y tiene atributos como ser el id para identificar a la mariposa, y

un tiempo de observacion que es el tiempo en que tiene a la mariposa para observarla y

luego la dejan libre.


8

ANALISIS DE TODOS LOS TIPOS DE INTERRELACION

Es una relacion de uno a muchos

En la relacion muestra que en una familia puede existir o pertenecer muchos generos.


En la relacion muestra que en un genero pueden existir o pertenecer muchas especies.


En la relacion muestra que en una especie pueden existir o pertenecer muchos ejemplares.

Es una relacion de muchos a uno

En la relacion muestra que muchos ejemplares fueron capturados en una zona geografica

o bien en una zona geografica fueron capturados muchos ejemplares.

FAMILIA GENERO 1 *

pertenece

GENERO ESPECIE 1 *

pertenece

EJEMPLAR ESPECIE 1 *

pertenece

ZONA_GEOGRAFICA EJEMPLAR

Fue_capturado_en

* 1


9


La relacion muestra que muchos ejemplares son capturados por una persona o bien una

mariposa solo puede ser capturada por una unica persona.


La relacion muestra que muchos coleccionespueden ser de una persona o bien una persona

puede tener muchas colecciones.


La relacion muestra que muchos coleccionespueden ser de una persona o bien una persona

puede tener muchas colecciones.

Es una relacion de muchos a muchos

La relacion muestra que una especie tiene muchos nombres comunes en muchas zonas

especificas.

PERSONA EJEMPLAR * 1

captura

COLECCION PERSONA * 1

tiene

MARIPOSA_COLLECCION COLECCION * 1

pertenece

ESPECIE ZONA_GEOGRAFICA

tiene

* * 1 1

NOMBRE_COMUN


10

Generalización: relación de uno a uno

Nos muestra que una mariposa_coleccion y una mariposa_observacion son un tipo de

ejemplar que hay y estos tipos van a tener todos los atributos de la entidad ejemplar

aparte de sus propios atributos que los diferencian uno del otro.

La relacion es de uno a uno dado que un ejemplar solo puede ser de mariposa_coleccion o

mariposa_observacion.

MARIPOSA_OBSERVACION MARIPOSA_COLLECCION

EJEMPLAR

1

1

1


11

MODELO RELACIONAL

DIAGRAMA RELACIONAL ANTES DE NORMALIZAR

Familia(id, Familia)

genero(id, genero, id_ Familia)

especie(id, nombre_especie, nombre_cientifico, id_ genero)

ejemplar(id, id_capturador, id_zona, fec_captura, id_especie, para)

Zona_geografica(id, zona)

Nombre_comun(id_especie,id_ zona, nombre_comun)

persona(id, nombres, apellidos, DNI)

coleccion(id, nombre, valor_estimado, id_dueno, fec_inicio)

Mariposa_coleccion(id_mariposa, precio, id_coleccion)

Mariposa_observacion(id_mariposa, tiempo_observacion)


12

DIAGRAMA RELACIONAL NORMALIZADO

Familia(id, Familia)

genero(id, genero, id_ Familia)

especie(id, nombre_especie, nombre_cientifico, id_ genero)

ejemplar(id, id_capturador, id_zona, fec_captura, id_especie, id_para)

Zona_geografica(id, zona)

Nombre_comun(id_especie,id_ zona, nombre_comun)

persona(id, nombres, apellidos, DNI)

coleccion(id, nombre, valor_estimado, id_dueno, fec_inicio)

Mariposa_coleccion(id_mariposa, precio, id_coleccion)

Mariposa_observacion(id_mariposa, tiempo_observacion)

Mariposa_para(id, para)


13


14

CONSTRUCCION Y USO DE LA BASE DE DATOS

Utilizando el motor sqlSERVER hicimos la base de datos

Creacion de las tablas

Tabla mariposa_para SET ANSI_PADDING ON GO CREATE TABLE [dbo].[mariposa_para]( [id] [int] NOT NULL, [para] [varchar](50) NULL, CONSTRAINT [PK_mariposa_para] PRIMARY KEY CLUSTERED ( [id] ASC )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY] ) ON [PRIMARY] GO SET ANSI_PADDING OFF GO Tabla persona SET ANSI_PADDING ON GO CREATE TABLE [dbo].[persona]( [id] [int] NOT NULL, [nombre] [varchar](50) NOT NULL, [apellidos] [varchar](50) NOT NULL, [DNI] [varchar](20) NULL, CONSTRAINT [PK_persona] PRIMARY KEY CLUSTERED ( [id] ASC )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY] ) ON [PRIMARY] GO SET ANSI_PADDING OFF GO Tabla zona_geografica SET ANSI_PADDING ON GO CREATE TABLE [dbo].[zona_geografica]( [id] [int] NOT NULL, [zona] [varchar](50) NOT NULL, CONSTRAINT [PK_zona_geografica] PRIMARY KEY CLUSTERED (


15

[id] ASC )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY] ) ON [PRIMARY] GO SET ANSI_PADDING OFF GO Tabla familia SET ANSI_PADDING ON GO CREATE TABLE [dbo].[familia]( [id] [int] NOT NULL, [familia] [varchar](50) NOT NULL, CONSTRAINT [PK_familia] PRIMARY KEY CLUSTERED ( [id] ASC )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY] ) ON [PRIMARY] GO SET ANSI_PADDING OFF GO Tabla genero SET ANSI_PADDING ON GO CREATE TABLE [dbo].[genero]( [id] [int] NOT NULL, [genero] [varchar](50) NOT NULL, [id_familia] [int] NULL, CONSTRAINT [PK_genero] PRIMARY KEY CLUSTERED ( [id] ASC )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY] ) ON [PRIMARY] GO SET ANSI_PADDING OFF GO Tabla especie SET ANSI_PADDING ON GO CREATE TABLE [dbo].[especie]( [id] [int] NOT NULL, [nombre_especie] [varchar](50) NULL, [nombre_cientifico] [varchar](50) NULL,


16

[id_genero] [int] NULL, CONSTRAINT [PK_espacie_1] PRIMARY KEY CLUSTERED ( [id] ASC )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY] ) ON [PRIMARY] GO SET ANSI_PADDING OFF GO Tabla nombre_comun SET ANSI_PADDING ON GO CREATE TABLE [dbo].[nombre_comun]( [id_especie] [int] NULL, [id_zona] [int] NULL, [nombre_comun] [varchar](50) NULL ) ON [PRIMARY] GO SET ANSI_PADDING OFF GO Tabla ejemplar SET QUOTED_IDENTIFIER ON GO CREATE TABLE [dbo].[ejemplar]( [id] [int] NOT NULL, [id_capturador] [int] NULL, [id_zona] [int] NULL, [fec_captura] [datetime] NULL, [id_especie] [int] NULL, [id_para] [int] NULL, CONSTRAINT [PK_ejemplar] PRIMARY KEY CLUSTERED ( [id] ASC )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY] ) ON [PRIMARY] GO Tabla coleccion SET ANSI_PADDING ON GO CREATE TABLE [dbo].[coleccion]( [id] [int] NOT NULL, [nombre] [varchar](50) NULL, [valor_estimado] [bigint] NULL,


17

[id_dueño] [int] NULL, [fec_inicio] [datetime] NULL, CONSTRAINT [PK_coleccion] PRIMARY KEY CLUSTERED ( [id] ASC )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY] ) ON [PRIMARY] GO SET ANSI_PADDING OFF GO Tabla mariposa_coleccion SET QUOTED_IDENTIFIER ON GO CREATE TABLE [dbo].[mariposa_coleccion]( [id_mariposa] [int] NOT NULL, [precio] [real] NULL, [id_coleccion] [int] NULL, CONSTRAINT [PK_mariposa_coleccion] PRIMARY KEY CLUSTERED ( [id_mariposa] ASC )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY] ) ON [PRIMARY] GO Tabla mariposa_observacion SET ANSI_PADDING ON GO CREATE TABLE [dbo].[mariposa_observacion]( [id_mariposa] [int] NOT NULL, [tiempo_observacion] [varchar](50) NULL, CONSTRAINT [PK_mariposa_observacion] PRIMARY KEY CLUSTERED ( [id_mariposa] ASC )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY] ) ON [PRIMARY] GO SET ANSI_PADDING OFF GO


18

Creacion de las relaciones(foreing key)

ALTER TABLE [dbo].[coleccion] WITH CHECK ADD CONSTRAINT [FK_coleccion_persona] FOREIGN KEY([id_dueño]) REFERENCES [dbo].[persona] ([id]) ON UPDATE CASCADE ON DELETE CASCADE GO ALTER TABLE [dbo].[coleccion] CHECK CONSTRAINT [FK_coleccion_persona] GO ALTER TABLE [dbo].[ejemplar] WITH CHECK ADD CONSTRAINT [FK_ejemplar_especie] FOREIGN KEY([id_especie]) REFERENCES [dbo].[especie] ([id]) ON UPDATE CASCADE ON DELETE CASCADE GO ALTER TABLE [dbo].[ejemplar] CHECK CONSTRAINT [FK_ejemplar_especie] GO ALTER TABLE [dbo].[ejemplar] WITH CHECK ADD CONSTRAINT [FK_ejemplar_mariposa_para] FOREIGN KEY([id_para]) REFERENCES [dbo].[mariposa_para] ([id]) ON UPDATE CASCADE ON DELETE CASCADE GO ALTER TABLE [dbo].[ejemplar] CHECK CONSTRAINT [FK_ejemplar_mariposa_para] GO ALTER TABLE [dbo].[ejemplar] WITH CHECK ADD CONSTRAINT [FK_ejemplar_persona] FOREIGN KEY([id_capturador]) REFERENCES [dbo].[persona] ([id]) ON UPDATE CASCADE ON DELETE CASCADE GO ALTER TABLE [dbo].[ejemplar] CHECK CONSTRAINT [FK_ejemplar_persona] GO ALTER TABLE [dbo].[ejemplar] WITH CHECK ADD CONSTRAINT [FK_ejemplar_zona_geografica] FOREIGN KEY([id_zona]) REFERENCES [dbo].[zona_geografica] ([id]) ON UPDATE CASCADE ON DELETE CASCADE GO ALTER TABLE [dbo].[ejemplar] CHECK CONSTRAINT [FK_ejemplar_zona_geografica] GO


19

ALTER TABLE [dbo].[especie] WITH CHECK ADD CONSTRAINT [FK_especie_genero] FOREIGN KEY([id_genero]) REFERENCES [dbo].[genero] ([id]) ON UPDATE CASCADE ON DELETE CASCADE GO ALTER TABLE [dbo].[especie] CHECK CONSTRAINT [FK_especie_genero] GO ALTER TABLE [dbo].[genero] WITH CHECK ADD CONSTRAINT [FK_genero_familia] FOREIGN KEY([id_familia]) REFERENCES [dbo].[familia] ([id]) ON UPDATE CASCADE ON DELETE CASCADE GO ALTER TABLE [dbo].[genero] CHECK CONSTRAINT [FK_genero_familia] GO ALTER TABLE [dbo].[mariposa_coleccion] WITH CHECK ADD CONSTRAINT [FK_mariposa_coleccion_coleccion] FOREIGN KEY([id_coleccion]) REFERENCES [dbo].[coleccion] ([id]) ON UPDATE CASCADE ON DELETE CASCADE GO ALTER TABLE [dbo].[mariposa_coleccion] CHECK CONSTRAINT [FK_mariposa_coleccion_coleccion] GO ALTER TABLE [dbo].[mariposa_coleccion] WITH CHECK ADD CONSTRAINT [FK_mariposa_coleccion_ejemplar] FOREIGN KEY([id_mariposa]) REFERENCES [dbo].[ejemplar] ([id]) ON UPDATE CASCADE ON DELETE CASCADE GO ALTER TABLE [dbo].[mariposa_coleccion] CHECK CONSTRAINT [FK_mariposa_coleccion_ejemplar] GO ALTER TABLE [dbo].[mariposa_observacion] WITH CHECK ADD CONSTRAINT [FK_mariposa_observacion_ejemplar] FOREIGN KEY([id_mariposa]) REFERENCES [dbo].[ejemplar] ([id]) ON UPDATE CASCADE ON DELETE CASCADE GO ALTER TABLE [dbo].[mariposa_observacion] CHECK CONSTRAINT [FK_mariposa_observacion_ejemplar] GO


20

ALTER TABLE [dbo].[nombre_comun] WITH CHECK ADD CONSTRAINT [FK_nombre_comun_especie] FOREIGN KEY([id_especie]) REFERENCES [dbo].[especie] ([id]) ON UPDATE CASCADE ON DELETE CASCADE GO ALTER TABLE [dbo].[nombre_comun] CHECK CONSTRAINT [FK_nombre_comun_especie] GO ALTER TABLE [dbo].[nombre_comun] WITH CHECK ADD CONSTRAINT [FK_nombre_comun_zona_geografica] FOREIGN KEY([id_zona]) REFERENCES [dbo].[zona_geografica] ([id]) ON UPDATE CASCADE ON DELETE CASCADE GO ALTER TABLE [dbo].[nombre_comun] CHECK CONSTRAINT [FK_nombre_comun_zona_geografica] GO


21

MANIPULACION DE LA BASE DE DATOS

Creacion de vistas

1. Obtener la informacion correspondiente a las especies de mariposas existentes

en la base de datos, las cuales han sido capturadas despues de abril de 1999

SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO CREATE VIEW [dbo].[Especie_Capturada] AS SELECT dbo.especie.nombre_especie AS Especie FROM dbo.especie INNER JOIN dbo.ejemplar ON dbo.especie.id = dbo.ejemplar.id_especie WHERE (dbo.ejemplar.fec_captura >= CONVERT(DATETIME, '1999-05-01 00:00:00', 102)) GO

2. Obtener los diferentes nombres comunes y los nombres cientificos de cada una de

las especies cuyos ejemplares hayan sido capturados entre los anos 1996 y 1997,

todos ellos ordenados por el nombre de la zona.

SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO CREATE VIEW [dbo].[Nombres] AS SELECT TOP (100) PERCENT dbo.especie.nombre_cientifico, dbo.nombre_comun.nombre_comun FROM dbo.especie INNER JOIN dbo.ejemplar ON dbo.especie.id = dbo.ejemplar.id_especie INNER JOIN dbo.zona_geografica ON dbo.ejemplar.id_zona = dbo.zona_geografica.id INNER JOIN dbo.nombre_comun ON dbo.especie.id = dbo.nombre_comun.id_especie WHERE (DATEPART(yyyy, dbo.ejemplar.fec_captura) BETWEEN 1996 AND 1997) ORDER BY dbo.zona_geografica.zona GO


22

3. Obtener la mariposa mas cara y mas barata de todas las capturadas, la persona

quien la capturo y a que colección pertenece.

SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO CREATE VIEW [dbo].[Cara_Barata] AS SELECT dbo.mariposa_coleccion.precio AS Precio, dbo.persona.nombre, dbo.coleccion.nombre AS Coleccion FROM dbo.persona INNER JOIN dbo.ejemplar ON dbo.persona.id = dbo.ejemplar.id_capturador INNER JOIN dbo.mariposa_coleccion ON dbo.ejemplar.id = dbo.mariposa_coleccion.id_mariposa INNER JOIN dbo.coleccion ON dbo.mariposa_coleccion.id_coleccion = dbo.coleccion.id WHERE (dbo.mariposa_coleccion.precio = (SELECT MAX(precio) AS Expr1 FROM dbo.mariposa_coleccion AS mariposa_coleccion_1)) UNION SELECT mariposa_coleccion_2.precio AS Precio, persona_1.nombre, coleccion_1.nombre AS Expr1 FROM dbo.persona AS persona_1 INNER JOIN dbo.ejemplar AS ejemplar_1 ON persona_1.id = ejemplar_1.id_capturador INNER JOIN dbo.mariposa_coleccion AS mariposa_coleccion_2 ON ejemplar_1.id = mariposa_coleccion_2.id_mariposa INNER JOIN dbo.coleccion AS coleccion_1 ON mariposa_coleccion_2.id_coleccion = coleccion_1.id WHERE (mariposa_coleccion_2.precio = (SELECT MIN(precio) AS Expr1 FROM dbo.mariposa_coleccion AS mariposa_coleccion_1)) GO

4. Obtener la familia de las mariposas cuyos ejemplares han sido liberados.

SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO CREATE VIEW [dbo].[familias_liberadas] AS SELECT DISTINCT dbo.familia.familia FROM dbo.familia INNER JOIN dbo.genero ON dbo.familia.id = dbo.genero.id_familia INNER JOIN dbo.especie ON dbo.genero.id = dbo.especie.id_genero INNER JOIN dbo.ejemplar ON dbo.especie.id = dbo.ejemplar.id_especie WHERE (dbo.ejemplar.id_para = 1) GO


23

5. Obtener la informacion sobre las familias, generos y especies de mariposas que

nunca hayan sido capturados.

SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO CREATE VIEW [dbo].[Info_familia_genero_especie] AS SELECT DISTINCT dbo.especie.nombre_especie, dbo.genero.genero, dbo.familia.familia FROM dbo.especie INNER JOIN dbo.genero ON dbo.especie.id_genero = dbo.genero.id INNER JOIN dbo.familia ON dbo.genero.id_familia = dbo.familia.id except SELECT DISTINCT dbo.especie.nombre_especie, dbo.genero.genero, dbo.familia.familia FROM dbo.ejemplar INNER JOIN dbo.especie ON dbo.ejemplar.id_especie = dbo.especie.id INNER JOIN dbo.genero ON dbo.especie.id_genero = dbo.genero.id INNER JOIN dbo.familia ON dbo.genero.id_familia = dbo.familia.id GO

6. Obtener ordenadas por el numero de capturas informacion de las zonas

geograficas en las que se hayan realizado mas de 6 capturas.

SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO CREATE VIEW [dbo].[Zona_Mas6Capturas] AS SELECT TOP (100) PERCENT dbo.zona_geografica.zona, COUNT(dbo.ejemplar.id) AS [Numero Mariposas] FROM dbo.zona_geografica INNER JOIN dbo.ejemplar ON dbo.zona_geografica.id = dbo.ejemplar.id_zona GROUP BY dbo.zona_geografica.zona HAVING (COUNT(dbo.ejemplar.id) > 6) ORDER BY [Numero Mariposas] GO


24

7. Crear una vista para todas las colecciones iniciadas a partir de 1996 y que

permita consultar simultaneamente los atributos dni, nombre, apellidos y precio

estimado.

SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO CREATE VIEW [dbo].[Colecciones_iniciadas] AS SELECT dbo.coleccion.nombre AS Coleccion, dbo.coleccion.valor_estimado, dbo.persona.nombre, dbo.persona.apellidos, dbo.persona.DNI FROM dbo.coleccion INNER JOIN dbo.persona ON dbo.coleccion.id_dueño = dbo.persona.id WHERE (DATEPART(yyyy, dbo.coleccion.fec_inicio) >= 1996) GO

8. Realizar una consulta sobre la vista colección

SELECT * FROM Colecciones_iniciadas

SELECT * FROM Colecciones_iniciadas WHERE valor_estimado>10000 SELECT nombre, coleccion, valor_estimado FROM Colecciones_iniciadas WHERE DNI='0801-1985-54561’

9. Obtener la informacion de las personas que han capturado ejemplares antes del

5 de noviembre de 1980, pero que no tienen ninguna colección.

SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO CREATE VIEW [dbo].[personas_sinColeccion] AS SELECT DISTINCT dbo.persona.id, dbo.persona.nombre, dbo.persona.apellidos, dbo.persona.DNI FROM dbo.persona INNER JOIN dbo.ejemplar ON dbo.persona.id = dbo.ejemplar.id_capturador WHERE (dbo.ejemplar.fec_captura < '5-11-1980') except SELECT dbo.persona.id, dbo.persona.nombre, dbo.persona.apellidos, dbo.persona.DNI FROM dbo.persona INNER JOIN dbo.coleccion ON dbo.persona.id = dbo.coleccion.id_dueño GO


25

10. Actualizar el valor estimado de las colecciones a valor real de la suma de los

ejemplares que la componen.

GO CREATE PROCEDURE [dbo].[actualizar_precio_estimado] AS BEGIN -- SET NOCOUNT ON added to prevent extra result sets from -- interfering with SELECT statements. SET NOCOUNT ON; update coleccion set valor_estimado = (select sum(precio) from mariposa_coleccion where id_coleccion =id) END GO

11. Actualizar el precio de los ejemplares de las colecciones de nuestra base de datos

en un tanto porciento dado y según el precio de los mismos, considerar la

existencia de tres intervalos: menor de 7000, de 7000 a 10000 y mayor de 10000.

GO CREATE PROCEDURE [dbo].[actualizar_PrecioEjemplares] @porcentaje real AS BEGIN -- SET NOCOUNT ON added to prevent extra result sets from -- interfering with SELECT statements. SET NOCOUNT ON; update mariposa_coleccion set precio= (case when precio<7000 then precio*(1+@porcentaje) when precio>=7000 and precio<10000 then precio*(1+(2*(@porcentaje))) else precio*(1+(3*(@porcentaje))) end) END GO


26

CONCLUSIONES

Para ayuda de resolucion del problema se tuvieron que crear algunas

vistas el motor para facilitar la visualizacion de los datos requeridos.

El uso de los procedimientos hace que sea mas facil la manipulacion de

la base de datos.

Se considero cada supuesto planteado al inicio del problema para la

delimitacion del mismo.

coleccion de mariposas

Documents