Consider the following relations: Album (albumId: integer, title: string, artistId (FK): integer) Artist (artistId: integer, name: string) Composer (artistId: integer, name: string) Singer (artistId: integer, name: string) Write the following queries in relational algebra. (1) Find all the titles of the albums. (2) Find all the tuples whose albumId is 2. (3) Rename the relation Artist to Art and attributes to artId, title. (4) Find all the tuples that are either an Artist or a Composer. (5) Find the tuples of Artists who are not a Composer. (6) Find the tuples of people who are Composer and a Singer at the same time. (7) Find the titles of albums created by the artist ‘Black Sabbath’.

Consider the following relations: Album (albumId: integer, title: string, artistId (FK): integer) Artist (artistId: integer, name: string) Composer (artistId: integer, name: string) Singer (artistId: integer, name: string) Write the following queries in relational algebra. (1) Find all the titles of the albums. (2) Find all the tuples whose albumId is 2. (3) Rename the relation Artist to Art and attributes to artId, title. (4) Find all the tuples that are either an Artist or a Composer. (5) Find the tuples of Artists who are not a Composer. (6) Find the tuples of people who are Composer and a Singer at the same time. (7) Find the titles of albums created by the artist ‘Black Sabbath’.

A Guide to SQL

9th Edition

ISBN:9781111527273

Author:Philip J. Pratt

Publisher:Philip J. Pratt

Chapter2: Database Design Fundamentals

Section: Chapter Questions

Problem 5RQ: What is a relation?

See similar textbooks

Similar questions

question one: defined the following relation on A={0,2,5,6} Answer each of the following questions by listing the index of the relation. For example, for Part 1), if R1 and R2 and are reflexive, simply enter 1, 2 as the answer.1) Which relations are reflexive? 2) R0={(0,6),(6,6),(6,2),(0,0),(0,2),(2,2),(5,5)} 3) R1={(6,6),(2,2),(5,5),(0,0),(0,6)} 4) R2={(0,6),(6,6),(0,0),(5,6),(6,5),(5,0),(5,5),(2,2),(0,5) 5) R3={(5,5),(0,0),(6,6),(2,2) 6) R4={(5,5),(6,2),(6,6),(0,0),(6,5),(2,6),(2,2)} 1) Which relations are reflexive? 2) Which relations are symmetric? 3) Which relations are anti-symmetric? 4) Which relations are transitive? 5) Which relations are equivalence relations? 6) Which relations are partial orders? question two :defined the following relation on A={0,1,5,6} Answer each of the following questions by listing the index of the relation. For example, for Part 1), if R1 and R2 and are reflexive, simply enter 1, 2 as the answer. R0={(5,5),(0,0),(6,6),(1,1)}…
Consider the following relation for published books:BOOK (Book_title, Authorname, Book_type, Listprice, Author_affil, Publisher)Author_affil refers to the affiliation of the author. Suppose the following dependencies exist:Book_title → Publisher, Book_typeBook_type → ListpriceAuthor_name → Author-affil (a) What normal form is the relation in? Explain your answer.(b) Apply normalization until you cannot decompose the relations further. State the reasons behind eachdecomposition.
Write a relational calculus based on the following relations: Professor (ProfID, ProfName, DeptNo) Course (CrsID, CrsName, Credits, DeptNo, ProfID) Department (DeptNo, DeptName) Use tuple relational calculus; 1. List all professor names, who are the owner of the courses named, "Database Design". 2. List all professor names who teach the 3-credit courses provided by the department named "Information Technology".
Write a relational calculus based on the following relations: Professor (ProfID, ProfName, DeptNo) Course (CrsID, CrsName, Credits, DeptNo, ProfID) Department (DeptNo, DeptName) Use domain relational calculus; 1. List all course names provided by the department named "Information Technology". 2. List all professor information who work the department named "Information Technology".
Ņ 5. Table 4-5 shows a shipping manifest. Your assignment is as follows: a. Draw a relational schema and diagram the functional dependencies in the relation. b. In what normal form is this relation? + c. Decompose MANIFEST into a set of 3NF relations. d. Draw a relational schema for your 3NF relations and show the referential integrity constraints. TABLE 4-5 Shipping Manifest 00-0001 Boston Brazil 39 Shipment ID: Origin: Destination: Ship Number: Item Number 3223 3297 Type BM BM Description Concrete Form Steel Beam Shipment Date: Expected Arrival: Captain: Weight 500 87 Quantity 100 2,000 Shipment Total: 01/10/2010 01/14/2010 002-15 Henry Moore TOTALWEIGHT 50,000 174,000 224,000
Given the following relational schema (Hint: the bold text represents a primary key), Department (dept_code, dept_name, emp_num, dept_address, dept_ext)Professor(emp_num, emp_fname, emp_lname, emp_dob, dept_code, prof_ext) Write a relation algebra to find all professor names (firstname and lastname) who are in the department named "Biology"
Consider the following relations: Employee(E_id: integer, E_name: string, Age: integer, Salary: real) Works(E_id: integer, Dep_id: integer, Start_date: date) Department(Dep_id: integer, Dep_name: string, Budget: real, Manager_E_id: integer) What referential integrity constraints exist between these relations? What are the options for enforcing these constraints when a user attempts to delete a Dept tuple?
1.Consider the relation schema given in below fig. Design and draw an ER diagram that capture the information of this schema.(Analyse)(Apr/May 2017)Employee(empno,name,office,age)Books(isbn,title,authors,publisher)Loan(empno,isbn,date)Write the following queries in relational algebra and SQL.Find the names of employees who have borrowed a book published by McGraw-Hill.Find the names of employees who have borrowed all books published by McGraw-Hill.
convert it to relations that are normalization
Question 6. Consider the following relation: Invoice(Order_ID, Order_Date, Customer_ID, Customer_Name,Customer_Address,Product_ID, Product_Desc, Price, Order_Quantity) FD1 - Order_ID -> Order_Date, Customer_ID, Customer_Name, Customer_Address FD2 - Customer_ID -> Customer_Name, Customer_Address FD3 - Product_ID -> Product_Description, Price FD4 - Order_ID, Product_ID -> Order_Quantity What are the candidate keys of this relation? Give reasons to explain if this is in 1NF or not. If not, decompose it to 1NF. Give reasons to explain if this is in 2NF or not. If not, decompose it to 2NF. Give reasons to explain if this is in 3NF or not. If not, decompose it to 3NF.
Consider the following relation: Invoice(Order_ID, Order_Date, Customer_ID, Customer_Name,Customer_Address,Product_ID, Product_Desc, Price, Order_Quantity) FD1 - Order_ID -> Order_Date, Customer_ID, Customer_Name, Customer_Address FD2 - Customer_ID -> Customer_Name, Customer_Address FD3 - Product_ID -> Product_Description, Price FD4 - Order_ID, Product_ID -> Order_Quantity What are the candidate keys of this relation? Give reasons to explain if this is in 1NF or not. If not, decompose it to 1NF. Give reasons to explain if this is in 2NF or not. If not, decompose it to 2NF. Give reasons to explain if this is in 3NF or not. If not, decompose it to 3NF.
Answer the following questions based on the functional dependencies for a relation as provided below: Aircraft(AC_Num, AC_Model, AC_Seats, AC_Rent_Charge, Model_Code, Model_Manufacturer, Model_Seates, Model_RentCharge) Primary key: AC_Num Functional dependencies: AC_Num->AC_Model, AC_Seats, AC_Rent_Charge, Model_Code, Model_Manufacturer, Model_Seates, Model_RentCharge Model_Code-> Model_Manufacturer, Model_Seates, Model_RentCharge questions: Does this relation contain a ttransitive dependency? if so what it is? Create new relations to remove transitive dependency. Make sure to include primary and foreign keys for all relations.