Touring the Newland Database
DSNs, SQL, ODBC, relationships, tables, queries, forms, reports, recordsetsthat's a lot to absorb! Don't worry if you don't feel as if you've mastered all of this yet. You'll get lots of experience from here on until the end of the book. The important thing is that you understand the big picture: that data is stored in tables; that the Web can be used as a read/write interface for these tables; that tables may be related to one another; and that the connection between the Web and database tables happens through a combination of ASP or ColdFusion, SQL, and ODBC.
In this task, you'll get a quick tour of Microsoft Access and the database that's going to drive the Newland Tours Web site.
Copy the entire Lesson08/Start/newland/database folder into the remote (not local) copy of your site, at the root level.
You'll access the database through the server, so having a copy of it at the local site won't do you any good.
NOTE
Make sure that both the database folder and the newland_tours.mdb file itself have appropriate read and write permissions. Users running IIS, PWS, or ColdFusion locally shouldn't have to worry about this, but users connecting to a remote server may need to ask the server administrator to set the appropriate permissions, or the database won't work. Failure to do so may result in hard-to-interpret error messages.
Open newland_tours.mdb, which is inside the database folder you just copied.
When you open the database file, you see a window that lists several object categories on the left side, and a group of objects on the right. The object categories are the same as the ones discussed earlier in the lesson.
NOTE
Macintosh users won't be able to open the Access file. Follow along using the screenshots or borrow a friend's PC.
Click each of the objects listed on the left in turn, to see the objects that already exist in the database.
As you can see, six tables are in the Tables category, and a corresponding number of forms are in the Forms category. The remaining categories are empty.
In the Tables group, double-click to open each of the tables to see what they contain.
Some of the tables have more data than others. The two tables with the most information are tbl_country and tbl_toursthese will be used to populate the Country Profile and Find a Tour segments.
When you open tbl_journal, you'll see that it contains the text used in the Traveler's Journal section of the homepage.
The tbl_agents table is odd in that it contains only numbers, and no names as you might expect. The reason for this is that these numbers are actually foreign keysthe user is userID from tbl_users and the specialty is regionID from tbl_region.
The tbl_users table contains information used for logging in and authenticating at the site. When users register, their information is stored in this table. Notice that there are two categories in the userGroup field: admin and visitor. Users can access different areas of the site, depending on their userGroup category.
Close the tables window to return to the main screen.
In the Forms group, open each of the forms, navigating through records to see what they contain.
The forms, again, are convenient interfaces through which one can enter data into the tables. You won't use these in the Newland Tours siteforms aren't accessible through the Web, so you will create your own Web forms to replace them. But I used these to create the tables used in the site, and I left them in place, so you could explore them.
Click the Relationships button on the toolbar.
Access has a convenient visual interface that lets you view and modify relationships among tables.
Review the relationships among the different tables.
The bold field in each table is its primary key. Foreign keys are linked to primary keys by a line. Do you remember how tbl_agents had only numbers in it? Now you can see why. The table comprises a primary key (agentID) and two foreign keys (user and specialty). Notice also that tbl_journal isn't linked to any other tables. Not every table needs to be part of a relationship.
Do any further exploring without making any changes, and then close all open windows and Access.
You've now had a crash course in Access and familiarized yourself with a bona fide database.
