💡Recall

🖋 Notes

SQL Statements Execution

client application - SQL statement execution request
- for any query - minimum response time
statement execution - stages:
- client:
  - generate SQL statement (non-procedural language), send it to server
- server:
  - analyze SQL statement (syntactically)
  - ranslate statement into an internal form (relational algebra expression)
  - transform internal form into an optimal form
  - generate a procedural execution plan
  - evaluate procedural plan, send result to client
the following operators are necessary in the querying process:
an SQL query can be written in multiple ways

Examples

programs[id, pname, pdescription]

groups[id, program, yearofstudy, gdescription]

students[cnp, lastname, firstname, sgroup, gpa, addr, email]

-- query: 
--  Find students (lastname, firstname, year of study, program name, gpa) 
--  in a given program (e.g. with id = 2, can be a parameter), 
--  with a gpa >= 9 (can be a parameter).
-- a
SELECT lastname, firstname, yearofstudy, pname, gpa
FROM students st, groups gr, programs pr
WHERE st.sgroup = gr.id AND gr.program = pr.id AND program = 2 and gpa >= 9

**𝜋𝛽(𝜎𝐶(𝑠𝑡𝑢𝑑𝑒𝑛𝑡𝑠 × 𝑔𝑟𝑜𝑢𝑝𝑠 × 𝑝𝑟𝑜𝑔𝑟𝑎𝑚𝑠))**

-- b
SELECT lastname, firstname, yearofstudy, pname, gpa
FROM (students st INNER JOIN groups gr ON st.sgroup = gr.id)
																			 INNER JOIN programs pr ON gr.program = pr.id
WHERE program = 2 AND gpa >= 9

𝜋𝛽(𝜎𝐶1((𝑠𝑡𝑢𝑑𝑒𝑛𝑡𝑠⨂𝐶2𝑔𝑟𝑜𝑢𝑝𝑠)⨂𝐶3𝑝𝑟𝑜𝑔𝑟𝑎𝑚𝑠))

-- c
SELECT lastname, firstname, yearofstudy, pname, gpa
FROM(
			(
				SELECT lastname, firstname, sgroup, gpa
				FROM students
				WHERE gpa >= 9
			) st INNER JOIN (
												SELECT * 
												FROM groups 
												WHERE program = 2
											) gr ON st.sgroup = gr.id
													 ) INNER JOIN (
																					SELECT id, pname 
																					FROM programs 
																					WHERE id = 2
																				) pr ON gr.program = pr.id

𝜋𝛽(((𝜋𝛽1(𝜎𝐶2(𝑠𝑡𝑢𝑑𝑒𝑛𝑡𝑠))) ⊗𝐶3 (𝜎𝐶4(𝑔𝑟𝑜𝑢𝑝𝑠))) ⊗𝐶5 (𝜋𝛽2(𝜎𝐶6(𝑝𝑟𝑜𝑔𝑟𝑎𝑚𝑠))))

Galaxies[GID, GName, Diameter]

SolarSystems[SID, SName, Galaxy, Mass]

Planets[PID, PName, SolarSystem, AvgOrbitalSpeed, AvgRadius, Volume]

-- Find planets (planet name, avg speed, solar system name, galaxy name, avg radius) 
-- from galaxy with GID = 2, with a volume >= 1.5*10^15 km^3.

-- a
SELECT PName, AvgOrbitalSpeed, SName, GName, AvgRadius
FROM Planets P, SolarSystems S, Galaxies G -- cross product by just enumerating 
WHERE P.SolarSystem = S.SID AND S.Galaxy = G.GID AND Galaxy = 2 AND Volume >= 150000000000000

**𝜋𝛽(𝜎𝐶(Planets × SolarSystem × Galaxies))**

-- b
SELECT PName, AvgOrbitalSpeed, SName, GName, AvgRadius
FROM (Planets P INNER JOIN SolarSystems S ON P.SolarSystem = S.SID)
																			    INNER JOIN Galaxies G ON S.Galaxy = G.GID
WHERE Galaxy = 2 AND Volume >= 150000000000000

𝜋𝛽(𝜎𝐶1((**Planets**⨂𝐶2**SolarSystem**)⨂𝐶3**Galaxies**))

-- c
SELECT PName, AvgOrbitalSpeed, SName, GName, AvgRadius
FROM(
			(
				SELECT PName, AvgOrbitalSpeed, AvgRadius, SolarSystem
				FROM Planets
				WHERE Volume >= 150000000000000
			) P INNER JOIN (
												SELECT * 
												FROM SolarSystems 
												WHERE Galaxy = 2
											) S ON P.SolarSystem = S.SID
													 ) INNER JOIN (
																					SELECT GID, GName 
																					FROM Galaxies 
																					WHERE GID = 2
																				) G ON S.Galaxy = G.GID

𝜋𝛽(((𝜋𝛽1(𝜎𝐶2(**Planets**))) ⊗𝐶3 (𝜎𝐶4(**SolarSystem**))) ⊗𝐶5 (𝜋𝛽2(𝜎𝐶6(**Galaxies**))))

Relational Algebra Operators - Evaluation

operands for relational operators:
- database tables (can have attached indexes)
- temporary tables (obtained by evaluating some relational operators)
several evaluation algorithms can be used for a relational algebra operator
when generating the execution plan:
- choose the algorithm with the lowest complexity (for the current database context)
- take into account data from the system catalog, statistical information

Algorithms

Table Scan

many operators require a full scan of the entire table
bR - number of blocks storing a table's records
- sequential search algorithm:
  - approximately bR/2 blocks are necessary (on average) when performing a sequential search on a key value
  - all blocks must be brought into main memory when performing a sequential search on a non-key field
high transfer time for large tables

Index Seek

searching for a key value K0
condition of the form: (simple or composite key) K = K0
search:
- explicit (searching a table, evaluating a join)
- implicit (checking a key constraint)
examine an index (stored as a B-tree, B+ tree) created:
- explicitly build (with the CREATE INDEX statement)
- implicitly build (via a key constraint)

Index Scan

evaluating 𝜎𝑐 𝑅 , where condition C is of the form:
- index built for a key A
  - A < v, A <= v, A > v, A >= v, A IS NULL, A IS NOT NULL
- index built for a non-key field A
  - A = v, A < v, A <= v, A > v, A >= v, A IS NULL, A IS NOT NULL
partial / total index scan: obtain desired records' addresses
get records from the relation; some blocks can be read multiple times

e.g.

B+ tree, entries a2

To obtain desired records:

⇒ index scan

⇒ get records’ addresses

⇒ get records from relation

Cross Join

this algorithm is used to evaluate a cross-product
- R CROSS JOIN S
- R INNER JOIN S ON C (C evaluates to TRUE)
- SELECT ... FROM R, S ..., no join condition between R and S
bR, bS = the number of blocks storing R and S, respectively
m, n = the number of blocks from R and S that can simultaneously appear in the main memory (there are m+n buffers for the 2 tables)
the following algorithm can be used to generate the cross-product {(r, s) | r ∈ R, s ∈ S}:

for every group of max. m blocks in R:

read the group of blocks from R into main memory; let M1 be the set of records in these blocks

for every group of max. n blocks in S:

read the group of blocks from S into main memory; let M2 be the set of records in these blocks

for every r ∈ M1:

for every s ∈ M2:

add (r, s) to the result
algorithm complexity:
to minimize this value, m should be maximized (the other operands are constants), one buffer can be used for S (so n = 1), while the remaining space can be used for R (m max.)
switch the 2 relations (in the algorithm and when computing the complexity):
choose better version
obs.: if bR ≤ m or bS ≤ n ⇒ complexity bR + bS

Nested Loops Join

the Cross Join algorithm can be used to evaluate a join between 2 tables
for every element (r, s) in the cross-product, evaluate the condition in the join operator
elements (r, s) that don't meet the join condition are eliminated

Indexed Nested Loops Join

this algorithm is used to evaluate R ⨂C S, where C ≡ (R.A=S.B), and there is an index on A (in R) or on B (in S)
depending on the type of index - at most 1 / multiple matching records in S
in the algorithm description below, we assume there is an index on column B in table S

Merge Join

this algorithm is used to evaluate R ⨂C S, where C ≡ (R.A=S.B), and there are no indexes on A (in R) and B (in S)
this algorithm replaces an evaluation tree with another evaluation tree: