DM534: Introduction to Relational Databases (Part 2) 2019 Slides - - PowerPoint PPT Presentation

DM534: Introduction to Relational Databases (Part 2)

2019 Slides by Christian Wiwie (edits by Rolf Fagerberg)

Nov 19, 2019 2

Relatjonal Query Languages

• Based on relatjonal algebra
• For relatjonal databases, i.e. relatjonal data model
• Relatjonal model supports simple, powerful QLs:

– Strong formal foundatjon based on logic – Allows for much optjmizatjon

• SQL (Structured Query Language)

– Most widely used relatjonal query language

→ Understanding Relatjonal Algebra is key to understanding SQL, query processing!

Nov 19, 2019 3

What is an “Algebra”?

• Mathematjcal system consistjng of

– Operands: Values from which new values can be

constructed by applying operatjons

– Operatjons: Procedures that construct new values from

given values

– Operators: Symbols denotjng operatjons

• Variables are letuers that can represent values

Nov 19, 2019 4

Examples for Algebras

• Integer algebra

– Operands: The set of integers [..., -1, 0, 1, …] – Operatjons: Additjon, subtractjon, multjplicatjon,

division, …

– Operators: +, -, *, ∕, …

• Example for algebraic expressions:

(3 + 5) * 2 5 – x / 3

Nov 19, 2019 5

Algebraic expressions

• Can be visualized as expression trees

(3 + 5) * 2

3 + 5 * 2

Nov 19, 2019 6

Algebraic expressions

• Can be visualized as expression trees

(3 + 5) * 2 vs. 3 + 5 * 2

3 + 5 * 2 3 + 5 2 *

Nov 19, 2019 7

Algebraic expressions

• Can be visualized as expression trees

5 – x / 3

5

• /

x 3

Nov 19, 2019 8

What is Relatjonal Algebra?

• An algebra where

– operands are relatjons – operatjons compute new relatjons from relatjons

• Can be used as a query language for relatjons

– “Language” of relatjonal databases

Nov 19, 2019 9

What is Relatjonal Algebra?

=> Expressions of relatjonal algebra can also be visualized as trees

• OP1 and OP2 are relatjonal operatjons
• R1, R2, R3 are variables for relatjons

R1 OP1 OP2 R2 R3

Nov 19, 2019 10

Relatjonal Algebra: 5 Basic Operatjons

• Selectjon:

Selects a subset of tuples from relatjon R, for which conditjon C holds (horizontal)

• Projectjon:

Retains atuributes from relatjon R (vertjcal)

• Cross-product: R1 x R2

Pairwise combinatjon of tuples of relatjons R1 and R2

• Set-difgerence: R1 – R2

Tuples in relatjon R1, but not in relatjon R2

• Union: R1

R2 ∪ R3

Tuples in relatjon R1 and/or in relatjon R2

• Since each operatjon returns a relatjon, operatjons can be composed

(Algebra is “closed”)

σ C(R) π A1,..., Ak(R) A1,..., Ak

Nov 19, 2019 11

What is Relatjonal Algebra?

=> An expression tree could like this

• What does this express?

σBrand=Ford ∪ Car σColor=blue Car

Nov 19, 2019 12

Relatjonal Algebra: Example Instances

Nov 19, 2019 13

Selectjon (σ*σ*)

Nov 19, 2019 14

Projectjon (σ*π*)

Nov 19, 2019 15

Projectjon (σ*π*)

Nov 19, 2019 16

Cross Product

Nov 19, 2019 17

Cross Product

Nov 19, 2019 18

Union and Set Difgerence

Nov 19, 2019 19

Union

Nov 19, 2019 20

Set Difgerence

Nov 19, 2019 21

Nestjng Operators

• Result of a relatjonal algebra operator is a relatjon
• It can be used as input to another relatjonal algebra
• perator

Nov 19, 2019 22

Nestjng Operators

• As expression tree:

σratjng>8 πsname,ratjng S2

Nov 19, 2019 23

Compound Operator: Intersectjon

• In additjon to the 5 basic operators, there are

several additjonal “Compound Operators”

– Do not add computatjonal power to the language – Useful shorthands – Can be expressed with basic operatjons

• Example: Intersectjon

– Takes two input relatjons that are union-compatjble

Nov 19, 2019 24

Compound Operator: Intersectjon

Nov 19, 2019 25

SQL - A language for Relational DBs

Nov 19, 2019 26

SQL - A language for Relatjonal DBs

• Say: “ess-cue-ell” or “sequel”

– But spelled “SQL”

• Data Defjnitjon Language (DDL)

– create, modify, delete relatjons – specify constraints – administer users, security, etc.

• Data Manipulatjon Language (DML)

– Specify queries to fjnd tuples that satjsfy criteria – add, modify, remove tuples

• The DBMS is responsible for effjcient evaluatjon

Nov 19, 2019 27

SQL - A language for Relatjonal DBs

• Query language to retrieve data from database
• Includes a data-defjnitjon component to defjne

database schemas

• SQL commands have to be terminated with ‘;’
• SQL is standardized

– some DBMS include their own SQL commands

Nov 19, 2019 28

Creatjng Databases in SQL

• Create a new, empty database ‘University’:

CREATE DATABASE University;

– Does not contain any relatjons

upon creatjon

DB “University”

Nov 19, 2019 29

Creatjng Relatjons in SQL

• Create a new, empty relatjon ‘Students’:

CREATE TABLE Students (sid CHAR(20) PRIMARY KEY, name CHAR(20), login CHAR(10), age INTEGER, gpa FLOAT);

– Does not contain any tuples

upon creatjon

– Note: the type (domain) of each

fjeld is specifjed, and enforced by the DBMS whenever tuples are added or modifjed.

DB “University” Students sid name login age gpa

Nov 19, 2019 30

Creatjng Relatjons in SQL

• Similarly:

CREATE TABLE Courses ( cid CHAR(20) PRIMARY KEY, cname CHAR(20), credits INTEGER);

DB “University” Students Courses

Nov 19, 2019 31

Adding and Deletjng Tuples

• Insert a single tuple:

INSERT INTO Students (sid, name, login, age, gpa) VALUES ('53688', 'Smith', 'smith@ee', 18, 3.2);

• Delete all tuples satjsfying some conditjon (e.g.,

name = Smith):

DELETE FROM Students S WHERE S.name = 'Smith';

sid name login age gpa 53688 Smith smith@ee 18 3.2 sid name login age gpa

Nov 19, 2019 32

Selectjng Tuples in SQL

• Find tuples for all 18 year old students with gpa’s

above 2.0:

SELECT * FROM Students S WHERE S.age=18 AND S.gpa > 2.0;

• To get just names and logins:

SELECT S.name, S.login FROM Students S WHERE S.age=18 AND S.gpa > 2.0;

sid name login age gpa 53688 Smith smith@ee 18 3.2 name login Smith smith@ee

Nov 19, 2019 33

Relatjonal Algebra Operators in SQL

• Relatjonal algebra operators can be expressed with SQL
• Selectjon operator (σ):

SELECT * FROM Students S WHERE S.age=18 AND S.gpa > 2.0;

• Projectjon operator (π):

SELECT S.age,S.gpa FROM Students S;

• Union:

SELECT * FROM Students S WHERE S.age=18 AND S.gpa > 2.0 UNION SELECT * FROM Students S WHERE S.age=20 AND S.gpa > 2.3;

Nov 19, 2019 34

Relatjonal Algebra Operators in SQL

• Set Difgerence:

SELECT * FROM Students S WHERE S.gpa > 2.0 EXCEPT SELECT * FROM Students S WHERE S.age=19;

• Cross Product:

SELECT * FROM Students S, Enrolled E;

Nov 19, 2019 35

Primary Keys in SQL

• Single aturibute primary key:

CREATE TABLE Students (sid CHAR(20) PRIMARY KEY, name CHAR(20), login CHAR(10), age INTEGER, gpa FLOAT)

• Multj-aturibute primary key:

CREATE TABLE Enrolled (sid CHAR(20) cid CHAR(20), grade CHAR(2), PRIMARY KEY (sid,cid))

Nov 19, 2019 36

Foreign Keys in SQL

• Only students listed in the Students relatjon should be

allowed to enroll for courses

→ sid is a foreign key referring to Students

• Students can only enroll for registered courses

→ cid is a foreign key referring to Courses CREATE TABLE Enrolled (sid CHAR(20),cid CHAR(20),grade CHAR(2), PRIMARY KEY (sid,cid), FOREIGN KEY (sid) REFERENCES Students, FOREIGN KEY (cid) REFERENCES Courses);

Nov 19, 2019 37

Thank you for your attention!