FIT9132 Introduction to databases
Assignment 1: Normalisation
Due date: Monday 3-April-2017, 12 PM (12 NOON).
Marks: 10%
CASE STUDY
AUSBnB provides service to match travellers who look to be guests in accommodation provided
by property owners acting as hosts. Currently the accommodations provided are limited to
properties in Australia. However, the guests may come from outside Australia. It has an ONLINE
system to allow guests to book accommodation with the host. Before the guests can book
accommodation, they must be a member of AusBnB. Similarly, the host needs to register as a
member to be able to list accommodation. A host may become a guest in another member’s
property. Hence, there is no specific designation attached to a member record. Their role as
host or guest is determined by the activity perform, eg acting as a guest or acting as a host.
Samples of data collected from the ONLINE registration forms are depicted in figure 1a and
figure 1b.
Member ID 3456
Member Name Linda Nguyen
Email linda.nguyen@gmail.com
Phone +62215621234
Date of Birth 10-Jan-1990
Country Indonesia
Address 10 Thamrin rd, Jakarta Pusat, 11345
Join Date 9-Nov-2015
Verification Level 3
Figure 1a. Member registration data
Member ID 1234
Member Name Rosa McManamey
Email Rosa1223.au@gmail.com
Phone +61422333777
Date of Birth 09/07/1978
Job Lawyer
Country Australia
Address 900 Dandenong Rd, Caulfield East, VIC 3145
Join Date 10-Aug-2016
Verification Level 2
Figure 1b. Member registration data
The member ID provided by the AusBnB when they process the registration. AusBnB performs
background checking on the member to ensure safety of its members. At the completion of the
background check, members are given verification level of 1 to 3. Level 3 means member has
provided international recognised identification, eg passport to the company. Level 3 is the
highest level verification.
Once they have registered, host can start listing their properties at the AusBnB website. Figure
2 depicts the data collected from property registration form.
1
Property ID 88888
Property Name The Cosy Tasman
Member ID 1234
Host Name Rosa McManamey
First Listed Date 20-Jan-2016
Verification Level 2
Property Address 26 Trevallyn Road, South Hobart, TAS 7004
Property City Hobart
Accommodation type Entire home
Description New luxury apartment fully and nicely furnished, comfortable
and very quiet.
Price per Night AUD $300
Facilities
Number of beds: 3
Cooking: Yes
WIFI: Yes
Car Parking: Yes
Renting Conditions
Minimum booking: 2 nights
Maximum no of guests: 5
Cancellation: Strict
Check in: 2 PM
Check out: 10 AM
Additional Fees
Security deposit: AUD $800
Cleaning Fee: AUD $120
Discounts
More than a week booking: 10%
More than a month booking: 20%
Figure 2. Property Listing data
2
Once a property is listed, a guest can make a booking for the property. Before making their
choice of property to rent, a guest usually would like to see some reviews from guests who have
stayed in the property. A sample data displayed by AusBnB on property reviews is depicted in
figure 3.
Property Name The Cosy Tasman
Host Name Rosa McManamey
Property Address 26 Trevallyn Road, South Hobart, TAS 7004
Property City Hobart
Reviews
guest
Name
Review Rating (1-5) Date of Review
Peter Pan Beautiful house and generous host.
We will be back!
5 1-Feb-2016
Jose
Carino
Rosa and David are very welcoming,
helpful and kind. I really enjoyed
spending time at your home. Best
summer experience in Tasmania.
5 8-Feb-2016
Peter Pan Another stay, another enjoyable
experience.
5 8-April-2016
Figure 3. Property listing with reviews
During the search, a potential guest may choose several properties as a possible booking. The
list of potential bookings is called the WISH list by AusBnB. An example of data in a WISH list is
depicted in figure 4a and figure 4b.
3
Guest name: Linda Nguyen
City: Hobart
Property Name Property Address
The Cosy Tasman 26 Trevallyn Road, South Hobart, TAS 7004
The Battery Point Mansion 10 Battery Point close, South Hobart, TAS 7004
City: Melbourne
Property Name Property Address
The Artful Southbank 2/12 Dorcas st, South Melbourne, VIC 3205
The Coffee Neighbour 3 Bank St, South Melbourne, VIC 3205
Figure 4a. A guest wish list
Guest name: Jose Carino
City: Melbourne
Property Name Property Address
The Artful Southbank 2/12 Dorcas St, South Melbourne, VIC 3205
The Coffee Neighbour 3 Bank St, South Melbourne, VIC 3205
The Beach Shack 5/1100 Beach St, Port Melbourne, VIC 3207
Figure 4b. A guest wish list
4
When a guest wants to make a booking or reservation, they need to fill in a booking request. A
sample of data completed by a traveler in a booking form is depicted in figure 5. A reservation
code is provided by AusBNB to track reservation and payment of the booking.
Reservation Code 4XX5Y3
Member ID 3456
Member Name Linda Nguyen
Host Name Rosa McManamey
Property Name The Cosy Tasman
Check-in Date 29-Oct-2016
Check-out Date 31-Oct- 2016
Number of guests 3
Figure 5 Reservation request
The host then will be provided with the booking information as above with the addition of the
guest details. Figure 6 shows the sample data that will be seen by the host in regards to
reservation 4XX5Y3.
Reservation Code 4XX5Y3
Property Name The Cosy Tasman
Check-in Date 29-Oct-2016
Check-out Date 31-Oct- 2016
Number of guests 3
Guest information
Name: Linda Nguyen
Member since: 9-Nov-2015
Country: Indonesia
Verification Level: 3
Figure 6. Reservation request to be reviewed by host
5
When the host approved the booking, an invoice is generated for the guest. Figure 7 shows a
sample of an invoice for the reservation 4XX5Y3.
AusBnB INVOICE
Reservation Number: 4XX5Y3
Member ID: 3456 Member Name: Linda Nguyen
Check in: 29-Oct- 2016 Check out: 31-oct-2016
Host Name: Rosa McManamey
Property Address: 26 Trevallyn Road, South Hobart, TAS 7004
To be paid by: 29-Sept-2016
Fee
Type
Description No of
unit
Unit
Cost
Total
ACC Accommodation 2 300 600
CL Cleaning 1 120 120
SC Security Deposit 1 800 800
Total $1520
Figure 7. Invoice
6
At the completion of the stay, both the guest and the host may write a review about the stay.
The guest may make a review about the property. The host may make a review about the guest.
A sample data extracted from a review made by a guest on a property is presented in figure 8.
Figure 9 presents an example of a host’s review on a guest.
Property Name The Cosy Tasman
Guest name Linda Nguyen
Review Rosa is very generous host. Tea, coffee and some
biscuits were provided at the house. The view from the
balcony is magnificent!
Review Date 3-Nov-2016
Rating 5
Figure 8. Property review from a guest
Guest Name Linda Nguyen
Host name Rosa McManamey
Review Linda is an excellent guest. She left everything clean. I
had a pleasure sharing her stories of travels.
Review Date 10-nov-2016
Rating 5
Figure 9. Guest review from a host
7
As part of their service to the members, AusBnB generates monthly reporting for the hosts. A
sample of the report is presented in figure 10.
Reporting Month: October 2016
Member ID: 1234
Member Name: Rosa McManamey
Address: 900 Dandenong Rd, Caulfield East, VIC 3145
Phone: +61422333777
Email: Rosa1223.au@gmail.com
Property ID: 77777
Property Address: 2/378 Lonsdale St, Melbourne, VIC 3000
Check in Check Out No of Guest Price Cleaning Fee Amount paid Rating
1-Oct-2016 5-Oct-2016 2 250 120 1120 4
10-Oct-2016 12-Oct-2016 4 250 120 620 5
29-Oct-2016 31-Oct-2016 2 250 120 620 5
Total 2360
Average Rating 4.67
Property ID: 88888
Property Address: 26 Trevallyn Road, South Hobart, TAS 7004
Check in Check Out No of Guest Price Cleaning Fee Amount paid Rating
29-Oct-2016 31-Oct-2016 3 300 120 720 5
Total 720
Average rating 5
Figure 10. Property Occupancy report for host.
8
mailto:Rosa1223.au@gmail.com
Tasks:
Create a Google document in the Google drive provided for you by the teaching team. The drive
has been setup in a way to allow you and your partner jointly writing the assignment. Google
document allows multiple users to write simultaneously. Type the answers to the tasks
incrementally in the Google document. DO NOT type the assignment in another editing software
and then copy and paste the whole assignment from the other editing software to the Google
document. The assignment will not be marked if your team decides to do so.
Complete the following tasks for the assignment:
1. Perform normalisation to the 3NF for each set of data depicted in various figures in this
document.
2. Consolidate all the found 3NF relations found in step 1.
Things to consider during normalisation:
1. Do not add surrogate key to the normalisation. You can add ID to a normalisation
process if that ID is actually used by AusBnB as identifier. For example, property ID is an
existing identifier (not a surrogate key). You can add property_id to data in figure 7
although it is not included in the invoice depicted in figure 7. If you add an identifier to
the normalisation process, add it from the beginning in the UNF. An attribute review_no
to uniquely identify each review on the other hand does not exist as identifier in AusBnB
daily operation, but this attribute may be created later during database implementation
as a surrogate key. In this case, you should NOT add review_no into the normalisation.
2. Identify clearly Primary Key in all relations.
3. Identify clearly the Foreign Key in relations (if applicable).
4. Identify clearly the partial and transitive dependency (if exists) in all 1NF relations. You
can use the dependency diagram or use your own notations.
Submission requirements:
Download your team’s Google Document into a pdf file. Submit the pdf file in Moodle before or
on the due date. You need to submit only one file per group. One of the group members can
upload the pdf file. However, once the file is uploaded, to complete the submission, each
member needs to press the SUBMIT button in Moodle and signs the student assignment
declaration individually. You need to make the submission process is completed correctly,
penalty of 5% per day (including weekend) is applicable to late assignment.
9