Question FIT9132 – Case Study On Database Normalisation


Question FIT9132 – Case Study On Database Normalisation.
Introduction to Database Assignment on Normalisation. 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.

Question FIT9132 – Case Study On Database Normalisation


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.