gistfile1.sql

-- --------------------------------------------------
-- Entity Designer DDL Script for SQL Server 2005, 2008, 2012 and Azure
-- --------------------------------------------------
-- Date Created: 04/29/2015 23:15:36
-- Generated from EDMX file: C:\RPPP\RPPP\RPPP-14\MI\MI\Model\Model.edmx
-- --------------------------------------------------

SET QUOTED_IDENTIFIER OFF;
GO
USE [RPPP14];
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_BerthHistory_Berths]', 'F') IS NOT NULL
    ALTER TABLE [dbo].[BerthHistory] DROP CONSTRAINT [FK_BerthHistory_Berths];
GO
IF OBJECT_ID(N'[dbo].[FK_BerthHistory_Boats]', 'F') IS NOT NULL
    ALTER TABLE [dbo].[BerthHistory] DROP CONSTRAINT [FK_BerthHistory_Boats];
GO
IF OBJECT_ID(N'[dbo].[FK_Berths_BerthTypes]', 'F') IS NOT NULL
    ALTER TABLE [dbo].[Berths] DROP CONSTRAINT [FK_Berths_BerthTypes];
GO
IF OBJECT_ID(N'[dbo].[FK_Berths_Marinas]', 'F') IS NOT NULL
    ALTER TABLE [dbo].[Berths] DROP CONSTRAINT [FK_Berths_Marinas];
GO
IF OBJECT_ID(N'[dbo].[FK_BerthServiceTypes_BerthServiceDurationTypes]', 'F') IS NOT NULL
    ALTER TABLE [dbo].[BerthServiceTypes] DROP CONSTRAINT [FK_BerthServiceTypes_BerthServiceDurationTypes];
GO
IF OBJECT_ID(N'[dbo].[FK_BerthServiceTypes_BerthTypes]', 'F') IS NOT NULL
    ALTER TABLE [dbo].[BerthServiceTypes] DROP CONSTRAINT [FK_BerthServiceTypes_BerthTypes];
GO
IF OBJECT_ID(N'[dbo].[FK_BillItems_Bills]', 'F') IS NOT NULL
    ALTER TABLE [dbo].[BillItems] DROP CONSTRAINT [FK_BillItems_Bills];
GO
IF OBJECT_ID(N'[dbo].[FK_BillItems_Service]', 'F') IS NOT NULL
    ALTER TABLE [dbo].[BillItems] DROP CONSTRAINT [FK_BillItems_Service];
GO
IF OBJECT_ID(N'[dbo].[FK_Bills_Contract]', 'F') IS NOT NULL
    ALTER TABLE [dbo].[Bills] DROP CONSTRAINT [FK_Bills_Contract];
GO
IF OBJECT_ID(N'[dbo].[FK_BoatEmployees_BoatEmployeeTypes]', 'F') IS NOT NULL
    ALTER TABLE [dbo].[BoatEmployees] DROP CONSTRAINT [FK_BoatEmployees_BoatEmployeeTypes];
GO
IF OBJECT_ID(N'[dbo].[FK_BoatEmployees_Boats]', 'F') IS NOT NULL
    ALTER TABLE [dbo].[BoatEmployees] DROP CONSTRAINT [FK_BoatEmployees_Boats];
GO
IF OBJECT_ID(N'[dbo].[FK_BoatEmployees_Persons]', 'F') IS NOT NULL
    ALTER TABLE [dbo].[BoatEmployees] DROP CONSTRAINT [FK_BoatEmployees_Persons];
GO
IF OBJECT_ID(N'[dbo].[FK_Boats_BoatTypes]', 'F') IS NOT NULL
    ALTER TABLE [dbo].[Boats] DROP CONSTRAINT [FK_Boats_BoatTypes];
GO
IF OBJECT_ID(N'[dbo].[FK_Boats_Countries]', 'F') IS NOT NULL
    ALTER TABLE [dbo].[Boats] DROP CONSTRAINT [FK_Boats_Countries];
GO
IF OBJECT_ID(N'[dbo].[FK_Boats_Owner]', 'F') IS NOT NULL
    ALTER TABLE [dbo].[Boats] DROP CONSTRAINT [FK_Boats_Owner];
GO
IF OBJECT_ID(N'[dbo].[FK_Cities_Countries]', 'F') IS NOT NULL
    ALTER TABLE [dbo].[Cities] DROP CONSTRAINT [FK_Cities_Countries];
GO
IF OBJECT_ID(N'[dbo].[FK_Contracts_Boats]', 'F') IS NOT NULL
    ALTER TABLE [dbo].[Contracts] DROP CONSTRAINT [FK_Contracts_Boats];
GO
IF OBJECT_ID(N'[dbo].[FK_Employees_EmployeeTypes]', 'F') IS NOT NULL
    ALTER TABLE [dbo].[Employees] DROP CONSTRAINT [FK_Employees_EmployeeTypes];
GO
IF OBJECT_ID(N'[dbo].[FK_Employees_Marinas]', 'F') IS NOT NULL
    ALTER TABLE [dbo].[Employees] DROP CONSTRAINT [FK_Employees_Marinas];
GO
IF OBJECT_ID(N'[dbo].[FK_Employees_Persons]', 'F') IS NOT NULL
    ALTER TABLE [dbo].[Employees] DROP CONSTRAINT [FK_Employees_Persons];
GO
IF OBJECT_ID(N'[dbo].[FK_EmployeeWorkTimes_Employees]', 'F') IS NOT NULL
    ALTER TABLE [dbo].[EmployeeWorkTimes] DROP CONSTRAINT [FK_EmployeeWorkTimes_Employees];
GO
IF OBJECT_ID(N'[dbo].[FK_Marinas_Cities]', 'F') IS NOT NULL
    ALTER TABLE [dbo].[Marinas] DROP CONSTRAINT [FK_Marinas_Cities];
GO
IF OBJECT_ID(N'[dbo].[FK_Owner_Countries]', 'F') IS NOT NULL
    ALTER TABLE [dbo].[Owners] DROP CONSTRAINT [FK_Owner_Countries];
GO
IF OBJECT_ID(N'[dbo].[FK_Owners_Persons]', 'F') IS NOT NULL
    ALTER TABLE [dbo].[Owners] DROP CONSTRAINT [FK_Owners_Persons];
GO
IF OBJECT_ID(N'[dbo].[FK_Persons_Cities]', 'F') IS NOT NULL
    ALTER TABLE [dbo].[Persons] DROP CONSTRAINT [FK_Persons_Cities];
GO
IF OBJECT_ID(N'[dbo].[FK_Persons_PersonType]', 'F') IS NOT NULL
    ALTER TABLE [dbo].[Persons] DROP CONSTRAINT [FK_Persons_PersonType];
GO
IF OBJECT_ID(N'[dbo].[FK_Reservations_BerthTypes]', 'F') IS NOT NULL
    ALTER TABLE [dbo].[Reservations] DROP CONSTRAINT [FK_Reservations_BerthTypes];
GO
IF OBJECT_ID(N'[dbo].[FK_Reservations_Boats]', 'F') IS NOT NULL
    ALTER TABLE [dbo].[Reservations] DROP CONSTRAINT [FK_Reservations_Boats];
GO
IF OBJECT_ID(N'[dbo].[FK_Reservations_Marinas]', 'F') IS NOT NULL
    ALTER TABLE [dbo].[Reservations] DROP CONSTRAINT [FK_Reservations_Marinas];
GO
IF OBJECT_ID(N'[dbo].[FK_Service_Marinas]', 'F') IS NOT NULL
    ALTER TABLE [dbo].[Services] DROP CONSTRAINT [FK_Service_Marinas];
GO
IF OBJECT_ID(N'[dbo].[FK_Service_Service]', 'F') IS NOT NULL
    ALTER TABLE [dbo].[Services] DROP CONSTRAINT [FK_Service_Service];
GO
IF OBJECT_ID(N'[dbo].[FK_Service_ServiceTypes]', 'F') IS NOT NULL
    ALTER TABLE [dbo].[Services] DROP CONSTRAINT [FK_Service_ServiceTypes];
GO

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

IF OBJECT_ID(N'[dbo].[BerthHistory]', 'U') IS NOT NULL
    DROP TABLE [dbo].[BerthHistory];
GO
IF OBJECT_ID(N'[dbo].[Berths]', 'U') IS NOT NULL
    DROP TABLE [dbo].[Berths];
GO
IF OBJECT_ID(N'[dbo].[BerthServiceDurationTypes]', 'U') IS NOT NULL
    DROP TABLE [dbo].[BerthServiceDurationTypes];
GO
IF OBJECT_ID(N'[dbo].[BerthServiceTypes]', 'U') IS NOT NULL
    DROP TABLE [dbo].[BerthServiceTypes];
GO
IF OBJECT_ID(N'[dbo].[BerthTypes]', 'U') IS NOT NULL
    DROP TABLE [dbo].[BerthTypes];
GO
IF OBJECT_ID(N'[dbo].[BillItems]', 'U') IS NOT NULL
    DROP TABLE [dbo].[BillItems];
GO
IF OBJECT_ID(N'[dbo].[Bills]', 'U') IS NOT NULL
    DROP TABLE [dbo].[Bills];
GO
IF OBJECT_ID(N'[dbo].[BoatEmployees]', 'U') IS NOT NULL
    DROP TABLE [dbo].[BoatEmployees];
GO
IF OBJECT_ID(N'[dbo].[BoatEmployeeTypes]', 'U') IS NOT NULL
    DROP TABLE [dbo].[BoatEmployeeTypes];
GO
IF OBJECT_ID(N'[dbo].[Boats]', 'U') IS NOT NULL
    DROP TABLE [dbo].[Boats];
GO
IF OBJECT_ID(N'[dbo].[BoatTypes]', 'U') IS NOT NULL
    DROP TABLE [dbo].[BoatTypes];
GO
IF OBJECT_ID(N'[dbo].[Cities]', 'U') IS NOT NULL
    DROP TABLE [dbo].[Cities];
GO
IF OBJECT_ID(N'[dbo].[Contracts]', 'U') IS NOT NULL
    DROP TABLE [dbo].[Contracts];
GO
IF OBJECT_ID(N'[dbo].[Countries]', 'U') IS NOT NULL
    DROP TABLE [dbo].[Countries];
GO
IF OBJECT_ID(N'[dbo].[Employees]', 'U') IS NOT NULL
    DROP TABLE [dbo].[Employees];
GO
IF OBJECT_ID(N'[dbo].[EmployeeTypes]', 'U') IS NOT NULL
    DROP TABLE [dbo].[EmployeeTypes];
GO
IF OBJECT_ID(N'[dbo].[EmployeeWorkTimes]', 'U') IS NOT NULL
    DROP TABLE [dbo].[EmployeeWorkTimes];
GO
IF OBJECT_ID(N'[dbo].[Marinas]', 'U') IS NOT NULL
    DROP TABLE [dbo].[Marinas];
GO
IF OBJECT_ID(N'[dbo].[Owners]', 'U') IS NOT NULL
    DROP TABLE [dbo].[Owners];
GO
IF OBJECT_ID(N'[dbo].[Persons]', 'U') IS NOT NULL
    DROP TABLE [dbo].[Persons];
GO
IF OBJECT_ID(N'[dbo].[PersonTypes]', 'U') IS NOT NULL
    DROP TABLE [dbo].[PersonTypes];
GO
IF OBJECT_ID(N'[dbo].[Reservations]', 'U') IS NOT NULL
    DROP TABLE [dbo].[Reservations];
GO
IF OBJECT_ID(N'[dbo].[Services]', 'U') IS NOT NULL
    DROP TABLE [dbo].[Services];
GO
IF OBJECT_ID(N'[dbo].[ServiceTypes]', 'U') IS NOT NULL
    DROP TABLE [dbo].[ServiceTypes];
GO

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

-- Creating table 'BerthHistories'
CREATE TABLE [dbo].[BerthHistories] (
    [Id] int IDENTITY(1,1) NOT NULL,
    [BerthId] int  NOT NULL,
    [BoatId] int  NULL,
    [DateFrom] datetime  NOT NULL,
    [DateTo] datetime  NULL
);
GO

-- Creating table 'Berths'
CREATE TABLE [dbo].[Berths] (
    [Id] int IDENTITY(1,1) NOT NULL,
    [MarinaId] int  NOT NULL,
    [BerthType] int  NOT NULL,
    [Location] int  NOT NULL,
    [Length] decimal(18,2)  NOT NULL
);
GO

-- Creating table 'BerthServiceDurationTypes'
CREATE TABLE [dbo].[BerthServiceDurationTypes] (
    [Id] int IDENTITY(1,1) NOT NULL,
    [ServiceDurationName] nvarchar(50)  NOT NULL
);
GO

-- Creating table 'BerthServiceTypes'
CREATE TABLE [dbo].[BerthServiceTypes] (
    [Id] int IDENTITY(1,1) NOT NULL,
    [BerthServiceDurationTypeId] int  NOT NULL,
    [BerthTypeId] int  NOT NULL
);
GO

-- Creating table 'BerthTypes'
CREATE TABLE [dbo].[BerthTypes] (
    [Id] int IDENTITY(1,1) NOT NULL,
    [BerthName] nvarchar(50)  NOT NULL,
    [BerthDescription] nvarchar(100)  NOT NULL
);
GO

-- Creating table 'BillItems'
CREATE TABLE [dbo].[BillItems] (
    [ServiceId] int  NOT NULL,
    [BillId] int  NOT NULL,
    [Quantity] int  NOT NULL,
    [Id] int  NOT NULL,
    [Discount] decimal(18,2)  NULL
);
GO

-- Creating table 'Bills'
CREATE TABLE [dbo].[Bills] (
    [Id] int IDENTITY(1,1) NOT NULL,
    [ContractId] int  NOT NULL,
    [DateIssued] datetime  NOT NULL,
    [PaymentDeadline] datetime  NULL,
    [Discount] decimal(5,2)  NULL,
    [Paid] bit  NOT NULL
);
GO

-- Creating table 'Persons'
CREATE TABLE [dbo].[Persons] (
    [Id] int IDENTITY(1,1) NOT NULL,
    [Name] nvarchar(50)  NOT NULL,
    [Address] nvarchar(50)  NOT NULL,
    [CityId] int  NOT NULL,
    [Email] nvarchar(50)  NULL,
    [TelephoneNumber] varchar(50)  NOT NULL,
    [TypeId] int  NOT NULL
);
GO

-- Creating table 'Persons_BoatEmployee'
CREATE TABLE [dbo].[Persons_BoatEmployee] (
    [BoatId] int  NOT NULL,
    [BoatEmployeeTypeId] int  NOT NULL,
    [WorkExperience] int  NOT NULL,
    [IdentificationNumber] int  NOT NULL,
    [Gender] nvarchar(50)  NOT NULL,
    [Id] int  NOT NULL
);
GO

-- Creating table 'BoatEmployeeTypes'
CREATE TABLE [dbo].[BoatEmployeeTypes] (
    [Id] int IDENTITY(1,1) NOT NULL,
    [EmployeeTypeName] nvarchar(50)  NOT NULL
);
GO

-- Creating table 'Boats'
CREATE TABLE [dbo].[Boats] (
    [Id] int IDENTITY(1,1) NOT NULL,
    [BoatName] nvarchar(50)  NOT NULL,
    [OwnerId] int  NOT NULL,
    [BoatTypeId] int  NOT NULL,
    [CountryId] int  NOT NULL,
    [Length] decimal(18,2)  NOT NULL
);
GO

-- Creating table 'BoatTypes'
CREATE TABLE [dbo].[BoatTypes] (
    [Id] int IDENTITY(1,1) NOT NULL,
    [BoatTypeName] varchar(50)  NOT NULL
);
GO

-- Creating table 'Cities'
CREATE TABLE [dbo].[Cities] (
    [Id] int IDENTITY(1,1) NOT NULL,
    [CityName] varchar(50)  NOT NULL,
    [CountryId] int  NOT NULL
);
GO

-- Creating table 'Contracts'
CREATE TABLE [dbo].[Contracts] (
    [Id] int IDENTITY(1,1) NOT NULL,
    [DateFrom] datetime  NOT NULL,
    [BoatId] int  NOT NULL,
    [DateTo] datetime  NOT NULL
);
GO

-- Creating table 'Countries'
CREATE TABLE [dbo].[Countries] (
    [Id] int IDENTITY(1,1) NOT NULL,
    [CountryName] varchar(50)  NOT NULL
);
GO

-- Creating table 'Persons_Employee'
CREATE TABLE [dbo].[Persons_Employee] (
    [MarinaId] int  NOT NULL,
    [MonthsWorking] int  NOT NULL,
    [PayCoefficient] decimal(18,2)  NOT NULL,
    [EmployeeTypeId] int  NOT NULL,
    [EarningsPerHour] decimal(18,2)  NOT NULL,
    [Id] int  NOT NULL
);
GO

-- Creating table 'EmployeeTypes'
CREATE TABLE [dbo].[EmployeeTypes] (
    [Id] int IDENTITY(1,1) NOT NULL,
    [TypeName] varchar(50)  NOT NULL
);
GO

-- Creating table 'EmployeeWorkTimes'
CREATE TABLE [dbo].[EmployeeWorkTimes] (
    [Id] int IDENTITY(1,1) NOT NULL,
    [EmployeeId] int  NOT NULL,
    [Date] datetime  NOT NULL,
    [StartTime] int  NOT NULL,
    [EndTime] int  NULL
);
GO

-- Creating table 'Marinas'
CREATE TABLE [dbo].[Marinas] (
    [Id] int IDENTITY(1,1) NOT NULL,
    [CityId] int  NOT NULL,
    [Name] nvarchar(50)  NOT NULL,
    [Address] nvarchar(50)  NOT NULL,
    [Coordinates] bigint  NOT NULL,
    [TelephoneNumber] varchar(50)  NULL
);
GO

-- Creating table 'Persons_Owner'
CREATE TABLE [dbo].[Persons_Owner] (
    [CountryId] int  NOT NULL,
    [CreditCardNumber] bigint  NOT NULL,
    [Id] int  NOT NULL
);
GO

-- Creating table 'PersonTypes'
CREATE TABLE [dbo].[PersonTypes] (
    [Id] int IDENTITY(1,1) NOT NULL,
    [PersonTypeName] nvarchar(50)  NOT NULL
);
GO

-- Creating table 'Reservations'
CREATE TABLE [dbo].[Reservations] (
    [Id] int IDENTITY(1,1) NOT NULL,
    [BerthTypeId] int  NOT NULL,
    [BoatId] int  NOT NULL,
    [DateFrom] datetime  NOT NULL,
    [DateTo] datetime  NOT NULL,
    [MarinaId] int  NOT NULL
);
GO

-- Creating table 'Services'
CREATE TABLE [dbo].[Services] (
    [Id] int IDENTITY(1,1) NOT NULL,
    [MarinaId] int  NOT NULL,
    [ServiceTypeId] int  NULL,
    [BerthServiceId] int  NULL,
    [ServicePrice] decimal(18,2)  NOT NULL,
    [BoatLength] decimal(18,2)  NULL
);
GO

-- Creating table 'ServiceTypes'
CREATE TABLE [dbo].[ServiceTypes] (
    [Id] int IDENTITY(1,1) NOT NULL,
    [ServiceTypeName] nvarchar(50)  NOT NULL
);
GO

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

-- Creating foreign key on [BerthId] in table 'BerthHistories'
ALTER TABLE [dbo].[BerthHistories]
ADD CONSTRAINT [FK_BerthHistory_Berths]
    FOREIGN KEY ([BerthId])
    REFERENCES [dbo].[Berths]
        ([Id])
    ON DELETE NO ACTION ON UPDATE NO ACTION;
GO

-- Creating non-clustered index for FOREIGN KEY 'FK_BerthHistory_Berths'
CREATE INDEX [IX_FK_BerthHistory_Berths]
ON [dbo].[BerthHistories]
    ([BerthId]);
GO

-- Creating foreign key on [BoatId] in table 'BerthHistories'
ALTER TABLE [dbo].[BerthHistories]
ADD CONSTRAINT [FK_BerthHistory_Boats]
    FOREIGN KEY ([BoatId])
    REFERENCES [dbo].[Boats]
        ([Id])
    ON DELETE NO ACTION ON UPDATE NO ACTION;
GO

-- Creating non-clustered index for FOREIGN KEY 'FK_BerthHistory_Boats'
CREATE INDEX [IX_FK_BerthHistory_Boats]
ON [dbo].[BerthHistories]
    ([BoatId]);
GO

-- Creating foreign key on [BerthType] in table 'Berths'
ALTER TABLE [dbo].[Berths]
ADD CONSTRAINT [FK_Berths_BerthTypes]
    FOREIGN KEY ([BerthType])
    REFERENCES [dbo].[BerthTypes]
        ([Id])
    ON DELETE NO ACTION ON UPDATE NO ACTION;
GO

-- Creating non-clustered index for FOREIGN KEY 'FK_Berths_BerthTypes'
CREATE INDEX [IX_FK_Berths_BerthTypes]
ON [dbo].[Berths]
    ([BerthType]);
GO

-- Creating foreign key on [MarinaId] in table 'Berths'
ALTER TABLE [dbo].[Berths]
ADD CONSTRAINT [FK_Berths_Marinas]
    FOREIGN KEY ([MarinaId])
    REFERENCES [dbo].[Marinas]
        ([Id])
    ON DELETE NO ACTION ON UPDATE NO ACTION;
GO

-- Creating non-clustered index for FOREIGN KEY 'FK_Berths_Marinas'
CREATE INDEX [IX_FK_Berths_Marinas]
ON [dbo].[Berths]
    ([MarinaId]);
GO

-- Creating foreign key on [BerthServiceDurationTypeId] in table 'BerthServiceTypes'
ALTER TABLE [dbo].[BerthServiceTypes]
ADD CONSTRAINT [FK_BerthServiceTypes_BerthServiceDurationTypes]
    FOREIGN KEY ([BerthServiceDurationTypeId])
    REFERENCES [dbo].[BerthServiceDurationTypes]
        ([Id])
    ON DELETE NO ACTION ON UPDATE NO ACTION;
GO

-- Creating non-clustered index for FOREIGN KEY 'FK_BerthServiceTypes_BerthServiceDurationTypes'
CREATE INDEX [IX_FK_BerthServiceTypes_BerthServiceDurationTypes]
ON [dbo].[BerthServiceTypes]
    ([BerthServiceDurationTypeId]);
GO

-- Creating foreign key on [BerthTypeId] in table 'BerthServiceTypes'
ALTER TABLE [dbo].[BerthServiceTypes]
ADD CONSTRAINT [FK_BerthServiceTypes_BerthTypes]
    FOREIGN KEY ([BerthTypeId])
    REFERENCES [dbo].[BerthTypes]
        ([Id])
    ON DELETE NO ACTION ON UPDATE NO ACTION;
GO

-- Creating non-clustered index for FOREIGN KEY 'FK_BerthServiceTypes_BerthTypes'
CREATE INDEX [IX_FK_BerthServiceTypes_BerthTypes]
ON [dbo].[BerthServiceTypes]
    ([BerthTypeId]);
GO

-- Creating foreign key on [BerthServiceId] in table 'Services'
ALTER TABLE [dbo].[Services]
ADD CONSTRAINT [FK_Service_Service]
    FOREIGN KEY ([BerthServiceId])
    REFERENCES [dbo].[BerthServiceTypes]
        ([Id])
    ON DELETE NO ACTION ON UPDATE NO ACTION;
GO

-- Creating non-clustered index for FOREIGN KEY 'FK_Service_Service'
CREATE INDEX [IX_FK_Service_Service]
ON [dbo].[Services]
    ([BerthServiceId]);
GO

-- Creating foreign key on [BerthTypeId] in table 'Reservations'
ALTER TABLE [dbo].[Reservations]
ADD CONSTRAINT [FK_Reservations_BerthTypes]
    FOREIGN KEY ([BerthTypeId])
    REFERENCES [dbo].[BerthTypes]
        ([Id])
    ON DELETE NO ACTION ON UPDATE NO ACTION;
GO

-- Creating non-clustered index for FOREIGN KEY 'FK_Reservations_BerthTypes'
CREATE INDEX [IX_FK_Reservations_BerthTypes]
ON [dbo].[Reservations]
    ([BerthTypeId]);
GO

-- Creating foreign key on [BillId] in table 'BillItems'
ALTER TABLE [dbo].[BillItems]
ADD CONSTRAINT [FK_BillItems_Bills]
    FOREIGN KEY ([BillId])
    REFERENCES [dbo].[Bills]
        ([Id])
    ON DELETE NO ACTION ON UPDATE NO ACTION;
GO

-- Creating non-clustered index for FOREIGN KEY 'FK_BillItems_Bills'
CREATE INDEX [IX_FK_BillItems_Bills]
ON [dbo].[BillItems]
    ([BillId]);
GO

-- Creating foreign key on [ServiceId] in table 'BillItems'
ALTER TABLE [dbo].[BillItems]
ADD CONSTRAINT [FK_BillItems_Service]
    FOREIGN KEY ([ServiceId])
    REFERENCES [dbo].[Services]
        ([Id])
    ON DELETE NO ACTION ON UPDATE NO ACTION;
GO

-- Creating non-clustered index for FOREIGN KEY 'FK_BillItems_Service'
CREATE INDEX [IX_FK_BillItems_Service]
ON [dbo].[BillItems]
    ([ServiceId]);
GO

-- Creating foreign key on [ContractId] in table 'Bills'
ALTER TABLE [dbo].[Bills]
ADD CONSTRAINT [FK_Bills_Contract]
    FOREIGN KEY ([ContractId])
    REFERENCES [dbo].[Contracts]
        ([Id])
    ON DELETE CASCADE ON UPDATE NO ACTION;
GO

-- Creating non-clustered index for FOREIGN KEY 'FK_Bills_Contract'
CREATE INDEX [IX_FK_Bills_Contract]
ON [dbo].[Bills]
    ([ContractId]);
GO

-- Creating foreign key on [BoatEmployeeTypeId] in table 'Persons_BoatEmployee'
ALTER TABLE [dbo].[Persons_BoatEmployee]
ADD CONSTRAINT [FK_BoatEmployees_BoatEmployeeTypes]
    FOREIGN KEY ([BoatEmployeeTypeId])
    REFERENCES [dbo].[BoatEmployeeTypes]
        ([Id])
    ON DELETE NO ACTION ON UPDATE NO ACTION;
GO

-- Creating non-clustered index for FOREIGN KEY 'FK_BoatEmployees_BoatEmployeeTypes'
CREATE INDEX [IX_FK_BoatEmployees_BoatEmployeeTypes]
ON [dbo].[Persons_BoatEmployee]
    ([BoatEmployeeTypeId]);
GO

-- Creating foreign key on [BoatId] in table 'Persons_BoatEmployee'
ALTER TABLE [dbo].[Persons_BoatEmployee]
ADD CONSTRAINT [FK_BoatEmployees_Boats]
    FOREIGN KEY ([BoatId])
    REFERENCES [dbo].[Boats]
        ([Id])
    ON DELETE CASCADE ON UPDATE NO ACTION;
GO

-- Creating non-clustered index for FOREIGN KEY 'FK_BoatEmployees_Boats'
CREATE INDEX [IX_FK_BoatEmployees_Boats]
ON [dbo].[Persons_BoatEmployee]
    ([BoatId]);
GO

-- Creating foreign key on [BoatTypeId] in table 'Boats'
ALTER TABLE [dbo].[Boats]
ADD CONSTRAINT [FK_Boats_BoatTypes]
    FOREIGN KEY ([BoatTypeId])
    REFERENCES [dbo].[BoatTypes]
        ([Id])
    ON DELETE NO ACTION ON UPDATE NO ACTION;
GO

-- Creating non-clustered index for FOREIGN KEY 'FK_Boats_BoatTypes'
CREATE INDEX [IX_FK_Boats_BoatTypes]
ON [dbo].[Boats]
    ([BoatTypeId]);
GO

-- Creating foreign key on [CountryId] in table 'Boats'
ALTER TABLE [dbo].[Boats]
ADD CONSTRAINT [FK_Boats_Countries]
    FOREIGN KEY ([CountryId])
    REFERENCES [dbo].[Countries]
        ([Id])
    ON DELETE NO ACTION ON UPDATE NO ACTION;
GO

-- Creating non-clustered index for FOREIGN KEY 'FK_Boats_Countries'
CREATE INDEX [IX_FK_Boats_Countries]
ON [dbo].[Boats]
    ([CountryId]);
GO

-- Creating foreign key on [OwnerId] in table 'Boats'
ALTER TABLE [dbo].[Boats]
ADD CONSTRAINT [FK_Boats_Owner]
    FOREIGN KEY ([OwnerId])
    REFERENCES [dbo].[Persons_Owner]
        ([Id])
    ON DELETE NO ACTION ON UPDATE NO ACTION;
GO

-- Creating non-clustered index for FOREIGN KEY 'FK_Boats_Owner'
CREATE INDEX [IX_FK_Boats_Owner]
ON [dbo].[Boats]
    ([OwnerId]);
GO

-- Creating foreign key on [BoatId] in table 'Contracts'
ALTER TABLE [dbo].[Contracts]
ADD CONSTRAINT [FK_Contracts_Boats]
    FOREIGN KEY ([BoatId])
    REFERENCES [dbo].[Boats]
        ([Id])
    ON DELETE CASCADE ON UPDATE NO ACTION;
GO

-- Creating non-clustered index for FOREIGN KEY 'FK_Contracts_Boats'
CREATE INDEX [IX_FK_Contracts_Boats]
ON [dbo].[Contracts]
    ([BoatId]);
GO

-- Creating foreign key on [BoatId] in table 'Reservations'
ALTER TABLE [dbo].[Reservations]
ADD CONSTRAINT [FK_Reservations_Boats]
    FOREIGN KEY ([BoatId])
    REFERENCES [dbo].[Boats]
        ([Id])
    ON DELETE CASCADE ON UPDATE NO ACTION;
GO

-- Creating non-clustered index for FOREIGN KEY 'FK_Reservations_Boats'
CREATE INDEX [IX_FK_Reservations_Boats]
ON [dbo].[Reservations]
    ([BoatId]);
GO

-- Creating foreign key on [CountryId] in table 'Cities'
ALTER TABLE [dbo].[Cities]
ADD CONSTRAINT [FK_Cities_Countries]
    FOREIGN KEY ([CountryId])
    REFERENCES [dbo].[Countries]
        ([Id])
    ON DELETE NO ACTION ON UPDATE NO ACTION;
GO

-- Creating non-clustered index for FOREIGN KEY 'FK_Cities_Countries'
CREATE INDEX [IX_FK_Cities_Countries]
ON [dbo].[Cities]
    ([CountryId]);
GO

-- Creating foreign key on [CityId] in table 'Marinas'
ALTER TABLE [dbo].[Marinas]
ADD CONSTRAINT [FK_Marinas_Cities]
    FOREIGN KEY ([CityId])
    REFERENCES [dbo].[Cities]
        ([Id])
    ON DELETE NO ACTION ON UPDATE NO ACTION;
GO

-- Creating non-clustered index for FOREIGN KEY 'FK_Marinas_Cities'
CREATE INDEX [IX_FK_Marinas_Cities]
ON [dbo].[Marinas]
    ([CityId]);
GO

-- Creating foreign key on [CityId] in table 'Persons'
ALTER TABLE [dbo].[Persons]
ADD CONSTRAINT [FK_Persons_Cities]
    FOREIGN KEY ([CityId])
    REFERENCES [dbo].[Cities]
        ([Id])
    ON DELETE NO ACTION ON UPDATE NO ACTION;
GO

-- Creating non-clustered index for FOREIGN KEY 'FK_Persons_Cities'
CREATE INDEX [IX_FK_Persons_Cities]
ON [dbo].[Persons]
    ([CityId]);
GO

-- Creating foreign key on [CountryId] in table 'Persons_Owner'
ALTER TABLE [dbo].[Persons_Owner]
ADD CONSTRAINT [FK_Owner_Countries]
    FOREIGN KEY ([CountryId])
    REFERENCES [dbo].[Countries]
        ([Id])
    ON DELETE NO ACTION ON UPDATE NO ACTION;
GO

-- Creating non-clustered index for FOREIGN KEY 'FK_Owner_Countries'
CREATE INDEX [IX_FK_Owner_Countries]
ON [dbo].[Persons_Owner]
    ([CountryId]);
GO

-- Creating foreign key on [EmployeeTypeId] in table 'Persons_Employee'
ALTER TABLE [dbo].[Persons_Employee]
ADD CONSTRAINT [FK_Employees_EmployeeTypes]
    FOREIGN KEY ([EmployeeTypeId])
    REFERENCES [dbo].[EmployeeTypes]
        ([Id])
    ON DELETE NO ACTION ON UPDATE NO ACTION;
GO

-- Creating non-clustered index for FOREIGN KEY 'FK_Employees_EmployeeTypes'
CREATE INDEX [IX_FK_Employees_EmployeeTypes]
ON [dbo].[Persons_Employee]
    ([EmployeeTypeId]);
GO

-- Creating foreign key on [MarinaId] in table 'Persons_Employee'
ALTER TABLE [dbo].[Persons_Employee]
ADD CONSTRAINT [FK_Employees_Marinas]
    FOREIGN KEY ([MarinaId])
    REFERENCES [dbo].[Marinas]
        ([Id])
    ON DELETE NO ACTION ON UPDATE NO ACTION;
GO

-- Creating non-clustered index for FOREIGN KEY 'FK_Employees_Marinas'
CREATE INDEX [IX_FK_Employees_Marinas]
ON [dbo].[Persons_Employee]
    ([MarinaId]);
GO

-- Creating foreign key on [EmployeeId] in table 'EmployeeWorkTimes'
ALTER TABLE [dbo].[EmployeeWorkTimes]
ADD CONSTRAINT [FK_EmployeeWorkTimes_Employees]
    FOREIGN KEY ([EmployeeId])
    REFERENCES [dbo].[Persons_Employee]
        ([Id])
    ON DELETE NO ACTION ON UPDATE NO ACTION;
GO

-- Creating non-clustered index for FOREIGN KEY 'FK_EmployeeWorkTimes_Employees'
CREATE INDEX [IX_FK_EmployeeWorkTimes_Employees]
ON [dbo].[EmployeeWorkTimes]
    ([EmployeeId]);
GO

-- Creating foreign key on [MarinaId] in table 'Reservations'
ALTER TABLE [dbo].[Reservations]
ADD CONSTRAINT [FK_Reservations_Marinas]
    FOREIGN KEY ([MarinaId])
    REFERENCES [dbo].[Marinas]
        ([Id])
    ON DELETE NO ACTION ON UPDATE NO ACTION;
GO

-- Creating non-clustered index for FOREIGN KEY 'FK_Reservations_Marinas'
CREATE INDEX [IX_FK_Reservations_Marinas]
ON [dbo].[Reservations]
    ([MarinaId]);
GO

-- Creating foreign key on [MarinaId] in table 'Services'
ALTER TABLE [dbo].[Services]
ADD CONSTRAINT [FK_Service_Marinas]
    FOREIGN KEY ([MarinaId])
    REFERENCES [dbo].[Marinas]
        ([Id])
    ON DELETE NO ACTION ON UPDATE NO ACTION;
GO

-- Creating non-clustered index for FOREIGN KEY 'FK_Service_Marinas'
CREATE INDEX [IX_FK_Service_Marinas]
ON [dbo].[Services]
    ([MarinaId]);
GO

-- Creating foreign key on [TypeId] in table 'Persons'
ALTER TABLE [dbo].[Persons]
ADD CONSTRAINT [FK_Persons_PersonType]
    FOREIGN KEY ([TypeId])
    REFERENCES [dbo].[PersonTypes]
        ([Id])
    ON DELETE NO ACTION ON UPDATE NO ACTION;
GO

-- Creating non-clustered index for FOREIGN KEY 'FK_Persons_PersonType'
CREATE INDEX [IX_FK_Persons_PersonType]
ON [dbo].[Persons]
    ([TypeId]);
GO

-- Creating foreign key on [ServiceTypeId] in table 'Services'
ALTER TABLE [dbo].[Services]
ADD CONSTRAINT [FK_Service_ServiceTypes]
    FOREIGN KEY ([ServiceTypeId])
    REFERENCES [dbo].[ServiceTypes]
        ([Id])
    ON DELETE NO ACTION ON UPDATE NO ACTION;
GO

-- Creating non-clustered index for FOREIGN KEY 'FK_Service_ServiceTypes'
CREATE INDEX [IX_FK_Service_ServiceTypes]
ON [dbo].[Services]
    ([ServiceTypeId]);
GO

-- Creating foreign key on [Id] in table 'Persons_BoatEmployee'
ALTER TABLE [dbo].[Persons_BoatEmployee]
ADD CONSTRAINT [FK_BoatEmployee_inherits_Person]
    FOREIGN KEY ([Id])
    REFERENCES [dbo].[Persons]
        ([Id])
    ON DELETE CASCADE ON UPDATE NO ACTION;
GO

-- Creating foreign key on [Id] in table 'Persons_Employee'
ALTER TABLE [dbo].[Persons_Employee]
ADD CONSTRAINT [FK_Employee_inherits_Person]
    FOREIGN KEY ([Id])
    REFERENCES [dbo].[Persons]
        ([Id])
    ON DELETE CASCADE ON UPDATE NO ACTION;
GO

-- Creating foreign key on [Id] in table 'Persons_Owner'
ALTER TABLE [dbo].[Persons_Owner]
ADD CONSTRAINT [FK_Owner_inherits_Person]
    FOREIGN KEY ([Id])
    REFERENCES [dbo].[Persons]
        ([Id])
    ON DELETE CASCADE ON UPDATE NO ACTION;
GO

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