outline

Databases

• Sequential access is used when records with be of differing length
• Allow more flexibility, but more difficult programming
• Require programmer to keep tack of the record number
• Can use custom variable types

Random Access

• Individual pieces of data may be referenced without having to process sequentially all of the information that precedes that data in the file.

Random Access Files

• The simplest is to require that all records in a file are of the same fixed length
• Using fixed length makes it easy for a program to calculate the exact location of any record relative to the beginning of the file
• Data can be inserted, updated or deleted and each random-access file will have one of these three access types

User defined TYPES

• A compound variable that combines many standard variable types
• Usually refers to a RECORD in a database and is also called a RECORD variable
• Usually fixed length which is ideal for ramdom-access

The TYPE statement

• TYPE newType
      var1 as datatype
      var2 as datatype
      var3 as datatype
  END TYPE
  GLOBAL variable AS newType

Description

• newType is your custom data type
• Usually ends in TYPE (by our convention)
• This is NOT a variable. It is a new variable TYPE (like string, integer, etc.)
• var1, var2, etc. are regular variable names
• Datatype refers to standard variable types (string, integer, single, etc.)

Example

• Type AddressType
      Name as String * 30
      Phone as String * 15
      Add as String * 50
  End Type
  Global Address as AddressType

Use of record (compound) variables

• Are much like Object Oriented Programming syntax
• Variable.Record (e.g., Address.Name = "Andy")

Files (description)

• Must be fixed-length records for random-access file
• More like a CD player. Individual records can be accessed directly
• Frequently use custom data types

OPEN statement

• OPEN filename for RANDOM AS #filenumber LEN = LEN(RecordVariable)
• Example:
    OPEN "A:\Address.dat" for Random as #1 LEN = LEN (Address)
• The same open statement is used for reading, writing, and appending.

PUT statement

• Is used to Copy information from a variable to the file
• The filename must have been opened with an OPEN for RANDOM command

Syntax:

• PUT # fileNumber, RecordNumber, RecordVariable
• Example:
  to place the current contents of the address variable to the 4th position in the file:
    PUT #1, 4, Address
• To place the current contents of the address variable in the position in the file contained in the recNum variable:
    PUT #1, recNum, Address

GET statement

• Identical to the PUT statement except it transfers information FROM the database TO a variable
• Filename must have been opened with an OPEN for RANDOM command

Syntax:

• GET # FileNumber, RecordNumber, RecordVariable
• Note: Filename must have been opened with an OPEN for RANDOM command

Example:

• To place the current contents of the 4th position in the file to the address variable
    GET #1, 4, Address

Notes on use of GET and PUT

• Both are frequently put in separate procedures to simplify use
• Both require an OPEN statement before use and a CLOSE statement after

Sample GET procedure

• (assumes Record variable called Address)
• Sub GetAddress
     OPEN FileName for Random as #1 Len = Len(Address)
     Get #1, RecNum, Address
     Close #1
     TxtName.text = Address.Name
     TxtPhone.text = Address.Phone
     TxtAdd.text = Address.Add
  End Sub

Notes:

• The previous code assumes access to variables FileName, RecNum and Address (the Record Variable)
• Notice that we first GOT the variable from the file, then copied it to the form
• This code would probably be at form-level

PUT procedure

• A similar PUT procedure would:
     Copy the fields from the form objects to the variable
     Open the file, put the variable to the file
     Close the file

Parallel Arrays

• Loads all records into a record array (record variable) or a series of arrays in memory
• Provides fast execution
• Loads from the disk only once
• Only allows a number of records that will fit into memory at one time
• Information is volatile until saved.

Linked Lists

• Each record contains a field that points to the next record
• Done by adding extra field(s) in custom variable type
• There is a Pointer field containing the record number of the next/previous record in the field
• Allows for convenient sorting, but slow searching
• Double-linked lists

Binary tree

Indexing

• A separate file or array is maintained comparing the record number to the position in a sorted list
• Various indices can be created sorting by different criteria
• This can be easily created with sorted list box technique in Visual Basic

Relational databases

• Information is contained in more than one table of data
• One table may track personnel, another may track payroll
• Links between tables
• Allows flexibility

Sorting algorithms

• Bubble sort - slow but unwieldy - easy to write
• Shell sort - more efficient but more complex
• Quick sort - among the best overall techniques, uses recursion