Assignment

Queries, Functions and Triggers

Database Systems

Aims

The aims of this assignment are to:

formulate SQL queries;
populate an RDBMS with a real dataset, and analyse the data;
design test data for testing SQL queries;
create SQL views;
understand the limitations of SQL queries;
create SQL functions (when needed); and
create triggers.

Description

In this assignment, the schema for a simple publication database is provided to you. The file is: pub-schema.sql. Based on the provided schema, you are required to answer the following questions by formulating SQL queries. You may create SQL functions or PLpgSQL to help you, if and only if the standard SQL query language is not expressive and powerful enough to satisfy a particular question. To enable auto-marking, your queries should be formulated as SQL views, using the view names and attribute names provided. Since this assignment is auto-marked, using different view names and/or attribute names from the provided names may result in losing part or all of the marks for the questions involved. If any answer requires you to output a calculated value that has decimals, you should round it to an integer.

1. List all the editors.
create or replace view Q1(Name) as ...

2. List all the editors that have authored a paper.
create or replace view Q2(Name) as ...

3. List all the editors that have authored a paper in the proceeding that they have edited.
create or replace view Q3(Name) as ...

4. For all editors that have authored a paper in a proceeding that they have edited, list the title of those papers.
create or replace view Q4(Title) as ...

5. Find the title of all papers authored by an author with last name "Clark".
create or replace view Q5(Title) as ...

6. List the total number of papers published in each year, ordered by year in ascending order. Do not include papers with an unknown year of publication. Also do not include years with no publication.
create or replace view Q6(Year, Total) as ...

7. Find the most common publisher(s) (the name). (i.e., the publisher that has published the maximum total number of papers in the database).
create or replace view Q7(Name) as ...

8. Find the author(s) that co-authors the most papers (output the name). If there is more than one author with the same maximum number of co-authorships, output all of them.
create or replace view Q8(Name) as ...

9. Find all the author names that never co-author (i.e., always published a paper as a sole author).
create or replace view Q9(Name) as ...

10. For each author, list their total number of co-authors, ordered by the total number of co-authors in descending order, followed by author names in ascending order. For authors that never co-author, their total is 0. For example, assume John has written 2 papers so far: one with Jane, Peter; and one with Jane, David. Then the total number of co-authors for John is 3. In other words, it is the number of people that have written papers with John.
create or replace view Q10(Name, Total) as ...

11. Find all the author names that have never co-authored with any co-author of Richard (i.e. Richard is the author's first name), nor co-authored with Richard himself.
create or replace view Q11(Name) as ...

12. Output all the authors that have co-authored with or are indirectly linked to Richard (i.e. Richard is the author's first name). We define that a is indirectly linked to b if there exists a C p1, p1 C p2,..., pn C b, where x C y means x is co-authored with y.
create or replace view Q12(Name) as ...

13. Output the authors name, their total number of publications, the first year they published, and the last year they published. Your output should be ordered by the total number of publications in descending order and then by name in ascending order. If none of their publications have year information, the word "unknown" should be output for both first and last years of their publications.
create or replace view Q13(Author, Total, FirstYear, LastYear) as ...

14. Suppose that all papers that are in the database research area either contain the word or substring "data" (case insensitive) in their title or in a proceeding that contains the word or substring "data". Find the number of authors that are in the database research area. (We only count the number of authors and will not include an editor that has never published a paper in the database research area).
create or replace view Q14(Total) as ...

15. Output the following information for all proceedings: editor name, title, publisher name, year, total number of papers in the proceeding. Your output should be ordered by the total number of papers in the proceeding in descending order, then by the year in ascending order, then by the title in ascending order.
create or replace view Q15(EditorName, Title, PublisherName, Year, Total) as ...

16. Output the author names that have never co-authored (i.e., always published a paper as a sole author) nor edited a proceeding.
create or replace view Q16(Name) as ...

17. Output the author name, and the total number of proceedings in which the author has at least one paper published, ordered by the total number of proceedings in descending order, and then by the author name in ascending order.
create or replace view Q17(Name, Total) as ...

18. Count the number of publications per author and output the minimum, average and maximum count per author for the database. Do not include papers that are not published in any proceedings.
create or replace view Q18(MinPub, AvgPub, MaxPub) as ...

19. Count the number of publications per proceeding and output the minimum, average and maximum count per proceeding for the database.
create or replace view Q19(MinPub, AvgPub, MaxPub) as ...

20. Create a trigger on RelationPersonInProceeding, to check and disallow any insert or update of a paper in the RelationPersonInProceeding table from an author that is also the editor of the proceeding in which the paper has published.

21. Create a trigger on Proceeding to check and disallow any insert or update of a proceeding in the Proceeding table with an editor that is also the author of at least one of the papers in the proceeding.

22. Create a trigger on InProceeding to check and disallow any insert or update of a proceeding in the InProceeding table with an editor of the proceeding that is also the author of at least one of the papers in the proceeding.