Reference no: EM132553280

CDB3034 - Database Programming Assignment - UOW Malaysia KDU Penang University College, Malaysia

Overview - In this assignment, you are required to use the new database called Publication. The database consists of seven schemes namely author, publication, wrote, proceedings, journal, article and book.

Programming Requirements:

You must NOT use any implicit cursors, table joins, sub-queries, set operators, group functions or SQL functions (such as COUNT) in the PL/SQL functions.

Use explicit cursor in retrieving multiple rows of records.

Task 1 -

Write a PL/SQL procedure called print_publication that print a list of publication records for each publication by an author name as parameter input. You should sort the name of the author for the publication in ascending order and follow by the year of publication (earliest first). The sample output format is as follows:

Pubid: pubid

Type : {book | journal | proceedings | article}

Authors: author-1, ... , author-k

Title: title

In addition, provide appropriate information for each particular type of publication. For example if the publication is under journal category, the following information should be printed:

Volume: volume

Number: number

Year: year

In the case of articles, the relevant publication details should also be printed out. The year of an article could be found in other relations such as book, journal and etc. Also a summary page should be printed as follows at the end of the output to summarize total publication for the author.

Proceedings: 1

Journal : 1

Article : 0

Book : 0

Total Publication: 2

Call the procedure from an anonymous block and capture the results. Appropriate exception handling should be performed for the above cases by sending the error message to the standard output

Task 2 -

a) Create a new table called publication_master. This table is used to consolidate the all 5 relations namely publication, proceedings, journal, book and article. Introduce a new column called type to specify the category of publication (either journal book or etc).

Each category of publication is having common or different types of details such as book has publisher and year and on the other hand journal has volume, number and year. Your new table should hold these values with common column names such as detail1, detail2, detail3, and detail4. However, you should display the columns with appropriate names programmatically using PLSQL in later process.

Write a PL/SQL stored procedure called merge_publication to transfer all the data from five respective tables into the publication_master table. This procedure should indicate how many records successfully posted into the master table upon execution to standard output. In addition, give any appropriate error message for any unsuccessful cases such as no publication details found in proceedings, journal, article, and book tables.

b) Based on the merge_publication table create in part 2a, create another PL/SQL stored procedure called print_article to print a list of publication records for each article appearing in a proceedings, journal or book which identified by a pubid supplied as input parameter. The articles should be displayed in ascending order on starting page number.

Call the procedures from an anonymous block and capture the results. Appropriate exception handling should be performed for the above cases by sending the error message to the standard output.

Task 3 -

You have been asked by the client to investigate the physical design of the publication database. Prepare a simple report (300-400 words) to review the design of the database and provide appropriate approaches/ways to improve database performance in accessing or searching the publication records. The report should contain a fully justified set of recommendations proposing an appropriate solution (e.g. using appropriate index in particular search column).

You should submit the following:

A complete source code (PL/SQL) in softcopy and hardcopy format. Save your solution according to the question number (e.g. q1.sql, q2a.sql ...etc)

Report:

Title page. Include the names and ID's.

Appropriate screen shots for all the test cases of question 1, question 2a and question

2b. A review report for question 3.

Conclusion

References

An appendix with

Program listings for question 1, question 2a, and question 2b.

The code should be well structured and self-documentation features such as sensible variable names and comments should be used.