Proj 1 - MySQL & Knowledge Discovery in the SPL Database

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

Proj 1 - MySQL & Knowledge Discovery in the SPL Database

Post by glegrady » Thu Dec 31, 2020 4:11 pm

Proj 1 - MySQL & Knowledge Discovery in the SPL Database

Provide a 1 paragraph description of your SQL data search project in the student forum and add a PDF that documents the work you did. It should include the following:
. The 1 paragraph description
. Concept description
. MySQl Query
. Data results
. Discussion/Analysis of results

Tues - January 05, 2021 - Course Overview & Introduction to MySQL
Thur - January 07, 2021 - Review MySQL examples
Tues - January 12, 2021 - Data Processing steps
Thur - January 14, 2021 - Student presentation

Data Exploration & Knowledge Discovery through a large multivariate dataset.

The first assignment is to explore a large database consisting of multivariate data with the intent to discover and extract patterns with MySQL that may reveal something of interest. SQL is the standardized language used to access the database:

We have access to a unique database consisting of checkouts of books, cds, dvd's documented by the hour since January 1, 2006 that represents the aggregated cultural interests of downtown Seattle, but also of the larger national interests. The database currently consists of over 98 million checkouts and returns.

MYSQL ASSIGNMENT - THE CONCEPT: In any database, there lies hidden knowledge. What does a database contain, and what can MySQL queries reveal? Your first assignment is to find something of interest based on your own interests and skillsets. Here are some options:

1) Patterns, Probability & Prediction
. What is the performance of a topic(s), a title(s), media over time, or by volume, or by trends?
. What can be predicted based on previous performance?
. Can it be predicted how a sequence of data change over time?

2) The Database Organizational Structure
. What anomalies, errors, outliers, illogical classification methods, etc. may be revealed within the organizational and classification of how items are encoded
(All databases have outliers, anomalies, errors in the system as its impossible to precisely classify all things within a structured form)

3) Covid Situation
. 2020 has had an unprecedented impact on the database as the library was closed for over 5 months between March to September. Nonetheless items were circulating.
. Electronic books have been in the collection since 2009 but these are not recorded in the database we receive. Nonetheless they can be reviewed through other means:

4) Data Analytics Query Methods
. Explore statistical methods or algorithms to retrieve or process data
. Are there any machine-learning opportunities in analyzing the data?

The database consists of multivariate data. For each checkout there exists the following metadata:

Ordinal (In a numeric sequence)
ID: Assigned by the database to keep track of each entry
ItemNumber: Assigned by the library when an object enters the system
Dewey Classification (Dewey numeric) The item's dewey classification if it is recorded as a Dewey (non-fiction) item

Interval Scale (Time-Stamp)
Check-out/check-in in minutes, hour, day, month, year

Categorical (Not necessarily numerically orderable)
BibNumber: Each title has a specific number, copies of titles all have same number. Defined by the LIbrary of Congress
Barcode: Each item has a unique number on RFID sticker
CallNumber: by which to locate items on shelves - Ordinal if Dewey, otherwise categorical. Multiple copies of same item may share same call number but have different barcodes and itemNumber
CollCode: What the item is and where its located: ... /6vkj-f5xf

Semantic (Text-based)
Title: Each Item has a title
ItemType: books, cds, dvds, music sheets, etc.
Subjects: Keywords (arbitrary labeling). These are located in a separate database:

The library uses the Dewey Decimal system by which to organize non-fiction item but the majority of the items in the library collection do not have Dewey classification labels. For instance, music, movies, seem to be distributed in both Dewey and non-Dewey ways of classification. The most popular Dewey tend to be comic books, cookbooks, health, travel books, etc. A daily insight to the Dewey performance can be tracked at: ... &d=02&h=12

Label your Documents
Please make sure to label your documents like csv files by the name of your project, or your name so we can identify where they come from
George Legrady

Posts: 3
Joined: Thu Jan 07, 2021 2:59 pm

Re: Proj 1 - MySQL & Knowledge Discovery in the SPL Database

Post by ashleybruce » Thu Jan 14, 2021 12:20 pm

For my project, I wanted to explore how the movie adaptations of a series impacted the popularity of that series. I specifically was curious as to whether subsequent movie releases for the series had an impact in the popularity of later books in the series. I did this by looking at four different book series -- the Twilight series, the Hunger Games series, the Maze Runner series, and the Divergent series -- and looked at their checkout rates over time.
(1.04 MiB) Downloaded 27 times
Word Document
(760.23 KiB) Downloaded 29 times
Last edited by ashleybruce on Thu Jan 14, 2021 4:22 pm, edited 1 time in total.

Posts: 3
Joined: Thu Jan 07, 2021 3:10 pm

Re: Proj 1 - MySQL & Knowledge Discovery in the SPL Database

Post by ingmar_sturm » Thu Jan 14, 2021 3:23 pm

During the year 2020, the police killings of George Floyd and other unarmed black people in the United States provided an unprecedented support for the Black Lives Matter (BLM) movement. Millions of people took to the street to protest what they saw as illegal and immoral acts of police violence. The movement encouraged white people to read up on racism in all of its forms and “anti-racist reading lists” were widely circulated online. These lists of titles provide a starting point to analyze the Seattle Public Library (SPL) database. Were anti-racist book titles checked out more (or less) frequently in 2020? Moreover, did the Seattle Public Library purchase more such publications?
Anti-Racist Books in the SPL_2.pdf
(2.17 MiB) Downloaded 24 times
MAT259a First Project Presentation.pdf
(734.54 KiB) Downloaded 28 times
Last edited by ingmar_sturm on Mon Jan 18, 2021 5:25 pm, edited 2 times in total.

Posts: 3
Joined: Thu Jan 07, 2021 3:00 pm

Re: Proj 1 - MySQL & Knowledge Discovery in the SPL Database

Post by zhuowei » Thu Jan 14, 2021 3:23 pm

This project focuses on the duration of checkout time for books in different dewey classes and tries to answer the following questions:
1. How does the duration of checkout times differs for books in different dewey classes over the past 15 years.
2. Does the covid situation impact the checkout durations?
3. For the dewey classes with the longest and shortest checkout durations, which subclassses contribute the most to the results.
(5.51 MiB) Downloaded 29 times

Posts: 3
Joined: Thu Jan 07, 2021 3:07 pm

Re: Proj 1 - MySQL & Knowledge Discovery in the SPL Database

Post by colette_lee » Thu Jan 14, 2021 3:34 pm

Over the past year, I've seen a rise in popularity of Leftist politics on social media, so for my project I wanted to analyze checkouts in the Seattle Public library relating to Karl Marx, capitalism and socialism. I explore how these items are categorized as well has how interest in these items changes over time.
(1.04 MiB) Downloaded 26 times
(1.2 MiB) Downloaded 29 times
Last edited by colette_lee on Sun Jan 17, 2021 6:41 pm, edited 1 time in total.

Posts: 3
Joined: Thu Jan 07, 2021 3:09 pm

Re: Proj 1 - MySQL & Knowledge Discovery in the SPL Database

Post by wsheppard » Thu Jan 14, 2021 3:40 pm

The United States has been involved in several armed conflicts in the Middle East and Northern Africa in the twenty-first century. I investigate how library patrons checkout materials relating to certain countries in these regions, in the hopes of gauging the interest of Americans in the conflicts over time.
SQL Project 1.pdf
(194.9 KiB) Downloaded 24 times

Posts: 1
Joined: Thu Jan 07, 2021 3:01 pm

Re: Proj 1 - MySQL & Knowledge Discovery in the SPL Database

Post by adorsett » Thu Jan 14, 2021 3:47 pm

For this project, I studied movie popularity as a function of time. Specifically, how the number of monthly rentals for a given movie changes the longer the movie has been out. I separate the movies I selected into two categories: critically-acclaimed and blockbusters. Once I collected the data for each movie, I plot the data for each movie as a histogram, and then fit it with an exponential distribution. I compare the fit results (including goodness-of-fit) between the two categories.
(1.56 MiB) Downloaded 26 times

Posts: 3
Joined: Thu Jan 07, 2021 3:05 pm

Re: Proj 1 - MySQL & Knowledge Discovery in the SPL Database

Post by richardjiang » Thu Jan 14, 2021 4:30 pm

An interesting concept in the management of a library is what to do when an is checked out and not returned. Motivated by this, I studied the library-wide phenomenon of non-returns. Specifically, I looked into the types of items and pieces of media that tend to get checked out and never returned. After constructing an approximate query for this, I investigated the rate at which non-returns occurs as function of media type, book type, year, and month.
(234.24 KiB) Downloaded 24 times
(288.29 KiB) Downloaded 22 times
Last edited by richardjiang on Thu Jan 14, 2021 9:35 pm, edited 1 time in total.

Posts: 4
Joined: Thu Jan 07, 2021 3:02 pm

Re: Proj 1 - MySQL & Knowledge Discovery in the SPL Database

Post by lfloegelshetty » Thu Jan 14, 2021 5:30 pm

I was interested in looking at how key national events that spark conversation about police brutality and racism towards the black community impacts the number of checkouts from the Seattle Public Library on resources that discuss these topics. To do this, I looked at popular books relevant to my interest and chose some key words that also closely aligned with key words used in protests and public discussions to filter through the SPL database. I first looked at the overall number of checkouts annually before diving into the monthly checkouts surrounding key events to see if any change occurs in the data.
(259.46 KiB) Downloaded 20 times
(305.47 KiB) Downloaded 22 times

Post Reply