CS1555 Recitation 12 Solution


Objective: to practice ER diagrams and transforming them to relational schemas


ER Diagrams

1. Create an ER Diagram for a rental management company that rents apartments. Each
apartment has an address (street address, city, zipcode), apt_number, floor, last inspection
permit number, number of bedrooms and number of bathrooms. Each apartment also has
an owner. We keep the owner’s name and address only. The apartments are rented to
tenants who are required to sign leases. Each lease has a lease_no, duration, start-date,
end-date, and monthly payment. When the tenants renew the lease, we keep track of the
renewal date and the duration of the renewal. We keep track of all the apartments a tenant
has rented from the company before. Tenants have SSNs, names, occupation, previous
address(es), cell phone numbers, and monthly income. A tenant could submit a
maintenance request for the apartment where something broke. A maintenance request
should have request_date, problem, description and whether it has been resolved.

NOTE: this is a more complex example that brings into ER diagram weak entities and
identifying relashionships


2. Transform the ER diagram from part 1 into relational schemas
























Observations:


A rental management company that rents apartments.

Each apartment has an address (street address, city, zipcode), apt_number, floor, last inspection
permit number, number of bedrooms and number of bathrooms.

Each apartment also has an owner. We keep the owner’s name and address only.

The apartments are rented to tenants who are required to sign leases.

Each lease has a lease_no, duration, start-date, end-date, and monthly payment.

When the tenants renew the lease, we keep track of the renewal date and the duration of the
renewal.

We keep track of all the apartments a tenant has rented from the company before.

Tenants have SSNs, names, occupation, previous address(es), cell phone numbers, and monthly
income.

A tenant could submit a maintenance request for the apartment where something broke.

A maintenance request should have request_date, problem, description and whether it has been
resolved.



















Lease Apartment
Maintenance
Request
Tenant
Leased
By
signs
Sub
mits
M
N
M
M
1
1
1
Ap
t#
Addr
ess
Zip
Cit
yStre
et
Flo
or
#B
r
#B
a
Leas
e#
start
D End
D Payment
Duration
name
Occupatio
n
Cell
#
income
Prev_Ad
dress
Zip Cit
y
Stre
et
Problem
description
date resolved
ssn
Rene
wal
date
Address Name
Inspection
Permit #
duration
Owner
2. Transform the ER diagram from part 1 into relational schemas

Entities:
1. Apartment ( address(street address, city, zipcode), apt_no, floor, n_bedrooms,
n_bathrooms, inspection_permit_no, {Owner(name, address)})
2. Lease ( lease_no, duration, start-date, end-date, monthly payment)
3. Tenant (ssn, name, occupation, {previous address(street, city, zipcode)}, cell_no,
income)
4. Maintenance Request (date, problem, description, resolved) – weak entity

Relationships:
1. Leased By M:1, TOTAL/PARTIAL
2. Signs < Lease, Tenant> M:N, TOTAL/TOTAL, renewal_date, duration
3. Submits 1:1:M
PARTIAL/PARTIAL/TOTAL

The resulting schemas after mapping:

APARTMENT (apt_no, floor, n_bedrooms, n_bathrooms, inspection_permit_no, street address,
city, zipcode, owner_name, owner_address)

LEASE ( lease_no, start-date, end-date, monthly payment, apt_no)
FK (apt_no) à APARTMENT (apt_no)

TENANT (ssn, name, occupation, cell_no, income)

MAINTENANCE_REQUEST (date, problem, apt_no, ssn, description, resolved)
FK (apt_no) à APARTMENT (apt_no)
FK (ssn) à TENANT (ssn)

TENANT_SIGNATURE (renewal_date, lease_no, ssn, duration)
FK (lease_no) à LEASE (lease_no)
FK (ssn) à TENANT (ssn)

TENANT_PREV_ADDR (ssn, street address, city, zipcode)
FK (ssn) à TENANT (ssn)



欢迎咨询51作业君