Overview Immaculate Cleaning Concepts (ICC) specializes in providing cleaning services for both domestic and commercial clients. The company is currently experiencing huge growth and management finds themselves in need of a simple way to track their clients, services requested and provided as well as cleaning staff.
Fiona Llewellyn, the principle owner of ICC, has undertaken a search to source a design for a database and user-friendly interface that she, and her supervisory staff, can access remotely using various smart devices (phones and tablets). Fiona likes the look of many of the newly developed smart device apps that many similar operations use, but she would also like the ability to eventually scale the database to include any possible future use such as contract terms and additional property services such as decorating and painting. For now she is more interested in allowing her managers quick access to clients, cleaning services required and staff availability as well as equipment required for specific services. Her hope is that by keeping the scope of this initial project small that she may have something to put in the hands of her supervisory team within the next few months.
The Cleaning Business
Each type of ICC client has a clearly defined set of requirements. For example, Skystone Properties requires cleaning services from Monday to Friday 7am until 9am and 5pm until 7pm each day, while D. Boruma contracts for cleaning services to be provided each Wednesday from 10am until 1pm. Whenever a new client is taken on, either Fiona, or one of her two office administrators, visit the site to assess how many cleaning staff would be required for the premises prior to assigning any staff to the job. Note that this is the ideal number, it may differ in practice.
In addition, an assessment is made to determine whether any specialist equipment is required, and when it might be required. For example, three industrial floor cleaners may be needed on two out of five occasions for one commercial client. The cleaning staff work in groups of six, with a supervisor to oversee all work done. The only other staff are Fiona's two administrative staff, Morgan and Brian, who manage the day-to-day office work including scheduling staff for specific contracts and services, visiting new clients and ensuring the specialist equipment is properly maintained. There are a number of clients who have contracted ICC over the years and Fiona would like to keep track of that historical data along with any other details you deem necessary.
One of Fiona's key requirements in this project is that you maintain an open dialogue with her and her administrative team during development so that your design considerations can be discussed at regular intervals, hopefully expediting the final design proposal. Case Requirements Individually, each of you must prepare a formal report containing your recommendations for the database design for ICC. This report should contain:
• Your final 3NF design in the standard format... tblTableName (primaryKevField(s), otherField(s), foreignKeiFieldfs), etc...) ...with each field separated by a coma.
• A list of all functional dependencies for all tables.
• A data dictionary indicating the domain (name, meaning and description) for each field of each table.