Ride Certification Tracker Database
Introduction
During my time as a supervisor in the Rides department at California's Great America, one of the biggest challenges faced by management was tracking who was certified to operate different positions in different attractions.
The current tracking system was kept under the associate time-card system (UKG Kronos), with all attractions an associate is certified to operate listed in their profile page. While the system generally worked, it had some flaws which led to issues when trying to find certified associates for attractions that may be facing a staffing shortage, or for any administrative tasks regarding certifications.
While Kronos showed where employees were certified and the date it was completed, it was only possible to query results by looking at each individual employee profile. While this solution worked when trying to find if one or a few employees were certified, it was very time consuming to try to gather an entire crew's worth of information by scanning each profile individually.
With this scalable database I created, this solves the problems originally faced with using the time-card system to track certifications. The new system allows users to enter an associate's information (name and ID number), the position/attraction they were certified at, and the date it was completed. Data entry has also been made easier, now with users not having to change employee profiles when completing each entry, but adding a new row to the database table.
The main problem of data retrieval has been solved by this database as queries can obtain very specific information that can answer questions/concerns leading to smoother operations, with examples including:
Which attractions can Ginji operate?
Who is certified to operate at this attraction?
All associates who completed any certification between January 1, 2023 and January 3, 2023.
These are some examples of what the results from database queries can answer, all of which can save significant time when compared to using Kronos.
Below are examples of my work in media form.
An example of an employee profile showing their certifications.
Business Rules:
An associate can have zero or many certifications (certifications expiring or new hires).
An associate is assigned to zero or one area (upper management is not tied to a specific area, and each associate has one main area).
An area consists of at least one ride/attraction.
Each ride has at least one certification (control, loading/unloading operator, etc.).
Database Entity Resource Diagrams:
Conceptual model of the database
Physical model of the database
Queries:
Find all attractions where the associate with the ID number of '210008' is certified to operate:
SELECT CERTIFICATION.ride_Name AS [Attraction Name], CERTIFICATION.certif_Level AS [Level], CERTIFICATION.certif_dateComp AS [Date Certified]
FROM CERTIFICATION
WHERE assoc_ID = 210008;
Find all attractions where the associate 'John Doe' is certified to operate:
SELECT CERTIFICATION.ride_Name AS [Attraction Name], CERTIFICATION.certif_Level AS [Level], CERTIFICATION.certif_dateComp AS [Date Completed]
FROM CERTIFICATION
LEFT JOIN ASSOCIATE ON CERTIFICATION.assoc_ID = ASSOCIATE.assoc_ID
WHERE ASSOCIATE.assoc_LName = 'Doe' AND ASSOCIATE.assoc_FName = 'John';
Find all associates from the 'Rides 12' area that is certified to operate 'Patriot'
SELECT ASSOCIATE.assoc_ID [ID], ASSOCIATE.assoc_LName [Last Name], ASSOCIATE.assoc_FName [First Name], CERTIFICATION.ride_Name [Attraction Name], CERTIFICATION.certif_Level [Level], CERTIFICATION.certif_dateComp [Certification Date]
FROM ASSOCIATE
LEFT JOIN CERTIFICATION ON ASSOCIATE.assoc_ID = CERTIFICATION.assoc_ID
WHERE ASSOCIATE.area_ID = 12 AND CERTIFICATION.ride_Name = 'Patriot';
Find all associates that were completed certifications to operate 'Gold Striker' between '01/05/2023' and '01/08/2023'
SELECT ASSOCIATE.assoc_ID [ID], ASSOCIATE.assoc_LName [Last Name], ASSOCIATE.assoc_FName [First Name], CERTIFICATION.certif_Level [Level], CERTIFICATION.ride_Name [Attraction], CERTIFICATION.certif_dateComp [Certification Date]
FROM ASSOCIATE
LEFT JOIN CERTIFICATION ON ASSOCIATE.assoc_ID = CERTIFICATION.assoc_ID
WHERE CERTIFICATION.certif_dateComp BETWEEN '2023-01-05' AND '2023-01-08' AND CERTIFICATION.ride_Name = 'Gold Striker'
ORDER BY CERTIFICATION.certif_dateComp asc; --ordered by date (earliest to latest)