APDL Public Domain 1

home *** CD-ROM | disk | FTP | other *** search

/ APDL Public Domain 1 / APDL_PD1A.iso / database / powerbase / docs / create < prev next >

Wrap

Text File | 1995-08-21 | 26.8 KB | 591 lines

Ch 4 − Creating a New Database Five steps are involved in setting up a new database:− (1) Create the database application shell. (2) Design the record screen (3) Specify the number of records the database is to contain. (4) Specify the primary key. (5) Build the empty database. 4.1 Creating the database application shell You need to have Powerbase installed on the icon-bar but with no database open, i.e. “No data” should appear under the icon. Click SELECT over the icon and a Save box will appear. Type in the name of your database and drag the database icon to a directory window. Remember that the name cannot exceed 10 characters, including the initial “!”. If you enter more the name will be truncated and you could end up overwriting an existing database with a similar name. You don’t actually need to enter the “!”; Powerbase will insert it automatically. If you open the newly-created application directory (Shift double-click) you will find that it contains files called !Run, !Sprites, !Sprites22 and Colours and four directories called Indices, PrintJobs, PrintRes and ValTables. All Powerbase applications require these objects to be present so don’t delete any of them. 4.2 Designing the record screen When you created the application shell you were left with a blank window on screen and it is here that you must design the database record. This is the lengthiest and trickiest part of setting up a database, although efforts have been made to render it as easy as possible. If you have closed the window just click on the Powerbase icon on the icon-bar to re-open it. 4.2.1 Simple field creation Clicking MENU over the window brings up the New database menu on which every item except Design field is shaded at this stage. Design field leads to a window which lets you specify the characteristics of a field. First decide what category of field you want to create. There are five such categories, selected via radio buttons:− (1) Data (2) External (3) Button (4) Check-box (5) Stamp For the present we will confine ourselves to the first, which is the default selection. As well as the field category you must decide on the type of field within the category. The default offered is Unrestricted, meaning that it will accept all printable characters. We’ll look at other types later. Decide on a name for the field and enter it in the Descriptor icon. This is the name which will appear on the record window and may be up to 40 characters long. You must also enter a Tag, which is used to identify the field in search formulae and is limited to 4 characters. Next enter the Data length; the maximum number of characters the field is to hold. Values up to 246 are allowed. Now click on Create and the field will appear on the record window. It’s probably not where you want it so use SELECT to drag the white rectangle to the position required. When you drop the field in its new position you will see that the descriptor falls into place too. If you want the descriptor somewhere other than to the left of the data icon (above it, for example) you can move it by itself without the data icon moving. That, basically, is all you need to do in order to create fields. To correct a mistake click MENU over the field to be altered and making the required changes. Then click on Update (Create will be shaded). 4.2.2 Deleting, inserting and re-ordering fields. It is important to realise the difference between the physical position of fields on the screen and the internal numbering of the fields. The former is purely a matter of appearance and you may drag the fields about the screen to your heart’s content, but the latter is fundamental to the way the database will function. Each field has a number which corresponds to the order in which it was created. Whenever you invoke the Field creation window the title-bar shows either the number of the (new) field you are about to create (e.g. “New field 3”) or the number of the (existing) field you were pointing at when you opened the menu (e.g. “Modify field 5”). The numbering of fields determines the order in which they will be “visited” by the caret when you are actually using the database, i.e. the editing order. A field may be deleted by clicking MENU over it and clicking on Remove. Fields which come after the deleted field will be found to have had their field-numbers reduced by 1. Inserting a field into the middle of the existing field-sequence is obviously a little more complicated because we have to specify where in the sequence the new field goes. You need to know the number of the field before which you want to place the new one. This number is entered in the before field icon before you click on Create. If you examine fields which come after the insertion you will find that their numbers have been increased by 1. The procedures just described can also be used to change the numbering of a field; first remove it , then re-insert it. There are, however, better ways. Click MENU over an existing field to bring up the dialogue box. We will call this the “current field”. Enter the number of another existing field in the same place as was used above to specify the insertion position of a new field. We’ll call this the “entered field”. You may then do one of the following:− • Click Swap with. This does exactly what you would expect. It swaps the positions of the current field and the entered field in the field sequence. • Click Renumber as. This is a bit more complicated. The current field acquires the number of the entered field. If this involves giving the current field a lower number than previously the fields beyond the new position are all moved up one place to open a gap for it, at the same time closing up the gap left by moving the current field from its old position. If it is being given a higher number the fields above its old position all move down one place, closing up the gap left by its removal and opening a gap in the required place farther up the sequence. 4.2.3 Re-sizing the bounding box Left to itself Powerbase makes all its field icons the same height and of a suitable length to contain the number of characters specified in Data length. This may not be quite what you want. If the Data length is more than about 70 the field runs off the right edge of the window. You may also want to make the field taller for emphasis. (But not for displaying multiple lines. Only fields of Text block type can be multi-line. These are discussed later.) There are two ways of altering the size of a field’s bounding box:− (a) By dragging with ADJUST. (b) By typing in the required width and height in the W and H icons of the Field creation window. The units used are the same OS units used for plotting on the screen. (In mode 27 the full screen is 1240 x 960 OS units.) Besides altering the size of the bounding box by typing the data directly, you may adjust its position in the same way by entering the X and Y co-ordinates of the lower left corner. The units are the same as for width and height but the origin is the top left corner of the record window. This means that the Y values are always negative. Clicking on Fit automatically sets the bounding-box width to fit the data length. 4.2.4 More about tags and descriptors Tags are of great importance in Powerbase. They are used when querying the database to produce reports, export CSV files etc. and also by some internal operations. Every writable field must have a tag and no two fields may have the same tag. Descriptors are less important. Their main purpose is to provide visible labels for fields and in some cases you may not need a descriptor at all. e.g. If your database is to hold names and addresses you might want the record to look like this:- NAME Fred Bloggs ADDRESS 27, Every Street Anytown Woolshire WL4 7XZ There are 5 fields here but, having given the second one the descriptor ADDRESS, you don’t really want descriptors for the remaining 3 (except perhaps POSTCODE for the last). It is quite in order to have null descriptors like this, but your must give each field a tag. Suitable ones might be NAME, ADD1, ADD2, ADD3, ADD4. (Remember they must be unique and not more than 4 characters.) The only cases in which you may omit the tag is where the field is simply an explanatory label and contains no actual data so that there would be no reason to include it in a query. 4.2.5 Other types of Data field So far we have only used fields of Unrestricted type. Clicking on “bump” icons to the left of the field type, or on the menu button to the right, lets you cycle through the various types available. These are:− (a) Unrestricted Accepts any printable character. (b) Alphanumeric Accepts all letters and numerals and common punctuation. (c) Upper case Accepts capital letters and numerals only. (d) Numeric Accepts numerals, +, − and . (decimal point). (e) Yes/No Accepts Y and N only. (d) Date Accepts dates in the form dd-mm-yy or dd-mm-yyyy. (e) Calculated Can’t be typed into directly. Takes its value from other fields (f) Composite Similar to a Calculated field, but for textual rather than numeric data. (g) Time Accepts times in the form hh:mm:ss up to a maximum value of 23:59:59. When Numeric or Calculated is selected, certain icons in the dialogue box which are normally shaded become available. Thus, you can make such a field of fixed-point type and Powerbase will modify whatever you type in so that it is displayed to the specified number of decimal places. For Numeric fields only, you may also specify a maximum and minimum value for numbers which may be entered in these fields. A Date field should be either 8 or 10 characters long in order to hold the date in one of the two formats specified above. The separator in these dates is, by default, a hyphen but may be changed via the Preferences window (see 14.5.1). Note that the actual typing in of dates allows far more flexibility. You may type any non-numeric character as a separator and Powerbase will make sense of entries such as 4/5/87, duly converting it to 04-05-87 when you press Return. A Calculated field is used when you want the record to automatically display the result of a calculation based on other fields. Similarly, a Composite field might be used to display a person’s name in the form FORENAME SURNAME when the record uses separate fields for SURNAME and FORENAME. You don’t specify at this stage how the field value is derived; that’s done when the database is actually working. A Time field (like a date) is checked for validity and (again like a date) you may be fairly flexible in how you actually type the values in. If you enter 3.45;9 it will be reformatted as 03:45:09. The colon separator may be changed via the Preferences window (see 14.5.1). Arithmetic may be performed on times held in this type of field. For a description of the workings of Calculated and Composite fields see Ch 6. 4.2.6 External fields External fields allow you to link Powerbase records to pieces of data of a size and type which make them unsuitable for inclusion in a Data field. Such items are sometimes called “BLOBs” (Binary Large Objects) in the PC world. The field types in this category and the types of data they “hold” are as follows:− (a) Text Plain text files, such as Edit creates. (b) Sprite Sprite files, such as Paint creates. (c) Draw Drawings such as Draw creates. (d) Text block Plain text files, as for (a). (e) Picture Sprite files, as for (b). When you create a field of type Text, Sprite or Draw it appears on the record window as a button bearing a small version of the icon for Edit, Paint or Draw respectively. Files of the appropriate type may be dropped on these buttons, whereupon the file is copied into a special system of subdirectories within the database application. Unlike fields of the Data category the data doesn’t become part of the Database file within the application; the Text, Sprite or Draw file retains its identity and may be exported for editing in the appropriate application. Clicking on the button in the record window will display the file if the filer knows the whereabouts of the relevant editor (Edit, Paint or Draw). Text block and Picture fields take this a step further by actually displaying a text or sprite file on the record window. The bounding box of the icon needs to be of suitable size to hold the text or picture. In the case of a Text block too small a box will cause the text to appear truncated. None is actually lost; it just can’t all be displayed. Too small a box for a Picture field will cause the sprite to spread beyond its boundaries. (N.B. To display the same sprite on each record, e.g. a company logo, define the field as of type Logo, not Picture.) 4.2.7 Check-box fields These are fields whose status alternates between two values when clicked on with SELECT. Three types are defined:− (a) Cross/tick Displays a cross by default. A click changes it to a tick. A second click changes it back to a cross. (b) Null/tick Similar to (a), but first state is an empty box. (c) Null/star Similar to (b), but second state is a star. Check-boxes provide the fastest way of entering true/false or yes/no type data. If you examine the ValStrings file inside the Powerbase directory you will find strings associated with each of these three types the latter parts of which read, respectively:− QNo,Yes Q-,Yes Q-,* These specify what will actually appear in a print-out when a check-box field is included in a query. You may change them if you wish, but don’t omit the initial Q (although this will not appear in the print-out) and take care not to alter the remainder of the string. 4.2.8 Stamp fields Stamp fields resemble Calculated and Composite fields in that you cannot edit them; Powerbase takes care of their automatic updating. The following types are available:− (a) Record number Database record number. (b) Sequence number Unique value numbered upwards from base value set by user. (c) Time Time at which record was created. (d) Date Date on which record was created. (e) Date and time Date and time of record creation. (f) Day Day on which record was created (in the form Mon, Tue etc or day of month as 10, 24 etc). (g) Month Month in which record was created (as either a string; Jan, Feb etc or a number; 1, 2 etc). (h) Year Year when record was created as four-digit number, e.g. 1993 (i) Last altered Records the date and time record is first created and updates it if the record is subsequently altered. Merely displaying the record does not cause updating. (j) Logo Allows a sprite to be included as a logo on every record. All such fields are “stamped” by Powerbase when a record is first entered. When using (a) or (b) make sure the Data length icon contains a large enough value to accommodate the longest number which will be encountered. In the case of (b) the base value from which the sequence numbers begin is entered in the Numeric min box. For the other types the required field length is already known by Powerbase and the Data length box is therefore shaded. Type (d) fields may display the date in any of three formats:− (i) Sun,01 Aug 1993 (called “Date stamp”) (ii) 01-08-93 (called “Date stamp8”) (iii) 01-08-1993 (called “Date stamp10”) (ii) and (iii) are identical to the formats in which Powerbase displays dates of the ordinary editable-data type (see 4.2.5). The numbers refer to the field length occupied by the date stamp. Type (j) fields require the name of the sprite to be entered as the tag of the field to be used as a logo. This means that the sprite name is limited to four characters. You may have several logo fields on your record, all the required sprites being included in a file called UsrSprites which is placed inside the database directory. 4.2.9 Button fields Any or all of the control buttons on the Powerbase keypad may be made to appear on the record window itself. They have exactly the same functions as the keypad equivalents. These button fields let you build a customised database which allows the user to use only the features you want him/her to have access to since the keypad and menus can then be suppressed. There are also four extra buttons (Print, Menu, Exit and Directory) which have no keypad equivalent:− • Print brings up the Print window for report generation. • Exit duplicates the action of Close database on the icon-bar menu. • Directory enables you to open a filer window by clicking on the button. The descriptor may be used to give the name of the associated directory and the link is established by dropping the directory onto the button in the same way that files are linked to fields of External type. • Menu allows you to associate an ordinary Data field with a pop-up menu activated by means of a button. Choosing a character string from the menu enters that string into the Data field. For this to work the field number of the menu button must immediately follow that of the associated Data field. The menu itself is a text file consisting of a heading and the items which are to appear on the menu, each on its own line. The file, whose name is the tag of the associated Data field plus the word “menu”, is stored in the database directory. 4.3 A short-cut to a working database Immediately beneath the Design field entry on the menu is one called Default database. Choosing this is by far the fastest way of getting a database up and running. Its action is to create three files inside the application directory. These are called Form (which holds the record design), PrimaryKey and Database (which will ultimately contain the entered records). The number of records in the database is set to 100, with 25 as the amount by which this should increase when the database becomes full. The primary key is defined as the first four characters of the first writable field. The database is opened and a blank record displayed ready for data entry. Since you can always alter such things as the database size and primary key structure later, you might wish to use these defaults while you experiment with the database. 4.4 Specifying the database size If, however, you want to set the database size yourself at this stage proceed as follows:− (1) Save the Form file (which contains all the field data) from the Save box reached from the menu item. The pathname is correctly set for saving the file inside your database application. (2) You will now see that the Database size choice is no longer shaded and may be used to reach the Size window in which you specify the number of records in the database and the increment for expanding the database when it becomes full. 4.5 Specifying the primary key 4.5.1 General procedure Click MENU and choose Primary key. This opens the Key Structure window. The primary key (or any other key) is derived from one or more record fields called key fields. Up to four key fields may be used to define a key but we will begin by using just one. Four pieces of information need to be specified:− (1) Choose the field, either by clicking on the “bump” icons or choosing from the associated pop-up menu. The default is the first editable field in the record. (2) Enter the word within the field from which characters are to be taken to make up the key. The default is word 1. (3) Enter the position within the word from which characters are to be taken. L (default) means take from start of word, R means take from end. A number (n) means start at the nth character. (4) Enter the number of characters to be taken from the word. The initially-set value is 4 but thereafter the default is the defined field length. After entering this information click on Create to build the database. 4.5.2 Some illustrative examples Supposing our key-field contains a person’s name in the order FORENAME SURNAME. The following settings of word, position and characters would produce the keys shown from the name ALBERT HERRING Word Position Chars Key (a) 1 L 5 ALBER (b) 1 R 4 BERT (c) 1 2 4 LBER (d) 1 4 4 ERT (NOTE: stops at word end) (e) 2 L 4 HERR (f) 2 R 3 ING (g) 2 3 4 RRIN (h) 0 L 5 ALBER (i) 0 L 7 ALBERTH (NOTE: ignores word break) (j) 0 R 8 THERRING (NOTE: ignores word break) (k) 0 4 6 ERTHER (NOTE: ignores word break) Take particular note of what happens if the word number is entered as 0 (or left blank). All breaks between words are then ignored. The field is treated as if the blanks between words had been removed leaving a single long word which is then subjected to the process specified by the position and characters icons. When, on the other hand, the word number is 1 or greater the scanning for characters stops when the end of the word is reached so that the key might be shorter than the character length specified. Example (d) illustrates this. 4.5.3 Using more than one field in a key Key fields should be chosen with care. An ideal key field is one whose contents would never be repeated in another record. Powerbase lets you enforce this condition if you wish (see 11.2.1). Occasional repetitions may not be serious, but a field which can have only a few “values” is usually a poor choice. A customer number or membership number is the sort of thing we are looking for but your database may not contain anything like that. In a database of school pupils the pupil’s name would be a good choice of key, but the form teacher’s name would not, since only a small number of names would be involved, each appearing on the record of many pupils. Suppose you decide to use a person’s name, stored surname first, in a single field whose tag is NAME, as a key. You could use the whole name but it would probably be far too long. The first four letters would be more appropriate but, as this is the start of the surname, you might get a lot of duplication. e.g. PRESTON and PRESCOTT would both have the key PRES − and this is before we even consider people whose surnames are identical. To get round this problem, Powerbase lets you construct an alphanumeric key from characters taken from up to four successive words. You will seldom need to go so far. In the present case a five-letter key made up from the first four letters of the surname and one letter of the forename would be good enough for most purposes. Set up the Key Structure window as follows:− Field Word Position Chars 1st row: NAME 1 L 4 2nd row: NAME 2 L 1 This works quite well. Smith Peter and Smith Janet would have the keys SMITP and SMITJ. Duplication can still occur, but not often enough to be a serious problem. You might, of course, want to store surname and forename in different fields. Let’s call their tags SNAM and FNAM. The key definition is then:− Field Word Position Chars 1st row: SNAM 1 L 4 2nd row: FNAM 1 L 1 and the keys generated will be exactly the same as they would be for the single field NAME. There are databases where no single field is suitable for constructing the primary key. In a database of classical music, for example, there could be a field for the composer’s name and one for the name of the work. Neither is much use individually, the former could contain many occurrences of Mozart and Beethoven and the latter many repetitions of Symphony No.5 or String Quartet in D minor. This is the kind of situation where you might want to use two fields and four words, taking one word from the Composer field and three from the Work field using, say, 4,3,3 and 2 characters respectively from them. The following two works then give the keys shown:− BEETHOVEN Symphony No 5: BEETSYMNO5 MOZART Piano concerto 23: MOZAPIACON23 Note the following points in these examples:− (a) If a word is shorter than the number of letters assigned to it (“No” in the first example) then the whole word is used but no padding is inserted. (b) It may be necessary to omit an insignificant word (“No” in the second example), in order to make a significant one (“23”) come in the first four words of the combined key fields. NOTE It is not usually sensible to build up keys derived from Numeric fields out of separate bits in this way and, indeed, Powerbase won’t allow you to do so. Numeric keys should use one key-field only, the key being the actual numeric value of the field contents. 4.5.4 Other matters We have already noted that a word shorter than the number of characters specified is used just as it is, resulting in a short key. There might be times when you want short words padding out with spaces to give a key of full length and there is a switch to select this action in the Key Structure window. A final factor to be decided is whether the indexing is to pay attention to the case of letters, i.e. if they are capitals or small letters By default indexing is not case-sensitive. Thus if a record has the word “Horse” as the contents of a key field and the first four letters are used as the key then the entry in the index will be “HORS” and you may search for it by entering “HORS”, “hors”, “Hors”, “hOrS” etc. This may not be what you want. If you require indexing to be case sensitive select the switch labelled Case specific. Forcing to upper case as described above does not then take place − keys are inserted and strings are searched for “as is”. The field containing “Horse” will be indexed as “Hors” and only that precise combination of upper and lower case letters will successfully find it. Alphanumeric keys will be ordered according to the ASCII values* of the letters. Since lower-case letters come later in the ASCII table than upper-case ones a record containing “dog” would appear AFTER one containing “Horse”, whereas one containing “Dog” would come before “Horse”. 4.6 Building the empty database Once you are satisfied click Create and blank Database and PrimaryKey files will be created. The record window is redrawn and you may start entering data at once.