Assignment
Case Study
The local Monash Municipality maintains several libraries for its residents across the municipality.
For each branch, Monash Library Services assigns a branch code (an incremental number for each branch with the first branch using a code of 10). The branch name, address and contact phone number are also recorded. Each branch is assigned a manager and has a unique phone number.
Due to the small size of some of the branches, a particular manager may manage several branches. Each manager is assigned a manager id. Monash Library Services record a manager's name and contact phone number.
Monash Library Services maintain records of current loans of books to borrowers.
Each borrower is identified by a unique borrower number. When a borrower first registers to borrow books, the branch where they register is recorded as their home branch. The name and address of each borrower is held so that communications, such as overdue loan reminders, can be sent when necessary.
The information held about a book includes its Dewey Decimal call no - this call no is used to identify a particular books details. The title, author's name/s, publisher's name, publication year, purchase price, classification (Reference or Fiction), number of pages and edition, if applicable, are recorded. Each publisher can be assumed to have a unique name. A given book may be written by a number of different authors, however, the library regards a book as only being published by a single publisher. The library assigns its own unique in-house numerical codes to identify authors and publishers.
A book may cover a number of different subjects which the library wishes to record so that borrowers can use an online catalogue system to select books by subject as well as title and author's name.
Branches hold copies of a book - each copy is the property of a particular branch and is identified by the branch number and a branch assigned local id number (these id numbers are repeated at each branch). Some book copies are placed on counter reserve, and are not available for loan - they may only be used in the library. A flag is added to a book copy to indicate if it is on counter reserve or not. There may also be other copies of the same title which are available for normal loan.
When a book copy is borrowed (goes out on loan), the return date is recorded. A record of all loans which take place is maintained. When a book is returned from a loan its actual return date is recorded. Each book copy can be loaned for exactly 4 weeks and must then be renewed to avoid a fine.
Borrowers may reserve books currently out on loan. The date and time on which the reserve was placed are recorded. A given book may be reserved by several borrowers, the book is made available based on the order in which the reserve was placed by the borrower.
When a borrower returns a book, they may if they wish renew their loan and take the book out for a further loan period provided the book has not been reserved by another borrower, the renewal is simply treated as a new loan for that borrower. Books must be returned to the branch from which they were borrowed (the branch owning the book copy).
In completing this assignment you are not permitted to manually:
- lookup a value in the database, obtain its primary key or highest or lowest value in a column, or
- calculate values external to the database eg. on a calculator and use such values in your answers.
You may ONLY use the data provided in the text of the questions.
As an example, say you were asked to find the name of the publisher for the book titled "Harry Potter and the Cursed Child" - you cannot look in the BOOK_DETAIL table and find the pub_id for the publisher of this book (say 100), and then use the value 100 directly to find the pub_name from the PUBLISHER table. Here you can only use the text you were given "Harry Potter and the Cursed Child".
Your answers must recognise the fact that you have been given, with the supplied insert file, only a small sample snapshot of a multiuser database, as such you must operate on the basis that there will be more data in all of the tables of the database than you have been given. Your answers must work regardless of the extra quantity of this extra "real" data and the fact that multiple users will be operating in the tables at the same time. You must take this aspect into consideration when writing SQL statements.
TASK 1: Data Definition
For this task you are required to complete the following:
Add to your solutions script, the CREATE TABLE and CONSTRAINT definitions which are missing from the FIT9132_2018S2_A2_Schema_Start.sql script. You MUST use the relation and attribute names shown in the data model above to name tables and attributes which you add.
Add the full set of DROP TABLE statements to your solutions script. In completing this section you must not use the CASCADE CONSTRAINTS clause as part of your DROP TABLE statement (you should include the PURGE clause).
Before proceeding with Task 2, you must run the file FIT9132_2018S2_A2_Schema_Start.sql (which must not be altered in any way) followed by the extra definitions that you added in 1.1 above.
TASK 2: Data Manipulation
Run the script FIT9132_2018S2_A2_Insert_Start.sql to add some initial data into the tables you have created so far.
For this task you are required to complete the following sub-tasks in the same order they have mentioned:
MonLib has just purchased its first 3 copies of a recently released edition of a book. Readers of this book will learn about the subjects "Database Design" and "Database Management".
Some of the details of the new book are: Call Number: 005.74 C822D 2018
Title: Database Systems: Design, Implementation, and Management
Publication Year: 2018
Edition: 13 Publisher: Cengage
Authors: Carlos CORONEL (author_id = 1 ) and Steven MORRIS (author_id = 2)
Price: $120
You may make up any other reasonable data values you need to be able to add this book.
Each of the 3 MonLib branches listed below will get a single copy of this book, the book will be available for borrowing (ie not on counter reserve) at each branch:
Caulfield (Ph: 8888888881) Glen Huntly (Ph: 8888888882) Carnegie (Ph: 8888888883)
Your are required to treat this add of the book details and the three copies as a single transaction.
An Oracle sequence is to be implemented in the database for the subsequent insertion of records into the database for BORROWER table.
Provide the CREATE SEQUENCE statement to create a sequence which could be used to provide primary key values for the BORROWER table. The sequence should start at 10 and increment by 1.
Provide the DROP SEQUENCE statement for the sequence object you have created in question 2.2 above.
TASK 3:
The sequence created in task 2 must be used to insert data into the database for the task 3 questions. For these questions you may only use the data supplied in this task.
You must correctly manage transactions with these tasks.
For this task you are required to complete the following sub-tasks in the same order they have been mentioned:
Today is 20th September, 2018, add a new borrower in the database. Some of the details of the new borrower are:
Name: Ada LOVELACE
Home Branch: Caulfield (Ph: 8888888881)
You may make up any other reasonable data values you need to be able to add this borrower.
Immediately after becoming a member, at 4PM, Ada places a reservation on a book at the Carnegie branch (Ph: 8888888883). Some of the details of the book that Ada has placed a reservation on are:
Call Number: 005.74 C822D 2018
Title: Database Systems: Design, Implementation, and Management Publication Year: 2018
Edition: 13
You may assume:
- MonLib has not purchased any further copies of this book, beyond those which you inserted in Task 2.1
- that nobody has become a member of the library between Ada becoming a member and this reservation.
After 7 days from reserving the book, Ada receives a notification from the Carnegie library that the book she had placed reservation on is available. Ada is very excited about the book being available as she wants to do very well in FIT9132 unit that she is currently studying at Monash University. Ada goes to the library and borrows the book at 2 PM on the same day of receiving the notification.
You may assume that there is no other borrower named Ada Lovelace.
TASK 4
For this task you are required to complete the following sub-tasks.
After using the system for some time, MonLib has realised that it is necessary to Record whether a book is damaged (D) or lost (L). If the book is not damaged or lost,then it is good (G) which means, it can be loaned. The value cannot be left empty for this. Change the "live" database and add this required information for all the books currently in the database. You may assume that condition of all existing books will be recorded as being good. The information can be updated later, if need be.
Allow borrowers to be able to return the books they have loaned to any library branch as MonLib is getting a number of requests regarding this from borrowers. As part of this process MonLib wishes to record which branch a particular loan is returned to. Change the "live" database and add this required information for all the loans currently in the database. For all completed loans, to this time, books were returned at the same branch from where those were loaned.
Some of the MonLib branches have become very large and it is difficult for a single manager to look after all aspects of the branch. For this reason MonLib are intending to appoint two managers for the larger branches starting in the new year - one manager for the Fiction collection and another for the Non-Fiction collection. The branches which continue to have one manager will ask this manager to manage the branches Full collection. The number of branches which will require two managers is quite small (around 10% of the total MonLib branches). Change the "live" database to allow monLib the option of appointing two managers to a branch and record, for all managers, which collection/s they are managing.
In the new year, since the Carnegie branch (Ph: 8888888883) has a huge collection of books in comparison to the Caulfield and Glen Huntly branches, Robert (Manager id: 1) who is currently managing the Caulfield branch (Ph: 8888888881) has been asked to manage the Fiction collection of the Carnegie branch, as well as the full collection at the Caulfield branch. Thabie (Manager id: 2) who is currently managing the Glen Huntly branch (Ph: 8888888882) has been asked to manage the Non-Fiction collection of Carnegie branch, as well as the full collection at the Glen Huntly branch. Write the code to implement these changes.
Attachment:- Introduction to Databases.rar