![Page 1: Essential Skills RDBMS and SQL · RDBMS History Relational Database Based on a paper by Edgar F. Codd in 1970 Allowed to take advantage of disk based storage. Earlier DBMS implementations](https://reader030.vdocuments.us/reader030/viewer/2022041012/5ebebdbae89b6e2cbf333185/html5/thumbnails/1.jpg)
Essential Skills - RDBMS and SQL
Essential SkillsRDBMS and SQL
Daniël van [email protected]
October 2011
![Page 2: Essential Skills RDBMS and SQL · RDBMS History Relational Database Based on a paper by Edgar F. Codd in 1970 Allowed to take advantage of disk based storage. Earlier DBMS implementations](https://reader030.vdocuments.us/reader030/viewer/2022041012/5ebebdbae89b6e2cbf333185/html5/thumbnails/2.jpg)
What is a Database?
A structured collection of data
![Page 3: Essential Skills RDBMS and SQL · RDBMS History Relational Database Based on a paper by Edgar F. Codd in 1970 Allowed to take advantage of disk based storage. Earlier DBMS implementations](https://reader030.vdocuments.us/reader030/viewer/2022041012/5ebebdbae89b6e2cbf333185/html5/thumbnails/3.jpg)
What is a DBMS
DataBase Management System A system which allows you to store data in a
generic way.
![Page 4: Essential Skills RDBMS and SQL · RDBMS History Relational Database Based on a paper by Edgar F. Codd in 1970 Allowed to take advantage of disk based storage. Earlier DBMS implementations](https://reader030.vdocuments.us/reader030/viewer/2022041012/5ebebdbae89b6e2cbf333185/html5/thumbnails/4.jpg)
RDBMS History
Relational Database Based on a paper by Edgar F. Codd in 1970 Allowed to take advantage of disk based
storage. Earlier DBMS implementations forced the
programmer to do a full scan over the data, which is okay for tape based storage.
![Page 5: Essential Skills RDBMS and SQL · RDBMS History Relational Database Based on a paper by Edgar F. Codd in 1970 Allowed to take advantage of disk based storage. Earlier DBMS implementations](https://reader030.vdocuments.us/reader030/viewer/2022041012/5ebebdbae89b6e2cbf333185/html5/thumbnails/5.jpg)
Why use a RDBMS?
You don't have to write your own datastore Allows concurrent access Enforce quality of data
Constraints Column types
Makes your data searchable
![Page 6: Essential Skills RDBMS and SQL · RDBMS History Relational Database Based on a paper by Edgar F. Codd in 1970 Allowed to take advantage of disk based storage. Earlier DBMS implementations](https://reader030.vdocuments.us/reader030/viewer/2022041012/5ebebdbae89b6e2cbf333185/html5/thumbnails/6.jpg)
The relational model
Store data in tables Normalization of data Link tables together with keys
![Page 7: Essential Skills RDBMS and SQL · RDBMS History Relational Database Based on a paper by Edgar F. Codd in 1970 Allowed to take advantage of disk based storage. Earlier DBMS implementations](https://reader030.vdocuments.us/reader030/viewer/2022041012/5ebebdbae89b6e2cbf333185/html5/thumbnails/7.jpg)
ACID
Atomicity Something happens completely or not at all
Consistency Only consistent data will be written to the database
Isolation Transactions are isolated from each other Ensures reliable outcomes
Durability Data is protected against crashes
![Page 8: Essential Skills RDBMS and SQL · RDBMS History Relational Database Based on a paper by Edgar F. Codd in 1970 Allowed to take advantage of disk based storage. Earlier DBMS implementations](https://reader030.vdocuments.us/reader030/viewer/2022041012/5ebebdbae89b6e2cbf333185/html5/thumbnails/8.jpg)
SQL
Structured Query Language DDL - Data Definition Language
CREATE TABLE, ALTER TABLE
DML - Data Manipulation Language INSERT, UPDATE, DELETE, SELECT
DCL - Data Control Languge GRANT, REVOKE
TCL - Transaction Control Language START TRANSACTION, COMMIT, ROLLBACK
![Page 9: Essential Skills RDBMS and SQL · RDBMS History Relational Database Based on a paper by Edgar F. Codd in 1970 Allowed to take advantage of disk based storage. Earlier DBMS implementations](https://reader030.vdocuments.us/reader030/viewer/2022041012/5ebebdbae89b6e2cbf333185/html5/thumbnails/9.jpg)
SQL
Declarative Language Many standards
SQL-92, SQL:2003, SQL:2006,… ANSI, FIPS, ISO/IEC
Divided into parts SQL/MED SQL/PSM SQL/XML …
![Page 10: Essential Skills RDBMS and SQL · RDBMS History Relational Database Based on a paper by Edgar F. Codd in 1970 Allowed to take advantage of disk based storage. Earlier DBMS implementations](https://reader030.vdocuments.us/reader030/viewer/2022041012/5ebebdbae89b6e2cbf333185/html5/thumbnails/10.jpg)
Three-valued Logic
A column can have three different kind of values:
1. A value: “some string” or 25
2. An empty or zero value: “” or 0
3. No value: NULL
![Page 11: Essential Skills RDBMS and SQL · RDBMS History Relational Database Based on a paper by Edgar F. Codd in 1970 Allowed to take advantage of disk based storage. Earlier DBMS implementations](https://reader030.vdocuments.us/reader030/viewer/2022041012/5ebebdbae89b6e2cbf333185/html5/thumbnails/11.jpg)
Foreign Keys
Foreign Keys are relations between tables Your database will protect the relationship by
enforcing the foreign key. This is called a foreign key constraint.
![Page 12: Essential Skills RDBMS and SQL · RDBMS History Relational Database Based on a paper by Edgar F. Codd in 1970 Allowed to take advantage of disk based storage. Earlier DBMS implementations](https://reader030.vdocuments.us/reader030/viewer/2022041012/5ebebdbae89b6e2cbf333185/html5/thumbnails/12.jpg)
Data Definition Languagetest=# CREATE TABLE "user" (uid SERIAL PRIMARY KEY, "name" VARCHAR(255));
NOTICE: CREATE TABLE will create implicit sequence "user_uid_seq" for serial column "user.uid"
NOTICE: CREATE TABLE / PRIMARY KEY will create implicit index "user_pkey" for table "user"
CREATE TABLE
test=# CREATE TABLE "group" (gid SERIAL PRIMARY KEY, "name" VARCHAR(255));
NOTICE: CREATE TABLE will create implicit sequence "group_gid_seq" for serial column "group.gid"
NOTICE: CREATE TABLE / PRIMARY KEY will create implicit index "group_pkey" for table "group"
CREATE TABLE
test=# CREATE TABLE "groupmembers" (mid SERIAL PRIMARY KEY, uid INT REFERENCES "user" (uid), gid INT REFERENCES "group" (gid));
NOTICE: CREATE TABLE will create implicit sequence "groupmembers_mid_seq" for serial column "groupmembers.mid"
NOTICE: CREATE TABLE / PRIMARY KEY will create implicit index "groupmembers_pkey" for table "groupmembers"
CREATE TABLE
![Page 13: Essential Skills RDBMS and SQL · RDBMS History Relational Database Based on a paper by Edgar F. Codd in 1970 Allowed to take advantage of disk based storage. Earlier DBMS implementations](https://reader030.vdocuments.us/reader030/viewer/2022041012/5ebebdbae89b6e2cbf333185/html5/thumbnails/13.jpg)
Data Definition Language
CREATE ALTER DROP Data Types
INT CHAR, VARCHAR BLOB
Primary Keys, Foreign Keys
![Page 14: Essential Skills RDBMS and SQL · RDBMS History Relational Database Based on a paper by Edgar F. Codd in 1970 Allowed to take advantage of disk based storage. Earlier DBMS implementations](https://reader030.vdocuments.us/reader030/viewer/2022041012/5ebebdbae89b6e2cbf333185/html5/thumbnails/14.jpg)
Data Manipulation Language
test=# INSERT INTO "user" (name) VALUES ('Steve');
INSERT 0 1
test=# INSERT INTO "group" (name) VALUES ('Steve''s Group');
INSERT 0 1
test=# INSERT INTO "groupmembers" (uid,gid) VALUES (1,1);
INSERT 0 1
test=# SELECT * FROM "user";
uid | name
-----+-------
1 | Steve
(1 row)
![Page 15: Essential Skills RDBMS and SQL · RDBMS History Relational Database Based on a paper by Edgar F. Codd in 1970 Allowed to take advantage of disk based storage. Earlier DBMS implementations](https://reader030.vdocuments.us/reader030/viewer/2022041012/5ebebdbae89b6e2cbf333185/html5/thumbnails/15.jpg)
Data Manipulation Language
test=# UPDATE "user" SET name='Steven' WHERE uid=1;
UPDATE 1
test=# UPDATE "group" SET name='Steven''s Group' WHERE gid=1;
UPDATE 1
test=# DELETE FROM "user" WHERE uid=1;
ERROR: update or delete on table "user" violates foreign key constraint "groupmembers_uid_fkey" on table "groupmembers"
DETAIL: Key (uid)=(1) is still referenced from table "groupmembers".
test=# DELETE FROM "groupmembers" WHERE uid=1;
DELETE 1
test=# DELETE FROM "user" WHERE uid=1;
DELETE 1
![Page 16: Essential Skills RDBMS and SQL · RDBMS History Relational Database Based on a paper by Edgar F. Codd in 1970 Allowed to take advantage of disk based storage. Earlier DBMS implementations](https://reader030.vdocuments.us/reader030/viewer/2022041012/5ebebdbae89b6e2cbf333185/html5/thumbnails/16.jpg)
Data Manipulation Language
SELECT, INSERT, UPDATE, DELETE WHERE
…WHERE fname='Richard' AND lname LIKE 's%'
Functions SELECT upper(fname) FROM … …WHERE upper(fname) = 'RICHARD'
![Page 17: Essential Skills RDBMS and SQL · RDBMS History Relational Database Based on a paper by Edgar F. Codd in 1970 Allowed to take advantage of disk based storage. Earlier DBMS implementations](https://reader030.vdocuments.us/reader030/viewer/2022041012/5ebebdbae89b6e2cbf333185/html5/thumbnails/17.jpg)
Data Manipulation Language
GROUP BY GROUP BY lname,fname SUM(), COUNT(), MAX(), MIN()
ORDER BY LIMIT 100
![Page 18: Essential Skills RDBMS and SQL · RDBMS History Relational Database Based on a paper by Edgar F. Codd in 1970 Allowed to take advantage of disk based storage. Earlier DBMS implementations](https://reader030.vdocuments.us/reader030/viewer/2022041012/5ebebdbae89b6e2cbf333185/html5/thumbnails/18.jpg)
Joinstest=# SELECT u.name Username FROM "user" u LEFT JOIN groupmembers gm ON gm.uid=u.uid LEFT JOIN "group" g ON g.gid=gm.gid WHERE g.name='Database Administrators';
username
----------
George
Simon
Patrick
(3 rows)
test=# SELECT g.name,COUNT(*) FROM "group" g LEFT JOIN groupmembers gm ON g.gid=gm.gid GROUP BY g.name;
name | count
-------------------------+-------
Steven's Group | 1
Database Administrators | 3
Developers | 1
System Administrators | 1
(4 rows)
![Page 19: Essential Skills RDBMS and SQL · RDBMS History Relational Database Based on a paper by Edgar F. Codd in 1970 Allowed to take advantage of disk based storage. Earlier DBMS implementations](https://reader030.vdocuments.us/reader030/viewer/2022041012/5ebebdbae89b6e2cbf333185/html5/thumbnails/19.jpg)
Transactions
test=# START TRANSACTION;
START TRANSACTION
test=# SELECT * FROM groupmembers WHERE uid=4;
mid | uid | gid
-----+-----+-----
3 | 4 | 4
4 | 4 | 5
(2 rows)
test=# DELETE FROM groupmembers WHERE uid=4;
DELETE 2
![Page 20: Essential Skills RDBMS and SQL · RDBMS History Relational Database Based on a paper by Edgar F. Codd in 1970 Allowed to take advantage of disk based storage. Earlier DBMS implementations](https://reader030.vdocuments.us/reader030/viewer/2022041012/5ebebdbae89b6e2cbf333185/html5/thumbnails/20.jpg)
Transactions
test=# SELECT * FROM groupmembers WHERE uid=4;
mid | uid | gid
-----+-----+-----
(0 rows)
test=# ROLLBACK;
ROLLBACK
test=# SELECT * FROM groupmembers WHERE uid=4;
mid | uid | gid
-----+-----+-----
3 | 4 | 4
4 | 4 | 5
(2 rows)
![Page 21: Essential Skills RDBMS and SQL · RDBMS History Relational Database Based on a paper by Edgar F. Codd in 1970 Allowed to take advantage of disk based storage. Earlier DBMS implementations](https://reader030.vdocuments.us/reader030/viewer/2022041012/5ebebdbae89b6e2cbf333185/html5/thumbnails/21.jpg)
Transactions
START TRANSACTION COMMIT ROLLBACK Transaction Isolation
SERIALIZABLE REPEATABLE READ READ COMMITTED READ UNCOMMITTED
![Page 22: Essential Skills RDBMS and SQL · RDBMS History Relational Database Based on a paper by Edgar F. Codd in 1970 Allowed to take advantage of disk based storage. Earlier DBMS implementations](https://reader030.vdocuments.us/reader030/viewer/2022041012/5ebebdbae89b6e2cbf333185/html5/thumbnails/22.jpg)
MVCC and Transaction Logs
Multi Version Concurrency Control Changes are written to the transaction logs This is used for
Crash recovery (Similar to Logging filesystems) Online backup MVCC
![Page 23: Essential Skills RDBMS and SQL · RDBMS History Relational Database Based on a paper by Edgar F. Codd in 1970 Allowed to take advantage of disk based storage. Earlier DBMS implementations](https://reader030.vdocuments.us/reader030/viewer/2022041012/5ebebdbae89b6e2cbf333185/html5/thumbnails/23.jpg)
Indices and the QEP
An index allows the database to find the needed rows faster.
It's easy to find someones number in a phonebook.
It's quite hard to find the name which belongs to a number.
You can create an index to speedup lookups by number:
Number: 575-4444, Page 78
![Page 24: Essential Skills RDBMS and SQL · RDBMS History Relational Database Based on a paper by Edgar F. Codd in 1970 Allowed to take advantage of disk based storage. Earlier DBMS implementations](https://reader030.vdocuments.us/reader030/viewer/2022041012/5ebebdbae89b6e2cbf333185/html5/thumbnails/24.jpg)
B-Tree
Used to store tables
![Page 25: Essential Skills RDBMS and SQL · RDBMS History Relational Database Based on a paper by Edgar F. Codd in 1970 Allowed to take advantage of disk based storage. Earlier DBMS implementations](https://reader030.vdocuments.us/reader030/viewer/2022041012/5ebebdbae89b6e2cbf333185/html5/thumbnails/25.jpg)
B+Tree
Used to store indices
![Page 26: Essential Skills RDBMS and SQL · RDBMS History Relational Database Based on a paper by Edgar F. Codd in 1970 Allowed to take advantage of disk based storage. Earlier DBMS implementations](https://reader030.vdocuments.us/reader030/viewer/2022041012/5ebebdbae89b6e2cbf333185/html5/thumbnails/26.jpg)
Cache-Oblivious B-Trees
Implemented as Fractal Tree™ by the TokuDB storage engine for MySQL
Caches data in non-leaf trees Avoids fragmentation Allows for high insert speeds
![Page 27: Essential Skills RDBMS and SQL · RDBMS History Relational Database Based on a paper by Edgar F. Codd in 1970 Allowed to take advantage of disk based storage. Earlier DBMS implementations](https://reader030.vdocuments.us/reader030/viewer/2022041012/5ebebdbae89b6e2cbf333185/html5/thumbnails/27.jpg)
Stored procedures
Allows you to store business logic in the database
Allows you to add functions to the database Examples: CALL addgroup('System Administators');
SELECT up1(first_name) FROM employees;
![Page 28: Essential Skills RDBMS and SQL · RDBMS History Relational Database Based on a paper by Edgar F. Codd in 1970 Allowed to take advantage of disk based storage. Earlier DBMS implementations](https://reader030.vdocuments.us/reader030/viewer/2022041012/5ebebdbae89b6e2cbf333185/html5/thumbnails/28.jpg)
Database design
ERD - Entity-Relationship Diagram Crow's Foot Notation
![Page 29: Essential Skills RDBMS and SQL · RDBMS History Relational Database Based on a paper by Edgar F. Codd in 1970 Allowed to take advantage of disk based storage. Earlier DBMS implementations](https://reader030.vdocuments.us/reader030/viewer/2022041012/5ebebdbae89b6e2cbf333185/html5/thumbnails/29.jpg)
Using SQL in applications
Security (SQL Injections) ORM - Object Relational Mapping
![Page 30: Essential Skills RDBMS and SQL · RDBMS History Relational Database Based on a paper by Edgar F. Codd in 1970 Allowed to take advantage of disk based storage. Earlier DBMS implementations](https://reader030.vdocuments.us/reader030/viewer/2022041012/5ebebdbae89b6e2cbf333185/html5/thumbnails/30.jpg)
SQL Injection
$sql= “SELECT article_body FROM articles WHERE pageid=$pageid”
index.php?pageid=5 SELECT article_body FROM articles WHERE pageid=5
index.php?pageid=5 OR 1=1; -- SELECT article_body FROM articles WHERE pageid=5' OR 1=1; --
![Page 31: Essential Skills RDBMS and SQL · RDBMS History Relational Database Based on a paper by Edgar F. Codd in 1970 Allowed to take advantage of disk based storage. Earlier DBMS implementations](https://reader030.vdocuments.us/reader030/viewer/2022041012/5ebebdbae89b6e2cbf333185/html5/thumbnails/31.jpg)
OLTP, OLAP
Online Analytical Processing Online Transaction Processing
![Page 32: Essential Skills RDBMS and SQL · RDBMS History Relational Database Based on a paper by Edgar F. Codd in 1970 Allowed to take advantage of disk based storage. Earlier DBMS implementations](https://reader030.vdocuments.us/reader030/viewer/2022041012/5ebebdbae89b6e2cbf333185/html5/thumbnails/32.jpg)
Opensource RDBMS
PostgreSQL MySQL SQLite
![Page 33: Essential Skills RDBMS and SQL · RDBMS History Relational Database Based on a paper by Edgar F. Codd in 1970 Allowed to take advantage of disk based storage. Earlier DBMS implementations](https://reader030.vdocuments.us/reader030/viewer/2022041012/5ebebdbae89b6e2cbf333185/html5/thumbnails/33.jpg)
SQLite
It's in every Firefox and Chrome installation It's in every MacOS X installation Bundled with PHP and Python Solaris 10 needs SQLite to boot Every iPhone, Android device and most
Symbian phones. It's used in Skype and McAfee
![Page 34: Essential Skills RDBMS and SQL · RDBMS History Relational Database Based on a paper by Edgar F. Codd in 1970 Allowed to take advantage of disk based storage. Earlier DBMS implementations](https://reader030.vdocuments.us/reader030/viewer/2022041012/5ebebdbae89b6e2cbf333185/html5/thumbnails/34.jpg)
SQLite
serverless, zero-configuration, transactional
![Page 35: Essential Skills RDBMS and SQL · RDBMS History Relational Database Based on a paper by Edgar F. Codd in 1970 Allowed to take advantage of disk based storage. Earlier DBMS implementations](https://reader030.vdocuments.us/reader030/viewer/2022041012/5ebebdbae89b6e2cbf333185/html5/thumbnails/35.jpg)
MySQL
The M in LAMP Multiple storage engines
MyISAM, InnoDB, NDB Cluster
![Page 36: Essential Skills RDBMS and SQL · RDBMS History Relational Database Based on a paper by Edgar F. Codd in 1970 Allowed to take advantage of disk based storage. Earlier DBMS implementations](https://reader030.vdocuments.us/reader030/viewer/2022041012/5ebebdbae89b6e2cbf333185/html5/thumbnails/36.jpg)
MySQL - Alexa Top 20
1.Google
2.Facebook
3.YouTube
4.Yahoo!
5.Baidu.com
6.Wikipedia
7.Blogger.com
8.Windows Live
9.Twitter
10.QQ.COM
11.LinkedIn
12.MSN
13.Yahoo! Japan
14.Amazon.com
15.Google India
16. 淘宝网 (Taobao.com)
17. 新浪新闻中心 (Sina.com.cn)
18.WordPress.com
19.Google.de
20.Google.com.hk
![Page 37: Essential Skills RDBMS and SQL · RDBMS History Relational Database Based on a paper by Edgar F. Codd in 1970 Allowed to take advantage of disk based storage. Earlier DBMS implementations](https://reader030.vdocuments.us/reader030/viewer/2022041012/5ebebdbae89b6e2cbf333185/html5/thumbnails/37.jpg)
PostgreSQL
Many features Standards compliant
![Page 38: Essential Skills RDBMS and SQL · RDBMS History Relational Database Based on a paper by Edgar F. Codd in 1970 Allowed to take advantage of disk based storage. Earlier DBMS implementations](https://reader030.vdocuments.us/reader030/viewer/2022041012/5ebebdbae89b6e2cbf333185/html5/thumbnails/38.jpg)
Closed source RDBMS
Oracle RDBMS Sybase ASE Microsoft SQL Server IBM DB2
![Page 39: Essential Skills RDBMS and SQL · RDBMS History Relational Database Based on a paper by Edgar F. Codd in 1970 Allowed to take advantage of disk based storage. Earlier DBMS implementations](https://reader030.vdocuments.us/reader030/viewer/2022041012/5ebebdbae89b6e2cbf333185/html5/thumbnails/39.jpg)
NoSQL
Stores data in other formats that tables/rows Object stores Document stores key-value stores
Access data using other protocols/languages than SQL Simple text based and/or binary protocols
![Page 40: Essential Skills RDBMS and SQL · RDBMS History Relational Database Based on a paper by Edgar F. Codd in 1970 Allowed to take advantage of disk based storage. Earlier DBMS implementations](https://reader030.vdocuments.us/reader030/viewer/2022041012/5ebebdbae89b6e2cbf333185/html5/thumbnails/40.jpg)
NoSQL
Automatic sharding Automatic replication Eventual consistency
![Page 41: Essential Skills RDBMS and SQL · RDBMS History Relational Database Based on a paper by Edgar F. Codd in 1970 Allowed to take advantage of disk based storage. Earlier DBMS implementations](https://reader030.vdocuments.us/reader030/viewer/2022041012/5ebebdbae89b6e2cbf333185/html5/thumbnails/41.jpg)
NoSQL Implementations
NoSQL implementations: MongoDB CouchDB Redis Cassandra
![Page 42: Essential Skills RDBMS and SQL · RDBMS History Relational Database Based on a paper by Edgar F. Codd in 1970 Allowed to take advantage of disk based storage. Earlier DBMS implementations](https://reader030.vdocuments.us/reader030/viewer/2022041012/5ebebdbae89b6e2cbf333185/html5/thumbnails/42.jpg)
Hybrid solutions
Allows NoSQL protocol for performance and uses SQL for ad-hoc queries.
MySQL with HandlerSocket MySQL with Memcached MySQL with NDB Cluster
![Page 43: Essential Skills RDBMS and SQL · RDBMS History Relational Database Based on a paper by Edgar F. Codd in 1970 Allowed to take advantage of disk based storage. Earlier DBMS implementations](https://reader030.vdocuments.us/reader030/viewer/2022041012/5ebebdbae89b6e2cbf333185/html5/thumbnails/43.jpg)
Pitfalls
CAP Theorem Scalability Locking
![Page 45: Essential Skills RDBMS and SQL · RDBMS History Relational Database Based on a paper by Edgar F. Codd in 1970 Allowed to take advantage of disk based storage. Earlier DBMS implementations](https://reader030.vdocuments.us/reader030/viewer/2022041012/5ebebdbae89b6e2cbf333185/html5/thumbnails/45.jpg)
What does a DBA do?
Check backups and restores
Create users Grant permissions Setup Monitoring Setup Replication Tune the database Tune queries Add indices
Rewrite Queries Test upgrades Benchmark Create functions and
procedures
![Page 46: Essential Skills RDBMS and SQL · RDBMS History Relational Database Based on a paper by Edgar F. Codd in 1970 Allowed to take advantage of disk based storage. Earlier DBMS implementations](https://reader030.vdocuments.us/reader030/viewer/2022041012/5ebebdbae89b6e2cbf333185/html5/thumbnails/46.jpg)
Bonus Items
Views Triggers Explain Subqueries Surrogate Keys Trees, Multivalue
attributes LIKE '% InnoDB
BB RAID