COM5024 Advanced Databases - Design and Implementation Report & Database Assessment Brief 2026

COM5024 Assessment Brief

Useful Terms

Task Summary

This assignment is based on the given scenario along with the two tasks. Your assignment response should be a formal written report, including a cover page, table of contents, headers and footers, page numbers, appropriate section headings and sub-headings, and a references section that uses the AU Harvard system.  

You are to design a database solution to this scenario and implement it using the Arden webhosting platform. 

Any evidence submitted using a database account or platform other than the one provided by your tutor will be treated as unverifiable and disregarded, no marks will be provided for the task 2 of the submitted work.

Assignment Instructions

You need to design a database solution satisfying the requirements of the scenario given below. Then the design needs to be implemented as suitable database objects in the appropriate Database Management system (DBMS) provided by your tutor. You need to evidence your advanced knowledge in the database design and development.

Scenario

A database is required to manage inventory for an online mail order store that provides individual parts for LEGO sets to both other businesses and members of the public.

Requirements

• Inventory Management: The database must be capable of managing data for the stock of LEGO parts. Each part should have list name, colour, dimensions, weight, price, etc.
• Inheritance Strategy: The database must implement an inheritance strategy to allow for the efficient management of data across categories of product.
• Customer, order, and shipping management: Basic customer information should be stored, along with their order history and shipping information.
• Payment information: Payment details along with the payment method details for the orders need to be stored.
• Supplier information: The business does not produce parts, but trades in them, therefore the database must allow for a list of suppliers for each LEGO part. Suppliers will all stock multiple parts, but not all suppliers will stock all parts.
• Search and filtering: The database should include functions for inventory searching and filtering so data sets can be narrowed down to specific parts or categories.
• Reporting: The database should provide the ability to generate customized reports and analytics on inventory levels, restocking needs, and popular parts.

As an example of this kind of service in a real-world context, please see:

1. https://www.lego.com/en-gb/pick-and-build
2. https://www.brickowl.com/
3. https://www.theminifigurestore.uk/

Your own business should be fictional, but realistic in scope.

Task 1 – Physical model

Present a physical model for your database solution in the form of a “crow’s foot” enhanced entity relationship diagram (EERD). Your database needs to be efficient and satisfy all the requirements of the given case study requirements.

The EERD must include entities, attributes, keys, relationships with modalities and cardinalities, datatypes, any appropriate junction tables and need to use naming conventions for all of it.

It should also indicate all constraints, including keys and AT LEAST TWO of the following:

• A default value constraint
• A check constraint used for validation
• An auto-incrementing column

Data must be normalised to 3nf prior to optimisation. You do not need to detail the steps taken to normalise the data; it should be apparent in the final structure.

It must also include AT LEAST ONE example of super/sub-typing (inheritance) modelled using a specific strategy, AT LEAST ONE example of appropriate de-normalised data AND AT LEAST ONE non-key index to improve the efficiency and performance of the database functionalities.

You need to discuss your assumptions for the database design along with their rationale. Write a further section justifying your selected approach(es) to super/sub-typing (inheritance), denormalisation, and the use of any non-key indexes. Avoid broad definitions of the terminology, this is not a knowledge check, instead present the reasoning behind your specific design choices. Support your arguments by citing credible sources.

 (1500 word equivalent)
(30 marks)
(LOs: 1 & 2)

Task 2

a. Implementation

Implement your physical design in the specified DBMS. You must include text-based copies of any SQL code used, and screenshots as evidence of the results of execution of that code, in your report. Screenshots should include identifying data, such as username/workspace information, to validate its origins.

Insert meaningful sample data approximately 3-20 records for all the tables, appropriate for the given scenario, for use in testing and query development. For the data insertion evidence include some sample data insertion in the main body of your report (at least 3 rows per table), but anything beyond this could be included in an appendix.

(1000 word equivalent)  (20 marks) b. Stored Database Objects

In each of the following you must demonstrate a level of complexity commensurate with the work being produced. Which is to say that, for example, a statement simply returning the contents of a single table is not complex enough. Your code should include joins, functions, grouping, aliases, expressions and so on. Your rationale for each should explain how the function relates to the scenario, and any benefits regarding performance optimisation or data security.

1. Example views

Create 2 meaningful examples of views, given the context of the scenario. Provide the code necessary to implement them, screenshots of them in use with valid outputs and the rationale behind each.

(word equivalent)
(20 marks)

2. Example Stored Procedures

Create 2 meaningful examples of stored procedures, given the context of the scenario. Provide the code necessary to implement them, screenshots of them in use with valid outputs and the rationale behind each.

(word equivalent)
(20 marks)

3. Example trigger

Create one meaningful example of a trigger to be used, given the context of the scenario. Provide the code necessary to implement it, screenshots of it in use with valid output and the rationale behind it.

(300 word equivalent)
(10 marks)

(Task 2 Total: 2500 words equivalent)
 (70 marks)
(LOs: 3 & 4)

END OF QUESTIONS

Learning Outcomes (LO)

By completing this assessment, you will have shown and be assessed on all four of the learning outcomes:

1. Demonstrate a critical understanding of the tools and techniques required for advanced database development.
2. Justify the design and development of a database application and critically evaluate the implementation and approach.
3. Design and implement a database system using a range of techniques, meeting the needs of both the user and the client.
4. Develop appropriate query language statements in order to efficiently create, manage and interrogate data within a database application.

Graduate Attribute

5. Discipline Expertise: Knowledge and understanding of chosen field. Possess a range of skills to operate within this sector, have a keen awareness of current developments in working practice being well positioned to respond to change.

 You will be graded based on how well you meet these learning outcomes. Your marker will use a rubric/marking matrix to grade your work, and you can find this on the “My Assessment” tab on the module iLearn page.

Guidelines and Policies

You can find links to more useful information about the assignment and university policies below.