Project Description Solution

Introduction



In this project, you will model and build a hotel database management system. This system is to track information about different hotels, such as the rooms they own, the maintenance of those rooms, the managers they employ, the bookings their customers make, and information about the customers that use the hotel services.




The project comprises three phases. In Phase 1, you will perform requirement analysis using the ER-model, given a set of requirements for your database. Please note that you are being given only a set of requirements, not the entities or actual set of attributes that go with each entity. In Phase 2 of this project, you will design a logical model of your database using the ER-model. In Phase 3, you will implement the database, and exercise your design using queries.




You will work on Phases 1 and 2 alone. Phase 3 is to be completed with a partner. Submit all your files via iLearn on the due date for each phase. Each submission should be a single zip file. Make sure that everything is included in your submission and it can be uncompressed without any errors.




Phase 1: ER Design




From the description given, first identify the entity sets in your model, and the attributes for each entity set, as required by the specifications and queries. Next, produce an ER-diagram that will serve as the foundation for the rest of the design. You can create this diagram using your favorite diagram editing software. Your model should use only the basic ER model features, and include entities, attributes, and relationships. Ensure that you capture all participation and key constraints. Also, make sure to include additional documentation describing the assumptions that you made during the design process.




You can make reasonable assumptions during your design, as long as:




You state them clearly in the documentation for this phase.



They do not contradict the system requirements analysis we provide.



Phase 2: Relational Schema Design




In this phase, we will provide you with an ER-diagram that is a solution to Phase 1 (so that the whole class will proceed with the same design). This final ER-diagram will be the starting point for the second phase, which involves the creation of the relational schema.




Your task in this phase will be to translate the provided ER design to a PostgreSQL relational database schema. The database schema will be in a form of a single executable SQL script (*.sql file with SQL statements). You must submit this SQL script via iLearn on the due date. The SQL script should include the necessary drop statements at the beginning so it is easy to test. Check how the drop statement works and consider using the IF EXISTS statements where necessary.




In this phase, we will evaluate you for the correctness and completeness of your relational schema. You may find some constraints in the model and/or system requirement analysis that are not possible to represent or enforce in the relational schema. You may specify all these issues in the documentation for this phase. Your submission should be a single compressed file, containing all the aforementioned files.



















Phase 3: Implementation




You will work in pairs on this part of the project. Your tasks in this phase will be:




Develop a client application using the Java Database Connector (JDBC) for psql.



Use the client application to support specific functionality and queries for your online booking system.



In this phase, we will provide you with a create.sql script that recreates the relational schema of Phase 2. You will use this schema to test and demo your application to us. We will also give you a collection of .csv files containing data that are compatible with the provided relational schema. You will have to create your own .sql scripts to insert the data from the given .csv files into the database.




Finally, we will give you some skeleton code for the client application. The code will be in Java and will contain some basic functionality that will help you to communicate with the database and issue various .sql statements.




This phase of the project is challenging. Please start early and allocate at least 25 hours per person to get it finished. Make sure to consider all possible scenarios for the client application and try to handle any exceptions that arise during the regular operation of your application.

For this phase you will be evaluated based on the system requirements. Your GUI and source code will also be taken into consideration in your final evaluation. Groups that implement systems with user-friendly interfaces, extra functionalities, error handling (i.e. invalid values, wrong operations, meaningful messages) will receive an extra credit. A final report about your system along with its source code must be submitted before the due date. Please submit the documentation and final source code on iLearn.
















1.1 Grading




Your contribution to this project will be graded based on the following characteristics:




Phase 1 (30%)



Conceptual Design (ER Diagram)



This phase will be completed individually. You must submit your solutions on iLearn.



Phase 2 (10%)



Logical DB Design (Relational Database Schema)



This phase will also be completed individually. You must submit your solutions on iLearn.



Phase 3 (60%)



Documentation of the project including details about your assumptions



(10%).




Implementation of SQL queries in the Client Application (30%).



Physical DB Design (DB performance tuning indexes) (10%).



Extra credit for good GUI design and interface, any dataset or schema changes/extensions, etc. (20%).



Phase 3 must be completed in groups of TWO students. No individual submissions are allowed. In your report, explicitly list the tasks that each member of your group was responsible for. If one of the group members does most of the work the grade will be proportional to the effort.




If you are not able to find a partner, one will be assigned to you at random. Please e-mail the TAs immediately if you need help finding a partner.







Requirements Analysis



You are to design a hotel management database that serves the needs of hotel managers and customers. Each of these types of individuals needs access to the following information:




Hotel Management:




Given a hotel ID, list a given room’s bookings for the week.



For each hotel ID, get highest price among all booked rooms for a given data range



Given a hotel ID and a date, get (1) the number of rooms still available and (2) number of rooms booked



Given a hotel ID and date, get a list of customers who made bookings for that date



Given a booking ID, retrieve information about the customer (First & Last Name, Gender, Date of birth, Address) who made the booking



Given a hotel ID and customer ID, get the total cost incurred by the customer for a given data range.



Hotel Staff:




Given a Hotel ID, list all details pertaining to staff, including their positions/roles (Hotel Managers, Receptionists, House cleaning, etc.) who are employed by that hotel



Hotel Managers may make maintenance/room repair requests, which will be handled by a maintenance company. The maintenance company must be certified to handle that specific type of repair. Given a manager ID list the hotel ID, room number and date of request.



Given a hotel ID and House cleaning staff ID list all the rooms he/she is assigned to



Customers:




Given customer ID, list all the rooms previously booked by that customer in all the hotels



Given a price and a data range, list all the available rooms in all hotels for that date range, and price at or below the specified price.



Given a customer ID give the hotel ID where the per-day cost incurred by that customer was the highest.



Maintenance Companies:




Given a maintenance company ID, list the type of repair, the hotel, and the room number for all repairs made by that company



For a given date range, list all the requests received by the maintenance company from a particular hotel manager ID