Design and Implementation of a Database System for Event Management Customizer (EMC): A Case-Based Approach

1. Introduction

The effective management of data is critical in today’s dynamic business environments, where organizations must store, retrieve, and process vast amounts of information with precision and efficiency. Database systems provide a structured approach to managing this data, offering a platform for systematic storage, seamless retrieval, and efficient manipulation [1,2,3,4]. Specialized businesses, like event management firms, rely heavily on efficient, scalable database systems due to their complex client interactions, multifarious service offerings, and real-time decision-making processes associated with the operations [5,6,7,8,9].

This assignment aims at designing and implementing a complete database system for Event Management Customizer, a start-up dealing in adventure holidays and team-building events. The case study of EMC represents a practical scenario where database systems are used to manage customer bookings, accommodation, outdoor facilities, and financial transactions. The aim of the project is to enable EMC to smoothen its operations, enhance data accuracy, and facilitate strategic decision-making through an efficient and scalable database architecture.

The project follows a strict design methodology, starting with the development of an ER model that outlines the logical structure of the database. In the ER diagram, there are eight interconnected entities: Client, Representative, Booking, Accommodation, Outdoor Facilities, Additional Facilities, Payment, and Staff. These represent the main operational aspects of EMC and include a wide range of attributes such as key, composite, multivalued, and derived attributes. For instance, attributes like “Representative_Name” (composite) and “Stay_Duration” (derived) are examples of how the database can aggregate and compute information relevant to the business processes of EMC [10,11,12,13,14,15,16].

The practical implementation of this design is done on MySQL, which is one of the most popular relational database management systems acknowledged for robustness and scalability [17,18,19,20]. The capabilities of MySQL are elaborated with the help of SQL queries to address some complex organizational needs like the generation of detailed client profiles, service utilization calculations, and analysis of financial performances. Additionally, the database system includes features to manage outdoor and additional facilities, ensuring that EMC can offer tailored services to diverse client groups, including families, schools, businesses, and governmental institutions [21,22,23,24,25,26].

A unique challenge addressed in this project is the global impact of the COVID-19 pandemic, which necessitated modifications to the database schema. Attributes like vaccination status and quarantine records were introduced to enhance health and safety compliance, while additional tables for room disinfection schedules and temperature checks ensured comprehensive monitoring [27,28]. These modifications demonstrate the database’s adaptability to evolving business requirements and external factors.

Database systems are not just tools for storage but also pivotal enablers of data-driven decision-making. As highlighted in prior research, databases support key organizational goals such as improving operational efficiency, reducing errors, and fostering strategic planning [29,30]. They also play a vital role in ensuring data security and regulatory compliance, particularly in industries like event management where client information is sensitive [31,32,33].

Therefore, this project epitomizes the design and implementation of a robust database system that suits every requirement of EMC for its operation processes. It narrates how a database could work as a strong tool for an organization toward growth by proving to be the basic platform for expansion, durability, and innovation regarding challenges. The EMC database system, by using conventional database design principles in combination with modern technology, raises the bar for applied deployment of database management within the event managing sector.

2. Literature Review

Designing and implementation of the event management database system are critical in terms of scalability, functionality, and user-oriented operations. The literature review has shown various developments and challenges concerning the subject matter. Also, integrated approaches have been identified using event management systems in handling these multifarious operations of booking, scheduling, and reporting, with database normalization playing a pivotal role in reduction in redundancy and enhancement in efficiency [34]. Similarly, relational database designs focusing on users have been propounded to enhance access and facilitate the operations for small and medium enterprises [35].

Cloud-based solutions have gained traction for their ability to scale dynamically and handle large volumes of data, particularly during high-traffic periods like event registrations [36]. The integration of Big Data analytics has further enabled personalized event planning by analysing attendee preferences and predicting trends based on historical data. Moreover, mobile databases have facilitated real-time data access and synchronization across devices, enhancing operational efficiency in event management.

In terms of flexibility, NoSQL databases have emerged as viable alternatives to relational databases, especially for managing unstructured data like multimedia associated with events [37]. Customizable database structures are particularly useful in accommodating diverse client requirements, making them ideal for event planning systems [38]. However, data security remains a pressing concern, with studies emphasizing the need for robust encryption and access control protocols to safeguard sensitive event data.

For example, there are several proposed techniques aimed at improving the performance of an event management database, including query optimization and indexing. Similarly, various data management challenges involving both structured and unstructured data have been provided for hybrid database systems, integrating SQL and NoSQL technologies. This workflow automation will reduce manual work through automated functions for repetitive tasks such as check-ins enabled by triggers and stored procedures within the database [39].

Scalable architectures have been proposed to address surges in user activity during large-scale events, ensuring that databases remain responsive under heavy loads [38]. Entity-relationship modelling techniques have been explored for designing efficient and logical database schemas, which are crucial for managing complex event data. Feedback management systems supported by databases enable the collection and analysis of attendee feedback, which is vital for post-event evaluations. Integration of Internet of Things technologies into event management systems enables data collection and its monitoring in real time, regarding venue occupancy or environmental conditions [39]. Integration of data, however, has become a big issue in consolidating several data sources coming from different locations into one cohesive system. Real-time databases that require fast data processing capabilities remain very crucial for live update management related to ticket sales or registrations [40].

Artificial intelligence (AI) has played a transformative role in event database management, improving decision-making and predictive analytics for efficient resource allocation [40]. Microservice architectures, which enhance modularity and scalability, ensure that each service operates independently while sharing a unified database. Lastly, the development of testing frameworks for validating database accuracy and reliability has been highlighted as critical for ensuring system performance [41]. This review comprehensively captures the challenges and advancements in designing and implementing database systems for event management. These synthesized studies bring out the fact that scalable, secure, and user-centric solutions are needed to cater to the diverse and dynamic requirements of event management applications. Future research should be directed at refining the existing methodologies and exploring innovative approaches to address the evolving needs of the industry. Future research in event management systems could explore ANN-based models for enhanced security and real-time processing [42], sentiment analysis for predictive insights [43,50], and deep learning techniques for classification and prediction [44,49]. Integrating IoT with 5G technology could further improve system responsiveness and efficiency [45,47]. Additionally, addressing cybersecurity and big data growth remains essential [46]. Experts are also focusing on power generation predictions and anomaly detection using advanced models [48,51].

3. Case Study: Event Management Customizer (EMC)

MC operates a start-up centre in Subang Jaya and intends to redesign its business database to store data about its services and clients efficiently and manage them appropriately. This is a centre with focused interest in providing its customers with adventure-packed holidays as well as teambuilding programmes. Their clients can have just accommodation or team-building programs or both kinds of services according to their goal and preference. The clients will be grouped as family groups, businesses, governmental institutions, and schools. Information to be stored about each client is a unique client number to identify the client, the client’s name identifying a school name, business name, or organizational name, and the contact of representatives. In schools, governmental institutions, and businesses, additional contacts are supplied for smooth follow-up and continued marketing.

4. Proposed Methodology

The methodology of designing and implementation of the database system for EMC is in a structured approach. The broad steps are highlighted below.

4.1. Requirement Analysis

Identify business needs and identify the data requirements concerning client management, booking processes, tracking facilities, and financial management. Classify data into logical categories such as clients, representatives, accommodations, bookings, facilities, payments, and staff.

4.2. Entity-Relationship (ER) Modelling

Draw an ER diagram that captures the logical structure of the database. The diagram looks at interconnected entities: Client, Representative, Booking, Accommodation, Outdoor Facilities, Additional Facilities, Payment, and Staff. Define key attributes and composite attributes for these entities and define how these entities relate to one another.

4.3. Physical Modelling

Translate the ER model into a physical database schema using MySQL. The physical model represents-in a relational form-the logical structure through the use of tables, keys, and constraints.

4.4. MySQL Implementation

The implementation is done in MySQL through the creation of tables, relationships, and subsequently filling the database with sample data. It also includes stored procedures, triggers, and indexes that optimize data operations and automate repetitive tasks.

4.5. SQL Query Design

Provide SQL queries to fetch business information, such as client details, managing bookings, financial reports, and facility usage analysis. These include advanced queries with aggregation, calculation, and conditional logic.

4.6. Testing and Validation

Perform accuracy, integrity, and performance tests on the database by writing test cases for scenarios ranging from high volumes of transactions to error conditions.

4.7. COVID-19 Pandemic Management Enhancements

Add attributes and tables in each level of the hierarchy to manage COVID-19-related data: vaccination statuses, quarantine records, and schedules of disinfections of rooms. This will make sure the database is updated to handle real-world challenges.

Figure 1 shows the conceptual representation of the database structure for EMC, showing the interconnected entities: Client, Representative, Booking, Accommodation, Outdoor Facilities, Additional Facilities, Payment, and Staff. The diagram shows key, composite, and derived attributes and their relationships. Figure 2 shows the detailed physical schema of the EMC database derived from the ER model. It shows the relational tables, keys, and constraints implemented in MySQL for efficient data management and optimization of operations. Figure 3 shows the Representative Table, The structure of the Representative entity in the EMC database. This comprises attributes such as Representative_ID, Representative_Name, and Contact_Details for managing the representatives of clients. Figure 4 shows the Information from clients includes Client_ID, Client_Name, Client_Type-family, business, or school-with attendant contact information for accurate client management in Client Table. Figure 5 shows the depicts the structure of the table used to manage the financial transaction that was involved, including but not limited to attributes like Payment_ID, Booking_ID, Payment_Amount, and Payment_Date for traceability of all kinds of payments in Payment Table. Figure 6 shows the accommodation table depicts details on Accommodation_ID, Room_Type, Capacity, and Availability_Status attributes meant to address various clients’ needs. Figure 7 shows the additional Facilities Table, Represents the structure of additional facilities available, including attributes like Facility_ID, Facility_Name, Facility_Cost, and Availability. Figure 8: Booking Table A broad table for bookings, including the attributes Booking_ID, Client_ID, booking_ Date, and Total_Charges, to monitor transactions and account for resource distribution. Figure 9 shows the Staff Table which Captures staff-related information, with attributes such as Staff_ID, Name, Certification, and Assigned Duties, supporting workforce management and performance tracking. Figure 10 indicate the Outdoor Facilities Table. The structure of outdoor facilities offered by EMC, along with their respective attributes like Facility_ID, Facility_Type, Usage_Cost, and Booking_Status, efficiently manage the usage.

s

s

5. Results (Queries)

In this sections we perform the SQL queries to test the system

Writing SQL query to list down total number of rooms that are of type dormitories.

Computing an increase of 6% on booking charges for all the facilities. Upon having the increased price now compute the raw income that would be achieved in 2023 using the new price.

Providing a report that indicates the total number of booking closed by staff in the year 2022.

Identify from the system the staff that have closed the highest sales for the year 2022.

Finding the total number of booked outdoor facilities for each accommodation:

1. Query to list age groups of staff

2. Query to list the 10 representatives with highest number of clients

3. Query to list the sum of payments done by clients of each Client_Type

4. Query to show the number of staff by certification and average age of staff holding each certification

Following is a set of queries that work out specific data and provide necessary insights into business outlooks, operational efficiency, and financial evaluation, among other facets that are stored in the EMC database. The first five of them are discussed below:

Query 1: Retrieval of high service transaction in states or towns

This query lists the businesses that have had the most service transactions, grouped by state or town. It summarizes facilities and services booked by businesses to show the level of activity and preferences of clients by region. Such information enables EMC to target high-value clients in certain areas and to develop marketing and enhance its services accordingly.

Query 2: Categorizing Clients by Type with Representative Details

This query categorizes clients into business, school, and youth groupings while fetching names and contact numbers of their representatives. It further enhances the management of clients through a clear outlook on the types of clients and their contacts. Such information aids in targeted communication approaches and follow-ups effectively.

Query 3: Counting Dormitory-Type Rooms

This query calculates the sum of all rooms within the database classified as dorms. The result will help EMC to estimate, in total capacity terms, about hosting, specifically large groups of people like schools or companies. This will also be of assistance when planning enhancements or alterations based on this demand.

Query 4: Anticipated Revenue Given an Increase in the Facility Charge

This query calculates a 6% increase in the booking charges of all facilities and develops the forecasted raw income for 2023 based on its updated prices. In this way, EMC will be able to understand the financial impact of changing the price and plan future pricing.

Query 5: Staff Booking Performance in 2022

This query will report the total number of bookings closed by each staff member in 2022, identify high-performing employees, enable performance evaluation, and give insights into the extent of reward allocation. The data also displays operational efficiency with regard to resource utilization in a staff team. These queries thus put together a strategic framework for analyzing client behavior, optimizing resource management, and forecasting financial outcomes to aid informed decisions and operational improvements for EMC.

6. Discussion

Due to the pandemic, several preventive and monitoring measures had to be put in place for the safety of both staff and clients. Some of the major measures include adding the “Vaccination Status” attribute in the Staff and Client tables. This will include information such as the number of doses taken by the staff and clients, and the date of vaccination completion to calculate the percentage coverage of the whole vaccination course within the institution. This will help companies maintain better control, especially in decision-making about whose cases are putting people at a high risk of either being infected or being an infecting agent.

This is further supported by the introduction of a new table named “COVID_Test_History”. This table will keep a record of relevant information concerning testing dates, the results of staff and clients tested, whether positive or negative, and subsequent measures taken. In this regard, the table “Quarantine” will be created for those who are positive to capture the quarantine dates and times. The relational structure separated staff and client health data, accordingly, creating appropriate linkages done through foreign keys such as the “Test_ID” in the Quarantine table to show clear, organized testing and quarantine history.

Another critical measurement would be the disinfection of the rooms and all those places, including lobbies, to which one is exposed. There would also be a “Rooms” table that tracks room occupancy with a column indicating whether the occupant tested positive or not, so the company knows which room needs cleaning and disinfection after such person has left. For keeping track of disinfection, the “Disinfection” table will be provided with a list of disinfection schedules in operation for all areas, ensuring that rooms and spaces are kept germ-free between use. Temperature checks of staff and clients alike will also be vital in the monitoring of health status. Two different tables, “Staff_Temperature_Check” and “Client_Temperature_Check,” respectively, will be used to store regular temperature checks during staff inspections and upon client entry. Businesses can quickly identify symptomatic individuals by recording the temperature readings and the date and time these checks are performed and take further action like restricting access to the premises or encouraging testing.

Thirdly, the pandemic is very likely to reduce the consumption of outdoor and extra facilities, which may cause temporary suspension or layoffs of part-time workers. In such a perspective, it reveals that the pandemic influences businesses financially and operationally, as such businesses must manage the headcount of employees and available resources. The tables proposed here not only help track public health data but also are instruments to adjust resource utilization optimally in insecure times. In short, the suggested data structure will bring a sound framework to bear in monitoring and mitigating the impacts of COVID-19 in a business setting. It includes vaccination status, test history, quarantine records, room occupancy, disinfection schedules, and temperature checks, all important features for a business to take effective action on the pandemic without affecting the health of staff and clients. However, businesses must constantly adapt and fine-tune these measures as new challenges emerge, specifically with the dynamics of the pandemic and its ongoing variants.

7. Conclusions

In conclusion, from this assignment, we learn that the development of a Database System is first identified through the requirements needed, followed by creating an ER diagram which will be acting as a blueprint for developing a physical model and, in the end, use it to design our MySQL database where we input all our records. We also learned the foreign keys that link entities with each other; for every entity, the creation of tables will be done in MySQL, and the relationships along with cardinalities in both Physical and ER Diagrams. Now, we actually did some Part C—simple MySQL commands we used and tried to delve into what was going on conceptually behind the commands. We then applied the said knowledge to develop five custom complex queries in Part D. In Part E, we discussed how our database system could be affected by the global COVID-19 pandemic that began in late 2019 and the changes that would be necessary for the system to remain efficient during the pandemic. With that, we concluded our assignment.