• To understand how relational databases store information
• To learn how to query a database with the Structured Query Language (SQL)
• To connect to a database with an application programming interface (API)
• To write database programs that insert and query data in a relational database

• Store, add, remove, change, and find data
• Efficiently
• Database management systems (DBMS) allow the programmer to focus on the data
• Relational database (RDB) is the most common type
• Allows you to use a structured query language
  • Formulate queries, updates, etc. naturally

• RDB stores information in tables
• E.g., a Product Table in an RDBMS

• Each row describes a product
  • Contains fields, one per column
• Column headers are product attributes
• All items in the same column have the same type

• Allowable column types differ somewhat
• SQL-compliant RDBs commonly support:

• Use SQL to interact w/the DB
• E.g., to create a product table:

  CREATE TABLE Products
  (
     Product_Code CHAR(11),
     Description CHAR(40),
     Unit_Price DECIMAL(10, 2)
  )

• SQL not case sensitive
• By convention, CAPS for keywords, mixed case for table and column names
• Uses single quotes to delimit strings
• Use two single quotes together to represent the single quote:

  'Sam''s Small Appliances'

• To add a row:

  INSERT INTO Products
     VALUES ('257-535', 'Hair dryer', 29.95)

• Values are in order (or can be named)
• To remove (not clear) a table:

  DROP TABLE Test

• Note: Avoid proprietary SQL extensions

• Store a simple object's data...

  class Customer
  {
     ...
  private:
     string name;
     string address;
     string city;
     string state;
     string zip;
  };

• as a row in a table (Customer table):

  Name	Address	City	State	Zip
  CHAR(40)	CHAR(40)	CHAR(30)	CHAR(2)	CHAR(10)
  Sam's Small Appliances	100 Main St.	Anytown	CA	98765

• Problems if an object references another object:

  class Invoice
  {
     ...
  private:
     Customer* cust;
     ...
  };

• Can't store a customer in a column (no ptrs. in SQL)

• Store customer info in invoice table?

• Bad idea - replicate data
• Wastes space
• Updates become unmanageable, inefficient, error-prone

• Add a unique field - primary key

• Primary key:
  • Field (or fields)
  • Unique
  • Used to establish links
  • Not required
• Foreign key:
  • Field(s) matched by primary key from another table
  • Not unique
• Relationship not maintained by DB

• Each invoice linked to exactly one customer
• An invoice has many items
• In C++, Items stored in container:

  class Invoice
  {
     ...
  private:
     Customer* cust;
     vector<Item> items;
     double payment;
  };

• Novice programmers often replicate columns:
  • Wastes space (1 or 2 items)
  • Doesn't solve problem (4 or 12 items)

• Given a populated database (next slides)
• Want to query it:
  • Names and addresses of all customers
  • In CA
  • Who bought toasters
  • With unpaid invoices

• Use SELECT
• E.g., to get all data from the Customer table:

  SELECT * FROM Customer

• Returns view:
  
  

  3175	Sam's Small Appliances	100 Main St.	Anytown	CA	98765
  3176	Electronics Unlimited	1175 Liberty Ave.	Pleasantville	MI	45066

• To select certain fields from all rows in table:

  SELECT City, State FROM Customer

• Returns view:
  
  

  Anytown	CA
  Pleasantville	MI

• To select rows (records) based on certain criteria:

  SELECT * FROM Customer WHERE State = 'CA'

• Returns:
  
  

  3175

  Sam's Small Appliances

  100 Main St.

  Anytown

  CA

  98765

• Relational operators:

  =   <>   <   <=    >   >=   LIKE

• (Case-sensitive matching is DB dependent)
  • E.g.

    SELECT * FROM Customer WHERE State <> 'CA'

• LIKE allows pattern matching

  _	Any single character
  %	Any sequence of characters

  • E.g.

    Name LIKE '_o%'

    matches "Toaster", but not "Crowbar"

• Logical Operators:

  AND   OR   NOT

  • E.g.

    SELECT *
       FROM Product
       WHERE Unit_Price < 100
          AND Description <> 'Toaster'

• Select row and column subsets:

  SELECT Name, City FROM Customer WHERE State = 'CA'

• Built-in functions:

  COUNT   SUM   AVG    MAX   MIN

• E.g.,

  SELECT COUNT(*) FROM Customer WHERE State = 'CA'

• The * argument applies to entire record; only valid in COUNT
• Specify column for other functions:

  SELECT AVG(Unit_Price) FROM Product

• Retrieve information distributed over several tables
• E.g., find all invoices that order a car vacuum:

  SELECT Product_Code
     FROM Product
     WHERE Description = 'Car vacuum'

  • To discover vacuum has code 643-119. Search invoices:

  SELECT Invoice_Number
     FROM Item
     WHERE Product_Code = '643-119'

• Can be combined into a single query
• Tables linked by column(s) (e.g., Product_Code)
• Syntax to qualify column names:

  TableName.ColumnName

• So,

  SELECT Item.Invoice_Number
     FROM Product, Item
     WHERE Product.Description = 'Car vacuum'
        AND Product.Product_Code = Item.Product_Code

• Yields
  
  

  11731

  11733

  
  
  

• Multiple tables after FROM
  • Comma-separated
  • Order irrelevant
• Called a join

• E.g., find all cities that received a hair dryer:
  • Use Product table to match 'Hair Dryer' to items
  • Use Item table to find invoices
  • Use Invoice table to find customers
  • Pull info from Customer table

  SELECT Customer.City, Customer.State, Customer.Zip
     FROM Product, Item, Invoice, Customer
     WHERE Product.Description = 'Hair dryer'
        AND Product.Product_Code = Item.Product_Code
        AND Item.Invoice_Number = Invoice.Invoice_Number
        AND Invoice.Customer_Number
           = Customer.Customer_Number

Anytown

CA

• Remember:
  • List appropriate tables in the FROM clause
  • Use the TableName.ColumnName syntax
  • List all join conditions (TableName1.ColumnName1 = TableName2.ColumnName2) in the WHERE clause
• Queries can be complex
• SQL doesn't specify how to search
• DBMSs are very good at queries

• SELECTs produce a result set
  • Do not modify the DB
• DELETE and UPDATE modify DB
  • Return # of rows affected
• DELETE removes specified row(s):

  DELETE FROM Customer WHERE State = 'CA'

• UPDATE modifies fields in record(s)

  UPDATE Item
     SET Quantity = Quantity + 1
     WHERE Invoice_Number = '11731'

• Update multiple fields by putting multiple expressions separated by commas in the SET clause

• MySQL will be used for the installation and programming examples
  • Open-source
  • Available on many platforms
  • Widely-used
• SQL fairly standard
• DB APIs are not

General sequence to install and test installation:

To connect from C++ (specific to MySQL):

• MySQL API
  • C/C++ compatible
  • No classes
  • Convert between string and char[] as needed
  • Allows for connections, queries, updates, etc.
• DB programs run continuously
• Wait for a client to connect

• Get a connection handle:

  MYSQL* connection = mysql_init();

• Make connection using handle:

  mysql_real_connect(connection, NULL, NULL, NULL,
     "bigcpp", 0, NULL, 0)

  Arguments are:

  • A ptr to an initialised connection
  • Three strings: hostname, username, password. NULL to use defaults
  • DB name (bigcpp)
  • Last three should be the defaults (0, NULL, 0)

• mysql_real_connect returns NULL if it fails
• Always check:

  if (mysql_real_connect(...) == NULL)
  {
     string error_message = mysql_error(connection);
     . . .
  }

• When done, free resources in your program and in the DB:

  mysql_close(connection);

• Use mysql_query to issue commands (even updates)
• Assemble command in a string object
• Then use c_str:

  string new_value = ...;
  string query = "INSERT INTO Test VALUES ('" + new_value + "')";
  mysql_query(query.c_str());

• Use int_to_string and double_to_string (chptr 12) to place numbers:

  int qty = ...;
  string command = "SELECT * FROM Item WHERE Quantity >= "
     + int_to_string(qty);
  mysql_query(connection, command.c_str());

• mysql_query returns non-zero error code for invalid command

• Always check:

  if (mysql_query(connection, command.c_str()) != 0)
  {
     cout << "Error: " << mysql_error(connection) << "\n";
     ...
  }

• Do not use results if query failed

• Result of a SELECT command stored in a MYSQL_RES object
• Call mysql_store_result to allocate and fill result object:

  MYSQL_RES* result = mysql_store_result(connection);

• Returns NULL if preceding command was not SELECT

• To get the # of rows and columns:

  int rows = mysql_num_rows(result);
  int fields = mysql_num_fields(result);

• rows == 0 means query yielded no records
• To inspect, fetch one row at a time:

  for (int r = 1; r <= rows; r++)
  {
     MYSQL_ROW row = mysql_fetch_row(result);
     // inspect field data from the current row
  }

• MYSQL_ROW is an array of C-style strings
• Convert to strings immediately
• To obtain the i^th field of a row:

  string field = row[i];

• Even numeric fields returned in a string
• Use string_to_int or string_to_double
• E.g., given

  SELECT Unit_Price, ... FROM Product WHERE ...

  To retrieve a record's price:

  double price = string_to_double(row[0]);

• Close each row (free resources) when done:

  mysql_free_result(result);

• Avoid loading very large result sets into memory at once

• execsql program demonstrates these concepts
• Executes SQL commands stored in a text file (e.g., product.sql)

  execsql < product.sql

• Prints out results of a SELECT query
• To run interactively:

  execsql

  • Enter key executes a query
  • End-of-file (ctrl-D or ctrl-Z to quit)

• 2 programs:
  • printinv.cpp gets an invoice # from the user, prints the invoice
  • addinv.cpp adds new invoices to DB
• sutil.h, sutil.cpp - for conversions

printinv.cpp

• Construct query for cust# and payment:

  string query = "SELECT Customer_Number, Payment FROM Invoice "
     "WHERE Invoice_Number = '" + invnum + "'";

  (C++ concatenates adj. string literals)

• If found, fetch first row, extract fields:

  MYSQL_ROW row = mysql_fetch_row(result);
  string custnum = row[0];
  double payment = string_to_double(row[1]);

• Avoid SELECT *, in case table changes later

printinv.cpp

• Query and print customer data:

  string query = "SELECT Item.Quantity, Product.Description, "
     "Product.Unit_Price FROM Item, Product WHERE "
     "Item.Invoice_Number = ' "+ invnum
     + "'AND Item.Product_Code = Product.Product_Code";

• Compute amt due:

  string query = "SELECT SUM(Item.Quantity * Product.Unit_Price) "
     "FROM Item, Product WHERE Item.Invoice_Number = '"
     + invnum + "' AND Item.Product_Code = Product.Product_Code";

  Yields a single row w/a single field

• Extract value, convert:

  MYSQL_ROW row = mysql_fetch_row(result); double amount_due = string_to_double(row[0]);

addinv.cpp

• Prompt for cust# and payment
• Assign unique invoice #:
  • Find max of existing invoice #s:

    SELECT MAX(Invoice_Number) FROM Invoice

  • Add 1
• Insert new row into DB:

  string command = "INSERT INTO Item VALUES ('" + invnum +
     "', '" + prodcode + "', " + int_to_string(quantity) + ")";

Transactions

• Transaction handling important DB task
• Transaction - set of operations that should all succeed, or fail entirely
• Use SQL COMMIT and ROLLBACK commands
• COMMIT makes a set of updates permanent
• ROLLBACK undoes all changes from last COMMIT
• Make creation of unique values (e.g., invoice #) part of the transaction

1. RDB stores information in tables. Each column has a datatype and unique name
2. SQL - command language for interacting w/the DB
3. CREATE TABLE and INSERT commands to add to a DB. SELECT to query. UPDATE and DELETE to modify the data
4. Do not replicate data among rows. Distribute over multiple tables
5. Primary key - column(s) that uniquely identify a record
   Foreign key - reference to primary key in linked table
6. A join is a query that involves multiple tables
7. Use an API to access a DB from a C++ program
8. Transaction - set of updates that should succeed entirely, or not at all