Home Warranty Corporation HWC ERD and SQL script

Description

Case Scenario Home Warranty Corporation ERD and SQL script

Home Warranty Corporation (HWC) provides warranties for home appliances. Home owner’s purchase these warranties as insurance policies on their various appliances (washers, driers, dishwashers, etc.). They pay HWC an annual fee for each appliance that they want covered and HWC is responsible for any necessary repairs. A contract is signed for a particular residence. The contract includes a list of the appliances covered for that residence. While most customers have only one contract, some have multiple contracts if they have multiple residences. HWC maintains a warehouse with parts that are necessary for repairs. In addition, they employ hourly service personnel who service the appliances when a problem arises. HWC needs a database to maintain information about their customers, contracts, appliances under contract, parts in the warehouse, employees, and service calls (including costs).  

The customer information would include the name of the customer, their phone# and address information for billing purposes. Each customer may have one or more contracts. The contract information includes address information for that particular contract as well as the appliances under contract at that address. In addition, the start and end dates of the contract should be maintained. The appliance information includes the manufacturer, the manufacturer serial# and a description of the appliance as well as the annual fee the customer is paying for the warranty on the appliance. Fees for a particular appliance may differ between contracts as HWC changes fees periodically and may offer some customers lower fees based on volume. The part information would include the part#, description, part cost, and quantity on hand. The employee information would include the name and hourly cost of each employee. The service call information would include the date of the service call, the contract being serviced, the appliance being serviced, the employee(s) who completed the service and the costs associated with the call.  Those costs would include the cost of the part(s) used and # of hours spent on the call by each of the service people.  Each service call may have one or more service people and may require one or more parts. A separate call is recorded for each appliance serviced.

Some of the goals that HWC wants to accomplish with their database include:

If a given contract is profitable for any given year.  (compare the fees paid against the costs associated with the service calls)
A list of service calls for any given contract or customer including the costs associated with each call.
A list of service calls for any given appliance including the costs associated with each call.

You are required to use any available to tool to design and develop an ER diagram and write SQL script. The accepted file formats are MS Word or PDF file format.