Study the following scenario and attempt all tasks that follow: -

Great Ocean Road Rental Company (GORRC) GORRC is one of the rental companies in Victoria and offers house or apartment rental to tourists who want to reside near the Great Ocean Road. The Company is using a manual method for keeping track of tourists and their rentals. However, they are interested in going online and allowing tourists to search available houses / apartments, do a reservation based on the advertised rents. When any tourists rent a house / apartment with the Company for the first time, their details must be recorded in a system. There are certain types of villas that are classified for senior aged people. It is a policy of GORRC that these villas are only rented to customers over 70 years old. Houses/apartments details must be recorded with information such house id, classification (normal vs senior), kitchen (Yes / No), living room (Yes / No), number of bedrooms, number of washrooms, BBQ (Yes / No), number of onsite car parks, daily rent, etc. All booking information such as booking id, customer id, house id, from_date, to_date, total cost must also be stored. Each house/apartment has a unique id. A customer can search their desired house/apartment online to see if it is available on their preferred dates. All rentals are for minimum 1 day. Rental charges may vary based on house/apartment types and dates. Total rental payment will be deducted automatically from customer’s credit/debit card at the time of registration. Each booking id must be unique. (The booking id can be a number automatically generated by the system).

Task 1

• Investigate and identify data requirements for the given case study.

• Provide (i) a list of required entities and (ii) a list of attributes for each entity identified in (i).

• Identify the Unique identifier for each entity.

Task 2

Develop an Entity-Relationship (ER) data model for the given scenario showing as many as possible the following:

• Entities and unique identifier for each entity.

• Relationships between entity types, including relationship constraints. For each relationship, indicate the degree of constraint such as ‘One to Many’, ‘Many to Many’ or ‘One to One’.

• Attributes: You are required to identify all necessary attributes. Assign all attributes to entity types and underline all unique identifier attributes. You may choose any modelling notation. State any necessary assumptions you have made, with the understanding that they do not contradict the given scenario.

Task 3

In this task, you are required to

• Derive a physical design having details of tables, primary key, foreign keys, and brief details about all essential columns of tables. o Provide detail information like requisite, atomic, type of value, key or non-key and constraint for all data columns of each table.