INFO 246-11
Information Technology Tools and Applications – Advanced Topic: MySQL in Depth
Summer 2022 Syllabus

Dr. Gerry Benoît
Email

Office location: (online)
Office Hours: (please email me for Zoom meeting)

Canvas Information: Courses will be available beginning June 1st, 6 am PT unless you are taking an intensive or a one-unit or two-unit class that starts on a different day. In that case, the class will open on the first day that the class meets.

You will be enrolled in the Canvas site automatically.

Course Description

We’re going to approach the topic of SQL in depth by a combination of readings, hands-on practice, and applying our practice to an ongoing demonstration database system, a photo gallery collection. There are a few advanced topics we’ll look at, too. These are using a local python program to save and retrieve data from your own computer copy of SQL and a how-to for using SQL on a web server: the open source, Apache web server. Because php, a popular script language used on web servers is being phased out, we’ll have a python version too, for creating web pages on the fly in response to our SQL queries.

Course Requirements

All course materials will be posted in our class site on Canvas.

You must be comfortable with using your computer, installing software, issuing commands in a terminal window.

Install MySQL on your computer as soon as possible!

Assignments

Course Calendar

Classes begin: 2022-06-01 Classes end: 2022-08-05 Independence Day: Monday, July 4, 2022
https://ischoolapps.sjsu.edu/gss/ajax/schedule.php?session=2&term=50

Assignments are due by the end of Saturday of that week.

Please start immediately with the installation of MySQL on your computer. Week 3 has a lot of material to review and practice so you’re encouraged to jump in!  Feel free to read how to install MySQL.

• Assignment 1: is to ensure you have installed your own working copy of MySQL on your computer. 0 points but required. CLO #1
• Assignment 2: is the result of practicing creating data, basic commands of insert, select, and conditions. 25%. CLO #1, #2
• Assignment 3: continues the practice of DML (data manipulation language) by applying the commands to a demonstration database, the Photo Gallery RDBMS. 25% CLOs #1-4
• Assignment 4: completes the command set of MySQL. 25% CLOs #1-4
• Quiz: An online quiz that covers the entirety of the course content. 20% CLOs #1-4
• Participation: in our class discussions. 5% of the total grade. CLOs #1-4

Grading

• Extra credit options: setting up your webserver to process MySQL and/or generating webpages using python and MySQL
• Grading policy on late or missed work: there's no penalty provided you keep me informed.

Other Relevant Information:

Participation expectations: Because we've a range of material to cover and because we'll setup MySQL on our own computers, it is critical that you setup your copy as soon as the course starts. There are detailed instructions in week 1's readings.

Second, note that we have a lot of work during Week 3 - a combination of hands-on practice and then application to our demo project, a Photo Gallery.

The course is a combo of example and application. The assignments are outputs of your SQL session based on the instructions. It's an easy course because we're reviewing and practicing MySQL ... the challenge of MySQL is application in your own work and personal interests!

About our class …
We’re going to approach the topic of SQL in depth by a combination of readings, hands-on practice, and applying our practice to an on-going demonstration database system, a photo gallery collection. There are a few advanced topics we’ll look at, too. These are using a local python program to save and retrieve data from your own computer copy of SQL and a how-to for using SQL on a web server, the free OpenSource Apache web server. Because php, a popular script language used on web servers is being phased out, we’ll have python version, too, for creating web pages on the fly in response to our SQL queries.

Each lesson begins with an overview. Be sure to read and understand the introduction before proceeding to the hands-on. In the hands-on section, SQL commands are grouped by their functionality. For instance, commands related to searching data are together; those related to inputting are grouped similarly. These demo commands are intended for you to focus on the syntax of a command. We build sequentially from the most basic form of the command to more complicated ones. At the end of the lesson you’ll write out the commands as they could apply to our photo collection demo project.

It is imperative that you keep to our syllabus.

About the topic of SQL:
SQL is both surprisingly powerful and surprisingly easy to start to learn. For instance, the command for extracting all the records from a single table (let’s name the table “students”) is just SELECT * FROM students;. In our course we cannot review everything in the great and deep detail that is SQL; we will go in deep, though, with a great many commands, practice our commands in a demonstration database system, and use these skills in real-world programming settings. These settings include a standalone python program to create and search for records as well as integrating them into an Apache web server. Given that our course is not very long, the addition of the program and web server are optional and extra credit. By the end, you’ll have a solid set of SQL skills, and an understanding of the big picture of SQL in some of the technology of relational databases in professional computing work.

About SQL, errors, and your computing environment:
Everyone’s computers differ in their setup. Sometimes commands are demonstrated in our class and certainly everyone online but they don’t work on your computer’s setup. That happens and it should encourage you to take notes writing down how the command is demonstrated and how the command should be on your machine. Fear not! This doesn’t happen a lot - but it can.

Different versions of Microsoft Windows can have lots of different ways of setting up something. In our course we cannot provide directions for every Windows version: all computer users need to know how to run their own machines and be their own tech support.

Error messages: no one likes to get an error message! But it is vital to become comfortable with them and to learn how to read them. When using SQL you may encounter three types of messages: an error created by your operating system, an error created by SQL, and an SQL status message. SQL commands return a status (confirming a command worked as well as when it doesn’t work) and error messages when something did go wrong. Error messages aren’t always to interpret. For example, if you try to issue some SQL commands in your terminal window and you receive this long, confusing message … it’s likely you haven’t started the SQL daemon. [A daemon or Service is software that “listens” to your computer. You’ll see SQL requires starting the SQL daemon or what’s also called an “SQL Service” (by the System Preferences on Mac or by starting the SQL Application (or command line on Windows)) and then actually starting to interact with SQL. We’ll see much more of this in week 1.

How to learn about SQL:
Students in our class may have no experience with SQL or a lot. I find a good way to learn is actually to read the examples, think of your own project (or our photo gallery demo program) and write the commands on a piece of paper or type them in a text editor. This way you can really review the syntax, finding missing semi-colons, or missing spaces, typos, and the like.

SQL in practice:
You may have learned about the software development life cycle (SDLC). Or perhaps you’ve studied interface design or needs analysis and documentation …. All of these come together in the SQL world because

• we design database systems in light of the people’s needs of who uses these systems;
• the logical design of the system is based on these needs in order to know how to “decompose” the data into functional groups;
• the interfaces also affect the logical design when we test how people might use the data in their jobs;
• the logical design has to be converted into a physical design - the actual computing commands that create, define and use our database’s tables;
• we may have to integrate these systems into other platforms - like web servers - and
• document the entire process’s data models, data flows, input and output designs, and more, to create, maintain, and scale our RDBMS.

Evaluating your progress:
Coding and design, while technical and precise, are really art forms. There is a wide range of subjectivity because you, the database designer, resolve data problems as you understand the end-user's needs and your own computing environment.

All SQL products share a basic syntax (“ANSI SQL”) so whether you learn MySQL or Oracle or other products the basic “Data Creation Language” and “Data Manipulation Language” commands are the same. DCL just means the commands for creating databases, tables, and indices. DML refers to those basic commands for inserting, selecting, deleting, and altering data. Commercial SQL products use proprietary techniques to do advanced commands so we won’t worry about them.

Course Contents:

1. Introduction: In our introduction, we learn about the need for and some software options in the SQL world.
2. Entity-Relation Diagrams: When we define a database and the tables, we need to define the type of data in our tables, restrictions or constraints on those data (called entities), and how they’re grouped to make a set of tables (relation). The result is a way to visualize our data and serve as part of our project documentation.
3. Demonstration Project: We have a completed demonstration product - a Photo Gallery Project - that we’ll use for testing. The demo project helps us contextualize the actual use of RDBMS - the idea of input of data (using a webpage or a program) and output (an on-the-fly created webpage or other output).
4. Creating Databases and Tables: Creating databases and tables requires a lot: a copy of MySQL software, administrator rights over our computer, and experience using a terminal window (Unix) or Windows equivalent so we can issue commands.
5. Input and Output: Here we consider issues related to the inputting and outputting of data.
6. Deleting and Updating: To be sure, we need to be able to locate data in order to correct or delete them.
7. Altering our tables: in real life, we sometimes have to fix a table.
8. Tables: Working with more than one table requires knowing how to search across more than one table; very often we use a “nickname” or alias. Aliases help us users keep track of our tables and help us create queries that are unambiguous. We’ll also look at how we “join” data by integrating data from multiple tables and how directionality affects our search results.
9. Views: A “view” of the data is a way of keeping a useful command as a kind of shortcut; it’s similar to a Prepared Statement.
10. Data in Practice: Data in programs and web-enabled databases.
11. Next steps: A reading about how you can continue your development in the database realm.

top

Course Workload Expectations

Success in this course is based on the expectation that students will spend, for each unit of credit, a minimum of forty-five hours over the length of the course (normally 3 hours per unit per week with 1 of the hours used for lecture) for instruction or preparation/studying or course related activities including but not limited to internships, labs, clinical practica. Other course structures will have equivalent workload expectations as described in the syllabus.

Instructional time may include but is not limited to:
Working on posted modules or lessons prepared by the instructor; discussion forum interactions with the instructor and/or other students; making presentations and getting feedback from the instructor; attending office hours or other synchronous sessions with the instructor.

Student time outside of class:
In any seven-day period, a student is expected to be academically engaged through submitting an academic assignment; taking an exam or an interactive tutorial, or computer-assisted instruction; building websites, blogs, databases, social media presentations; attending a study group;contributing to an academic online discussion; writing papers; reading articles; conducting research; engaging in small group work.

Course Prerequisites

INFO 246 has no prequisite requirements.

Course Learning Outcomes

Upon successful completion of the course, students will be able to:

1. Identify the features of SQL.
2. Query a database using SQL.
3. Utilize subqueries in the database.
4. Modify data in a database using SQL.

Core Competencies (Program Learning Outcomes)

INFO 246 supports the following core competencies:

top

Textbooks

No Textbooks For This Course.

top

Grading Scale

The standard SJSU School of Information Grading Scale is utilized for all iSchool courses:

In order to provide consistent guidelines for assessment for graduate level work in the School, these terms are applied to letter grades:

• C represents Adequate work; a grade of "C" counts for credit for the course;
• B represents Good work; a grade of "B" clearly meets the standards for graduate level work or undergraduate (for BS-ISDA);
  For core courses in the MLIS program (not MARA, Informatics, BS-ISDA) — INFO 200, INFO 202, INFO 204 — the iSchool requires that students earn a B in the course. If the grade is less than B (B- or lower) after the first attempt you will be placed on administrative probation. You must repeat the class if you wish to stay in the program. If - on the second attempt - you do not pass the class with a grade of B or better (not B- but B) you will be disqualified.
  
• A represents Exceptional work; a grade of "A" will be assigned for outstanding work only.

Graduate Students are advised that it is their responsibility to maintain a 3.0 Grade Point Average (GPA). Undergraduates must maintain a 2.0 Grade Point Average (GPA).

University Policies

Per University Policy S16-9, university-wide policy information relevant to all courses, such as academic integrity, accommodations, etc. will be available on Office of Graduate and Undergraduate Programs' Syllabus Information web page at: https://www.sjsu.edu/curriculum/courses/syllabus-info.php. Make sure to visit this page, review and be familiar with these university policies and resources.

In order to request an accommodation in a class please contact the Accessible Education Center and register via the MyAEC portal.

Download Adobe Acrobat Reader to access PDF files.

More accessibility resources.