Relational Algebra - Select and Project Operators

• Relational Algebra - Select and Project Operators

http://www.youtube.com/watch?v=yVh_LcOcQdg

• Relational Algebra

Relational SELECT

SELECT is used to obtain a subset of the tuples of a relation that satisfy a select condition.

For example, find all employees born after 1st Jan 1950:

SELECTdob '01/JAN/1950'(employee)


Relational PROJECT

The PROJECT operation is used to select a subset of the attributes of a relation by specifying the names of the required attributes.

For example, to get a list of all employees surnames and employee numbers:

PROJECTsurname,empno(employee)

http://db.grussell.org/section010.html#_Toc67114472

• Relational Algebra: 5 Basic Operations

• Selection () Selects a subset of rows from
relation (horizontal).
• Projection () Retains only wanted columns
from relation (vertical).

• Cross-product (x) Allows us to combine two
relations.
• Set-difference (–) Tuples in r1, but not in r2.

• Union ( ) Tuples in r1 and/or in r2.

https://docs.google.com/viewer?a=v&q=cache:VDokuEkCX5wJ:inst.eecs.berkeley.edu/~cs186/sp06/lecs/lecture8Alg.ppt+&hl=en&pid=bl&srcid=ADGEESgiCeJZcOiv5iPRotaxu6pomoztERrMYuEVScwpi1kqlrF3ep4OJFlHIAWi4oJY0lFzFdq_eN73o0g7LQQo0Hvq34G_A9_pPIHPycr-NpyCL8B4brQhGmZwtReFMTuvHpynj-w5&sig=AHIEtbS3ZRzsDc-Udg4I5DzR4P1muwA-VA

• Relational Algebra

An algebra is a formal structure consisting of sets and operations on those sets.
Relational algebra is a formal system for manipulating relations.

Operands of this algebra are relations.
Operations of this algebra include the usual set operations (since relations are sets of tuples), and special operations defined for relations
selection
projection
join
http://www.cs.rochester.edu/~nelson/courses/csc_173/relations/algebra.html

• relational schema for a music albums database.

Keys are (mostly) underlined.
The attributes should be self-evident.
For a given music track, we code the title, its play length in time (minutes:seconds), its genre (pop, metal, jazz, etc.) and a 5 star maximum rating.
The musicians, singers and instrumentalists are all listed in on their contribution to the track.
A person may have 1 or more listing for a track. For example someone may both sing and play the piano.
The album is a collection of tracks.  An album is distributed and owned by a company called the label and has a producer and an engineer.
For a given music track, we code the title, its play length in time (minutes:seconds), its genre (pop, metal, jazz, etc.) and a 5 star maximum rating.
The musicians, singers and instrumentalists are all listed in on their contribution to the track.
A person may have 1 or more listing for a track. For example someone may both sing and play the piano.
The album is a collection of tracks.
An album is distributed and owned by a company called the label and has a producer and an engineer.


PEOPLE (PID, name, address, zip, phone)
CSZ (zip, city, state)
TRACKS (trID, title, length, genre, rating, albID) //trID is unique across all albums
ALBUMS (albID, albumTitle, year, label, prodPID, engPID, length, price)
CONTRIBS (trID, PID, role)


Use the R.A. notation below.
BE EXPLICIT in the join condition which attributes make the join where necessary.

Syntax reminder for Relational Algebra expressions:
SELECT :  condition(relation)
PROJECT : attribute-list(relation)
SET Operations and JOIN:  relation1 OP relation2, where OP is  , , - , , , and  ||condition
RENAME:  relation[new attribute names]
ASSIGN:    new-relation(attrs)  R.A. expression


a) List all names and phone numbers of people from zip 90210.
name, phone(zip=90210(PEOPLE))

b) List album titles and labels with a list price of more than $18.
albumTitle, label(price>18(ALBUMS))


c) List all the musicians and what they played or contributed to on all jazz type tracks.
name, role(genre= ‘jazz’(TRACKS |X| trID=trID CONTRIBS |X| PID= PID PEOPLE))


d) Get a list of names of people who produced OR engineered an album, but did not perform on any track.  (Hint: set operations are very helpful)
d) name(((prodPID ALBUMS)[PID]  (engrPID ALBUMS)[PID]) - PID CONTRIB)

          |X| PID= PID PEOPLE)


e) List names of musicians who have contributed in at least two different roles on the same tracks with ratings 4 or higher. (Hint: self-join)
name, role(rating>= 4 and role <>role2
(CONTRIBS |X| trID=trID and PID=PID CONTRIBS[trID, PID, role2] )
|X| PID= PID PEOPLE))


http://jcsites.juniata.edu/faculty/rhodes/dbms/funcdep.html

• relational algebra

Consider the following relation database
schema of people who places book orders.
Book(BookID,title,price)
Person(PersonID,Name,Zip)
Orders(PersonID,BookID,quantity,BillingID)
Billing(BillingID,PersonID,CreditCardNum)
Answer the following questions based on this schema. Pay particular attention to the language we
ask for the query in.
(a) Write a query in Relational Algebra to find the title of the book(s) with the lowest price. (3
points)
(b) Write a query in Relational Algebra to find Zip of every person who ordered the book with
the title ’Database Systems’. (4 points)
(c) Write a query in SQL to create the table Billing. Remember to specify the Primary Key and
the foriegn key constraints. All columns are of type Varchar(255). No column is allowed to
be NULL. (4 points)
(d) Write a query in SQL to find how much money has been spent on the books. (4 points)

Solution:
(a) πtitle(Book) − πB1.title(ρB1(Book) c ρB2(Book))
Where c = B1.price > B2.price
(b) πZip(σtitle= DatabaseSystems ((Book c1 Orders) c2 P erson))
Where c1 = Book.BookID = Orders.BookID
Where c2 = Orders.PersonID = Person.PersonID
(c) CREATE TABLE Billing (
BillingID Varchar(255) PRIMARY KEY,
PersonID Varchar(255) NOT NULL,
CreditCardNum Varchar(255) NOT NULL,
FOREIGN KEY (PersonID) REFERENCES Person(PersonID)
)
(d) SELECT SUM(booksales) FROM (SELECT (price*quantity) AS booksales FROM Orders o
LEFT JOIN Book b ON o.BookID = b.BookID)

webcache.googleusercontent.com/search?q=cache:8vRupC7L9OYJ:https://wiki.engr.illinois.edu/download/attachments/227743489/CS411-F2011-Final-Sol.pdf%3Fversion%3D1%26modificationDate%3D1380470739000+&cd=3&hl=tr&ct=clnk&gl=tr&client=firefox-a