asd

1 files changed, 116 insertions, 0 deletions

diff --git a/Query.sql b/Query.sql
new file mode 100644
index 0000000..5ca9c02
--- /dev/null
+++ b/Query.sql
@@ -0,0 +1,116 @@
+CREATE TABLE [dbo].[Apartment] (
+    [ApartmentId]   INT           NOT NULL,
+    [Size]          FLOAT (53)    NULL,
+    [NumberOfRooms] INT           NULL,
+    [MonthlyCharge] FLOAT (53)    NULL,
+    [Floor]         INT           NULL,
+    [Address]       VARCHAR (100) NULL,
+    [PlanPicture]   VARCHAR (MAX) NULL,
+    PRIMARY KEY CLUSTERED ([ApartmentId] ASC)
+);
+
+CREATE TABLE [dbo].[Change] (
+    [ChangeId]    INT           IDENTITY (0, 1) NOT NULL,
+    [ApartmentId] INT           NOT NULL,
+    [Name]        VARCHAR (50)  NULL,
+    [UploadDate]  DATE          NULL,
+    [Description] VARCHAR (MAX) NULL,
+    [Status]      VARCHAR (50)  NULL,
+    PRIMARY KEY CLUSTERED ([ChangeId] ASC),
+    CONSTRAINT [FK_Change_ToApartment] FOREIGN KEY ([ApartmentId]) REFERENCES [dbo].[Apartment] ([ApartmentId])
+);
+
+CREATE TABLE [dbo].[ChangeComment] (
+    [CommentId] INT           IDENTITY (0, 1) NOT NULL,
+    [ChangeId]  INT           NOT NULL,
+    [Comment]   VARCHAR (MAX) NULL,
+    [Name]      VARCHAR (50)  NULL,
+    [Date]      DATETIME      NULL,
+    PRIMARY KEY CLUSTERED ([CommentId] ASC),
+    CONSTRAINT [FK_ChangeComment_ToChange] FOREIGN KEY ([ChangeId]) REFERENCES [dbo].[Change] ([ChangeId]) ON DELETE CASCADE
+);
+
+CREATE TABLE [dbo].[ChangeDocument] (
+    [DocumentId] INT          IDENTITY (0, 1) NOT NULL,
+    [ChangeId]   INT          NOT NULL,
+    [Document]   VARCHAR (50) NULL,
+    PRIMARY KEY CLUSTERED ([DocumentId] ASC),
+    CONSTRAINT [FK_ChangeDocument_ToChange] FOREIGN KEY ([ChangeId]) REFERENCES [dbo].[Change] ([ChangeId]) ON DELETE CASCADE
+);
+
+CREATE TABLE [dbo].[Defect] (
+    [DefectId]    INT           IDENTITY (0, 1) NOT NULL,
+    [ApartmentId] INT           NOT NULL,
+    [Name]        VARCHAR (50)  NULL,
+    [UploadDate]  DATE          NULL,
+    [Description] VARCHAR (MAX) NULL,
+    [Status]      VARCHAR (50)  NULL,
+    PRIMARY KEY CLUSTERED ([DefectId] ASC),
+    CONSTRAINT [FK_Defect_ToApartment] FOREIGN KEY ([ApartmentId]) REFERENCES [dbo].[Apartment] ([ApartmentId])
+);
+
+CREATE TABLE [dbo].[DefectComment] (
+    [CommentId] INT           IDENTITY (0, 1) NOT NULL,
+    [DefectId]  INT           NOT NULL,
+    [Comment]   VARCHAR (MAX) NULL,
+    [Name]      VARCHAR (50)  NULL,
+    [Date]      DATETIME      NULL,
+    PRIMARY KEY CLUSTERED ([CommentId] ASC),
+    CONSTRAINT [FK_DefectComment_ToDefect] FOREIGN KEY ([DefectId]) REFERENCES [dbo].[Defect] ([DefectId]) ON DELETE CASCADE
+);
+
+CREATE TABLE [dbo].[DefectPicture] (
+    [PictureId] INT           IDENTITY (0, 1) NOT NULL,
+    [DefectId]  INT           NOT NULL,
+    [Picture]   VARCHAR (MAX) NULL,
+    PRIMARY KEY CLUSTERED ([PictureId] ASC),
+    CONSTRAINT [FK_DefectPicture_ToDefect] FOREIGN KEY ([DefectId]) REFERENCES [dbo].[Defect] ([DefectId]) ON DELETE CASCADE
+);
+
+CREATE TABLE [dbo].[PastUser] (
+    [Username]    VARCHAR (30) NOT NULL,
+    [ApartmentId] INT          NOT NULL,
+    [Password]    VARCHAR (30) NOT NULL,
+    [IsBm]        BIT          NOT NULL,
+    [FirstName]   VARCHAR (30) NULL,
+    [LastName]    VARCHAR (30) NULL,
+    [BirthDate]   DATE         NULL,
+    [Phone]       VARCHAR (20) NULL,
+    [Email]       VARCHAR (50) NULL,
+    [Picture]     VARCHAR (50) NULL,
+    [MoveInDate]  DATE         NULL,
+    [MoveOutDate] DATE         NULL,
+    PRIMARY KEY CLUSTERED ([Username] ASC),
+    CONSTRAINT [FK_PastUser_ToApartment] FOREIGN KEY ([ApartmentId]) REFERENCES [dbo].[Apartment] ([ApartmentId])
+);
+
+CREATE TABLE [dbo].[Resident] (
+    [ResidentId]  INT           IDENTITY (0, 1) NOT NULL,
+    [ApartmentId] INT           NOT NULL,
+    [FirstName]   VARCHAR (30)  NULL,
+    [LastName]    VARCHAR (30)  NULL,
+    [BirthDate]   DATE          NULL,
+    [Phone]       VARCHAR (20)  NULL,
+    [Email]       VARCHAR (50)  NULL,
+    [Picture]     VARCHAR (MAX) NULL,
+    PRIMARY KEY CLUSTERED ([ResidentId] ASC),
+    CONSTRAINT [FK_Resident_ToApartment] FOREIGN KEY ([ApartmentId]) REFERENCES [dbo].[Apartment] ([ApartmentId])
+);
+
+CREATE TABLE [dbo].[User] (
+    [Username]    VARCHAR (30)  NOT NULL,
+    [ApartmentId] INT           NOT NULL,
+    [Password]    VARCHAR (30)  NOT NULL,
+    [IsBm]        BIT           NOT NULL,
+    [FirstName]   VARCHAR (30)  NULL,
+    [LastName]    VARCHAR (30)  NULL,
+    [BirthDate]   DATE          NULL,
+    [Phone]       VARCHAR (20)  NULL,
+    [Email]       VARCHAR (50)  NULL,
+    [Picture]     VARCHAR (MAX) NULL,
+    [MoveInDate]  DATE          NULL,
+    [MoveOutDate] DATE          NULL,
+    PRIMARY KEY CLUSTERED ([Username] ASC),
+    CONSTRAINT [FK_User_ToApartment] FOREIGN KEY ([ApartmentId]) REFERENCES [dbo].[Apartment] ([ApartmentId])
+);
+