Overview

For this assignment, you will be selecting an online JSON data source, create a database model that will hold the data, and import the JSON data into MySQL. In addition to the tables, you will also create SQL Views, and use the views in your REST read queries. You will also create a stored procedure.

Assignment

Step 1: Identify JSON Data Source

Reddit contains a large number of categorized topics. Find a topic of interest by navigating to the reddit websit Links to an external site., enter a topic of interest, and search for a reddit in the search results Communities and Users section. You will need to find a reddit with at least 20 postings, so select a reddit with a large number of subscribers.

Each reddit URL consists of the reddit domain name, followed by an r, then the name of the reddit. For example, for the cyber security reddit: www.reddit.com/r/cybersecurity Links to an external site.. The “r” indicates that the reddit is a community.

Every reddit community includes a JSON version of the postings. To view the JSON version, remove the trailing slash, and append .json. The cyber security reddit becomes: www.reddit.com/r/cybersecurity.jso Links to an external site.. The display of the JSON data will depend upon the browser you are using. Some browsers will help with JSON formatting.

Important: The JSON data appears to only be available for communities, and not individual posts or even a list of communities about a specific topic.

You will need to extract the raw JSON data, and save the data to a file called cit381-assign07-reddit.json.

Tip: If your browser does not help present the JSON data to help you understand the JSON structure, you should find an online tool or application to help with the JSON data structure visualization (e.g. JSON Editor Onlin Links to an external site.). By default Chrome didn’t appear to help, but Firefox created a navigable JSON layout.

Step 2: Create a model to hold your JSON data

You will need to design a database to store some of the reddit information. Analyze the JSON, and determine what tables you will need, and the primary keys for each table. You must store enough information to provide most of the information visible for a “Listing” reddit posting. You will need to look at the reddit in your browser, and find the information in the JSON, to better understand the data.

Hint: Look for the children array property.

The required data items are:

• Title of posting
• Author name that could be used to find the author (analyze the posting to determine which property is used to lookup a user)
• Date/time of posting (stored as UNIX epoch, use Epoch ConverterLinks to an external site.to convert epoch to human readable date/time)
• Actual reddit link
• Any links to an external or original article
• Number of comments
• Number of cross posts
• Link to comments (analyze the posting to determine what property contains the comment link)

Note: Include the comments table in your schema, but don’t worry about populating this table. Only individual reddit posting contain links to comments, so will not be parsing out this data.

You will also need to store user names in a user table, and relate the user table to the posting table.

Save your database model as cit381-assign07-schema.sql. Export an image of your schema as a png file and save as cit381-assign07-schema.png.

Step 3: Manually enter a few rows of JSON data into MySQL

You will need to eventually load at least 10 rows of your reddit JSON into your database tables, but for now, manually enter in a couple of entries.

Export the manually entered data from all of the tables using the Export recordset to an external file button above the results in the Result Grid. You will need to export each table as a csv file, then combine all of the data into a single file called cit381-assign07-data.csv.

Step 4: Create SQL View

Create one meaningful view for your data. Do not simply create a select statement that returns all columns from one table: you MUST use a join, and include a computed field, such as the age of the posting.

Save the SQL to create the view in a file called cit381-assign07-view.sql.

Step 5: Create stored procedures

Create the following two stored procedures without using a view, only base tables:

1. The first stored procedure inserts user names and any other user information you can find into the user table.
2. The second stored procedure inserts data into the posting table and must include a user name value in addition to all of the data required by the posting table.

Save the SQL to create both stored procedures in a file called cit381-assign07-procedures.sql.

Deliverables

You will need to collect all of the files for this assignment, and create a single compressed/ZIP folder called cit381-assign07.zip that contains all six files. Upload this single file to Canvas.

Below is the list of files that are required for this assignment.

1. (5) cit381-assign07-reddit.json
2. (15) cit381-assign07-schema.sql
3. (5) cit381-assign07-schema.png
4. (5) cit381-assign07-data.csv
5. (5) cit381-assign07-view.sql
6. (10) cit381-assign07-procedures.sql