wk4 - 10.18.22 Discover Patterns with MySQL

Post Reply
glegrady
Posts: 203
Joined: Wed Sep 22, 2010 12:26 pm

wk4 - 10.18.22 Discover Patterns with MySQL

Post by glegrady » Fri Sep 16, 2022 7:49 am

10.18.22 Discover Patterns with MySQL

The approach to discovering patterns is by searching the database without necessarily knowing what you will get, but eventually to arrive at something of interest that features unexpected patterns and/or unexpected associations. Concentrate on performance and volume over time based on ItemNumber, and the performance of the unique objects representing a title through barcode. (Updated Oct.18.2022 during crit)

Karl Yerkes, lecturer in the MAT program (also software developer, creative coder, and music instrument builder) did a simple project years ago that explored the serendipitous nature of a random search, asking the question "What are the first twelve items in the database that have the word "Happiness" in the title: https://www.mat.ucsb.edu/~g.legrady/aca ... reqmap.pdf Once he had the data for the twelve items he then did a frequency map of how the items performed over a period of time (JANUARY 1, 2005 to JANUARY 19, 2011). Through this process an interesting comparison is revealed as we see how the different items' history performed over time.

There are many things to explore, for instance, how long items remain in the collection, and then need to be replaced, what are gaps in itemnumber and how do they correlate to dates, etc. The goal of this assignment is to be granular and reveal details (Revised Oct 18, 2022).
-------------

The assignment is due on October 18, 2022. Present to the class the sequence of steps it took to arrive at an "interesting result" showing both the queries and the data results in csv.

This means that you will probably author numerous MySQL queries which will advance your skills in interacting with the database, asking innovative questions, with presentable results.
-------------

DOCUMENTATION IN THE STUDENT FORUM
Introduce your project with a brief and clear description in the student forum. Your pdf and csv files should have relevant titles, so we know what to expect before clicking on them.

Your pdf describing your project should be structured as follows:
Title,
your name
Abstract introduction
Detailed description
MySQL queries that can be copied
Analysis of results
References and URL links
-------------

REFERENCES:
"Frequency Pattern Mining", authors Aggarwal, Han, https://www.mat.ucsb.edu/~g.legrady/aca ... Mining.pdf
(link to the book https://link.springer.com/book/10.1007/ ... 19-07821-2)

A project by former MAT graduate student Anastasiya Lazavera about co-occurence:
https://vislab.mat.ucsb.edu/2015/anasta ... index.html
George Legrady
legrady@mat.ucsb.edu

nataliadubon
Posts: 15
Joined: Tue Mar 29, 2022 3:30 pm

Re: wk4 - 10.18.22 Discover Patterns with MySQL

Post by nataliadubon » Tue Oct 18, 2022 4:47 am

Abstract:
For this week’s student forum on patterns, I decided to explore the Seattle Public Library dataset to find statistical correlations between the progression of time and a subject’s total corresponding checkouts. For this, I’ve decided to choose all items that relate to Computer Science and/or Data Science. I essentially want to discover if I can make a predictable statistical linear model that will be able to answer my question regarding such correlation. All queries are cited along the descriptions/analysis and can also be found in its own section further below in the pdf. Note that I have chosen to use both SQL and R for this week’s assignment due to some limitations I find SQL to have in comparison to R regarding running statistical methods.

Report:
https://drive.google.com/file/d/1kEQfLT ... sp=sharing

All CSV files are attached to pdf
Attachments
Week 4_ Finding Patterns within Data (1).pdf
(457.99 KiB) Downloaded 44 times
Last edited by nataliadubon on Tue Oct 25, 2022 3:25 pm, edited 2 times in total.

ilianikiforov
Posts: 8
Joined: Tue Oct 04, 2022 10:24 am

Re: wk4 - 10.18.22 Discover Patterns with MySQL

Post by ilianikiforov » Tue Oct 18, 2022 11:18 am

For this report, I wanted to explore seasonal patterns in SPL book checkouts. Specifically, I am interested in (1) whether or not seasonal patterns exist; (2) whether or not there is some association with weather conditions (temperature and precipitation); and (3) whether there are seasonal trends for particular age groups (since kids have school breaks) or item types. Apart from SPL data and SQL, I used NOAA for weather data and R studio for visualization.
Attachments
seasons_split_weekly.csv
(3.73 KiB) Downloaded 45 times
seasons_split_plus_itemtypes_by_age.csv
(3.71 KiB) Downloaded 42 times
seasons_split_plus_itemtypes.csv
(10.66 KiB) Downloaded 34 times
seasons_split_monthly_colyears.csv
(514 Bytes) Downloaded 39 times
seasons_split_monthly.csv
(875 Bytes) Downloaded 39 times
Assignment 3.pdf
(426.53 KiB) Downloaded 51 times

shaokang
Posts: 8
Joined: Fri Sep 23, 2022 10:07 am

Re: wk4 - 10.18.22 Discover Patterns with MySQL

Post by shaokang » Tue Oct 18, 2022 11:54 am

During past decades, numerous data storage devices appeared and dominated the market at some timestamp. For example, the VHS format, developed by JVC, is released in 1976, and in 2016, no one would make VHS products since there are much more advanced storage devices (DVD, Blu-Ray etc.). Instead of using physical media, people would stream contents on their laptop or smartphone with access to the Internet.

In this topic dive, I would like to look at questions below, exploring facts about these legacy items.
What’s the trend of different legacy items? Will anyone still checkout these legacy today?
If so, which item do they borrow?
Are there any findings in these item?
Assignment 03.pdf
(3.95 MiB) Downloaded 42 times
All queries and CSV files are attached within PDF.

briannagriffin
Posts: 11
Joined: Fri Sep 23, 2022 10:04 am

Re: wk4 - 10.18.22 Discover Patterns with MySQL

Post by briannagriffin » Tue Oct 18, 2022 11:59 am

Abstract Introduction:
For this week’s assignment, I began with a question of how library book checkouts have changed in the past 10 years. To begin, I looked at the most popular books that were checked out this year (2022) and compared it to the most popular books ten years ago (2012). I identified themes and unique data points in these queries as a way to further investigate the Seattle Public library data set. The two main themes that I highlighted in my queries and analysis are the timing of popularity in checkouts of trilogy book series around movie release dates and learning about books that are classified “novels”.

I am attaching a pdf here
week 4 assignment - MAT 265.pdf
(5.71 MiB) Downloaded 51 times
which includes the introduction, SQL code, and analysis for this week's assignment.

Below is an excel file with the output data tables from the SQL queries:
Week 4 Mat 265 Brianna Griffin.xlsx
(1.74 MiB) Downloaded 48 times

Post Reply