Database Systems, CSCI 4380-01 Homework # 3




5/5 - (5 votes)

Problem 1. In this question, you are asked to create an E-R diagram that represents all the
requirements below precisely. Note that while there are multiple potential correct models, not all
answers are right. Be extra careful about choosing entities and correctly implementing the participation requirements. ER-modeling is an iterative process. Work on a model first and then refine it.
A note on drawing ER diagrams. You will find that the diagrams can get very large if entities have
a lot of attributes. To avoid this, you can simply list the attributes for the entities inside the box
for that entity. Remember to underline the key attributes.
You are creating a database for a house sharing company like Airbnb. The database stores the
following information. Customers have name, email, address, credit card. Renters (those who list
houses) have name, email, address, a number of property listings for houses. Each house has an
id, a label, a description, address (street, state, city, zip), pictures (each picture has image and
sequence id) and amenities. Each listing is for a house and has multiple available dates ranges
(fromdate, todate) and a price for each date range (while the date ranges should not overlap, this
is handled by software outside of the database). Customers have past and upcoming trips. Each
trip involves a specific house, a specific date range, the date for the reservation and listed price.
For future trips, you also store whether the trip was confirmed or not, and on which date. Past
trips may have a review from the renter for the customer with a star value, a yes/no field for
whether they recommend the customer and a review text. Past trips may also have a review from
the customer for the renter with star values for cleanliness, convenience and quality of property
as well as review text. Each review is for a house. Finally, there is a message board for messages
between customer and renter for future trips. Each message has a sender, a receiver (customer or
renter), message date, message text. If the message was a response to a previous message, this is
also stored.
SUBMISSION INSTRUCTIONS. Submit a PDF document for this homework using Gradescope. For this homework only, we will accept hand drawn ER diagrams, but only if they are
legible. Remember, we cannot grade what we cannot read. Of course, ER diagrams drawn using
software will be highly welcome and appreciated. No late submissions will be allowed.