There are two schemas in this assignment and queries related to those schemas. You need to
answer all the questions.
• Schema 1 has 5 queries (40%) and 2 integrity constraint (10%).
• Schema 2 has 5 queries (50%) and no integrity constraint.
Common Instructions for both schema:
Write the queries below in relational algebra. There are several variations on relational algebra,
and different notations for the operations. You must use the same notation as we have used in
class and on the slides. You may use assignment, and the operators we have used in class: 𝜋,
σ, ⨝, ⨝ 𝜃, ×, ∩, ∪, −, ρ. Assume that all relations are sets (not bags), as we have done in class,
and do not use any of the extended relational algebra operations from Chapter 5 of the
textbook (for example, do not use the division operator). Some additional points to keep in mind:
• Do not make any assumptions about the data that are not enforced by the original
constraints given above, including the ones written in English. Your queries should work
for any database that satisfies those constraints.
• Assume that every tuple has a value for every attribute. For those of you who know some
SQL, in other words, there are no null values.
• Remember that the condition on a select operation may only examine the values of the
attributes in one tuple, not whole columns. In other words, to use a value (other than a
literal value such as 100 or “Adele”), you must get that value into the tuples that your select
• The condition on a select operation can use comparison operators (such as ≤ and ≥) and
Boolean operators (∨, ∧ and ¬). Simple arithmetic is also okay,
e.g., attribute1≤ attribute2 + 5000.
• Some relations in our schema have a date-time attribute. You may use comparison
operators on such values. You may refer to the year component of a date-time attribute
d using the notation d.year.
• You are encouraged to use assignment to define intermediate results.
• It’s a good idea to add commentary explaining what you’re doing. This way, even if your
final answer is not completely correct, you may receive part marks.
• The order of the columns in the result does not matter.
• When asked for a maximum or minimum, if there are ties, report all of them.
At least one of the queries cannot be expressed in the language that you are using. In those
cases, simply write “cannot be expressed”.
本网站支持淘宝 支付宝 微信支付 paypal等等交易。如果不放心可以用淘宝交易！
E-mail: firstname.lastname@example.org 微信:itcsdx