Introduction to Database Management Systems using dBase III+ =============================================================== READINGS: CU - 4 (databases) BACKGROUND AND INTRODUCTORY STUFF: - what is a database? - the various meanings of 'database' - base of data - base of data with relations - program to manage base of data - how 'database' relates to 'knowledge base' - purpose of database management system (DBMS) - manage large volumes of data - the simple database - flat file format - records and fields - operations that may be performed on records - adding records - updating/chenging records - deleting records - operations that may be performed on groups of records - selecting - sorting - math functions INTRODUCTION TO DBASE III+ - Introduction to dBase III+ - capabilities and limitations - maximum fields per record: 254 - maximum characters per record - standard database file: 4,000 - database text file: 512,000 - maximum records per database: 1 billion - maximum characters per database: 2 billion - maximum characters per character field: 256 - accuracy of numeric fields: 15.9 digits - largest number: 1 x 10 to the +308 power - smallest positive number: 1 x 10 to the -307 - maximum memory variables available: 256 - maximum memory variable bytes: 6,000 - maximum files open at one time: 15 - maximum database files open at one time: 10 (database file = 2 if memo fields are used) - maximum index files per open database file: 7 - maximum characters in a command line: 254 NOTE: these limits vary with computer hardware and disk capacity - field types - character (max 254 bytes) - numeric (max 19 bytes) - date (max 8 bytes) - logical (max 1 byte) - memo (max 5000 bytes or capacity of word processor used) DBASE III+ COMMANDS (*) dBase HELP HELP or PF1 - gives menu of help topics - will try to start from your current location in dBase - will automatically offer help if you give a command it dosen't understand (*) Placing a database in use USE example: USE PHONE - places PHONE.DBF in use - if there is an associated .DBT (memo) file it will also be opened (*) Examining structure of database DISPLAY STRUCTURE - first must have a database in USE - will show fields structure of database (*) Browsing the database BROWSE - can optionally change or add data (*) CLEAR ALL - all database files are cleared and all active memory variables are released from memory (*) QUIT - close database and all related files (*) Creating a database structure (.DBF file) CREATE - will ask for name of database file - prompts for definition of fields - allows data entry at this time (*) ADDING RECORDS TO DATABASE APPEND (manual adding) - provides you with a data entry screen - adds records to the .DBF file currently in USE (*) adding groups of records automatically from a file APPEND FROM - appends records from to .DBF file currently in USE - this must be another .DBF with fields having same names and types APPEND FROM DELIMITED - appends from ASCII file with fields delimited by commas - other options: - SDF (ASCII fixed-field file) - DIF (VisiCalc/SuperCalc DIF file) - WKS (Lotus 1-2-3 file) - SYLK (Multiplan SYLK file) (*) conditional expressions = equals <> or # not equals > means greater than < less than >= greater than or equal to <= less than or equal to $ contained within .AND. .OR. .NOT. (*) DISPLAYing data - for example USE precreated student database that has MIDTERM field with false data DISPLAY - displays the current record DISPLAY ALL - displays all fields DISPLAY , , etc... - displays certain fields for current record DISPLAY ALL , , etc.. - displays certain fields for entire database DISPLAY ALL FOR - displays all fields for certain conditions DISPLAY , , ... FOR - displays certain fields for certain conditions (*) going to a particular record GO n (top/bottom) GOTO n SKIP SKIP n SKIP -n (*) COUNT - to get count of number or records meeting criteria COUNT - counts all records COUNT FOR MIDTERM > 80 COUNT FOR MIDTERM > 80 TO memvar (*) SUM - to get sum of a field SUM MIDTERM FOR MIDTERM > 80 (*) AVERAGE - to get arithmetic average of numeric field AVERAGE MIDTERM - display average of variable MIDTERM AVERAGE MIDTERM TO memvar - calculate average of MIDTERM and store in variable 'memvar' AVERAGE MIDTERM FOR AGE > 20 (*) the COPY command for copying databases - to make a complete copy of DBF file (structure and contents) COPY TO - to copy structure and subset, use conditionals COPY TO FOR - to copy certain fields with conditionals COPY FIELD , ... TO FOR - to copy structure only -- no data, empty database COPY STRUCTURE TO (*) more COPY COPY TO filename DELIMITED - trailing blanks removed and fields delimited by commas (ASCII) COPY TO filename SDF - trailing blanks intact and extra blank between fields COPY TO filename TYPE WKS (*) SORT SORT ON fieldname TO filename - copies database structure and contents, sorted on the named field in alaphanumeric order, to the named file SORT ON fieldname TO filename DESCENDING - copies the database structure and contents, sorted on named field in descending order, to the named file SORT ON field1, field2 TO filename - multiple field sort SORT ON field1 TO filename FOR expression - sorts records matching 'expression' to specified file (*) Command files - allow sets of commands to be entered. These may be used as macros or as procedures that may them be integrated into larger dBASE-III+ programs MODIFY COMMAND - creates a .PGM file - create a simple DO file and execute it - note that dBASE allows for variables, has comments, and allows one command file to call another command file as a procedure (*) LOCATE/CONTINUE - for locating records that meet specific criteria LOCATE FOR MIDTERM > 85 CONTINUE (*) modifying the structure of a database MODIFY STRUCTURE - you should make a copy of database first just to be safe COPY TO example: - add MIDTERM field (*) INDEX - faster way to sort large files - does not create new database, only .NDX file USE STUDENT INDEX ON LNAME TO TEMP1 INDEX ON TERM+LNAME TO TEMP2 USE STUDENT INDEX TEMP1 REINDEX - guarantees that any changes made are reflected in the index (*) CREATE/MODIFY SCREEN - creates input and display screens for use with APPEND, INSERT or EDIT - can combine LOCATE/CONTINUE and EDIT to view various records in specific forms - can program into command file (*) CREATE/MODIFY LABEL - creates a label format file CREATE LABEL filename MODIFY LABEL filename (*) LABEL FORM - to print labels from a database LABEL FORM filename TO PRINT LABEL FORM zip WHILE ZIP = '16802'