IOM205

Advanced Database Management

1st SEMESTER 2025/26 Group Coursework

INSTRUCTIONS TO CANDIDATES

1. Your work must be submitted electronically through Learning Mall Online by 00:00 AM, OCT 20th 2025.

2. This coursework accounts for 30% of the final mark in this course.

3. This is a group coursework.

a)  Only the team leader needs to submit the files on LMO.

b) Marks are awarded to the group as a whole; every member receives the identical score.

4. Generative AI is not allowed in completing this coursework.

Coursework Title:

Group coursework: Designing a Database

Scenario

Context

UniBooks is a small start-up company specialising in second-hand textbooks. The company buys surplus academic books in bulk from publishers but also buys individual books from students.  The books are advertised and sold via the company’s website. You have been approached to develop a database system for the company. The system needs to be able to store details of the books held in stock, to process incoming books and to record all sales. The system will also need to be able to perform other functions, such as producing invoices and different management reports.

Specification

UniBooks supplies textbooks to university students at discounted prices. The company obtains stocks of books directly from publishers and students:

•    Usually, UniBooks is able to purchase large stocks of books from publishers for 30% of the cover price.

•    After completing a course, a student may wish to sell his/her unwanted textbooks. UniBooks buystextbooks directly from students for 20% of the cover price.

•    All books are sold to the public for 80% of the cover price. There is also a standard charge of ¥10 for postage and packaging, regardless of the size of the orderplaced.

The current system suffers from several limitations:

•    Occasionally, a customer will order a book that is not held in stock (e.g., print on demand titles).

A staff member will need to search for details of the book’s publisher, and then contact the publisher to see ifUniBooks can obtain a copy of the book. The current system does not provide a search facility that can be used to locate a publisher’s details.

•    When stocks of a given book run out, it is left to a staff member to reorder a fresh supply from publishers. This raises two problems: customer orders are delayed until new stocks ofthe book arrive, and there is inconsistency regarding the number of books ordered from publishers. As an example, sometimes too few or too many books are ordered, resulting in further delays to customers or increased inventory costs. Ideally, it should be possible to set reorder levels and reorder quantities for booksto improve inventory control.

•    Management  information  takes  a  great  deal  of time  to  produce  since  it  is  collected  and processed manually. As a result, managers are only able to see important information, such as sales figures, on a monthly basis. Ideally, management information should be available on demand.

•    Most publishers offer discounts based on order size (in addition to the discount over the cover price). In general, discounts are given as shown below in Table 1. At present, UniBooks often fails to claim the discounts it is entitled to.

Table 1: Discount from publishers

•    UniBooks offers discounts to customers based on order size (in addition to the discount over cover price). In general, discounts are given as shown below in Table 2.   Currently, these discounts are processed manually, and the sales information is not recorded accurately.

Table 2: Discount to buyers

Task Details and Description:

The primary task for your group is to go through the scenario context and specification to understand and analyse the company’s data and information requirements. This should help you to design an appropriate database structure, which can meet the requirements of the organisation. In developing, realising and implementing the design, your group must create sample records (dummy data) for the identified entities.

This is a group task and each member of the group must complete an equal share of the work. A list of tasks (not exhaustive) that you will need to perform. as a group in completing this coursework are given below.

Tasks:

1.   Decide upon the entities to create tables and corresponding attributes you will need, to address a range of requirements stemming from the scenario and specification. Example entities: customer, book, student, employee, order, etc. (Note: this is not an exhaustive list.)

2.   Create  an Entity-Relationship Model (ERM) using  Microsoft  Access  to  address  the requirements. This model must be normalised to reduce data redundancy and improve data integrity.

3.   Include sample records (at least four) in Microsoft Access for each table after developing the ERM and include validation of the attribute types, constraints and primary/foreign keys.

4.   Reflect upon your ERM,  discuss two issues/limitations associated with the design and anticipated operation of your database, and its impact on the business, i.e., how it might affect the business.

Note:

All these above the tasks will help you to make progress and completing this group coursework. Upon completion of the group coursework, the deliverables produced will provide a suitable starting point for the individual coursework.

Deliverables:

You MUST submit the   following three separate files electronically to complete this coursework. Your team leader is responsible for submitting all required documents before the deadline.

• Deliverable 1 (25%): The Group report (a MS word document) should not exceed 500 words. Marks of up to 5% points will be deducted if you exceed this word limit. Estimated word limits for each section are provided below to help you write the report.

• Deliverable 2 (50%): Entity Relationship Model as a PDF document from Microsoft Access.

• Deliverable 3 (25%): Your Microsoft Access Database File

Contents of the report (Deliverable 1)

•    A separate cover page of the report must include your group number and student information (including name and ID) for each member in the group. [No word count applies]

•    Briefly  explain the normalisation and resolving m:m relationship process with the aid of a suitable example (use the same example for each process step) drawn from your own ER model. [estimated word limit – not more than 300 words]

Tip: You should not define normalisation and describe its objectives, rather explain how you have employed normalisation and resolved m:m relationship in your ER model (using at least one suitable example).

•    A brief discussion of two issues associated with the design and anticipated operation of your database, and how these issues may impact the business/organisation. You may discuss any issues/problems for which a solution is not yet clear, or where there are competing alternative designs. [Estimated word limit – not more than 200 words].

Tip: To score a passing mark for this component, you MUST at minimum discuss two issues suitably in your report (as stated above).

Note:

1. Your  work  will  be  assessed  on  robustness,  correctness,  design  meeting  the  business requirements, and the overall quality of your design.  Please refer to the grading criteria at the end of this document, to know how each component or a combination of components will be graded.

2. Please ensure you have created one or more back-ups of your work in suitable places using appropriate mediums available to you, to avoid losing the work you may have already done.

Learning Sessions Relevant to this Coursework

Week 1: Introduction to Databases

Week 2: Entity Relationship Modelling

Week 3: Normalisation

Week 4: Recap

Week 5: Assignment workshop