Consider the following schema: Publisher (name, phone, address), PK: name. Book
ID: 672463 • Letter: C
Question
Consider the following schema:
Publisher (name, phone, address), PK: name.
Book (ISBN, title, year, published_by, previous_edition), PK: ISBN, FK: published_by refs Publisher, previous_edition refs Book.
Author (SSN, first_name, last_name, address, income), PK: SSN.
Write(aSSN, bISBN), PK: (aSSN, bISBN), FK: aSSN refs Author, bISBN refs Book.
Author_Editor(aeSSN, works_for), PK: aeSSN, FK: aeSSN refs Author, works_for refs Publisher.
Give relational algebraic expressions or SQL statements for the following plain English language queries based on the above schema.
Provide the ISBN of those books that have an author whose last name is Smith but do not have a coauthor whose last name is Doe.
Explanation / Answer
dom(Name)=Name dom(SSN)=SSN
dom(Phone)= phone dom(first_name)=first_name
dom(address)=address dom(last_name)=last_name
dom(address)=address
dom(income)=income
CREATE VIEW ISBN BOOKS
AS SELECT last_name ='Smith' FROM AUTHOR
WHERE last_name NOT 'Doe';