The Use of Emerging Technologies in Real Estate and Built Environment Sector

Part 1: Database setup (30%)

Case Study:

The traditionally labour-intensive real estate and built environment sector has been hit particularly hard by the pandemic and the resulting manpower shortage, but with the increasing use of emerging technologies like drones, artificial intelligence (AI) and Internet-of-Things (IoT) devices, the sector may soon see significant alleviation of its manpower issues.

On top of that, these technologies can also benefit the end users of the real estate sector – real people living in real homes. Besides making the construction process more efficient and less reliant on manpower, these technologies can also help with safety monitoring, fall detection and even cleaning.

Part 1: Database setup (30%)

To support this initiative by the real estate sector, the Singapore government has brought in a new cloud hosting service provider Umezon. Incorporated and based in Singapore, the cloud services it provides will give the real estate sector the processing power it needs for its digitisation and AI initiatives.

You have been hired as Umezon’s database administrator. To spearhead its incorporation, it has recently launched a web portal for its customers to easily purchase and use its services.

It has received its first batch of 200 user accounts (see Cloud User Accounts.csv).

Tasks:
1.Analysis the fields and data in the csv file and design the new user account database.
2.Write an SQL script to insert the customer data in csv file data into the database.

What to submit:
1.ER diagram of the new user account database(s). (10 marks)
2.SQL script to setup the new database. (10 marks)
3.SQL script to load the customer data into the database. (10 marks)

Part 2: Billing (40%)

For their launch, Umezon is offering 2 server types for deployment: the Elastic Computing Unit (ECU), and the Relational Database Unit (RDU). Each user account can deploy as many number of each type of server as they want, and they will be charged based on 1) how long each server is deployed, and 2) the performance level of the server.

Users can choose to deploy servers under either a Subscription basis (which charges by month) or a Pay-as-you-Go basis (which charges by hour). Once you deploy a server, you have to pay the full amount for the minimum period (e.g. deploying a server for half a month under Subscription basis will still cost the full month’s price).

Each server type can be deployed at a performance level between 1 to 5. The cost per period of the deployment will be equals to the Base Cost (shown below) multiplied by the performance level.

The deployment data of the users from July to September are in the file Cloud Hosting Deployments.csv, and the rates for each server type are as follows:

Task:
1.Analysis the fields and data in the csv file and design the new tables in the database to store the usage data.
A billing report detailing the customer details and the amount due.

What to submit:
1.ER diagram of with the new table(s) in the Umezon database. (10 marks)
2.SQL script to create the new table(s) in the database. (10 marks)
3.SQL script to load the usage csv data into the database. (10 marks)
SQL script to generate an SQL output showing the amount each user has spent on subscription-based ECUs only. (10 marks)

Part 3: Customer in-sight analysis (30%)

Tasks:
1.The marketing manager would like to have a user in-sight analysis of the deployment duration (in hours) of both the ECU and RDU (average and total hours). The result should also be grouped by the deployment type. (15 marks)
2.Implement one more customer in-sight analysis that is useful for the marketing manager. Justify your answer (15 marks)

What to submit:
1.SQL script to generate the output for the user in-sight analysis.
2.The two customer in-sight analysis SQL outputs.
3.Explain why the customer in-sight analysis you choose to implement is useful for the marketing manager.