diff options
14 files changed, 294 insertions, 226 deletions
diff --git a/ApartmentManager/ApartmentManager/Model/ApartmentChange.cs b/ApartmentManager/ApartmentManager/Model/Change.cs index 608db89..466d85c 100644 --- a/ApartmentManager/ApartmentManager/Model/ApartmentChange.cs +++ b/ApartmentManager/ApartmentManager/Model/Change.cs @@ -7,7 +7,7 @@ using System.Runtime.CompilerServices; namespace ApartmentManager { - public class ApartmentChange : INotifyPropertyChanged + public class Change : INotifyPropertyChanged { public int ChangeId { get; set; } public int ApartmentId { get; set; } diff --git a/ApartmentManager/HousingWebApi/Controllers/ApartmentChangesController.cs b/ApartmentManager/HousingWebApi/Controllers/ApartmentChangesController.cs deleted file mode 100644 index eadd910..0000000 --- a/ApartmentManager/HousingWebApi/Controllers/ApartmentChangesController.cs +++ /dev/null @@ -1,141 +0,0 @@ -using System; -using System.Collections.Generic; -using System.Data; -using System.Data.Entity; -using System.Data.Entity.Infrastructure; -using System.Linq; -using System.Net; -using System.Net.Http; -using System.Web.Http; -using System.Web.Http.Description; -using HousingWebApi; - -namespace HousingWebApi.Controllers -{ - public class ApartmentChangesController : ApiController - { - private DataModel db = new DataModel(); - - // GET: api/ApartmentChanges - public IQueryable<ApartmentChange> GetApartmentChanges() - { - return db.ApartmentChanges; - } - [Route("api/ApartmentChangesByid/{id}")] - public IQueryable<ApartmentChange> GetApartmentChangesByid(int id) - { - var changetlist = from change in db.ApartmentChanges - where (change.ApartmentId == id) - orderby change.ChangeId descending - select change; - return changetlist; - } - // GET: api/ApartmentChanges/5 - [ResponseType(typeof(ApartmentChange))] - public IHttpActionResult GetApartmentChange(int id) - { - ApartmentChange apartmentChange = db.ApartmentChanges.Find(id); - if (apartmentChange == null) - { - return NotFound(); - } - - return Ok(apartmentChange); - } - - // PUT: api/ApartmentChanges/5 - [ResponseType(typeof(void))] - public IHttpActionResult PutApartmentChange(int id, ApartmentChange apartmentChange) - { - if (!ModelState.IsValid) - { - return BadRequest(ModelState); - } - - if (id != apartmentChange.ChangeId) - { - return BadRequest(); - } - - db.Entry(apartmentChange).State = EntityState.Modified; - - try - { - db.SaveChanges(); - } - catch (DbUpdateConcurrencyException) - { - if (!ApartmentChangeExists(id)) - { - return NotFound(); - } - else - { - throw; - } - } - - return StatusCode(HttpStatusCode.NoContent); - } - - // POST: api/ApartmentChanges - [ResponseType(typeof(ApartmentChange))] - public IHttpActionResult PostApartmentChange(ApartmentChange apartmentChange) - { - if (!ModelState.IsValid) - { - return BadRequest(ModelState); - } - - db.ApartmentChanges.Add(apartmentChange); - - try - { - db.SaveChanges(); - } - catch (DbUpdateException) - { - if (ApartmentChangeExists(apartmentChange.ChangeId)) - { - return Conflict(); - } - else - { - throw; - } - } - - return CreatedAtRoute("DefaultApi", new { id = apartmentChange.ChangeId }, apartmentChange); - } - - // DELETE: api/ApartmentChanges/5 - [ResponseType(typeof(ApartmentChange))] - public IHttpActionResult DeleteApartmentChange(int id) - { - ApartmentChange apartmentChange = db.ApartmentChanges.Find(id); - if (apartmentChange == null) - { - return NotFound(); - } - - db.ApartmentChanges.Remove(apartmentChange); - db.SaveChanges(); - - return Ok(apartmentChange); - } - - protected override void Dispose(bool disposing) - { - if (disposing) - { - db.Dispose(); - } - base.Dispose(disposing); - } - - private bool ApartmentChangeExists(int id) - { - return db.ApartmentChanges.Count(e => e.ChangeId == id) > 0; - } - } -}
\ No newline at end of file diff --git a/ApartmentManager/HousingWebApi/Controllers/ChangeCommentsController.cs b/ApartmentManager/HousingWebApi/Controllers/ChangeCommentsController.cs index 6026938..ef90861 100644 --- a/ApartmentManager/HousingWebApi/Controllers/ChangeCommentsController.cs +++ b/ApartmentManager/HousingWebApi/Controllers/ChangeCommentsController.cs @@ -16,18 +16,6 @@ namespace HousingWebApi.Controllers { private DataModel db = new DataModel(); - //GET: api/ChangeCommentsById - [Route("api/ChangeCommentsById/{id}")] - public IQueryable<ChangeComment> GetChangeCommentsById(int id) - { - var commentsList = from changeComment in db.ChangeComments - where (changeComment.ChangeId == id) - orderby changeComment.CommentId descending - - select changeComment; - return commentsList; - } - // GET: api/ChangeComments public IQueryable<ChangeComment> GetChangeComments() { @@ -47,6 +35,18 @@ namespace HousingWebApi.Controllers return Ok(changeComment); } + //GET: api/CommentsByChangeId + [Route("api/CommentsByChangeId/{id}")] + public IQueryable<ChangeComment> GetCommentsByChangeId(int id) + { + var commentsList = from changeComment in db.ChangeComments + where (changeComment.ChangeId == id) + orderby changeComment.CommentId descending + + select changeComment; + return commentsList; + } + // PUT: api/ChangeComments/5 [ResponseType(typeof(void))] public IHttpActionResult PutChangeComment(int id, ChangeComment changeComment) @@ -92,22 +92,7 @@ namespace HousingWebApi.Controllers } db.ChangeComments.Add(changeComment); - - try - { - db.SaveChanges(); - } - catch (DbUpdateException) - { - if (ChangeCommentExists(changeComment.CommentId)) - { - return Conflict(); - } - else - { - throw; - } - } + db.SaveChanges(); return CreatedAtRoute("DefaultApi", new { id = changeComment.CommentId }, changeComment); } diff --git a/ApartmentManager/HousingWebApi/Controllers/ChangeDocumentsController.cs b/ApartmentManager/HousingWebApi/Controllers/ChangeDocumentsController.cs index 0ffaec0..ae5ff81 100644 --- a/ApartmentManager/HousingWebApi/Controllers/ChangeDocumentsController.cs +++ b/ApartmentManager/HousingWebApi/Controllers/ChangeDocumentsController.cs @@ -16,16 +16,6 @@ namespace HousingWebApi.Controllers { private DataModel db = new DataModel(); - //GET: api/ChangeDocumentsById - [Route("api/ChangeDocumentsById/{id}")] - public IQueryable<ChangeDocument> GetChangeDocumentsById(int id) - { - var documentsList = from changeDocument in db.ChangeDocuments - where (changeDocument.ChangeId == id) - select changeDocument; - return documentsList; - } - // GET: api/ChangeDocuments public IQueryable<ChangeDocument> GetChangeDocuments() { @@ -45,6 +35,17 @@ namespace HousingWebApi.Controllers return Ok(changeDocument); } + //GET: api/DocumentsByChangeId + [Route("api/DocumentsByChangeId/{id}")] + public IQueryable<ChangeDocument> GetDocumentsByChangeId(int id) + { + var documentsList = from changeDocument in db.ChangeDocuments + where (changeDocument.ChangeId == id) + select changeDocument; + return documentsList; + } + + // PUT: api/ChangeDocuments/5 [ResponseType(typeof(void))] public IHttpActionResult PutChangeDocument(int id, ChangeDocument changeDocument) @@ -90,22 +91,7 @@ namespace HousingWebApi.Controllers } db.ChangeDocuments.Add(changeDocument); - - try - { - db.SaveChanges(); - } - catch (DbUpdateException) - { - if (ChangeDocumentExists(changeDocument.DocumentId)) - { - return Conflict(); - } - else - { - throw; - } - } + db.SaveChanges(); return CreatedAtRoute("DefaultApi", new { id = changeDocument.DocumentId }, changeDocument); } diff --git a/ApartmentManager/HousingWebApi/Controllers/ChangesController.cs b/ApartmentManager/HousingWebApi/Controllers/ChangesController.cs new file mode 100644 index 0000000..55096b7 --- /dev/null +++ b/ApartmentManager/HousingWebApi/Controllers/ChangesController.cs @@ -0,0 +1,128 @@ +using System; +using System.Collections.Generic; +using System.Data; +using System.Data.Entity; +using System.Data.Entity.Infrastructure; +using System.Linq; +using System.Net; +using System.Net.Http; +using System.Web.Http; +using System.Web.Http.Description; +using HousingWebApi; + +namespace HousingWebApi.Controllers +{ + public class ChangesController : ApiController + { + private DataModel db = new DataModel(); + + // GET: api/Changes + public IQueryable<Change> GetChanges() + { + return db.Changes; + } + + // GET: api/Changes/5 + [ResponseType(typeof(Change))] + public IHttpActionResult GetChange(int id) + { + Change change = db.Changes.Find(id); + if (change == null) + { + return NotFound(); + } + + return Ok(change); + } + + [Route("api/ChangesByApartmentId/{id}")] + public IQueryable<Change> GetChangesByApartmentId(int id) + { + var changesList = from change in db.Changes + where (change.ApartmentId == id) + orderby change.ChangeId descending + select change; + return changesList; + } + + // PUT: api/Changes/5 + [ResponseType(typeof(void))] + public IHttpActionResult PutChange(int id, Change change) + { + if (!ModelState.IsValid) + { + return BadRequest(ModelState); + } + + if (id != change.ChangeId) + { + return BadRequest(); + } + + db.Entry(change).State = EntityState.Modified; + + try + { + db.SaveChanges(); + } + catch (DbUpdateConcurrencyException) + { + if (!ChangeExists(id)) + { + return NotFound(); + } + else + { + throw; + } + } + + return StatusCode(HttpStatusCode.NoContent); + } + + // POST: api/Changes + [ResponseType(typeof(Change))] + public IHttpActionResult PostChange(Change change) + { + if (!ModelState.IsValid) + { + return BadRequest(ModelState); + } + + db.Changes.Add(change); + db.SaveChanges(); + + return CreatedAtRoute("DefaultApi", new { id = change.ChangeId }, change); + } + + // DELETE: api/Changes/5 + [ResponseType(typeof(Change))] + public IHttpActionResult DeleteChange(int id) + { + Change change = db.Changes.Find(id); + if (change == null) + { + return NotFound(); + } + + db.Changes.Remove(change); + db.SaveChanges(); + + return Ok(change); + } + + protected override void Dispose(bool disposing) + { + if (disposing) + { + db.Dispose(); + } + base.Dispose(disposing); + } + + private bool ChangeExists(int id) + { + return db.Changes.Count(e => e.ChangeId == id) > 0; + } + } +}
\ No newline at end of file diff --git a/ApartmentManager/HousingWebApi/DataModel.cs b/ApartmentManager/HousingWebApi/DataModel.cs index aa2721f..2e60842 100644 --- a/ApartmentManager/HousingWebApi/DataModel.cs +++ b/ApartmentManager/HousingWebApi/DataModel.cs @@ -14,7 +14,7 @@ namespace HousingWebApi } public virtual DbSet<Apartment> Apartments { get; set; } - public virtual DbSet<ApartmentChange> ApartmentChanges { get; set; } + public virtual DbSet<Change> Changes { get; set; } public virtual DbSet<ChangeComment> ChangeComments { get; set; } public virtual DbSet<ChangeDocument> ChangeDocuments { get; set; } public virtual DbSet<Defect> Defects { get; set; } @@ -36,7 +36,7 @@ namespace HousingWebApi .IsUnicode(false); modelBuilder.Entity<Apartment>() - .HasMany(e => e.ApartmentChanges) + .HasMany(e => e.Changes) .WithRequired(e => e.Apartment) .WillCascadeOnDelete(false); @@ -60,28 +60,18 @@ namespace HousingWebApi .WithRequired(e => e.Apartment) .WillCascadeOnDelete(false); - modelBuilder.Entity<ApartmentChange>() + modelBuilder.Entity<Change>() .Property(e => e.Name) .IsUnicode(false); - modelBuilder.Entity<ApartmentChange>() + modelBuilder.Entity<Change>() .Property(e => e.Description) .IsUnicode(false); - modelBuilder.Entity<ApartmentChange>() + modelBuilder.Entity<Change>() .Property(e => e.Status) .IsUnicode(false); - modelBuilder.Entity<ApartmentChange>() - .HasMany(e => e.ChangeComments) - .WithRequired(e => e.ApartmentChange) - .WillCascadeOnDelete(false); - - modelBuilder.Entity<ApartmentChange>() - .HasMany(e => e.ChangeDocuments) - .WithRequired(e => e.ApartmentChange) - .WillCascadeOnDelete(false); - modelBuilder.Entity<ChangeComment>() .Property(e => e.Comment) .IsUnicode(false); @@ -209,6 +199,10 @@ namespace HousingWebApi modelBuilder.Entity<ApartmentResident>() .Property(e => e.Email) .IsUnicode(false); + + modelBuilder.Entity<ApartmentResident>() + .Property(e => e.Picture) + .IsUnicode(false); } } } diff --git a/ApartmentManager/HousingWebApi/HousingWebApi.csproj b/ApartmentManager/HousingWebApi/HousingWebApi.csproj index 779dd08..b94fb4b 100644 --- a/ApartmentManager/HousingWebApi/HousingWebApi.csproj +++ b/ApartmentManager/HousingWebApi/HousingWebApi.csproj @@ -151,9 +151,13 @@ </Reference>
</ItemGroup>
<ItemGroup>
+ <Compile Include="Controllers\ApartmentsController.cs" />
+ <Compile Include="Controllers\ChangeCommentsController.cs" />
+ <Compile Include="Controllers\ChangeDocumentsController.cs" />
+ <Compile Include="Controllers\ChangesController.cs" />
<Compile Include="Models\Apartment.cs" />
- <Compile Include="Models\ApartmentChange.cs" />
<Compile Include="Models\ApartmentResident.cs" />
+ <Compile Include="Models\Change.cs" />
<Compile Include="Models\ChangeComment.cs" />
<Compile Include="Models\ChangeDocument.cs" />
<Compile Include="Controllers\DefectPicturesController.cs" />
@@ -189,10 +193,6 @@ <Compile Include="Areas\HelpPage\SampleGeneration\SampleDirection.cs" />
<Compile Include="Areas\HelpPage\SampleGeneration\TextSample.cs" />
<Compile Include="Areas\HelpPage\XmlDocumentationProvider.cs" />
- <Compile Include="Controllers\ApartmentChangesController.cs" />
- <Compile Include="Controllers\ApartmentsController.cs" />
- <Compile Include="Controllers\ChangeCommentsController.cs" />
- <Compile Include="Controllers\ChangeDocumentsController.cs" />
<Compile Include="Controllers\DefectCommentsController.cs" />
<Compile Include="Controllers\DefectsController.cs" />
<Compile Include="Controllers\HomeController.cs" />
diff --git a/ApartmentManager/HousingWebApi/Models/Apartment.cs b/ApartmentManager/HousingWebApi/Models/Apartment.cs index 9e703bd..c114d09 100644 --- a/ApartmentManager/HousingWebApi/Models/Apartment.cs +++ b/ApartmentManager/HousingWebApi/Models/Apartment.cs @@ -12,7 +12,7 @@ namespace HousingWebApi [System.Diagnostics.CodeAnalysis.SuppressMessage("Microsoft.Usage", "CA2214:DoNotCallOverridableMethodsInConstructors")] public Apartment() { - ApartmentChanges = new HashSet<ApartmentChange>(); + Changes = new HashSet<Change>(); Defects = new HashSet<Defect>(); PastUsers = new HashSet<PastUser>(); Residents = new HashSet<Resident>(); @@ -36,7 +36,7 @@ namespace HousingWebApi public string PlanPicture { get; set; } [System.Diagnostics.CodeAnalysis.SuppressMessage("Microsoft.Usage", "CA2227:CollectionPropertiesShouldBeReadOnly")] - public virtual ICollection<ApartmentChange> ApartmentChanges { get; set; } + public virtual ICollection<Change> Changes { get; set; } [System.Diagnostics.CodeAnalysis.SuppressMessage("Microsoft.Usage", "CA2227:CollectionPropertiesShouldBeReadOnly")] public virtual ICollection<Defect> Defects { get; set; } diff --git a/ApartmentManager/HousingWebApi/Models/ApartmentResident.cs b/ApartmentManager/HousingWebApi/Models/ApartmentResident.cs index 9936a5e..e4d3fed 100644 --- a/ApartmentManager/HousingWebApi/Models/ApartmentResident.cs +++ b/ApartmentManager/HousingWebApi/Models/ApartmentResident.cs @@ -27,6 +27,6 @@ namespace HousingWebApi [StringLength(50)] public string Email { get; set; } - public byte[] Picture { get; set; } + public string Picture { get; set; } } } diff --git a/ApartmentManager/HousingWebApi/Models/ApartmentChange.cs b/ApartmentManager/HousingWebApi/Models/Change.cs index a3308f3..de7eef7 100644 --- a/ApartmentManager/HousingWebApi/Models/ApartmentChange.cs +++ b/ApartmentManager/HousingWebApi/Models/Change.cs @@ -6,17 +6,16 @@ namespace HousingWebApi using System.ComponentModel.DataAnnotations.Schema; using System.Data.Entity.Spatial; - [Table("ApartmentChange")] - public partial class ApartmentChange + [Table("Change")] + public partial class Change { [System.Diagnostics.CodeAnalysis.SuppressMessage("Microsoft.Usage", "CA2214:DoNotCallOverridableMethodsInConstructors")] - public ApartmentChange() + public Change() { ChangeComments = new HashSet<ChangeComment>(); ChangeDocuments = new HashSet<ChangeDocument>(); } - [Key] public int ChangeId { get; set; } public int ApartmentId { get; set; } diff --git a/ApartmentManager/HousingWebApi/Models/ChangeComment.cs b/ApartmentManager/HousingWebApi/Models/ChangeComment.cs index 954d348..83b205f 100644 --- a/ApartmentManager/HousingWebApi/Models/ChangeComment.cs +++ b/ApartmentManager/HousingWebApi/Models/ChangeComment.cs @@ -21,6 +21,6 @@ namespace HousingWebApi public DateTime? Date { get; set; } - public virtual ApartmentChange ApartmentChange { get; set; } + public virtual Change Change { get; set; } } } diff --git a/ApartmentManager/HousingWebApi/Models/ChangeDocument.cs b/ApartmentManager/HousingWebApi/Models/ChangeDocument.cs index 320686f..31b6437 100644 --- a/ApartmentManager/HousingWebApi/Models/ChangeDocument.cs +++ b/ApartmentManager/HousingWebApi/Models/ChangeDocument.cs @@ -17,6 +17,6 @@ namespace HousingWebApi [StringLength(50)] public string Document { get; set; } - public virtual ApartmentChange ApartmentChange { get; set; } + public virtual Change Change { get; set; } } } diff --git a/ApartmentManager/HousingWebApi/Web.config b/ApartmentManager/HousingWebApi/Web.config index d4ce313..e610a4b 100644 --- a/ApartmentManager/HousingWebApi/Web.config +++ b/ApartmentManager/HousingWebApi/Web.config @@ -80,5 +80,6 @@ </providers> </entityFramework> <connectionStrings> - <add name="DataModel" connectionString="data source=housingdb.database.windows.net;initial catalog=housingdb;persist security info=True;user id=deltaadmin;password=Delta123!;MultipleActiveResultSets=True;App=EntityFramework" providerName="System.Data.SqlClient" /></connectionStrings> + <add name="DataModel" connectionString="data source=(localdb)\MSSQLLocalDB;initial catalog=housingdb;integrated security=True;pooling=False;MultipleActiveResultSets=True;App=EntityFramework" providerName="System.Data.SqlClient" /> + </connectionStrings> </configuration>
\ No newline at end of file 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]) +); + |