DATABASE MANIPULATION

Visual Tools

 The vast majority of modern database packages use visual tools for manipulation of data. Examples are packages such as Microsoft Access, Delphi and Visual FoxPro. We will concentrate on one of these packages Microsoft Access in this section.

 Amending the data types and field sizes

 · Use File & Open and open up a database.

· Click on a table in the list.

· Click on Design in the toolbar.

· To change a field name, click into the field name cell and alter the field name.

· To alter the data type, click on the data type cell besides the relevant field name. Click on the drop down list and select the new data type. Take care in altering data types when data is already in the table, as this data can be affected or even deleted by selecting an incompatible data type.

· Field Size or field properties can be changed from the first row in the General tab below. Again if you are reducing a field size, take care as data can be deleted.

· A field can be set as a Primary Key from this screen as well. A Primary Key must be unique and Access will enforce this if the user attempts to enter identical data into the primary key field in more than one record.

Inserting & Amending Data

 Data can be inserted and amending in a variety of manners. In MS Access data can be inserted using a basic datasheet view or by a specially designed form.

 Datasheet View

 In a datasheet each field is displayed in cells within rows, with each row being a record. A blank record is always present at the bottom of the datasheet, simply click into the first cell in this row to start entering data. Type in data using the keyboard and move to the next field or cell by pressing enter, tab or by using the cursor keys.

 Data can easily be amended by clicking into an existing cell and using the backspace or delete keys to delete data and then amend the data by typing in the new value. You can also overtype existing data.

 Deleting an entire record is different. Merely deleting the data from all the cells in a record would not delete the empty record from the table. This is done by clicking on a record and then using the Edit menu, followed by clicking on delete row. This deletes the data and the record from the table.

 The disadvantage of using the datasheet view, instead of a form, is that the datasheet view is quite restrictive. On a form the designer can set up the fields to different sizes, depending on the type and sizes of data expected. Special objects are used for special data types like pictures and the like.

Form

 A form is a much more user-friendly tool for entering data into a table. Text boxes and edit boxes are set to specific sizes so that data can be best displayed. Instead of having just a row on a datasheet for each record, a single record fills the entire form. Pictures can be displayed as part of the form, and this avoids the need to double click on a cell in the datasheet view to see the picture or object.

 New records can be inserted by hitting on the new record button.

 The user can move forward one record.

 The user can move back one record.

 The user can move to the first record in the table.

 The user can move to the last record in the table.

Creating a Form

 In MS Access two methods exist for creating a form. Using the form wizard or using the form design view.

 Using the wizard is an easy way to create attractive but simple forms. Often a good idea is to create a form using the wizard and then make adjustments in the form design view.

 To create a form using the wizard, click first on Forms on the left hand side under Objects. Then double click on "Create form by using wizard".

 Step 1

 Select the fields you want to show on the form, by clicking on the field name and then clicking on the arrow to move the field into the selected fields box. Then click on Next.

 Step 2

 Select the required layout from a choice of 4 layouts and then click on Next. The normal is Columnar.

The tabular layout is similar to the columnar layout, except that more than one record is shown onscreen at a time.

The datasheet layout basically just gives you a fancy looking datasheet.

The justified layout is like the columnar layout, except that it forces the text boxes and objects in the form to fill the entire form and not leave any gaps.

Step 3

 Select the style that you like best, this is purely cosmetic. Then click on Next. 

Step 4

Give the form a name and then click on Finish. If you wish to open the form for immediate editing from the design view, select this option first.

Editing the Form in the Design View

 Editing forms in design view is similar to working with a graphics package. Text boxes and labels are objects, which can be resized or moved about using the mouse. The toolbar shows the different types of objects that can be used. You can choose from labels, text boxes, option groups, tick boxes, list box, command buttons, image controls and many more. The most common are labels and text boxes for normal text fields.

 You may wish to edit the properties of a particular object. The Control Source is an important property as it tells the object the record and table it is linked to, if indeed it is linked to a particular record. Each object has numerous properties from the color of the object, to more complicated things like "Input Mask", which can be used to limit the type of data that can be entered.

 You can also use the properties to validate the data that is entered. To ensure it is the correct type of data that you want to hold in the table.

Introduction

 There are different ways in which you can approach data analysis. We are going to examine two possible approaches - the top-down approach and the bottom-up approach - and compare their strengths and weaknesses.

One method starts with the big picture, the whole concept, and breaks it down into smaller, more manageable chunks, while the other method starts from the bottom with the different data components and fits them together to make the larger model.

Top-down data analysis

 The top-down approach to data analysis involves a number of steps to build the data model for your database.

Step 1 - Identify data entities

The first step is to identify the data entities involved. A data entity can be anything about which data needs to be stored, physical or otherwise. For example, ORDERS could be one entity for the data model and CLIENTS could be another. With the top-down approach, this is the top level in the analysis.

An instance of an entity is an example of an entity. An entity is not a specific item but a grouping of items that can be grouped together. Within a database, an entity would be a single row in a table. An example of an instance of an entity would be 'Joe Brown', which is an instance of the CLIENTS entity.

Step 2 - Attributes and properties

 The next step is to identify the attributes for each of the entities that have been defined. An attribute is some item of information about the entity, which can be stored against it. Time should also be spent defining the properties for these attributes. For example, attributes of CLIENTS could be total sales, company name, address, telephone number, e-mail address etc. An example of a property for the total sales attribute is that it must be of type NUMERIC.

Step 3 - Relationships between entities

The last step is to identify the relationships that exist between the different entities. A relationship is an association between instances of entities. For example, a CLIENT may have many ORDERS, but an ORDER can only have one CLIENT.

Once each of these steps has been carried out, the whole data model has been defined from the top down. This method of analysis results in a well organized data model.

Bottom-up data analysis

 The bottom-up approach to data analysis involves a different set of steps to build up the data model for your database.

Step 1 - Identify data elements

 The first step for an organization using the bottom-up approach is to start at the bottom and identify data elements contained in documents, reports, files etc. Similar elements may have different names and so must be carefully matched to the right elements.

Step 2 - Grouping elements

 The next step is to take the various elements and group them into entity types. Once this has been done the relationships can be identified between the entities, so giving us the final data model.

This approach to data analysis enables us to create a more complete data model.

Strengths and weaknesses

 The main advantage to using the top-down approach to data analysis is that it results in a well-structured and well-organized data model. On the down side however, is the fact that information could easily be overlooked especially when there is a wide variety of data to be considered.

 The bottom-up approach does not suffer from this problem. As a result of gathering all the data elements together to start with, there is less chance of data being overlooked and therefore a more complete data model is created. However, the bottom-up approach suffers from the fact that the resultant data model will not be as well organized as it could be and will model the application level closer than the real world situation.

It is possible to combine the approaches to insure a well-structured data model as well as a complete data model. This is done by first developing a more general data model and then, or even at the same time, discovering the data elements using the bottom-up approach. Once this has been done the data collected needs to be fitted to the general data model, which can be modified if need to be to fit the needs of the data.

 This results in a model that benefits from the best of both approaches to data analysis.

FOR MORE INFORMATION CLICK HERE