Question

Specify this query in Relational Algebra using the given database schema: Find the sid's of sailors...

Specify this query in Relational Algebra using the given database schema: Find the sid's of sailors who made at least 3 reservations.

Saliors(sid, sname, rating, age)

Boats (bid, name,color)

Reserves(sid, bid, date)

Is this correct:

∏sname((OCOUNT(sid)>=3Reserves))⋈Sailors)

Homework Answers

Answer #1

Answer :

sid (count(sid)>=3 (Sailors Reserves) ) is the specification for the given query.

Explanation :

  • We have to all the sids. Therefore we have used projection. ()
  • sids should be selected from sailors.So, we have specified Sailors first in the Join.
  • sids should be from Sailors such that sid should have at least 3 Reserves i.e. the count of sids in Reserves should be greater than or equal to 3. (count(sid)>=3 )
  • But sids should be common in both Sailors and Reserves. Therefore we write join as " Sailors Reserves ".

We can also write it as sid ((count(sid)>=3 (Reserves)) Sailors )

  • We have to all the sids. Therefore we have used projection. ()
  • sids should have at least 3 Reserves i.e. the count of sids in Reserves should be greater than or equal to 3. (count(sid)>=3  (Reserves) )
  • Then we join with Sailors because we want sids that are common in the above select operation so performed and Sailors. ((count(sid)>=3 (Reserves)) Sailors )

Explanation for why your answer is wrong :

∏sname((OCOUNT(sid)>=3Reserves))⋈Sailors) is wrong because it prints all the snames rather than sids.

Know the answer?
Your Answer:

Post as a guest

Your Name:

What's your source?

Earn Coins

Coins can be redeemed for fabulous gifts.

Not the answer you're looking for?
Ask your own homework help question
Similar Questions
Relational Algebra (50 pts): Consider the following database schema that keeps track of Sailors, Boats and...
Relational Algebra (50 pts): Consider the following database schema that keeps track of Sailors, Boats and the boats reserved by sailors. Sailors(sid, sname, rating, age) Boatsbid, bname, color) Reserves(sid, bid, date) Keys are underlined in each relation. Specify the following queries in Relational Algebra using above database schema. (f) find the name of sailors with the highest rating (g) find the name and age of oldest sailors (h) find the age of youngest sailor for each rating level (i) find...
Given the following schema: Catalog(sid, pid, price) (Line under sid and pid) Suppliers(sid, sname, phonenum) (Line...
Given the following schema: Catalog(sid, pid, price) (Line under sid and pid) Suppliers(sid, sname, phonenum) (Line under sid) Parts(pid, pname, color) (Line under pid) Write the following queries in Relational Algebra: “Find names of suppliers who supply a blue part that costs less than 120 dollars and a yellow part that costs more than 150 dollars
1. In SQL. Given the following relational database schema . The primary key is underlined. All...
1. In SQL. Given the following relational database schema . The primary key is underlined. All attributes are of type string if not indicated otherwise.  branch(branch_name,branch_city,assets: integer)  customer(customer_i,customer_name,customer_street,customer_city)  account(account_number,branch_name,balance)  depositor(customer_id,account_number) a. Find the names of customers that have accounts in every branch in Miami. b. Find the names of customers who do not have an account. (assume the bank keeps the customer information even if the account is closed.)
Give the relational algebra, tuple calculus, and SQL expressions for each using the below schema: SPERSON(EmpID,...
Give the relational algebra, tuple calculus, and SQL expressions for each using the below schema: SPERSON(EmpID, Name, Dept) TRIP(TripID, EmpID, ToCity, StartDate, EndDate) EXPENSE(ExpID, TripID, AccountID, Amount) a) List the names and employee ID of a salesperson. b) List employee ID and employee names that took a trip(s) to Charleston city. c) List the names of all employees who have spent more than $500 on their trip. d) List employees' names and their IDs that have never taken a trip....
Specify the following queries in SQL on the COMPANY database. Show the result of each query....
Specify the following queries in SQL on the COMPANY database. Show the result of each query. Retrieve the names of all employees in department 5 who work more than 10 hours per week on the ‘ProductX’ project. List the names of all employees who have a dependent with the same first name as themselves. Find the names of all employees who are directly supervised by ‘Franklin Wong’. Specify the following updates using the SQL update commands. Show the state of...
The relational schema for the Academics database is as follows: DEPARTMENT(deptnum, descrip, instname, deptname, state, postcode)...
The relational schema for the Academics database is as follows: DEPARTMENT(deptnum, descrip, instname, deptname, state, postcode) ACADEMIC(acnum, deptnum*, famname, givename, initials, title) PAPER(panum, title) AUTHOR(panum*, acnum*) FIELD(fieldnum, id, title) INTEREST(fieldnum*, acnum*, descrip) Some notes on the Academics database: An academic department belongs to one institution (instname) and often has many academics. An academic only works for one department. Research papers (PAPER) are often authored by several academics, and of course an academic often writes several papers (AUTHOR). A research field...
You are tasked to design the ER diagram for the database for the 3S clothing store....
You are tasked to design the ER diagram for the database for the 3S clothing store. Data requirements are as given below. • Information about 3S employees includes a unique employee ID, and his/her name and position. • Each customer of 3S is identified by a customer ID, and has a name. • Information about the suppliers of 3S include a unique name, and a contact phone number. • Clothes stocked by 3S are described by a product code, size,...
Describe what the following query does using lyrics database with mysql. lyrics database is posted below...
Describe what the following query does using lyrics database with mysql. lyrics database is posted below 1.) select m.lastname, m.firstname, s.lastname from members m inner join salespeople s using (salesID) order by m.lastname asc; 2.) select studioID, studioname, base from salespeople sa inner join studios st on (sa.salesID = st.salesid) where base < 300 3.) SELECT artistName FROM Artists WHERE artistID IN (SELECT artistID FROM Titles) DROP TABLES IF EXISTS Artists,Genre, Members, Titles, Tracks,SalesPeople,Studios,XrefArtistsMembers; DROP TABLES IF EXISTS Authors,Publishers,Titles,Title_Authors,Royalties; DROP...
Summary The Ch08_ConstructCo database stores data for a consulting company that tracks all charges to projects....
Summary The Ch08_ConstructCo database stores data for a consulting company that tracks all charges to projects. The charges are based on the hours each employee works on each project. The structure and contents of the Ch08_ConstructCo database are shown in Figure P8.1. Use this database to answer the following problems. Database Schema The schema for the Ch08_ConstructCo database is shown below and should be used to answer the next several problems. Click this image to view it in its own...
using mysql lyrics.database. i will provide the lyrics schema database info below 1. List the first...
using mysql lyrics.database. i will provide the lyrics schema database info below 1. List the first name, last name, and region of members who do not have an email. 2. List the first name, last name, and region of members who do not have an email and they either have a homephone ending with a 2 or a 3. 3. List the number of track titles that begin with the letter 's' and the average length of these tracks in...
ADVERTISEMENT
Need Online Homework Help?

Get Answers For Free
Most questions answered within 1 hours.

Ask a Question
ADVERTISEMENT