bazka

/*==============================================================*/
/* DBMS name:      Microsoft SQL Server 2014                    */
/* Created on:     09.12.2017 19:38:06                          */
/*==============================================================*/


if exists (select 1
   from sys.sysreferences r join sys.sysobjects o on (o.id = r.constid and o.type = 'F')
   where r.fkeyid = object_id('Bilet') and o.name = 'FK_BILET_RODZAJU_RODZAJ')
alter table Bilet
   drop constraint FK_BILET_RODZAJU_RODZAJ
go

if exists (select 1
   from sys.sysreferences r join sys.sysobjects o on (o.id = r.constid and o.type = 'F')
   where r.fkeyid = object_id('"Jest na trasie"') and o.name = 'FK_JEST NA _JEST NA T_TRASA')
alter table "Jest na trasie"
   drop constraint "FK_JEST NA _JEST NA T_TRASA"
go

if exists (select 1
   from sys.sysreferences r join sys.sysobjects o on (o.id = r.constid and o.type = 'F')
   where r.fkeyid = object_id('"Jest na trasie"') and o.name = 'FK_JEST NA _JEST NA T_PRZYSTAN')
alter table "Jest na trasie"
   drop constraint "FK_JEST NA _JEST NA T_PRZYSTAN"
go

if exists (select 1
   from sys.sysreferences r join sys.sysobjects o on (o.id = r.constid and o.type = 'F')
   where r.fkeyid = object_id('"Jest przypisana do"') and o.name = 'FK_JEST PRZ_JEST PRZY_KURS')
alter table "Jest przypisana do"
   drop constraint "FK_JEST PRZ_JEST PRZY_KURS"
go

if exists (select 1
   from sys.sysreferences r join sys.sysobjects o on (o.id = r.constid and o.type = 'F')
   where r.fkeyid = object_id('"Jest przypisana do"') and o.name = 'FK_JEST PRZ_JEST PRZY_TRASA')
alter table "Jest przypisana do"
   drop constraint "FK_JEST PRZ_JEST PRZY_TRASA"
go

if exists (select 1
   from sys.sysreferences r join sys.sysobjects o on (o.id = r.constid and o.type = 'F')
   where r.fkeyid = object_id('Kierunek') and o.name = 'FK_KIERUNEK_KOŃCZY W_MIEJSCOW')
alter table Kierunek
   drop constraint "FK_KIERUNEK_KOŃCZY W_MIEJSCOW"
go

if exists (select 1
   from sys.sysreferences r join sys.sysobjects o on (o.id = r.constid and o.type = 'F')
   where r.fkeyid = object_id('Kierunek') and o.name = 'FK_KIERUNEK_STARTUJE _MIEJSCOW')
alter table Kierunek
   drop constraint "FK_KIERUNEK_STARTUJE _MIEJSCOW"
go

if exists (select 1
   from sys.sysreferences r join sys.sysobjects o on (o.id = r.constid and o.type = 'F')
   where r.fkeyid = object_id('Kurs') and o.name = 'FK_KURS_JEST KIER_KIEROWCA')
alter table Kurs
   drop constraint "FK_KURS_JEST KIER_KIEROWCA"
go

if exists (select 1
   from sys.sysreferences r join sys.sysobjects o on (o.id = r.constid and o.type = 'F')
   where r.fkeyid = object_id('Kurs') and o.name = 'FK_KURS_RELATIONS_KIERUNEK')
alter table Kurs
   drop constraint FK_KURS_RELATIONS_KIERUNEK
go

if exists (select 1
   from sys.sysreferences r join sys.sysobjects o on (o.id = r.constid and o.type = 'F')
   where r.fkeyid = object_id('Kurs') and o.name = 'FK_KURS_JEST UZYW_AUTOKAR')
alter table Kurs
   drop constraint "FK_KURS_JEST UZYW_AUTOKAR"
go

if exists (select 1
   from sys.sysreferences r join sys.sysobjects o on (o.id = r.constid and o.type = 'F')
   where r.fkeyid = object_id('Pasazer') and o.name = 'FK_PASAZER_BYL PASAR_KURS')
alter table Pasazer
   drop constraint "FK_PASAZER_BYL PASAR_KURS"
go

if exists (select 1
   from sys.sysreferences r join sys.sysobjects o on (o.id = r.constid and o.type = 'F')
   where r.fkeyid = object_id('Pasazer') and o.name = 'FK_PASAZER_KUPIONY P_BILET')
alter table Pasazer
   drop constraint "FK_PASAZER_KUPIONY P_BILET"
go

if exists (select 1
   from sys.sysreferences r join sys.sysobjects o on (o.id = r.constid and o.type = 'F')
   where r.fkeyid = object_id('Przystanek') and o.name = 'FK_PRZYSTAN_TO JEST_MIEJSCOW')
alter table Przystanek
   drop constraint "FK_PRZYSTAN_TO JEST_MIEJSCOW"
go

if exists (select 1
            from  sysobjects
           where  id = object_id('Autokar')
            and   type = 'U')
   drop table Autokar
go

if exists (select 1
            from  sysindexes
           where  id    = object_id('Bilet')
            and   name  = 'Rodzaju_FK'
            and   indid > 0
            and   indid < 255)
   drop index Bilet.Rodzaju_FK
go

if exists (select 1
            from  sysobjects
           where  id = object_id('Bilet')
            and   type = 'U')
   drop table Bilet
go

if exists (select 1
            from  sysindexes
           where  id    = object_id('"Jest na trasie"')
            and   name  = 'Jest na trasie2_FK'
            and   indid > 0
            and   indid < 255)
   drop index "Jest na trasie"."Jest na trasie2_FK"
go

if exists (select 1
            from  sysindexes
           where  id    = object_id('"Jest na trasie"')
            and   name  = 'Jest na trasie_FK'
            and   indid > 0
            and   indid < 255)
   drop index "Jest na trasie"."Jest na trasie_FK"
go

if exists (select 1
            from  sysobjects
           where  id = object_id('"Jest na trasie"')
            and   type = 'U')
   drop table "Jest na trasie"
go

if exists (select 1
            from  sysindexes
           where  id    = object_id('"Jest przypisana do"')
            and   name  = 'Jest przypisana do2_FK'
            and   indid > 0
            and   indid < 255)
   drop index "Jest przypisana do"."Jest przypisana do2_FK"
go

if exists (select 1
            from  sysindexes
           where  id    = object_id('"Jest przypisana do"')
            and   name  = 'Jest przypisana do_FK'
            and   indid > 0
            and   indid < 255)
   drop index "Jest przypisana do"."Jest przypisana do_FK"
go

if exists (select 1
            from  sysobjects
           where  id = object_id('"Jest przypisana do"')
            and   type = 'U')
   drop table "Jest przypisana do"
go

if exists (select 1
            from  sysobjects
           where  id = object_id('Kierowca')
            and   type = 'U')
   drop table Kierowca
go

if exists (select 1
            from  sysindexes
           where  id    = object_id('Kierunek')
            and   name  = 'Kończy w_FK'
            and   indid > 0
            and   indid < 255)
   drop index Kierunek."Kończy w_FK"
go

if exists (select 1
            from  sysindexes
           where  id    = object_id('Kierunek')
            and   name  = 'Startuje z_FK'
            and   indid > 0
            and   indid < 255)
   drop index Kierunek."Startuje z_FK"
go

if exists (select 1
            from  sysobjects
           where  id = object_id('Kierunek')
            and   type = 'U')
   drop table Kierunek
go

if exists (select 1
            from  sysindexes
           where  id    = object_id('Kurs')
            and   name  = 'Relationship_8_FK'
            and   indid > 0
            and   indid < 255)
   drop index Kurs.Relationship_8_FK
go

if exists (select 1
            from  sysindexes
           where  id    = object_id('Kurs')
            and   name  = 'jest uzywany na_FK'
            and   indid > 0
            and   indid < 255)
   drop index Kurs."jest uzywany na_FK"
go

if exists (select 1
            from  sysindexes
           where  id    = object_id('Kurs')
            and   name  = 'Jest kierowca na_FK'
            and   indid > 0
            and   indid < 255)
   drop index Kurs."Jest kierowca na_FK"
go

if exists (select 1
            from  sysobjects
           where  id = object_id('Kurs')
            and   type = 'U')
   drop table Kurs
go

if exists (select 1
            from  sysobjects
           where  id = object_id('Miejscowosc')
            and   type = 'U')
   drop table Miejscowosc
go

if exists (select 1
            from  sysindexes
           where  id    = object_id('Pasazer')
            and   name  = 'Byl pasarzerem kursu_FK'
            and   indid > 0
            and   indid < 255)
   drop index Pasazer."Byl pasarzerem kursu_FK"
go

if exists (select 1
            from  sysindexes
           where  id    = object_id('Pasazer')
            and   name  = 'Kupiony przez_FK'
            and   indid > 0
            and   indid < 255)
   drop index Pasazer."Kupiony przez_FK"
go

if exists (select 1
            from  sysobjects
           where  id = object_id('Pasazer')
            and   type = 'U')
   drop table Pasazer
go

if exists (select 1
            from  sysindexes
           where  id    = object_id('Przystanek')
            and   name  = 'To jest_FK'
            and   indid > 0
            and   indid < 255)
   drop index Przystanek."To jest_FK"
go

if exists (select 1
            from  sysobjects
           where  id = object_id('Przystanek')
            and   type = 'U')
   drop table Przystanek
go

if exists (select 1
            from  sysobjects
           where  id = object_id('Rodzaj')
            and   type = 'U')
   drop table Rodzaj
go

if exists (select 1
            from  sysobjects
           where  id = object_id('Trasa')
            and   type = 'U')
   drop table Trasa
go

/*==============================================================*/
/* Table: Autokar                                               */
/*==============================================================*/
create table Autokar (
   Autobus_ID           numeric              not null,
   Model                char(15)             null,
   Marka                char(15)             null,
   Rejestracja          char(10)             null,
   constraint PK_AUTOKAR primary key (Autobus_ID)
)
go

/*==============================================================*/
/* Table: Bilet                                                 */
/*==============================================================*/
create table Bilet (
   Bilet_ID             numeric              not null,
   NazwaBiletu          char(15)             not null,
   constraint PK_BILET primary key (Bilet_ID)
)
go

/*==============================================================*/
/* Index: Rodzaju_FK                                            */
/*==============================================================*/




create nonclustered index Rodzaju_FK on Bilet (NazwaBiletu ASC)
go

/*==============================================================*/
/* Table: "Jest na trasie"                                      */
/*==============================================================*/
create table "Jest na trasie" (
   Trasa_ID             numeric              not null,
   Przystanek_ID        numeric              not null,
   constraint "PK_JEST NA TRASIE" primary key (Trasa_ID, Przystanek_ID)
)
go

/*==============================================================*/
/* Index: "Jest na trasie_FK"                                   */
/*==============================================================*/




create nonclustered index "Jest na trasie_FK" on "Jest na trasie" (Trasa_ID ASC)
go

/*==============================================================*/
/* Index: "Jest na trasie2_FK"                                  */
/*==============================================================*/




create nonclustered index "Jest na trasie2_FK" on "Jest na trasie" (Przystanek_ID ASC)
go

/*==============================================================*/
/* Table: "Jest przypisana do"                                  */
/*==============================================================*/
create table "Jest przypisana do" (
   Kurs_ID              numeric              not null,
   Trasa_ID             numeric              not null,
   constraint "PK_JEST PRZYPISANA DO" primary key (Kurs_ID, Trasa_ID)
)
go

/*==============================================================*/
/* Index: "Jest przypisana do_FK"                               */
/*==============================================================*/




create nonclustered index "Jest przypisana do_FK" on "Jest przypisana do" (Kurs_ID ASC)
go

/*==============================================================*/
/* Index: "Jest przypisana do2_FK"                              */
/*==============================================================*/




create nonclustered index "Jest przypisana do2_FK" on "Jest przypisana do" (Trasa_ID ASC)
go

/*==============================================================*/
/* Table: Kierowca                                              */
/*==============================================================*/
create table Kierowca (
   Kierowca_ID          numeric              not null,
   Imie                 char(15)             null,
   Nazwisko             char(15)             null,
   DataZatrudnienia     datetime             null,
   constraint PK_KIEROWCA primary key (Kierowca_ID)
)
go

/*==============================================================*/
/* Table: Kierunek                                              */
/*==============================================================*/
create table Kierunek (
   Kierunek_ID          numeric              not null,
   NazwaMiasta          char(20)             not null,
   Mie_NazwaMiasta      char(20)             not null,
   constraint PK_KIERUNEK primary key (Kierunek_ID)
)
go

/*==============================================================*/
/* Index: "Startuje z_FK"                                       */
/*==============================================================*/




create nonclustered index "Startuje z_FK" on Kierunek (Mie_NazwaMiasta ASC)
go

/*==============================================================*/
/* Index: "Kończy w_FK"                                         */
/*==============================================================*/




create nonclustered index "Kończy w_FK" on Kierunek (NazwaMiasta ASC)
go

/*==============================================================*/
/* Table: Kurs                                                  */
/*==============================================================*/
create table Kurs (
   Kurs_ID              numeric              not null,
   Kierowca_ID          numeric              not null,
   Kierunek_ID          numeric              not null,
   Autobus_ID           numeric              not null,
   Data                 datetime             null,
   Odjazd               datetime             null,
   Przyjazd             datetime             null,
   constraint PK_KURS primary key (Kurs_ID)
)
go

/*==============================================================*/
/* Index: "Jest kierowca na_FK"                                 */
/*==============================================================*/




create nonclustered index "Jest kierowca na_FK" on Kurs (Kierowca_ID ASC)
go

/*==============================================================*/
/* Index: "jest uzywany na_FK"                                  */
/*==============================================================*/




create nonclustered index "jest uzywany na_FK" on Kurs (Autobus_ID ASC)
go

/*==============================================================*/
/* Index: Relationship_8_FK                                     */
/*==============================================================*/




create nonclustered index Relationship_8_FK on Kurs (Kierunek_ID ASC)
go

/*==============================================================*/
/* Table: Miejscowosc                                           */
/*==============================================================*/
create table Miejscowosc (
   NazwaMiasta          char(20)             not null,
   constraint PK_MIEJSCOWOSC primary key (NazwaMiasta)
)
go

/*==============================================================*/
/* Table: Pasazer                                               */
/*==============================================================*/
create table Pasazer (
   Pasazer_ID           numeric              not null,
   Kurs_ID              numeric              not null,
   Bilet_ID             numeric              not null,
   constraint PK_PASAZER primary key (Pasazer_ID)
)
go

/*==============================================================*/
/* Index: "Kupiony przez_FK"                                    */
/*==============================================================*/




create nonclustered index "Kupiony przez_FK" on Pasazer (Bilet_ID ASC)
go

/*==============================================================*/
/* Index: "Byl pasarzerem kursu_FK"                             */
/*==============================================================*/




create nonclustered index "Byl pasarzerem kursu_FK" on Pasazer (Kurs_ID ASC)
go

/*==============================================================*/
/* Table: Przystanek                                            */
/*==============================================================*/
create table Przystanek (
   Przystanek_ID        numeric              not null,
   NazwaMiasta          char(20)             not null,
   constraint PK_PRZYSTANEK primary key (Przystanek_ID)
)
go

/*==============================================================*/
/* Index: "To jest_FK"                                          */
/*==============================================================*/




create nonclustered index "To jest_FK" on Przystanek (NazwaMiasta ASC)
go

/*==============================================================*/
/* Table: Rodzaj                                                */
/*==============================================================*/
create table Rodzaj (
   NazwaBiletu          char(15)             not null,
   constraint PK_RODZAJ primary key (NazwaBiletu)
)
go

/*==============================================================*/
/* Table: Trasa                                                 */
/*==============================================================*/
create table Trasa (
   Trasa_ID             numeric              not null,
   constraint PK_TRASA primary key (Trasa_ID)
)
go

alter table Bilet
   add constraint FK_BILET_RODZAJU_RODZAJ foreign key (NazwaBiletu)
      references Rodzaj (NazwaBiletu)
go

alter table "Jest na trasie"
   add constraint "FK_JEST NA _JEST NA T_TRASA" foreign key (Trasa_ID)
      references Trasa (Trasa_ID)
go

alter table "Jest na trasie"
   add constraint "FK_JEST NA _JEST NA T_PRZYSTAN" foreign key (Przystanek_ID)
      references Przystanek (Przystanek_ID)
go

alter table "Jest przypisana do"
   add constraint "FK_JEST PRZ_JEST PRZY_KURS" foreign key (Kurs_ID)
      references Kurs (Kurs_ID)
go

alter table "Jest przypisana do"
   add constraint "FK_JEST PRZ_JEST PRZY_TRASA" foreign key (Trasa_ID)
      references Trasa (Trasa_ID)
go

alter table Kierunek
   add constraint "FK_KIERUNEK_KOŃCZY W_MIEJSCOW" foreign key (NazwaMiasta)
      references Miejscowosc (NazwaMiasta)
go

alter table Kierunek
   add constraint "FK_KIERUNEK_STARTUJE _MIEJSCOW" foreign key (Mie_NazwaMiasta)
      references Miejscowosc (NazwaMiasta)
go

alter table Kurs
   add constraint "FK_KURS_JEST KIER_KIEROWCA" foreign key (Kierowca_ID)
      references Kierowca (Kierowca_ID)
go

alter table Kurs
   add constraint FK_KURS_RELATIONS_KIERUNEK foreign key (Kierunek_ID)
      references Kierunek (Kierunek_ID)
go

alter table Kurs
   add constraint "FK_KURS_JEST UZYW_AUTOKAR" foreign key (Autobus_ID)
      references Autokar (Autobus_ID)
go

alter table Pasazer
   add constraint "FK_PASAZER_BYL PASAR_KURS" foreign key (Kurs_ID)
      references Kurs (Kurs_ID)
go

alter table Pasazer
   add constraint "FK_PASAZER_KUPIONY P_BILET" foreign key (Bilet_ID)
      references Bilet (Bilet_ID)
go

alter table Przystanek
   add constraint "FK_PRZYSTAN_TO JEST_MIEJSCOW" foreign key (NazwaMiasta)
      references Miejscowosc (NazwaMiasta)
go