Access Project Spr 2006

Document Sample
Access Project Spr 2006 Powered By Docstoc
					                                 COB 204 – Access Database Project
                                       Due March 1, 2006

Because of your expertise in Access, you have been hired by an Automobile dealership to create a
database for them. The database will track the Automobile dealership and its customers. In addition,
some customers may choose to make their payments via an Electronic funds transfer (EFT) so bank
information will be necessary for those customers. The following information needs to be considered
with this database. An automobile may be purchased by only one Customer. A Customer must have
purchased one automobile and but may have purchased many Automobiles. Only one bank may be
used by a customer for their EFT transactions but a bank may be used by many Customers.

You are to create the database using Access and create reports and queries from the database. The last
couple pages of this assignment show the Automobile, Customer, and Bank data that need to be put in
the database. To minimize your work effort, you should read and understand all of the
specifications in this document before starting the project.

Requirements:

1.     Design the structure of the table: AUTOMOBILE. Use an existing field as the primary key
       field and make sure to include a field that can be used for a join with the CUSTOMER table
       (foreign key). If you need to, you can add a field or fields and make minor changes to the other
       fields.

2.     Design the structure of the table: CUSTOMER. Add a field as a primary key field and make
       sure to include a field that can be used for a join with the BANK table (foreign key). If you
       need to, you can add a field or fields and make minor changes to the other fields.

3.     Design the structure of the table: BANK. Use an existing field as the primary key field. If you
       need to, you can add a field or fields and make minor changes to the other fields.

4.     Create a relationship between the AUTOMOBILE and CUSTOMER tables with referential
       integrity and cascade updates enforced.

5.     Create a relationship between the CUSTOMER and BANK tables with referential integrity and
       cascade updates enforced.

6.     Create a form to enter CUSTOMER data. Choose any style for your form but include one
       picture on this form.

7.     Add the data on the last page to the CUSTOMER table using the form. There will be six
       Customers.

8.     Create a form to enter AUTOMOBILE data. Choose any style for your form.

9.     Add the data on the last page to the AUTOMOBILE table using the form. There will be ten
       Automobiles.

10.    Create a form to enter BANK data. Choose any style for your form.

11.    Add the data on the last page to the BANK table using the form. There will be three Banks.

                                                                                                         1
12.   Print the CUSTOMER form showing only one record: the one with your name. (Print)

13.   Create a report showing the Customers grouped by State and sorted by last name and first
      name. Only the following Customer fields should be shown: customer name, telephone
      number, street address and City (State is included also but this field is displayed as part of the
      grouping). Include “Created by” with your name at the bottom of the report. Print the report.
      (Print)

14.   Create a query and then a report from the query showing the banks in Virginia. Show all fields
      in the Bank table. Do not do any grouping in this report. Include “Created by” with your
      name at the bottom of the report. Print the query design and the report (not the report design).
      Print the query design as follows: (Print)
             Open the query using the design view
                    On the keyboard, press the Print Screen key.
                    Go to Word, and Paste the screen. Print the Word document.

15.   Create a report showing all Automobiles grouped by Make. Only the following Automobile
      fields should be included within the report: Automobile Model, year, and purchase price
      (Automobile Make is included also but this field is displayed as part of the grouping). In the
      report wizard, on the page where you specify sort order, select Summary Options. Choose Sum
      of Purchase Price and on the right side of the screen choose (if necessary) Detail and Summary.
      The report will then show the total of the purchase prices for each Make and a grand total of all
      of the purchase prices. Include “Created by” with your name at the bottom of the report. Print
      the report. (Print)

16.   Create a query and then a report from the query to show all Customers who purchased an
      automobile between June 1, 2005 and August 10, 2005. Show the Customer’s last and first
      name. List the Customers in alphabetical order. Print this query in design view and print the
      report (not the report design). Include “Created by” with your name at the bottom of the report.
      Print the query design as follows: (Print)
             Open the query using the design view
                     On the keyboard, press the Print Screen key.
                     Go to Word, and Paste the screen. Print the Word document.

17.   Create a report that includes the CUSTOMER and BANK tables to show all Customer grouped
      by Bank name. Only the following Customer fields should be included in the report: Customer
      first name and Customer last name. Include “Created by” with your name at the bottom of the
      report. Print the report.

18.   Create a query grouping Automobiles by Make. Show minimum, average, maximum and the
      sum of the Purchase Price for all Automobiles for the Automobile Make. You should not show
      individual Automobiles, just totals. Print the query results.

19.   Print the design as follows:
             Print the relationship by opening the relationship on the screen.
                     On the keyboard, press the Print Screen key.
                     Go to Word, and Paste the screen. Print the Word document.
             Print all of the table designs, by opening each table in design view.
                     For the AUTOMOBILE table, place your pointer in Purchase date so that the
                      field properties shown are for Purchase date.

                                                                                                           2
                        On the keyboard, press the Print Screen key.
                        Go to Word, and Paste the screen. Print the Word document.
                      For the AUTOMOBILE table, place your pointer in Purchase Price so that the
                       field properties shown are for Purchase Price.
                        On the keyboard, press the Print Screen key.
                        Go to Word, and Paste the screen. Print the Word document.
                      For the BANK table, place your pointer in Telephone Number so that the field
                       properties shown are for Telephone Number.
                        On the keyboard, press the Print Screen key.
                        Go to Word, and Paste the screen. Print the Word document.

Grading criteria:
     Is the data entered correctly and have you included database field descriptions?
     Have you used appropriate field lengths?
     Have you minimized data redundancy?
     Have you used good database design concepts?
     Do the reports show what they are supposed to show?
     Are your reports and the form attractive?
           o Report and query titles make sense
           o Columns are wide enough
           o Column names make sense
           o Report sort order makes sense
           o Was anything truncated?

Turn in, stapled together:

      A cover page with your typed name, section number, and the following signed pledge:
       “I pledge that I have neither given nor received unauthorized assistance on this project.”

      The printed pages from #12-19 above. Please mark the upper-right corner with the number
       from the assignment (12-19). If the reports are out of order or are not marked with the
       appropriate number, your grade will be lowered by up to 5 points.

In addition:
       Turn in a copy of your database on March 1 during the first 2 minutes of class. No late Access
        Projects will be accepted.

This project is an individual assignment and must be done alone. You MAY NOT sit next to another
person and do the project together while discussing each step. You MAY discuss minor issues with
another student but all keystrokes must be done by you and you must have your own database. Any
other collaboration is a violation of the JMU honor code.




                                                                                                         3
                                       AUTOMOBILE

Vehicle Identification Number (VIN):    ABC123
Make:                                   Toyota
Model:                                  Highlander
Year:                                   2004
Purchase Price:                         $24,000
Purchase Date:                          6/1/2004
Customer:                               Charlie Brown

Vehicle Identification Number (VIN):    ABC124
Make:                                   Toyota
Model:                                  Highlander
Year:                                   2004
Purchase Price:                         $18,900
Purchase Date:                          8/1/2004
Customer:                               Patty Peppermint

Vehicle Identification Number (VIN):    PDF457
Make:                                   Ford
Model:                                  Mustang
Year:                                   2005
Purchase Price:
Purchase Date:
Customer:

Vehicle Identification Number (VIN):    SDD723
Make:                                   Toyota
Model:                                  Land Cruiser
Year:                                   2004
Purchase Price:                         $37,000
Purchase Date:                          9/1/2004
Customer:                               Charlie Brown

Vehicle Identification Number (VIN):    SDD724
Make:                                   Toyota
Model:                                  Land Cruiser
Year:                                   2004
Purchase Price:
Purchase Date:
Customer:

Vehicle Identification Number (VIN):    PRT486
Make:                                   Chevrolet
Model:                                  Malibu
Year:                                   2005
Purchase Price:                         $19,000
Purchase Date:                          8/1/2005
Customer:                               Linus Van Pelt in Harrisonburg


                                                                         4
Vehicle Identification Number (VIN):         PRT487
Make:                                        Chevrolet
Model:                                       Malibu
Year:                                        2005
Purchase Price:                              $21,000
Purchase Date:                               8/5/2005
Customer:                                    Linus Van Pelt in Winchester

Vehicle Identification Number (VIN):         PRT488
Make:                                        Chevrolet
Model:                                       Malibu
Year:                                        2005
Purchase Price:
Purchase Date:
Customer:

Vehicle Identification Number (VIN):         PRT489
Make:                                        Chevrolet
Model:                                       Malibu
Year:                                        2005
Purchase Price:                              $21,000
Purchase Date:                               9/10/2005
Customer:                                    Lucy Van Pelt

Vehicle Identification Number (VIN):         AJY278
Make:                                        Dodge
Model:                                       Neon
Year:                                        2005
Purchase Price:                              $18,500
Purchase Date:                               6/2/2005
Customer:                                    your name

Note: Use the best foreign key, this does not mean that the best one is the customer’s name.

                                            CUSTOMER

   Name:                         Charlie Brown
   Telephone Number:             302-565-1212
   Address:                      989 University St., Harrisonburg, VA. 19801
   Bank:                         First Bank of Rockingham

   Name:                         Lucy Van Pelt
   Telephone Number:             215-343-0035
   Address:                      111 Adams Rd., Chester, PA. 19013
   Bank:

   Name:                         Linus Van Pelt
   Telephone Number:             540-798-0034
   Address:                      11156 Main St., Harrisonburg, VA 22801
   Bank:                         Old Dominion Bank
                                                                                               5
Name:                         Patty Peppermint
Telephone Number:             410-323-1678
Address:                      1456 Navy St., Annapolis, MD. 21401
Bank:                         CrestMoon Bank

Name:                         Linus Van Pelt
Telephone Number:             803-569-1205
Address:                      2378 North St., Winchester, VA 22601
Bank:

Name:                         your name
Telephone Number:             real or made up
Address:                      real or made up
Bank:                         CrestMoon Bank

Note: Use the best foreign key, this does not mean that the best one is the bank’s name.


                                           BANK
Routing Number:       12345
Bank Name:            First Bank of Rockingham
Address:              Harrisonburg VA
Telephone Number:     540-434-2123

Routing Number:       93845
Bank Name:            Old Dominion Bank
Address:              Fairfax VA
Telephone Number:     703-534-2565

Routing Number:       14444
Bank Name:            CrestMoon Bank
Address:              Silver Spring MD
Telephone Number:     301-455-2299




                                                                                           6

				
DOCUMENT INFO
Shared By:
Categories:
Tags:
Stats:
views:0
posted:2/14/2012
language:
pages:6