CET341 – ADVANCED DATA TECHNOLOGIES (2022/23)

Assignment Two

The following learning outcomes will be assessed:

1. Demonstrate critical appreciation of current and new data models and database systems for traditional and Big Data systems.
2. Appraise current and emerging trends in database systems and their application in the real world.
3. Design and develop database systems using a range of different database development tools.
4. Evidence of critical evaluation of the major developments and issues of databases within the database arena and their support in various application areas.

Important Information

You are required to submit your work within the bounds of the University Infringement of Assessment Regulations (see your Programme Guide). Plagiarism, paraphrasing and downloading large amounts of information from external sources, will not be tolerated, and will be dealt with severely. You should make full use of any source material, which would normally be an occasional sentence and/or paragraph (referenced) followed by your own critical analysis/evaluation. You will receive no marks for work that is not your own. Your work may be subject to checks for originality which can include use of an electronic plagiarism detection service.

Where you are asked to submit an individual piece of work, the work must be entirely your own. The safety of your assessments is your responsibility. You must not permit another student access to your work.

Where referencing is required, unless otherwise stated, the Harvard referencing system must be used (see your Programme Guide).

Please ensure that you retain a duplicate of your assignment. We are required to send samples of student work to the external examiners for moderation purposes. It will also safeguard in the unlikely event of your work going astray.

Submission Date and Time	As specified on Canvas
Submission Location	Electronic submission via Canvas

This is the second assignment for this module and is worth 65% of the overall module mark. All work must be done individually.

Your assignment is to design, develop and query a database using multiple DBMSs, i.e., Oracle and MongoDB, to provide a brief comparison of relational and NoSQL document store database technologies.

If you wish you may use PostgreSQL instead of Oracle, and your solutions should be PostgreSQL equivalents to the relevant questions.

For a case study of your choice, perform the following tasks:

1. Provide a UML class diagram (NOT an ER diagram) showing the main entities and attributes in your design and the relationships between them. The UML class diagram MUST be data model independent, i.e., you are showing entities in the system, NOT tables. Ensure that you include complex relationships (e.g., inheritance hierarchies, aggregation, etc.) in your design.

• Create a complete SQL script file that will run in Oracle to implement the design from task 1 as an object-relational database. Ensure your SQL script file runs without errors, i.e., it drops then creates the database objects (tables, user defined types, etc.) and inserts sufficient sample data for your queries below. Make sure that you provide the complete SQL script file within your submitted report. Any auto-generated code exported from the DBMS will be given a mark of 0 for this part.

Provide a brief critical discussion of no more than 200 words identifying the object features that you have implemented in your database and how you implemented them.

• Develop a set of documents using MongoDB to develop the equivalent document store database for your design in task 1. Ensure you make use of appropriate document store features including, for example, nested documents, arrays, etc. Do not simply replicate the Oracle implementation but make full use of MongoDB document store features. Make sure the sample data you use matches the data stored in your database from task 2. Make sure you provide the complete script file for creating your MongoDB database (i.e. the db.collection_name.insert commands), do not just provide a list of documents outputted from the interface, otherwise you will get 0 marks for this part.

Provide a brief critical discussion, of no more than 200 words, identifying how you have incorporated document store features into your database.

• Develop the following queries in Oracle SQL on your database from task 2 above. Ensure that you develop queries which include:
  • A join of three or more tables – you must use multiple types of join operations in this query (e.g., inner join, left/right/full outer joins, etc.) and the query must include a restriction on the rows selected.
  • A query which uses one (or more) of the UNION, DIFFERENCE or INTERSECT operators.
  • A query which requires use of either a nested table, sub-types (or sub-tables if using PostgreSQL) or similar. Note: sub-queries are NOT acceptable as they do not meet the requirements of this task.
  • A query using temporal features (e.g., timestamps, intervals, etc.) of Oracle SQL.

• A query using OLAP (e.g., ROLLUP, CUBE, PARTITION) features of Oracle SQL.

You should submit FIVE SQL queries maximum, i.e., one for (a), one for (b), etc. Any other queries will not be marked. This means, for example, that if you submit two queries for part

(a) then only the first query will be marked. It is required that at least one of your queries will be embedded within a stored procedure, and one query will include a stored function.

• Using MongoDB, implement queries which produce an equivalent result (i.e. returns the same data) to those in task 4a-e above. If you are unable to complete an identical query in MongoDB, then you should write a query which is as similar as possible in functionality to the SQL query. Ensure you use the native MongoDB query language and NOT SQL for your MongoDB queries.

Ensure that you cite appropriate academic references (i.e., research papers) where relevant in your discussions.

You need to ensure therefore that your case study is of sufficient detail to be able to complete tasks 1-5 above. As a suggestion, you should aim to have between four and six entities within your design which you would then represent in your two implementations using tables and document collections as appropriate. You must consider complex relationships between the entities such as inheritance and aggregation in your design.

Marks given will consider the challenge of your case study and the challenge of the queries that you develop. For example, a query using temporal functions such as extract/intervals will gain more marks than a simple query on a date which is not at the appropriate level for this module.

Submission Requirements

Your submission for this assignment is a technical report which includes your solutions to tasks 1-5 above.

You are required to submit the following three files within one ZIP file:

1. A technical report which includes your solutions to tasks 1-5 above. A template (which you must use) for this document is given in appendix 1. All SQL and MongoDB queries must be included as well as screenshots to demonstrate that all your database creation, inserts and queries work.  The file must be converted to PDF for submission.
2. An SQL script file (with a .sql extension) containing all of the code for task 2.
3. An equivalent script file (with a .js extension) containing all the MongoDB code for task 3.

Ensure that you use your full name as the name of the ZIP file (i.e., FIRST_NAME_LASTNAME.zip).

Staying within the bounds of University of Sunderland regulations, you should make full use of any source material available to you (particularly journals, conference papers or technical reports). Any form of academic misconduct, including plagiarism, collusion and paraphrasing, will not be tolerated, and will be dealt with under University Infringement regulations. If you are unclear about how to reference correctly then please ask.

Marking Scheme

The marks breakdown is as follows:

Task		Weight	Exceeds expected standard	Meets expected standard	Just below expected standard	Fails to meet expected standard	Not attempted
1	UML class diagram	5%	Case study at a very good level and challenge. UML class	Case study is of an appropriate level and challenge. UML class	Case study is almost at an appropriate level and challenge. UML class	Case study is not at an appropriate level and lacks challenge. UML	Not attempted or very poor attempt.
			diagram correctly	diagram correctly	diagram correctly	class diagram provided
			describes the case	describes the case	describes the case	but does not suitably
			study, notation is used	study, notation is used	study, notation is mostly	describe the case study,
			correctly, clearly	correctly, clearly	correct, but attributes	and incorrect notation is
			showing attributes and	showing attributes and	and entities are not	used. Attributes and
			entities with a very good	entities with some	clearly shown with	entities are not clearly
			attempt to embed	attempt to embed	limited attempt to embed	shown and no complex
			complex relationships.	complex relationships.	complex relationships.	relationships.
			A good number of	Suitable number of	Suitable number of	Unsuitable number of
			entities is described to	entities is described.	entities is described.	entities is described.
			demonstrate the
			complexity of the
			scenario.
2	Oracle SQL database creation and data insertion	15%	Correctly working SQL script file which can be run without error and completely matches the scenario described, utilising some advanced features of Oracle. Database integrity rules are correctly implemented including all types of integrity constraint. Very good use good use of object features (e.g. inheritance/user-defined types/aggregation) incorporated. An excellent set of screenshots are provided to fully demonstrate that the code works. An excellent set of sample data is provided. A stored procedure and function have been included.	Correctly working SQL script file which can be run without error and matches the scenario described. Database integrity rules are correctly implemented. Good use of object features (e.g. inheritance/user-defined types/aggregation) incorporated. Screenshots are provided to fully demonstrate that the code works. A good set of sample data is provided. A stored procedure and/or function has been included.   Good discussion provided. Good use of references.	SQL script file developed and mostly error free but does not fully match scenario. Not all database integrity rules correctly specified. No/limited object features (e.g. inheritance/user-defined types/aggregation) incorporated. Some screenshots are provided but more could have been provided to clearly demonstrate that that system works. Some sample data is provided.   Limited discussion provided. No/limited use of references	SQL script file developed but does not match scenario and contains errors. No, or limited, integrity rules specified. No, or very limited, screenshots provided.   No, or limited, sample data is provided. No discussion provided.	Not attempted or very poor attempt.

			Very good discussion with very good use of references.
3	MongoDB database creation and data insertion	15%	MongoDB database code provided which correctly implements the case study using appropriate and some	MongoDB database code provided which correctly implements the case study using appropriate document	Good attempt at the MongoDB database but shows some issues in understanding of how to implement a document	Poor attempt at the MongoDB database which shows lack of understanding of how to create a document	Not attempted or very poor attempt.
			advanced document	store facilities.	store. Code is mostly	store. Code contains
			store facilities	MongoDB code will work	error free and a good	many errors. Limited or
			throughout. MongoDB	without error. Sample	matching data set has	no sample data
			code will work without	data matches sample	been provided. Some	provided. No, or limited,
			error. Sample data	data provided in Oracle	screenshots provided.	screenshots provided.
			matches sample data	but correctly uses	Limited discussion	No discussion provided.
			provided in Oracle but	MongoDB concepts.	provided. No/limited
			correctly uses some	Screenshots have been	use of references
			advanced MongoDB	provided to demonstrate
			concepts. Screenshots	that code works. Good
			have been provided to	discussion provided.
			fully demonstrate that	Good use of references.
			code works. Very good
			discussion with very
			good use of references.
4	Oracle queries	15%	All Oracle queries developed, work and are	All Oracle queries developed, work, and	All queries attempted but may contain errors	Not all Oracle queries attempted and contain	Not attempted or very poor attempt.
			at an excellent level of	are at an appropriate	and not at an	errors. Not at an
			challenge. Natural	level of challenge.	appropriate level of	appropriate level of
			language descriptions	Natural language	challenge. Natural	challenge. No, or poor,
			are given which clearly	description of each	language description is	natural language
			describe each query.	query is given.	given but does not	description is given. No,
			Screenshots are given	Screenshots are given	clearly describe the	or limited, screenshots
			which show the correct	which show the correct	query. Screenshots are	provided.
			output from each query.	output from each query.	given which show data
					output.
5	MongoDB queries	15%	Excellent set of MongoDB queries provided fully using	4 or 5 MongoDB queries provided and are a good attempt to match the	Good attempt at 3 or 4 of the MongoDB queries, using MongoDB	Poor attempt at one of more of the MongoDB queries. Many errors in	Not attempted or very poor attempt.
			appropriate and some	Oracle queries provided,	constructs where	code. No, or few,
			advanced MongoDB	using appropriate	relevant. Some errors in	screenshots have been
			constructs. Full set of	MongoDB constructs.	code. Screenshots	provided.
			screenshots provided	Screenshots are	have been provided
			which clearly	provided to fully	which attempt to show
			demonstrate that the	demonstrate that the	that the queries work
			queries work with	queries work, showing	with appropriate results.
			complete results shown.	appropriate results.

Appendix 1 – CET341 Assignment 2 Structure Template

Task One:

Include the UML class diagram here

Task Two:

Include screenshots to demonstrate that ALL of your SQL code works. Provide discussion of no more than 200 words.

Task Three:

Include screenshots to demonstrate that ALL of your MongoDB code works. Provide discussion of no more than 200 words.

Tasks Four to Six

Complete the following tables:

Query a: A join of three or more tables – you should consider various types of join in this query (e.g. inner join, left/right/full outer joins, etc.) and the query must include a restriction on the rows selected   Provide a description of your query here
SQL code	MongoDB code
Insert the SQL query code here	Insert the MongoDB query code here

Screenshots
Insert a screenshot of the output from your SQL query here
Insert a screenshot of the output from your MongoDB query here

Query b: A query which uses one (or more) of the UNION, DIFFERENCE or INTERSECT operators.   Provide a description of your query here
SQL code	MongoDB code
Insert the SQL query code here	Insert the MongoDB query code here
Screenshots
Insert a screenshot of the output from your SQL query here
Insert a screenshot of the output from your MongoDB query here

Query c: A query which requires use of either a nested table or subtypes   Provide a description of your query here
SQL code	MongoDB code
Insert the SQL query code here	Insert the MongoDB query code here

Screenshots
Insert a screenshot of the output from your SQL query here
Insert a screenshot of the output from your MongoDB query here

Query d: A query using temporal features (e.g., timestamps, intervals, etc.) of Oracle SQL   Provide a description of your query here
SQL code	MongoDB code
Insert the SQL query code here	Insert the MongoDB query code here
Screenshots
Insert a screenshot of the output from your SQL query here
Insert a screenshot of the output from your MongoDB query here

Query e: A query using OLAP (e.g., ROLLUP, CUBE, PARTITION) features of Oracle SQL   Provide a description of your query here
SQL code	MongoDB code
Insert the SQL query code here	Insert the MongoDB query code here
Screenshots
Insert a screenshot of the output from your SQL query here
Insert a screenshot of the output from your MongoDB query here

References

Provide your reference list in Harvard format.

Get expert help for CET341 – ADVANCED DATA TECHNOLOGIES and many more. 24X7 help, plag free solution. Order online now!