Explore BrainMass
Share

Relational Algebra and Tuple Relational Calculus

This content was STOLEN from BrainMass.com - View the original, and get the already-completed solution here!

Consider the following relation schemas (in attached image). Primary keys are underlined.

Use (1) Relational Algebra and (2) Tuple Relational Calculus to express the following queries:
1. Find the instruments (InstID) played by musician named 'John'.
2. Find the titles of the albums produced by musicians who play guitar or piano (iname='guitar' or 'piano').
3. Find the musicians who played both song1 (songID='song1') and song2 (songID='song2').
4. Find the musician(s) with the highest annual income.
5. Find all the pairs of musicians (give names) who share the same addresses.
6. Find the songs that are not performed by any musician.
7. Find the musicians (names) who played all the songs written by 'John' (musician author's name = 'John').

© BrainMass Inc. brainmass.com October 25, 2018, 7:26 am ad1c9bdddf
https://brainmass.com/computer-science/algorithms/relational-algebra-and-tuple-relational-calculus-503152

Attachments

Solution Preview

1. Find the instruments (InstID) played by musician named 'John'.

Since join attribute 'ssn' is named same in both the relations - Musicians and Plays, we can apply natural join here.

Result <= MinstrID (?name='John' (Musicians * Plays))

Another way to do this would be as follows:

Result ? MinstrID ((?name='John' Musicians) * Plays)

Second query will significantly reduce the number of Musicians tuples that participate in natural join.

2. Find the titles of the albums produced by musicians who play guitar or piano (iname='guitar' or 'piano').

Result <= Ltitle (((omega*iname='guitar' OR iname='piano' Instruments) * Plays) * AlbumProducer)

Here, innermost natural join "... Instruments) * Plays" on join attribute 'instrID' results in tuples that give the 'ssn' of Musicians that play either guitar or piano. This result is further 'natural join'-ed with AlbumProducer on join attribute 'ssn'.

Another way to achieve same result would be as follows:

Result <= Mtitle ((Mssn ((omega*iname='guitar' OR iname='piano' Instruments) * Plays)) * AlbumProducer)

3. Find the musicians who played both song1 (songID='song1') and song2
(songID='song2').

Result <= ((Mssn (omega*songID='song1' Perform)) and (Mssn (omega*songID='song2' ...

Solution Summary

Kindly refer to the solution attachment 503152.doc for answer with proper symbols/notations. Little bit of supportive explanation is also provided with many queries.

$2.19
See Also This Related BrainMass Solution

Specify the given queries using relational algebra and tuple relational calculus.

Consider the following University Database schemas:
Department (D-code, D-Name, Chair-SSn)
Course (D-code, C-no, Title, Units)
Prereq (D-code, C-no, P-code, P-no)
Class (Class-no, D-code, C-no, Instructor-SSn)
Faculty (Ssn, F-Name, D-Code, Rank)
Student (Ssn, S-Name, Major, Status)
Enrollment (Class-no, Student-Ssn)
Transcript (Student-Ssn, D-Code, C-no, Grade)

In the above tables, primary keys are underlined. Some explanations of attributes are as follows. Chair-Ssn in Department refers to Ssn in Faculty table. Instructor-SSn refers to Ssn in Faculty table, and Student-Ssn refers to Sssn in Student table. Class-no is a unique ID for each offering of a course. (For example, a class with class-no='12020' may be an offering of INFS 614, in which case, we may have a row like <12020, INFS, 614, 100009399> in the Class table.) <P-code, P-no> in Prereq table refers to <D-code, C-no>in the Course table, i.e., they refer to courses. A example row in Prereq table is <INFS,614, INFS, 501> , which means that INFS614 needs INFS501 as a prerequisite course. Other foreign keys and meanings of the tables should be obvious.

Specify the following queries in relational algebra, relational calculus and implement them in SQL (I do not need help with SQL implementation):
1. List the courses (D-code and C-no), along with the names of the students who are currently taking them.
2. List all the courses (D-code and C-no) that John (i.e., S-Name=``John'') got 'A' grade.
3. List the courses (D-Code and C-No) that do not require any pre-requisites.
4. Give the students (Ssn) who are enrolled in INFS614 (i.e., D-code=``INFS'' and C-no=``614'') and have satisfied all its prerequisites.

View Full Posting Details