CS377: Database Design - The Structured Query Language (SQL)

Activity Goals

The goals of this activity are:
  1. To use the SQL language to query and manipulate a database
  2. To integrate common programming constructs with SQL language
  3. To use a prepared statement to sanitize program inputs used in a SQL query

Supplemental Reading

Feel free to visit these resources for supplemental background reading material.

The Activity

Directions

Consider the activity models and answer the questions provided. First reflect on these questions on your own briefly, before discussing and comparing your thoughts with your group. Appoint one member of your group to discuss your findings with the class, and the rest of the group should help that member prepare their response. Answer each question individually from the activity, and compare with your group to prepare for our whole-class discussion. After class, think about the questions in the reflective prompt and respond to those individually in your notebook. Report out on areas of disagreement or items for which you and your group identified alternative approaches. Write down and report out questions you encountered along the way for group discussion.

Model 1: Python SQL Programming

Schema Definition Generated by eralchemy from the PythonSqlExample replit example

Questions

  1. Create a class to encapsulate the behavior of a SQL Query.
  2. Discuss with your group a potential data model and draw a schema for that model. As a class, create the database in SQL, and then implement it with your Python program.
  3. What are the primary keys, foreign keys, kernels, and dependencies in your data model? For each dependency, what kind of dependency is it?

Embedded Code Environment

You can try out some code examples in this embedded development environment! To share this with someone else, first have one member of your group make a small change to the file, then click "Open in Repl.it". Log into your Repl.it account (or create one if needed), and click the "Share" button at the top right. Note that some embedded Repl.it projects have multiple source files; you can see those by clicking the file icon on the left navigation bar of the embedded code frame. Share the link that opens up with your group members. Remember only to do this for partner/group activities!

Model 2: Sanitizing Database Inputs

XKCD Exploits of a Mom

Questions

  1. What is the problem being demonstrated here? How might you write a program that is vulnerable to this type of attack?
  2. What can we do to prevent this?
  3. Look up a SQL Prepared Statement and write down how to execute one in your favorite programming language and library.

Model 3: Advanced SQL Queries

Schema Definition Generated by eralchemy from the Sql-Queries replit example

Questions

  1. Write a Python program to implement this database and queries.
  2. Add a statement to UPDATE a column in this database.
  3. Add a statement to DELETE a column in this database.
  4. Deleting records can be dangerous if your query has an error, and you may wish to preserve the record history and provenance, anyway. What is an alternative to deleting the record that would cause it to be hidden in your subsequent queries?

Embedded Code Environment

You can try out some code examples in this embedded development environment! To share this with someone else, first have one member of your group make a small change to the file, then click "Open in Repl.it". Log into your Repl.it account (or create one if needed), and click the "Share" button at the top right. Note that some embedded Repl.it projects have multiple source files; you can see those by clicking the file icon on the left navigation bar of the embedded code frame. Share the link that opens up with your group members. Remember only to do this for partner/group activities!

Model 4: Group Clauses

Schema Definition Generated by eralchemy from the Sql-Groups replit example

Questions

  1. Write a query to count the number of dependents an employee has from the ER Model Activity

Embedded Code Environment

You can try out some code examples in this embedded development environment! To share this with someone else, first have one member of your group make a small change to the file, then click "Open in Repl.it". Log into your Repl.it account (or create one if needed), and click the "Share" button at the top right. Note that some embedded Repl.it projects have multiple source files; you can see those by clicking the file icon on the left navigation bar of the embedded code frame. Share the link that opens up with your group members. Remember only to do this for partner/group activities!

Model 5: Subqueries

Schema Definition Generated by eralchemy from the Sql-Subqueries replit example

Questions

  1. How do subqueries relate to the SQL JOIN we've seen previously?

Embedded Code Environment

You can try out some code examples in this embedded development environment! To share this with someone else, first have one member of your group make a small change to the file, then click "Open in Repl.it". Log into your Repl.it account (or create one if needed), and click the "Share" button at the top right. Note that some embedded Repl.it projects have multiple source files; you can see those by clicking the file icon on the left navigation bar of the embedded code frame. Share the link that opens up with your group members. Remember only to do this for partner/group activities!

Introduction to the Structured Query Language (SQL)

Structured Query Language (SQL) is a standard programming language used to manage and manipulate relational databases. It provides a set of commands for creating, modifying, and querying databases. SQL is widely used in the industry for various applications such as data analysis, data management, and integration with other programming languages.

SQL Basics

SQL allows users to interact with databases in a declarative manner, meaning that users specify what they want to achieve, and the database system takes care of how to accomplish it. Some of the key concepts and commands in SQL are:

  1. Data Definition Language (DDL): DDL commands are used to define and manage database objects such as tables, indexes, and views. Examples of DDL commands include CREATE TABLE, ALTER TABLE, and DROP TABLE.

  2. Data Manipulation Language (DML): DML commands are used to manipulate data within tables. Examples of DML commands include SELECT, INSERT, UPDATE, and DELETE.

  3. Querying Data: The SELECT statement is used to retrieve data from one or more tables in a database. It allows users to specify conditions, sorting, and grouping of the result set.

  4. Joining Tables: SQL supports joining multiple tables based on common columns. Joins are essential for combining data from different tables to answer complex queries.

  5. Aggregate Functions: SQL provides built-in functions like SUM, COUNT, AVG, and MIN/MAX for performing calculations on a set of rows.

SELECT Statement

  • The SELECT statement is used to retrieve data from a database.
  • It allows you to specify which columns to fetch and apply conditions to filter the results.

Example:

SELECT column1, column2
FROM table
WHERE condition;

INSERT Statement

  • The INSERT statement is used to add new records to a table.
  • It specifies the column names and the values to be inserted.

Example:

INSERT INTO table (column1, column2)
VALUES (value1, value2);

UPDATE Statement

  • The UPDATE statement modifies existing records in a table.
  • It allows you to set new values for specific columns based on specified conditions.

Example:

UPDATE table
SET column = new_value
WHERE condition;

DELETE Statement

  • The DELETE statement is used to remove records from a table.
  • It can delete specific rows based on specified conditions.

Example:

DELETE FROM table
WHERE condition;

Python Integration with SQL

Python is a popular programming language for data analysis and manipulation. It provides several libraries and packages for working with SQL databases, such as the sqlite3 module for local databases and the psycopg2 module for working with PostgreSQL databases.

Here is an example of using Python to connect to an SQLite database and execute a simple SQL query:

import sqlite3

# Connect to the database
conn = sqlite3.connect('example.db')

# Create a cursor object
cursor = conn.cursor()

# Execute a SELECT query
cursor.execute("SELECT * FROM employees")

# Fetch all rows from the result set
rows = cursor.fetchall()

# Print the retrieved data
for row in rows:
    print(row)

# Close the database connection
conn.close()

SQL Injections

  • SQL injections are a common security vulnerability in web applications.
  • They occur when untrusted user input is directly concatenated into SQL queries, allowing attackers to manipulate the query’s structure.
  • SQL injections can lead to unauthorized access, data leakage, and data manipulation.

Preventing SQL Injections

  • Use parameterized queries or prepared statements to ensure that user input is properly sanitized.
  • Validate and sanitize all user input before incorporating it into SQL queries.
  • Limit database privileges for application accounts to mitigate the impact of SQL injections.
import sqlite3

# Example of using a parameterized query to prevent SQL injection
def get_user(username):
    conn = sqlite3.connect('mydb.db')
    cursor = conn.cursor()

    query = "SELECT * FROM users WHERE username = ?"
    cursor.execute(query, (username,))
    result = cursor.fetchone()

    cursor.close()
    conn.close()

    return result

Installing and Configuring MySQL

MySQL is an open-source relational database management system that is widely used for managing and organizing large volumes of data. This report serves as a guide for installing and configuring MySQL on various operating systems and provides Python examples for interacting with the database.

Installation

Windows

  1. Download the MySQL Installer from the official MySQL website.
  2. Run the installer and choose the “Developer Default” setup type.
  3. Follow the instructions to complete the installation process.

MacOS

  1. Install Homebrew package manager by executing the following command in the Terminal:
    $ /usr/bin/ruby -e "$(curl -fsSL https://raw.githubusercontent.com/Homebrew/install/master/install)"
  2. Install MySQL using Homebrew by executing the following command:
    $ brew install mysql
  3. Follow the instructions displayed after the installation to set up MySQL.

Linux (Ubuntu)

  1. Update the package index by executing the following command in the Terminal:
    $ sudo apt update
  2. Install MySQL server using the command:
    $ sudo apt install mysql-server
  3. During the installation, you will be prompted to set a root password for MySQL.

Configuration

Security

To improve the security of your MySQL installation, it is recommended to perform the following steps:

Change the Root Password
  1. Open a terminal or command prompt and login to the MySQL server as the root user:
    $ mysql -u root -p
  2. Enter the current root password when prompted.
  3. Set a new password for the root user using the following command:
    ALTER USER 'root'@'localhost' IDENTIFIED WITH mysql_native_password BY 'new_password';
Remove Anonymous Users
  1. Login to the MySQL server as the root user in the terminal.
  2. Execute the following command to list all user accounts:
    SELECT user, host FROM mysql.user;
  3. Identify any accounts with an empty username or host.
  4. Remove these anonymous user accounts using the following command:
    DROP USER ''@'hostname';

Python Integration

To interact with MySQL using Python, you can leverage the mysql-connector-python library. Install the library using the following command:
$ pip install mysql-connector-python

Example code for connecting to a MySQL database, executing a query, and fetching the results:

import mysql.connector

# Establish a connection to the database
cnx = mysql.connector.connect(
    host='localhost',
    user='root',
    password='your_password',
    database='your_database'
)

# Create a cursor object to execute queries
cursor = cnx.cursor()

# Execute a query
query = "SELECT * FROM your_table"
cursor.execute(query)

# Fetch the results
results = cursor.fetchall()
for row in results:
    print(row)

# Close the cursor and connection
cursor.close()
cnx.close()

Connecting to MySQL Programmatically Through a Java JDBC String

MySQL is one of the most popular relational database management systems (RDBMS) used today. In order to interact with MySQL database programmatically, we can make use of the Java Database Connectivity (JDBC) API. This API provides a set of library functions for connecting to and working with various databases, including MySQL.

In this report, we will explore how to connect to a MySQL database programmatically through a Java JDBC string. We will discuss the necessary steps to set up the connection, provide code examples in Java, and also mention relevant Python examples where appropriate.

Steps to Connect to MySQL Using JDBC String

  1. Import the necessary libraries: 
    import java.sql.Connection;
import java.sql.DriverManager;
  2. Define the connection URL: 
    String jdbcUrl = "jdbc:mysql://hostname:port/dbName";

    Here, hostname is the address of the MySQL server, port is the port number (default is 3306), and dbName is the name of the database you want to connect to.

  3. Provide the username and password: 
    String username = "yourUsername";
String password = "yourPassword";

    Replace yourUsername and yourPassword with the actual credentials required to access the MySQL database.

  4. Establish the connection: 
    Connection connection = DriverManager.getConnection(jdbcUrl, username, password);

    This line establishes the connection to the MySQL database using the provided URL, username, and password.

  5. Perform database operations: Once the connection is established, you can perform various operations on the MySQL database, such as executing queries, inserting data, updating records, etc. We won’t be covering these operations in detail here, as the focus is on the connection process.

Example Code in Java

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.SQLException;

public class MySQLConnectionExample {
    public static void main(String[] args) {
        // Define connection URL
        String jdbcUrl = "jdbc:mysql://hostname:port/dbName";

        // Provide username and password
        String username = "yourUsername";
        String password = "yourPassword";

        // Establish connection
        try {
            Connection connection = DriverManager.getConnection(jdbcUrl, username, password);
            System.out.println("Connected to MySQL database successfully!");
            // Perform database operations here
            // ...

            // Close the connection
            connection.close();
        } catch (SQLException e) {
            System.out.println("Error connecting to MySQL database: " + e.getMessage());
        }
    }
}

Example Code in Python

Here is an example code snippet in Python:

import mysql.connector # pip install mysql-connector-python

# Define connection details
config = {
    'user': 'yourUsername',
    'password': 'yourPassword',
    'host': 'hostname',
    'port': port,
    'database': 'dbName',
    'raise_on_warnings': True
}

# Establish connection
try:
    connection = mysql.connector.connect(**config)
    print("Connected to MySQL database successfully!")
    # Perform database operations here
    # ...

    # Close the connection
    connection.close()
except mysql.connector.Error as err:
    print("Error connecting to MySQL database: ", err)

Submission

I encourage you to submit your answers to the questions (and ask your own questions!) using the Class Activity Questions discussion board. You may also respond to questions or comments made by others, or ask follow-up questions there. Answer any reflective prompt questions in the Reflective Journal section of your OneNote Classroom personal section. You can find the link to the class notebook on the syllabus.