Lab #19

My name is:  Spencer Johnson

 

 

  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.).

Authors(ISBN list, Author I.D., Author’s Name, Year Born)

Publishers(Pub I.D., Name, Company Name, Address, City, State, Zip, Telephone, Fax, Comments)

Title Author(ISBN, Author I.D.)

Titles(Title, Year Published, ISBN, Pub I.D., Description, Notes, Subject, Comments)

 AUTHORS(AUT_ID, AUTHOR, YEAR BORN), PUBLISHERS(PUBLISHER,NAME, CITY, STREET, ADDRESS, COMPANY NAME) TITLE AUTHOR(ISBN,AU_ID) TITLE (TITLE, YEAR ID, ISBN, PUBLISH ID,DESCRIPTION, NOTES, SUBJECTS, COMMENTS)

  1. 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:
    Title, ISBN, Author, Year Published, Company Name are the headings of the All Titles query.  All four tables were necessary to display the information.  Each table only had a piece and in order to match the ISBN and Pub I.D. with the correct Author, Title, etc. Access had to use all four.
    TITLE, ISBN, AUTHOR, YEAR ID, COMPANY NAME
  2. 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)).

Au_ID(Title Author, Authors), Pub_ID(Titles, Publishers), ISBN(Title Author, Titles)
IBSN(TITLE AUTHOR, TITLES, PUBLISHERS) AU_ID(TITLES, AUTHORS) PUB_ID, AUT_ID(AUTHORS, TITLE AUTHOR)

  1. 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.

That the data is repeated or a duplicate.

 HOW MANY FIELDS THERE ARE CONNECTED TO THAT TABLE.

The Au_ID link connects each author in the author table to a table that matches authors and the titles of their books.  This is a many-many relation because one author has many titles and one title can have several authors.   Pub_ID links all of the books in the Titles table published by one publisher to that publisher’s data.   Similarly, ISBN links each title in the Titles table to the authors that contributed to that title by linking to the Title Author table where there is a matching ISBN entry paired with each author_ID.

  1. 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?

Duplication signifies the ability to link information from one table to another.

IT SIGNIFIES THAT BOTH COLUMNS EXIST IN BOTH TABLES MEANING THAT ARE CONNECTED OR LINKED TOGETHER.
MULTIPLE connections

  1. How did the designer decide to have four tables?   Why not have just one table with each of the different fields as a column?  The designer did not want to have to go through a large cumbersome table to make updates every time a field of data needed to be changed.  Instead, the user can go to the specified data field and the query matches the information together.

      BY GROUPING CEARTAIN FIELDS TOGETHER THAT CONTAINED SIGNIFICANT INFORMATION. Why not have just one table with each of the different fields as a column?  IT PROBALY WOULD BE TO CONFUSING AND ALSO IT WOULD HAVE NOT BEEN EASY TO SEARCH FOR A CEARTAIN FIELD.

  1. 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?

Which ISBN numbers are associated with each author.  It’s a link between Authors and Title Author.
THIS COLMUN IDENTIFIES THE AUTHORS USING AN ID NUMBER TO TELL WHAT POSITION OR ORDER THE AUTHOR IS IN.

  1. Open the Publisher and Titles tables and determine what the untitled columns signify (note that the intersection table does not have such a column).  A link to a table that has duplicate values and then displays them in a list forms.
    THAT THE TITLE TABLE IS LINKED TO THE PUBLISHERS THEREFORE THE READER CAN CALL THE TITLE FROM PUBLISHERS
  2. 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)?

The join operation.

 THE RESULT IS BIGGER FROM THE TABLE WHERE THE MOST INFORMATION IS EXTRACTED FROM THE LEFT AND BOTH QUERIES ARE JOINED TOGETGHER, THUS CAUSING THE RESULTED TABLE TO EXPAND.

 

  1. A smaller table can be created by the operation of extracting some of the columns.   This operation is called a projection

PROJECTION.

  1. 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.  Table1(Author I.D., Title, Year Published, ISBN, Pub I.D., Description, Notes, Subject, Comments)

Table2(Author I.D., Author’s Name, Year Born, Title, Year Published, ISBN, Pub I.D., Description, Notes, Subject, Comments)

      Table3(Author I.D., Author’s Name, Year Born, Title, Year Published, ISBN, Pub I.D., Description, Notes, Subject, Comments, Name, Company Name, Address, City, State, Zip, Telephone, Fax, Comments)

TABLE 1(TITLE, ISBN, TITLE ISBN) TABLE 2(TITLE,AUTHOR, ISBN, TITLE ISBN, TITLE PUBLISH ID) TABLE 3(TITLE, ISBN, TITLE ISBN, TITLE,AUTHOR, ISBN, TITLE ISBN, TITLE PUBLISH ID)

  1.  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.

      VERSION 5.00

Object = "{67397AA1-7FB1-11D0-B148-00A0C922E820}#6.0#0"; "MSADODC.OCX"

Object = "{CDE57A40-8B86-11D0-B3C6-00A0C90AEA82}#1.0#0"; "MSDATGRD.OCX"

Begin VB.Form Form1

   Caption         =   "Form1"

   ClientHeight    =   5730

   ClientLeft      =   60

   ClientTop       =   345

   ClientWidth     =   7440

   LinkTopic       =   "Form1"

   ScaleHeight     =   5730

   ScaleWidth      =   7440

   StartUpPosition =   3  'Windows Default

   Begin MSDataGridLib.DataGrid DataGrid1

      Bindings        =   "Form db.frx":0000

      Height          =   1695

      Left            =   1800

      TabIndex        =   0

      Top             =   1320

      Width           =   4335

      _ExtentX        =   7646

      _ExtentY        =   2990

      _Version        =   393216

      AllowUpdate     =   -1  'True

      HeadLines       =   1

      RowHeight       =   15

      BeginProperty HeadFont {0BE35203-8F91-11CE-9DE3-00AA004BB851}

         Name            =   "MS Sans Serif"

         Size            =   8.25

         Charset         =   0

         Weight          =   400

         Underline       =   0   'False

         Italic          =   0   'False

         Strikethrough   =   0   'False

      EndProperty

      BeginProperty Font {0BE35203-8F91-11CE-9DE3-00AA004BB851}

         Name            =   "MS Sans Serif"

         Size            =   8.25

         Charset         =   0

         Weight          =   400

         Underline       =   0   'False

         Italic          =   0   'False

         Strikethrough   =   0   'False

      EndProperty

      ColumnCount     =   2

      BeginProperty Column00

         DataField       =   ""

         Caption         =   ""

         BeginProperty DataFormat {6D835690-900B-11D0-9484-00A0C91110ED}

            Type            =   0

            Format          =   ""

            HaveTrueFalseNull=   0

            FirstDayOfWeek  =   0

            FirstWeekOfYear =   0

            LCID            =   1033

            SubFormatType   =   0

         EndProperty

      EndProperty

      BeginProperty Column01

         DataField       =   ""

         Caption         =   ""

         BeginProperty DataFormat {6D835690-900B-11D0-9484-00A0C91110ED}

            Type            =   0

            Format          =   ""

            HaveTrueFalseNull=   0

            FirstDayOfWeek  =   0

            FirstWeekOfYear =   0

            LCID            =   1033

            SubFormatType   =   0

         EndProperty

      EndProperty

      SplitCount      =   1

      BeginProperty Split0

         BeginProperty Column00

         EndProperty

         BeginProperty Column01

         EndProperty

      EndProperty

   End

   Begin MSAdodcLib.Adodc Adodc1

      Height          =   330

      Left            =   1800

      Top             =   3000

      Width           =   4335

      _ExtentX        =   7646

      _ExtentY        =   582

      ConnectMode     =   0

      CursorLocation  =   3

      IsolationLevel  =   -1

      ConnectionTimeout=   15

      CommandTimeout  =   30

      CursorType      =   3

      LockType        =   3

      CommandType     =   2

      CursorOptions   =   0

      CacheSize       =   50

      MaxRecords      =   0

      BOFAction       =   0

      EOFAction       =   0

      ConnectStringType=   1

      Appearance      =   1

      BackColor       =   -2147483643

      ForeColor       =   -2147483640

      Orientation     =   0

      Enabled         =   -1

      Connect         =   "Provider=Microsoft.Jet.OLEDB.3.51;Persist Security Info=False;Data Source=C:\Program Files\Microsoft Visual Studio\VB98\Biblio.mdb"

      OLEDBString     =   "Provider=Microsoft.Jet.OLEDB.3.51;Persist Security Info=False;Data Source=C:\Program Files\Microsoft Visual Studio\VB98\Biblio.mdb"

      OLEDBFile       =   ""

      DataSourceName  =   ""

      OtherAttributes =   ""

      UserName        =   ""

      Password        =   ""

      RecordSource    =   "Authors"

      Caption         =   "Authors table from biblio.mdb"

      BeginProperty Font {0BE35203-8F91-11CE-9DE3-00AA004BB851}

         Name            =   "MS Sans Serif"

         Size            =   8.25

         Charset         =   0

         Weight          =   400

         Underline       =   0   'False

         Italic          =   0   'False

         Strikethrough   =   0   'False

      EndProperty

      _Version        =   393216

   End

End

Attribute VB_Name = "Form1"

Attribute VB_GlobalNameSpace = False

Attribute VB_Creatable = False

Attribute VB_PredeclaredId = True

Attribute VB_Exposed = False

Option Explicit

Private Sub Adodc1_WillMove(ByVal adReason As ADODB.EventReasonEnum, adStatus As ADODB.EventStatusEnum, ByVal pRecordset As ADODB.Recordset)

 

End Sub