Ask PL-SQL Expert

Assignment - Queries, Functions and Triggers

Aim

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.

PL-SQL, Programming

  • Category:- PL-SQL
  • Reference No.:- M93104001
  • Price:- $80

Guranteed 48 Hours Delivery, In Price:- $80

Have any Question?


Related Questions in PL-SQL

Complete the following tasksin microsoft access create the

Complete the following tasks: In Microsoft Access, create the database and tables that you identified in W3 Assignment 2. In Microsoft Word, write the SQL statements to create the database and tables. Write SQL statement ...

Purpose of the assessment with ulo mapping the purpose of

Purpose of the assessment (with ULO Mapping) The purpose of this assignment is to develop skills in managing data in databases and to gain understanding of data model development and implementation using a commercially a ...

Continuing the project you have worked on in weeks 1-4 in

Continuing the project you have worked on in Weeks 1-4, in this final week, complete the following tasks: Refine your database and SQL statements by incorporating your instructor's feedback. Verify that the database comp ...

For this assignment you will be provided a database backup

For this assignment, you will be provided a database backup for a database called FinanceDB. You will have to restore this backup to your own version of SQL Server. All of the questions in this assignment relate to the F ...

Assignment - queries functions and triggersaimthe aims of

Assignment - Queries, Functions and Triggers Aim 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 SQ ...

  • 4,153,160 Questions Asked
  • 13,132 Experts
  • 2,558,936 Questions Answered

Ask Experts for help!!

Looking for Assignment Help?

Start excelling in your Courses, Get help with Assignment

Write us your full requirement for evaluation and you will receive response within 20 minutes turnaround time.

Ask Now Help with Problems, Get a Best Answer

Why might a bank avoid the use of interest rate swaps even

Why might a bank avoid the use of interest rate swaps, even when the institution is exposed to significant interest rate

Describe the difference between zero coupon bonds and

Describe the difference between zero coupon bonds and coupon bonds. Under what conditions will a coupon bond sell at a p

Compute the present value of an annuity of 880 per year

Compute the present value of an annuity of $ 880 per year for 16 years, given a discount rate of 6 percent per annum. As

Compute the present value of an 1150 payment made in ten

Compute the present value of an $1,150 payment made in ten years when the discount rate is 12 percent. (Do not round int

Compute the present value of an annuity of 699 per year

Compute the present value of an annuity of $ 699 per year for 19 years, given a discount rate of 6 percent per annum. As