INF60009 High Distinction Task Overview ✓ Solved

INF60009 High Distinction Task High Distinction Task Overview

You’ve now been given exposure to quite a few tools in the database analysis and design toolkit. In this task, you’re going to combine these tools – and your creativity and expertise – to deliver a complete database analysis, design, and piloting activity (i.e. narrative, ERDs, DB solution, queries, reports and visualizations).

This task is centered around a problem that you identify, describe and implement as a database solution.

Task Descriptions

Task 1 – Business Narrative

Describe the problem that you want to solve and describe how you believe that a relational database is an appropriate solution. This must be in the form of a business narrative. Describe how the problem is being dealt with currently. Describe the data that is involved. Describe why it is useful for the organization to use a database rather than some other application. Describe the business rules that apply to the data you want to store. Describe the types of queries, reports and/or visualizations that you believe will provide useful outcomes.

Task 2 - Entity Relationship Diagram

Draw an ERD that matches the requirements of the business narrative that you described. Generate a relational schema based on the ERD. Do not include entities or attributes that have not been described in the narrative.

Task 3 - Database Solution

Build a database solution in MS Access or an SQL script that can be run on the Feenix Oracle server (or other server with agreement from your tutor). All tables must have a 9999 suffix (last 4 digits of your student ID).

Task 4 – Test Data

Populate the tables with sample data. One of the test entries for at least one of the entities must include a reference to your name and/or student ID. There must be sufficient data for queries, reports and/or visualizations to have meaning for your tutor. Create simple queries that list all of the data in each of the tables.

Task 5 – Queries, Reports, Visualizations

Create a series of complex queries and visualizations that meet some requirements as listed above. You must have at least 8 reports and visualizations. There must be a mixture of each. A complex query typically involves using totals and/or multiple tables and/or subqueries and/or outer joins.

Paper For Above Instructions

In this paper, we will discuss the overall project aimed at developing a relational database solution for a local sporting club to manage data effectively. The sports club has expressed a clear desire to upgrade its current record-keeping systems, which rely heavily on spreadsheets and manual documentation. The aim of this project is to provide a robust solution that addresses critical data management issues using a relational database.

Task 1 – Business Narrative

The problem at hand is the inefficiency of record-keeping and data tracking within the sporting club. Currently, the club stores data about players, match results, team histories, and financial transactions in flat files and spreadsheets. This system leads to data redundancy, lack of data integrity, and difficulties in retrieving relevant information promptly.

Relational databases provide an appropriate solution as they enable structured storage of data with defined relationships among different datasets. By creating relational tables for players, matches, results, payments, and officials, the club can ensure data integrity and simplify data management processes.

The data to be encompassed includes entities such as players, match results, teams, officials, and financial transactions. Utilizing a database will enable the organization to prioritize data accuracy and availability, thereby enhancing decision-making and improving operational efficiency.

The business rules in this scenario will safeguard the integrity of the database. For example, each player must be linked to a specific team, and results can only be associated with existing matches. Queries will be designed to extract valuable information such as player statistics, financial status, and historical performance.

Task 2 - Entity Relationship Diagram

The Entity-Relationship Diagram (ERD) for this project will consist of several entities: Players, Teams, Matches, Results, and Payments. Each entity will have its corresponding attributes. For instance, the Players entity will include attributes such as player ID, name, age, team ID, and payment status. The Matches entity will include match ID, date, and team IDs for the participants.

The ERD will illustrate the relationships between these entities, for example, the relationship between Players and Teams will be a one-to-many relationship, while the relationship between Matches and Results will be one-to-one. A relational schema will be generated accordingly to define these structures, ensuring all attributes align with the narrative described earlier.

Task 3 - Database Solution

For the database solution, a Microsoft Access database will be constructed. The database will house the various tables defined in the ERD, each ending with the specified suffix of “9999”. A table for players will be created with the relevant fields, and similar structures will be implemented for teams, matches, and payments.

The database will be designed to accommodate data entry forms for ease of use, with validation rules implemented to enhance data integrity. This foundational setup will enable effortless interaction with the database for current and future users.

Task 4 – Test Data

Sample data will be populated within the database. It is crucial that at least one entry within the Players table refers to the student ID, allowing easy identification of the database creator. Each table will have a sufficient number of entries to ensure meaningful queries can be crafted.

Queries will be developed to test the functionality of this populated data. For instance, a simple query will list all players, detailing their team affiliation and payment status. Such queries will provide insights into the data's coherence and accurately reflect the expected outputs.

Task 5 – Queries, Reports, Visualizations

Finally, a series of complex queries and visualizations will be created. These will consist of reports summarizing player statistics, financial summaries for the club, and visual representations of team performances over seasons. At least eight distinct reports will include graphical visualizations, allowing for dynamic data presentation.

To enhance user engagement, Power BI might be employed for visualizations, providing an interactive experience that further elevates the club's data presentation capabilities.

Conclusion

This proposed database solution for the sporting club not only addresses current inefficiencies but also provides a framework for sustainable data management. Deploying this relational database can significantly improve record-keeping practices, enhancing the club's operational efficiency.

References

  • Coronel, C., & Morris, S. (2015). Database Systems: Design, Implementation, & Management. Cengage Learning.
  • Date, C. J. (2003). An Introduction to Database Systems. Addison-Wesley.
  • Elmasri, R., & Navathe, S. B. (2015). Fundamentals of Database Systems. Pearson.
  • Harrington, J. L. (2016). Relational Database Design and Implementation. Morgan Kaufmann.
  • Kroenke, D. M., & Auer, D. J. (2013). Database Concepts. Pearson.
  • Rob, P., & Coronel, C. (2016). Database Systems: Design, Implementation, & Management. Cengage Learning.
  • Silberschatz, A., Korth, H. F., & Sudarshan, S. (2010). Database System Concepts. McGraw-Hill.
  • Thompson, B. S. (2019). Database Management Systems. McGraw-Hill.
  • Ullman, J. D., & Widom, J. (2011). A First Course in Database Systems. Pearson.
  • Vaughan, T. (2019). Power BI for Dummies. Wiley.

Related Assignments