First Lab Assignment: Creating the Database and Entering Destinations

Today your task is to become familiar with basic operations in Microsoft Access. Because the prior experience with Access varies among students, some of you will find this rather easy and some of you will be more challenged. It is important that you do what is asked, and also that you understand why you did what you did. If you do not know how to do a particular operation, use the Help system before you ask for the instructor's assistance.

For compatibility with the notebook computers, you should do all work on this project using Access 2000. Databases created in one version of Access are not compatible with another version of the software, and conversions from one version to another rarely retain all the functionality.

You will use the following skills to complete this assignment:

• Create a database
• Use a CASE tool (Visible Analyst) to create a database structure
  1. Generate SQL Schema in VA
  2. Execute queries in Access to implement the schema
     • Create table structures
     • Edit table structures as necessary
     • Relate tables
• Create a data-entry form
• Populate a table using a data-entry form
• Populate a table by importing comma-delimited data
  1. Create a CSV file
  2. Modify database structure if necessary
  3. Get external data

Before starting on this assignment, review the situation analyzed in MIS 321: Adventures Afloat. Based on the analysis by student groups in that class, an ER diagram has been developed. For clarity, the ERD shows only entities and primary and foreign key attributes. A report generated by Visible Analyst lists all attributes and their characteristics. See also the SQL schema generated therefrom.

i	You are encouraged to use the Visible Analyst output to create the project.
@	Create a new database named ADVAFL. All database objects (above the level of fields) should be named using Hungarian Notation.
@	Create unpopulated tables for each of the entities in the ERD. Refer to the SQL Schema and the report. If you are using the VA-generated schema, stop after creating tblWWWLinks. You must alter the table structure (next step) before issuing the Alter Table commands that establish the relationships.
@	Unfortunately, generating Access code via Visible Analyst does not identify the fields (usually primary keys) that should be AutoNumber fields. You must open each of the following tables in design view and change the Data Type of the indicated field to "AutoNumber." Table Field tblAssignments    AssignmentNum tblDestinations    DestinationID tblPersons    PersonID tblReservations    ReservationNum tblSpecificTours    TourNumber tblStandardTours    TourID tblWWWLinks    SiteNumber
	Click this icon to see a sample.
@	Establish the relationships using either the VA-generated schema or the relationships screen. Regardless of method, once you have established the relationships, arrange the relationships screen so it is readable.
@	Create a simple form for the entry of data into the Destinations table. Hint: I find it easier to create new forms using the forms wizard.
@	Enter the following data into your Destinations form: City,      Region,      Country      Durnstein, Lower Austria, Austria Melk, Lower Austria, Austria Salzburg, Salzburg, Austria Vienna, Lower Austria, Austria Bastogne, Luxembourg, Belgium Brugge, West Flanders, Belgium Brussels, Brabant, Belgium Waterloo, Brabant, Belgium
@	Add the remainder of the data in the Destinations Document to the Destinations table – either by entering each item using your form or by importing comma-separated-value (CSV) data.
�	DELIVERABLE  During your team meeting next week, you will be expected to have an ADVAFL database with all tables, fields, keys, and relationships established, frmDestinations developed, and tblDestinations fully populated.