Demonstration of Data Modelling and Querying Approaches

Assignment Brief:

In this assignment you must demonstrate knowledge, understanding and skills in coping with various data modelling and querying approaches, which range from schema based to more flexible design approaches. The assignment is meant to expose you to the challenges posed by the choice of a data modelling approach to the subtle balance between flexibility in data modelling and complexity of querying, with emphasis on analytical queries.

To this extent, you are given a dataset, which provides exemplary data about the rented properties on the market. The key concept of your coursework is to use the three different data modelling and querying approaches, for the very same dataset, as discussed and practiced on this module: A) relational database and SQL, B) schema-less collections of documents with MongoDB, C) knowledge graphs with GraphDB.

Therefore, your coursework consists of three constituent parts with specific tasks as outlined below. The dataset is initially available by the property.sql script available on Canvas module site. For parts (B) and (C), this dataset needs to be transformed into suitable datasets, collections of documents and knowledge graphs, respectively, prior to writing the tasked queries

Tasks:

1. Create the “properties” database, within your Oracle database account, by running the property.sql script being made available as a “programming resource for practical exercises” in the module’s Canvas site. [5 Marks]

2. For the sake of business intelligence, write the SQL queries to answer the following questions:

a. For each property type count the number of properties in each type. [5 Marks]
b. For each property-type show how many properties we have for each rental-payment amount. For instance, for property type S we have 2
properties that pay £450 per month. [5 Marks]
c. For each monthly payment count the number of properties for each property type. [5 Marks]
d. Show the number of leases per monthly rental amount. [5 Marks]
e. Show the number of leases per monthly rental amount, and the total number of leases overall. [5 Marks]
f. Show for each payment method, the different monthly rental amounts with the number of leases for that monthly rental amount, the total
number of leases per payment method, and the overall total number of leases. [5 Marks]
g. Show all combinations of numbers of lease, monthly rentals and payment methods. [5 Marks]

3. Write SQL DDL statements, which will transform the existing database schema, as initially defined within properties.sql, in such a way that any
attribute-based generalization/specialization hierarchies for property types are now expressed by explicit relations as well. You should choose a
solution with the least impact possible to the complexity of queries, as of task A.2 above, while the risk of update anomalies is minimised.
[10 Marks]

4. Justify your choice for A.3 in that you explain how the possible options will impact the syntactic complexity and, eventually, performance of the analytical queries as of task A.2.