CS386 Introduction to Databases
Spring 2006 Quarter

Assignment 2 - SQL & Relational Algebra 1

Due: Thursday, 20 April 2006 at the beginning of class
 
You may do this assignment individually or you may work with one partner.  That is, this assignment is to be completed by individuals or by teams of two students.  If you work with a partner, then you should turn in one assignment paper, with both of your names on the paper. You should only talk to the instructor, the TA and your partner about this assignment. You may also post questions to the course mailing list, cs386@cs.pdx.edu.
Please turn in your completed assignments on paper. Put your last name, first name, the assignment number in that order in the first line of your assignment.  List last name and first name for your partner, if you have one, on the second line of your assignment. (If you are working with a partner, turn in one assignment paper.)
This assignment is based on the Spy relational database. Information about this database and information how to access it is on the Database Info Page. Note that the database you want is called ‘introdb_spy’.
Part I
Relational Algebra
Note: you won't be able to execute these queries, since we don't have an implemented system that supports user queries written in relational algebra.

Write the following queries in Relation Algebra, using ONLY the select, project and cross product operators:
 
[Although the textbook states that attributes with naming conflicts must be referred to by position number only in the result of a cross product, it is standard practice for clarity to instead use the source relation name to resolve the attribute names.  Thus, either method of identifying attributes was accepted here.]
 
(a) Find the city and country for all agents with last name Li.
 
π city, country (σlast = ‘Li’ (Agent))
 
(b) List first and last name of all agents with the Pilot skill.
 
π first, last (σskill = ‘Pilot’ ∧ Skill.skill_id = Skillrel.skill_id ∧ Skillrel.agent_id = Agent.agent_id (Skill × Skillrel × Agent))
 
(c) List the country, salary and clearance description of all agents who speak Vietnamese.
 
π country, salary, description (σsc_id = clearance_id (Securityclearance × σAgent.agent_id = Languagerel.agent_id (Agent × σLanguagerel.lang_id = Language.lang_id (Languagerel × σlanguage = ‘Vietnamese’ (Language)))))
 
 
Part II
Write the SQL for the following queries. Show (at most) the first five rows of the result for
each query and the number of rows returned.  You should be able to write these SQL queries
using only the features covered in the first two sets of lecture notes.
 
(a) Find the agent id and salary in Euros for all agents whose country is France. Name the result columns France_ids and Euro_pay.
 
[Since the currency of the salary value in the Agent table was nowhere stated, answers assuming either Euros or US Dollars were accepted.]
 
SELECT agent_id AS France_ids, (salary * 0.81) AS Euro_pay
FROM Agent
WHERE country = ‘France’
 
france_ids
euro_pay
2
41273.55
8
43219.17
20
40638.51
49
47679.84
64
44763.03
 
55 rows
 
(b) Find the number of different language skills
 
SELECT COUNT(lang_id) AS num_lang_skills
FROM Language
 
num_lang_skills
20
 
1 row
 
(c) Find the high, low and total salary for all agents with Majestic clearance.
 
SELECT MAX(salary) AS High, MIN(salary) AS Low, SUM(salary) AS Total
FROM Agent A INNER JOIN Securityclearance SC ON A.clearance_id = SC.sc_id
WHERE SC.sc_level = ‘Majestic’
 
high
low
total
365456
50008
11041010
 
1 row
 
(d) Find the team name for all teams with at least one agent with the Cryptographer skill.
Do this query two ways: Once using NATURAL JOIN and one without any JOIN operator in the FROM clause. (You only need to submit one copy of the result.)
 
SELECT DISTINCT name
FROM Team NATURAL JOIN Teamrel NATURAL JOIN Skillrel NATURAL JOIN Skill
WHERE skill = ‘Cryptographer’
 
SELECT DISTINCT name
FROM Team T, Teamrel TR, Skillrel SR, Skill S
WHERE T.team_id = TR.team_id AND TR.agent_id = SR.agent_id
    AND SR.skill_id = S.skill_id AND skill = ‘Cryptographer’
 
name
Beasties
Boat Team 2
Boat Team 4
Cha Cha Cha
Failsafe
 
12 rows
 
(e) List the affiliations for each agent, including agents with no affiliations. The result should have first name, last name and affiliation description.
 
SELECT first, last, description
FROM Agent NATURAL LEFT OUTER JOIN
    (Affiliationrel NATURAL JOIN Affiliation)
 
first
last
description
Nick
Black
NULL
Bill
Bundt
Russian Foreign Intelligence
Mathew
Cohen
International Police Organisation
Jim
Cowan
NULL
George
Fairley
Secret terrorist group in Sicily
 
1,114 rows
 
(f) List the team name for each team that has an agent who can speak German and an agent who can speak Hebrew. Do this query twice: Once using INTERSECT and once without using that operator.
 
SELECT Team.name
FROM Team NATURAL JOIN Teamrel
    NATURAL JOIN Languagerel NATURAL JOIN Language
WHERE Language.language = 'German'
 
INTERSECT
 
SELECT Team.name
FROM Team NATURAL JOIN Teamrel
    NATURAL JOIN Languagerel NATURAL JOIN Language
WHERE Language.language = 'Hebrew'
 
--------------------------------
 
SELECT DISTINCT T.name
FROM  Team T, Teamrel TR1, Teamrel TR2, Agent A1, Agent A2, Languagerel LR1,     Languagerel LR2, Language L1, Language L2
WHERE T.team_id = TR1.team_id AND TR1.agent_id = A1.agent_id
    AND A1.agent_id = LR1.agent_id AND LR1.lang_id = L1.lang_id
    AND T.team_id = TR2.team_id AND TR2.agent_id = A2.agent_id
    AND A2.agent_id = LR2.agent_id AND LR2.lang_id = L2.lang_id
    AND L1.language ='German' AND L2.language = 'Hebrew'
 
name
Beasties
Blue Dagger
Blunt
Boat Team 1
Boat Team 3
 
26 rows
 
(g) Find all agents who can speak Hindi or who have the Biologist skill. Do this query twice: Once with UNION and once without using that operator.
 
SELECT Agent.agent_id
FROM Agent NATURAL JOIN Languagerel NATURAL JOIN Language
WHERE language = 'Hindi'
 
UNION
 
SELECT Agent.agent_id
FROM Agent NATURAL JOIN Skillrel NATURAL JOIN Skill
WHERE skill = 'Biologist'
 
-----------------------------------
 
SELECT DISTINCT Agent.agent_id
FROM ((((Agent LEFT OUTER JOIN Skillrel ON Agent.agent_id = Skillrel.agent_id)
    INNER JOIN Skill ON Skillrel.skill_id = Skill.skill_id)
    LEFT OUTER JOIN Languagerel ON Agent.agent_id = Languagerel.agent_id)
    INNER JOIN Language ON Languagerel.lang_id = Language.lang_id)
WHERE language = 'Hindi' OR skill = 'Biologist'
 
 
agent_id
7
33
36
50
71
 
144 rows