Lab #19

1. A database is different from a file system in that it is a software program that manages data rather than records. Records are physical units of data even though they are logically defined (unlike disk sectors, that are physical units of space that may have records or not, or may split a record across a sector boundary). A database organizes the fields of data to form “records” on the fly, whatever clumps of information that the user happens to want to see. More importantly, databases maintain relationships among multiple records whereas file systems are ‘flat”—they only access the data they contain as a group of homogeneous records. Thus, with files you have to program the kind of reports and queries you will allow, but for databases you can have a query language that allows the user to extract any collection of data she chooses. The steps for setting up the database are similar to those required for file systems, you must layout your data fields, you must load your data onto the disk, and you must then interact with the stored data to generate reports. Today we will concentrate on specifying the data layout. Relational databases are built around tables. Access is an example of a relational database system. Open Access and find the copy of the biblio.mdb that comes with Visual Basic in the VB98 directory. Open it, and you will have to convert it, giving it a new name: biblio2.dmb (you will have to use this converted copy because the VB6.0 examples use Office ’97). You will then be able to see that the database consists of 4 tables. Examine each table and list below its fields (the usual notation is table( fieldname1, fieldname2, fieldname3, etc.).

2. Click on the Query Box under Objects and run the All Titles query. Note the headings of the columns. Find these column headings and correlate them with the fields listed above, and identify how many tables participated in the query response:
   
   
3. View the query results in Design mode verify the relation the data fields have to connect the tables. There is data about authors, about books (titles) and about publishers. There are fields in each of these three tables that are duplicated in another table. These duplicated fields are shown linked to each other. List below the link fields and the tables they connect: (use the notation linkname (table1, table2)).
4. The links have numbers at each end. These numbers signify either 1-1 (the default, so they could be omitted) or 1-many. Explain, in terms of the links listed above what the numeric order of the link signifies.
5. The title author table is called an intersection table because it simply pairs two keys, there is no other data in the table. If you sorted this table on the authorID column, you would have duplicates of the author key. Likewise, if you sorted on the title column, you would possibly have duplicates of the ISBN key. On the other hand, were you to sort the Authors table on the authorID column or the Titles table on the ISBN column there would be no duplicate keeys. What does the duplication signify?
6. How did the designer decide to have four tables? Why not have just one table with each of the different fields as a column?
7. Access provides two methods to load data onto the disk, typing it directly into a table (create a table by typing data) and via data entry forms. Once the table is created, you can add items either way. Open the Authors table and observe that data is not entered in all fields (birth year is non-mandatory) and that there is an untitled column at the start of the table. What does this column tell the reader?
8. Open the Publisher and Titles tables and determine what the untitled columns signify (note that the intersection table does not have such a column).
9. Relational databases match the values in repeated fields of different tables to connect or “relate” the rows of the two tables. Imagine that the duplicate field appears as the first field of one table and the last field of the other, and that both tables are sorted ascending on this field. The result of what operation is a bigger table whose rows are all those that can be made choosing the left half from the first table and the right half of the row from the second table where the duplicate fields have the same value)?
10. A smaller table can be created by the operation of extracting some of the columns. This operation is called a
11. A third operation on a table is to extract only those rows from the table that are identified by some value in a specified column (retrieve rows with the specified characteristic). By using these operations in alternatively, a clever user can sift through a database and build the table that has exactly the rows and columns that answer the question at hand. Return to the All Titles query and view it in SQL mode. SELECT chooses the columns that the user wants to see and ORDER sorts the selected table. The rows of the big table FROM which the selection are made are produced by successively doing the INNER JOIN operation on two tables with the same fields, the last of which produces Table3 as a result of Publishers INNER JOIN (Table2) ON Publishers.PubID = Titles.PubID, where Table2 must have the column Titles.PubID. Table2 is produced by Authors INNER JOIN(Table1) ON Authors.Au_ID = [title author].Au_ID where Table1 must have the column [title author].Au_ID as well as the column Titles.PubID. Table1 is formed by [title author] INNER JOIN Titles ON [title author].ISBN = Titles.ISBN. Use the notation of question 1 to show the fields of Table1, Table2 and Table3.
12. The result of a query such as All Titles is a Recordset. You may think of a recordset as a table in memory that may or may not exist in a database (it could be a copy of a database table or it could be, as in this case, a derived table) but VB considers a Recordset an object with properties (somewhat like an array with its valid subscripts) As such the Recordset object supplies records to other objects or controls, hence behaves as a RecordSource (analogous to a Stream of text records). It is possible to create and manipulate several recordsets simultaneously and they could be derived from the same or different databases. To create a recordset in VB we have to create a runtime connection to a database and transfer records back and forth from the program to the disk. The software to do this depends upon the nature of the database (VB can exchange data with many different database systems) but Access requires use of the Microsoft Jet 3.51 OLE DB Provider as described on p. 774. The connection is like a pipe with one end in the database (feeding in data through the ADO Data Control and the other in the program where the data is received by a data aware control such as the DataGrid control (the others are listed in Fig 18.3). The author uses the DataGrid control to show how any VB project can access the same data that the Access user sees. However, you cannot use the ADO Data Contol to reference biblio1.mdb as it is designed to interface with Access ’97. Perform the steps shown on p. 774 and paste the .frm file here.