解析FIT9132留学生、Monash数据库模型辅导、data编程讲解、辅导讲解数据库编程
讲解数据库SQL|解析C/C++编程
FIT9132 Introduction to Databases
2018 Semester 2
Assignment 2 - Monash Library Services (MonLib) Case Study
Due Date:? Wednesday 10th October 2018 5PM (Week 11)
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.
Page 1 of 6Borrowers 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).
A model to represent this system has been developed:
This model is available on Moodle as a PDF document.
You have been supplied with a schema file FIT9132_2018S2_A2_Schema_Start.sql which partially
implements the Monash Library model and an insert file FIT9132_2018S2_A2_Insert_Start.sql
which inserts some initial sample data. Neither of these files may? be altered in any way?.
You have also been supplied with a document FIT9132_2018S2_A2_Solutions.sql - you should
rename this script by prepending your authcate username to the start of the filename, e.g.,
abc123_FIT9132_2018S2_A2_Solutions.sql. This script file will be referred to as your solutions
script. Within this script there are marked points where each of your solutions must be added.
All of the work for assignment 2 will take place in this document so please take great care to keep
regular backups, including off your computer, e.g., on Google Drive, so you do not lose
work?. It is suggested that you place regular backups of this file on to your Google drive.
Page 2 of 6Before starting work on the task you ?MUST? complete the header by adding your name etc.,
in the solutions script.
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".
You MUST USE ONLY the values listed for the particular task you are working on in this
document. You must ensure that no unnecessary SQL conditions are used in the WHERE
clause of your SQL statements.
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 [15 + 5 = 20 mks]
For this task you are required to complete the following:
1.1 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.
1.2 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.
In a script you can run a section of the script by highlighting the lines you wish to run and
selecting the run button. If at any stage your tables are corrupted during working on this
assignment you simply need to run your drop commands from 1.2 above and then rerun
FIT9132_2018S2_A2_Schema_Start.sql and your extra definitions that you added in 1.1
above.
Page 3 of 6TASK 2: Data Manipulation [9 + 1 + 1 = 11 mks]
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:
2.1 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.
2.2 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.
2.3 Provide the DROP SEQUENCE statement for the sequence object you have
created in question 2.2 above.
Page 4 of 6TASK 3: [6 + 6 + 6 + 6 = 24 mks]
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:
3.1 Today is 20
th 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.
3.2 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.
3.3 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.
3.4 At 2 PM on the day the book is due, Ada goes to the library and renews the book as
her exam for FIT9132 is in 2 weeks.
You may assume that there is no other borrower named Ada Lovelace.
Page 5 of 6TASK 4: [10 + 15 + 20 = 45 mks]
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
4.1 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.
4.2 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.
4.3 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.
SUBMISSION REQUIREMENTS
Due Date: Wednesday 10th October 2018 5PM (Week 11)
For this assignment there is only one file to submit. You are required to submit your solutions script
file to Moodle before the assignment due date/time. If you need to make any comments your
marker/tutor should be aware of please place them at the head of your solutions script in the
"Comments for your marker:" section.
Late submission will incur penalties as outlined in the unit guide.