DATA MODELLING, MANAGEMENT AND GOVERNANCE

Table of Contents

Introduction 2

Methodology, design process and necessary tool 2

Use case diagram 5

ER model 8

SQL statement for creating database and table 10

Relational schema 15

SQL query 15

Conclusion 18

Reference List 19

Introduction

Sports club record systems (SCRS) is a crucial part to maintain all records of members, staff and other club authorities regarding sports activities. Each member of a sport club has unique identity number, telephone, email, date of birth, ethical background, surname and medical conditions. Hence, database based records systems is required for sports club in managing those huge information. At the same time, managing sports classes of each member using class code, delivery time, title and day for the instructor is an essential part for controlling the activities of sports club. An advanced SCRS can assists sports club member and staff to address classes, personal details, sports teaching and attendance activities to enhance the efficiency of operations.

This report will analyse design process, methodologies and software tools that are required for sustainable development of advanced SCRS development for sports clubs. Benefits and effectiveness of use-case diagram and Entry Relationship Model (ERM) will analyse for the database management process of SCRS. Here, SQL workbench, UML diagram, formatted tables and diagram will be used in the development process to manage the rational challenges and SQL queries during development phases. Based on the detailed analysis, a appropriate conclusion will be provided for the SCRS.

Methodology, design process and necessary tool

Record management systems are a crucial part of sports club to manage big data of staff and members about activities. The first stage of SCRS development process is to acquire diversified data and information related to sports club through different sources and stored information. Different types of open sources such club website, national and international league website and search engine database to make proper framework about data specification and data labelling. SQL documentation model and intimidation strategies can be used for sustainable data base development (mysql.com, 2022). Statistical theories about data mining and data analysis process can be used to specify data as per different factors of sports information. Therefore, different types of code such as INT, VARCHAR, DATE, BOOLEN, CHAR and ENUM can be used in during specification of data variables (Ellison et al. 2018). Moreover, different types of constraints and variable will be considered in development of tables and diagram development to manage the efficiency of SCRS.

Here, several types of commands and codes will be used for managing experimental testing of SCRS development phases to enhance the rate of record management efficiency. Clear mechanism and potential factors of sports members and staffs will be incorporate during prototype design process to manage the fundamental areas of SCRS (Al-Obeidallah et al. 2018). Therefore, database design, architecture, data modelling, development and comprehensive administrative tools will be used in the SQ Workbench based record management system development. Different methods of development have been used in the below points:

Design: Web-based SCRS should provide interface facilities to both users and admin to manage the front end users involvement in admin dashboard management. Therefore, sports clubs activities, training facilities, and case scenarios should be used in the design process. Different volunteer events and sections are interconnected with the database of record management systems to enhance the backend design. Database tables and relationship tables are used in development of interconnection between UX and design framework for sustainable design process (mysql.com, 2022). Architectural design and volunteer control will be used in the front page dashboard development for end users of sports club. Bootstrap is used for front end design and hierarchy of documentation process for standard design framework. Moreover, dynamic style and design procedures can be used in implementation of proper interaction development for sustainable development of module development to enhance the rate of success. During the design process, complex ER models, DBA, documentation model and engineering features have been used for proper design. Moreover, Architectural issues, communication challenges and flexible database management procedures have been analysed for physical standard management and database development (Navarrete et al. 2018). Forward and reverse engineering methods can be used in SQ Workbench data database management to build advanced DDL scripts to run proper functional operation of the different text format and data types.

Development: SQ Workbench based system development is the most crucial part of methodology to implement database such as user name, password, gender, age, title, email, date of birth and etc.. Advanced coding and language should be used for auto generation of table and diagram for installation of different data sources. Predefined methods and models have been used in evaluation of connection and integration of queries and data of different sources. Therefore, multiple views, SQL queries, snippet panel, command history panel and database searching panel will be used in the SQL editor based development phases (mysql.com, 2022). Schema inspectors can be used for the development of statistical analyse and operation of various detailed information of sports clubs members and staff. Connection wizards and panel will be enabled for creating connections among IP address, username, and other information of members for suitable development process. Pre-defined analyse and layouts will be analysed during development process to evaluate the implementation factors for sustainable analyse of data processing and modelling. Here, dynamic module and design procedures will be analysed for back end controlling to manage the user interface design.

Administer: Administer tools are essential for the graphical representation of interface design and DBA based easier performance management framework design to improve configuration and administrating process of servers. Here, SQL workbench based GUI will be analysed for setup, backup and schedule and run to get recovery of different valuable information related to sports activities (Bogusławski et al. 2022). Powerful database auditing can manage the suspect activities of sports members and staffs regarding the privilege management. Entity Relationship Model can be implemented as a administrative tool for proper set up and easier operation process of different attribution in sports events. Less complex and effective configuration of server network and database parameters management can manage the connection in true navigation process for sustainable operation of SQL servers for SCRS. Server logs can be used in development process to introduce centralise operation of sports club members information and database challenges. Visual tools of SQ Workbench should be implemented for advanced configuration of browsing function and data formatting sequence. Health indicators and authentication settings can be used for the server directories and graphical challenges mitigation in system design.

Performance analyse using testing: Different types of tools of SQ Workbench should be used to analyse performance and testing of prototype of SCRS for membership records management. Here, performance of hotspots, SQL statements, data engine, network and key servers of record storage servers will be analysed in testing and implementation phases for the optimise operation (Bogusławski et al. 2022). Unit testing is used for analysing the effectiveness of coding such as initial mistakes, security bugs and errors for perfect system development. Integration tastings usually applied for analysing interrelation among fundamental details of sports authorities and members. System and usability testing are necessary to investigate operational efficiency in practical working situation to manage vast amount of data of sports members of clubs. User-friendly of UI will be investigate during the implementation process to manage the efficiency of user attraction and functional efficiency improvement procedures. Performance reports and query reports will be utilised for the effective statistical analyse of different information of sports persons.

Database migration: SQ Workbench data migration wizards are effective tools that can be used in evaluation of effective modelling and fundamental development of proper configuration of easiest data migration process. Therefore, this tool can manage the configuration, editing, coping and execution of advanced user oriented data migration procedures to target functional efficiency (Navarrete et al. 2018). The tool can be applied in database mapping and user interface structure development process for advanced data governance framework design and development.

Use case diagram

Figure 1: Use case diagram of sports club database

Use case diagram has been created for the sports club database management system. Two actors along with seven activities have been identified in the concerned situation. Six activities including login and registration, details entry, modification along with activity booking, signup, attendance and logout process has been provided in this use case diagram. In the initial stage, it is recommended to login and register. Both staffs and club members are responsible for conducting the concerned process. Apart from that both actors are responsible for entering details as well as modifying details according to requirement. It has been also found that members are capable for booking activities and max 5 activities are possible within 1 week. Staffs can signup for the activity and one staffs can be involved in one activity. Staffs also can provide attendance in this situation. Draw.io has been used for the development of use case diagram.

A “Use case diagram” can represent the unique behavior of a system and it has the ability to encapsulate the overall functionality of the system with the help of use cases, actors, and their respective relationships. It can model the task, services, and functions, which are required for establishing the system of an application. The “high level functionality” of a system application can be depicted by this and states the process, under which the users can handle the system properly (IBM, 2021; Javatpoint, 2021). There are some essential purposes are available for use case diagram and those are mentioned below:

“Use case diagrams” have the ability to gather needs of the system

“Use case diagram” can be created before starting a project, in order to model a specific business, that can help all the participants in that project to share an understanding of workers , customers and several activities of the business (IBM, 2021)

External view of the system can be depicted through “Use case diagrams”

Recognition of internal as well as external factors, influencing the system can be done with it

Interaction between actors can be represented by use case diagrams

Use Cases from the use case diagram can be utilized to identify the classes, that are required by the system, during the design and analysis phase

Use case diagram can be used to identify specific test for the system, during the testing phase

The following terms can describe the models within use case diagram:

Use Cases

Use case refers to a function that should be performed by a system to meet the goal of the user and this always yields an observable result, which can be reliable to the user of the system (IBM, 2021).

Actors

Actors represented the role of use in the system.

Subsystem

In terms of “UML (Unified Modeling Language)”, subsystems consist of a specific type of stereotype components that can represent independents and behavioral units within the system. These are used within the class, component and use case diagram for the representation of large-scale components within the system (IBM, 2021).

Relationship in Use Case diagram

A relationship refers to a connection between several model elements under “UML”.

Drawing process of Use case Diagram

In order to draw the use case diagram, an individual, at first needs to understand and analyze the whole system. After that, the functionalities of the system can be found. Once, each of the functionalities of the system is identified, and then they can be imposed into the use cases that can be used to draw the use case diagram. After this, the above-mentioned actors can be enlisted, which will interact with the entire system (IBM, 2021). These actors refer to a person or a thing that can be used to invoke the functionality of the system. The actors can be a system or any private entity. After the enlisting actors and use cases, the actual relationship between the actors and use case can be investigated. It can be used to determine the total no of time that an actor will interact with the system.

ER model

Figure 2: Use case diagram of sports club database

ER model has been created using draw.io and in this situation four different classes has been identified. It has been also found that multiple entity identification is required for the concerned situation. Relationship with each other has been evaluated in the concerned situation. Staff id, first name, sur name as well as role and contact number has been identified for sport club staff. On the other hand ids, first name, surname, address, telephone number, email as well as dob and medical condition has been identified for club members. Booking class has id, member id, sports id, date and time. Primary key and foreign key has been identified to make the system more effective.

ER model refers to “Entity Relationship model”, which is a “high-level data model”. This model can be used to determine the elements of the data and identify the relationship for a specified system. “ER models” can be used to develop a conceptualized design for a specific database system. It can develop a simple and easy to design view of any specific data used in a database system. The entire database structure within an “ER model” is considered as a diagram and this diagram is called an ER diagram (Javatpoint, 2021). “ER diagram” refers to a visualization tool that can be used to help users design the database schema in a correct way. ER diagram creation is the first step to develop a database. An example of an ER diagram can be shown that can new useful to design the specific ER diagram for this particular assignment (Cheng and Chu, 2019).

Figure 3: A general Architecture of “ER Diagram”

(Source: Cheng and Chu, 2019)

As per the above figure, a general architecture of “ER diagrams” has been shown, which consist of some entities such as customer, car and insurance, and showed a relationship between these entities. This is almost similar to a concept map that also includes several concept nodes” and “relation links (Cheng and Chu, 2019). In order to design an ER diagram, at first, the entities within a database need to be identified and attributes required to be added to each of the identified entities. For example if a school database needs to be designed, then within this database the students will be the entities that can have attributes such as “Address”, “Name”, ID”, “Age”, and others. Moreover, the address can be considered as another entity, which will have several other attributes such as city, street name, pin code, and others. There will be a relation between them within the ER diagram. Within an ER diagram, total 3 components can exist such as “Entity”, “Attributes” and “Relations”. The entity can be a weak entity, if the entity is dependable on other entities (Javatpoint, 2021).

A weak entity can not include any key attributes within itself. Within the “ER diagram”, a “weak entity” is represented by a “double rectangle”. “Attributes” are presented, to describe the property of any entity, and “Eclipse” is used for represent it. These attributes can be classified in 4 divisions such as “Key Attributes”, “Composite Attributes”, “Multivalued Attributes”, and “Derived Attributes”. Relation represents the connection between the entities and “Diamond or rhombus” is always used to represent relation between two entities (Javatpoint, 2021). The relationship can also be classified into many sections such as “One-to-One relationship”, “One-to-Many relationship: “Many-to-One relationship” and “Many to Many Relationship”.

In order to understand the entire “ER diagram”, at first a person needs to read the diagram from left to right and then from right to left.

Figure 4: Example of reading an “ER Diagram”

(Source: IBM, 2021)

For example, within a relationship between Name and Address, names can be linked to zero or exactly one specific address. On the other hand, addresses can be linked with zero, one, or many names.

SQL statement for creating database and table

Sports_club_staff table

Sport_club_member table

Sports table

Booking table

Sports_club_staff table data

Sport_club_member table

Sports table

Booking table

In terms of “SQL”, the “Create DATABASE STATEMENT” is considered as the first step, in order to the structured data into the database. The developers of the database and users use these “SQL statements” within the “SQL” for creating the new database. It can be used to create a new database with a unique name, which has been already mentioned in the SQL database statement. The “SQL statement” is used for creating a new database and the basic syntax for this create database statement is “CREATE DATABASE DatabaseName”. It needs to be understood that the name of the database should always be unique in the “RDBMS”. In case the user wants to create a new database named <xyZ>, then the create database statement will be “SQL> CREATE DATABASE xyZ” (IBM, 2021). The user should ensure that he or she must have the admin privilege before creating any new database. Once, the user created a database, and then the user can check the database within the database list. Overall, it can be stated from the reports that SQL statements are mainly used for performing several tasks such as, in order to update the data within a database, or to retrieve data from a specific database (Sussex, 2021). Some of commonly used relational database management systems within “SQL” are as follows:

“Oracle”

“Sybase”

“Microsoft SQL Server”

“Microsoft Access and many others”

“SQL commands” can be classified in different terms such as “DDL – Data Definition Language”, “DQL – Data Query Language”, “DML – Data Manipulation Language”, and “DCL – Data Control Language”. DDL statement includes database commands that can be used for defining the ‘Database Schema" (IBM, 2021). This statement mainly deals with all the description of database schema, and can be used by the user to modify and create new structure of the objects within the database. This can refer to a series of “SQL commands” that can be used by the user in order to delete, modify only the database structures, not the data within it (IBM, 2021). However, it needs to be mentioned that general users, who are accessing the database through an application, do not use these statements. Some of the important “DDL commands” are: “Create”, “Drop”, “Alter”, “Truncate”, “Comment”, and “Rename”.

“DQL statements” refer to a statement, which is used to perform any queries within the “schema objects”, based on the data. “DQL” can be defined as a major component of the “SQL statement” that can ensure to get data from the overall database and can impose several orders upon it. The “Select” statement is included within it and this command can be used to allow the data to be gathered from the database for performing operations within this system, as per user’s requirements (IBM, 2021). When the command ‘SELECT’ is used against a specific table or some tables, then the result will be shown in a compiled format within another temporary table. This will either be displayed or will be received by the actual program called “a front end”. The “SELECT” command within the “SQL statement” is used for retrieving data from the created database.

Apart from this, the statement that is used within the “SQL system” to manipulate the data, which is present in the existing database is called “DML” and this specific language consists of most of the “SQL statements” (Medium, 2021). This can be defined as the components of an “SQL statement”, which can be used for controlling the access to the data and the entire database system. Actually, the “DML statements” within “SQL” are combined with “DCL statements”. Some of the “DML Commands” are as follows:

“Insert”

“Update”

“Delete”

“Lock”

“DCL” is another “SQL statement” that is used for giving the rights and permission to the respective users of the system to get access to the database. DCL consists of some commands such as “Grant” and “Revoke” (Medium, 2021). The command “Grant” is used within the SQL system to give the user the permission of accessing the database. On the other hand, the command 'Revoke’ is considered to withdraw the privileges given to the users for accessing the database with the help of “Grant” command. As a result, it can be observed that these two commands are opposite commands to each other. For example, within a University students sometimes are not able to access several information presented within their college portal. However, they claimed that their respective faculties could do so (Medium, 2021). The reason behind this is, the permission to access the data into the university portal has been given to the respective faculties through “Grand” command in “DCL” by the admin (Medium, 2021). The permission given to the admin for accessing the data can also be removed from them, by using the command “Revoke” by the admin. Even the students can get the access to the data, if the admin generates them permission by using the ‘Grant” command.

Relational schema

A “relational schema” refers to a blueprint that is incorporated within the design of the database, in order to respect the data that will be entered into the database. This is also used to describe the way to structure the data into a table. A relational schema defines the relation between those tables. It can be defined as an outline for identifying the process that is adopted by any entity or business to store and manage their data into a database. “Relational database schema diagrams” include different types of elements such as “Tables”, “Attributes”, “Relations”, “Primary keys”, “Foreign keys”, in order to make a clear as well as concise database for storing essential data (Indeed, 2021). The fundamental component of a “relational schema diagram” is the tables, and these are actually a set of records. Moreover, it needs to be stated that a “data scheme”, for example a “relational schema”, is required to impose several restrictions on the overall structure of type data that is required to be stored (Chillón et al. 2019). As a result, a proper “schema diagram” needs to be prepared for developing a specific and systematic structure for the data before storing it. A database without “relational schema diagram” can have different structures, for data that is stored for any specific entity.

SQL query

Sports club members can view their current booking

Figure 5: Query 1

Display current weekly activity for the sports club

Figure 6: Query 2

Most active members monthly activity

Figure 7: Query 3

The query is referred to as a request for the information on the data that is involved from the database tables or else having a combination of the tables. The data that can be generated as to the result that needs to be effectively returned by Structured Query Language (SQL) method. The complex results, graphs, pictorials and others are involved with the trend analysis and the data mining tools for the query table. As suggested by McWhirter et al. (2018) the SQL command can be run in any Microsoft excel page and to execute any specific statements can be selected run with the command. This can be managed with selecting the databases and accepting the defined values in relation to the optional steps has been defined. The involvement of the databases into the management system is the need and cohesive development is to effectively develop and measure with the by product of the query.

This query can be used in order to execute a large amount of the data from a specific database with the involvement of various commands. As stated by Borisova and Zein (2021) the advantages of using this query tool can be used as to have an easy to learn method and having almost every DBMS supporting the SQL tools. This is a database tool that is useful and efficient with the use of a large amount if the coding requirement. This is to informant critically reflected over the process and the management of the long databases and the extraction process. The coding that is required are shorter in relation to other tools that have been used for the approach (Mukhtar and Azer, 2020). Therefore, these can be an effective measurement to the voice of the certain tools to execute the process of the SQL use for the data calculation process.

Furthermore, there have been certain issues that are related with the SQL involvement of the server to the performance issues. As suggested by McWhirter et al. (2018) the queries that usually take a longer time to the usual processing if the data that needs to be extracted. On the other hand the data bases those faces and out if the space errors that need to be deleted and maintained with the proper server amounts. Moreover the permissions are being denied with the grant and the denial of the stored process and tier effects. Therefore, it is necessary for the objectives and their client to lose in the connectivity development for the SQL server. Thus, the status of the SQL server has been in need for the quality measures and the data extraction of the major points as well. At the same time, it provides critical choices and their major developing areas that can be effective with the voice if certain growth and development about the data that need to be extracted. It is being effectively managed with the tools and the involvement of the standard out time that need to be suffered with the choice if certain action required. Hence, it can be stated and concluded that the query tool for the SQL process is the need for the data analysis sources and their major developing areas.

Conclusion

Based on the above analysis, the project has shown the development of sports record management system development is a complex process to control different activities and events. The study has shown layout design and interface architectural views management is a complex procedure for managing different types of challenges of SCRS development. Proper ideas and concepts regarding design and development of SCRS have been required for in cooperate sport member information and their activities with five sports club with the SQ Workbench. Therefore, it has seen different types of methods and tools are used in proper implementation of strategies and data governance methods for sustainable coding and framework development. Different type difficulties and risks of data implementation to configure advanced systems for sports club members’ data management.

In order to influence the effectiveness and efficiency of real time data processing of members data, user case diagram have been used to make interconnection and UI design of system. Moreover, Entity Relationship Model (ERM) has been utilised in the development process to develop user friendly ER diagram to control critical aspects of SCRS development phases for sustainability management. The process of data table and graph development framework has been discussed in the methodology sections to manage the data analysis framework. Therefore, SQL statements and editors have been used to evaluate database connection and tables for the membership data management for the club authorities. Relation Schema diagram has been developed using the SQ Workbench to address different types of interrelation among members information with club objectives in challenging activities management. Furthermore, SQL query and administer environment have been utilised in the above report to analyse performance level of SCRS systems in data migration and visual performance management. SQL workbench has different types of advantages and for that purpose this software has been identified.

Reference List

Al-Obeidallah, M.G., Petridis, M. and Kapetanakis, S., 2018. A multiple phases approach for design patterns recovery based on structural and method signature features. International Journal of Software Innovation (IJSI), 6(3), pp.36-52.

Bogusławski, A., Veselý, P., Husenicová, L. and Čupka, O., 2022. Creating Database Models in Rational Data Architect. In Developments in Information & Knowledge Management for Business Applications (pp. 731-766). Springer, Cham.

Borisova, S. and Zein, A., 2021, September. Disadvantages of Relational DBMS for Big Data Processing. In International Conference for Information Systems and Design (pp. 279-290). Springer, Cham.

Cheng, L.C. and Chu, H.C., 2019. An innovative consensus map-embedded collaborative learning system for ER diagram learning: Sequential analysis of students’ learning achievements. Interactive Learning Environments, 27(3), pp.410-425.

Chillón, A.H., Ruiz, D.S., Molina, J.G. and Morales, S.F., 2019. A model-driven approach to generate schemas for object-document mappers. Ieee Access, 7, pp.59126-59142.

Ellison, M., Calinescu, R. and Paige, R.F., 2018. Evaluating cloud database migration options using workload models. Journal of Cloud Computing, 7(1), pp.1-18.

IBM, 2021. How to read E-R diagrams. Viewed on: 03/05/2022. https://www.ibm.com/docs/SSGU8G_12.1.0/com.ibm.ddi.doc/ids_ddi_179.htm

IBM, 2021. Netezza SQL functional categories. Viewed on: 03/05/2022.

IBM, 2021. Use-case diagrams. Viewed on: 03/05/2022.

Indeed, 2021. Relational-database-schema. Viewed on: 03/05/2022.

Javatpoint, 2021. DBMS-relational-model-concept. Viewed on: 03/05/2022. https://www.javatpoint.com/dbms-relational-model-concept

Javatpoint, 2021. UML Use Case Diagram. Viewed on: 03/05/2022.

McWhirter, P.R., Kifayat, K., Shi, Q. and Askwith, B., 2018. SQL injection attack classification through the feature extraction of SQL query strings using a gap-weighted string subsequence kernel. Journal of information security and applications, 40, pp.199-216.

Medium, 2021. SQL-Statements. Viewed on: 03/05/2022.

Mukhtar, B.I. and Azer, M.A., 2020, December. Evaluating the modsecurity web application firewall against SQL injection attacks. In 2020 15th International Conference on Computer Engineering and Systems (ICCES) (pp. 1-6). IEEE.

mysql.com, 2022. Workbench, Available at: https://www.mysql.com/products/workbench/ [Accessed on 2 May 2022]

Navarrete, C.B., Malverde, M.G.M., Lagos, P.S. and Mujica, A.D., 2018. Buhos: A web-based systematic literature review management software. SoftwareX, 7, pp.360-372.

Sussesx, 2021. What is SQL and what's it got to do with Microsoft Access ?. Viewed on: 03/05/2022. <://www.sussex.ac.uk/its/help/faq.php?faqid=897>