TDB2073 – SPDB

Database

Part 2
Course Objectives

• Terminology of relational model.

• How tables are used to represent data.
• The basic commands and functions of SQL.
• How to use SQL for data administration (create,
delete table)
• How to use SQL for data manipulation (add,
modify, delete and retrieve data)
• How to use SQL to query a database to extract
useful information
• How to connect to MySQL database using C++
program
Why choose Relational Database Model?

• Most widely used model.

– Vendors: IBM, Informix, Microsoft, Oracle,
Sybase, etc.
– Other models
• File system, hierarchical, Object-oriented (OO)
• “Legacy systems” in older models
– E.G., IBM’s IMS
Relational Model Terminology

• Relational database
– a set of relations
• A relation is a table with columns and rows.
– Only applies to logical structure of the
database, not the physical structure.
• Attribute is a named column of a relation.
– Sometimes referred as fields
• Attribute Domain is the set of allowable
values for one or more attributes.
Relational Model Terminology

• Tuple is a row of a relation.

– Represents single entity occurrence within an entity
set
– Sometimes referred as records
• Degree is the number of attributes in a relation.
• Cardinality is the number of tuples in a
relation.
Instances of Branch and Staff (part) Relations
Examples of Attribute Domains
Alternative Terminology for Relational Model
Terminology Summary
Main Relational Keys

• Primary Key
– Candidate key selected to identify tuples
uniquely within relation.

• Foreign Key
– An attribute whose values match primary
key values in the related table
Relational Query Languages

• A major strength of the relational model:

supports simple, powerful querying of
data.
• SQL (Structured Query Language)
– database computer language designed for the
retrieval and management of data
– is a querying language for querying and
modifying data and managing databases.
– Querying to extract useful information from a
database
SQL Statement Types

• Data Definition Language (DDL)

– Commands that define a database, including
creating, altering, and dropping tables and
establishing constraints
• CREATE, ALTER, DROP, TRUNCATE
• Data Manipulation Language (DML)
– Commands that maintain and query a
database
• INSERT, SELECT, UPDATE, DELETE
Scenario :

• An excerpt from ENGINEER table

Eng_Name Eng_Id Age Salary Dept_Name

Faheem 223368 24 3 000 Mechanical
Rahmanov 137714 30 4 350 Chemical
Mphatabalo 117945 30 4580 Petroleum
Kumar 103456 28 5 000 Civil
Linda 98743 39 8 560 Mechanical
Liew 245778 26 3 250 Civil
SQL Syntax : CREATE table

CREATE TABLE
   TableName
   (   
      ColumnName1 Datatype1,
      ColumnName2 Datatype2,
      ColumnName3 Datatype3,
   )

CREATE TABLE
   Engineer
   (   
      Eng_Name char(50),
      Eng_Id int,
      Age int,
      Salary float,
Dept_Name char(50)
   )
SQL Common data types

DATA TYPE FORMAT DESCRIPTION

Numeric NUMBER(size,d) NUMBER(7,2) indicates numbers that will
be stored with two decimal places and up to
six digits long (including the sign (+/-) and
decimal point. (ex : -234.67)
INTEGER Whole counting numbers (without decimal
places)
SMALLINT Like INTEGER but limited to integer values
up to six digits
FLOAT(size, d) A small number with a floating decimal
point. The maximum number of digits may
be specified in the size parameter. The
maximum number of digits to the right of the
decimal point is specified in the d parameter
DOUBLE(size, d) A large number with a floating decimal
point. The maximum number of digits may
be specified in the size parameter. The
maximum number of digits to the right of the
decimal point is specified in the d parameter
SQL Common data types

DATA TYPE FORMAT DESCRIPTION

Character CHAR(size) Holds a fixed length string (can contain
letters, numbers, and special characters).
The fixed size is specified in parenthesis.
Can store up to 255 characters
VARCHAR(size) Holds a variable length string (can contain
letters, numbers, and special characters).
The maximum size is specified in
parenthesis. Can store up to 255
characters. Note: If you put a greater value
than 255 it will be converted to a TEXT type
TINYTEXT Holds a string with a maximum length of
255 characters

TEXT Holds a string with a maximum length of

65,535 characters
SQL Common data types

DATA TYPE FORMAT DESCRIPTION

Date DATE A date. Format: YYYY-MM-DD

Note: The supported range is from '1000-

01-01' to '9999-12-31
Time TIME A time. Format: HH:MM:SS

Note: The supported range is from '-

838:59:59' to '838:59:59'

Year YEAR A year in two-digit or four-digit format.

Note: Values allowed in four-digit format:
1901 to 2155. Values allowed in two-digit
format: 70 to 69, representing years from
1970 to 2069
SQL Syntax : ALTER table

• To add new column

• ALTER TABLE
   Engineer
 ADD
   LastName char(50)

• To change the name of a column:

• ALTER TABLE
   Engineer
CHANGE
   LastName Surname(50)
SQL Syntax : ALTER table

• To modify the data type of the column

• ALTER TABLE
   Engineer
 MODIFY
   Eng_Name char(30)

• To remove a column:
• ALTER TABLE
   Engineer
DROP
   Salary
SQL Syntax : DROP table

• To remove a table from a database

• DROP TABLE
   Table_Name

• DROP TABLE
   Engineer
SQL Syntax : TRUNCATE table

• To remove all data from a table

• TRUNCATE TABLE
   Table_Name

• TRUNCATE TABLE
   Engineer
SQL Syntax : INSERT row

• To enter data into a table

• INSERT INTO table_name
VALUES (value1, value2, value3,...)

• INSERT INTO Engineer

VALUES ('Nilsen',345598, 29,3 500,
‘Electrical')
SQL Syntax : INSERT data

• To enter data only in the specified column

• INSERT INTO table_name (column1, column2, column3,...)
VALUES (value1, value2, value3,...);

• INSERT INTO Engineer (Eng_Name, Eng_Id, Dept_Name)

VALUES (‘Jen',495598,‘Mechanical')
Result of INSERT

Eng_Name Eng_Id Age Salary Dept_Name

Faheem 223368 24 3 000 Mechanical
Rahmanov 137714 30 4 350 Chemical
Mphatabalo 117945 30 4580 Petroleum
Kumar 103456 28 5 000 Civil
Linda 98743 39 8 560 Mechanical
Liew 245778 26 3 250 Civil
Nilsen 345598 29 3 500 Electrical
Jen 495598 Mechanical
SQL Syntax : SELECT

• To select all columns from a table

• SELECT * FROM table_name;
• SELECT * FROM Engineer;
Result :

Eng_Name Eng_Id Age Salary Dept_Name

Faheem 223368 24 3 000 Mechanical
Rahmanov 137714 30 4 350 Chemical
Mphatabalo 117945 30 4580 Petroleum
Kumar 103456 28 5 000 Civil
Linda 98743 39 8 560 Mechanical
Liew 245778 26 3 250 Civil
Nilsen 345598 29 3 500 Electrical
Jen 495598 Mechanical
SQL Syntax : SELECT
• To select selected columns from a table
• SELECT Column_Name (s)
FROM table_name;

• SELECT Eng_Name, Age

FROM Engineer;
Result :

Eng_Name Age

Faheem 24
Rahmanov 30
Mphatabalo 30
Kumar 28
Linda 39
Liew 26
Nilsen 29
Jen
SQL Syntax : SELECT DISTINCT

• to list only the different (distinct) values in a table

SELECT DISTINCT Column_Name
FROM table_name;

SELECT DISTINCT Dept_Name

FROM Engineer;
Result :

Dept_Name
Mechanical
Chemical
Petroleum
Civil
Electrical
SQL Syntax : SELECT and WHERE clause
• to extract only those records that fulfill a specified
criterion
SELECT column_name(s)
FROM table_name
WHERE column_name operator value;
SQL Syntax : SELECT and WHERE clause - RULES

For text values:

This is correct:
SELECT * FROM Engineer WHERE Eng_Name=‘Jen'

This is wrong:
SELECT * FROM Engineer WHERE Eng_Name= Jen

For numeric values:

This is correct:
SELECT * FROM Engineer WHERE Salary=3500

This is wrong:
SELECT * FROM Engineer WHERE Salary=‘3500’
SQL Syntax : SELECT and WHERE clause

SELECT *
FROM Engineer
WHERE Dept_Name=‘Civil‘;

Result :

Eng_Name Eng_Id Age Salary Dept_Name

Kumar 103456 28 5 000 Civil

Liew 245778 26 3 250 Civil

Operators Allowed in the WHERE Clause

Operator Description
= Equal
<> Not Equal
> Greater than
< Less than
>= Greater than or equal
<= Less than or equal

BETWEEN Between an inclusive range

LIKE Searching for a pattern

IN If you know the exact value you want to return for at

least one of the columns
SQL Syntax : SELECT and WHERE clause – > operator

SELECT *
FROM Engineer
WHERE Age >= 30;
Result :

Eng_Name Eng_Id Age Salary Dept_Name

Eng_Name Eng_Id Age Salary Dept_Name
Rahmanov 137714 30 4 350 Chemical
Kumar 103456 28 5 000 Civil
Mphatabalo 117945 32 4580 Petroleum
Liew 245778 26 3 250 Civil
Linda 98743 39 8 560 Mechanical
SQL Syntax : SELECT and WHERE clause – AND operator

•The AND operator displays a record if both the first condition and
the second condition is true.

SELECT *
FROM Engineer
WHERE
Result : Age = 39
AND Dept_Name=‘Mechanical‘;
Result :

Eng_Name Eng_Id Age Salary Dept_Name

Linda 98743 39 8 560 Mechanical
SQL Syntax : SELECT and WHERE clause – OR operator

•The OR operator displays a record if either the first condition or

the second condition is true.

SELECT *
FROM Engineer
WHERE
Result : Salary = 3000
OR Dept_Name=‘Chemical‘;
Result :

Eng_Name Eng_Id Age Salary Dept_Name

Faheem 223368 24 3 000 Mechanical
Rahmanov 137714 30 4 350 Chemical
SQL Syntax : SELECT and WHERE clause – AND & OR

SELECT *
FROM Engineer
WHERE Age = 30
AND (Dept_Name=‘Chemical‘ OR
Result :
Dept_Name=‘Petroleum‘);

Result :

Eng_Name Eng_Id Age Salary Dept_Name

Rahmanov 137714 30 4 350 Chemical
Mphatabalo 117945 30 4580 Petroleum
SQL Syntax : UPDATE

• To update records in a table

UPDATE table_name
SET column1=value, column2=value2,...
WHERE some_column=some_value;
(If you omit the WHERE clause, all records will be updated!)

UPDATE Engineer
SET Age = 30, Salary= 5000
WHERE Eng_Name=‘Jen' AND Dept_Name=‘Mechanical‘;
Result :
Eng_Name Eng_Id Age Salary Dept_Name

Jen 495598 30 5000 Mechanical

SQL Syntax : DELETE

• To delete row/record in a table

DELETE FROM table_name
WHERE some_column=some_value;
(If you omit the WHERE clause, all records will be DELETED!)

DELETE FROM Engineer

WHERE Eng_Name=‘Jen’;

Result :
Eng_Name Eng_Id Age Salary Dept_Name

Jen 495598 30 5000 Mechanical

Query for Joining Database Tables

• To join (combine tables) on common

attributes
• A join is performed when data are
retrieved from more than one table at a
time

SELECT table1.column1, table2.column2

FROM table1, table2
WHERE table1.column1 = table2.column1;
SQL Syntax : Sample Tables

Table : Engineer
Eng_Name Eng_Id Age Salary Dept_Name
Faheem 223368 24 3 000 Mechanical
Rahmanov 137714 30 4 350 Chemical
Mphatabalo 117945 30 4580 Petroleum

Table : Project
Proj_Name Proj_Id Duration Customer_Id Eng_Id

Construction 05 24 1000 19265

Highway 04 12 3388 456782
Hybrid Car 01 36 5000 223368
Deep Water 02 24 2300 117945
SQL Syntax : Join

SELECT Eng_Name, Dept_Name, Proj_Name

FROM Engineer, Project
WHERE Engineer.Eng_id = Project.Eng_id

Result :

Eng_Name Dept_Name Proj_Name

Faheem Mechanical Hybrid Car

Mphatabalo Petroleum Deep Water
How to Connect a relational database with C++ program?

• There are many ways to connect a relational

database to C++ application/program - depends
on what type of database used
• For example, if we use MySQL database (open
source)
– It includes an application programming interface (API)
that allows C and C++ programs to:
• Access the database
• Execute database commands
• Issue queries
• Retrieve the query results
MySQL API Functions

• mysql_init()
– to initialize the connection structure to the
database
• mysql_real_connect(8 arguments)
– To establish connection to the server that
contains the a MySQL database
• mysql_error()
– To detect and display error message
• mysql_close()
– To close the connection
MySQL API Functions

• MYSQL_RES
– Structure type for information concerning the
results of a SELECT or SHOW query.

• MYSQL_ROW
– The array type of data for one row of a SELECT
query
MySQL API Functions (parameters and examples)
• mysql_query (connection pointer, SQL query statement)
– To query for data from the database
Ex :
string query = "SELECT Eng_Name, Age, Dept_Name FROM Engineer"
"WHERE Eng_Id = '"+engId+"'";
mysql_query(connection, query.c_str())

• mysql_store_results (connection pointer)

– To store the queried result in a MYSQL_RES structure
Ex : MYSQL_RES* result = mysql_store_result(connection);

• mysql_num_rows (MYSQL_RES pointer)

– To know the number of rows that the database returns as the result of
the query
Ex : int rows = mysql_num_rows(result);

• mysql_fetch_row (MYSQL_RES pointer)

– To get the data for each row
Ex : MYSQL_ROW row = mysql_fetch_row(result);
MySQL database and C++ program

Steps to establish the MySQL database connection using C++

1. Connect to the MySQL database.
MYSQL* connection = mysql_init();

- * connection – pointer to the MYSQL Structure

- mysql_init() – MySQL API function that we must call for initialization of the connection
structure
2. Call the mysql_real_connect function.
mysql_real_connect(connection, NULL,
NULL,NULL,”bigcpp”, 0, NULL, 0);

- connection - a pointer to an initialized connection object

- NULL, NULL,NULL, - host name, database user name, password.
(Default value is NULL)
- ”bigcpp” - database name
- 0, NULL, 0 - special parameters. Leave at their defaults.

3. Close the connection (at the end of the database program)

mysql_close(connection);
Example
#include <iostream>
#include <iostream>
#include <fstream>
#include <fstream>
#include <iomanip>
#include <iomanip>
#include <string>
#include <string>
#include <mysql.h>
#include <mysql.h>
using namespace std;
using namespace std;
int string_to_int(string s);
int string_to_int(string s);
void print_eng_info(MYSQL* connection, string engId);
void print_eng_info(MYSQL* connection, string engId);
int main () {
int main () {
MYSQL* connection = mysql_init(NULL); /*create connection */
MYSQL* connection = mysql_init(NULL); /*create connection */
/*connect to database and check the connection*/
/*connect to database and check the connection*/
if(mysql_real_connect(connection, NULL, NULL, NULL, "employeeDB", 0, NULL, 0) == NULL)
if(mysql_real_connect(connection, NULL, NULL, NULL, "employeeDB", 0, NULL, 0) == NULL)
{
{
cout << "Error:" << mysql_error(connection) << endl;
cout << "Error:" << mysql_error(connection) << endl;
return 1;
return 1;
}
}
/* ask user to enter engineer id*/
/* ask user to enter engineer id*/
cout << "Enter engineer id: "
cout << "Enter engineer id: "
string engId;
string engId;
cin >> engId;
cin >> engId;
print_eng_info(connection, engId);
print_eng_info(connection, engId);
mysql_close(connection);
mysql_close(connection);
return 0;
return 0;
}
}
Example - cont

/*
/* perform
perform the
the query
query */
*/
void print_eng_info(MYSQL* connection, string engId)
void print_eng_info(MYSQL* connection, string engId)
{
{
string query = "SELECT Eng_Name, Age, Dept_Name FROM Engineer"
string query = "SELECT Eng_Name, Age, Dept_Name FROM Engineer"
"WHERE Eng_Id = '"+engId+"'";
"WHERE Eng_Id = '"+engId+"'";
/* check the query */
/* check the query */
if(mysql_query(connection, query.c_str()) != 0)
if(mysql_query(connection, query.c_str()) != 0)
{
{
cout << "Error: " << mysql_error(connection) << endl;
cout << "Error: " << mysql_error(connection) << endl;
return;
return;
}
/*}store the result from query*/
/* store the result from query*/
MYSQL_RES* result = mysql_store_result(connection);
MYSQL_RES* result = mysql_store_result(connection);
/*if the result cannot be retrieved*/
/*if the result cannot be retrieved*/
if(result == NULL)
if(result == NULL)
{
{
cout << "Error: " << mysql_error(connection) << endl;
cout << "Error: " << mysql_error(connection) << endl;
return;
return;
}
}
...
...
Example - cont…

/* if no result */
/* if no result */
int rows = mysql_num_rows(result);
int rows = mysql_num_rows(result);
if(rows == 0)
if(rows == 0)
{
{
cout << "Information not found" << endl;
cout << "Information not found" << endl;
return;
return;
}
}
/* get a row of data*/
/* get a row of data*/
MYSQL_ROW row = mysql_fetch_row(result);
MYSQL_ROW row = mysql_fetch_row(result);
string name = row[0];
string name = row[0];
int age = string_to_int(row[1]); //converts to int
int age = string_to_int(row[1]); //converts to int
string dept = row[2];
string dept = row[2];
/*print result*/
/*print result*/
cout << " Information found\n\n";
cout << " Information found\n\n";
cout << "Engineer ID : " << engId << endl;
cout << "Engineer ID : " << engId << endl;
cout << "Name : " << name << endl;
cout << "Name : " << name << endl;
cout << "Age : " << age << end;
cout << "Age : " << age << end;
cout << "Department : " << dept << endl;
cout << "Department : " << dept << endl;
}// end of function
}// end of function
Example - Cont…

/* Converts a string to integer*/

/* Converts a string to integer*/
int string_to_int(string s)
int string_to_int(string s)
{
{
instringstream instr(s);
instringstream instr(s);
int n;
int n;
instr >> n;
instr >> n;
return n;
return n;
}
}
Summary

• Tables are basic building blocks for

relational database.
• The relational table is composed of
intersecting rows(tuples/records) and
columns(fields).
• Each row represents a single entity and
each column represents the
characteristics/attributes of the entities.
Summary

• The SQL statements can be divided into 2

categories: Data Definition Language (DDL)
commands and Data Manipulation Language
(DML) commands.
• Data Definition Language (DDL)
– Commands that define a database,
• CREATE, ALTER, DROP, TRUNCATE
• Data Manipulation Language (DML)
– Commands that maintain and query a
database
• INSERT, SELECT, UPDATE, DELETE
References

• Database Systems Design, Implementation, &

Management, Seven Edition
– Peter Rob & Carlos Coronel
– ISBN 1-418-83593-5
– Thompson
• http://www.w3schools.com/sql/default.asp
• http://www.thecodefish.com/SQL/SQL_Tutorial_-
_The_Basics_-_Part_III