create table [dbo].[Airport] (
    [Id] [smallint] not null identity,
    [Name] [nvarchar](30) null,
    [Ort] [nvarchar](40) null,
    [Land] [nvarchar](40) null,
    primary key ([Id])
);
create table [dbo].[Fluege] (
    [Abflugdatum] [datetime] not null,
    [FlugNr] [int] not null identity,
    [Abflugort] [nvarchar](max) null,
    [Zielort] [nvarchar](50) null,
    [NichtRaucherFlug] [bit] not null,
    [Plaetze] [smallint] not null,
    [FreiePlaetze] [smallint] not null,
    [Ankunft] [datetime] null,
    [Bestreikt] [bit] not null,
    [SkyMarshalOnBoard] [bit] not null,
    primary key ([FlugNr])
);
create table [dbo].[FlugPassagier] (
    [Flug_FlugNr] [int] not null,
    [Passagier_PersonId] [int] not null,
    primary key ([Flug_FlugNr], [Passagier_PersonId])
);
create table [dbo].[Person] (
    [PersonId] [int] not null identity,
    [Name] [nvarchar](max) null,
    [Vorname] [nvarchar](max) null,
    [Land] [nvarchar](max) null,
    [Strasse] [nvarchar](max) null,
    [Geburtstag] [datetime] null,
    [Foto] [varbinary](max) null,
    [EMail] [nvarchar](max) null,
    [Stadt] [nvarchar](max) null,
    [Memo] [nvarchar](max) null,
    [MitarbeiterNr] [int] null,
    [Einstellungsdatum] [datetime] null,
    [FlugscheinSeit] [datetime] null,
    [Flugscheintyp] [nvarchar](max) null,
    [Flugstunden] [int] null,
    [Flugschule] [nvarchar](max) null,
    [KundeSeit] [datetime] null,
    [KundenStatus] [nvarchar](max) null,
    [Discriminator] [nvarchar](128) not null,
    primary key ([PersonId])
);
create table [dbo].[PilotFlug] (
    [Pilot_PersonId] [int] not null,
    [Flug_FlugNr] [int] not null,
    primary key ([Pilot_PersonId], [Flug_FlugNr])
);
alter table [dbo].[FlugPassagier] add constraint [Flug_Passagiere_Source] foreign key ([Flug_FlugNr]) references [dbo].[Fluege]([FlugNr]) on delete cascade;
alter table [dbo].[FlugPassagier] add constraint [Flug_Passagiere_Target] foreign key ([Passagier_PersonId]) references [dbo].[Person]([PersonId]) on delete cascade;
alter table [dbo].[PilotFlug] add constraint [Pilot_Flug_Source] foreign key ([Pilot_PersonId]) references [dbo].[Person]([PersonId]) on delete cascade;
alter table [dbo].[PilotFlug] add constraint [Pilot_Flug_Target] foreign key ([Flug_FlugNr]) references [dbo].[Fluege]([FlugNr]) on delete cascade;
