程序代写 Introduction to Databases for Business Analytics

Introduction to Databases for Business Analytics
Week 5 Relational Algebra and SQL Joins
Term 2 2022
Lecturer-in-Charge: Kam-Fung (Henry) : Tutors:

PASS Leader:

• There are some file-sharing websites that specialise in buying and selling academic work to and from university students.
• If you upload your original work to these websites, and if another student downloads
and presents it as their own either wholly or partially, you might be found guilty of collusion — even years after graduation.
• These file-sharing websites may also accept purchase of course materials, such as copies of lecture slides and tutorial handouts. By law, the copyright on course materials, developed by UNSW staff in the course of their employment, belongs to UNSW. It constitutes copyright infringement, if not academic misconduct, to trade these materials.

Acknowledgement of Country
UNSW Business School acknowledges the Bidjigal (Kensington campus) and Gadigal (City campus) the traditional custodians of the lands where each campus is located.
We acknowledge all Aboriginal and Islander Elders, past and present and their communities who have shared and practiced their teachings over thousands of years including business practices.
We recognise Aboriginal and Islander people’s ongoing leadership and contributions, including to business, education and industry.
UNSW Business School. (2022, May 7). Acknowledgement of Country [online video]. Retrieved from https://vimeo.com/369229957/d995d8087f

Relational Languages
❑ Codd (1970, 1971)’s relation model is the conceptual basis for relational
databases. The relational model includes two relational languages:
1. Relational algebra is a non-procedural, high-level language that provides a declarative way to specify database queries. (Relational algebra “declares a definition” to get to certain data.)
2. Relational calculus is a procedural, low-level language that provides a procedural way for specifying queries. (Relational calculus provides a “order of steps” to get to certain data.)
❑ For every expression in the relational algebra there is an equivalent expression in the relational calculus, and vice versa. They are logically equivalent.
❑ Relational algebra and relational calculus are not very user friendly.
❑ SQL was developed as user-friendly query to work with RDBMS.

Relational Algebra
The relational algebra has operations. These operations fall into three main categories:
1. Union, Intersection and Difference: Boolean operations to define a new relation based on two existing relations.
2. Selection and Projection: Operations that remove parts of a relation.
3. Cartesian Product / Joins: Operations that combine the tuples of two relations.

1) Union, Intersection and Difference
❑ Union, Intersection and Difference are operations (or “set operations”) on two relations (R and S), both relations should have schemas with identical sets of attributes and identical order of the attributes.
❑ UNION:RS
▪ The union of R and S is the set of all tuples that are in R and S. ▪ In short: merge the two sets of tuples!
❑ INTERSECTION:RS
▪ The intersection of R and S is the set of tuples that appear in both tables. ▪ In short: find the common tuples!
❑ DIFFERENCE:R-S
▪ The difference of R and S, is the set of tuples that are in R but not in S. ▪ In short: subtract the tuples in S from the tuples in R!
Question: Is R – S the same as S – R?

Exercise 1
DIFFERENCE

24 INTERSECT
DIFFERENCE

Solution to Exercise 1
13 UNION yields
DIFFERENCE yields

INTERSECT yields DIFFERENCE yields

2) Selection and Projection ❑Selection and Projection operations are applied to a single relation (R).
❑ SELECTION
• Selection (SELECT) returns a relation that contains only those tuples (i.e., rows in a table) from a specified relation
(R) that satisfy a specified condition (horizontal subset of a table).
• Relational operator is σ. σ predicate R (σ = “sigma”)
❑ PROJECTION
• Projection (PROJECT) returns a relation that contains a list of tuples for selected attributes from a specified relation
(R), eliminating duplicates (vertical subset of a table).
• Relational operator is π. π attribute 1, … attribute n R (π = “pi”)

Exercise 2
1. What is the result of: σ price < 2.00 R ? (“Selection with Price less than 2.00 of R”; “List all tuples with a price less than 2.00”) 2. What is the result of π Product Description, Price R ? (“Projection with Product Description, Price from R”; “List all tuples showing only description and price”) Solution to Exercise 2 ➢ Selection: σ price < 2.00 R (“List all tuples with a price less than 2.00”) 213345 9v battery 1.92 254467 100w bulb 1.47 ➢ Projection: π Product Description, Price R (“List all tuples showing only description and price”) 9v battery 1.92 Power drill 34.99 100w bulb 1.47 3) Cartesian Product and Joins ❑ Cross Join joins (creates and returns) the Cartesian Product of two relations R and S. (“𝑅 ∗ 𝑆”, “all possible tuple combinations of two relations”, “everything joined to everything”). ❑ Inner Join returns combined tuples from R and S that fulfil a certain criterion. This is the most common, the default join type. • An Equi Join joins tuples from R and S based on equality of values for specified attributes. The join is called a Theta Join if a comparison other than “equality” (=) is used, such as “smaller/less than” (<). • A Natural Join joins tuples from R and S that agree in value for whatever attributes are common to the schemas of R and S. The attributes are not explicitly specified. Hence, “naturally”, attributes in common are used for the join. ❑ A Full Outer Join returns tuples from both relations with their matching values in the respective other relation (i.e., tuples with no match in the other relation still appears, with NULL values instead of matching values). Cross Join (Cartesian Product) ❑ Cartesian = “relating to René Descartes (1596-1650) and his ideas”. The word comes from the Latinised version ( ) of the name (René Descartes). ❑ Descartes made major progress in analytical geometry. A = [x , y , z] B=[1, 2,3] Cross Join (Cartesian Product) ❑ Cross Join (Cartesian Product): Select all possible combinations of tuples in R with tuples in S (“𝑅 ∗ 𝑆”, “all possible tuple combinations of two relations”, “everything joined to everything”). SELECT * FROM R CROSS JOIN S; this is an explicit cross join SELECT * FROM R, S; this is an implicit cross join Question: Is a Cross Join of R, S identical to a Union of R, S? Why (not)? Cross Join (Cartesian Product) Compare to Union Inner Join An Inner Join returns combined tuples from two relations that have the same value for a defined attribute (match on the attribute). This is the default join type, the most common join type. SELECT * FROM R INNER JOIN S this is an explicit inner join ON R.attribute = S.attribute Tip: One way to think of an Inner Join is as a Cross Join (Cartesian Product) with all tuples removed that do not match on the defined attribute. Inner Join ❑ An equi join is a join with a join condition containing an equality operator. ❑ A theta join is when other comparison operators are used (<=, >=, <, >).

Inner Join & Natural Join
A natural join joins tuples based on all attributes with identical names in the two relations.
A Natural Join joins 2 tables on the basis of all common columns
An Inner Join joins 2 tables on the basis of common columns mentioned in the ON clause
Natural Join
(All common columns) Here only Column1
Inner Join
on Column1

Full Outer Join
Full Outer Join: Selects and joins tuples from two tables that match on a defined attribute. If there is no match for a tuple, the tuple will still appear with missing attributes shown as NULL.
SELECT * FROM R
FULL OUTER JOIN S
ON R.attribute = S.attribute

Full Outer Join

Left Outer Join
Left Outer Join : Select and joins tuple from the “left” table (R) with tuples from the “right” table (S) on defined attributes. If there is no match, the attributes from the right side will contain NULL values.
SELECT * FROM R
LEFT OUTER JOIN S
ON R.attribute = S.attribute

Left Outer Join

Right Outer Join
Right Outer Join : Select and joins tuple from the “left” table (R) with tuples from the “right” table (S) on defined attributes. If there is no match, the attributes from the left side will contain NULL values.
SELECT * FROM R RIGHT OUTER JOIN S ON R.attribute = S.attribute

Right Outer Join

Full Outer Join, Left Outer Join and Right Outer Join

Exercise 3
Dimension of T: Dim(T) = (No. of rows in T, No. of columns in T) Dim(R) = (2, 2)
Dim(S) = (3, 3)
Dim(𝑅 ∗ 𝑆) = (2 x 3, 2 + 3) = (6, 5)
Build the Cartesian Product of 𝑅 ∗ 𝑆.

Solution to Exercise 3
Dim(𝑅) = (2, 2) Table: 𝑆
Table: 𝑅 ∗ 𝑆
Dim(𝑆) = (3, 3)
Dim(𝑅 ∗ 𝑆) = (6, 5)

Exercise 4
❑ Apply natural (inner) join, left outer join, right outer join and full outer join on Std_Name. Table 1

Solution to Exercise 4
Inner Join/ Natural join
Left Outer Join

Solution to Exercise 4
Right Outer Join
Full Outer Join

Source: petcare.com.au

程序代写 CS代考加微信: powcoder QQ: 1823890830 Email: powcoder@163.com

Related Posts