Question

We are given a relational schema. Study the schema and understand the information contained in each column. It is helpful to visualize sample data in each column when you do this.

Now, look at each question, that represents a request for business information. Visualize what columns you want in the answer to the question.

Formulate a query in relational algebra that will answer the business information request.

Make sure you follow the syntax for relational algebra, as shown in class.

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. SELECT r_name

    FROM RESEARCHERS

    WHERE r_institution='University of Tulsa';

2. SELECT r_name

    FROM RESEARCHERS

    WHERE r_institution='University of Tulsa'

    AND (SELECT r_ssn FROM RRS WHERE r_spec_name= 'MIS');

3. SELECT r_name, r_address

    FROM RESEARCHERS

    WHERE r_ssn=(SELECT chief_editor_r_ssn FROM JOURNALS);

4. SELECT j_name

    FROM JOURNALS

    WHERE j_subject_area= 'MIS';

5. SELECT r_name, r_address

    FROM RESEARCHERS

    WHERE r_ssn=(SELECT r_ssn FROM WRITES OR SELECT r_ssn FROM REVIEWS);

6. SELECT art_title

    FROM ARTICLES

    WHERE A_publishedI_ j_num = (SELECT j_num FROM JOURNAMS WHERE j_name='Journal of the Association of Information Systems');

7. SELECT i_num_pages

    FROM ISSUES

    WHERE i_num= (SELECT ApublishedI_i_num FROM ARTICLES WHERE art_num ='5471');