| Database Management and Security |
Case Study:
The traditionally labor-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.
You may read more about this recent initiative here:
https://www.straitstimes.com/tech/how-ai-and-digitalisation-are-changing-the-realestate-sector-in-singapore
Part 1: Database setup
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 digitization 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 of 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).
2. SQL script to set up the new database.
3. SQL script to load the customer data into the database. (10 marks)
Part 2: Billing
For their launch, Amazon 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 numbers 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 the 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 a 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:
