Similar presentations:
Development course SQL databases in node
1.
DEVELOPMENT COURSESQL DATABASES IN NODE.JS
CONFIDENTIAL
1
2.
Agenda of the lecture• Important Aspects of Relational Databases;
• Create Relationships among Tables;
• SQL COMMANDS;
- DDL - Data Definition Language
- DML - Data Manipulation Language
- DCL - Data Control Language
- TCL - Transaction Control Language
- Transaction Isolation Levels
• Native node - postgresql driver
- Connection
- Queries
- Pool, TCL
CONFIDENTIAL
1
3.
DATABASESCONFIDENTIAL
1
4.
Important Aspects of Relational DatabasesCONFIDENTIAL
1
5.
Create Relationships among TablesOne-to-Many (1:N)
One-to-One (1:1)
PK
PK
FK
PK
Many-to-Many (N:М)
FK
PK
PK - Primary Key
FK - Foreign Key
CONFIDENTIAL
PK
FK
FK
1
6.
SQL CommandsCONFIDENTIAL
1
7.
DDL- Data Definition LanguageData Definition Language is used to define the database structure or
schema. DDL is also used to specify additional properties of the data.
CREATE (DATABASE, TABLE, VIEW, TRIGGER, PROCEDURE) : to
create objects in database
ALTER (DATABASE, TABLE, VIEW, TRIGGER, PROCEDURE) : alters the
structure of database
DROP (DATABASE, TABLE, VIEW, TRIGGER, PROCEDURE) : delete
objects from database
RENAME (DATABASE, TABLE, VIEW, TRIGGER, PROCEDURE) :
rename an objects
TRUNCATE TABLE : The result of this operation quickly removes all data
from a table, typically bypassing a number of integrity enforcing mechanisms.
CONFIDENTIAL
1
8.
DML-Data Manipulation LanguageDML statements are used for managing data within schema objects.
DML are of two types:
Procedural DMLs : require a user to specify what data are needed and how
to get those data.
Declarative DMLs : (also referred as Non-procedural DMLs) : require a user
to specify what data are needed without specifying how to get those data.
Declarative DMLs are usually easier to learn and use than procedural DMLs.
However, since a user does not have to specify how to get the data, the
database system has to figure out an efficient means of accessing data.
SELECT: retrieve data from the database
INSERT: insert data into a table
UPDATE: update existing data within a table
DELETE: deletes all records from a table, space for the records remain
CONFIDENTIAL
1
9.
DDL AND SIMPLE DMLEXAMPLES
CONFIDENTIAL
1
10.
12
3
4
CONFIDENTIAL
1
11.
56
7
8
9
10
CONFIDENTIAL
1
12.
1112
13
14
15
CONFIDENTIAL
1
13.
DML QUERY EXAMPLESCONFIDENTIAL
1
14.
21
3
4
CONFIDENTIAL
1
15.
56
7
8
9
10
CONFIDENTIAL
1
16.
1112
13
14
CONFIDENTIAL
1
17.
DML QUERY WITH INDEXEXAMPLES
CONFIDENTIAL
1
18.
12
3
4
5
6
7
8
CONFIDENTIAL
1
19.
SQL JoinsCONFIDENTIAL
1
20.
DML QUERY JOINSEXAMPLES
CONFIDENTIAL
1
21.
31
2
4
5
6
CONFIDENTIAL
1
22.
78
9
CONFIDENTIAL
1
23.
1011
CONFIDENTIAL
1
24.
DCL- Data Control LanguageA Data Control Language is a syntax similar to a computer programming language used to control access to
data stored in a database (Authorization). In particular, it is a component of Structured Query Language
(SQL).
Examples of DCL commands :
GRANT: allow specified users to perform specified tasks.
REVOKE: cancel previously granted or denied permissions.
The operations for which privileges may be granted to or revoked from a user or role apply to both the Data
definition language (DDL) and the Data manipulation language (DML), and may include CONNECT, SELECT,
INSERT, UPDATE, DELETE, EXECUTE and USAGE.
CONFIDENTIAL
1
25.
TCL- Transaction Control LanguageTransaction Control Language commands are used to manage transactions in the database. These are used
to manage the changes made by DML-statements. It also allows statements to be grouped together into
logical transactions.
Examples of TCL commands:
COMMIT: Commit command is used to permanently save any transaction
into the database.
ROLLBACK: This command restores the database to last committed state.
It is also used with savepoint command to jump to a savepoint
in a transaction.
SAVEPOINT: Savepoint command is used to temporarily save a transaction so
that you can rollback to that point whenever necessary.
CONFIDENTIAL
1
26.
Transaction isolation levels vs readphenomena
CONFIDENTIAL
1
27.
Lost UpdateTransaction 1
Non-Repeatable-Read
Transaction 2
Transaction 1
UPDATE tbl1
SET f2=f2+20 WHERE f1=1;
Transaction 2
SELECT f2 FROM tbl1
WHERE f1=1;
UPDATE tbl1
SET f2=f2+25 WHERE f1=1;
UPDATE tbl1 SET f2=f2+1
WHERE f1=1;
COMMIT;
Dirty Read
Transaction 1
SELECT f2 FROM tbl1
WHERE f1=1;
Transaction 2
SELECT f2 FROM tbl1
WHERE f1=1;
f2
-------------100
(1 row)
Phantom Reads
Transaction 1
Transaction 2
SELECT SUM(f2) FROM tbl1;
UPDATE tbl1 SET f2=200
WHERE f1=1;
INSERT INTO tbl1(f1, f2)
VALUES (15, 20);
SELECT f2 FROM tbl1
WHERE f1=1;
f2
-------------200
(1 row)
COMMIT;
SELECT SUM(f2) FROM tbl1;
ROLLBACK
CONFIDENTIAL
1
28.
Serialization AnomaliesTransaction 1
Transaction 2
SELECT SUM(value) FROM
mytab WHERE class = 1;
-----------30
(1 row)
SELECT SUM(value) FROM
mytab WHERE class = 2;
-----------30
(1 row)
INSERT INTO mytab (value, class)
VALUES (30, 2)
INSERT INTO mytab (value,
class)
VALUES (300, 1)
COMMIT;
COMMIT;
CONFIDENTIAL
XA - Transaction
1
29.
TCL DEMO EXAMPLESCONFIDENTIAL
1
30.
12
3
4
5
CONFIDENTIAL
1
31.
Native node - postgresql driver (Connection)Link Nodejs driver api:
https://node-postgres.com
Free Database(
20MB data, 5concurrent connections
):
https://www.elephantsql.com/
CONFIDENTIAL
1
32.
Native node - postgresql driver (Queries)CONFIDENTIAL
1
33.
Native node - postgresql driver (Pool, TCL)CONFIDENTIAL
1