You are in a real estate business renting apartments to customers. Your job is t

Question

You are in a real estate business renting apartments to customers. Your job is to define an appropriate schema using SQL DDL in MySQL. The relations are Property(Id, Address, NumberOfUnits), Unit(ApartmentNumber, PropertyId, RentalPrice, Size),Customer (choose appropriate attributes), Rentals (choose attributes; this relationshould describe who rents what, since when, and until when), and Payments (shoulddescribe who paid for which unit, how much, and when). Assume that a customer canrent more than one unit (in the same or different properties) and that the same unit can be co-rented by several customers.

Explanation / Answer

Hi,

I am writing the sql queries here with explanation, so that you can execute and see the result.

create table Property(property_id int(11) not null PRIMARY KEY, Address varchar(50), NumberOfUnits int(11));

Here, we are creating property table with property_id as primary key.

create table Unit(ID int(11) not null AUTO_INCREMENT PRIMARY KEY, ApartmentNumber int(11), PropertyId int(11), RentalPrice int(11), Size int(11), CONSTRAINT fk_Unit FOREIGN KEY (PropertyId) REFERENCES Property(property_id));

Here, we are creating Unit with PropertyId as Foreign Key to Property, because one property may have any number of apartments. So, we can add any number of apartments with it's number, rental price, size and corresponding property.

create table Customer (ID int(11) not null AUTO_INCREMENT PRIMARY KEY, unit_id int(11),name varchar(50), address varchar(50), CONSTRAINT fk_Customer FOREIGN KEY(unit_id) REFERENCES Unit(Id));

Given the condition that customer can rent more than one unit, same or different properties, so we are creating a foreign key to Unit, also same unit can be co-rented by several customers. If we observe carefully, both are same, as long as we are not putting unit_id as unique, it will allow any number of rows with different details. That means, a unit can be co-reneted by any number of customers.

create table Rentals(ID int(11) not null AUTO_INCREMENT PRIMARY KEY, unit_id int(11) UNIQUE, start_date DATETIME, end_date DATETIME, customer_id int(11) UNIQUE, CONSTRAINT fk_rentals FOREIGN KEY (unit_id) REFERENCES Unit(PropertyId), CONSTRAINT fk2_rentals FOREIGN KEY (customer_id) REFERENCES Customer(ID));

This table acts as intermediate between customer and units. Firstly, we are creating foreign key referring to PropertyId in Unit table and making it as unique so that we can figure out which unit is being rent. Also, we are creating a foreign key to customer with unique so that we can ensure for a unit and for a tenant, there is only single entry.

create table Payments (ID int not null AUTO_INCREMENT PRIMARY KEY, amount int(50), paid_time datetime default now(), paid_for_unit int(11) UNIQUE,customer_id int(11) UNIQUE, CONSTRAINT fk_property_id FOREIGN KEY(paid_for_unit) REFERENCES Unit(PropertyId), CONSTRAINT fk2_customer_id FOREIGN KEY(customer_id) REFERENCES Customer(Id) );

Coming to Payments, we have to ensure to which Unit the payment has made and who made the payment. So, creating two foreign keys against Unit and Customer respectively. Making that UNIQUE ensures us only a single row against those constraints.

Related Questions

Navigate

• Browse (All)
• Browse Y
• Subjects

Integrity-first tutoring: explanations and feedback only — we do not complete graded work. Learn more.