We need to build a website that will show real estate agents the number of times their property listings were viewed (pageviews) on real estate websites (portals). To do this the system must retrieve the daily visit number from the HTML of an admin page in the portal that we don't control, e.g http://www.realestate.com.au/pdpvisi...?id=118114975. One agent usually send their property data to 3 portals. We have a database of 100 agents and we store the list of their properties in other portals, plus the name of their management company (if they have a management company, as some do not). Assume each agent have 1000 properties and send their all their properties to 3 portals, that means we need to crawl 300,000 admin pages.

1. Please create the database schema required to support this website.

2. Please explain how you will get the data and store it because there are lots of data to collect.

3. Please create the query to show page view for the last 7 days for agent X and property Y on each portals.

4. Please write the query to retrieve the agent name, management company name if there is one, and the total number of times all properties were viewed for a specified agent in a specified period.