CS代写 MC316 - PowCoder代写

PowerPoint Presentation

THE relational algebra Unary operations of Projection and Selection

Student Objectives
Upon completion of this video, you should be able to:
Write a relational algebra expression that uses SELECTION given a table and a query.
Given a table and a SELECTION relational algebra expression, show the new table that would be returned once the expression is performed on the table.
Write a relational algebra expression that uses PROJECTION given a table and a query.
Given a table and a PROJECTION relational algebra expression, show the new table that would be returned once the expression is performed on the table.
Write an expression that renames an attribute
Break an expression down so that it creates temporary tables that are used as input to the next expression

Examples of Projection & Selection
Using the following data:

Create a new table from a given table and in that new table return only the rows that satisfy a given condition
Symbol  σ
Example Expression:
σ Age > 30 (EMPLOYEE)

Symbol for Selection
Condition that each row must satisfy to be returned in the answer
Table Name
ID FirstName LastName Age

ID FirstName LastName Age

QUESTION: What would be returned with the expression:
σ Salary > 3000 (Employee)

The above rewritten as an English question would be:
Find all the employee information about employees who make a salary greater than 3000.
This is called a QUERY

SSN LastName MiddleInitial FirstName Bdate Address Sex Salary SuperSSN DeptNum
3 Beuvieau P Patty 3/3/59 Toronto F 4000 6 Y5J
4 Burns P Montgomery 7/7/20 Toronto M 5000 S7G

PROJECTION
Create a new table from a given table and in that new table return only the COLUMNS that satisfy a given condition
Symbol  π
Example Expression:
π Age,LastName (EMPLOYEE)

Symbol for Projection
The Columns (Attributes) that should be returned
Table Name
Age LastName
13 Simpson

ID FirstName LastName Age

QUESTION: Rewrite the question above as a query (English Question):
QUESTION: What would be returned with the expression:
π LastName, FirstName (Employee)
ANSWER: Give me just the first name and last name of all the employees.

LastName FirstName
Simpson NSWER

QUESTION: Write the expression to find all project information about projects located in Toronto or London:
QUESTION: Write the expression to find all department names:
QUESTION: Write the expression to find the address and first name of male employees:
σ (ProjectLocation = ‘Toronto’ or ProjectLocation = ‘London’) (Project)

π DeptName (Department)
π Address,FirstName( σ (Sex=‘M’) (Employee))

Sequence of Operations
Building Temporary Tables
Can break a series of operation down into separate operation and temporarily rename resulting relations

Example: The following expression:
π LastName, Sex (σ BDate > 1/1/70 (Employee))

Can be broken down into:

Temp1  σ BDate > 1/1/70 (Employee)
Temp2  π LastName, Sex (Temp1)

SSN LastName MiddleInitial FirstName Bdate Address Sex Salary SuperSSN DeptNum
1 Bart 2/2/95 London M 1000 2 G8H
6 Lisa 6/6/90 London F 1000 2 S7G

LastName Sex

Renaming Attributes:
You may need to rename attributes to make the names easier to understand and occasionally you MUST rename attributes when performing union and joins.
TempTabA (LName, MorF)  π LastName, Sex (Temp2)
LastName Sex

TableA  π LastName, Sex (σ Bdate > 1/1/70 (Employee))
TableB  σ Bdate > 1/1/70 (π LastName, Sex (Employee))
QUESTION: Are these the same? YES or NO?

SSN LastName MiddleInitial FirstName Bdate Address Sex Salary SuperSSN DeptNum
1 Bart 2/2/95 London M 1000 2 G8H
6 Lisa 6/6/90 London F 1000 2 S7G

LastName Sex

Smithers M

NEVER SHOW DUPLICATE ROWS IN RELATIONAL ALGEBRA

QUESTION: What would be returned with the expression: π MiddleInitial(Employee)

MiddleInitial

THE relational algebra Binary operation of Union and Difference

Student Objectives
Upon completion of this video, you should be able to:
Decide if 2 tables are Union Compatible
Write a relational algebra expression that uses UNION given two tables and a query.
Given 2 tables and a UNION relational algebra expression, show the new table that would be returned once the expression is performed.
Write a relational algebra expression that uses DIFFERENCE given two tables and a query.
Given 2 tables and a DIFFERENCE relational algebra expression, show the new table that would be returned once the expression is performed.

Binary Operations
In arithmetic 8 ÷ 3 would be a binary operation because it has 2 operands: 8 and 3 and one operator: ÷
Most of the remaining relational algebra expression we are going to look at use binary operators (i.e. they require TWO tables)
For example: Table1 U Table2 would return a new table, our result.
Could also write: RESULTTABLE  Table1 U Table2

Union Compatible
Two tables are union compatible, if and only if:
They have the same number of columns
Each respective column from each table is from the same domain
Examples  Are these Union Compatible?
TableA and TableB
TableA and TableC
TableB and TableD

ID Name Age

ID Age Name

Creates a new table from the given 2 tables that include every row from both tables with NO repeating identical rows.
The 2 Tables MUST be union compatible
Symbol  U
Example Expression:
Table1 U Table2

Symbol for Union
First Table Name
Second Table Name
ID FirstName LastName Age

ID FirstName LastName Age
33 Marg Jones 28

Temp1 (SuperSSN)  π ManagerSSN (Department)
Result  Temp1 U π SuperSSN(Employee)

QUESTION: What would the following relational expression result in?
Temp1(Field1)  π MiddleInitial (Employee)
Temp2(Field1)  π Sex (Employee)
Result  Temp1 U Temp2

QUESTION: What would the following relational expression result in?
Temp1(Loc)  πProjectLocation (Project)
Temp2(Loc)  πAddress (σ LastName = “Simpson” (Employee))
Result  Temp1 U Temp2
QUESTION: What, in English, does the above expression represent?

Springfield

Show me all the project locations cities together with the cities that the Simpson employee’s live in.

Write a relational algebra expression that would need to use Union
Write the Relation Algebra expression to answer this query:
Return the first name of all students and faculty at Western whose name starts with D

ID FirstName LastName Office Number
12 MC316
45 SSC22

StudentID FName LName HomeCity Major
2501 Windso Math

Temp1(FName)  π FirstName (σFirstName like “D*”(Faculty))
Temp2  π FName(σFName like “D*”(Student))
ANSWER  Temp1 U Temp2

DIFFERENCE
Create a new table from the given 2 tables that include every row from the table on the left side that is NOT in the table on the right side.
The 2 Tables MUST be union compatible
Symbol  –
Example Expression:
Table1 – Table2

Symbol for Difference
First Table Name
Second Table Name
ID FirstName LastName Age

ID FirstName LastName Age

ID FirstName LastName Age
33 Marg Jones 28

More ON DIFFERENCE
NOTE that Table1 – Table2 does NOT equal Table2 – Table1
e.g Answer  Table2 – Table1

ID FirstName LastName Age

ID FirstName LastName Age
33 Marg Jones 28

RESULT  πLastName(Employee) – πLastName(σSex=“M”(Employee))

QUESTION: What would the following relational expressions result in?
Temp1 (SSN)  π ManagerSSN (Department)
Temp2 (SSN)  π SuperSSN (σSuperSSN <>Null(Employee))
Result1  Temp1 – Temp2
Result2  Temp2 – Temp1

QUESTION: What do they mean in English?
Result1 means: Show me all the employee ids for people who are departmental managers but not also supervisors.
Result2 means: Show me all the ids for people who are employee supervisors but not also departmental managers.

Write a relational algebra expression that would need to use DIFFERENCE
Write the Relation Algebra expression to answer this query:
Return the first name of all faculty members who don’t have the same first name as any of our students.

ID FirstName LastName Office Number
12 MC316
45 SSC22

StudentID FName LName HomeCity Major
2501 Windso Math

Temp1(FName)  π FirstName (Faculty)
Temp2  π FName(Student)
ANSWER  Temp1 – Temp2

THE relational algebra Binary operation of Cartesian Product

Student Objectives
Upon completion of this video, you should be able to:
Write a relational algebra expression that uses CARTESIAN PRODUCT given two tables and a query.
Given 2 tables and a CARTESIAN PRODUCT relational algebra expression, show the new table that would be returned once the expression is performed.
Determine how many attributes/columns will be in the resulting table when two tables are CARTESIAN PRODUCTED together.
Determine how many row will be in the resulting table when two tables are CARTESIAN PRODUCTED together.
Identify patterns in a CARTESIAN PRODUCT resulting table will help determine the answer to queries.

Cartesian Product
Creates a new table from the given 2 tables where every row in the new table is a match of each row from each table.
The new table will have all the attributes of the first
table AND all the attributes of the second table
The new table’s number of rows will equal first table’s number
of rows * the second table’s number of rows.
Symbol  X
Example Expression:
Table1 X Table2

Symbol for Cartesian Product
First Table Name
Second Table Name
ID FirstName LastName Age Table2.ID Table2.FirstName Table2.LastName Table2.Age
12 24 33 Marg Jones 28
24 13 33 Marg Jones 28
45 45 33 Marg Jones 28
12 24 24 13
24 13 24 13
45 45 24 13

ID FirstName LastName Age
33 Marg Jones 28

ID FirstName LastName Age

11 Pig Cat
22 Dog Cat

AA.A B C BB.A D
11 Pig Cat Horse 11
22 Dog Cat Horse 11
11 Pig Cat Pig 22
22 Dog Cat Pig 22
11 Pig Cat Pig 33
22 Dog Cat Pig 33

Example of Cartesian Product:

Department X Project:
QUESTION: How many tuples are above?
How many columns/attributes are there?
Where did those numbers come from?

QUESTION: What would the following relational algebra expression result in?
Temp1 (LastName, FN) π LastName, FirstName (σ EmpID > 4 (Employee))
Temp2  π FirstName, Salary, Sex (σ Sex = “M” (Employee))
Result  Temp1 X Temp2
LastName FN
Simpson Name Salary Sex
Bart 1000 M
Waylan 2000 M
Monty 5000 M
Homer 2000 M

QUESTION: What would the following relational algebra expression result in?
Temp1  π DeptName, ManagerEmpID (Department)
Temp2  π LastName, FirstName, EmpID (Employee)
Result  Temp2 X Temp1

QUESTION: Do you notice any patterns in the resulting relationship (Hint: Look for attributes that are equal)

QUESTION: Suppose I asked you to give me the name of the managers of the departments and their department names, how could you use the above result to answer my query (question)?

Temp1 π FirstName,LastName, EmpID (Employee)
Temp2 π DeptName,ManagerEmpID (Department)
Temp3 (Temp1 X Temp2)
ANSWER π FirstName,LastName, DeptName (σEmpID=ManagerEmpID( Temp3))

The power of Cartesian Product!
If you use a Cartesian Product WITH a Selection (and some Projections to limit down the number of attributes), you can join your tables together to figure out things like:
Who is managing the departments?
What are the names of the projects that Homer work on, and for how long on each?
What projects are managed by the “Head Office” Department?

This is how you would answer the second query above  What are the names of the projects that Homer work on, and for how long on each?
Temp11 π EmpID (σFirstName=“Homer”( Employee)
Temp22 (Temp1 X WorksOn X Project)
ANSWER π Project.ProjectName,Hours (σ(EmpID=EmpSSNum AND WorksOn.ProjectNum=Project.ProjectNum)(Temp22))

THE relational algebra Binary operation of INNER JOIN (Natural Join and equi join)

Student Objectives
Upon completion of this video, you should be able to:
Identify the symbols for natural join and equi join.
Determine if the join will be possible based on the given tables and join information.
Write a relational algebra expression that uses JOINS given two tables based on a given query.
Given 2 tables and a JOIN relational algebra expression, show the new table that would be returned once the expression is performed.
Given a JOIN relational algebra expression and two tables, explain in simple English what query is answered by the expression.

A join is just a Cartesian Product X with a Selection σ to find matches. The selection will remove some of the rows/tuples from the returned Cartesian Product.
The attributes that will be in the new table depends on if you are doing an equi join or a natural join
Symbol  ⋈
Example Expression:

Symbol for Join and maybe a subscript saying which column to join on.
First Table Name
Second Table Name
ID FirstName LastName Age

ANSWER FOR Table1 ⋈ Table2
ID FirstName LastName Age
33 Marg Jones 28

ID FirstName LastName Age

ID FirstName LastName Age Table2.ID Table2.FirstName Table2.LastName Table2.Age
12 24 24 13
24 33 33 Marg Jones 28
45 33 33 Marg Jones 28

ANSWER FOR Table1 ⋈ Age=ID Table2
Table1 ⋈ Table2 (natural join)
Table1 ⋈ columnnametable1=columnnametable2Table2 (equi join)

MORE ON Joins
R ⋈ Boolean formula S: Produces a relation that contains tuples satisfying a condition from the Cartesian Product of R X S where the formula can contain comparisons using one of <,<=,>,>=,=,<> connected by and, or and not

Department ⋈ ManagerSSN = SuperSSN Employee

Staff ⋈ Salary > Salary and JobLevel=JobLevel Manager

Natural Join More Info:
If the two tables being joined have the same attribute name(s), it is a natural join and the attribute will only show up ONCE in the resulting table
The natural join includes EACH pair of attributes with the same name, “AND” ed together, for example:
Q  R(A,B,C,D) ⋈ S(C,D,E)
Result would only keep one copy of each pair
R.C=S.C AND R.D = S.D
and would give: Q(A,B,C,D,E)

Equi Join: when all of the comparisons are =, then it is called an equi join and pairs of the attributes are returned that are equal (i.e. attribute from both sides of the equals are returned).
Natural Join: when all of the comparisons are = and it matches any attribute in Table1 that has the same name as the attribute in Table2. The attribute is only shown once.

7 Cow Pink 22
8 Dog Pink 33
9 Cow Red 44

6 Cow Blue Hat 33
6 Cow Blue Sock 44
8 Cow Pink Shoe 44
8 Cat Pink Hat 33

Answer  Table1 ⋈ D < D Table1 Answer  Table1 ⋈ Table2 A E C F D B 8 Cat Pink Hat 33 Dog Answer  Table1 ⋈ A=ATable2 Table1.A Table2.A E Table1.C F Table1.D B Table2.C Table2.D 8 8 Cow Pink Shoe 33 Dog Pink 44 8 8 Cat Pink Hat 33 Dog Pink 33 Answer  Table1 ⋈ C=CTable2 Table1.A B Table1.C Table1.D Table2.A E Table2.C F Table2.D 7 Cow Pink 22 8 Cow Pink Shoe 44 7 Cow Pink 22 8 Cat Pink Hat 33 8 Dog Pink 33 8 Cow Pink Shoe 44 8 Dog Pink 33 8 Cat Pink Hat 33 A B C D Table1.A Table1.B Table1.C Table1.D 7 Cow Pink 22 8 Dog Pink 33 7 Cow Pink 22 9 Cow Red 44 8 Dog Pink 33 9 Cow Red 44 Answer  (π A (Table1)) ⋈ Table2 8 Cow Pink Shoe 44 8 Cat Pink Hat 33 7 Cow Pink 22 8 Dog Pink 33 9 Cow Red 44 More realistic Examples of Joins: Example 1: Equi Join on Department and Project: Project ⋈ Project.DeptNumber = Department.DeptNumber Department Example 2: Natural Join on Department and Project: Project ⋈ Department QUESTION: What will be the resulting table of the following relational algebra expression? π LastName, DeptName (Employee ⋈ Department) QUESTION: What does the above result represent in English? ANSWER: Show me all the Employee’s last names and the name of the Department that they have been assigned to. QUESTION: What will be the difference that will result in the answers in the following two relation algebra expressions? Expression 1: Employee ⋈ Department Expression 2: Employee ⋈ DeptNumber = DeptNumber Department QUESTION: What will the following expression return? Temp(LN, FN, SSN)  π LastName,FirstName,EmpID (Employee) Result  Temp⋈SSN=SuperSSN(π LastName,FirstName,SuperSSN,EmpID(Employee)) QUESTION: In English, what does the above expression represent? ANSWER: Show me the id and first and last names of the supervisors and the ids and first and last names of the employees that they supervise. QUESTION: Write the Relational Algebra expression to print the department name, and the manager's first and last name: QUESTION: Write the Relational Algebra expression to display department names of departments who have people making more than 4,000 dollars. TEMP  π DeptNumber (σ Salary > 4000 (Employee))
ANSWER π DeptName (TEMP ⋈ Department)
π DeptName, LastName, FirstName (Employee ⋈ EmpID = ManagerEmpID Department)

QUESTION: Write the Relational Algebra expression to print the first and last name of all employees, the name of the projects they work on and the number of hours they work on each project. (This is a join for a many to many relationship)
TEMP  π FirstName, LastName, ProjectNumber,Hours (EMPLOYEE ⋈ EmpID=EmpSSNNumberWORKSON)
ANSWER π FirstName, LastName, Hours,ProjectName (TEMP ⋈ PROJECT)

ANSWER π FirstName, LastName, Hours,ProjectName (EMPLOYEE ⋈ EmpID=EmpSSNNumberWORKSON ⋈ PROJECT)

THE relational algebra Binary operation of Outer Joins (FULL, RIGHT AND LEFT)

Student Objectives
Upon completion of this video, you should be able to:
Identify the symbols for FULL, LEFT and RIGHT outer joins.
Write a relational algebra expression that uses OUTER JOINS given two tables based on a given query.
Given 2 tables and an OUTER JOIN relational algebra expression, show the new table that would be returned once the expression is performed.
Given an OUTER JOIN relational algebra expression and two tables, explain in simple English what query is answered by the expression.

FULL OUTER JOIN
A full outer join is similar to a join except that it includes all the rows from both tables even if they don’t have a matching value in the column that you are joining. If there is no match, put nulls in the columns from the other table.

A B C D F G

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

Related Posts