CityBuldingFloorSuite-Object

USE [test1]
GO

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[BuildingNames](
    [BuildingID] [INT] NOT NULL,
    [LanguageId] [INT] NOT NULL,
    [name] [nvarchar](100) NULL,
PRIMARY KEY CLUSTERED
(
    [BuildingID] ASC,
    [LanguageId] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]

GO

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[Buildings](
    [ID] [INT] IDENTITY(1,1) NOT NULL,
    [CityID] [INT] NULL,
PRIMARY KEY CLUSTERED
(
    [ID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]

GO

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[Cities](
    [ID] [INT] IDENTITY(1,1) NOT NULL,
PRIMARY KEY CLUSTERED
(
    [ID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]

GO

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[CityLanguages](
    [CityID] [INT] NOT NULL,
    [LanguageId] [INT] NOT NULL,
    [name] [nvarchar](100) NULL,
PRIMARY KEY CLUSTERED
(
    [CityID] ASC,
    [LanguageId] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]

GO

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[FloorNames](
    [FloorID] [INT] NOT NULL,
    [LanguageId] [INT] NOT NULL,
    [name] [nvarchar](100) NULL,
PRIMARY KEY CLUSTERED
(
    [FloorID] ASC,
    [LanguageId] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]

GO

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[Floors](
    [ID] [INT] IDENTITY(1,1) NOT NULL,
    [BuildingID] [INT] NULL,
PRIMARY KEY CLUSTERED
(
    [ID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]

GO

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[Languages](
    [ID] [INT] NOT NULL,
    [languageName] [nvarchar](50) NOT NULL,
    [languageCode] [nvarchar](10) NOT NULL,
 CONSTRAINT [PK_Languages] PRIMARY KEY CLUSTERED
(
    [ID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]

GO

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[ObjectLocations](
    [ID] [INT] IDENTITY(1,1) NOT NULL,
    [ObjectID] [INT] NOT NULL,
    [SuiteID] [INT] NULL,
    [FloorID] [INT] NULL,
PRIMARY KEY CLUSTERED
(
    [ID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]

GO

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[ObjectNames](
    [ObjectID] [INT] NOT NULL,
    [LanguageId] [INT] NOT NULL,
    [name] [nvarchar](100) NULL,
PRIMARY KEY CLUSTERED
(
    [ObjectID] ASC,
    [LanguageId] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]

GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[Objects](
    [ID] [INT] IDENTITY(1,1) NOT NULL,
    [BuildingID] [INT] NOT NULL,
PRIMARY KEY CLUSTERED
(
    [ID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]

GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[SuiteNames](
    [SuiteID] [INT] NOT NULL,
    [LanguageId] [INT] NOT NULL,
    [name] [nvarchar](100) NULL,
PRIMARY KEY CLUSTERED
(
    [SuiteID] ASC,
    [LanguageId] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]

GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[Suites](
    [ID] [INT] IDENTITY(1,1) NOT NULL,
    [FloorID] [INT] NULL,
PRIMARY KEY CLUSTERED
(
    [ID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]

GO
INSERT [dbo].[BuildingNames] ([BuildingID], [LanguageId], [name]) VALUES (1, 1, N'The Building 1')
GO
INSERT [dbo].[BuildingNames] ([BuildingID], [LanguageId], [name]) VALUES (1, 2, N'Le Building 1')
GO
INSERT [dbo].[BuildingNames] ([BuildingID], [LanguageId], [name]) VALUES (1, 3, N'Das Building 1')
GO
SET IDENTITY_INSERT [dbo].[Buildings] ON

GO
INSERT [dbo].[Buildings] ([ID], [CityID]) VALUES (1, 1)
GO
INSERT [dbo].[Buildings] ([ID], [CityID]) VALUES (2, 2)
GO
SET IDENTITY_INSERT [dbo].[Buildings] OFF
GO
SET IDENTITY_INSERT [dbo].[Cities] ON

GO
INSERT [dbo].[Cities] ([ID]) VALUES (1)
GO
INSERT [dbo].[Cities] ([ID]) VALUES (2)
GO
INSERT [dbo].[Cities] ([ID]) VALUES (3)
GO
SET IDENTITY_INSERT [dbo].[Cities] OFF
GO
INSERT [dbo].[CityLanguages] ([CityID], [LanguageId], [name]) VALUES (1, 1, N'The Prague')
GO
INSERT [dbo].[CityLanguages] ([CityID], [LanguageId], [name]) VALUES (1, 2, N'Le Prague')
GO
INSERT [dbo].[CityLanguages] ([CityID], [LanguageId], [name]) VALUES (1, 3, N'Das Prague')
GO
INSERT [dbo].[CityLanguages] ([CityID], [LanguageId], [name]) VALUES (1, 4, N'La Pragua')
GO
INSERT [dbo].[CityLanguages] ([CityID], [LanguageId], [name]) VALUES (2, 1, N'The Moscow')
GO
INSERT [dbo].[CityLanguages] ([CityID], [LanguageId], [name]) VALUES (2, 2, N'Le Moscow')
GO
INSERT [dbo].[CityLanguages] ([CityID], [LanguageId], [name]) VALUES (3, 1, N'New York')
GO
INSERT [dbo].[FloorNames] ([FloorID], [LanguageId], [name]) VALUES (1, 1, N'The Floor 1')
GO
INSERT [dbo].[FloorNames] ([FloorID], [LanguageId], [name]) VALUES (1, 2, N'Le Etage 1')
GO
INSERT [dbo].[FloorNames] ([FloorID], [LanguageId], [name]) VALUES (1, 3, N'Das Stufe 1')
GO
INSERT [dbo].[FloorNames] ([FloorID], [LanguageId], [name]) VALUES (2, 1, N'The Floor 2')
GO
INSERT [dbo].[FloorNames] ([FloorID], [LanguageId], [name]) VALUES (3, 1, N'The Floor 3')
GO
INSERT [dbo].[FloorNames] ([FloorID], [LanguageId], [name]) VALUES (4, 1, N'The Floor 1')
GO
INSERT [dbo].[FloorNames] ([FloorID], [LanguageId], [name]) VALUES (5, 2, N'Le Etage 2')
GO
INSERT [dbo].[FloorNames] ([FloorID], [LanguageId], [name]) VALUES (6, 1, N'The Floor 3')
GO
SET IDENTITY_INSERT [dbo].[Floors] ON

GO
INSERT [dbo].[Floors] ([ID], [BuildingID]) VALUES (1, 1)
GO
INSERT [dbo].[Floors] ([ID], [BuildingID]) VALUES (2, 1)
GO
INSERT [dbo].[Floors] ([ID], [BuildingID]) VALUES (3, 1)
GO
INSERT [dbo].[Floors] ([ID], [BuildingID]) VALUES (4, 2)
GO
INSERT [dbo].[Floors] ([ID], [BuildingID]) VALUES (5, 2)
GO
INSERT [dbo].[Floors] ([ID], [BuildingID]) VALUES (6, 2)
GO
SET IDENTITY_INSERT [dbo].[Floors] OFF
GO
INSERT [dbo].[Languages] ([ID], [languageName], [languageCode]) VALUES (1, N'English', N'EN')
GO
INSERT [dbo].[Languages] ([ID], [languageName], [languageCode]) VALUES (2, N'French', N'FR')
GO
INSERT [dbo].[Languages] ([ID], [languageName], [languageCode]) VALUES (3, N'German', N'DE')
GO
INSERT [dbo].[Languages] ([ID], [languageName], [languageCode]) VALUES (4, N'Spanish', N'ES')
GO
SET IDENTITY_INSERT [dbo].[ObjectLocations] ON

GO
INSERT [dbo].[ObjectLocations] ([ID], [ObjectID], [SuiteID], [FloorID]) VALUES (1, 1, 9, 3)
GO
INSERT [dbo].[ObjectLocations] ([ID], [ObjectID], [SuiteID], [FloorID]) VALUES (4, 2, NULL, 4)
GO
INSERT [dbo].[ObjectLocations] ([ID], [ObjectID], [SuiteID], [FloorID]) VALUES (5, 3, 3, 1)
GO
INSERT [dbo].[ObjectLocations] ([ID], [ObjectID], [SuiteID], [FloorID]) VALUES (6, 3, 2, 1)
GO
SET IDENTITY_INSERT [dbo].[ObjectLocations] OFF
GO
INSERT [dbo].[ObjectNames] ([ObjectID], [LanguageId], [name]) VALUES (1, 1, N'The Car')
GO
INSERT [dbo].[ObjectNames] ([ObjectID], [LanguageId], [name]) VALUES (1, 2, N'Le Car')
GO
INSERT [dbo].[ObjectNames] ([ObjectID], [LanguageId], [name]) VALUES (1, 3, N'Das Car')
GO
INSERT [dbo].[ObjectNames] ([ObjectID], [LanguageId], [name]) VALUES (1, 4, N'La Car')
GO
INSERT [dbo].[ObjectNames] ([ObjectID], [LanguageId], [name]) VALUES (2, 1, N'The Sink')
GO
INSERT [dbo].[ObjectNames] ([ObjectID], [LanguageId], [name]) VALUES (2, 2, N'Le Sink')
GO
INSERT [dbo].[ObjectNames] ([ObjectID], [LanguageId], [name]) VALUES (2, 3, N'Das Sink')
GO
INSERT [dbo].[ObjectNames] ([ObjectID], [LanguageId], [name]) VALUES (3, 2, N'Le Cat')
GO
INSERT [dbo].[ObjectNames] ([ObjectID], [LanguageId], [name]) VALUES (3, 3, N'Das Cat')
GO
INSERT [dbo].[ObjectNames] ([ObjectID], [LanguageId], [name]) VALUES (3, 4, N'La Cat')
GO
SET IDENTITY_INSERT [dbo].[Objects] ON

GO
INSERT [dbo].[Objects] ([ID], [BuildingID]) VALUES (1, 1)
GO
INSERT [dbo].[Objects] ([ID], [BuildingID]) VALUES (2, 1)
GO
INSERT [dbo].[Objects] ([ID], [BuildingID]) VALUES (3, 1)
GO
SET IDENTITY_INSERT [dbo].[Objects] OFF
GO
INSERT [dbo].[SuiteNames] ([SuiteID], [LanguageId], [name]) VALUES (1, 1, N'The Suite 110')
GO
INSERT [dbo].[SuiteNames] ([SuiteID], [LanguageId], [name]) VALUES (2, 1, N'The Suite 120')
GO
INSERT [dbo].[SuiteNames] ([SuiteID], [LanguageId], [name]) VALUES (3, 1, N'The Suite 130')
GO
INSERT [dbo].[SuiteNames] ([SuiteID], [LanguageId], [name]) VALUES (4, 1, N'The Suite 210')
GO
INSERT [dbo].[SuiteNames] ([SuiteID], [LanguageId], [name]) VALUES (5, 1, N'The Suite 220')
GO
INSERT [dbo].[SuiteNames] ([SuiteID], [LanguageId], [name]) VALUES (6, 1, N'The Suite 230')
GO
INSERT [dbo].[SuiteNames] ([SuiteID], [LanguageId], [name]) VALUES (7, 1, N'The Suite 310')
GO
INSERT [dbo].[SuiteNames] ([SuiteID], [LanguageId], [name]) VALUES (8, 1, N'The Suite 320')
GO
INSERT [dbo].[SuiteNames] ([SuiteID], [LanguageId], [name]) VALUES (9, 1, N'The Suite 330')
GO
SET IDENTITY_INSERT [dbo].[Suites] ON

GO
INSERT [dbo].[Suites] ([ID], [FloorID]) VALUES (1, 1)
GO
INSERT [dbo].[Suites] ([ID], [FloorID]) VALUES (2, 1)
GO
INSERT [dbo].[Suites] ([ID], [FloorID]) VALUES (3, 1)
GO
INSERT [dbo].[Suites] ([ID], [FloorID]) VALUES (4, 2)
GO
INSERT [dbo].[Suites] ([ID], [FloorID]) VALUES (5, 2)
GO
INSERT [dbo].[Suites] ([ID], [FloorID]) VALUES (6, 2)
GO
INSERT [dbo].[Suites] ([ID], [FloorID]) VALUES (7, 3)
GO
INSERT [dbo].[Suites] ([ID], [FloorID]) VALUES (8, 3)
GO
INSERT [dbo].[Suites] ([ID], [FloorID]) VALUES (9, 3)
GO
INSERT [dbo].[Suites] ([ID], [FloorID]) VALUES (10, 4)
GO
INSERT [dbo].[Suites] ([ID], [FloorID]) VALUES (11, 4)
GO
INSERT [dbo].[Suites] ([ID], [FloorID]) VALUES (12, 5)
GO
INSERT [dbo].[Suites] ([ID], [FloorID]) VALUES (13, 5)
GO
SET IDENTITY_INSERT [dbo].[Suites] OFF
GO
ALTER TABLE [dbo].[BuildingNames]  WITH CHECK ADD FOREIGN KEY([BuildingID])
REFERENCES [dbo].[Buildings] ([ID])
GO
ALTER TABLE [dbo].[BuildingNames]  WITH CHECK ADD FOREIGN KEY([LanguageId])
REFERENCES [dbo].[Languages] ([ID])
GO
ALTER TABLE [dbo].[Buildings]  WITH CHECK ADD  CONSTRAINT [fk_Building_City] FOREIGN KEY([CityID])
REFERENCES [dbo].[Cities] ([ID])
GO
ALTER TABLE [dbo].[Buildings] CHECK CONSTRAINT [fk_Building_City]
GO
ALTER TABLE [dbo].[CityLanguages]  WITH CHECK ADD FOREIGN KEY([CityID])
REFERENCES [dbo].[Cities] ([ID])
GO
ALTER TABLE [dbo].[CityLanguages]  WITH CHECK ADD FOREIGN KEY([LanguageId])
REFERENCES [dbo].[Languages] ([ID])
GO
ALTER TABLE [dbo].[FloorNames]  WITH CHECK ADD FOREIGN KEY([FloorID])
REFERENCES [dbo].[Floors] ([ID])
GO
ALTER TABLE [dbo].[FloorNames]  WITH CHECK ADD FOREIGN KEY([LanguageId])
REFERENCES [dbo].[Languages] ([ID])
GO
ALTER TABLE [dbo].[Floors]  WITH CHECK ADD  CONSTRAINT [fk_Floor_Building] FOREIGN KEY([BuildingID])
REFERENCES [dbo].[Buildings] ([ID])
GO
ALTER TABLE [dbo].[Floors] CHECK CONSTRAINT [fk_Floor_Building]
GO
ALTER TABLE [dbo].[ObjectLocations]  WITH CHECK ADD FOREIGN KEY([FloorID])
REFERENCES [dbo].[Floors] ([ID])
GO
ALTER TABLE [dbo].[ObjectLocations]  WITH CHECK ADD FOREIGN KEY([ObjectID])
REFERENCES [dbo].[Objects] ([ID])
GO
ALTER TABLE [dbo].[ObjectLocations]  WITH CHECK ADD FOREIGN KEY([SuiteID])
REFERENCES [dbo].[Suites] ([ID])
GO
ALTER TABLE [dbo].[ObjectNames]  WITH CHECK ADD FOREIGN KEY([LanguageId])
REFERENCES [dbo].[Languages] ([ID])
GO
ALTER TABLE [dbo].[ObjectNames]  WITH CHECK ADD FOREIGN KEY([ObjectID])
REFERENCES [dbo].[Objects] ([ID])
GO
ALTER TABLE [dbo].[Objects]  WITH CHECK ADD FOREIGN KEY([BuildingID])
REFERENCES [dbo].[Buildings] ([ID])
GO
ALTER TABLE [dbo].[SuiteNames]  WITH CHECK ADD FOREIGN KEY([LanguageId])
REFERENCES [dbo].[Languages] ([ID])
GO
ALTER TABLE [dbo].[SuiteNames]  WITH CHECK ADD FOREIGN KEY([SuiteID])
REFERENCES [dbo].[Suites] ([ID])
GO
ALTER TABLE [dbo].[Suites]  WITH CHECK ADD  CONSTRAINT [fk_Suite_Floor] FOREIGN KEY([FloorID])
REFERENCES [dbo].[Floors] ([ID])
GO
ALTER TABLE [dbo].[Suites] CHECK CONSTRAINT [fk_Suite_Floor]
GO