Проектирование баз данных: «Информационная система гостиницы»

wikipedia.org/wiki/Microsoft_SQL_Server 3-Tier System Architecture, [Электронныйресурс] URL:http://en.wikipedia.org/wiki/Multitier_architecture Introduction to ADO.NET, [Электронныйресурс] URL: http://en.wikipedia.org/wiki/ADO.NETIntroduction to .NET Architecture, [Электронныйресурс] URL: http://www.devtopics.com/what-is-net/ Common Language Infrastructure Definition, [Электронныйресурс] URL: http://en.wikipedia.org/wiki/Common_Language_Infrastructure 3-Tier Architecture [Электронныйресурс] URL: http://www.c-sharpcorner.com/Рейтинг СУБД по версии TOPDB- [Электронный ресурс] URL:https://pypl.github.io/DB.html Константин Таранов о SQL Server [Электронный ресурс] URL: https://habr.com/ru/company/pgdayrussia/blog/329842/Рейтинг СУБД по версии компании Flourish [Электронный ресурс] URL: https://public.flourish.studio/visualisation/5446616/?utm_source=showcase&utm_campaign=visualisation/5446616ПРИЛОЖЕНИЕ АХарактеристики программно-аппаратного обеспеченияОперационная система: ОС Windows 10 и выше.NETFramework 6.0SQLServer 2019ивышеПРИЛОЖЕНИЕ БРуководство системного программистаДля развертывания информационной системы необходимо выполнить следующие действия:1.1. Используя файл Hotel.bak, создать копию базы данных в среде ManagementStudio – для версии SQL Server2019.Databases -> RestoreDatabase.1.2. Используя файл Scripts.sql, создать базу данных, таблицы, механизмы БД, а также выполнить заполнение созданных таблиц – для других версий SQL Server.2. Скопировать папку Hotel с клиентским приложением на локальный компьютер.3. В файл App.config добавить имя сервера, к которому будет осуществляться подключение. Нужное имя сервера должно быть установлено в качестве значения атрибута «DataSource».4. Запустить приложение, используя исполняемый файл Hotel.exe.ПРИЛОЖЕНИЕ В Листинг SQL-скриптаСоздание базы данных и таблицUSE [master]CREATEDATABASE [Hotel] CONTAINMENT =NONEONPRIMARYLOGONGOUSE [Hotel]GOSETANSI_NULLSONGOSETQUOTED_IDENTIFIERONGOCREATETABLE [dbo].[Admins]([Id] [uniqueidentifier] NOTNULL,[Login] [nvarchar](max)NULL,[Password] [nvarchar](max)NULL,CONSTRAINT [PK_dbo.Admins] PRIMARYKEYCLUSTERED([Id] ASC)WITH(PAD_INDEX=OFF,STATISTICS_NORECOMPUTE=OFF,IGNORE_DUP_KEY=OFF,ALLOW_ROW_LOCKS=ON,ALLOW_PAGE_LOCKS=ON,OPTIMIZE_FOR_SEQUENTIAL_KEY=OFF)ON [PRIMARY])ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]GOSETANSI_NULLSONGOSETQUOTED_IDENTIFIERONGOCREATETABLE [dbo].[Bookings]([Id] [uniqueidentifier] NOTNULL,[StartDate] [datetime] NOTNULL,[EndDate] [datetime] NOTNULL ,[Room_Id] [uniqueidentifier] NULL,CONSTRAINT [PK_dbo.Bookings] PRIMARYKEYCLUSTERED([Id] ASC)WITH(PAD_INDEX=OFF,STATISTICS_NORECOMPUTE=OFF,IGNORE_DUP_KEY=OFF,ALLOW_ROW_LOCKS=ON,ALLOW_PAGE_LOCKS=ON,OPTIMIZE_FOR_SEQUENTIAL_KEY=OFF)ON [PRIMARY])ON [PRIMARY]GOSETANSI_NULLSONGOSETQUOTED_IDENTIFIERONGOCREATETABLE [dbo].[GuestBookings]([Guest_Id] [uniqueidentifier] NOTNULL,[Booking_Id] [uniqueidentifier] NOTNULL,CONSTRAINT [PK_dbo.GuestBookings] PRIMARYKEYCLUSTERED([Guest_Id] ASC,[Booking_Id] ASC)WITH(PAD_INDEX=OFF,STATISTICS_NORECOMPUTE=OFF,IGNORE_DUP_KEY=OFF,ALLOW_ROW_LOCKS=ON,ALLOW_PAGE_LOCKS=ON,OPTIMIZE_FOR_SEQUENTIAL_KEY=OFF)ON [PRIMARY])ON [PRIMARY]GOSETANSI_NULLSONGOSETQUOTED_IDENTIFIERONGOCREATETABLE [dbo].[Guests]([Id] [uniqueidentifier] NOTNULL,[Name] [nvarchar](max)NULL,[Passport] [nvarchar](max)NULL,[BithrDate] [datetime] NOTNULL,CONSTRAINT [PK_dbo.Guests] PRIMARYKEYCLUSTERED([Id] ASC)WITH(PAD_INDEX=OFF,STATISTICS_NORECOMPUTE=OFF,IGNORE_DUP_KEY=OFF,ALLOW_ROW_LOCKS=ON,ALLOW_PAGE_LOCKS=ON,OPTIMIZE_FOR_SEQUENTIAL_KEY=OFF)ON [PRIMARY])ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]GOSETANSI_NULLSONGOSETQUOTED_IDENTIFIERONGOCREATETABLE [dbo].[Rooms]([Id] [uniqueidentifier] NOTNULL,[RoomNumber] [int] NOTNULL,[CostPerNight] [float] NOTNULL,[RoomType_Id] [uniqueidentifier] NULL,CONSTRAINT [PK_dbo.Rooms] PRIMARYKEYCLUSTERED([Id] ASC)WITH(PAD_INDEX=OFF,STATISTICS_NORECOMPUTE=OFF,IGNORE_DUP_KEY=OFF,ALLOW_ROW_LOCKS=ON,ALLOW_PAGE_LOCKS=ON,OPTIMIZE_FOR_SEQUENTIAL_KEY=OFF)ON [PRIMARY])ON [PRIMARY]GOSETANSI_NULLSONGOSETQUOTED_IDENTIFIERONGOCREATETABLE [dbo].[RoomTypes]([Id] [uniqueidentifier] NOTNULL,[Name] [nvarchar](max)NULL,CONSTRAINT [PK_dbo.RoomTypes] PRIMARYKEYCLUSTERED([Id] ASC)WITH(PAD_INDEX=OFF,STATISTICS_NORECOMPUTE=OFF,IGNORE_DUP_KEY=OFF,ALLOW_ROW_LOCKS=ON,ALLOW_PAGE_LOCKS=ON,OPTIMIZE_FOR_SEQUENTIAL_KEY=OFF)ON [PRIMARY])ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]GOALTERTABLE [dbo].[Bookings] WITHCHECKADDCONSTRAINT [FK_dbo.Bookings_dbo.Rooms_Room_Id] FOREIGNKEY([Room_Id])REFERENCES [dbo].[Rooms]([Id])GOALTERTABLE [dbo].[Bookings] CHECKCONSTRAINT [FK_dbo.Bookings_dbo.Rooms_Room_Id]GOALTERTABLE [dbo].[GuestBookings] WITHCHECKADDCONSTRAINT [FK_dbo.GuestBookings_dbo.Bookings_Booking_Id] FOREIGNKEY([Booking_Id])REFERENCES [dbo].[Bookings]([Id])ONDELETECASCADEGOALTERTABLE [dbo].[GuestBookings] CHECKCONSTRAINT [FK_dbo.GuestBookings_dbo.Bookings_Booking_Id]GOALTERTABLE [dbo].[GuestBookings] WITHCHECKADDCONSTRAINT [FK_dbo.GuestBookings_dbo.Guests_Guest_Id] FOREIGNKEY([Guest_Id])REFERENCES [dbo].[Guests]([Id])ONDELETECASCADEGOALTERTABLE [dbo].[GuestBookings] CHECKCONSTRAINT [FK_dbo.GuestBookings_dbo.Guests_Guest_Id]GOALTERTABLE [dbo].[Rooms] WITHCHECKADDCONSTRAINT [FK_dbo.Rooms_dbo.RoomTypes_RoomType_Id] FOREIGNKEY([RoomType_Id])REFERENCES [dbo].[RoomTypes]([Id])GOALTERTABLE [dbo].[Rooms] CHECKCONSTRAINT [FK_dbo.Rooms_dbo.RoomTypes_RoomType_Id]GOСозданиепроверочныхтриггеровCREATETRIGGERCreatePrimaryKeyONdbo.AdminsINSTEADOFINSERTASBEGINdeclare @id uniqueidentifierset @id =newid()insertintodbo.Adminsselect @id, [login], [Password] from insertedSETNOCOUNTON;ENDGOCREATETRIGGERCreateRoomTyoePrimaryKeyONdbo.RoomTypesINSTEADOFINSERTASBEGINdeclare @id uniqueidentifierset @id =newid()insertintodbo.RoomTypesselect @id, [Name] from insertedSETNOCOUNTON;ENDGOХранимыепроцедурыCREATEPROCEDURE [dbo].[CreateBooking]-- Add the parameters for the stored procedure here@guestIduniqueidentifier,@startDatedatetime,@endDatedatetime,@roomIduniqueidentifierASBEGINdeclare @id uniqueidentifierset @id =newid()insertintodbo.Bookingsvalues (@id, @startDate, @endDate, @roomId)insertintodbo.GuestBookingsvalues (@id, @guestId)ENDПРИЛОЖЕНИЕ Г Листинг приложенияДоменданныхpublicclassAdmin {publicGuid Id { get; set; }publicstring Login { get; set; }publicstring Password { get; set; } }publicclassBooking {publicGuid Id { get; set; }publicDateTimeStartDate { get; set; }publicDateTimeEndDate { get; set; }publicvirtualICollection Guests {get;set;}publicvirtual Room Room { get; set; } }publicclassGuest {publicGuid Id { get; set; } [DisplayName("ФИО")]publicstring Name { get; set; }publicstring Passport { get; set; }publicDateTimeBithrDate { get; set; }publicvirtualICollection Bookings { get; set; }publicoverridestringToString() {return Name; } }publicclassRoom {publicGuid Id { get; set; } [DisplayName("Типномера")]publicvirtualRoomTypeRoomType { get; set; } [DisplayName("номер")]publicintRoomNumber {get;set;} [DisplayName("ценазаночь")]publicdoubleCostPerNight { get; set; }}publicclassRoomType{publicGuid Id { get; set; } [DisplayName("Типномера")]publicstring Name { get; set; }publicoverridestringToString() {return Name ;}}publicclassHotelDbContext : DbContext {publicDbSet Rooms { get; set; }publicDbSet RoomTypes { get; set; }publicDbSet Bookings { get; set; }publicDbSet Guests { get; set; }publicDbSet Admins { get; set; }}СервисныйслойusingHotel.Domain;usingSystem.Linq;namespaceHotelService{publicclassDataService {publicbool Login(string login, string pass) {using (vardbContext = newHotelDbContext()) {var password = pass.GetHashCode().ToString();returndbContext.Admins.Any(x => x.Login == login && x.Password == password); } }publicIList GetBookings() {using (vardbContext = newHotelDbContext()) {returndbContext.Bookings.Include("Guests").Include("Room").ToList(); } }publicIList GetRooms() {using (vardbContext = newHotelDbContext()) {returndbContext.Rooms.Include("RoomType").ToList();} } }}СлойпользовательскогоинтерфейсаЭкран входа в системуpublicpartialclassWelcomeForm : Form {privateDataService _service;publicWelcomeForm() {InitializeComponent(); _service = newDataService(); _service.InitDatabase();passwordTbx.PasswordChar = '*'; }privatevoidEnterBtn_Click(object sender, EventArgs e) {if (aasGuestchBx.Checked)newBookingForm().Show();else {if (_service.Login(loginTbx.Text, passwordTbx.Text)) {newAdminBookingForm().Show(); }else{ MessageBox.Show("пользоватеьстакимиданныминенайден!");loginTbx.Clear(); passwordTbx.Clear(); } } }privatevoidaasGuestchBx_CheckedChanged(object sender, EventArgs e) {loginTbx.Enabled= !aasGuestchBx.Checked;passwordTbx.Enabled= !aasGuestchBx.Checked;} }}ЭкранадминистратораpublicpartialclassAdminBookingForm : Form {privateDataService _service ;publicAdminBookingForm() {InitializeComponent(); _service = newDataService();var list = _service.GetBookings();var source = newBindingList(list);bookingDataGridView.Enabled = false;bookingDataGridView.DataSource = source;this.bookingDataGridView.Columns["Id"].Visible = false;this.bookingDataGridView.Columns["Guests"].Visible = false;var rooms = _service.GetRooms();varroomSource = newBindingList(rooms);roomridView.DataSource = roomSource;this.roomridView.Columns["Id"].Visible = false; }privatevoidsearchBtn_Click(object sender, EventArgs e) {var list = _service.GetBookings().Where(x => x.StartDate >= startBookingDateTimePicker.Value && x.StartDate <= EndBookingDateTimePicker.Value).ToList();var source = newBindingList(list);bookingDataGridView.DataSource = source; }privatevoidsearchFreeRoomNumber_Click(object sender, EventArgs e) {var rooms = _service.GetRooms();varroomIds = rooms.Select(x => x.Id);var list = _service.GetBookings().Where(x => x.EndDate <= startFreeRoomDateTimePicker.Value || x.StartDate >= freeRoomEnddateTimePicker.Value).Select(x=>x.Room.Id).ToList();var source = rooms.Where(x=>!list.Contains(x.Id)).ToList();roomridView.DataSource = source; } }}