as attached

The importance of a database can be very misunderstood. Databases create a great snapshot of how your business performance for example, when we go to the car mechanic to get your typical maintenance sometimes you may notice the mechanic hooking a computer up to your car’s internal computer. The computer gives raw data back to the mechanic on the engine’s performance and other indicators he/she may be looking for that is not visibly shown on the dashboard/touchscreen monitor. Database for the most part does the same thing. As noted, “Databases are a collection of organized information that can easily be accessed, managed and updated. Database systems are very important to your business because they communicate information related to your sales transactions, product inventory, customer profiles and marketing activities. Without you having a centralized place to store all this information, you have no clue what is occurring within your business. Without knowing the actual details of your business, business owners make assumptions, and assumptions are usually based upon opinions rather than facts” (Importance of Data in Your Business n.d.)

With the establishment of a database D.R.I.P. Boxing and Performance Academy will be able to track some of the key elements of business and that is customers/clients, appointments, billing, and employees. This will have an immediate impact on the foundation or base for the business, thus resulting in efficiency and results.

So, what’s next?

Scope of Work: Planning, development, and implementation of database for real time feedback of operations integrity. Focus: Customers/Clients, appointments, billing, and employees.

Estimate: $6000-$8000. These are costs of hardware and software needed to create and store data.

Length of Work: 1 Month is needed to complete this project for the small business. Complete installation of hardware and software.

Justification: As mentioned above it is critical that an establishment implement efficient and accurate tracking tools to highlight the health of the business.

This section presents a high-level view of the proposed database schema. The analysis will focus on the entities that will be used in the design processes and their relationships. Similarly, the project will provide a high-level overview of the data, variables, and requirements that will be used in resolving the underlying problem. On the same note, the analysis will justify the design and its significance in resolving the current issues presented through the intended goals (Elrefai et al., 2017). The system design is represented in the conceptual diagram below, which captures the entities and their relationships.

Figure 1. Entity Relationship Conceptual Model for DRIP Boxing and Performance Academy.

DRIP Boxing and Performance Academy is a training facility that intends to build a high-level system to manage its daily operations. The facility, through the proposed system, will capture details about the employees, appointments, buildings, and consumers. These stakeholders and variables are the primary focus for the development of the proposed system. The proposed solution will be created and implemented in several entities. The primary entity is the client. The client is the consumer whom the system is built around. The second entity is the employee. The employee works at the gym, offering diverse services. This section will assume that the employee is the trainer who engages the client in their daily operations. The third entity is the appointment. An appointment is an agreement between the trainer and the client through the gym. The fourth entity is the location or the building where the appointment occurs. The last entity to include in this system is a date. Each appointment is governed by a schedule. These schedules are influenced by the appointment data, which facilitates resources allocation across the employees.

The rationale for selecting the proposed model is based on the ability to capture information about the employees, clients, and the appointment processes. These processes and parties form the foundation for the operations of the proposed system and the gym as a whole. The system will resolve the current problem with managing appointments for the clients through strategic scheduling. The trainers or employees will use the system to automate their meetings with the clients. Similarly, the system will collect and capture client information as a foundation for the scheduling processes. This design will resolve the current issues that the gym faces by automating the scheduling process. The gym will use the system to capture client details and needs while matching them with the available trainers. The trainers will be scheduled through appointments with the clients at the available spaces to facilitate the overall training exercises (Szczepaniuk & Szczepaniuk, 2019). Since the system`s success depends on capturing the right information about the clients and employees, it will require employee and customer details as the ultimate inputs.

In software engineering, developers undertake numerous tasks which allow them to create solutions that meet the increasing consumer needs. The software development process starts with idea determination and requirements gathering. This stage is vital because it collects the various factors, variables and conditions needed to meet consumer demands. The previous submission provided the problem facing DRIP Boxing and Performance Academy, which showed the need for a new system to foster client management throughout their interactions with the relevant stakeholders. The proposed system comprises three primary tables and entities: the client, trainer, and date. However, the system will capture and store additional details to meet the expected standards effectively (Domdouzis, Lake & Crowther, 2021). Therefore, the developer will include two additional entities: the schedule an appointment for the system to achieve the intended outcomes. The rationale for including these entities is to cater to the many relationships between the client and the trainer. Since many trainers can train clients at different intervals, the schedule creates a segmented plan for each customer. The underlying appointments control this segmentation. Similarly, each trainer is responsible for many clients, which may happen simultaneously or at different intervals. Therefore, the clients' book appointments with their desired trainers where the latter include them in a schedule based on the dates.

The diagram below presents the entities, attributes, and relationships. These entities are connected based on their relationships. The ultimate decisions made concerning the presentation of these entities is based on the normalization of the attributes. These entities are normalized to 3rd normal form to eliminate data redundancy. Redundancy affects the capacity to achieve reliability when storing data from related tables and entities (Thalheim, 2020). Also, redundancy takes up more space from the underlying databases, making it hard to optimize the performance of the resulting system. Therefore, it is vital to appreciate the idea that normalizing the database to the 3rd form is a design recommendation for promoting operational efficiency and enhancing the reliability of the resulting solutions.

Figure 1. Normalised Database Design for the Academy.

Normalization is a process through which database designers and developers identify the entities, attributes, and relationships to ensure maximum efficiency and optimization. The first step in normalization is identifying the entities in the context. This stage allows the developers to determine the required attributes that will best match the needs of the proposed systems. Similarly, this step will collect vital information about the attributes and systems. The second step is to determine the attributes common in the selected entities. These entities must contain information that is unique to their functions and purpose. The last stage is to remove attributes that can form another complete entity. In the first normal form, the attributes in the selected database must have unique characteristics.

Also, the order in which the database stores its information is not a major determining factor towards the underlying outcomes. In the second normal form, the primary focus is to remove attributes that partially depend on each other (Merunka, Wijekoon & Shegolev, 2019). The third normal form ensures that the attributes in the context do not have a transitive dependency. These stages provided a foundation for improving awareness and ensuring that the resulting database stores unique values and that operations like deletion and insertion will not be affected based on the relationships in the context. The primary problem that may be witnessed with the failure to implement a database in the third normal form is that operations like deletion are likely to be affected by the underlying dependencies. For example, it is difficult to delete a record that depends on multiple tables' records. Therefore, normalization ensures efficiency in dealing with these operations and the effectiveness of the underlying databases.

Create database Gym;

Use Gym:

CREATE TABLE client (ClientID int (12), not null PK

Cname varchar (255) not null

ccontact varchar (255) not null

caddress varchar (255) not null

cneeds varchar (255) not null

trainerID Int (12) FK

);

CREATE TABLE Trainer (TrainerID int (12) not null PK

Trainerqualification varchar (255) not null

Trainerdescription varchar (255) not null

);

CREATE TABLE Appointment (AppointmentID int (12) not null PK

Appointmentdescription varchar (255) not null

ClientID int(12) FK

);

CREATE TABLE Schedule (ScheduleID int (12) not null PK

ClientID int(12) FK

AppointmentID int (12) not null FK

scheduledescription varchar (255) not null

Dateid int(12) FK

);

CREATE TABLE Date (DateId int (12) not null PK

Date datetime not null

Dateevent varchar (255) not null

);

INSERTING RECORDS

INSERT INTO client (

ClientID int (12), not null PK

Cname varchar (255) not null

ccontact varchar (255) not null

caddress varchar (255) not null

cneeds varchar (255) not null

trainerID Int (12) FK

)

VALUES

('1', 'Agnes', '1234', 'west wood', 'fitness'),

('2', 'Pete', '1235', 'Angle wood', 'Fitness'),

('3', 'Angle', '234', 'Rose wood', 'Physiotherapy'),

('4', 'Joe', '2345', 'NYC', 'fitness'),

('5', 'John', '123', 'Florida', 'physical fitness'),

('6', 'Doe', '1256', 'NYC', 'fitness'),

('7', 'Martin', '89897', 'California', 'Physical fitness'),

('8', 'Peterson', '3456', 'west wood', 'fitness'),

('9', 'Grace', '98977', 'Florida', 'Physical health'),

('10', 'Ann', '23768', 'Texas', 'Physiotherapy'),

('11', 'George', '989890', 'west wood', 'Physiotherapy'),

('12', 'Alex', '34567', 'Florida', 'fitness'),

('13, 'Patrick', '230908', 'west wood', 'Physiotherapy'),

('14', 'Jude', '0998978', 'California', 'fitness'),

('15', 'June', '2389802', 'Rosewood', 'Physical fitness');

UPDATE Client

SET caddress = "009878"

WHERE

ClientId = 1;

UPDATE Client

SET cname = "Patrick"

WHERE

ClientId = 1 AND 5;

UPDATE Client

SET cneeds = "physiotherapy"

WHERE

ClientId = BETWEEN 2 AND 3;

UPDATE Client

SET CCONTACT = "09090909"

WHERE

cname = John

HAVING (clientid) > 5

UPDATE Client

SET caddress = "California"

WHERE cname IN ('California', 'Florida', 'Texas');

INSERT INTO Trainer (TrainerID int (12) not null PK

Trainerqualification varchar (255) not null

Trainerdescription varchar (255) not null

)

VALUES

('1', 'Expert', 'Reliable'),

('2', 'Intermediate', 'Reliable'),

('3', 'Expert', 'Non-reliable'),

('4', 'Intermediate', 'Excellent'),

('5', 'Expert', 'Efficient'),

('6', 'Expert', 'Reliable'),

('7', 'Intermediate', 'Excellent'),

('8', 'Expert', 'Commendable'),

('9', 'Expert', 'Excellent'),

('10', 'Expert', 'Good'),

('11', 'Expert', 'Bad'),

('12', 'Expert', 'Excellent'),

('13', 'Expert', 'Good'),

('14', 'Intermediate', 'Bad'),

('15', 'Expert', 'Excellent');

UPDATE trainer

SET Trainerqualification = "Reliable"

WHERE

Trainerid = 1;

UPDATE trainer

SET Trainerqualification = "Exellent"

WHERE

Trauinerid = 1 AND 5;

UPDATE trainer

SET cneeds = "physiotherapy"

WHERE

Trainerid = BETWEEN 2 AND 3;

UPDATE trainer

SET CCONTACT = "09090909"

WHERE

trainerdescription = Reliable

HAVING (Trainerid) > 5

UPDATE trainer

SET trainerqualification = "Expert"

WHERE trainerdescription IN ('Reliable', 'Commendable');

INSERT INTO Appointment Appointment (AppointmentID int (12) not null PK

Appointmentdescription varchar (255) not null)

VALUES

('1', 'physical fitness'),

('2', 'fitness'),

('3', 'physical fitness'),

('4', 'physical fitness'),

('5', 'fitness'),

('6', 'physical fitness'),

('7', 'fitness'),

('8', 'physical fitness'),

('9', 'physical fitness'),

('10', 'fitness'),

('11', 'physical fitness'),

('12', 'fitness'),

('13', 'physical fitness'),

('14', 'fitness'),

('15', 'physical fitness'),

UPDATE appointment

SET Appointmentdescription = "Physical fitness"

WHERE

appointmentid = 1;

UPDATE appointment

SET appointmentdescription = "Regular fitness check"

WHERE

appointmentid = 1 AND 5;

UPDATE appointment

SET appointmentdescription = "physiotherapy"

WHERE

appointmentid = BETWEEN 2 AND 3;

UPDATE appointment

SET appointmentdescription = "General fitness"

WHERE

appointmentdescription = Fitness

HAVING (appointmentid) > 10

UPDATE appointment

SET appointmentdescription = "Boxing"

WHERE appointmentdescription IN ('Physical fitness', 'Fitness');

INSERT INTO schedule (ScheduleID int (12) not null PK

ClientID int(12) FK

AppointmentID int (12) not null FK

scheduledescription varchar (255) not null

Dateid int(12) FK)

VALUES

('1', 'Regular visit'),

('2', 'Checkup'),

('3', 'Regular visit'),

('4', 'Regular visit'),

('5', 'Regular visit'),

('6', 'Physiotherapy checkup'),

('7', 'Regular visit'),

('8', 'Regular visit'),

('9', 'Regular visit'),

('10', 'Checkup'),

('11', 'Regular visit'),

('12', 'Follow up'),

('13', 'Regular visit'),

('14', 'Checkup'),

('15', 'Regular visit');

UPDATE schedule

SET scheduledescription = "Follow up"

WHERE

appointmentid = 1;

UPDATE schedule

SET scheduledescription = Regular Visit"

WHERE

scheduleid = 1 AND 5;

UPDATE schedule

SET scheduledescription = "physiotherapy"

WHERE

scheduleid = BETWEEN 2 AND 3;

UPDATE schedule

SET scheduledescription = "General"

WHERE

scheduledescription = Checkup

HAVING (appointmentid) > 5

UPDATE schedule

SET scheduledescription = "Premium visit"

WHERE appointmentdescription IN ('regular visit', 'Checkup');

INSERT INTO Date (DateId int (12) not null PK

Date datetime not null

Dateevent varchar(255) not null)

VALUES

('1', '1-2-2021', 'box jump'),

('2', '1-3-2021', 'Bench press'),

('3', '1-4-2021', 'box jump'),

('4', '1-5-2021', 'Treadmill'),

('5', '1-6-2021', 'Plank'),

('6', '1-7-2021', 'box jump'),

('7', '1-8-2021', 'box jump'),

('8', '1-9-2021', 'Push ups'),

('9', '1-10-2021', 'treadmill'),

('10', '1-11-2021', 'treadmill'),

('11', '1-12-2021', 'box jump'),

('12', '2-1-2021', 'Squats'),

('13', '3-1-2021', 'Foam roller'),

('14', '4-1-2021', 'box jump'),

('15', '5-1-2021', 'Plank'),

UPDATE Date

SET Datetime = "1-4-2021"

WHERE

dateid = 1;

UPDATE Date

SET Datetime = "2-1-2021"

WHERE

dateid = 1 AND 5;

UPDATE Date

SET dateevent = "physiotherapy"

WHERE

dateid = BETWEEN 2 AND 3;

UPDATE Date

SET dateevent = "treadmill"

WHERE

datevent = "push ups"

HAVING (dateid) > 5

UPDATE Date

SET dateevent = "Premium visit"

WHERE dateevent IN ('box jump', 'foam roller');

DELETE FROM Client WHERE clientid = '1' AND '15';

DELETE FROM client HAVING COUNT (clientid) >= 6

SELECT TOP (3)

clientid

FROM client

WHERE

clientid >10;

SELCT COUNT (clientid) FROM client;

SELECT AVG(clientid) FROM client;

SELCT SUM(clientid) FROM client;

SELECT (clientid), caddress

FROM client

WHERE clientid = '1'

HAVING COUNT (clientid) > 10

GROUP BY caddress

SELCT COUNT (clientid), cneed

FROM client

GROUP BY cneeds

HAVING COUNT (clientid) > 10

ORDER BY COUNT (clientid) DESC

INSERT INTO client (

ClientID int (12), not null PK

Cname varchar (255) not null

ccontact varchar (255) not null

caddress varchar (255) not null

cneeds varchar (255) not null

trainerID Int (12) FK

)

VALUES

('16, 'Roy', '0009090', 'London', 'Physical wellness'),

UPDATE

UPDATE client

SET cneed = "Follow up"

WHERE

clientid = 16;

DELETE

DELETE FROM client WHERE clientid = '15';

LOCK

LOCK TABLE client IN EXCLUSIVE MODE;

SELECT schedule.scheduleid, client.clientid

FROM CLIENT

INNER JOIN client ON schedule.client.clientid.

Select trainer.trainerid, client.cname

From ((trainer

Inner join client ON trainer.clientid = client.clientid)

Inner join cname ON trainer.clientid = client.clientid);

Select appointment.appointmentid, client.clientid

FROM appointment

Inner join client ON appointment.appointmentid = client.clientid;

Select client.clientid, schedule.scheduleid

From client

Left join schedule ON client.clientid = schedule.clientid

Order by client.caddress;

References