Assessment Details Instructions

Assessment Overview

This assessment aims to create a comprehensive modelling report that showcases your proficiency in evaluating business requirements and constructing a conceptual and logical design suitable for implementation within a relational database management system (RDBMS). The report should demonstrate your ability to translate real-world business needs into a technical database solution.

Assessment Instructions

In this assessment, you will need to complete the following steps:

Context

The initial design of a database system is critical because post-implementation changes can be complex and costly. Modifications often impact integrated systems and may necessitate data migration, leading to significant challenges. Therefore, it is essential to ensure the system design aligns with the specific operational requirements of the business from the outset.

Understanding business requirements through case studies and consultations with end users and subject matter experts ensures a thoughtful and accurate initial design. This approach minimizes the need for future modifications by addressing business needs efficiently and effectively, reducing potential disruption to operations.

Case Study

Please read the DAT101A_Assessment_2_CaseStudy.

Instructions

Based on the information provided in the case study, prepare a modelling report to answer the following tasks:

1. Task 1- Conceptual Model: Design and Develop an ER Diagram in Crow’s foot notation with all the entity names, attribute names, primary keys and foreign keys, relationships, cardinality, participation, and constraints indicated. You can add entities or attributes as you see fit. All many-to-many relationships must be resolved.

2. Task 2– Normalisation: Break up the dependency diagram you drew in task 1 to produce a dependency diagram that are in 3NF (Hint: You might have to create a few new attributes. Also, make sure that the new dependency diagrams contain attributes that meet proper design criteria; that is, make sure there are no multivalued attributes, and that the naming conventions are met). Normalise the database design you developed to the Third Normal Form. (3NF). Identify and remove all partial dependencies and transitive dependencies for all entities of the model. Discuss the complete process of normalisation of your database design. As you progress through the normalisation process, critically evaluate each step. Consider the following reflective mechanisms:

• Explain why each specific normalisation step is necessary. What problems or inefficiencies does it address in the database design?

• Reflect on the implications of normalisation on the overall database structure. How does it affect data integrity, storage efficiency, and query performance?

• Discuss any challenges or trade-offs encountered during the normalisation process. Were there situations where achieving a higher normal form. posed difficulties?

• Compare the initial database design (before normalisation) with the final 3NF design. Highlight the improvements and advantages gained through the normalisation process.

• Present your normalisation process and reflective evaluation in a rational, structured, and logical fashion. Use appropriate headings, subheadings, and visuals to enhance clarity.

3. Task 3- Relational Schema: Translate the conceptual model into relational schema. This schema will be the foundation of the database implementation. A Relational schema indicates the type and purpose of all attributes. Ensure your schema includes data types, constraints, and foreign key references. Your logical model should:

• Define Tables and Fields: Define each table listing all fields for each entity, including primary keys, foreign keys, and concatenated keys where necessary.

• Business Rules: Identify and list all relevant business rules that govern relationships, constraints, and data flows. The rules should ensure data integrity and alignment with the real-world scenario.

• Relationships: Identify and define the relationships between tables using foreign keys. Provide cardinality (one-to-one, one-to-many, many-to-many) for each relationship, ensuring that these relationships are appropriately represented in the schema.

• Application of Constraints: Apply appropriate constraints such as:

o Primary Key: Ensure each table has a uniquely identified primary key.

o Foreign Key: Define foreign keys to establish relationships between tables.

o Unique & Not Null: Apply constraints to ensure data uniqueness and validity.

o Default Values: Use default values where applicable to ensure the consistency of records.

Report

• The word count of this assessment is 1000 words +/- 10% but this assessment focuses largely on models and diagrams. The text in this assessment would largely be the explanation of the diagrams, assumptions, business rules, normalisation, and discussions.

• All diagrams must be drawn using any diagramming tool (draw.io, lucid chart) and then copied to MS Word document.

• You can make assumptions where necessary but please outline them very clearly.

• Please read the case study multiple times and try to understand the business requirements from the client's perspective.

• Please provide all of the above in a MS Word document and upload into the submission link, with the Cover sheet at the beginning.

Presentation

• Please feel free to use between 6-10 slides, but we wouldn’t recommend you go more than that.

• Keep text minimal on each slide and focus on visuals (diagrams, schemas). Explain your diagrams clearly.

• The presentation should be 5 minutes +/- 10%. Practice to ensure that you stay within this time frame.

Artificial Intelligence (AI) Use

In this assessment, the use of generative artificial intelligence (AI) tools is:

AI PERMITTED/OPEN

In this assessment, you can use the provided list of artificial intelligence (AI) type only in order to generate summaries and concepts such as business rules, normalization, entities and relationships only. However generated text must be critically evaluated and refined.

Example tools might include:

• Chatgpt

• Jasper

Example functions and ways might include:

• Generate passages of text that must then be transformed to apply to a student’s particular context.

• Generate passages of text that must be critiqued and applied to a scenario or case study.

• Produce summaries of topics that provide a basis for further non-AI assisted enquiry.

• Produce a definition that provides a basis for further discussion and evaluation.

• Produce a counter-argument with the objective to refine main arguments.

Any use of AI must be appropriately acknowledged. If the AI tools you wish to use are not included on the list provided, you need to seek approval from your lecturer first.

Using AI tools in an unethical or irresponsible manner, such as copying or paraphrasing the output without citation or evidence, using the output as your own work without verification or integration, or using the output to misrepresent your knowledge or skills, is considered a form. of academic misconduct.

You CANNOT use AI tools to generate your full report.

NOTE:

• Refer to the Artificial Intelligence in Education Framework for further information.

• Using AI in an unethical or irresponsible manner, such as copying or paraphrasing the output without citation or evidence; or using the output as your own work without verification or integration or using the output to misrepresent your knowledge or skills, is considered a form. of academic misconduct. Refer to the Academic Integrity Policy and Procedures for more details.