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)

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! 2 Nov 19, 2019

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 3 Nov 19, 2019

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 4 Nov 19, 2019

Algebraic expressions ● Can be visualized as expression trees (3 + 5) * 2 * + 2 3 5 5 Nov 19, 2019

Algebraic expressions ● Can be visualized as expression trees (3 + 5) * 2 vs. 3 + 5 * 2 * + 3 * + 2 3 5 5 2 6 Nov 19, 2019

Algebraic expressions ● Can be visualized as expression trees 5 – x / 3 - 5 / x 3 7 Nov 19, 2019

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 8 Nov 19, 2019

What is Relatjonal Algebra? => Expressions of relatjonal algebra can also be visualized as trees OP1 R1 OP2 R2 R3 ● OP1 and OP2 are relatjonal operatjons ● R1, R2, R3 are variables for relatjons 9 Nov 19, 2019

Relatjonal Algebra: 5 Basic Operatjons ● Selectjon: σ C ( R ) Selects a subset of tuples from relatjon R, for which conditjon C holds (horizontal) ● Projectjon: π A 1 , ... , A k ( R ) A 1 , ... , A k 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 ∪ R3 ● Union: R1 R2 Tuples in relatjon R1 and/or in relatjon R2 ● Since each operatjon returns a relatjon, operatjons can be composed (Algebra is “closed”) 10 Nov 19, 2019

What is Relatjonal Algebra? => An expression tree could like this ∪ σ Brand=Ford σ Color=blue Car Car ● What does this express? 11 Nov 19, 2019

Relatjonal Algebra: Example Instances 12 Nov 19, 2019

Selectjon (σ*σ*) 13 Nov 19, 2019

Projectjon (σ*π*) 14 Nov 19, 2019

Projectjon (σ*π*) 15 Nov 19, 2019

Cross Product 16 Nov 19, 2019

Cross Product 17 Nov 19, 2019

Union and Set Difgerence 18 Nov 19, 2019

Union 19 Nov 19, 2019

Set Difgerence 20 Nov 19, 2019

Nestjng Operators ● Result of a relatjonal algebra operator is a relatjon ● It can be used as input to another relatjonal algebra operator 21 Nov 19, 2019

Nestjng Operators ● As expression tree: π sname,ratjng σ ratjng>8 S2 22 Nov 19, 2019

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 23 Nov 19, 2019

Compound Operator: Intersectjon 24 Nov 19, 2019

SQL - A language for Relational DBs 25 Nov 19, 2019

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 26 Nov 19, 2019

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 27 Nov 19, 2019

Creatjng Databases in SQL ● Create a new, empty database ‘University’: CREATE DATABASE University; – Does not contain any relatjons upon creatjon DB “University” 28 Nov 19, 2019

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 DB “University” – Note: the type (domain) of each Students fjeld is specifjed, and enforced by the DBMS whenever tuples are added or modifjed. sid name login age gpa 29 Nov 19, 2019

Creatjng Relatjons in SQL ● Similarly: CREATE TABLE Courses ( cid CHAR(20) PRIMARY KEY, cname CHAR(20), credits INTEGER); DB “University” Students Courses 30 Nov 19, 2019

Adding and Deletjng Tuples ● Insert a single tuple: INSERT INTO Students (sid, name, login, age, gpa) VALUES ('53688', 'Smith', 'smith@ee', 18, 3.2); sid name login age gpa 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 31 Nov 19, 2019

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; sid name login age gpa 53688 Smith smith@ee 18 3.2 ● To get just names and logins: SELECT S.name, S.login FROM Students S WHERE S.age=18 AND S.gpa > 2.0; name login Smith smith@ee 32 Nov 19, 2019

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; 33 Nov 19, 2019

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; 34 Nov 19, 2019

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)) 35 Nov 19, 2019

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); 36 Nov 19, 2019

Thank you for your attention! 37 Nov 19, 2019