Question

The relational schema for the Academics database is as follows: DEPARTMENT(deptnum, descrip, instname, deptname, state, postcode) ACADEMIC(acnum, deptnum*, famname, givename, initials, title) PAPER(panum, title)

AUTHOR(panum*, acnum*) FIELD(fieldnum, id, title) INTEREST(fieldnum*, acnum*, descrip)

Some notes on the Academics database:

• An academic department belongs to one institution (instname) and often has many academics. An

  academic only works for one department.

• Research papers (PAPER) are often authored by several academics, and of course an academic often

  writes several papers (AUTHOR).

• A research field (FIELD) often attracts many academics and an academic can have interest in several

  research fields (INTEREST).
  Primary keys are underlined and foreign keys are marked with *. You should download the SQL script for defining and populating the database academics.sql from Canvas (the Oracle section) and run academics.sql in your Oracle account to build the database.

  Write ONE SQL query for each of questions 1.3 through to 1.15. Your query must be formatted in such a way that it could be directly ported and run in Oracle SQL Developer. For example, if you use explanatory comments, they must be appropriately formatted SQL comments. Do not include the output of the query or the script used to create the tables.

  Assessment notes:

• Each question in this section is worth 1 point.

• For questions with “You must ...” or “You must not ...” requirements, queries failing to meet the

  requirements receive a maximum of 0.5 mark. For example, question 1.2 has “You must use a subquery”.

  A query not using the subquery operator can receive a maximum of 0.5 mark.

• Your query should not output duplicates, but use DISTINCT only if necessary.

• Queries are marked in terms of both correctness and efficiency. Unnecessary joins will incur a deduction.

  1.1. Explain the following query in English. A literal explanation will receive 0 marks.

  select givename, famname, instname from academic natural join department where acnum in

and

(select acnum
from author
where acnum not in

(select acnum from interest group by acnum))

deptNum in (select deptNum from academic

where deptname = ‘Computer Science’);

Page 3 of 8

1.2. The following SQL query is meant to output a list of papers (panum) with the total number of authors for each paper. It has syntax errors and logic errors. Explain the syntax and logic errors and give the correct query.

select PaNum, count(A1.AcNum) from Author A1, Author A2 where PaNum = A2.PaNum group by PaNum;

1.3. Find departments that have a description (descrip) available in the database. Return all details of these departments.

1.4. List the paper number and title of papers by the academic whose acnum is 100.

1.5. For each academic, give the acnum, givename, famname and the total number of papers s/he has written. Note that if an academic has not written any paper, his/her total should be zero. You can use JOIN operators such as NATURAL, JOIN ...ON.

1.6. The research field ID is a research field classification code representing classes for three “Levels”. These three Levels are separated by a “full stop” in a single string. For example the research field ID “B.1.6” represents that the research field belongs to Class “B” for Level one, Class “1” for Level two and Class “6” for Level three. For research fields in Class “1” for Level two, list the field IDs and the number of academics for each field ID.

1.7. Find departments where at least one academic does not have research interest, and list the deptnum, depntname, instname of these departments. Must use a subquery.

1.8. Output in alphabetical order the acnum, famname, givename, and department number (deptnum), and description (descrip) of authors whose family name starts with “C”.

1.9. List the fieldnum, title, and total number of interested academics (under the heading "NO. ACADEMICS INTERESTED") in each research field, in increasing order (i.e. ascending order) of fieldnum.

10. 1.10. List in alphabetical order the institution and name of departments where at least 10 academics have written papers.

11. 1.11. List the deptnum of departments whose postcodes are in the range 3000..3999 and that do not have any academics with the title of Professor (stored as “Prof” or “Prof.” in the database) , including departments that do not have any academics.

12. 1.12. Find the departments that have produced at least ten papers (that is, those departments where the sum of papers written by their academics is at least ten). Output their deptnum and deptname in ascending order.

13. 1.13. List the deptnum and deptname of departments whose academics have never written any papers.

1.14 List papers (panum) by academics with research interests in fields related to "data". You must use EXISTS. Note that “fields related to data” includes any occurrence of the four letters “data” within a field name, in any case.

1.15. The popularity of a field is measured by the number of interested academics. List details (filednum, ID and title) of the most popular field together with the total number of interested academics.

Answer 1

1.1.

It displays institution and other names of an Academic who is an Author and not have Interest in a field and is of Computer science department.

1.2.

Logical error : A paper can have more than 2 Authors. In that case considering only authors A1,A2 is not correct.

syntax error : PaNum = A2.PaNum , here PaNum is not specified whether it is attribute of A1 or A2.

Correct query syntax:

select PaNum, count(AcNum) from Author group by PaNum;

1.3.

Select * from Department where Descrip IS NOT NULL;

1.4.

Select panum, title from Paper where panum IN(Select panum from Author where acnum = 100) ;

Do ask if any doubt.