Reference no: EM133537824

Data Modelling

SQL and Extensions

Learning Outcome 1: Interpretation of business rules from additional information for case study;
Learning Outcome 2. Knowledge acquisition with respect to Spatial, Graph and Document Databases:

a. Creation of a spatial database, graph database and a document database;
b. Creation of spatial data within the spatial database, graph data within the graph database and document data within the document database;
c. Querying of the spatial, graph and document data.

Timelines and Expectations
Case Study - Northern Territory Tourism Track Recommender System

Refer to Figure 1 for a high-level overview of the system to be developed.

The company for whom you are developing this system ("NT Tourism Tracks" or NT3) wants people to visit local walks that are considered to have touristic appeal. For instance, for the towns in the Northern Territory (e.g. Alice Springs, Darwin, Katherine, Jabiru) there are tourism guide books and websites that detail walks that interested tourists can use to explore sites of local interest.

In order to find potentially interested people, NT3 trawls various sources of social media (e.g. Facebook, Twitter, Instagram etc) for geocoded data - which could include a mention of a certain place, or coordinates tagging postings or hidden within image EXIF data.

The geographic coordinates are then used to search through a database of tracklogs for touristic walks. The nearest walk is retrieved and recommended to that person.

Additionally, during the social media trawl, friendship links are gathered, and these are used to recommend the same retrieved walk to the persons' friends. The links are inserted into a separate database for later use.

Additionally, anyone who wants to submit a review of their walking experience can do so. This is inserted into a separate database.

NOTE: The complete scenario for the assignment will be discussed in lectures and support provided in tutorials, including appropriate algorithms for calculating the nearest tracklog.

Technical Details

While the system as explained appears to be a single application, you do not need to develop a Graphical User Interface that incorporates these different elements. Instead you merely need to demonstrate these different parts. The case study is used to motivate these different types of technology.

To simulate the social media data, you will use a Postgres database, which will include tables of people (at least 5), their connections to other people, and some coordinates related to them - which supposedly originated from their social media profile.

You can use the same Postgres database for the spatial part but you will need to extend the PostgreSQL relational database to a PostGIS spatial database. You can write SQL commands in pgAdmin4 to create spatial data in the extended database, and write spatial queries using SQL. The PostGIS database will need to have some tracklogs inserted (at least 5), each comprised of at least 10 points. These will need to be from towns in the Northern Territory. You will need to be able to query this database as follows: given a specific point (set of x,y coordinates), you are to find all tracklogs (as polygons) within which this point is contained. You are also required to retrieve the nearest tracklog if the point does not lie within a specific tracklog. For the spatial database tasks you should use the Spatial Reference ID (SRID) of 4326 for your spatial data.

The Neo4j database will contain nodes representing people and links representing the relationships between these people. You will need to be able to insert new nodes and links into the database. Insert at least 5 people. You will need to be able to retrieve friends of a specific named person.

The MongoDB document database will contain a collection of reviews, comprised of JSON documents that have appropriate key:value attributes, for instance keys such as "Trip name/ID", "Reviewer name/ID", "Rating", "Review" etc. You will need to write the code to insert at least 5 reviews. Use MongoDBCompass GUI and associated software mongosh or equivalent. Create the document database, create a collection in that document database, create documents within that collection, write MongoDB commands to populate the collection. You will also need to write MQL to query the database to find the best review.

You are to provide extensions to the current system, for each of the Spatial, NoSQL and Graph capabilities. For each category, write three additional queries which provide useful new functionality, and explain the purpose of these queries.

Documentation requirements

1. Prepare a report (single Word or PDF document) which contains the following: a). Title page
b). Introduction and table of contents
c). Details of all Tasks of the Recommender System.
d). A copy of your code - you must also provide associated output (as screengrabs, inserted into the report)
e). Statement of any resources used. These includes full disclosure of assistance from all sources including tutors and other students. Full APA referencing of any resources used.