
-- --------------------------------------------------
-- Entity Designer DDL Script for SQL Server 2005, 2008, and Azure
-- --------------------------------------------------
-- Date Created: 11/28/2011 12:21:33
-- Generated from EDMX file: h:\TFS\ITVExperten\WWWingsMini\2011HeiseDeveloperTutorial\HeiseDeveloperTutorialTeil1\WWWings_GO\WWWingsModell.edmx
-- --------------------------------------------------

SET QUOTED_IDENTIFIER OFF;
GO
USE [WWWings];
GO
IF SCHEMA_ID(N'dbo') IS NULL EXECUTE(N'CREATE SCHEMA [dbo]');
GO

-- --------------------------------------------------
-- Dropping existing FOREIGN KEY constraints
-- --------------------------------------------------

IF OBJECT_ID(N'[dbo].[FK_PassagierFlug_Passagier]', 'F') IS NOT NULL
    ALTER TABLE [dbo].[PassagierFlug] DROP CONSTRAINT [FK_PassagierFlug_Passagier];
GO
IF OBJECT_ID(N'[dbo].[FK_PassagierFlug_Flug]', 'F') IS NOT NULL
    ALTER TABLE [dbo].[PassagierFlug] DROP CONSTRAINT [FK_PassagierFlug_Flug];
GO
IF OBJECT_ID(N'[dbo].[FK_PilotFlug]', 'F') IS NOT NULL
    ALTER TABLE [dbo].[FlugSet] DROP CONSTRAINT [FK_PilotFlug];
GO
IF OBJECT_ID(N'[dbo].[FK_Passagier_inherits_Person]', 'F') IS NOT NULL
    ALTER TABLE [dbo].[PersonSet_Passagier] DROP CONSTRAINT [FK_Passagier_inherits_Person];
GO
IF OBJECT_ID(N'[dbo].[FK_Pilot_inherits_Person]', 'F') IS NOT NULL
    ALTER TABLE [dbo].[PersonSet_Pilot] DROP CONSTRAINT [FK_Pilot_inherits_Person];
GO

-- --------------------------------------------------
-- Dropping existing tables
-- --------------------------------------------------

IF OBJECT_ID(N'[dbo].[PersonSet]', 'U') IS NOT NULL
    DROP TABLE [dbo].[PersonSet];
GO
IF OBJECT_ID(N'[dbo].[FlugSet]', 'U') IS NOT NULL
    DROP TABLE [dbo].[FlugSet];
GO
IF OBJECT_ID(N'[dbo].[PersonSet_Passagier]', 'U') IS NOT NULL
    DROP TABLE [dbo].[PersonSet_Passagier];
GO
IF OBJECT_ID(N'[dbo].[PersonSet_Pilot]', 'U') IS NOT NULL
    DROP TABLE [dbo].[PersonSet_Pilot];
GO
IF OBJECT_ID(N'[dbo].[PassagierFlug]', 'U') IS NOT NULL
    DROP TABLE [dbo].[PassagierFlug];
GO

-- --------------------------------------------------
-- Creating all tables
-- --------------------------------------------------

-- Creating table 'PersonSet'
CREATE TABLE [dbo].[PersonSet] (
    [ID] int IDENTITY(1,1) NOT NULL,
    [Vorname] nvarchar(max)  NOT NULL,
    [Name] nvarchar(max)  NOT NULL,
    [Geburtsdatum] datetime  NULL
);
GO

-- Creating table 'FlugSet'
CREATE TABLE [dbo].[FlugSet] (
    [ID] int  NOT NULL,
    [Abflugort] nvarchar(max)  NOT NULL,
    [Zielort] nvarchar(max)  NOT NULL,
    [Datum] datetime  NOT NULL,
    [Plaetze] smallint  NOT NULL,
    [FreiePlaetze] smallint  NOT NULL,
    [PilotId] int  NOT NULL
);
GO

-- Creating table 'PersonSet_Passagier'
CREATE TABLE [dbo].[PersonSet_Passagier] (
    [PassagierStatus] nvarchar(max)  NOT NULL,
    [ID] int  NOT NULL
);
GO

-- Creating table 'PersonSet_Pilot'
CREATE TABLE [dbo].[PersonSet_Pilot] (
    [Eingestellungsdatum] datetime  NOT NULL,
    [ID] int  NOT NULL
);
GO

-- Creating table 'PassagierFlug'
CREATE TABLE [dbo].[PassagierFlug] (
    [Passagier_ID] int  NOT NULL,
    [Flug_ID] int  NOT NULL
);
GO

-- --------------------------------------------------
-- Creating all PRIMARY KEY constraints
-- --------------------------------------------------

-- Creating primary key on [ID] in table 'PersonSet'
ALTER TABLE [dbo].[PersonSet]
ADD CONSTRAINT [PK_PersonSet]
    PRIMARY KEY CLUSTERED ([ID] ASC);
GO

-- Creating primary key on [ID] in table 'FlugSet'
ALTER TABLE [dbo].[FlugSet]
ADD CONSTRAINT [PK_FlugSet]
    PRIMARY KEY CLUSTERED ([ID] ASC);
GO

-- Creating primary key on [ID] in table 'PersonSet_Passagier'
ALTER TABLE [dbo].[PersonSet_Passagier]
ADD CONSTRAINT [PK_PersonSet_Passagier]
    PRIMARY KEY CLUSTERED ([ID] ASC);
GO

-- Creating primary key on [ID] in table 'PersonSet_Pilot'
ALTER TABLE [dbo].[PersonSet_Pilot]
ADD CONSTRAINT [PK_PersonSet_Pilot]
    PRIMARY KEY CLUSTERED ([ID] ASC);
GO

-- Creating primary key on [Passagier_ID], [Flug_ID] in table 'PassagierFlug'
ALTER TABLE [dbo].[PassagierFlug]
ADD CONSTRAINT [PK_PassagierFlug]
    PRIMARY KEY NONCLUSTERED ([Passagier_ID], [Flug_ID] ASC);
GO

-- --------------------------------------------------
-- Creating all FOREIGN KEY constraints
-- --------------------------------------------------

-- Creating foreign key on [Passagier_ID] in table 'PassagierFlug'
ALTER TABLE [dbo].[PassagierFlug]
ADD CONSTRAINT [FK_PassagierFlug_Passagier]
    FOREIGN KEY ([Passagier_ID])
    REFERENCES [dbo].[PersonSet_Passagier]
        ([ID])
    ON DELETE NO ACTION ON UPDATE NO ACTION;
GO

-- Creating foreign key on [Flug_ID] in table 'PassagierFlug'
ALTER TABLE [dbo].[PassagierFlug]
ADD CONSTRAINT [FK_PassagierFlug_Flug]
    FOREIGN KEY ([Flug_ID])
    REFERENCES [dbo].[FlugSet]
        ([ID])
    ON DELETE NO ACTION ON UPDATE NO ACTION;

-- Creating non-clustered index for FOREIGN KEY 'FK_PassagierFlug_Flug'
CREATE INDEX [IX_FK_PassagierFlug_Flug]
ON [dbo].[PassagierFlug]
    ([Flug_ID]);
GO

-- Creating foreign key on [PilotId] in table 'FlugSet'
ALTER TABLE [dbo].[FlugSet]
ADD CONSTRAINT [FK_PilotFlug]
    FOREIGN KEY ([PilotId])
    REFERENCES [dbo].[PersonSet_Pilot]
        ([ID])
    ON DELETE NO ACTION ON UPDATE NO ACTION;

-- Creating non-clustered index for FOREIGN KEY 'FK_PilotFlug'
CREATE INDEX [IX_FK_PilotFlug]
ON [dbo].[FlugSet]
    ([PilotId]);
GO

-- Creating foreign key on [ID] in table 'PersonSet_Passagier'
ALTER TABLE [dbo].[PersonSet_Passagier]
ADD CONSTRAINT [FK_Passagier_inherits_Person]
    FOREIGN KEY ([ID])
    REFERENCES [dbo].[PersonSet]
        ([ID])
    ON DELETE NO ACTION ON UPDATE NO ACTION;
GO

-- Creating foreign key on [ID] in table 'PersonSet_Pilot'
ALTER TABLE [dbo].[PersonSet_Pilot]
ADD CONSTRAINT [FK_Pilot_inherits_Person]
    FOREIGN KEY ([ID])
    REFERENCES [dbo].[PersonSet]
        ([ID])
    ON DELETE NO ACTION ON UPDATE NO ACTION;
GO

-- --------------------------------------------------
-- Script has ended
-- --------------------------------------------------