5915 Database Design COLT2

5915 Database Design COLT2, 2017




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.


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


  1. Requirements


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
  • 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
    • Author;
    • Title; or
    • 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.



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





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.


Order Now