Proj 1 - Data Exploration Concept & MySQL Basics
Posted: Wed Jan 10, 2024 10:11 pm
Proj 1 - Data Exploration Concept & MySQL Basics
This is the 1st of 3 assignments to be realized in the MAT 259 Data Visualization course:
https://www.mat.ucsb.edu/~g.legrady/aca ... 4w259.html
----------------------------------
PROJECT ASSIGNMENT: The assignment is to 1) acquire basic skills in the MySQL relational database SQL language, and to 2) Familiarize with the Seattle Library multivariate database to ask an interesting question by which to retrieve data and analyse it.
The Seattle (SPL) database we are using is called "spl2016". The most common tables are outraw (checkouts), inraw(checkins), subject (keywords attached to each entry). Descriptions of all the metadata are here: https://www.mat.ucsb.edu/~g.legrady/aca ... ataDef.pdf
MYSQL QUERIES EXAMPLES:
https://www.mat.ucsb.edu/~g.legrady/aca ... wMysql.pdf
https://www.mat.ucsb.edu/~g.legrady/aca ... tudent.pdf
https://www.mat.ucsb.edu/~g.legrady/aca ... MySQL2.pdf
https://www.mat.ucsb.edu/~g.legrady/aca ... demos_.pdf
MYSQL BASICS. Review the following functions by Tuesday, January 16, 2024: Count(), *, distinct, as, from, group by, order by, %, having, like, soundex, where, timestampdiff(), limit, desc, not in, between, >= <=, inner join, year(), date(), time, hour(), sum(), avg(), min(), max(), case, when, then, else, not, and, or, as, substring(), rand(), floor(), variance().
Tutorials: https://www.mysqltutorial.org/mysql-com ... functions/ and https://www.w3schools.com/sql/default.asp
----------------------------------
SCHEDULE:
January 11: Introduction to MySQL
January 16: Example reviews in class
January 18: 1st hour lab work, 2nd hour, short casual presentation to class
January 23: Formal presentation to class of your assignment documentation
----------------------------------
The assignment can be posted in full detail here in the Student Forum or else attached as a PDF document. In the case of the PDF document please provide:
a) A one paragraph description of your SQL data search project in the student forum which can be repeated in the attached PDF file
b) Add a PDF that documents the work you did. The PDF should have your name in the title
c) Also post in the Student Forum, the results of your query as a CSV file(s).
----------------------------------
The PDF should include the following:
. A one-paragraph description of your query search
. Concept description
. MySQL Query
. The data and data results
. Discussion/Analysis of results
----------------------------------
WHAT SHOULD THE QUERY BE ABOUT?
. The query can be about a topic of interest to you that might be represented in the library database
. The query can explore the structure of how the database is organized, how things are classified
. The query can reveal anomalies, errors, outliers in either the data or how the data is organized, or how things are classified
. Consider that this is a warm-up exercise to produce data to be used to create a 3D visualization.
For reference, we are currently only doing MySQL queires so only need a minimum of 2 csv columns but the 3D visualization assignement will will follow will require 5 columns of values:
1) A value for horizontal position
2) A value for vertical position
3) A value for depth position
4) A value for scaling or color for each cell in the visualization
5) Possibly a string value for labeling each indivisual cell
----------------------------------
SOME PREVIOUS STUDENT EXAMPLES
. PREDICTION: https://vislab.mat.ucsb.edu/2020/p1/Gua ... rName.html
. ITEMNUMBER: https://vislab.mat.ucsb.edu/2019/p1/Jia ... index.html
. CORRELATION: https://vislab.mat.ucsb.edu/2022/p1/Lij ... Cheng.html
. INTEREST IN A TOPIC: https://vislab.mat.ucsb.edu/2021/p1/Lar ... hetty.html
ADVANCED EXAMPLES
Good to review to become familiar with how to describe the approach of how your project has evolved: https://www.mat.ucsb.edu/~g.legrady/aca ... projs.html
I presented in class:
"Hunger Games" (Brianna): https://www.mat.ucsb.edu/~g.legrady/aca ... rianna.pdf
"Legacy Media" (Shaokang): https://www.mat.ucsb.edu/~g.legrady/aca ... aokang.pdf
ALL PREVIOUS STUDENT PROJECTS: https://vislab.mat.ucsb.edu/courses.html
----------------------------------
EVALUATION CRITERIA
. The topic should be relevant or interesting. Describe why
. The query should result in extensive data saved as a csv file
. Consider the whole database, going beyond the Dewey numeric classification
This is the 1st of 3 assignments to be realized in the MAT 259 Data Visualization course:
https://www.mat.ucsb.edu/~g.legrady/aca ... 4w259.html
----------------------------------
PROJECT ASSIGNMENT: The assignment is to 1) acquire basic skills in the MySQL relational database SQL language, and to 2) Familiarize with the Seattle Library multivariate database to ask an interesting question by which to retrieve data and analyse it.
The Seattle (SPL) database we are using is called "spl2016". The most common tables are outraw (checkouts), inraw(checkins), subject (keywords attached to each entry). Descriptions of all the metadata are here: https://www.mat.ucsb.edu/~g.legrady/aca ... ataDef.pdf
MYSQL QUERIES EXAMPLES:
https://www.mat.ucsb.edu/~g.legrady/aca ... wMysql.pdf
https://www.mat.ucsb.edu/~g.legrady/aca ... tudent.pdf
https://www.mat.ucsb.edu/~g.legrady/aca ... MySQL2.pdf
https://www.mat.ucsb.edu/~g.legrady/aca ... demos_.pdf
MYSQL BASICS. Review the following functions by Tuesday, January 16, 2024: Count(), *, distinct, as, from, group by, order by, %, having, like, soundex, where, timestampdiff(), limit, desc, not in, between, >= <=, inner join, year(), date(), time, hour(), sum(), avg(), min(), max(), case, when, then, else, not, and, or, as, substring(), rand(), floor(), variance().
Tutorials: https://www.mysqltutorial.org/mysql-com ... functions/ and https://www.w3schools.com/sql/default.asp
----------------------------------
SCHEDULE:
January 11: Introduction to MySQL
January 16: Example reviews in class
January 18: 1st hour lab work, 2nd hour, short casual presentation to class
January 23: Formal presentation to class of your assignment documentation
----------------------------------
The assignment can be posted in full detail here in the Student Forum or else attached as a PDF document. In the case of the PDF document please provide:
a) A one paragraph description of your SQL data search project in the student forum which can be repeated in the attached PDF file
b) Add a PDF that documents the work you did. The PDF should have your name in the title
c) Also post in the Student Forum, the results of your query as a CSV file(s).
----------------------------------
The PDF should include the following:
. A one-paragraph description of your query search
. Concept description
. MySQL Query
. The data and data results
. Discussion/Analysis of results
----------------------------------
WHAT SHOULD THE QUERY BE ABOUT?
. The query can be about a topic of interest to you that might be represented in the library database
. The query can explore the structure of how the database is organized, how things are classified
. The query can reveal anomalies, errors, outliers in either the data or how the data is organized, or how things are classified
. Consider that this is a warm-up exercise to produce data to be used to create a 3D visualization.
For reference, we are currently only doing MySQL queires so only need a minimum of 2 csv columns but the 3D visualization assignement will will follow will require 5 columns of values:
1) A value for horizontal position
2) A value for vertical position
3) A value for depth position
4) A value for scaling or color for each cell in the visualization
5) Possibly a string value for labeling each indivisual cell
----------------------------------
SOME PREVIOUS STUDENT EXAMPLES
. PREDICTION: https://vislab.mat.ucsb.edu/2020/p1/Gua ... rName.html
. ITEMNUMBER: https://vislab.mat.ucsb.edu/2019/p1/Jia ... index.html
. CORRELATION: https://vislab.mat.ucsb.edu/2022/p1/Lij ... Cheng.html
. INTEREST IN A TOPIC: https://vislab.mat.ucsb.edu/2021/p1/Lar ... hetty.html
ADVANCED EXAMPLES
Good to review to become familiar with how to describe the approach of how your project has evolved: https://www.mat.ucsb.edu/~g.legrady/aca ... projs.html
I presented in class:
"Hunger Games" (Brianna): https://www.mat.ucsb.edu/~g.legrady/aca ... rianna.pdf
"Legacy Media" (Shaokang): https://www.mat.ucsb.edu/~g.legrady/aca ... aokang.pdf
ALL PREVIOUS STUDENT PROJECTS: https://vislab.mat.ucsb.edu/courses.html
----------------------------------
EVALUATION CRITERIA
. The topic should be relevant or interesting. Describe why
. The query should result in extensive data saved as a csv file
. Consider the whole database, going beyond the Dewey numeric classification