Database Design COLT2

The assignment is to design a University Library system database using Microsoft Access. It requires a group of 3-4 students to design a logical entity relationship diagram in third normal form, create a data dictionary including all entities and attributes, and implement the solution as a database system. The database should have different views for library staff, students, and general staff, and provide various facilities such as checking for unpaid fines, adding new borrowings, providing records of return dates, and running reports.

5915 Database Design COLT2, 2017
ASSIGNMENT 2
Due date: Friday week 12 of Term 2, 2017 at 11:55pm
This assignment has 150 marks which constitutes 20% of the total marks for this unit.
You need to submit the design documents as set out in the Submissions Section in separate
documents for this assignment AND your database (Access Database). The documents include a
logical Entity Relationship Diagram in third normal form, together with a Data Dictionary
including ALL entities and attributes and the database documentation as described in the
assignment specification below. Please contact Paramjeet if you have any queries about this
assignment.
1. General Information
The purpose of this assignment is to provide you with experience in logical design then
implementing a solution for University Library system database. Your solution should be
implemented as a database system using Microsoft Access. This assignment will help you to
understand the nature and purpose of logical design and implementation. It offers you
experience in managing a technical database project.
This assignment is to be attempted by groups of 3 to 4 students. Each group is collectively
responsible for both the submission and the outcome. There are no restrictions on the use of
word processors or similar tools for the production of submissions for this assignment. You will
also perform a demonstration of your assignment to your tutor in week 12 during the tutorial
session.
Make sure that you maintain regular back-ups for any models or material prepared with the aid
of software. Loss of files will not be accepted as an excuse for non-completion of this
assignment. Each group member is advised to retain a full copy of the material submitted for
this assignment.
2. Problem Description
The problem description is that given in Assignment 1. The major differences are the LOGICAL
design (i.e. in third normal form) and requirements that relate to the way that the
implementation (database) must work.
3. Requirements
Page 1 of 4

The database program developed by your team should have well designed screens that are easy
to use, understand and follow by novice staff. A novice user should be able to use the database
application (program) by reading instruction on the computer screen of your program. For
security reasons the views of library staff, students and general staff (non-administration)
should be different.
All reports and input screens should be easy to read, use and understand. It should be logically
laid out for novice users.
To produce the three types of computer-generated reports are produced by the database
system and these are spelled out in Assignment 1. These reports are created by staff of the
Administration section. The reports are as follows:
1. The first shows all overdue books. This report is sorted by student number and it is used to
contact people with overdue items by e-mail. People are reminded to return the specified items
and of the late-return charges that will be incurred. If the items are not returned after a period of
two months from the due date legal action is usually taken and a report is lodged to the student
administration of the university. Also the student's record is marked with a special notation to
prevent further borrowings.
2. The second report shows the total number of books that are reported to be missing, so that new
copies can be ordered.
3. The third report shows the details of the books that are borrowed in the last three months. This
report is used to order more copies of popular books.
The database program should provide the following facilities:
Requirements for staff
As different staff have different responsibilities, each section should have specific access
according to the section that they work in. The sections and the way that they use the system
are set out below.
Information desk staff should be able to:
 Access the catalog to help students with its use; and
 Provide advice on the library rules.
Loan desk staff
 Check for unpaid fines for a student;
 Add a new borrowing to the system and;
 Provide a record of the return date and time for the books.
Return desk staff
 Update the loan record to include the date and time of return.
Administration staff should be able to:
 Manage and run the reports that are created by the system;
 Check Student loan details for any overdue books; and
Page 2 of 4

 Print advice to students with fines owing.
Book-order staff should be able to use the database system to:
 Order new or replacement books from a publisher.
Cataloguing staff should be able to:
 Enter the details of new books to the database system to the library catalog as well as
new copies of an existing book;
 Change the details of the existing books (e.g. missing books etc.).
Requirements for Students
Students should be able to access the system as follows:
 Access the catalogue to search for a book by
o Author;
o Title; or
o Subject;
 Check the availability of a particular book;
 Place a reservation for a book that is out on loan; and
 Check if there are any outstanding fines that need to be paid.
Page 3 of 4

SUBMISSIONS
Submit your design documents (Logical ERD and Data Dictionary), your Access database and the
database documentation via Moodle by the due date as specified above together with a cover
page containing the names and student numbers of the members of your team using the form
located in Moodle. This is a total of FIVE files.
Late submissions will NOT be marked.
DO NOT use compression software as the files can be submitted at the
same time (NO .ZIP FILES!!!!).
MARKING SCHEME
Presentation in tutorial 15
ER diagram (logical) 30
Data Dictionary 20
Database documentation (see below)* 20
Tables 20
Data 15
Queries 10
Forms 10
Reports 10
TOTAL 150
*This includes instructions on how to operate the database, screen dumps of your
Access forms, completed reports and comments on the future directions for the
database. This should also include any assumptions that you may have made
about the Library.
Page 4 of 4