Relational Databases and SQL

Database Managment Systems (DBMS):

MS Access, MS SQL Server, Oracle, Sybase, MySQL, ...

Relational databses

model the data as a collection of relations/tables

SQL

outline

Create a table
Change the schema of a table
Insert data records into a table
Delete records from a table
Query

Example: a database for wikipedia edits

Table of editors

username	IsAdmin	Content
Alex	False	abc#hj
Bob	True	aaaaa
John	False	dsds

Table of wiki-articles

title	Content
Data_science	Data Science is an interdisciplinary field about ..
Python_prog_language	Python is a widely used high-level, general-purpose, ..

Table of edits

editor	edited_title	modified_time
John	Python_prog_language	2013-03-07 13:25:43
John	Data_science	2013-04-12 20:14:13
Alex	Data_science	2013-04-13 10:04:17
Alex	Python_prog_language	2013-04-13 17:34:31
Alex	Data_science	2015-07-02 12:41:27
Bob	Python_prog_language	2015-11-08 09:24:36

mysql -h localhost -D [select-databse] -u [username] -p [prompt for password]

SQL: Create Table

Table of editors

CREATE TABLE Editors (
    username VARCHAR(40) PRIMARY KEY,
    is_admin BOOLEAN,
    content VARCHAR(500)
);

describe Editors;

+----------+--------------+------+-----+---------+-------+
| Field    | Type         | Null | Key | Default | Extra |
+----------+--------------+------+-----+---------+-------+
| username | varchar(40)  | NO   | PRI | NULL    |       |
| is_admin | tinyint(1)   | YES  |     | NULL    |       |
| content  | varchar(500) | YES  |     | NULL    |       |
+----------+--------------+------+-----+---------+-------+
3 rows in set (0.00 sec)

Table of articles

CREATE TABLE Articles (
    title VARCHAR(40) PRIMARY KEY,
    content VARCHAR(500)
);

describe Articles;

+---------+--------------+------+-----+---------+-------+
| Field   | Type         | Null | Key | Default | Extra |
+---------+--------------+------+-----+---------+-------+
| title   | varchar(40)  | NO   | PRI | NULL    |       |
| content | varchar(500) | YES  |     | NULL    |       |
+---------+--------------+------+-----+---------+-------+
2 rows in set (0.00 sec)

Table of Edits

Primary key is a combination of multiple attributes
Use foreign key to reference to records in other two tables

CREATE TABLE Edits (
    editor VARCHAR(40),
    edited_title VARCHAR(40),
    modified_time DATETIME,
    PRIMARY KEY (editor, edited_title, modified_time),
    FOREIGN KEY (editor) REFERENCES Editors(username),
    FOREIGN KEY (edited_title) REFERENCES Articles (title)
);

describe Edits;

+---------------+-------------+------+-----+---------------------+-------+
| Field         | Type        | Null | Key | Default             | Extra |
+---------------+-------------+------+-----+---------------------+-------+
| editor        | varchar(40) | NO   | PRI |                     |       |
| edited_title  | varchar(40) | NO   | PRI |                     |       |
| modified_time | datetime    | NO   | PRI | 0000-00-00 00:00:00 |       |
+---------------+-------------+------+-----+---------------------+-------+
3 rows in set (0.00 sec)

FOREIGN KEY

Foregn key creates a connection between records in two tables.
Foreign key enforces that records in Edits table must have a correspondant entry in other table.

MySQL Data Types

Numeric types
- INTEGER,
- Signed vs. Unsigned

Type	Number of bytes
Tinyint	1
Smallint	2
Mediumint	3
Int	4
Bigint	8

Note: INT is a synonym for INTEGER.

Date and time types
- DATE, TIME, DATETIME, TIMESTAMP, YEAR
- YEAR(2) vs YEAR(4)

Type	Format
Date	'0000-00-00'
Time	'00:00:00'
Datetime	'0000-00-00 00:00:00'
Timestamp	'0000-00-00 00:00:00'
Year	0000

String
- CHAR, VARCHAR

Summary of MySQL Commands

SET PASSWORD=PASSWORD('new password');
SHOW DATABASES;
USE databasename;
SHOW TABLES;
DESCRIBE tablename;
DROP TABLE tablename;
SOURCE script-file;
LOAD DATA INFILE /path/to/a/file.txt INTO TABLE tablename;

Alter an existing table:

ALTER TABLE tablename ADD new-column-name format/type;
ALTER TABLE tablename DROP COLUMN column-name;
ALTER TABLE tablename MODIFY column-name new-type/format DEFAULT default-value;
ALTER TABLE tablename DROP PRIMARY KEY;

Insert or Delete Operations

INSERT INTO tablename VALUES ('attribue1', 'attribute2', ..);
DELETE FROM tablename WHERE condition-statement;

Example: Insert

-- insert new records into articles table
 INSERT INTO Articles VALUES ('Data_science', 'Data Science is an interdisciplinary field about ..');
 INSERT INTO Articles VALUES ('Python_prog_language', 'Python is a widely used high-level, general-purpose, ..');

 -- insert new records into Editors table
 INSERT INTO Editors VALUES ('Alex', False, 'I am a python developer');
 INSERT INTO Editors VALUES ('Bob', True, 'Database administrator');
 INSERT INTO Editors VALUES ('John', False, 'Machine learning enthusiast');

 -- insert new records into edits table
 INSERT INTO Edits VALUES ('John', 'Python_prog_language', '2013-03-07 13:25:43');
 INSERT INTO Edits VALUES ('John', 'Data_science', '2013-04-12 20:14:13');
 INSERT INTO Edits VALUES ('Alex', 'Data_science', '2013-04-13 10:04:17');
 INSERT INTO Edits VALUES ('Alex', 'Python_prog_language', '2013-04-13 17:34:31');
 INSERT INTO Edits VALUES ('Alex', 'Data_science', '2015-07-02 12:41:27');
 INSERT INTO Edits VALUES ('Bob', 'Python_prog_language', '2015-11-08 09:24:36');

Retrieval Query

SELECT <attribute-list> FROM <table list> WHERE <condition>;

Example retirieval query

+----------+----------+-----------------------------+
| username | is_admin | content                     |
+----------+----------+-----------------------------+
| Alex     |        0 | I am a python developer     |
| Bob      |        1 | Database administrator      |
| John     |        0 | Machine learning enthusiast |
+----------+----------+-----------------------------+

Exmaple of Complex Queries

1) Aggregate, sort, and limit results

SELECT editor, count(*)
FROM Edits
GROUP BY editor
ORDER BY count(*) DESC
LIMIT 2;

+--------+----------+
| editor | count(*) |
+--------+----------+
| Alex   |        3 |
| John   |        2 |
+--------+----------+

2) Join two tables

SELECT edited_title
FROM Editors, Edits
WHERE editor=username AND is_admin = True ;

+----------------------+
| edited_title         |
+----------------------+
| Python_prog_language |
+----------------------+

3) count distinct items for non-admin editors

SELECT username, COUNT(DISTINCT edited_title) AS num_distinct
FROM Editors, Edits
WHERE editor = username AND is_admin = False
GROUP BY editor;

+----------+--------------+
| username | num_distinct |
+----------+--------------+
| Alex     |            2 |
| John     |            2 |
+----------+--------------+

Using MySQL in PHP

Four steps

Connect to a database, login: $con=mysql("host", "user", "passwd", "dbname");
Submit a SQL query $results = $con->query("query-command");
For a retrieval query, fetch the results $row = $result->fetch_row();
Close the connection

Example

<?php
    $con = mysql("localhost", "username", "mypassword", "database");

    if ($con->connect_errno > 0) {
        die ('Unable to connect to database');
    }

    $query = "CREATE TABLE IF NOT EXISTS wiki_edit (

    )";

    if(!($result = $con->query($query))) {
        echo "Cannot create table <br>";
    }
?>

Load Data from a database

<?php
    $con = mysql("localhost", "username", "mypassword", "database");

    if ($con->connect_errno > 0) {
        die ('Unable to connect to database');
    }

    $query = "SELECT * FROM wiki_edit LIMIT 20";

    $result = $con->query($query);

    while ($cols = $result->$fetch_row()) {
        echo $cols[1]." edited by ". $cols[3] . " on ".$cols[2]."<br>";
    }
?>



In [ ]: