本次数据库代写是设计企业关系数据库并使用PostgreSQL DBMS进行操作和维护

CSI2132 – Databases I
Winter 2021

Project Description:
The aim of this project is to gain experience in the design of an enterprise relational database as well as
its operation and maintenance using PostgreSQL DBMS. Suppose that six hotel chains, each one with
hotels in more than 20 locations in North and South America combined, have decided to cooperate and
develop an application (NOTE: The application can be a simple command line interface, however, if
you wish you can design more sofisticated interfaces.) that will allow their customers to easily book
rooms in their hotels, seeing room availability in real time, etc. This will be the application domain
considered for this project. You are required to develop, as part of a new information systems project, a
new database to allow this.
For each parent hotel brand you will need to store the information regarding: the central offices location
(Main office location) and physical address, email addresses, phone numbers, the total number of hotels
of the chain. For each hotel chain we need to know: the star category (e.g. 1-star up to 5-star), the
number of rooms, the address of the hotel and contact email and phone numbers for this hotel. For the
rooms in a hotel, we need to know their price, all amenities (e.g. TV, air condition, fridge, etc), the room
capacity (e.g. single, double etc), if they have sea view or mountain view and if they can be extended
(e.g. adding one more bed). For customers we need to store their full name, address and SIN number
and the date of their registration into the system. Also, store the booking information details of the
customer (e.g. room type booked, total number of occupants of the room etc). For employees of the
hotels, we need to store their full name, address, salary and SIN (Any more personal details which you
might feel relevant to add). The employees may have various roles/positions in a hotel such as concierge,
housekeeper, manager, etc. Every hotel needs to have a manager.
The customers can search for and book rooms for specific dates. When they check-in the hotel, their
room booking is transformed to renting and they can also pay for this renting. The employee that does the
check-in for a customer is responsible for transforming the room booking to renting. A customer may
present himself physically at a hotel without a booking and directly ask to rent a room. In this case the
employee at the hotel can do the renting of the room right away without prior booking. We need to store in
the database the history of both bookings and rentings (archives), but we do not need to store the history
of payments. Information about an old (archived) room booking/renting must exist in the database, even if
information about the room itself does not exist in the database anymore. We should be able to add and
to delete from our database hotel chains, hotels and rooms. If the database has information about a room
then, the information about the corresponding hotel should also exist. The same applies to hotels and
corresponding hotel chain.
The project should be carried out by a group of 3 (or 2) students from the same Lab to ensure project
completion before the submission deadlines. In exceptional cases, subject to the approval of the
instructor , a student may be allowed to work alone on the course project. An email should be sent to the
instructor including a justification for this.
The project has 3 parts:
 [12%] 1st deliverable
 [14%] 2nd deliverable
 [4%] Presentation
You are requested to do the following for the 1st
Deliverable [12%]:
1. (5%) Create the ER diagram that corresponds to the above description. Be sure to identify the primary
keys, relationship cardinalities, etc.
2. (5%) Create the Relational model that corresponds to your ER model. Provide both the relational model
and the schema diagram.
3. (2%) Define the necessary constraints that will ensure the correctness of the database to be created
according to your Relational model. These are primary keys, referencial integrity constraints, domain
constraints and user-defined constraints. Please include a brief justification for each constraint.
You are requested to do the following for the 2nd
Deliverable [14%]:
4. (3%) Implement the database according to your Relational model and the constraints that you have
defined. Implement the relations in PostgreSQL and make sure you create indices and constraints as
appropriate. If you discover flaws that require changes to your E-R Diagram, make sure these changes
are captured in your relational model.
5. (2%) Create the necessary SQL modifications (use queries and triggers). Your database should allow
insert, delete and update operations of data in your database according to the referential integrity
constraints that you have defined. Give the SQL code for at least 2 queries and 2 triggers in your report.
6. (2%) Populate your relations. Your database needs to be populated with data after creating it. Add
enough data will make your queries interesting. You should have at least 6 different hotel chains with at
least 5 hotels each. The hotels of each chain should belong to at least 3 different star categories. You
should also add at least 5 rooms with different capacities in each hotel and you should include several
customers, employees, etc. Add at least 3 hotels from different hotel chains in Ottawa city.
7. (3%) Several users will need to make use of the database and each will require a special application
during access. (Since it is not a web application development course, a simple command line interface
will suffice but you can develop an application if you are able to).
 Database administrator will need to use SQL either through the command line or SQL Developer.
The administrator should be able to insert/delete/update all information related to customers,
employees, hotels and rooms.
 Customers need a web interface to search for rooms and to book rooms for specific dates.
 Hotel employees need a lookup application that allows a quick access to rooms available and
booked. Employees will transform booked room into rented rooms when the customers arrive at
the hotel. If a customer arrives to the hotel without a booking the employee should be able to find
a room and rent it without prior booking. An employee should also be able to insert a customer
payment for a renting.
8. (4%) You need to run a list of queries the database clients are interested in. Please provide the code
and the results you obtained when running the queries in your database.
1. Give the details of all currently rented rooms in a specific hotel. Please display the
columns as customer name, room type, room price, rental start date, room view, hotel chain.
Sort by the room price in ascending order and rental start date in descending order.
2. Create a view named CustomerListView that gives the details of all the customers.
Please, sort the customers by hotel chain.
3. Display the details of the cheapest hotel room of all hotel chains.
4. List all the rooms in all hotels in Ottawa and sort them based on the hotel stars and price.
5. List all the details of all rooms rented on the 10th day of a month of your choice. Ensure to
insert dates in your table that correspond to that month in order to run your query.
6. Update the phone number of a customer.
7. Which category hotels (1 star to 5 star) are most preferred by the customers?
8. Find the second highest salary from the employee table.