Ask DBMS Expert


Home >> DBMS

SQL and PLSQL

Before you start this coursework you must have finished the SQL exercises on the Bus/ Depots / Drivers Database.
Important note - check that you have not, in preceding modules, created tables with the same names as in this exercise. If any of these table names already exist in your area in Oracle then you should either delete them (using ‘drop table xyz cascade constraints’) or use different names for tables in the coursework database. There might also be problems if constraint primary key and foreign key names are the same so it is advisable to delete all tables created in preceding modules. After creating and loading tables check that all data is present.
Employ Oracle SQL*PLUS to answer the following queries. It is recommended which you either use Notepad or similar (not Word) to initially create the queries.

The Children’s Library Database

This situation relates to a database that is to be set for a library that lends books to children:

A Children's Library keeps information on books held, borrowers who borrow these books and the loans of these books, which the borrowers make. In addition information is held regarding the authors and publishers of these books.

The information is to be held in a Relational database and implemented using Oracle. Two files are available on Oasis Plus for you to make the relations and to load the relations with sample data.  The tables in the database are shown. The identifier attribute(s) are underlined, foreign keys are asterisked *.

Table                                  Attributes
Borrower    borId, borName, borAddress, borMaxbooks

BookTitle    isbn, btName, datePublished, pubId*, ageLower ageUpper, value

BookCopy    bcId, isbn*, dateAcquired, dateDestroyed

Loan                borId*, bcId*, dateOut, dateDue, dateBack

Publisher    pubId, pubName, pubAddress

Author                   authorId, authorName

Authorship    authorId*, isbn*

Note that the BookCopy table contains information on the physical books kept in the library whereas the BookTitle table contains information on a specific publication of a book. For ex, there are two copies of 'Winnie the Pooh', with bcId of 101 and 102 with an ISBN (International Standard Book Number) of 10: 0786843411.  The BookTitle table contain information regarding the recommended age range for each book (ageLower and ageUpper). A book might have a number of authors and this is indicated in the Authorship table.

The characteristic borMaxbooks points to the maximum number of books that a borrower can borrow at a time. Also, a book, which is still out on loan, will have a blank dateBack field in the loan table.

As copies of books become old, damaged and dirty the books are deleted from the library and destroyed. Destroyed book copies have a date to indicate this otherwise the date is null.

It is essential which you draw an Entity-Relationship diagram mapped to a relational database, showing the entities and relationships included. Suppose that features with the same name are based on the same domain. Do not hand in this diagram – it is not assessed.

Use join conditions to answer the queries in this problems 1 and 2

Q1. Find out borrowers (by name) who have ever borrowed a book written by the Phillip Pullman.

Q2. Find out borrowers (by name) who have currently on loan a book written by the Phillip Pullman

Q3. Give the ISBN and title of every book and the number of copies currently on the loan listing most popular first.

Q4 Display the names of borrowers who have never borrowed books published by the publisher Mammoth.

Q5. Give the titles of books that have the same value as ‘Northern Lights’. Do not comprise Northern Lights in your output.

Q6 List out all book titles and the total value of the book copies for a particular title that are currently on loan where total value is greater than or equal to £20.

Q7. List out the borrowers (by number and name, once only), who have currently on loan a book that has the same title as a book that Jenny Wren has borrowed. Do not comprise Jenny Wren in the output.

Q8. Provide the name of any borrower who has ever borrowed all the book copies in the library. It is possible that no-one has borrowed all the copies. (Hint: this is an ex of relational algebra divide).

Q9. Run the make View statement and the query below and show the results. Give the English meaning of the query (as in problems 1-7 above):

Create view borrowedBooks

As     Select l.borId, bc.isbn, bc.bcId
        From Loan l, bookCopy bc
        Where l.bcId = bc.bcId;

Select b.borName
From Borrower b
Where not exists
    (select *
    from bookTitle bt, Publisher p
where bt.pubId=p.pubId
and pubName='Puffin'
    and not exists
        (select *
        from borrowedBooks bb
        where bb.borId = b.borId
        and bb.isbn = bt.isbn));

Q10. Run the query below and demonstrate the results. Provide the English meaning of the query (as in problems 1-7 above):

Select distinct                   b.borName
From  Borrower           b, Loan l, Book_copy bc,
Author                              a1, Author a2,
Authorship                  ash1, Authorship ash2
Where                            b.borId = l.borId
And                                l.bcId = bc.bcId
And                        bc.isbn = ash1.isbn
And                    bc.isbn = ash2.isbn
And               ash1.authorId = a1.authorId
And                ash2.authorId = a2.authorId
And                a1.authorName = 'Janet Ahlberg'
 And              a2.authorName = 'Allan Ahlberg';

Triggers and stored procedures

Don’t forget to type the command ‘set serveroutput on’ before implementing the code in this section. If you are forced out of the SQL*PLUS don’t forget to retype this command.

Before you start this coursework you must have completed the PL/SQL exercises on the Bus/Drivers/Depots Database.

1. prepare down a PL/SQL block which comprises a procedure getTotalLoans which accepts a ISBN and returns the title and number of copies currently on loan for that book. (See problem 3 of the SQL queries part of the coursework.) The main block must call the procedure with an ISBN of 4 and output the book title and number of copies.

2. prepare down a PL/SQL block, which displays for all book titles held in the library (not those destroyed). You should include the ISBN, the book title, publisher name and the number of copies of each held (whether on loan or not). Use a cursor to do this problem.

3. prepare down a stored function called getPublisher. This function takes up as input an ISBN for a book and returns the publisher name.
Call the function from within an SQL statement to display the publisher for book title ‘The Twits’

4. Make a trigger ‘checkRecommendedAge’ to enforce the (harsh) constraint that children are only permitted to borrow books deemed suitable for their age. The trigger fires whenever there is a new loan and outputs an error message whenever an attempt is made to borrow a book where the borrower’s real age is not in the lower to upper age range for the book. Ignore the fact that some of the data already in the database may violate the constraint.

Show what happens when you try to insert following records into the Loan table using the following statements:

Insert into Loan values (001,144,'29-aug-2008','18-sep-2008','');
Insert into Loan values (001,101,'29-aug-2008','18-sep-2008','');
Insert into Loan values (001,120,'29-aug-2008','18-sep-2008','');
For testing purposes the following commands (which set the database back to its original state) might be useful:
delete from Loan where borId=001 and bcId=144 and dateOut='29-aug-2008';
delete from Loan where borId=001 and bcId=101 and dateOut='29-aug-2008';
delete from Loan where borId=001 and bcId=120 and dateOut='29-aug-2008';
Note well: read following page before handing in your coursework

- For SQL coding, indication of the problem a particular solution refers to, written as a comment e.g. /* problem 1 List the borrowers……*/

- For PLSQL coding  the problem number is required only

- The SQL and PLSQL code in its entirety, formatted with indentations so it is easy to read (see code in text book). Please use a reasonable size of font.

- SQL*Plus Screen print to verify that you have executed the code

- The resulting table created from the SQL execution or results from your PLSQL execution.  You should copy and paste the results from SQL*Plus. If the output is very long, hand in the first page only. Note that the correct solutions generate small tables.  Do not print/hand in

DBMS, Programming

  • Category:- DBMS
  • Reference No.:- M9954

Have any Question?


Related Questions in DBMS

Data mining assignment -in this assignment you are asked to

Data Mining Assignment - In this assignment you are asked to explore the use of neural networks for classification and numeric prediction. You are also asked to carry out a data mining investigation on a real-world data ...

Sql query assignment -for this assignment you are to write

SQL Query Assignment - For this assignment you are to write your answers in a word document. This assignment is in three parts: Part A (reporting queries), Part B (query performance), Part C (query design). For this assi ...

The groceries datasetimagine 10000 receipts sitting on your

The groceries Dataset Imagine 10000 receipts sitting on your table. Each receipt represents a transaction with items that were purchased. The receipt is a representation of stuff that went into a customer's basket. That ...

You are in a real estate business renting apartments to

You are in a real estate business renting apartments to customers. Your job is to define an appropriate schema using SQL DDL in MySQL. The relations are Property(Id, Address, NumberOfUnits), Unit(ApartmentNumber, Propert ...

Objectivethe objective of this lab is to be familiar with a

OBJECTIVE: The objective of this lab is to be familiar with a process in big data modeling. You're required to produce three big data models using the MS PowerPoint software. This tool is available on UMUC Virtual Deskto ...

The relation memberstudentid organizationid roleid stores

The relation Member(StudentId, OrganizationId, RoleId) stores the membership information of student joining organization. For example, ('S1', 'O2', 'R3') indicates that student with Id 'S1' joined the organization with i ...

Relational database exerciseyou have been assigned to a new

Relational Database Exercise: You have been assigned to a new development team. A client is requesting a relational database system to manage their present store with the anticipation of adding more stores in the future. ...

Relational database design a given the following business

Relational Database Design A) Given the following business rules, identify entity types, attributes (at least two attributes for each entity, including the primary key) and relationships, and then draw an Entity-Relation ...

We can represent a data set as a collection of object nodes

We can represent a data set as a collection of object nodes and a collection of attribute nodes, where there is a link between each object and each attribute, and where the weight of that link is the value of the object ...

Data model development and implementationpurpose of the

Data model development and implementation Purpose of the assessment (with ULO Mapping) The purpose of this assignment is to develop data models and map Database System into a standard development environment to gain unde ...

  • 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