Lecture 11 Introduction to Relational Database

Lecture 11Introduction to Relational Database Presented By Dr. ShazzadHosain Asst. Prof. EECS, NSU

CSC382: Internet & Web TechnologyMySQL Database System Lecture Contents Database Concepts SQL Commands Database Connectivity Connectivity Example DDL Query DML Query MySql Functions

Client-Server Interaction • Client program can be a MySQL command line client, GUI client, or a program written in any language such as C, Perl, PHP, Java that has an interface to the MySQL server. • MySQL databases are ideal for storing that data we have collected about a user or for holding user preferences between visits. It is free and it is easy. Make a request(SQL query) MySQLServer ClientProgram Get results

3-Tier Architecture WebBrowser(Client) WebServer DatabaseServer PHP

Program Program Program Program Data Data Data Database DBMS Database Management System • Collection of data = Database (DB) • Set of interrelated data and programs to access those data is called DBMS • DBMS Provides environment that is convenient and efficient to use for data retrieval and storage

Relational Database Basics • Today’s database implementations are almost all based on the relational model • A relational database management system consists of a number of databases. • Each database consists of a number of tables. • It represents data in a two-dimensional table called a relation • The attributes are located across the top of the relation attributes name 6

Tuples • The rows in the relation (other than attribute row) are called tuples • A tuple has one component or value for each attribute of the relation • A tuple should never appear more than once in a relation • We must ensure that the relation has a sufficient set of attributes so that no two tuples will have the same values for all attributes tuples 7

Database Languages (Query) • DBMS provide two types of language • One to specify schema and create the database • One to express database queries and updates • Data-Definition Language (DDL Query) • Schema is specified by a set of definitions expressed by the DDL • Result is set of tables stored in the Data Dictionary • Data Dictionary is a file that contains metadata, data about data • Data-Manipulation Language (DML Query) • Language for accessing and manipulating the data organized by the appropriate data model. That is, data retrieval, insertion, deletion, modification

SQL commands SHOW, USE SHOW Display databases or tables in current database; Example (command line client): show databases; show tables; USE Specify which database to use Example use bookstore; 9

Entering commands (1) • Show all the databases • SHOW DATABASES; mysql> SHOW DATABASES;+-------------+| Database |+-------------+| bookstore || employee_db || mysql || student_db || test || web_db |+-------------+

Entering commands (2) • Choosing a database and showing its tables • USE test;SHOW tables; mysql> USE test;Database changedmysql> SHOW tables;+----------------+| Tables_in_test |+----------------+| books || name2 || names || test |+----------------+4 rows in set (0.00 sec)mysql>

Entering commands (3) • Show the structure of a table • DESCRIBE names; mysql> DESCRIBE names;+-----------+-------------+------+-----+---------+----------------+| Field | Type | Null | Key | Default | Extra |+-----------+-------------+------+-----+---------+----------------+| id | int(11) | | PRI | NULL | auto_increment || firstName | varchar(20) | | | | || lastName | varchar(20) | | | | |+-----------+-------------+------+-----+---------+----------------+3 rows in set (0.00 sec)mysql>

SQL Commands • SQL is a reasonably powerful query language. • However it is incredibly simple. You can learn it in a night. • The fundamental SQL commands are: • CREATE • SELECT • INSERT • DELETE • UPDATE

Example of SQL DDL mark studentID first_name last_name markstable USE test;CREATE TABLE marks (studentID SMALLINT AUTO_INCREMENT NOT NULL,first_nameVARCHAR(20) NOT NULL,last_nameVARCHAR(20) NOT NULL, mark SMALLINT DEFAULT 0 NOT NULL, PRIMARY KEY (studentID)); 14

marks.sql -- Insert some rows into marks table INSERT INTO marks (first_name, last_name, mark) VALUES ('Fred', 'Jones', 78);INSERT INTO marks (first_name, last_name, mark) VALUES ('Bill', 'James', 67);INSERT INTO marks (first_name, last_name, mark) VALUES ('Carol', 'Smith', 82);INSERT INTO marks (first_name, last_name, mark) VALUES ('Bob', 'Duncan', 60);INSERT INTO marks (first_name, last_name, mark) VALUES ('Joan', 'Davis', 86); 15

Conditional Creation Conditional database creation CREATE DATABASE IF NOT EXISTS db_name; Conditional table creation CREATE TABLE IF NOT EXISTS table_name; 16

Selecting the complete table Entering commands SELECT * FROM marks; +-----------+------------+-----------+------+| studentID | first_name | last_name | mark |+-----------+------------+-----------+------+| 1 | Fred | Jones | 78 || 2 | Bill | James | 67 || 3 | Carol | Smith | 82 || 4 | Bob | Duncan | 60 || 5 | Joan | Davis | 86 |+-----------+------------+-----------+------+5 rows in set (0.00 sec) 17

PHP to MySQL Connectivity • mysql_connect() establishes a connection to a MySQL server. • It takes 3 parameters. • The address of the server • Your Username for that db account • Your password $conn=mysql_connect(“address",“user“,“pass”); • XAMPP mysql server is found at the following address: localhost 18

PHP to MySQL Connectivity • In our code mysql_select_db() then tells PHP that any queries we make are against the mydb database. mysql_select_db(“dbname",$conn); • We could create multiple connections to databases on different servers. But for now, you’ll only need one database. • mysql_query() does all the hard work. • Using the database connection identifier, it sends a line of SQL to the MySQL server to be processed. • This is the key command for interacting with the database. 19

Extracting Query Result • Finally, mysql_result() is used to display the values of fields from our query: mysql_result($result,0,"first"); • Using $result, we go to the first row, which is numbered 0, and return the value of the specified fields. • Close the connection to the database server mysql_close(); 20

First MySql/PHP Program <? $db = mysql_connect("localhost", "root"); mysql_select_db("mydb",$db); $result = mysql_query("SELECT * FROM employees"); $firstname = mysql_result($result,0,"first"); $lastname = mysql_result($result,0,“last"); $address = mysql_result($result,0,“address"); ?> Hello <?=$firstname?> <?=$lastname?> <BR> Your address is <?=$address?> 21

Unpolitically Correct Create Example • For example, to create a table from our PHP code you might type: mysql_query(“CREATE TABLE players ( name varchar(30), age integer)”); • Remember that this is something that you would only want to do once – once the table is created we don’t want to wipe it by accident 22

MySQL Insert Example • Equally we can populate our tables with INSERT statements via mysql_query() mysql_query(“INSERT INTO player VALUES (‘Zidane',32)”); mysql_query(“INSERT INTO player VALUES (‘Ronaldinho',28)”); mysql_query(“INSERT INTO player VALUES (‘Pele',58)”); • These are hard coded examples – but we could be using variables in these statements 23

Mysql Select Example • We use a SELECT statement to grab data from a certain table and then put the result into a variable ready to analyse… $result = mysql_query(“SELECT * FROM players WHERE age<35”); • However now result has all the info we want inside it… how are we going to extract it in the form we want? 24

mysql_fetch_row() • mysql_This function gets a result row as an enumerated array. • subsequent calls to mysql_fetch_row() would return the next row in the result set, or FALSE if there are no more rows. <? mysql_connect(“mysql_address", "mysql_user", "mysql_pass"); mysql_select_db(“dbname"); $result = mysql_query("SELECT name, age FROM players"); while ($player = mysql_fetch_array($result)) { print “Player $player[name] is “; print “$player[age] years old”; } mysql_free_result($result); ?> 25

mysql_num_rows() • mysql_num_rows() returns the number of rows in a result set. This command is only valid for SELECT statements. mysql_query(“SELECT * FROM players WHERE age<35); print mysql_num_rows().“players are younger than 35"; • It’s a great function for when you need to loop round all the results in your query, or just to know how many matches you got 26

mysql_rows_affected() • mysql_affected_rows() returns the number of rows affected by the last INSERT, UPDATE or DELETE query associated with. For example: mysql_query("DELETE FROM mytable WHERE id < 10"); print "Records deleted: ".mysql_affected_rows()."<BR>"; • N.b. this function does not work with SELECT statements - only on statements which modify records. 27

A db_connect Function This function can be used in scripts to connect to a database. Put it in a file called db_connect.php in your include path <?php function db_connect($db_name){ $host_name = "localhost:3306";$user_name = "xxxxx"; $password = "yyyyy";$db_link = mysql_connect($host_name,$user_name, $password) or die("Could not connect to $host_name");mysql_select_db($db_name) or die("Could not select database $db_name"); return $db_link;} ?> 28

The SELECT Command There are many other variations of the select command. Example: finding the number of records in a table assuming a primary key called id: Can also perform searching using the WHERE option SELECT COUNT(id) FROM table_name 29

MySQL Functions (1) How many rows are there ? Can use COUNT(marks) instead of COUNT(*) SELECTCOUNT(*) FROM marks; +----------+| COUNT(*) |+----------+| 5 |+----------+1 row in set (0.00 sec) 30

MySQL Functions (2) What is the sum of all the marks? SELECTSUM(mark) FROM marks; +-----------+| SUM(mark) |+-----------+| 373 |+-----------+1 row in set (0.00 sec) 31

MySQL Functions (3) What is the average mark? SELECTAVG(mark) FROM marks; +-----------+| AVG(mark) |+-----------+| 74.6000 |+-----------+1 row in set (0.00 sec) 32

MySQL Functions (4) What is the minimum mark? SELECTMIN(mark) FROM marks; +-----------+| MIN(mark) |+-----------+| 60 |+-----------+1 row in set (0.00 sec) 33

MySQL Functions (5) What is the maximum mark? SELECTMAX(mark) FROM marks; +-----------+| MAX(mark) |+-----------+| 86 |+-----------+1 row in set (0.00 sec) 34

Entering commands • Updating a record • UPDATE names SETlastName = 'Stone'WHERE id=3; • SELECT * FROM names; mysql> UPDATE names SET lastName = 'Stone' WHERE id=3;Query OK, 1 row affected (0.28 sec)Rows matched: 1 Changed: 1 Warnings: 0mysql> SELECT * FROM names;+----+-----------+------------+| id | firstName | lastName |+----+-----------+------------+| 1 | Fred | Flintstone || 2 | Barney | Rubble || 3 | Ralph | Stone |+----+-----------+------------+3 rows in set (0.00 sec)mysql>

The DROP Command • To delete databases and tables use the DROP command • Examples • DROP DATABASE db_name; • DROP DATABASE IF EXISTS db_name; • DROP TABLE table_name; • DROP TABLE IF EXISTS table_name; Note: Don't confuse DROP with DELETE which deletes rowsof a table.

The WHERE Clause • Select rows according to some criterion SELECT * FROM marks WHERE studentID > 1AND studentID < 5; +-----------+------------+-----------+------+| studentID | first_name | last_name | mark |+-----------+------------+-----------+------+| 2 | Bill | James | 67 || 3 | Carol | Smith | 82 || 4 | Bob | Duncan | 60 |+-----------+------------+-----------+------+3 rows in set (0.01 sec)

The WHERE Clause • Select rows with marks >= 80 SELECT * FROM marks WHERE mark >= 80; +-----------+------------+-----------+------+| studentID | first_name | last_name | mark |+-----------+------------+-----------+------+| 3 | Carol | Smith | 82 || 5 | Joan | Davis | 86 |+-----------+------------+-----------+------+2 rows in set (0.00 sec)

The ORDER BY Clause • Select rows according to some criterion SELECT * FROM marks ORDER BY mark DESC; +-----------+------------+-----------+------+| studentID | first_name | last_name | mark |+-----------+------------+-----------+------+| 5 | Joan | Davis | 86 || 3 | Carol | Smith | 82 || 1 | Fred | Jones | 78 || 2 | Bill | James | 67 || 4 | Bob | Duncan | 60 |+-----------+------------+-----------+------+5 rows in set (0.00 sec)

Searching Using LIKE (1) • LIKE is used to search a table for values containing a search string: • There are two wild-card characters used to specify patterns: • _ matches a single character • % matches zero or more characters • Can also use NOT LIKE • Searching is case insensitive

Searching Using LIKE (2) • Example: last names in marks table that begin with J • Example: first names that have 3 letters SELECT * FROM marks WHERE last_nameLIKE'J%'; SELECT * FROM marks WHERE first_nameLIKE'_ _ _';

employee_db.sql (1) CREATE TABLE employees (employeeIDSMALLINT NOT NULL, name VARCHAR(20) NOT NULL, position VARCHAR(20) NOT NULL, address VARCHAR(40) NOT NULL,PRIMARY KEY (employeeID));INSERT INTO employees VALUES (1001, 'Fred', 'programmer', '13 Windle St');INSERT INTO employees VALUES (1002, 'Joan', 'programmer', '23 Rock St');INSERT INTO employees VALUES (1003, 'Bill', 'manager', '37 Front St');

employee_db.sql (2) CREATE TABLE jobs (employeeIDSMALLINT NOT NULL, hours DECIMAL(5,2) NOT NULL,);INSERT INTO jobs VALUES (1001, 13.5);INSERT INTO jobs VALUES (1002, 2);INSERT INTO jobs VALUES (1002, 6.25);INSERT INTO jobs VALUES (1003, 4);INSERT INTO jobs VALUES (1001, 1);INSERT INTO jobs VALUES (1003, 7);INSERT INTO jobs VALUES (1003, 9.5);

Database Tables Jobs table Employees table

Select Queries With Joins (1) • Cartesian product query SELECT * FROM employees, jobs; +------------+------+------------+--------------+------------+-------+| employeeID | name | position | address | employeeID | hours |+------------+------+------------+--------------+------------+-------+| 1001 | Fred | programmer | 13 Windle St | 1001 | 13.50 || 1002 | Joan | programmer | 23 Rock St | 1001 | 13.50 || 1003 | Bill | manager | 37 Front St | 1001 | 13.50 || 1001 | Fred | programmer | 13 Windle St | 1002 | 2.00 || 1002 | Joan | programmer | 23 Rock St | 1002 | 2.00 || 1003 | Bill | manager | 37 Front St | 1002 | 2.00 || 1001 | Fred | programmer | 13 Windle St | 1002 | 6.25 || 1002 | Joan | programmer | 23 Rock St | 1002 | 6.25 || 1003 | Bill | manager | 37 Front St | 1002 | 6.25 |

Select Queries With Joins (2) • Cartesian product query (continued) | 1001 | Fred | programmer | 13 Windle St | 1003 | 4.00 || 1002 | Joan | programmer | 23 Rock St | 1003 | 4.00 || 1003 | Bill | manager | 37 Front St | 1003 | 4.00 || 1001 | Fred | programmer | 13 Windle St | 1001 | 1.00 || 1002 | Joan | programmer | 23 Rock St | 1001 | 1.00 || 1003 | Bill | manager | 37 Front St | 1001 | 1.00 || 1001 | Fred | programmer | 13 Windle St | 1003 | 7.00 || 1002 | Joan | programmer | 23 Rock St | 1003 | 7.00 || 1003 | Bill | manager | 37 Front St | 1003 | 7.00 || 1001 | Fred | programmer | 13 Windle St | 1003 | 9.50 || 1002 | Joan | programmer | 23 Rock St | 1003 | 9.50 || 1003 | Bill | manager | 37 Front St | 1003 | 9.50 |+------------+------+------------+--------------+------------+-------+21 rows in set (0.01 sec) The cartesian product query is rarely what we want.

Select Queries With Joins (3) • Substitution SELECT name, hours FROM employees, jobs WHEREemployees.employeeID = jobs.employeeID; +------+-------+| name | hours |+------+-------+| Fred | 13.50 || Joan | 2.00 || Joan | 6.25 || Bill | 4.00 || Fred | 1.00 || Bill | 7.00 || Bill | 9.50 |+------+-------+ 7 rows in set (0.00 sec) Here we are replacing the employeeID numbers in the jobs table by the employee's name

Select Queries With Joins (4) • Entries only for Fred SELECT name, hours FROM employees, jobs WHEREemployees.employeeID = jobs.employeeID ANDname = 'Fred'; +------+-------+| name | hours |+------+-------+| Fred | 13.50 || Fred | 1.00 |+------+-------+ 2 rows in set (0.00 sec)

Select Queries With Joins (5) • Total hours worked for each person SELECT name, SUM(hours) FROM employees, jobsWHERE employees.employeeID = jobs.employeeIDGROUP BY name; +------+------------+| name | SUM(hours) |+------+------------+| Bill | 20.50 || Fred | 14.50 || Joan | 8.25 |+------+------------+3 rows in set (0.00 sec)

Lecture 11 Introduction to Relational Database