Description

Assignment: Access Database

The community centre in your neighborhood runs a comic library system, in which all registered users can borrow up to four comic books for up to two weeks at any one time. Recently the centre is computerizing the system. Knowing that you are a reliable volunteer and have taken an information systems course, the centre’s coordinator invites you to work on the project.

A preliminary design of the library system database is as follows.

The table members stores the data of each registered member of the library system.

The table copies stores the data concerning each copy of the comic books in the library. They include the book title, publisher, author, cost, language, category and purchase date. Note there may be multiple copies of the same comic title (determined jointly by booktitle + language), which means that these copies share the same book title, language, publisher, author and category.

The table borrow_records stores the data concerning the borrow records of the copies, including the borrow date, expected return date (which is always two weeks after the borrow date), and whether the book has been returned or not (the default value should be FALSE).

You are required to complete the following tasks with Microsoft Access.

Part 1: Creating Tables (40%)

Modify the above preliminary design to eliminate redundant data storage and facilitate efficient filtering or sorting of records in subsequent queries. We expect a few tables being logically related together. For each table, determine appropriate table names, field names, primary key fields and field types, and have them filled in with the sample data shown above. There is no need for you to make up any other data.

Part 2: Creating Queries from the Database (60%)

In order to obtain useful information from the data stored in the database, one needs to summarize them with queries. Create the following queries for the management team, the frontline staff and the borrowers.

1.          Total cost of all the copies in the library

2.          The average costs of all the comic copies from individual publishers

3.          The borrowing count of the comic titles (a unique [booktitle, language] as a single comic title) within some two borrowing dates being specified dynamically, arranged in descending order of popularity

4.          The number of distinct comic titles in different categories

5.          The total number of copies held for each comic title, sorted by ascending order of comic title

6.          The number of members in different districts (e.g. Tin Shui Wai, Yuen Long and Tuen Mun)

7.          A list of copies purchased before a specific date inputted dynamically. You should include the copy_id, booktitle, language and publisher.

8.          The list of copies currently being borrowed by a member whose ID is being inputted dynamically. You should include the copy_id, booktitle, language and expected due date.

9.          A list of overdue copies. You should include the copy_id, booktitle, language and expected due date.

10.      A list of members who have overdue books. List these member IDs, full names and mobile numbers.

Label the queries as 2-01, 2-02 …, 2-10.  

Submission

Name your Access file xxxxxxxxxx.accdb (your 10-char student ID) and upload it to the Blackboard’s assignment collection drop box by March 27 before midnight. Late submission will not be considered.