Write a procedure called listoverduebooks

Assignment Help Database Management System

Reference no: EM132667076

COMP2350 Database Systems - Macquarie University

Database Programming and Implementation

Task 1
Create the tables listed in the relational data model as shown in Figure 1 below, by running the DDL script provided in the 'Assignment 3' folder. Then insert sample records to the tables by running the provided DML script. Verify that the tables are created and populated as intended.

1174_Relational Data Model.jpg

Figure 1 : Relational Data Model

Task 2
In this task, you will write a BEFORE INSERT trigger called TR_BORROWING which fires when a member wishes to borrow a book from a branch (that is, when a new row is to be inserted into the borrowedby table).
In the trigger body, the following checks would need to be done first:
• The member's status is "REGULAR".
• The branch is holding copies for borrowing at the branch (i.e., OnLoan < InStock).
• The member has not borrowed any copies of this book from any branch.
• The return due date of the borrowed book is normally in exactly 3 weeks from the borrowed date, however, it cannot be past the membership expiry date.
If the above checks pass, then the INSERT statement is allowed to proceed, and the OnLoan
value in the holding table is also updated (+1).

Task 3
In this task, you will write a procedure called ListOverdueBooks which takes a member ID and lists all the books borrowed by the member whose return is overdue. Furthermore, if the number of such overdue books exceeds a threshold number (say 2 in this case to make it simple) the member is suspended from borrowing books until further notice by SL.
Note that the procedure needs to be implemented using a cursor FOR LOOP.

Task 4

This task involves testing the code developed in Tasks 2 & 3.

First you are required to test the programs you wrote against the sample data provided. The tests to be carried out, in the order given, is provided in the supplied file: test_script.sql. These tests constitute a minimal test against a very small number of records. Look at this file carefully to understand what is being tested and what should be the expected result. Note the roles of setting autocommit off and the rollback statements.

Next you carry out a more extensive test by testing the programs against a larger set of records that are designed to easily expose any flaws in your programs. You do that by deleting records, adding records, or modifying the records in other ways, and then calling different procedures. You accordingly modify the file test_script.sql, and save it as
<yourLastname_yourFirstname>_test_script.sql, and submit among other files.

3 Report Specification
You will also prepare and submit a report (in the PDF format). A word file template for this purpose will be provided which you will complete, convert to pdf, and submit. The file you submit would be named: <yourLastname_yourFirstname>_report.pdf.
Your report should have the following sections:

1. Initial State of the database. Paste to the word file the screen shots showing the provided sample data in the tables.

2. Stored Programs. Paste into this section the programs you wrote (the sql file
<yourLastname_yourFirstname>_programs.sql that you prepared).

3. Required Testing against Sample Database. Paste into this section the initial tests you ran (one by one) and then the corresponding results as screenshots. The provided script file lists what tests to run, how, and in which order.

4. More Extensive Testing. Explain what sort of changes you are going to make to which tables, what tests you are going to run, and why. Copy and paste from your file
<yourLastname_yourFirstname>_test_script.sql the DML statements you used to modify the tables followed by the screenshots of the records in those tables; then copy and paste the procedure calls you made, and the screenshots of the records in the relevant tables (or the error messages).

5. Notes (optional). In this optional section, you might wish to note anything, such as whether you faced any particular difficulty in completing any of these tasks, the nature and extent of any help you received from anyone, and why.
Remember to convert this file to pdf and submit only the pdf file.

Attachment:- Database Programming and Implementation.rar

Reference no: EM132667076

Questions Cloud

Focus on the issuance of engineering : Detailed design in which you focus on the issuance of engineering documents leading to the manufacturing of one piece of equipment. For example the reactor

How the outcomes of one system level effect the other level : Describe the selected problem from two of the three systems levels (micro-, meso-, and macro). Explain how the outcomes of one system level effect the other.

Compute the fixed amount of the production cost : Compute the total production cost using 17, 500 machine hours. Compute the fixed amount of the production cost. Compute the variable rate per machine hour

BSBPMG522 Undertake Project Work Assignment : BSBPMG522 Undertake Project Work Assignment Help and Solution - Newton College, Australia - Assessment Writing Service

Write a procedure called listoverduebooks : Write a BEFORE INSERT trigger called TR_BORROWING which fires when a member wishes to borrow a book from a branch (that is, when a new row is to be inserted

What do you value most about your learning the week : What do you value most about your learning this week? What else do you need to explore to further grow as a DNP practice scholar?

Find what amount of custodial supplies expense would expect : Find what amount of custodial supplies expense would you expect to be incurred at an occupancy level of 14,800 occupancy-days?

Define archaeology : Define archaeology. How does archaeology inform us of our past? What does it mean to be "transitional" in the evolutionary sense of the word? Discuss examples.

What amount should reported as current liability for deposit : The lessor appropriately classified the lease as an operating lease. What amount should be reported as current liability for deposit?

User Account

All Pages