How to Create MS Access Database for Given Scenario (AHI Company)?

MS Acess, SQL. Queries, Solution

Assignment #5: Access – Create Database for AHI

A1 Home Improvement (AHI) is a home improvement retailer in the Northern Virginia area. AHI does not employ installers, but rather subcontracts the installations out to a list of approved installers. Whenever a customer purchases an item in the store, an AHI agent will help create a contract for the installation. Then, AHI will try to assign an installer for the installation contract. After the assignment, the installer will go to the customer’s house and complete installing the item. Currently, this process is primarily paper-based and does not provide staff with the information they need to efficiently complete contracts. Frequently, the merchandising group arbitrarily runs advertisements for specials such as a guaranteed 2 week installation of ceiling fans.  Without a way to effectively manage the contracts and installers, contracts don’t get completed in a timely manner which has resulted in customer dissatisfaction.

You are hired by AHI’s IT department and you have been asked to create a tracking system to automatically track these installations. In the first task, you have created the E-R diagram for the database as shown below (next page). Your second job is to implement the database using Microsoft Access 2013 to work on Q1-Q5.

Q1.Create the access database and name it exactly as your last name followed by your G number without “G” and then followed by suffix “_assignment_5”. There should not be extra spaces or underlines in between. For example, the file’s full name should be like Ye12345678_assignment_5.accdb, in which .accdb is the file extension.

 

 

 

Q2.Create all the tables in the database.  You should refer to the description of Assignment #4 if forget the meaning of each attribute. Make sure that: 

  • Each attribute has a description to describe its meaning as well as any other details.
  • For each of the attributes, you will need to correctly assign the appropriate Data Type. For example, it is not advisable to define the data types of InstallerIDa s Text.
  • For each of the attributes, you will need to correctly assign the appropriate Field Size if the field is a Text data type or Integer data type. For example, it is not advisable to allocate 250 to the size of CustomerFirstName.
  • Specify the decimals places to be 2 for fields in the currency format.
  • Any date field must be in the Short Date format and have the Short Date input mask.
  • Any telephone field has an appropriate input mask such as (XXX) XXX-XXXX. All the values of primary keys must be automatically generated by the system.

 

Q3.Establish the relationships among the tables. Make sure that referential integrity is enforced for each relationship.

Q4 .Enter data (shown on next page) into the database (you enter the data for tables Customer, Installer, and Agent first and make sure they are correct. Then you input the data for table Contract).

Q5.Create a columnar form that allows personnel to enter all the information about new customers or update existing customer information. Change the form title to “Customer Data Entry Form”.

 

Table: Customer
CustomerID CustomerFirstName CustomerLastName CustomerStreet CustomerCity CustomerState CustomerZipCode CustomerPhone
1 Alex Anderson 14897 Condale Dr. Woodbridge VA 22193 (570) 570-5711
2 Jonas Minor 520 Cherry Hill Rd. Fairfax VA 22030 (301) 423-4560
3 Chris Smith 1046 Rockland Rd. McLean VA 22101 (703) 900-0765
4 David Beckcamp 912 Fisherman Ln Vienna VA 22180 (571) 777-1111

 

Table: Installer
InstallerID InstallerFirst

Name

InstallerLast

Name

InstallerStreet InstallerCity Installer State Installer ZipCode InstallerPhone InstallerDailyCharge
1 Scott Langston 222 Lee Hwy Fairfax VA 22030 (703) 444-4433 $160.00
2 Randy Nguyen 111 West Ox Rd. Fairfax VA 22030 (703) 555-3422 $175.00
3 Michael Quinn 402 Chain Bridge Rd. McLean VA 22101 (703) 565-3434 $200.00

 

Table: Agent
AgentID AgentFirstName AgentLastName AgentPhone
1 Mike Kaine (703) 452-5681
2 John Wadel (703) 580-1642
3 Tim King (703) 577-3420

 

Solution:

Agent
AgentID AgentFirstName AgentLastName AgentPhone
5 Mike Kaine (703) 452-5681
6 John Wadel (703) 580-1642
7 Tim King (703) 577-3420
 

 

Paste Errors
Field0 Field1 Field2 Field3 Field4 Field5 Field6 Field7 Field8 Field9
5/15/2015 5/15/2015 5/22/2015 3 4 2 $3500.00 Carpet 95
5/14/2015 5/15/2015 5/23/2015 2 4 1 $2500.00 Lighting Chandelier 85
5/14/2015 5/14/2015 5/22/2015 3 4 1 $6000.00 Cabinetry 85
6/21/2015 1 1 $5000.00 Carpet
6/20/2015 3 1 $3500.00 Ceiling fan

 

 

Installer
InstallerID InstallerFirstName InstallerLastName InstallerStreet InstallerCity InstallerState InstallerZipCode InstallerPhone InstallerDailyCharge
4 Scott Langston 222 Lee Hwy Fairfax VA 22030 (703) 444-4433 $160.00
5 Randy Nguyen 111 West Ox Rd. Fairfax VA 22030 (703) 555-3422 $175.00
6 Michael Quinn 402 Chain Bridge Rd. McLean VA 22101 (703) 565-3434 $200.00

 

Customer
CustomerID CustomerFristName CustomerLastName CustomerCity CustomerStreet CustomerState CustomerZipCode CustomerPhone
9 Alex s Anderson 14897 Condale Dr. Woodbridge VA 22193 (570) 570-5711
10 Jonas Minor 520 Cherry Hill Rd. Fairfax VA 22030 (301) 423-4560
11 Chris Smith 1046 Rockland Rd. McLean VA 22101 (703) 900-0765
12 David Beckcamp 912 Fisherman Ln Vienna VA 22180 (571) 777-1111
13 New Data Last Name New New Jersy Trump MacDAonald 3000 (571) 777-1111

ERD, MS Access

 

LEAVE A REPLY

Please enter your comment!
Please enter your name here