帮做FIT9132留学生、Monash数据库模型代写、data编程代做、代写代做数据库编程

日期：2018-09-30 05:47

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 6

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).

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 6

Before 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 6

TASK 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 6

TASK 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 6

TASK 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.