/****** Objeto:  base de datos grupo2   fecha de la secuencia de comandos: 26/11/2004 17:12:36 ******/
IF EXISTS (SELECT name FROM master.dbo.sysdatabases WHERE name = N'grupo2')
	DROP DATABASE [grupo2]
GO

CREATE DATABASE [grupo2]  ON (NAME = N'grupo2_Data', FILENAME = N'C:\Archivos de programa\Microsoft SQL Server\MSSQL\data\grupo2_Data.MDF' , SIZE = 1, FILEGROWTH = 10%) LOG ON (NAME = N'grupo2_Log', FILENAME = N'C:\Archivos de programa\Microsoft SQL Server\MSSQL\data\grupo2_Log.LDF' , SIZE = 1, FILEGROWTH = 10%)
 COLLATE Modern_Spanish_CI_AS
GO

exec sp_dboption N'grupo2', N'autoclose', N'false'
GO

exec sp_dboption N'grupo2', N'bulkcopy', N'false'
GO

exec sp_dboption N'grupo2', N'trunc. log', N'false'
GO

exec sp_dboption N'grupo2', N'torn page detection', N'true'
GO

exec sp_dboption N'grupo2', N'read only', N'false'
GO

exec sp_dboption N'grupo2', N'dbo use', N'false'
GO

exec sp_dboption N'grupo2', N'single', N'false'
GO

exec sp_dboption N'grupo2', N'autoshrink', N'false'
GO

exec sp_dboption N'grupo2', N'ANSI null default', N'false'
GO

exec sp_dboption N'grupo2', N'recursive triggers', N'false'
GO

exec sp_dboption N'grupo2', N'ANSI nulls', N'false'
GO

exec sp_dboption N'grupo2', N'concat null yields null', N'false'
GO

exec sp_dboption N'grupo2', N'cursor close on commit', N'false'
GO

exec sp_dboption N'grupo2', N'default to local cursor', N'false'
GO

exec sp_dboption N'grupo2', N'quoted identifier', N'false'
GO

exec sp_dboption N'grupo2', N'ANSI warnings', N'false'
GO

exec sp_dboption N'grupo2', N'auto create statistics', N'true'
GO

exec sp_dboption N'grupo2', N'auto update statistics', N'true'
GO

use [grupo2]
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[FK_Mostrador_Aeropuerto]') and OBJECTPROPERTY(id, N'IsForeignKey') = 1)
ALTER TABLE [dbo].[Mostrador] DROP CONSTRAINT FK_Mostrador_Aeropuerto
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[FK_Vuelo_Aeropuerto]') and OBJECTPROPERTY(id, N'IsForeignKey') = 1)
ALTER TABLE [dbo].[Vuelo] DROP CONSTRAINT FK_Vuelo_Aeropuerto
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[FK_Avion_Compania]') and OBJECTPROPERTY(id, N'IsForeignKey') = 1)
ALTER TABLE [dbo].[Avion] DROP CONSTRAINT FK_Avion_Compania
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[FK_Mostrador_Compania]') and OBJECTPROPERTY(id, N'IsForeignKey') = 1)
ALTER TABLE [dbo].[Mostrador] DROP CONSTRAINT FK_Mostrador_Compania
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[FK_Asiento_Avion]') and OBJECTPROPERTY(id, N'IsForeignKey') = 1)
ALTER TABLE [dbo].[Asiento] DROP CONSTRAINT FK_Asiento_Avion
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[FK_Vuelo_Avion]') and OBJECTPROPERTY(id, N'IsForeignKey') = 1)
ALTER TABLE [dbo].[Vuelo] DROP CONSTRAINT FK_Vuelo_Avion
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[FK_TarjetaDeEmbarque_Mostrador]') and OBJECTPROPERTY(id, N'IsForeignKey') = 1)
ALTER TABLE [dbo].[TarjetaDeEmbarque] DROP CONSTRAINT FK_TarjetaDeEmbarque_Mostrador
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[FK_Reserva_Vuelo]') and OBJECTPROPERTY(id, N'IsForeignKey') = 1)
ALTER TABLE [dbo].[Reserva] DROP CONSTRAINT FK_Reserva_Vuelo
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[FK_TarjetaDeEmbarque_Vuelo]') and OBJECTPROPERTY(id, N'IsForeignKey') = 1)
ALTER TABLE [dbo].[TarjetaDeEmbarque] DROP CONSTRAINT FK_TarjetaDeEmbarque_Vuelo
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[FK_Pasajero_Reserva]') and OBJECTPROPERTY(id, N'IsForeignKey') = 1)
ALTER TABLE [dbo].[Pasajero] DROP CONSTRAINT FK_Pasajero_Reserva
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[FK_TarjetaDeEmbarque_Pasajero]') and OBJECTPROPERTY(id, N'IsForeignKey') = 1)
ALTER TABLE [dbo].[TarjetaDeEmbarque] DROP CONSTRAINT FK_TarjetaDeEmbarque_Pasajero
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[FK_Asiento_TarjetaDeEmbarque]') and OBJECTPROPERTY(id, N'IsForeignKey') = 1)
ALTER TABLE [dbo].[Asiento] DROP CONSTRAINT FK_Asiento_TarjetaDeEmbarque
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[FK_Equipaje_TarjetaDeEmbarque]') and OBJECTPROPERTY(id, N'IsForeignKey') = 1)
ALTER TABLE [dbo].[Equipaje] DROP CONSTRAINT FK_Equipaje_TarjetaDeEmbarque
GO

/****** Objeto: tabla [dbo].[Asiento]    fecha de la secuencia de comandos: 18/01/2005 23:39:31 ******/
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[Asiento]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[Asiento]
GO

/****** Objeto: tabla [dbo].[Equipaje]    fecha de la secuencia de comandos: 18/01/2005 23:39:31 ******/
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[Equipaje]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[Equipaje]
GO

/****** Objeto: tabla [dbo].[TarjetaDeEmbarque]    fecha de la secuencia de comandos: 18/01/2005 23:39:31 ******/
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[TarjetaDeEmbarque]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[TarjetaDeEmbarque]
GO

/****** Objeto: tabla [dbo].[Pasajero]    fecha de la secuencia de comandos: 18/01/2005 23:39:31 ******/
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[Pasajero]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[Pasajero]
GO

/****** Objeto: tabla [dbo].[Reserva]    fecha de la secuencia de comandos: 18/01/2005 23:39:31 ******/
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[Reserva]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[Reserva]
GO

/****** Objeto: tabla [dbo].[Vuelo]    fecha de la secuencia de comandos: 18/01/2005 23:39:31 ******/
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[Vuelo]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[Vuelo]
GO

/****** Objeto: tabla [dbo].[Avion]    fecha de la secuencia de comandos: 18/01/2005 23:39:31 ******/
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[Avion]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[Avion]
GO

/****** Objeto: tabla [dbo].[Mostrador]    fecha de la secuencia de comandos: 18/01/2005 23:39:31 ******/
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[Mostrador]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[Mostrador]
GO

/****** Objeto: tabla [dbo].[Aeropuerto]    fecha de la secuencia de comandos: 18/01/2005 23:39:31 ******/
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[Aeropuerto]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[Aeropuerto]
GO

/****** Objeto: tabla [dbo].[Compania]    fecha de la secuencia de comandos: 18/01/2005 23:39:31 ******/
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[Compania]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[Compania]
GO

/****** Objeto: tabla [dbo].[Tarjeta]    fecha de la secuencia de comandos: 18/01/2005 23:39:31 ******/
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[Tarjeta]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[Tarjeta]
GO

/****** Objeto: tabla [dbo].[Aeropuerto]    fecha de la secuencia de comandos: 18/01/2005 23:39:31 ******/
CREATE TABLE [dbo].[Aeropuerto] (
	[CodigoAeropuerto] [varchar] (3) NOT NULL ,
	[Ciudad] [varchar] (50) NULL ,
	[Pais] [varchar] (50) NULL 
) ON [PRIMARY]
GO

/****** Objeto: tabla [dbo].[Compania]    fecha de la secuencia de comandos: 18/01/2005 23:39:31 ******/
CREATE TABLE [dbo].[Compania] (
	[Compania] [varchar] (50) NOT NULL ,
	[Nombre] [varchar] (50) NULL 
) ON [PRIMARY]
GO

/****** Objeto: tabla [dbo].[Tarjeta]    fecha de la secuencia de comandos: 18/01/2005 23:39:31 ******/
CREATE TABLE [dbo].[Tarjeta] (
	[Numero] [varchar] (50) NOT NULL ,
	[Nombre] [varchar] (50) NULL ,
	[MesAnoCaducidad] [varchar] (50) NULL 
) ON [PRIMARY]
GO

/****** Objeto: tabla [dbo].[Avion]    fecha de la secuencia de comandos: 18/01/2005 23:39:31 ******/
CREATE TABLE [dbo].[Avion] (
	[Avion] [varchar] (50) NOT NULL ,
	[Tipo] [varchar] (50) NULL ,
	[Capacidad] [int] NULL ,
	[Vuelo] [varchar] (50) NULL ,
	[Compania] [varchar] (50) NULL 
) ON [PRIMARY]
GO

/****** Objeto: tabla [dbo].[Mostrador]    fecha de la secuencia de comandos: 18/01/2005 23:39:31 ******/
CREATE TABLE [dbo].[Mostrador] (
	[Mostrador] [int] NOT NULL ,
	[Aeropuerto] [varchar] (3) NULL ,
	[Compania] [varchar] (50) NULL 
) ON [PRIMARY]
GO

/****** Objeto: tabla [dbo].[Vuelo]    fecha de la secuencia de comandos: 18/01/2005 23:39:31 ******/
CREATE TABLE [dbo].[Vuelo] (
	[Vuelo] [varchar] (50) NOT NULL ,
	[EsDeLlegada] [bit] NULL ,
	[Aeropuerto] [varchar] (3) NULL ,
	[Avion] [varchar] (50) NULL ,
	[FechaHoraSalida] [datetime] NULL ,
	[PuertaDeEmbarque] [varchar] (50) NULL 
) ON [PRIMARY]
GO

/****** Objeto: tabla [dbo].[Reserva]    fecha de la secuencia de comandos: 18/01/2005 23:39:31 ******/
CREATE TABLE [dbo].[Reserva] (
	[Localizador] [varchar] (50) NOT NULL ,
	[Clase] [varchar] (50) NULL ,
	[Vendida] [bit] NULL ,
	[Vuelo] [varchar] (50) NULL 
) ON [PRIMARY]
GO

/****** Objeto: tabla [dbo].[Pasajero]    fecha de la secuencia de comandos: 18/01/2005 23:39:31 ******/
CREATE TABLE [dbo].[Pasajero] (
	[Pasajero] [int] NOT NULL ,
	[Nombre] [varchar] (50) NULL ,
	[Apellidos] [varchar] (50) NULL ,
	[Localizador] [varchar] (50) NULL 
) ON [PRIMARY]
GO

/****** Objeto: tabla [dbo].[TarjetaDeEmbarque]    fecha de la secuencia de comandos: 18/01/2005 23:39:31 ******/
CREATE TABLE [dbo].[TarjetaDeEmbarque] (
	[Tarjeta] [varchar] (50) NOT NULL ,
	[Pasajero] [int] NULL ,
	[VueloV] [varchar] (50) NULL ,
	[Mostrador] [int] NULL ,
	[Asiento] [varchar] (50) NULL 
) ON [PRIMARY]
GO

/****** Objeto: tabla [dbo].[Asiento]    fecha de la secuencia de comandos: 18/01/2005 23:39:31 ******/
CREATE TABLE [dbo].[Asiento] (
	[Asiento] [varchar] (50) NOT NULL ,
	[Clase] [varchar] (50) NULL ,
	[Fila] [bigint] NULL ,
	[Columna] [varchar] (50) NULL ,
	[AvionAvion] [varchar] (50) NULL ,
	[TarjetaDeEmbarque] [varchar] (50) NULL 
) ON [PRIMARY]
GO

/****** Objeto: tabla [dbo].[Equipaje]    fecha de la secuencia de comandos: 18/01/2005 23:39:32 ******/
CREATE TABLE [dbo].[Equipaje] (
	[Equipaje] [varchar] (50) NOT NULL ,
	[Peso] [varchar] (1) NULL ,
	[TarjetaDeEmbarque] [varchar] (50) NULL 
) ON [PRIMARY]
GO

ALTER TABLE [dbo].[Aeropuerto] WITH NOCHECK ADD 
	CONSTRAINT [PK_Aeropuerto] PRIMARY KEY  CLUSTERED 
	(
		[CodigoAeropuerto]
	)  ON [PRIMARY] 
GO

ALTER TABLE [dbo].[Compania] WITH NOCHECK ADD 
	CONSTRAINT [PK_Compania] PRIMARY KEY  CLUSTERED 
	(
		[Compania]
	)  ON [PRIMARY] 
GO

ALTER TABLE [dbo].[Tarjeta] WITH NOCHECK ADD 
	CONSTRAINT [PK_Tarjeta] PRIMARY KEY  CLUSTERED 
	(
		[Numero]
	)  ON [PRIMARY] 
GO

ALTER TABLE [dbo].[Avion] WITH NOCHECK ADD 
	CONSTRAINT [PK_Avion] PRIMARY KEY  CLUSTERED 
	(
		[Avion]
	)  ON [PRIMARY] 
GO

ALTER TABLE [dbo].[Mostrador] WITH NOCHECK ADD 
	CONSTRAINT [PK_Mostrador] PRIMARY KEY  CLUSTERED 
	(
		[Mostrador]
	)  ON [PRIMARY] 
GO

ALTER TABLE [dbo].[Vuelo] WITH NOCHECK ADD 
	CONSTRAINT [PK_Vuelo] PRIMARY KEY  CLUSTERED 
	(
		[Vuelo]
	)  ON [PRIMARY] 
GO

ALTER TABLE [dbo].[Reserva] WITH NOCHECK ADD 
	CONSTRAINT [PK_Reserva] PRIMARY KEY  CLUSTERED 
	(
		[Localizador]
	)  ON [PRIMARY] 
GO

ALTER TABLE [dbo].[Pasajero] WITH NOCHECK ADD 
	CONSTRAINT [PK_Pasajero] PRIMARY KEY  CLUSTERED 
	(
		[Pasajero]
	)  ON [PRIMARY] 
GO

ALTER TABLE [dbo].[TarjetaDeEmbarque] WITH NOCHECK ADD 
	CONSTRAINT [PK_TarjetaDeEmbarque] PRIMARY KEY  CLUSTERED 
	(
		[Tarjeta]
	)  ON [PRIMARY] 
GO

ALTER TABLE [dbo].[Asiento] WITH NOCHECK ADD 
	CONSTRAINT [PK_Asiento] PRIMARY KEY  CLUSTERED 
	(
		[Asiento]
	)  ON [PRIMARY] 
GO

ALTER TABLE [dbo].[Equipaje] WITH NOCHECK ADD 
	CONSTRAINT [PK_Equipaje] PRIMARY KEY  CLUSTERED 
	(
		[Equipaje]
	)  ON [PRIMARY] 
GO

ALTER TABLE [dbo].[Avion] ADD 
	CONSTRAINT [FK_Avion_Compania] FOREIGN KEY 
	(
		[Compania]
	) REFERENCES [dbo].[Compania] (
		[Compania]
	)
GO

ALTER TABLE [dbo].[Mostrador] ADD 
	CONSTRAINT [FK_Mostrador_Aeropuerto] FOREIGN KEY 
	(
		[Aeropuerto]
	) REFERENCES [dbo].[Aeropuerto] (
		[CodigoAeropuerto]
	) ON DELETE CASCADE  ON UPDATE CASCADE ,
	CONSTRAINT [FK_Mostrador_Compania] FOREIGN KEY 
	(
		[Compania]
	) REFERENCES [dbo].[Compania] (
		[Compania]
	) ON DELETE CASCADE  ON UPDATE CASCADE 
GO

ALTER TABLE [dbo].[Vuelo] ADD 
	CONSTRAINT [FK_Vuelo_Aeropuerto] FOREIGN KEY 
	(
		[Aeropuerto]
	) REFERENCES [dbo].[Aeropuerto] (
		[CodigoAeropuerto]
	),
	CONSTRAINT [FK_Vuelo_Avion] FOREIGN KEY 
	(
		[Avion]
	) REFERENCES [dbo].[Avion] (
		[Avion]
	) ON DELETE CASCADE  ON UPDATE CASCADE 
GO

ALTER TABLE [dbo].[Reserva] ADD 
	CONSTRAINT [FK_Reserva_Vuelo] FOREIGN KEY 
	(
		[Vuelo]
	) REFERENCES [dbo].[Vuelo] (
		[Vuelo]
	) ON DELETE CASCADE  ON UPDATE CASCADE 
GO

ALTER TABLE [dbo].[Pasajero] ADD 
	CONSTRAINT [FK_Pasajero_Reserva] FOREIGN KEY 
	(
		[Localizador]
	) REFERENCES [dbo].[Reserva] (
		[Localizador]
	)
GO

ALTER TABLE [dbo].[TarjetaDeEmbarque] ADD 
	CONSTRAINT [FK_TarjetaDeEmbarque_Mostrador] FOREIGN KEY 
	(
		[Mostrador]
	) REFERENCES [dbo].[Mostrador] (
		[Mostrador]
	) ON DELETE CASCADE  ON UPDATE CASCADE ,
	CONSTRAINT [FK_TarjetaDeEmbarque_Pasajero] FOREIGN KEY 
	(
		[Pasajero]
	) REFERENCES [dbo].[Pasajero] (
		[Pasajero]
	) ON DELETE CASCADE  ON UPDATE CASCADE ,
	CONSTRAINT [FK_TarjetaDeEmbarque_Vuelo] FOREIGN KEY 
	(
		[VueloV]
	) REFERENCES [dbo].[Vuelo] (
		[Vuelo]
	) ON DELETE CASCADE  ON UPDATE CASCADE 
GO

ALTER TABLE [dbo].[Asiento] ADD 
	CONSTRAINT [FK_Asiento_Avion] FOREIGN KEY 
	(
		[AvionAvion]
	) REFERENCES [dbo].[Avion] (
		[Avion]
	) ON DELETE CASCADE  ON UPDATE CASCADE ,
	CONSTRAINT [FK_Asiento_TarjetaDeEmbarque] FOREIGN KEY 
	(
		[TarjetaDeEmbarque]
	) REFERENCES [dbo].[TarjetaDeEmbarque] (
		[Tarjeta]
	)
GO

ALTER TABLE [dbo].[Equipaje] ADD 
	CONSTRAINT [FK_Equipaje_TarjetaDeEmbarque] FOREIGN KEY 
	(
		[TarjetaDeEmbarque]
	) REFERENCES [dbo].[TarjetaDeEmbarque] (
		[Tarjeta]
	) ON DELETE CASCADE  ON UPDATE CASCADE 
GO

