Question

Here is a schema for POP:

JOURNALS(j_num, j_name, j_subject_area, chief_editor_r_ssn[1])

chief_editor_rssn FK REF Researchers

ISSUES(j_num, i_num, i_date, i_num_pages)

j_num FK REF journals

RESEARCHERS(r_ssn, r_name, r_address, r_phone, r_institution)

ARTICLES(art_num, art_title, art_area_topic, art_abstract, A_submittedJ_j_num, A_publishedI_ j_num, ApublishedI_i_num)

A_submittedJ_j_num FK REF journals

(A_publishedI_ j_num, ApublishedI_i_num) FK REF issues

AREA_EDITS(j_num, r_ssn)

j_num FK REF journals

r_ssn FK REF researchers

WRITES(r_ssn, art_num)

r_ssn FK REF researchers

art_num FK REF articles

REVIEWS(r_ssn, art_num)

r_ssn FK REF researchers

art_num FK REF articles

RESEARCH_SPECIALTIES(r_spec_name)

RRS(r_ssn, r_spec_name)

r_ssn FK REF researchers

spec_name FK REF research_specialties

Please formulate the following queries using relational algebra:

1. The names of all researchers from University of Tulsa.

2. The names of all researchers from TU with an “MIS” specialty.

3. The names and addresses of all researchers who are chief editors.

4. The names of all journals with subject area = “MIS”.

5. The names and addresses of all researchers who have written or reviewed an article.

6. The titles of all articles published in “Journal of the Association of Information Systems”.

7. The number of pages of the issue that contains article number 5471.

8. The specialties of all researchers named “John Von Neumann” who are NOT at research institution “Princeton University”.

[1] The chief_editor_r_ssn is the r_ssn of the researcher who is chief editor for the journal.

Answer 1

1.

∏_{r_name} ( _{r_institution="University of Tulsa​"} (RESEARCHERS))

2.

∏_{r_name} ( _{r_institution="TU" and r_spec_name​ ="MIS"} (RESEARCHERS ⋈ RRS ⋈ RESEARCH_SPECIALTIES))

3.

∏_{r_name,r_address} ( _{chief_editor_r_ssn= r_ssn}(RESEARCHERS ⋈ JOURNALS))

4.

∏_{j_name}( _{j_subject_area​="MIS"}( JOURNALS))

5.

∏_{r_name,r_address} (RESEARCHERS ⋈ REVIEWS) U ∏_{r_name,r_address} (RESEARCHERS ⋈ WRITES)

U is for union

6.

∏_{art_title} (_{art_area_topic​ = “Journal of the Association of Information Systems”​} (ARTICLES))

7.

∏_{i_num_pages} (_{art_num​= 5471​} (ISSUES ⋈ ARTICLES))

8.

∏_{r_spec_name​} ( _{r_name =“John Von Neumann”​​ and r_institution​ != “Princeton University”}(RESEARCH_SPECIALTIES ⋈ RRS ⋈ RESEARCHERS ))