COM5013 Database design and implementation Report Assignment Brief
| Category | Report | Subject | Computer Science |
|---|---|---|---|
| University | Arden University (AU) | Module Title | COM5013 Database design and implementation |
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.