Module Code : 6COM1048
Module title: Advanced Database Topics
Assignment title: Probing and contrasting data modelling and querying approaches for data analytics across different data modelling and engineering paradigms
Group or individual: Individual
6COM1048 Advanced Database Topics Assignment – Hertfordshire University UK.
This Assignment assesses the following module Learning Outcomes (from Definitive Module
Document):
Knowledge and understanding:
[i] seek and apply the concepts of top-down data modelling paradigms to produce a workable design which can be implemented in a multi-user environment;
[ii] have an in-depth understanding of data warehousing, distributed data bases and business intelligence;
[iv] areas where new types of database are emerging (e.g., Spatial data, Big Data, No SQL databases).Skills and attributes
[ii] map the conceptual model to a logical model for implementation in a relational data base;
[iii] analyse, design and critically evaluate databases in selected current and emerging topics;
Aim: To provide exposure to challenges and trade-offs posed to various data modelling and querying approaches, ranging from schema based (e.g., relational design and SQL) to flexible designs (e.g., MongoDB documents and querying, RDF and SPARQL),with particular emphasis on queries for data analytics.
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 data set, 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 data set, as discussed and practiced on this module: A) relational database and SQL, B) schema-less collections of documents with MongoDB, C) knowledge graphs with Graph DB.
There fore, 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 data set needs to be transformed into suitable data sets, collections of documents and knowledge graphs, respectively, prior to writing the tasked queries.
Submission Requirements:
You are required to submit different plain text files containing your code and answers, where needed, via the appropriate assignment links Study net/Canvas. Each file should include your results and answers for the related part and should be submitted by following the corresponding link for this assignment as depicted by the figure below:
For instance, the submitted file(s) containing results and answers for part A (SQL related part)should be submitted by following the link CWK-SQL-Part, whereas the submitted file(s)containing results and answers for part B (schema-less collections of documents) should be submitted by following the link CWK-Mongo-DB-Part. Finally, part C related submissions should be accomplished by following the link CWL-Graph-RDF-Part.
Please note that only appropriate file formats will be accepted (see ‘deliverable’ details below) as we will run your queries and import the created datasets for parts B and C, in order to mark your submission. Failure to do so will result to a zero mark.
6COM1048 Advanced Database Topics Assignment – Hertfordshire University UK.
Marks awarded for:
Please see the detailed marking scheme below.
Type of Feedback to be given for this assignment:
Feedback will be in the form of a score and tutor comments on your work.Feedback will be made available via Study net with a rubric for a detailed mark explanation.
Your tasks :
As part of this assignment, you must perform the following tasks:
A. Schema-Based (Relational Data Model And Sql Related Tasks)
Deliverables (two files in total):
For all deliverables in this part, you must include your Oracle username. Failure to do so will result in a deduction of 5 marks.
For task 1, nothing to submit, however, we will check upon your Oracle account for the correct execution of this task. Failure to do so will result to zero marks.
For task 2, a SQL script containing the executable SQL queries for tasks a to g. Please note that we should be able to import this file into your Oracle account and run the queries. Failure to do so will result to zero marks.
For Tasks 3 and 4, a SQL script containing the DDL statements with which the relational schema will be redesigned and/or populated with appropriate data. Your answer for task 4 should be included as a comment within the same script. The script must be a plain text file (extensions .sql, .txt). You can find examples of such scripts on Canvas units for the practical exercises of the module, e.g., Teachers .sql.
Please note that we should be able to import this file into your Oracle account and run the queries. Failure to do so will result to zero marks.
Environment: You should use your Oracle database account to work out solutions for these tasks.
Related study material: Units 1 to 7.
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.
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.
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.
c. For each monthly payment count the number of properties for each property type.
d. Show the number of leases per monthly rental amount.
e. Show the number of leases per monthly rental amount, and the total number of leases overall.
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.
g. Show all combinations of numbers of lease, monthly rentals and payment methods.
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.
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.
B.FLEXIBLE AND QUASI SCHEMA-LESS DESIGN BASED MODELLING AND QUERYING (MongoDB Collection of BSON/JSON Documents)
Deliverables (two files in total):
For task 1, a script containing the collection(s) of documents, which replicate the data from the two tables prop_for_rent and prop_type only, as of the script property.sql from the previous task A. Depending on the queries, you may decide to create one collection for both datasets, or two separate collections of documents. The script must be a plain text file (extensions .json, .txt). You can find examples of such scripts on Canvas units for the practical exercises of the module, e.g., unit 8 with the flights.json file as well as the very large persons.json file.
For task 2, ideally a JavaScript (.js extension) file containing the queries to be executed from within MongoDB CLI (Command Language Interface). Your query must follow the formatting and syntactic examples as discussed and practiced in units 8 & 9 (see also following example):
Environment: MongoDB Compass client for connecting to MongoDB on the cloud, with similar installation and connection instructions as discussed in units 8 & 9. Correctness of the created collection(s) of documents can be tested by firstly creating your collection(s) within a selected database, with Read/Write access rights, and, subsequently by importing your collection(s) (Pathway: select created collection -> ADD DATA button -> Import File -> Select File -> Select Input File Type -> JSON). Once your collections have been imported, use the MONGOSH sub-window to write and test your queries.
Connection string to be used:
Databases to be used: Depending on your timetabled tutorial slot, please do use one of the following databases to create and test your collection(s) of documents. If you are not allocated to any of these classes, please use the Test database.
If you are not allocated to any of these classes, please use the Test database.
A Highly recommended editor is the Sublime Text editor, for both json and JavaScript files.
Related study material: Units 8 and 9.
Tasks:
1.Transform the data from the relations “prop_for_ rent” and “prop_type” into a MongoDB’s collection(s) (documents rather than relations).
2.Subsequently, write queries by using MongoDB’s aggregation framework to answer the same questions as of A.3.a and A.3.b. Please note that same aggregate results are expected.
a. For each property type count the number of properties in each type.
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.
c.FLEXIBLE AND QUASI SCHEMA-LESS DESIGN BASED MODELLING AND QUERYING (RDF and SPARQL)
Deliverables :
For task 1: An XML script containing the knowledge graph as the result from transforming the data from the two tables prop_for_rent and prop_type only (script property .sql from the previous task A). The script must be a plain text (.txt) or XML file (.xml extension). You can find examples of such scripts on Canvas units for the practical exercises of the module, e.g., unit 11 with FOAFexample.txt and pizza .xml files.
task 2: A plain text file (.txt extension) containing the SPARQL queries. Examples of such queries can be found in the document “Activities U11 – Updated”, Unit 11.
Environment: Correctness of your XML script will be checked either by submitting or after a successful import into Graph DB (free desktop version) as discussed and practiced in units 10 & 11. Importing your knowledge graph into Graph DB requires creation and connection to a specific repository, which needs to be created by yourself. Correctness of your SPARQL queries will be checked within the Graph DB work bench.
6COM1048 Advanced Database Topics Assignment – Hertfordshire University UK.
Related study material: Units 10 and 11.
1.Transform the data from the relations “prop_for_ rent” and “prop_type” into collections of RDF triples (subject-predicate-object triples), assuming that every single property for rent and their associated tenants become URIs pointing at contents of Web pages. You may use either real URLs or fake ones for your “base” XML name space.
2.Subsequently, write SPARQL queries to answer the same questions as of A.3.a and A.3.b above. Please note that same aggregate results are expected.
a. For each property type count the number of properties in each type.
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.
ORDER This 6COM1048 Advanced Database Topics Assignment NOW And Get Instant Discount
Read More :-
6COM1033 Computer Systems Security Assignment-Hertfordshire University
