IOM205
2023/24 Resit Coursework
BSc Information Management & Information Systems
Advanced Database Management
Coursework Title:
Individual coursework: Developing Database using Microsoft Access
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:
• Occasionally, a publisher will decide to dispose of surplus books. As an example, when a new edition of a popular text is released, the publisher may decide to get rid of any remaining stocks of the previous edition. In such cases, UniBooks is often able to purchase large stocks of books from publishers for as little as 10% of the cover price.
• After completing a course, a student may wish to sell his/her unwanted textbooks. UniBooks buys textbooks directly from students for 20% of the cover price.
• Some books (e.g. print on demand titles) are ordered directly from the publisher whenever an order from a customer is received. These books are purchased at 10% of the cover price of the printed version.
• All books are sold to the public for 50% of the cover price. There is also a standard charge of ¥10 for postage and packaging, regardless of the size of the order placed.
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 of the dispatch team will need to search for details of the book’s publisher, and then contact the publisher to see if UniBooks 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 member of the dispatch team to reorder a fresh supply from publishers. This raises two problems: customer orders are delayed until new stocks of the 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 books to improve inventory control.
• Management information takes a great deal of time to produce since it is collected/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 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
Copies Ordered |
Discount (% Of Total Order Value) |
20 - 49 |
5 |
50 - 99 |
7.5 |
100 - 199 |
10 |
More than 200 |
12.5 |
• UniBooks offer discounts to customers based on order size (in addition to the discount over cover price). Currently, these discounts are processed manually, and the sales information is not recorded accurately. In general, discounts are given as shown below in Table 2.
Table 2: Discount to buyers
Order Size |
Discount (% Of Total Order Value) |
10 -19 |
5 |
20 - 29 |
7.5 |
30 - 39 |
10 |
More than 40 |
12.5 |
Task Details/Description:
The primary task for this part of the coursework is to produce a fully tested and functional working database application that has been implemented using Microsoft Access. Please note, you MUST at a minimum have the following functionalities in your database application to score a passing mark for the corresponding grading component. Please refer to the last two pages of this document for the grading components and criteria.
• The database must support the business processes described in the scenario and specification. You may change some of the design produced in the group coursework but should include a brief commentary in your report clearly stating, what you have changed and why (please refer to ID2.4 below in the deliverables section).
• Include functionality (forms, queries, reports and charts) to support the main operations of the company. At a minimum, the database should be able to: add, edit and remove books, generate invoices, reorder low stock and produce useful management information (e.g. total value of stocks).
• Entities should be identified by a concise and relevant set of attributes. They must include the basic attributes and be realistic but need not be fully comprehensive for the prototype.
• Include at least two realistic, non-trivial queries. Your queries should demonstrate the use of multiple tables, parameters, calculated fields and a multi-stage query. You should also produce appropriate SQL code for at least one of your queries. The code should be annotated in the report to demonstrate your understanding (please refer to ID2.5 below in the deliverables section).
• Include at least two forms that will help to realise your non-trivial queries, i.e. gather inputs and produce the correct output, which are displayed in an appropriate manner to the user.
• Include two neatly formatted Access reports to provide useful management information. Include at least one level of grouping, and at least one summary/calculated field.
• Include one Access chart that illustrates some key operational information from the application to the organisation.
Including any extra features, you may wish to – adding value to the application in functionality and usability may earn extra credit. Your report must clearly state these features.
Deliverables:
To complete this coursework, you MUST submit the following two deliverables (ID1 and ID2).
ID1: A fully tested and working database that fulfils the functional requirements.
ID2: A report not exceeding 2000 words, which MUST include the following. The font cover page, table of contents, headers, sub-headers, figure and table labels as well as annotations, citations and references, are not included in the word count (i.e. 2000 words). Estimated word limits for each section are provided below to help you write the report and get some idea of how much to focus on each section, while deciding the contents. You must use Harvard referencing style. for your references and citations. Please note, your report MUST include the following at a (or the) minimum to score a passing mark for the corresponding grading component. Please refer to the last two pages of this document for the grading components and criteria.
• ID2.1: The front cover page of the report must include your student number [No word count applies]
• ID2.2: Entity Relationship Model of your database. Please include a brief commentary describing the model. In your commentary you may briefly discuss any changes made to the ERM you have submitted in the group coursework, and rationale for these changes. [estimated word limit – 250 words]
• ID 2.3: For each table in the ERM, please provide examples of sample records (minimum four for each table) using screenshots from Microsoft Access. Each screenshot must be clearly labelled with the name of the table and any other information, you may want to include (Example of a label. Table1: Books). [No word count applies]
• ID2.4: Simple instructions briefly describing how to operate the database. The instructions must be written as if for a typical employee with limited knowledge of Microsoft Access. You may use figures and annotate them to better articulate the instructions. [estimated word limit – 250 words]
• ID 2.5: Brief description of the most significant features in the database, for example, how these features meet the needs of the business and address the current limitations, explained with the aid of figures/tables (as applicable). Justify the features and/or design decisions citing appropriate literature. You may like to focus on three-four most significant features that help to realise the value of your application to the business. [estimated word limit – 600 words]
• ID2.6: Include a brief description of two non-trivial queries. In your description, state why they are non-trivial and how they meet the needs of the organisation. Additionally, please include figures to show each query you have generated and the corresponding output. You must also include the SQL code for one of the queries, and briefly explain the code in your own words, demonstrating your understanding, how the query will execute. [estimated word limit – 250 words]
• ID2.7: Include a brief description of two management reports. In your description, state why the reports are useful for the management, and how they will add business value. Please include figures to show sample report outputs you have generated. You may annotate them (as applicable). [estimated word limit – 200 words]
• ID2.8: Include a brief description of one non-trivial chart. In your description, clearly explain the value of the chart to the organisation. Please include a figure to show the sample output. You may annotate them (as applicable). [estimated word limit – 150 words]
• ID2.9: Include a brief description of how you have tested the database to ensure it is working properly, capable of handling errors, and generating the correct output for your queries. Please include a couple of screenshots to provide evidence of testing. [estimated word limit – 100 words]
• ID2.10: A brief reflection on the strengths (at least two) and weaknesses (at least two) of your application. You must also suggest at least two ways in which you could significantly improve or enhance the application. This section should also include a brief discussion of your own performance and your learning. [estimated word limit – 200 words]
Warning! If your database cannot be opened, is corrupt or is otherwise inaccessible, or your media contains malware, you will score zero for this part of the assignment.
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. Your work will be assessed on robustness, functionality meeting the business requirements, usability and the overall quality of your design. The working and fully functional database contributes to the majority weight for this coursework, hence please create necessary back-ups.
Submission Date & Time:
The deadline for the submission is on 31 Jul. 2024.
You are required to submit two deliverables: (1) Your Microsoft Access Application; (2) Your Report. The coursework is going to be submitted electronically using the Blackboard. Further instructions about how to do this will be provided during a seminar session.
Assessment Weighting for the Module:
The weight of this coursework is 100% for referred/repeating students
版权所有:编程辅导网 2021 All Rights Reserved 联系方式:QQ:99515681 微信:codinghelp 电子信箱:99515681@qq.com
免责声明:本站部分内容从网络整理而来,只供参考!如有版权问题可联系本站删除。