Skip to content Skip to navigation

Connexions

You are here: Home » Content » Exercise: Relational Algebra

Navigation

Lenses

What is a lens?

Definition of a lens

Lenses

A lens is a custom view of the content in the repository. You can think of it as a fancy kind of list that will let you see content through the eyes of organizations and people you trust.

What is in a lens?

Lens makers point to materials (modules and collections), creating a guide that includes their own comments and descriptive tags about the content.

Who can create a lens?

Any individual member, a community, or a respected organization.

What are tags? tag icon

Tags are descriptors added by lens makers to help label content, attaching a vocabulary that is meaningful in the context of the lens.

This content is ...

Affiliated with (What does "Affiliated with" mean?)

This content is either by members of the organizations listed or about topics related to the organizations listed. Click each link to see a list of all content affiliated with the organization.
  • VOCW

    This module is included inLens: Vietnam OpenCourseWare's Lens
    By: Vietnam OpenCourseWare

    Click the "VOCW" link to see all content affiliated with them.

Recently Viewed

This feature requires Javascript to be enabled.
 

Exercise: Relational Algebra

Module by: Nguyen Kim Anh. E-mail the author

Exercise 1

Consider the relations:

Table 1
r(A B C) s(D E F) t(A B D)
a1 b1 1 d1 e1 1 a1 b1 d1
a2 b2 4 d2 e2 4 a2 b2 d2
  d3 e2 8 a1 b3 d3
  d3 e3 1 a2 b1 d2
    a3 b3 d3
    a3 b1 d1
    a2 b3 d3

Show the results of the following relational algebra expressions

  1. Π AB ( σ D <> d1 ( t ) ) AB ( r ) Π AB ( σ D <> d1 ( t ) ) AB ( r ) size 12{Π rSub { size 8{ ital "AB"} } \( σ rSub { size 8{D`"<>"`d1} } \( t \) \) `\`Π rSub { size 8{ ital "AB"} } \( r \) } {}
  2. r * t * s
  3. t ÷ Π A ( r ) * s t ÷ Π A ( r ) * s size 12{ left (t div Π rSub { size 8{A} } \( r \) right ) * s} {}
  4. r ⊳ ⊲ C ≥ F s r ⊳ ⊲ C ≥ F s size 12{r`` ⊳ ⊲ rSub { size 8{C >= F} } `s} {}

Exercise 2

Consider the relations

Table 2
r(A B C) s(D E ) t(A D G)
a1 b1 1 d1 e1 a1 d1 11
a2 b2 2 d2 e2 a1 d2 12
a3 b3 3 d3 e3 a2 d1 21
a3 b1 4   a2 d2 22
    a3 d1 31
    a2 d3 23

Show the results of the following expressions.

  • Π EG ( σ A = a2 ∨ A = a3 ( s ∗ t ) ) Π EG ( σ A = a2 ∨ A = a3 ( s ∗ t ) ) size 12{Π rSub { size 8{ ital "EG"} } \( σ rSub { size 8{A=a2` or `A=a3} } \( s*t \) \) } {}
  • Π AD ( t ) ÷ Π A ( r ) Π AD ( t ) ÷ Π A ( r ) size 12{Π rSub { size 8{ ital "AD"} } \( t \) ` div `Π rSub { size 8{A} } \( r \) } {}

Exercise 3

Consider two relations R1 and R2, where R1 contains N1 tuples and R2 contains N2 tuples, and N1 > N2 > 0. Give the minimum and maximum possible sizes (in tuples) for the result relation produced by each of the following relational algebra expressions. In each case state any assumptions about the schemas of R1 and R2 that are needed to make the expression meaningful.

  • R1 size 12{ union } {} R2
  • R1 size 12{ intersection } {} R2
  • R1 \ R2
  • R1 × R2
  • σ a = 5 ( R1 ) σ a = 5 ( R1 ) size 12{σ rSub { size 8{a=5} } \( R1 \) } {}
  • π a ( R1 ) π a ( R1 ) size 12{π rSub { size 8{a} } \( R1 \) } {}
  • R1 size 12{ div } {} R2

Exercise 4

Consider the following relational schema:

Suppliers(sid, sname, address)

Parts(pid, pname, colour)

Catalog(supplier, part, cost)

Assume that the ids are integers, that cost is a real number, that all other attributes are strings, that the supplier field is a foreign key containing a supplier id, and that the part field is a foreign key containing a part id. Write a relational algebra expression to answer each of the following queries:

  • Find the names of suppliers who supply some red part.
  • Find the sids of suppliers who supply some red or green part.
  • Find the sids of suppliers who supply some red part or whose address is 221 Packer Street.
  • Find the sids of suppliers who supply some red part and some green part.
  • Find the sids of suppliers who supply every part.
  • Find the sids of suppliers who supply every red part.
  • Find the sids of suppliers who supply every red or green part.
  • Find the sids of suppliers who supply every red part or supply every green part.
  • Find the pids of parts that are supplied by at least two different suppliers.
  • Find pairs of sids such that the supplier with the first sid charges more for some part than the supplier with the second sid.
  • Find the pids of the most expensive part(s) supplied by suppliers named Yosemite Sham.

Exercise 5

Consider the following database schema

Student (StudentId, FullName, BDate, Address)

Course (Code, Name, Lecturer)

Enrol(StudentId, Code, Grade)

Specify the following queries in relational algebra

  1. List the name of all courses
  2. List the StudentId, FullName of students who lives in Hanoi
  3. List the StudentId of students who enrol in course ‘COMP-1101’ but not in course ‘COMP-1102’
  4. List the name of students who enrol in both course ‘COMP-1101’ and course ‘COMP-1102’
  5. List the name, lecturer of all course in which student ‘John Smith’ has enroled
  6. List the name of student who enrol in at least one course teach by Professor Le Quan
  7. List the name, address of all student who enrol in every course teach by Professor Le Quan
  8. List the name of the course which has no student enrolled in

Content actions

Download module as:

PDF | EPUB (?)

What is an EPUB file?

EPUB is an electronic book format that can be read on a variety of mobile devices.

Downloading to a reading device

For detailed instructions on how to download this content's EPUB to your specific device, click the "(?)" link.

| More downloads ...

Add module to:

My Favorites (?)

'My Favorites' is a special kind of lens which you can use to bookmark modules and collections. 'My Favorites' can only be seen by you, and collections saved in 'My Favorites' can remember the last module you were on. You need an account to use 'My Favorites'.

| A lens I own (?)

Definition of a lens

Lenses

A lens is a custom view of the content in the repository. You can think of it as a fancy kind of list that will let you see content through the eyes of organizations and people you trust.

What is in a lens?

Lens makers point to materials (modules and collections), creating a guide that includes their own comments and descriptive tags about the content.

Who can create a lens?

Any individual member, a community, or a respected organization.

What are tags? tag icon

Tags are descriptors added by lens makers to help label content, attaching a vocabulary that is meaningful in the context of the lens.

| External bookmarks