AgencyApplications/bd.sql

USE [gr606_midvl]
GO
/****** Object:  Table [dbo].[Addreses]    Script Date: 02.06.2022 18:20:27 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[Addreses](
	[Id] [int] IDENTITY(1,1) NOT NULL,
	[Streat] [nvarchar](100) NULL,
	[HouseNumber] [int] NULL,
	[FlatNumber] [int] NULL,
	[City] [nvarchar](100) NULL,
 CONSTRAINT [PK_Addreses] 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
/****** Object:  Table [dbo].[ListPerson]    Script Date: 02.06.2022 18:20:27 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[ListPerson](
	[IDList] [int] IDENTITY(1,1) NOT NULL,
	[IDPerson] [int] NOT NULL,
	[IDProperty] [int] NOT NULL,
 CONSTRAINT [PK_ListPerson] PRIMARY KEY CLUSTERED 
(
	[IDList] 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
/****** Object:  Table [dbo].[Passports]    Script Date: 02.06.2022 18:20:27 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[Passports](
	[Id] [int] IDENTITY(1,1) NOT NULL,
	[Serial] [varchar](max) NOT NULL,
	[Number] [varchar](max) NOT NULL,
	[Issuedby] [varchar](max) NULL,
	[DateOfIssue] [date] NULL,
	[OwnerId] [int] NULL,
 CONSTRAINT [PK_Passports] 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] TEXTIMAGE_ON [PRIMARY]
GO
/****** Object:  Table [dbo].[Persons]    Script Date: 02.06.2022 18:20:27 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[Persons](
	[IdPerson] [int] IDENTITY(1,1) NOT NULL,
	[FirstName] [nvarchar](100) NULL,
	[LastName] [nvarchar](100) NULL,
	[MidleName] [nvarchar](100) NULL,
	[Login] [varchar](100) NULL,
	[Password] [varchar](100) NULL,
	[PhoneNumber] [varchar](100) NULL,
 CONSTRAINT [PK_Persons] PRIMARY KEY CLUSTERED 
(
	[IdPerson] 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
/****** Object:  Table [dbo].[TheProperty]    Script Date: 02.06.2022 18:20:27 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[TheProperty](
	[Id] [int] IDENTITY(1,1) NOT NULL,
	[Name] [varchar](100) NULL,
	[Area] [float] NULL,
	[Rooms] [int] NULL,
	[PropertyType] [varchar](100) NULL,
	[District] [varchar](100) NULL,
	[YearOfConstruction] [date] NULL,
	[Floor] [int] NULL,
	[Owner] [int] NULL,
	[Address] [int] NOT NULL,
	[Price] [decimal](18, 2) NOT NULL,
	[TypeRent] [nvarchar](510) NULL,
 CONSTRAINT [PK_TheProperty] 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
/****** Object:  Table [dbo].[Treaty]    Script Date: 02.06.2022 18:20:27 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[Treaty](
	[IdTreaty] [int] IDENTITY(1,1) NOT NULL,
	[PropertyId] [int] NOT NULL,
	[RentalPeriod] [int] NULL,
	[DateOfConclusion] [date] NULL,
	[Cost] [decimal](18, 2) NOT NULL,
	[PersonsInTreaty] [int] NOT NULL,
 CONSTRAINT [PK_Treaty] PRIMARY KEY CLUSTERED 
(
	[IdTreaty] 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
ALTER TABLE [dbo].[ListPerson]  WITH CHECK ADD  CONSTRAINT [FK_ListPerson_Persons] FOREIGN KEY([IDPerson])
REFERENCES [dbo].[Persons] ([IdPerson])
GO
ALTER TABLE [dbo].[ListPerson] CHECK CONSTRAINT [FK_ListPerson_Persons]
GO
ALTER TABLE [dbo].[ListPerson]  WITH CHECK ADD  CONSTRAINT [FK_ListPerson_TheProperty] FOREIGN KEY([IDProperty])
REFERENCES [dbo].[TheProperty] ([Id])
GO
ALTER TABLE [dbo].[ListPerson] CHECK CONSTRAINT [FK_ListPerson_TheProperty]
GO
ALTER TABLE [dbo].[Passports]  WITH CHECK ADD  CONSTRAINT [FK_Passports_Persons] FOREIGN KEY([OwnerId])
REFERENCES [dbo].[Persons] ([IdPerson])
GO
ALTER TABLE [dbo].[Passports] CHECK CONSTRAINT [FK_Passports_Persons]
GO
ALTER TABLE [dbo].[TheProperty]  WITH CHECK ADD  CONSTRAINT [FK_TheProperty_Addreses] FOREIGN KEY([Address])
REFERENCES [dbo].[Addreses] ([Id])
GO
ALTER TABLE [dbo].[TheProperty] CHECK CONSTRAINT [FK_TheProperty_Addreses]
GO
ALTER TABLE [dbo].[Treaty]  WITH CHECK ADD  CONSTRAINT [FK_Treaty_ListPerson] FOREIGN KEY([PersonsInTreaty])
REFERENCES [dbo].[ListPerson] ([IDList])
GO
ALTER TABLE [dbo].[Treaty] CHECK CONSTRAINT [FK_Treaty_ListPerson]
GO
ALTER TABLE [dbo].[Treaty]  WITH CHECK ADD  CONSTRAINT [FK_Treaty_TheProperty] FOREIGN KEY([PropertyId])
REFERENCES [dbo].[TheProperty] ([Id])
GO
ALTER TABLE [dbo].[Treaty] CHECK CONSTRAINT [FK_Treaty_TheProperty]
GO