Familiarizing with DMSs & SCMS Database Assignment Sample

Introduction Of Familiarizing with DMSs & SCMS Database

The SCMS database assignment focuses on a programme used to organise sports clubs. There are four primary tables in the SCMS database: attendance, employee, sports information, and session. Users, attendance, personnel, sessions, and sport specifics are all stored in separate tables, indicating that the database is likely used as part of a larger sports centre administration system. Relationships between tables may be established with the use of primary and foreign keys, both of which are included in the database dump. In this report, the SCMS's relational data architecture, practise strategies to normalise the database to 3NF, and build an Entity Relationship diagram to visualise the interdependencies between the tables will be analysed. The objective of this project is to familiarise with DMSs and their practical applications.

Tight deadlines leaving you anxious? Turn to New Assignment Help for prompt assistance! Our experienced team provides unmatched assignment writing services in the UK, helping you achieve academic excellence. Check out our free assignment samples for a taste of our expertise.

Description of the database

A sports club management system have been designed for this project. It is named as SCMS and it only consists of only 4 tables namely attendance, employee, sports details and session. There are columns for users, attendance, workers, sessions, and sports information, suggesting that this database is part of a sports centre management system (SCMS). People who have signed up for the fitness centre are probably recorded in the users table. Users' attendance is recorded in a "attendance table." workers' information, such as management, training, and administrative statuses, are stored in the workers table (Kuyumdzhiev, 2019). Information on scheduled sports sessions, including session title, day, time, and employee in charge, seems to be kept in the sessions table. Last but not least, the sports information table seems to connect customers to their scheduled sports classes. The SQL dump's foreign keys define the connections between the tables. It seems that the userID column is utilised to connect the tables for users, attendance, and sports information. The eID column connects the two databases of workers and sessions. The sessions and sports information tables are connected via the sID column.

Relational data model

Tables, columns, and connections are the fundamental building blocks of the relational data model, which is a method for arranging data in a relational database management system (RDBMS). In this model, the data is structured into one or more tables, where each table represents an entity and each row in the table represents an instance of that object (Ogli, 2022). This model may accommodate any number of tables.

A different quality or characteristic of the item is denoted by one of the columns in the table. For instance, in the database dump that was supplied, the tables represent entities such as "employee," "session," "attendence," and "sports details," and the columns reflect characteristics of those entities such as "eID," "F_name," "L_name," "Function," and "session_title," "session_day," and "sports selection."

The usage of keys, which are used to connect relevant data across databases, is what is responsible for establishing the relationships that exist across the tables (Gillenson, 2023). The given database dump contains tables that include primary and foreign keys, which are necessary for the database management system to build and maintain the relevant connections between the entities. In a similar fashion, the "session" table includes foreign keys called "userID" and "eID" that connect it to the "users" database and the "employee" table, respectively. Both the "attendence" and the "sports details" tables each have their own set of foreign keys that connect them to other tables in the database.

The relational data model offers a method that is both flexible and efficient, making it possible to store and retrieve vast quantities of data in an organised fashion (Chan etal, 2023). This is accomplished by organising the data into tables and creating links between the tables. It is used extensively in a wide variety of applications, ranging from low-scale systems to big enterprise-level databases and everything in between.

Techniques to data normalisation for 3NF

• Determine which columns rely on other columns by analysing the connections between the columns in the tables and identifying the functional dependencies.
• First normal form (1NF) states that it should always have a primary key and that null values should never appear in any column of a table.
• Use the second normal form (2NF) and make sure that all characteristics other than the main key are 100% reliant on it (Alsobhi and Alshareef, 2022). Attributes that are only conditionally reliant on the main key should be moved to a separate table connected to the primary key table through a foreign key connection.
• Use the third normal form (3NF) and check that no non-key properties are reliant on the main key in any way. It is recommended to create a new table with a foreign key connection to the old table if any attributes are transitively dependent.
• Determine whether there are any transitive dependencies by looking at the connections between the table columns (Rawat and Purnama, 2021). Transparently dependent columns should be copied to a new table.
• One-to-one related tables without duplicate information may be merged into a single table.
• If it is needed to boost query performance, denormalizing the database is one option to consider.

The database may be normalised to 3NF using these methods, guaranteeing that all data is consistent and that any outliers have been removed.

Requirements specification

Conceptual design

The above figure represents the ER diagram of the sports club management system. The entity relation diagram may be generated in the manner described below by using the SQL Workbench. This Entity Relationship diagram will illustrate how the various data stores that make up the sports club administration system are linked with one another. An Entity Relationship Diagram, often known as an ER Diagram, is a diagram that may be used to visually illustrate the information and relationships that exist between the various parts of a sports club administration system. The process of data modelling is used to assist in gaining an understanding of the structure of the system, which may consist of a wide variety of entities, attributes, connections, and constraints.

The SCMS database has an attendance table, which is shown in the above figure. The userID and attendance fields may be found in the attendance database. Foreign keys in the users database are stored in the userID column, which is a string with a maximum length of 20 characters. The attendance enumeration used in this field can only take the two potential values of "P" and "A."

The image that is represented in figure 3 shows the SCMS database table for the employees. The eID, F_name, L_name, Function, mobile_number, and userID fields make up the employee database. The eID column, which contains a 20-character string, serves as the primary key. Both the F_name and L_name columns include strings of length 45, which reflect the employee's first and last names. Only the words "management," "trainer," and "admin" may appear in the Function column, since it is an enumeration. The employee's mobile phone number is stored in the mobile_number field as a string of length 45. This column's userID is a foreign key to the users table's userID column.

The above figure represents the session table that is present within the SCMS database. sID, session_title, session_day, timing, userID, and eID in the session table's fields will be found. The sID column is the main key and is a 20-character string. The session's title is stored in the session_title column, which is a string of 200 characters. The date of the planned session may be seen in the session_day column. The session time is recorded as a string of length 45 in the timing column. This column's userID is a foreign key to the user’s table's userID column. eID is a foreign key that points to the corresponding field in the employee’s database.

The above figure representts the Sports details table that is present within the SCMS database. The userID, sID, sportsID, sport selection, and time slot are the columns that make up the sports information table. The userID column in the users table is a foreign key, a string, and it has a length of 20. The sID column in the session table is a string of length 20 and serves as a foreign key to the sID column there. The sportsID column is a foreign key to the sportsID column in the sports_enrollment database, and it stores strings of up to 20 characters in length. There are just three possible values for the sports choose list column: yoga, badminton, and football. There are just three possible enumeration values—"6-8," "8-10," and "10-12"—for the slotted column.

Conclusion

Finally, the Sports Club Management System database is an essential resource for running a successful sports club. There are four main tables in the system, each with its own set of columns and relationships: attendance, employee, sports information, and session. The relational data architecture used here allows for the convenient and quick retrieval of massive volumes of data. Data normalisation procedures make sure that all of the numbers add up and that there are no outliers. By depicting the database's structure and interactions graphically, the ER diagram clarifies how the system is built. The Sports Club Management System database is an effective resource for running a sports club and satisfying members' needs.

Personal reflection

During the process of developing this relational model, I ran into a number of challenges, the most significant of which was the length of time required to configure a SQL server. Due to the restricted availability of secondary sources of information, this study required a considerable amount of time. Nevertheless, by conducting exploratory research and making cognitive decisions, I was able to finish all of the primary requirements, such as creating the relational database model, ER diagram, and normalisation. It is important to point out that having a database of either fictitious or actual pupils might have been helpful for arriving at conclusions much more quickly.

References

• Alsobhi, H. and Alshareef, R., 2020, September. Sql injection countermeasures methods. In 2020 International Conference on Computing and Information Technology (ICCIT-1441) (pp. 1-4). IEEE.
• Chan, S.C., Chui, S. and Karczmarski, L., 2023. Application of multi-species photo-ID database management systems: a user’s perspective. Mammalian Biology, pp.1-11.
• Elkins, G.E. and Ostrom, B., 2021. Long-term pavement performance information management system user guide (No. FHWA-HRT-21-038). United States. Federal Highway Administration. Office of Infrastructure Research and Development.
• Gillenson, M.L., 2023. Fundamentals of database management systems. John Wiley & Sons.
• Kuyumdzhiev, I., 2019. Comparing backup and restore eficiency in MySQL, MS SQL server and MongoDB. International Multidisciplinary Scientific GeoConference: SGEM, 19(2.1), pp.167-174.
• Ogli, R.A.R., 2022, February. The difference between the concepts of database and database management system. In Archive of Conferences (pp. 33-34).
• Rawat, B. and Purnama, S., 2021. MySQL Database Management System (DBMS) On FTP Site LAPAN Bandung. International Journal of Cyber and IT Service Management, 1(2), pp.173-179.