## Chapter 28 - Relational Databases

## Chapter Goals

- 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

## Chapter 28 - Relational Databases

- 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

## 28.1 Organising Database Information

## 28.1.1 Database Tables

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

Product_Code | Description | Unit_Price
--- | --- | ---
116-064 | Toaster | 24.95
257-535 | Hair dryer | 29.95
643-119 | Car vacuum | 19.99

## 28.1.1 Database Tables (cont.)

- 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

## 28.1.1 Database Tables (cont.)

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

SQL Data Type | C++ Data Type
--- | ---
INTEGER or INT | int
REAL | float
DOUBLE | double
DECIMAL(n, x) | Fixed-point decimal numbers with n total digits and x digits after the decimal point
BOOLEAN | bool
CHARACTER(x) or CHAR(x) | Fixed-length string of length x. Similar to string

## 28.1.1 Database Tables (cont.)

- 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)
  >     )

## 28.1.1 Database Tables (cont.)

- 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'`

## 28.1.1 Database Tables (cont.)

- 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

## 28.1.2 Linking Tables

- Store a simple object's data...

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

## 28.1.2 Linking Tables (cont.)

- 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

## 28.1.2 Linking Tables (cont.)

- Problems if an object references another object:

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

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

## 28.1.2 Linking Tables (cont.)

- Store customer info in invoice table?

Invoice_Number | Customer_ Name | Customer_ Address | Customer_ City | Customer_ State | Customer_ Zip | ...
--- | --- | --- | --- | --- | --- | ---
`INTEGER` | `CHAR(40)` | `CHAR(40)` | `CHAR(30)` | `CHAR(2)` | `CHAR(10)` | `...`
11731 | Sam's Small Appliances | 100 Main St. | Anytown | CA | 98765 | ...
11732 | Electronics Unlimited | 1175 Liberty Ave. | Pleasantville | MI | 45066 | ...
11733 | Sam's Small Appliances | 100 Main St. | Anytown | CA | 98765 | ...

## 28.1.2 Linking Tables (cont.)

- Bad idea - replicate data

- Wastes space
- Updates become unmanageable, inefficient, error-prone

## 28.1.2 Linking Tables (cont.)

In C++, many objects can refer to a single object To accomplish this in SQL: Break data into 2 tables Solves replication problem Cust# primary key for Customer table A foreign key in the Invoice table Links tables |
Invoice
Invoice_ Number | Customer_ Number | Customer_ Payment
--- | --- | ---
`INTEGER` | `INTEGER` | `DECIMAL(10,2)`
11731 | 3175 | 0
11732 | 3176 | 249.95
11733 | 3175 | 0

## 28.1.2 Linking Tables (cont.)

- Add a unique field - primary key

Customer
Customer_ Number | Name | Address | City | State | Zip
--- | --- | --- | --- | --- | ---
`INTEGER` | `CHAR(40)` | `CHAR(40)` | `CHAR(30)` | `CHAR(2)` | `CHAR(10)`
3175 | Sam's Small Appliances | 100 Main St. | Anytown | CA | 98765
3176 | Electronics Unlimited | 1175 Liberty Ave. | Pleasantville | MI | 45066

## 28.1.2 Linking Tables (cont.)

- 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

## 28.1.3 Implementing One-to-Many Relationships

- Each invoice linked to exactly one customer
- An invoice has many items
- In C++, `Item`s stored in container:

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

## 28.1.3 Implementing One-to-Many Relationships (cont.)

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

Poor design for Invoice Table
Invoice_ Number | Customer_ Number | Product_ Code1 | Quantity1 | Product_ Code2 | Quantity2 | Product_ Code3 | Quantity3 | Payment
--- | --- | --- | --- | --- | --- | --- | --- | ---
`INTEGER` | `INTEGER` | `CHAR(10)` | `INTEGER` | `CHAR(10)` | `INTEGER` | `CHAR(10)` | `INTEGER` | `DECIMAL(10,2)`
11731 | 3175 | 116-064 | 3 | 257-535 | 1 | 643-119 | 2 | 0

## 28.1.3 Implementing One-to-Many Relationships (cont.)

Break into 2 tables Link items to an invoice w/`Invoice_Number` |
Item
Invoice_ Number | Product_ Code | Quantity
--- | --- | ---
`INTEGER` | `CHAR(10)` | `INTEGER`
11731 | 116-064 | 3
11731 | 257-535 | 1
11731 | 643-119 | 2
11732 | 116-064 | 10
11733 | 116-064 | 2
11733 | 643-119 | 1
Invoice
Invoice_ Number | Customer_ Number | Payment
--- | --- | ---
`INTEGER` | `INTEGER` | `DECIMAL(10,2)`
11731 | 3175 | 0
11732 | 3176 | 249.5
11733 | 3175 | 0

## 28.1.3 Implementing One-to-Many Relationships (cont.)

Our DB now has 4 tables: Invoice Customer Item Product |

## 28.2 Queries

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

## 28.2 Queries (cont.)

Product
Product_Code | Description | Unit_Price
--- | --- | ---
116-064 | Toaster | 24.95
257-535 | Hair dryer | 29.95
643-119 | Car vacuum | 19.99
Item
Invoice_Number | Product_Code | Quantity
--- | --- | ---
`INTEGER` | `CHAR(10)` | `INTEGER`
11731 | 116-064 | 3
11731 | 257-535 | 1
11731 | 643-119 | 2
11732 | 116-064 | 10
11733 | 116-064 | 2
11733 | 643-119 | 1

## 28.2 Queries (cont.)

Invoice
Invoice_Number | Customer_Number | Payment
--- | --- | ---
`INTEGER` | `INTEGER` | `DECIMAL(10,2)`
11731 | 3175 | 0
11732 | 3176 | 249.5
11733 | 3175 | 0

Customer
Customer_Number | Name | Address | City | State | Zip
--- | --- | --- | --- | --- | ---
`INTEGER` | `CHAR(40)` | `CHAR(40)` | `CHAR(30)` | `CHAR(2)` | `CHAR(10)`
3175 | Sam's Small Appliances | 100 Main St. | Anytown | CA | 98765
3176 | Electronics Unlimited | 1175 Liberty Ave. | Pleasantville | MI | 45066

## 28.2.1 Simple Queries

- 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

## 28.2.2 Selecting Columns

- To select certain fields from all rows in table:

  > `SELECT City, State FROM Customer`

- Returns view:

  Anytown | CA
  Pleasantville | MI

## 28.2.3 Selecting Subsets

- 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

## 28.2.3 Selecting Subsets (cont.)

- Relational operators:

  > `= <> < <= > >= LIKE`

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

    > `SELECT * FROM Customer WHERE State <> 'CA'`

## 28.2.3 Selecting Subsets (cont.)

- `LIKE` allows pattern matching

  `_` | Any single character
  `%` | Any sequence of characters

  - E.g.

    > `Name LIKE '_o%'`

    matches "Toaster", but not "Crowbar"

## 28.2.3 Selecting Subsets

- 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'`

## 28.2.4 Calculations

- 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`

## 28.2.5 Joins

- 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'

## 28.2.5 Joins (cont.)

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

  > TableName.ColumnName

## 28.2.5 Joins (cont.)

- So,

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

- Yields

  11731
  11733

## 28.2.5 Joins (cont.)

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

## 28.2.5 Joins (cont.)

- 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

## 28.2.5 Joins (cont.)

- 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

## 28.2.6 Updating and Deleting Data

- `SELECT`s 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'`

## 28.2.6 Updating and Deleting Data

- `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

## 28.3 Installing a Database

- 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

## 28.3 Installing a Database (cont.)

General sequence to install and test installation:

## 28.3 Installing a Database (cont.)

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

## 28.4 Database Programming in C++

## 28.4.1 Connecting to the MySQL Database

- 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

## 28.4.1 Connecting to the MySQL Database

- 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)`

## 28.4.1 Connecting to the MySQL Database

- `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);`

## 28.4.2 Executing SQL Statements

- 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());`

## 28.4.2 Executing SQL Statements (cont.)

- 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

## 28.4.2 Executing SQL Statements (cont.)

- Always check:

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

- Do not use results if query failed

## 28.4.3 Analyzing Query Results

- 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`

## 28.4.3 Analyzing Query Results (cont.)

- 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
  >     }

## 28.4.3 Analyzing Query Results (cont.)

- `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];`

## 28.4.3 Analyzing Query Results (cont.)

- 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]);`

## 28.4.3 Analyzing Query Results (cont.)

- Close each row (free resources) when done:

  > `mysql_free_result(result);`

- Avoid loading very large result sets into memory at once

## 28.4.3 Analyzing Query Results (cont.)

- `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)

## 28.4.3 Analyzing Query Results (`execsql.cpp`)

## 28.4.3 Analyzing Query Results (`product.sql`)

## 28.5 Case Study: Accessing an Invoice Database

- 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

## 28.5 Case Study: Accessing an Invoice Database (cont.)

`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)

## 28.5 Case Study: Accessing an Invoice Database (cont.)

- 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

## 28.5 Case Study: Accessing an Invoice Database (cont.)

`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";`

## 28.5 Case Study: Accessing an Invoice Database (cont.)

- 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]);`

## 28.5 Case Study: Accessing an Invoice Database (cont.)

`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) + ")";`

## 28.5 Case Study (printinv.cpp)

## 28.5 Case Study (addinv.cpp)

## Advanced Topic 23.1

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

## Chapter Summary

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