Need help making an ERD

Pool Entertainment Products and Services (PEPS) provides customers with the finest in inground pools. PEPS also provides services to clean and maintain pools so customers can enjoy a worryfree leisure experience. Your company has been hired to design a database to help PEPS track all of the product and service contracts for their growing company. The system that you are helping to design does not need to track billing and payments, which are already handled by an accounting package, but it will keep track of various costs. Customers hire PEPS to install and / or service pools. For each customer, the customer is given a unique number and their name and phone number are recorded. Sales people try to gather this information on potential customers when they come in to talk about purchasing a pool or a service contract. Some of these customers eventually sign contracts with PEPS and some dont, but the information is kept on all of them. PEPS has installation contracts and service contracts. Installation contracts are created to record the purchase of a pool. Service contracts are for contracting cleaning and maintenance services. For both types of contracts, the contract is associated with only a single customer, but a customer can have many contracts with PEPS. For example, a customer can have pools installed at two different locations (one at their personal home and one at a rental property they own) so that would require two installation contracts. That same person perhaps wants to arrange cleaning services for one of those pools, so that customer has one service contract. If the customer wanted PEPS to handle the cleaning of both pools, they would need two service contracts. All contracts, whether it is an installation contract or a service contract, have a contract number, a sign date, and a start date. The sign date is the date that the contract is signed by the customer. The start date is the date the installation or service is scheduled to begin. Additionally, the address where the pool is to be installed or serviced is a part of the contract and needs to be recorded. To be clear, if a customer wishes to purchase more than one pool, each purchase must be through a separate installation contract. If a customer wants to purchase a pool and maintenance services for it, an installation contract and a service contract must be created as separate contracts for that pool. For installation contracts, an estimated completion date, actual completion date, sales price of the pool, and installation labor price need to be included in the contract. Note that the actual completion date will not be known until the installation project is completed. PEPS has many different in-ground pools that it can install. For each pool, the pool style, a brief description, price, size, and shape are recorded in the system. Some pools have been purchased by many different customers, but others have never been purchased. Page 2 of 3 During the construction of a pool, PEPS may or may not hire one or more subcontractors to complete different portions of the project. For example, PEPS may hire one subcontractor to do the excavation work, another to do the cement work, and yet another to do the electrical work. For each subcontractor, the company name, phone number, contact person name, and type of work they do (excavation, cement, electrical, etc.) needs to be recorded. PEPS tends to use reliable subcontractors over and over, so a given subcontractor may have worked on many different installations. Information is sometimes kept on new subcontractors in the area even if PEPS has never used them before. For every installation worked on by a subcontractor, the date the subcontractor started work on that project, the date they finished work on that project, and the fee charged by the subcontractor are recorded. When the final construction of the pool is finished, the actual completion date for the contract is added to the system. Service contracts are for one or more maintenance services to be performed on a single pool. If a customer wants services provided on two or more pools, that will require separate service contracts for each pool. PEPS offers many services such as water chemistry maintenance and various types of cleaning. For each service, the name of the service and a brief description are kept in the system. Services also include a frequency and a fee. For example, the Deep Cleaning service is described as providing top water skimming, side scrubbing, and bottom vacuuming the pool every two weeks for $45 per cleaning. A service contract can cover several different services on the same pool. For example, a service contract may include both the Deep Cleaning service and the Perfect Balance water chemistry service. Every service contract must be for some service. Some services are very popular and PEPS has many contracts to perform that service for different pools. PEPS wants the ability to add new services in the future so it will be possible for a service to exist in the system without any contracts for that service. It is also possible that PEPS can change the frequency and fee for a service over time, so it is necessary to know for each service contract what frequency and fee for each service covered by the contract were in place at the time the contract was created. For example, the Perfect Balance service used to offer water chemistry testing every week for a $15 fee. Last month, PEPS changed the Perfect Balance service to be testing once every two weeks for a $20 fee. Any contracts that were in place before the service change will continue to be tested with the weekly frequency for the $15 fee. Any contracts created after the service change will receive the testing every two weeks for the $20 fee. The contract details what needs to be done. Each time a given service is actually performed for a contract, the date of the performance needs to be recorded. Additionally, the person performing the service needs to be able to record any comments. Occasionally, an extra fee may be associated with a given performance of a service. For example, a service contract for a pool at a given location may specify that the Deep Cleaning cleaning service and the Perfect Balance chemical maintenance services are supposed to be performed. On July 3rd, the Deep Cleaning service was performed, and the worker noted that the skimmer basket is damaged and may need replacing soon. On July 8th, the Perfect Balance service was performed. The worker noted that the client was out of chlorine tablets, so he had to use 2 tablets from PEPS supply. He recorded an extra fee of $5.00 for this visit as a charge for the chlorine tables. Since city and state regulations require pools to be in fenced or controlled areas, the service contract also records a contact person name and phone number to call if the worker has an issue with accessing the pool area. Unlike the installation contracts, service contracts are ongoing and do not end after the service is performed. If a customer contacts PEPS to cancel the service contract, the date of cancellation is recorded and the date the service should end is also recorded. For example, a customer may call on August 3rd to tell PEPS to stop the service at the end of September. In that case, the cancellation date is August 3rd and the stop date is September 30th.