access

MIS303 Microsoft Access Assignment #1

Page 1 of 4

MIS303 Microsoft Access Assignment #1

Note (Important):

(1) This is an individual assignment. It accounts for 9% of your grade.

(2) Your final solution should be ONE Microsoft Access file (.accdb file) that is submitted via Blackboard. Make sure that you submit the actual Access file. Do

NOT submit the temporary lock file (.laccdb file) that I will not be able to open.

Otherwise, you will get a 0 grade. To avoid making the mistake of submitting the

wrong temporary file, you should close all Microsoft Access programs first before

uploading your file as explained in the lessons and tutorials.

(3) Pay attention to details: all questions are detail-oriented. Make sure you read the instruction word by word carefully. Otherwise, you might lose points for failing to

fulfill any of the clearly specified requirements.

(4) You may want to submit your solution at least 30 minutes before the deadline. Make sure you click “Submit” rather than “Save Draft” to actually submit. You are required

to double-check your submission by downloading it and opening it to see if it is the

correct file. You will be responsible for any technical difficulty or mistake on your

side that results in submitting the wrong work or your work not being submitted or

being accepted as late. No exceptions will be made.

(5) Make good use of the videos and tutorials.

Microsoft Access Assignment #1 (Total: 50 pts)

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 for ceiling fans. 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

MIS303 Microsoft Access Assignment #1

Page 2 of 4

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 form, 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.

In the first task, you have created the ER diagram for the database as shown below.

Below is a list of additional business rules:

1) Any first name or last name is between 1 and 20 characters and is required. 2) Any city name is between 1 and 25 characters and is required. 3) ContractDescription is between 1 and 200 characters and is required. 4) CustomerID, InstallerID, and ContractID are Incremental AutoNumbers when

serving as primary keys.

5) ContractInstallerRating takes integer values between 0 and 100 (hint: use this information to determine the Data Type and Field Size of the attribute; no need to

worry about Validation Rule that was not covered in the lessons).

6) All dates are in the Short Date format and have the Short Date input mask. 7) All telephone numbers have the (XXX) XXX-XXXX input mask. 8) Any SSN has the XXX-XX-XXXX input mask. 9) All the currency fields have 2 decimal places.

Your second task is to convert the above conceptual model plus business rules into a physical

model using Microsoft Access 2016 by working on Q1-Q6 (next page).

MIS303 Microsoft Access Assignment #1

Page 3 of 4

Q1. Create all the tables in the database. Make sure that: (30 pts)

• Each attribute has a meaningful description to explain its meaning.

• Each attribute has an appropriate Data Type and Field Size based on the above business rules and/or the data provided (on next page and in the Excel file) and/or

domain knowledge.

• Business rules related to format, decimal places, and input mask are implemented.

Q2. Establish the relationships among the tables with referential integrity enforced for each relationship. (10 pts)

Q3. Enter data for tables PaymentOption, Installer, and Customer (shown on next page) into the database. (4 pts)

Q4. Import data from the Excel file Contract.xlsx into the Contract table. (1 pts)

Q5. Create a columnar form that allows personnel to add new customers or update existing customer information. Include three buttons in the form: one button for moving to

previous record, one button for moving to next record, and one button for saving the

current record. Change the form title to “Customer Data Entry Form”. (4 pts)

Q6. Name the Access database exactly as your last name followed by your G number without “G” and then followed by suffix “_Access_1” (there should not be extra spaces or

underlines in between; e.g. Ye12345678_ Access_1.accdb with .accdb being the file

extension) and submit it via Blackboard. (1 pts)

MIS303 Microsoft Access Assignment #1

Page 4 of 4

Table: Customer

CustomerID CustomerFirstName CustomerLastName CustomerStreet CustomerCity CustomerState CustomerZipCode CustomerPhone

1 Alex Anderson 1462 Stanford Dr Woodbridge VA 22193 (570) 231-5714

2 Jonah Park 325 Cherrywood Ct McLean VA 22101 (301) 561-4607

3 Jennifer Smith 106 Pickett Rd Fairfax VA 22030 (703) 761-0732

4 Aaron Goodman 715 Adelphi Ln Vienna VA 22180 (571) 158-1135

Table: Installer

InstallerID InstallerSSN InstallerFirst

Name

InstallerLast

Name

Installer

City

Installer

State

Installer

ZipCode

InstallerPhone InstallerDaily

Charge

1 202-12-7831 Scott Spacey Fairfax VA 22030 (703) 432-4233 $170.00

2 301-20-3451 Randy Langston Fairfax VA 22030 (703) 502-3421 $180.00

3 190-18-1775 Mike Quinn Oakton VA 22180 (703) 265-9087 $220.00

Table: PaymentOption

PaymentOptionID PaymentDepositPct

PD05 0.05

PD10 0.10

PD15 0.15

PD20 0.20

PD30 0.30

PD40 0.40

PD50 0.50

PD60 0.60

Data for the Contract table should be imported from the Excel file Contract.xlsx.