CMT655程序代做、代写program语言编程、SQL程序代写

日期：2021-05-25 11:19

Cardiff School of Computer Science and Informatics

Module Code: CMT655

Module Title: Manipulating and Exploiting Data

Assessment Title: Course Portfolio

Assessment Number: 1

Date Set: Friday 26th March 2021

Submission Date and Time: May 31st 2021 9:30

Return Date: June 30th 2021

This assignment is worth 100% of the total marks available for this module. If coursework

is submitted late (and where there are no extenuating circumstances):

1 If the assessment is submitted no later than 24 hours after the deadline, the mark

for the assessment will be capped at the minimum pass mark;

2 If the assessment is submitted more than 24 hours after the deadline, a mark of 0

will be given for the assessment.

Your submission must include the official Coursework Submission Cover sheet, which can

be found here:

https://docs.cs.cf.ac.uk/downloads/coursework/Coversheet.pdf

Submission Instructions

Your coursework should be submitted via Learning Central by the above deadline. It

consists of a portfolio divided in three assessments.

Assessment (1) consists of a set of exercises. The final deliverable consists of two

jupyter notebooks.

Assessment (2) is a machine-learning powered web service which is able to

train, evaluate and run predictions on unseen data, as well as storing model

configuration and results in a database. The deliverable is a zip file with the

application source code, a README.txt file and (optionally) a requirements.txt

file which lists dependencies and versions the app would require to run.

Assessment (3) is a reflective report (up to 2,000 words) describing solutions,

design choices and a reflection on the main challenges and ethical considerations

addressed during the development of solutions for assessments (1) and (2).

You have to upload the following files:

Any deviation from the submission instructions above (including the number and types of

files submitted) may result in a mark of zero for the assessment or question part.

Assignment

In this portfolio, students demonstrate their familiarity with the topics covered in the module

via three separate assessments.

Deliverable

Assessment 1

The deliverable for Assessment 1 consists of 2 jupyter notebook (.ipynb) files. They will be

submitted with all the output cells executed in a fresh run (i.e., Kernel -> Restart and run

all). 20 marks.

Assessment 2

The deliverable for Assessment 2 will be a zip file containing the webapp code, a

README.txt and an optional requirements.txt file, which will list the dependencies the app

requires. 25 marks.

Assessment 3

The deliverable for Assessment 3 will be a PDF file based on the .docx template provided

for this assessment in the starter package, available at Learning Central. 55 marks.

Description Type Name

Cover sheet  Compulsory One PDF (.pdf) file [student number].pdf

Assessment

1

Compulsory One Jupyter notebook (.ipynb) assessment1_db_creation_[stude

nt number].ipynb

Assessment

1

Compulsory One Jupyter notebook (.ipynb) assessment1_queries_[student

number].ipynb

Assessment

2

Compulsory One zip file (.zip) assessment2_webapp_[student

number].zip

Assessment

3

Compulsory One PDF (.pdf) file assessment3_report_[student

number].pdf

Assessment 1

In Assessment 1, students solve two main types of challenges. These challenges are: (1)

data modeling and (2) database querying.

DATA MODELING AND QUERYING (20 Marks)

assessment1_db_creation_[student number].ipynb

assessment1_queries_[student number].ipynb

1. Data modeling (8 marks)

You are given an initial .csv dataset from Reddit (data_portfolio_21.csv,

available in the starter package in Learning Central). This data dump contains posts

extracted from Covid-related subreddits, as well as random subreddits. Your first

task is to process this dump and design, create and implement a relational

(MySQL) database, which you will then populate with all the posts and related data.

This dataset has information about three entities: posts, users and subreddits.

The column names are self-explanatory: columns starting with the prefix user_

describe users, those starting with the prefix subr_ describe subreddits, the column

subreddit is the subreddit name, and the rest of the columns are post attributes

(author, post date, post title and text, number of comments, score, favorited by,

etc.).

What to implement: Start from the notebook assessment1_db_creation_[student

number].ipynb, replacing [student number] with your student number. Implement

the following (not necessarily in this order):

-- Python logic for reading in the data. [2 marks]

-- SQL code for creating tables. [3 marks]

-- SQL code for populating tables. [3 marks]

Use comments or markdown along the way to explain how you dealt with

issues such as missing data, non-standard data types, multivalued columns, etc.

You are not required to explain the database design (normalization, integrity,

constraints, etc) process in this notebook, as there is a dedicated part of the report

in Assessment 3 for this. However, you can include pointers to design choices for

facilitating the understanding of your implementation.

All your code should be self-contained in Python code, and therefore you will have

to rely on a MySQL library for executing SQL statements and queries. Please use

pymysql, the one we have used in class.

You should submit your notebook with all the cells executed, from start to

finish, in a fresh run (i.e., first cell number should be [1], second [2], etc.). You can

achieve this by selecting Kernel -> Restart and run all. At the end of the run, your

notebook should have populated a database in the university server which you will

have created exclusively for this coursework.

2. Querying (12 marks)

You are given a set of questions in natural language, for which you must implement

queries to find the answer. While the queries will be answered in the provided

jupyter notebook, they will have to be written in SQL, i.e., you cannot use Python to

solve them.

What to implement: Start from the notebook assessment1_queries_[student

number].ipynb, replacing [student number] with your student number. All the logic

should be contained inside the provided (empty) functions. Then, a call to each

function should show the output of these queries. You are also required to submit

your notebook after a fresh run (Kernel -> Restart and run all).

The questions are:

1 - Users with highest scores over time [0.5 marks]

● Implement a query that returns the users with the highest aggregate scores (over all their

posts) for the whole dataset. You should restrict your results to only those whose aggregated

score is above 10,000 points, in descending order. Your query should return two columns:

username and aggr_scores.

2 - Favorite subreddits with numbers but not 19 [0.5 marks]

● Implement a query that returns the set of subreddit names who have been favorited at least

once and that contain any number in their name, but you should exclude those with the digit

'19', as we want to filter out COVID-19 subreddit names. Your query should only return one

column: subreddit.

3 - Most active users who add subreddits to their favorites. [0.5 marks]

● Implement a query that returns the top 20 users in terms of the number of subreddits they have

favorited. Since several users have favorited the same number of subreddits, you need to

order your results, first, by number of favourites per user, and secondly, alphabetically by user

name. The alphabetical order should be, first any number, then A-Z (irrespective of case). Your

query should return two columns: username and numb_favs.

4 - Awarded posts [0.5 marks]

● Implement a query that returns the number of posts who have received at least one award.

Your query should return only one value.

5 - Find Covid subreddits in name and description. [1 mark]

● Implement a query that retrieves the name and description of all subreddits where the name

starts with covid or corona and the description contains covid anywhere. The returned table

should have two columns: name and description.

6 - Find users in haystack [1 mark]

● Implement a query that retrieves only the names of those users who have at least 3 posts with

the same score as their number of comments, and their username contains the string meme

anywhere. Your returned table should contain only one column: username.

7 - Subreddits with the highest average upvote ratio [1 mark]

● Implement a query that shows the 10 top subreddits in terms of the average upvote ratio of the

users that posted in them. Your query should return two columns: subr_name and

avg_upv_ratio.

8 - What are the chances [1 mark]

● Implement a query that finds those posts whose length (in number of characters) is exactly the

same as the length of the description of the subreddit in which they were posted on. You

should retrieve the following columns: subreddit_name, posting_user, user_registered_at,

post_full_text, post_description and dif (which should show the difference in characters

between the subreddit description and the post.

9 - Most active December 2020 days. [1 mark]

● Write a query that retrieves only a ranked list of the most prolific days in December 2020,

prolific measured in number of posts per day. Your query should return those days in a

single-column table (column name post_day) in the format YYYY-MM-DD.

10 - Top 'covid'-mentioning users. [1 mark]

● Retrieve the top 5 users in terms of how often they have mentioned the term 'covid' in their

posts. Your query should return two columns: username and total_count. You will consider an

occurrence of the word 'covid' only when it appears before and after a whitespace (i.e.,

<space>covid<space>) and irrespective of case (both <space>Covid<space> and

<space>covid<space> would be valid hits).

11 - Top 10 users whose posts reached the most users, but only in their favorite subreddits. [2

marks]

● Write a query to retrieve a list of 10 users sorted in descending order by the number of users

their posts reached, considering only the subset of users belonging to their favourite

subreddits. Your query must return only one column: username.

12 - Users with high score for their posts. [2 marks]

● Retrieve the number of users with an average score for their posts which is higher than the

average score for the posts in our dataset. Your query should return only one result, under the

column result.

Assessment 2

In Assessment 2, you implement a Flask application which manages a machine-learning

based text classifier and speaks to both MongDB and MySQL databases.

WEBAPP (25 Marks)

assessment2_webapp_[student number].zip

In this assessment the goal is to set up a web service based on Flask which will sit on top

of the database you built in Assessment 1, and will have a machine learning component.

Specifically, the app will have several functionalities for training, evaluating and deploying

a covid-or-not classifier, which will take as input a message posted in social media (e.g.,

Reddit), and predicts whether it is about Covid-19 or not.

What to implement: You will pull your data from the MySQL database that you

implemented in Assessment 1. Then, your task is to develop a web application based on

Flask which will have the following functionalities:

a) Run a classification experiment and store results, models and configuration in a

MongoDB database;

b) Retrieve results for the experiments done so far, ranked based on a criterion of

your choice; and

c) Perform inference, i.e., given a piece of text provided by the user, predict whether

it is about Covid-19 or not.

You are provided with an ‘empty’ skeleton which contains starter HTML and Python code.

Your task is to implement the backend logic following the detailed instructions below. The

provided skeleton has an index.html page which has the layout shown in Figure 1:

Figure 1: Landing page of the ML-powered Flask application.

Then, your task will be to implement the backend logic that is triggered when each of the

three buttons shown in Figure 1 are clicked on. The logic corresponding to each of these

buttons is explained in detail below.

1. Run a classification experiment [15 marks]

In this exercise, you have to implement the following workflow:

a) Reset, create and verify two VIEWS, which you will call training_data and

test_data. These VIEWS should contain non-overlapping posts which you will use

to train and evaluate your classifier. The logic will be implemented in the following

(empty) functions, located in the main.py script, provided in the starter package:

- reset_views() - Drop (if they exist) and create the views. [1 mark]

- create_training_view() - Create an SQL VIEW for training data. This

view will have two columns, the text and the label. It is up to you to decide the size

of the dataset and the proportion of Covid vs. non-Covid posts. And also which part

of the post you take (title, body or both). You will justify these choices in

Assessment 3. We will make the strong assumption that any post submitted to a

Covid-related subreddit will be about Covid-19, and that it will be non-related to

Covid if it is submitted somewhere else. [3 marks]

- create_test_view() - Create view for test data. This view will have two

columns, the text and the label. The same principles as in the previous case apply.

[3 marks]

- check_views() - Retrieve all records in training_data and test_data, and

print to the console their size. This is a small sanity check. [1 mark]

b) Retrieve data from the views you created in step 1, train and evaluate a

classifier, and return the results as a JSON object that is rendered in the browser.

Implement this functionality in the experiment() method, again in the main.py

script. It is up to you to decide on the classifier and its configuration. There is an

opportunity to reflect on these choices in Assessment 3. [5 marks]

c) Take the model binaries, model configuration and the classification results you

obtained in step (b), as well as the time in which the experiment was performed,

and store this information in a dedicated collection. This exercise is open, i.e., there

is no suggested format on how to store this data, what information to store for your

models or the evaluation metrics you use. There is an opportunity to reflect on

these choices in Assessment 3. [2 marks]

2. Retrieve information on the experiments conducted so far (5 marks)

a) In this exercise, you query the collection you implemented in step 1c, and show

the top 3 experiments based on a certain criterion (best scoring according to

metric X, the most recent experiments, the fastest experiments in training time,

etc.). Your results will be returned as JSON objects and rendered in the browser. [5

marks]

3. Implement a ‘covid-or-not’ predictor (5 marks)

a) In this exercise, you implement a functionality for predicting ‘on the fly’ whether a

piece of text is Covid-19-related or not. To this end, you will use the top-ranked

model according to the ranking you implemented in step 2a. This model will then be

applied to the input text and the results will be rendered in the browser as a JSON

object with the format:

{

"input_text": some_input_text,

"prediction": the_prediction_of_your_classifier

}. [5 marks]

Assessment 3

Report (55 Marks)

assessment3_report_[student number].pdf

In Assessment 3, you write a technical report on Assessments 1 and 2, and discuss

ethical, legal and social implications of the development of this Covid-19 application in the

context of the UK Data Ethics Framework. You are strongly encouraged to follow a

scholarly approach, e.g., with peer-reviewed references as support, and with additional

empirical evidence (your own tests) for justifying your decisions (e.g., performance or

physical storage for DBMS, training time or accuracy for the ML webapp solution).

Maximum 2,000 words (not counting references, index and table and figure captions).

This report should cover the following aspects, discussing challenges and problems

encountered and the solutions implemented to overcome them. The mark will be divided

between 3 expected sections:

o [3a] Database Creation (DB choice, design, etc.), i.e., the research and

findings stemming from the development of Assessment 1. Specifically, you

should discuss any business rules that can be inferred from the dataset

(reverse-engineering), normalization (identifying partial and transitive

dependencies, if any, unnormalized relations, etc.), data integrity and

constraints, bad data, etc. Moreover, the expectation is that any design decision

(or lack thereof) will be empirically (e.g., with performance tests) and/or

theoretically (pointing to peer-reviewed publications) supported. [20 Marks]

o [3b] ML Application, explaining the implementation of the training and test

VIEWS; the ML algorithm chosen (based on main features, hyperparameters

used in the application, training speed as opposed to other alternatives, etc);

evaluation metrics; the overall logic followed by the app for storing and retrieving

experimental results; and finally any further details that may be relevant for the

‘covid-or-not’ inference functionality. You should also discuss the rationale

behind the MongoDB interaction with pointers both to the database and the code

that interacts with it. [20 Marks]

o [3c] Ethics and Bias in Data-driven Solutions in the specific context of this

dataset and the broader area of application of this project (automatic

categorization of social media content to enable easier screening of public

opinion). You should map your discussion to one of the five actions outlined

in the UK’s Data Ethics Framework. You should prioritize the action that, in your

opinion, is the weakest. Then, justify your choice by critically analyzing the three

key principles outlined in the Framework, namely transparency, accountability

and fairness. Finally, you should propose one solution that explicitly addresses

one point related to one of these three principles, reflecting on how your solution

would improve the data cycle in this particular use case. [15 Marks]

Learning Outcomes Assessed

This coursework covers the 7 LOs listed in the module description.

Criteria for assessment

Credit will be awarded against the following criteria.

Assessment 1 (20 Marks) Criteria for assessment are detailed below:

Data Modeling (8 marks)

Criteria Fail (0-50%) Pass

(50-59%)

Merit

(60-69%)

Distinction

(70-100%)

Functionality

and quality

Faulty

application,

with unclear

pipeline. Parts

of the original

dataset are

missing (either

whole entities

or their

attributes).

Data types

wrongly

defined in the

DDL

statements. No

implementation

of PK/FK

constraints.

Notebook

poorly

structured.

Inefficient table

population

code.

Full dataset

loaded into a

MySQL

database,

some missing

values are

handled and

there is an

attempt to deal

with

multi-valued

columns.

Some data

types in DDL

statements are

appropriate.

Minimal PK/FK

constraints

implemented.

Notebook is

overall

self-explanator

y with some

parts difficult to

understand.

Somewhat

efficient table

population

code.

Pass and all

data types and

multivalued

columns

correctly

handled,

column data

types are

appropriate to

their range and

domain. Some

PK/FK

constraints

implemented.

Efficient and

well-document

code.

Notebook well

structured.

Merit and

excellent

management

of missing

data, type

recasting and

multivalued

columns. Fully

maintainable

DB, with DDL

and DML

statements

embedded in

high quality

SQL code that

evidences

research

beyond the

concepts seen

in class (e.g.,

Transaction

management

or Triggers).

Excellent

notebook

presentation,

with elegant

mix of Python

and SQL

Querying (12 marks)

Assessment 2 (25 Marks) Criteria for assessment are detailed below.

comments and

Markdown.

Criteria Fail (0-50%) Pass

(50-59%)

Merit

(60-69%)

Distinction

(70-100%)

Correctness Query not

implemented

or evidencing

limited

progress

towards a

solution.

Query

implemented

with wrong

output, but

evidencing

substantial

progress

towards a

solution.

Query

implemented

with correct

output, but with

minor mistakes

such as wrong

column

arrangement.

Query

implemented

and correct,

returning the

exact value or

table as

requested in

the question.

Criteria Fail (0-50%) Pass

(50-59%)

Merit

(60-69%)

Distinction

(70-100%)

Functionality

and quality

Faulty

application with

most or all

functionality

not

implemented.

Functional

application with

some of the

requested

functionalities

implemented.

Code for

defining

training and

test VIEWs is

functional, but

with some

errors. Browser

rendering of

results and

Mongo

insertion is

minimally

implemented,

but with some

errors. Covid

detector works

with some

Pass and all

requested

functionalities

implemented,

some of them

with high

quality.

Training and

test VIEWS are

correctly

implemented,

and

Python+Mongo

handling of

models, results

and model

parameters is

very good.

Covid

detection

works and is

well justified,

with almost all

Merit and all

functionalities

showing

excellent

quality, with

evidence of

maintainability

and

customization

beyond what

we have seen

in class (e.g.,

user can select

how much

training/test

data is used in

each

experiment).

Excellent

handling of

Python and

Mongo

interaction,

Assessment 3 (55 Marks) Criteria for assessment are detailed below, with specific

benchmarks for each section.

Database creation (20 marks)

inputs, and

criterion for

model

selection is

unclear.

inputs. with elegant

implementation

. Covid

detection

works

flawlessly with

any input.

Criteria Fail (0-50%) Pass

(50-59%)

Merit

(60-69%)

Distinction

(70-100%)

Correctness

and reflection

Missing or

limited

discussion of

the different

modules of the

pipeline. No or

minimal

discussion on

reverse-engine

ering,

normalization,

data integrity,

and

missing/multiav

lued data

handling.

Some

discussion

involving the

modules of the

pipeline.

Limited

discussion on

reverse-engine

ering,

normalization

is mentioned

but not

developed in

detail, data

integrity

constraints are

mentioned but

miss a clear

mapping with

the

implementation

, handling of

bad data is

mentioned but

without proper

reflection on

the decisions

made.

Pass and all

significant

discussion of

all modules in

the pipeline.

Business rules

fully

developed,

most

normalization

steps well

justified. Data

integrity

constraints well

mapped to the

implementation

and with a

reflective

component on

how they make

the db better

(preventing

anomalies,

ensuring

consistency,

etc.). Handling

of bada data is

mentioned and

reflected upon,

discussing

different

Merit and

excellent

discussion of

all modules.

Business rules

fully developed

and all

normalization

steps show

excellent

reflection. Data

integrity

constraints and

modular/maint

ainable SQL

code is

discussed.

Excellent and

scholarly

reference-supp

orted reflection

on data

integrity and

bad data

management.

ML Application (20 marks)

Ethics and Bias in Data-driven Solutions (15 marks)

approaches.

Criteria Fail (0-50%) Pass

(50-59%)

Merit

(60-69%)

Distinction

(70-100%)

Correctness

and reflection

Missing or

limited

discussion of

the different

modules of the

pipeline. No

justification for

the algorithm

chosen,

evaluation

metrics,

vectorization

procedure and

interaction with

browser and

MongoDB

database. No

discussion on

the

‘covid-or-not’

functionality.

Some

discussion

involving the

modules of the

pipeline. Some

analysis of the

algorithm

chosen, with

some

experiments

reported on

held-out data

referencing the

training/test

VIEWS. Some

reflection on

the

Python-Mongo

DB-browser

interaction.

‘Covid-or-not’

functionality

briefly

described.

Pass and

extensive

analysis of the

ML model

chosen, with

meaningful

comparison

with other

models,

vectorization

methods,

training/test

data splits

(with reference

to the VIEWS)

and with

ablation tests.

Good reflection

on the

Python-Mongo

DB-browser

interaction.

Significant

reflection on

the

‘Covid-or-not’

functionality,

showing

evidence of

previous

attempts.

Merit and

excellent

analysis of the

ML

component.

Extensive

analyses

across models,

vectorizers and

training/test

data splits

which

reference the

application’s

implementation

. Excellent

reflection on

the

Python-Mongo

DB-browser

interaction, and

extensive

reflection on

the

‘Covid-or-not’

functionality,

showing

evidence of

previous

attempts under

different

experimental

setups.

Criteria Fail (0-50%) Pass

(50-59%)

Merit

(60-69%)

Distinction

(70-100%)

Correctness

and reflection

Fails to

address the

actions and

One action

addressed

without proper

One action

addressed and

critically

One action

addressed and

critically

The grade range is divided in:

o Distinction (70-100%)

o Merit (60-69%)

o Pass (50-59%)

o Fail (0-50)

Feedback and suggestion for future learning

Feedback on your coursework will address the above criteria. Feedback and marks will be

returned between June 30rd and July 2

th via Learning Central. There will be opportunity for

individual feedback during an agreed time.

Feedback for this assignment will be useful for subsequent skills development, such as

database design, SQL and NoSQL, data analysis, machine learning and client-motivated

deliverables involving predictive analysis.

principles in

the

UK Data Ethics

Framework. No

or trivial

solution

provided.

justification.

Some

principles

addressed.

Trivial solution

provided that

does not

clearly map

with a specific

action or

principle.

justified. All

principles

addressed.

Some

supporting

peerreviewed

references.

Justified

solution based

on

limited

literature

and/or

empirical

results.

justified and

all principles

addressed, in

both cases

with

extensive use

of supporting

peer-reviewed

references.

Solution is

justified

extensively

with

references and

empirical

results.