Purchase Solution

Relational Algebra and Tuple Relational Calculus

Not what you're looking for?

Ask Custom Question

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').

Attachments
Purchase this Solution
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.

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' ...

Purchase this Solution
Free BrainMass Quizzes
Basic Computer Terms

We use many basic terms like bit, pixel in our usual conversations about computers. Are we aware of what these mean? This little quiz is an attempt towards discovering that.

C# variables and classes

This quiz contains questions about C# classes and variables.

Basic Networking Questions

This quiz consists of some basic networking questions.

Excel Introductory Quiz

This quiz tests your knowledge of basics of MS-Excel.

Word 2010: Tables

Have you never worked with Tables in Word 2010? Maybe it has been a while since you have used a Table in Word and you need to brush up on your skills. Several keywords and popular options are discussed as you go through this quiz.

View More Free Quizzes
Related BrainMass Solutions

  • Queries using Relational Algebra and Relational Calculus

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

  • Relational Algebra Expressions

    B) Using Relational algebra, create a view of all rooms in the ABC Hotel, excluding price details. .jpg is for part A) and .doc is for part B) Relational algebra expressions are examined in the solution.

  • SQL Conversion to Relational Algebra: Example Problem

    244469 SQL Conversion to Relational Algebra: Example Problem Convert this SQL statement into relational algebra: SELECT COUNT(*) FROM Employees e1,e2, Department d WHERE e1.lName = 'Adams' AND e1.fName = 'James' AND e1.empID = d.mgrEmpID AND d.deptNo

  • Relational Algebra

    Relational algebra is applied.

  • Relational Algebra Expressions for the attached file !

    324714 Show relational algebra expressions for given requirements. Show relational algebra expressions for: a. The names of all salespeople. b. The names of all salespeople having an ORDER row. c.

  • Relational Algebra Expression

    Generate the relational algebra expressions for the following queries: (a) List all hotels. (b) List all single rooms with a price below $20 per night. (c) List the names and cities of all guests.

  • Relational Database Management system

    Most importantly, Relational Database Management system supports relational algebra and so supports the relational operations of set theory. 2. Trying to compare and contrast the Hierarchical Database Structure and the Network Database Structure.

  • Key benefit provided by the relational model over previous data

    Nested loop join--> he naive algorithm that joins two relations R and S by making two nested loops: For each tuple in R as r do For each tuple in S as s do If r and s satisfy the join condition Then output the tuple

  • Important information about Database Definitions Exercise

    For example: the author's name, title and/or keywords. b. Record - A record in the framework of a relational database is a row which is also known as a record or tuple symbolizing a solitary, yet implicitly prepared data point in a table.

View More Related Solutions