ASSESSMENT 1 BRIEF
Subject Code and Title MIS602 Data Modelling and Database Design
Assessment Modelling Report
Individual/Group Individual
Length N/A
Learning Outcomes The Subject Learning Outcomes demonstrated by successful completion of the task below include:
a) Examine the role of data in an organisation, and develop solutions related to the creation, storage and management of organisational data.
c) Apply contemporary database modelling to identify and address anomalies in data and recommend solutions.
Submission For 12-week Duration: Due by 11:55pm AEST Sunday end of Module 2 (Week 4)
For 6-week Duration: Due by 11:55pm AEST Sunday end of
Module 2 (Week 2)
Weighting 30%
Total Marks 100 Marks
Task Summary
This assessment requires you to prepare a modelling report that demonstrates your ability to assess business requirements along with a logical design that could be implemented in a relational database management system.
Context
When preparing to implement a new information system, it is essential that the initial design is well thought out from the outset. Modifying the database after implementation can often be an expensive process in terms of time, effort and cost. Often the first engagement step is to conduct interviews and workshops with end users and business subject matter experts to get a better understand of how the business runs. The output of these workshops becomes the foundation for the database design.
Task Instructions
Please read the attached MIS602_Assessment 1_Case Study.
Based on the information provided in the case study, prepare a modelling report to answer the following tasks:
Task 1: Conceptual Data Model Design:
A Conceptual Data Model serves as the blueprint for understanding and organizing data, focusing on the overarching structure without diving into database-specific implementations. It outlines business concepts, their interconnections, and the quality of the data tied to these concepts. To ensure a comprehensive and effective conceptual model for this assignment, you must include the following key components:
• Entities: Identify the primary data objects around which you’ll be gathering data. These form the foundational building blocks of your model.
• Attributes: While a high-level conceptual model may not delve into the details of individual attributes, this assignment requires that you specify all critical attributes for each entity.
• Relationships: Clearly illustrate how different entities interact with each other. Specify the nature of each relationship, be it one-to-many, many-to-many, or otherwise.
• Cardinality and Multiplicity: Though these aspects may be optional in a rudimentary conceptual model, for the purposes of this assignment, they are mandatory. Include them to depict the quantity and variety of instances in which entities relate to each other.
• Relationship Names: Assign descriptive names to the relationships between entities to add context, ensuring the model can be readily understood by anyone reviewing it.
Task 2: Logical (Relational) Model Design:
In this task, you are required to transition from the high-level conceptual design to a more detailed logical or relational model. Your model should adhere to the Third Normal Form (3NF) to eliminate redundancy and improve data integrity. Below are the essential components your logical model should encompass:
• Entities and Attributes: Clearly specify all the fields or attributes for each Entity (table). This should be a detailed extension of what you have identified in the conceptual model.
• Primary Keys: Identify and indicate the primary keys for each table. These keys serve as unique identifiers for the records in your database.
• Foreign Keys: Specify the foreign keys that establish relationships between different tables. These keys are crucial for enforcing referential integrity.
• Concatenated Keys: If applicable, indicate any concatenated keys that use more than one field to establish a unique identifier within a table.
• Relationships and Cardinality: Show the cardinality of each relationship, whether it’s one-to-one or one-to-many.
• Assumptions/Business Rules: Clearly list any assumptions you have made or any business rules you are adhering to during the design process. These could relate to data constraints, relationship constraints, or any specific conditions under which the database will operate.
Task 3: A sample dummy input screen (mock-up):
For this task, you will design a sample dummy input screen for a process of your choice from the case study (e.g., an e-commerce application where a user places an order). Designing the front-end or user interface is crucial for accessing the data stored in the database. Prototyping the interface also helps in capturing initial system and data requirements. For this task, please consider the following points:
• Ensure that all the data requirements for the selected process are adequately captured and represented in the ERD.
• You are free to use any tool to create this sample screen. Below are some free tools you may consider:
Draw.io (also known as diagrams.net)
Features: A versatile diagramming tool that’s also good for creating simple wireframes and mock-ups.
Website: https://app.diagrams.net/
Pencil Project
Features: This open-source tool allows you to create mock-ups for web and mobile applications.
Website: https://pencil.evolus.vn/
Wireframe.cc
Features: A minimalist wireframing tool that offers a simple interface for sketching your ideas quickly.
Website: https://wireframe.cc/
MockFlow
Features: While not entirely free, it offers a free tier that allows you to create one project.
Website: https://mockflow.com/
• Write a brief paragraph of about 200 words discussing the following:
• Data Completeness: Does your database design comprehensively accommodate the storage of all requisite information as per the business requirements or case study guidelines?
• Design Iterations: After prototyping the sample input screen, what modifications, if any, were necessary to refine your conceptual model and logical model? Were there any data fields or relationships that needed to be added, altered, or removed to better align with the user interface and functionality?
Submission Instructions
Please compile all the three tasks into a single word document and submit via the Assessment 1 section found in the main navigation menu of the subject’s Blackboard site. The Learning Facilitator will provide feedback via the Grade Centre in the LMS portal. Feedback can be viewed in My Grades.
Academic Integrity
When submitting their assessment task, students will be asked to declare the academic integrity of their assessment by completing and signing an assignment cover sheet. You can
find the assignment cover sheet here
All students are responsible for ensuring that all work submitted is their own and is appropriately referenced and academically written according to the Academic Writing Guide. Students also need to have read and be aware of Torrens University Australia Academic Integrity Policy and Procedure and subsequent penalties for academic misconduct. These are available at https://www.torrens.edu.au/policies-and-forms.
Students also must keep a copy of all submitted material and any assessment drafts.
Special Consideration
To apply for special consideration for a modification to an assessment or exam due to unexpected or extenuating circumstances, please consult the Assessment Policy for Higher Education Coursework and ELICOS and, if applicable to your circumstance, submit a completed Application for Assessment Special Consideration Form to your Learning Facilitator. These documents are available at https://www.torrens.edu.au/policies-andforms
Assessment Rubric
Assessment Attributes Fail
(Unacceptable) 0-49% Pass
(Functional)
50-64% Credit (Proficient) 65-74% Distinction
(Advanced)
75 -84% High Distinction
(Exceptional)
85-100%
Conceptual Model (40%)
The ER diagram does not have
to be in 3NF. i.e. it can contain
many
to many relationships
Fails to identify essential entities or includes unnecessary entities.
Fails to define or incorrectly defines most relationships.
Fails to include essential attributes or includes unnecessary attributes.
Uses incorrect notation and presentation is confusing. Identifies some essential entities but has significant gaps.
Defines some relationships correctly but has significant gaps or inaccuracies.
Includes some essential attributes but misses many.
Uses somewhat correct notation but presentation is hard to follow. Identifies most of the essential entities but may miss a few.
Defines most relationships correctly but may miss a few.
Includes most essential attributes but misses a few.
Uses mostly correct notation, and presentation is somewhat clear. Correctly identifies almost all essential entities.
Almost perfectly defines all relationships.
Optionality.
Includes almost all essential attributes.
Uses almost perfect notation, and presentation is clear.
Correctly identifies all essential entities.
Perfectly defines all relationships, considering multiplicity and optionality.
Includes all essential attributes.
Uses perfect notation, and presentation is exceptionally clear.
Logical Model (40%)
The relational model needs to be in
3NF and free from anomalies
Database is not normalized, containing redundant data and anomalies.
No proper use of Primary
Keys (PK) and Foreign Keys (FK) leading to issues in data integrity and relations.
Some effort to normalize up to 1NF but still has data redundancy and anomalies.
Some use of PK and FK but with significant errors or omissions. Some multi-valued attributes are identified, Normalized to 2NF; removes partial dependencies but still
has some issues with transitive dependencies.
Properly defines most PK and FK, with some minor errors or omissions.
Most multi-valued attributes are identified and correctly Almost fully normalized to 3NF; minor issues with transitive dependencies may exist.
Almost perfectly defines all PK and FK, with minor issues in optimal selection.
Perfectly normalized to 3NF; no redundancy or anomalies related to functional dependencies.
Perfectly defines and optimizes all PK and FK to maintain data integrity and relational integrity.
Assignment 1, MIS602 Page 6 of 7
Multi-valued attributes are not identified or improperly handled.
No efforts to decompose M:M relationships, leading to data integrity issues.
but handling is suboptimal.
Some M:M relationships are decomposed, but with significant errors or omissions.
converted into separate entities.
Most M:M relationships are correctly decomposed into associative entities, with minor errors.
Almost all multi-valued attributes are identified and properly normalized.
Almost all M:M
relationships are perfectly decomposed, with minor issues in optimal design.
All multi-valued attributes are identified and perfectly normalized into separate entities.
All M:M relationships are perfectly decomposed into associative entities with PK and FK, preserving data integrity.
Sample mock-up screen (20%)
Sample mock-up for a process. Discuss how the database design allow the storage of all the required information and the changes made to the initial ER
diagram and relational model
Input screen elements are mostly inconsistent with the ERD, missing critical elements like keys.
The paragraph does not address whether the initial ERD captured the data needs of the process.
The paragraph provides no clear discussion about changes made to the ERD after the mock-up screens Some elements are consistent, but significant mismatches with entities, attributes relationships are present.
The paragraph briefly mentions the ERD’s relevance to data needs but lacks detail or depth.
The paragraph somewhat discusses changes but lacks clarity or detail.
Majority of input fields align with the ERD entities and attributes, with minor inconsistencies.
The paragraph mostly discusses how the ERD addresses the data needs, missing minor details.
The paragraph clearly explains most changes made to the ERD after mock-up screens.
Nearly all input fields are consistent with the ERD, with minor optional elements missing.
The paragraph is highly relevant, detailing how the initial ERD met or failed to meet data needs.
The paragraph very clearly and thoroughly discusses changes made, with minor room for improvement.
Perfectly matches the logical ERD, including entities, attributes, and relationships.
The paragraph is exceptionally relevant and provides a comprehensive discussion of how the ERD