COM5013 Database design and implementation Report Assignment Brief

Assignment Brief

As part of the formal assessment for the programme you are required to submit an Advanced Databases assignment. Please refer to your Student Handbook for full  details of the programme assessment scheme and general information on preparing  and submitting assignments.

Learning Outcomes: 

After completing the module, you should be able to: 
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.

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

Scenario 

A database is required to manage inventory for an online platform that provides a  second-hand electronic gadgets and accessories to members of the public in UK, both  sellers and buyers. Electronic gadgets and accessories include items from all brands  and categories.

 Requirements: 

• Inventory Management: The database must be capable of managing data  for each user’s electronic gadgets and accessories on sale. Each electronic  gadgets and accessory item should have its name, model, colour, brand, date of purchase, specification, condition, price, etc.

• Inheritance Strategy: The database must implement an inheritance strategy  to allow for the efficient management of data across electronic items and  accessories’ categories. 

• User, order, and shipping management: Basic user information should be  stored for their user profile, along with their order history and shipping  information. 

• Sales: Basic sales data of electronic gadgets and accessories need to be  included which helps to manage the stock. 

• Search and filtering: The database should include functions for inventory  searching and filtering so data sets can be narrowed down to specific  categories of electronic gadgets or accessories. 

• Reporting: The database should provide the ability to generate customized  reports and analytics on inventory levels of the electronic gadgets and  accessories. 

You are to design a database solution to this scenario using the database  management system (DBMS) specified by your tutor. 
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 work.

Assignment 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). This 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. Your database needs  to be efficient and satisfy all the requirements of the given case study. 

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.

Assignment Task 2

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 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 should be included in an appendix.