$(6)$ Make good use of the tutorial for the in$-$class exercise. If you forget about some of the skills we have covered in class, you can always refer to the tutorial posted on Canvas which documents every step we have done for the in$-$class exercise.

Assignment $\backslash$#$4$: Microsoft Access $($Total: $50$ pts$)$

Background: A$1$ Home Improvement $($AHI$)$ provides installation services of different types for home improvement $($e$.$g$.,$ Flooring, Lighting $\backslash$& Ceiling Fan, HVAC, etc.$)$ 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. 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 installer performance. 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. 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 should also keep track of all approved installers, including their unique installer ID$,$ 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 needs to record each type of installation service AHI provides, including unique service type ID and service type name. In order to provide management with the information needed to effectively track installations, the database should keep track of the details for each installation contract, including unique contract ID$,$ the ID of the customer who

Page $1$ of $4$

MIS$303$ Assignment $\backslash$#$4$ Microsoft Access Assignment

requests the service, the ID of the installer assigned to complete the installation, the ID of the service type the installation belongs to$,$ additional descriptions of the requested installation job, service charge of the installation $($i$.$e$.,$ how much the customer pays to AHI for the installation service$),$ the date the contract is created by AHI $,$ the date an installer is assigned to work on the installation, the date the installation is completed by the installer, and the customer's satisfaction rating of the installation job after the job is finished.

An installation contract may have not been assigned an installer yet, but once assigned each installation is assigned to only one installer. An approved installer may have not been assigned to any installation or may have been assigned to multiple installations. Each installation contract belongs to only one service type, but each service type may have no installation contracts or may be associated with 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 only one customer.

Earlier, you have created the conceptual model of the database based on the above business description as shown in the following E$-$R diagram. $4$:$314$

$5$G

$83$

Your Task: Now you need to convert the developed conceptual model into a physical model using Microsoft Access by completing Q$1-$Q$6$ shown on next page.

Page $2$ of $4$

MIS$303$ Assignment $\backslash$#$4$ Microsoft Access Assignment

Here is a list of additional business rules you need to consider when creating the database:

$1)$ Any name $($first$,$ last, city, service type$)$ is between $1$ and $25$ characters and is required.

$2)$ ContractDescription is between $1$ and $200$ characters and is required.

$3)$ CustomerID and InstallerID are Incremental AutoNumbers when serving as primary keys.

$4)$ ContractRating take integer values between $0$ and $100($Hint: use this information to determine the DataType and Field Size of the ContractRating attribute$).$

$5)$ All dates are in the Short Date format and have the Short Date input mask.

$6)$ All telephone numbers have the $($XXX$)$ XXX$-$XXXX input mask.

$7)$ All the currency fields have $2$ decimal places.

Q$1.$ Create all the tables in the database. Make sure that: $(30$ pts$)$

$\backslash (\backslash$square $\backslash )$ Each attribute has a meaningful description to explain its meaning.

$\backslash (\backslash$square $\backslash )$ Each attribute has an appropriate Data Type and Field Size based on the business rules and$/$or the data provided $($on next page and in the Excel file$)$ and$/$or domain knowledge.

$\backslash (\backslash$square $\backslash )$ Business rules related to format, decimal places, and input mask are implemented.

Q$2.$ Establish the relationships among the tables with referential in