1

I'm doing some exercices waiting for the exam. I have some doubt.

RELATIONAL ALGEBRA

  • These are the table:

    Newspaper (Cod_n, Name_n, Publisher)

    Article (Cod_a, Title, Topic, Cod_N)

    Request : Find out the name of the newspapers that have published a topic deal with motorcycle at least

    My solution: π_name ( σ_topic = "motorcycle" (newspaper naturaljoin article))

    Book's solution: π_name (newspaper naturaljoin σ_topic = "motorcycle" (article))

Are the solution equivalent? Is the only difference between them the "cost of the operation" ?

  • The table is the same of the previous exercice.

    Request: Find out the name of the newspapers that have never published a topic deal with motorcycle

    My solution: π_name_n (newspaper naturaljoin (σ_topic notegual "motorcycle" (article)

    Book's solution: π_name_n(newspaper naturaljoin (π_cod_n(newspaper) - π_cod_n (σ_topic = "motorcycle" (Article)

Are the solution equivalent?

SQL

These are the table:

Student(matriculation_number, surname, name)

Exams(Student, subject, grades, date)

Request: Find out students (showing matriculation number) that have passed two exams after 1/1/2000 at least

My Solution:

select distinct matriculation_number

from student join exams on matriculation_number = student

where data > '2000-01-01'

EXCEPT

select distinct matriculation_number

from student join exams on matriculation_number = students

where data =< '2000-01-01'

Book's solution:

select distinct e1.student

from exams e1 join e2 on e1.student = e2.student

where e1.subject < > e2.subject

and e1.date > '2000-01-01'

and e2.date > '2000-01-01'

Is the solutions equivalent?

Thank you in advantage for your time. I'm sorry I don't know how to insert relational algebra's symbol.

  • 1
    Isn't this 3 questions? – Colin 't Hart Aug 20 '14 at 17:01
1

Your sql query is wrong:

- Assume there is a student s1 who has passed one exam 
  after '2000-01-01' and none before. 
  Your query results in {s1} - {} = {s1}. This will be a false positive.

- Assume there is a student s1 that passed three exams 
  after '2000-01-01' and one exam before. 
  Your query results in {s1} - {s1} = {}. This will be a false negative.

I'm having a bit of trouble reading your algebra expressions, but the first one looks ok. It should not matter that you do the selection (topic = "motorcycle") after the join instead of joining on the selection.

The second one can't be right. Assume there's a newspaper that published both an article on motorcycle and an article on something else. Your expression will pick the article on something else and therefore return that newspaper (incorrectly).

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.