A1 Home Improvement (AHI) provides installation services for home improvement in Northern Virginia. AHI does not employ installers, but rather subcontracts the installations out to a list of approved installers. Whenever a customer requests an installation service in the store, a contract is created for the installation. AHI offers different payment options such as 20% deposit+80% after completion, 30% deposit+70% after completion, etc. AHI decides a deposit amount based on the installation job and the customer pays upfront. Then AHI will assign an installer for the installation job in the contract. Afterwards, the installer will go to the customer’s house and complete the installation. Currently, this process is primarily paper-based and does not provide management with the information needed to efficiently monitor contracts and installers. AHI often runs advertisements for specials such as guaranteed 2-week installation. Without a way to effectively manage installations, installation service sometimes does not get completed within the promised timeframe which has resulted in customer dissatisfaction.
You are hired by AHI to create a database that helps automatically tracks installation contracts and installers. The database must keep track of each AHI customer, including their unique customer ID, first name, last name, street address, city, state, zipcode, and phone number. It also keeps track of all approved installers, including their unique installer ID, SSN (i.e., social security number), first name, last name, city, state, zipcode, phone number, and daily charge (i.e., how much AHI pays to the installer for one day’s work). In addition, the database records each payment option, including unique payment option ID and deposit percentage. In order to provide management with the information needed to effectively track installations, the database keeps track of the details for each installation contract, including unique contract ID, the ID of the customer who requests the service, the ID of the installer assigned to complete the installation, description of the requested installation job, the service charge amount (i.e., how much the customer need to pay to AHI for the installation service), the ID of the payment option associated with the contract, the date the contract is created, the date an installer is assigned to work on the installation, the date the installation is completed by the installer, and the customer’s rating of the job performed by the installer after the job is finished.
Each installation contract may have not been assigned an installer yet, but once assigned each installation is assigned to only one installer. An installer might have not been assigned to any installation or might have been assigned to multiple installations. Each installation contract is associated with one payment option, but each payment option may have not been used for any installation contract yet or may have been used for multiple installation contracts. The database only records existing customers who has requested services from AHI. While a customer might have requested for multiple installations, each installation is for one specific customer.
Q. Carefully read the above business description and draw an Entity-Relationship Diagram (E-R diagram) using Microsoft Powerpoint 2016 for the database that is required for AHI’s tracking system. Make sure that:
1) Your E-R Diagram includes all necessary entities, attributes, and relationships. Hint: since each installation is represented as a contract, installation or contract or installation contract mean the same.
2) For each entity, you need to underline its identifier.
3) For each relationship, you need to give it a meaningful name (remember that a relationship should have a name with a verb).
4) For each relationship, you need to specify the relationship type and cardinality numbers.
5) Make sure that the E-R diagram looks professional: the boxes for entities and the diamonds for relationships are clearly separated from each other rather than being overlapped; the lines are connecting to boxes for entities and diamonds for relationships; the numbers for cardinalities are written at the appropriate positions (i.e., near the entities and are not overlapping with the lines).
This material may consist of step-by-step explanations on how to solve a problem or examples of proper writing, including the use of citations, references, bibliographies, and formatting. This material is made available for the sole purpose of studying and learning - misuse is strictly forbidden.
This is only a preview of the solution. Please use the purchase button to see the entire solution