Database Management System II: How to Create Database – SS2 Data Processing Lesson Note (First Term)

A database is created by defining its structure, specifying a field type for each field, and then entering the records. Once data is stored, the main operations are searching, sorting, modifying data and generating a report. This note explains these steps using MS Access as the example.

Behavioural objectives

By the end of the lesson, students should be able to:

• List the basic steps in creating a database.
• State the common field types (data types) and what each accepts.
• Enter and save data in an MS Access table.
• Name the basic operations performed on a database.
• Sort records and modify data in MS Access.

How do you create a database?

Creating a database using any DBMS involves the following basic steps.

1. Define the database structure

The database structure specifies the type of database organisation that should be used. If the relational form is selected, the database structure will include the RDBMS, the structure of a table, the number of rows, the number of columns, the key, and the relationship of the database, etc.

2. Specify the field type

When a database is being created, all fields are set to accept a particular type of input by specifying a field type. A field type is also known as a data type. The purpose of a data type is to prevent wrong input from being stored in a database (that is, to protect database integrity). The name of a data type varies with the DBMS, but the general meaning is the same. The data types include:

• Alphanumeric/text field: Fields that accept both numbers and text, e.g. ASP2548.
• Numeric fields: Fields that accept numbers in two forms: real numbers, i.e. decimal numbers, e.g. 8.15, 9.1, and integers, i.e. whole numbers, e.g. 125, 80.
• Date fields: They store data in date format, e.g. 11-04-2009.
• Boolean fields: The data accepted by these fields is either Yes/No or True/False.
• Memo: Long text. Use it for long pieces of text, such as notes and long descriptions. It can store up to 64,000 characters.
• Currency: Use for currency.
• AutoNumber: Unique sequential numbers or random numbers are inserted automatically when you create a record. It is used to create a primary key.
• Hyperlink: Use to store hyperlinks.
• Attachment: Use to store attachments, e.g. files and images.
• OLE Object: Use to attach an OLE object such as a Word document, a spreadsheet or a PowerPoint presentation.

How do you input data in a database?

After the field names and their data types have been specified, the records are stored in the database by entering the appropriate input.

In MS Access, to input data:

1. Double click on the Student Table in the left-hand pane of the MS Access window.
2. Enter the data beneath the field names and click on the next cell to populate data.

To keep the database updated, data entered into the database must be saved regularly. Every DBMS has commands for saving a database. In MS Access, select the Save option on the MS Access window, or press CTRL + S. Alternatively, click on the Office button and save.

What are the basic operations on a database?

The basic operations to be considered are:

1. Searching
2. Sorting
3. Modifying
4. Generate report

Searching

1. On the Tools menu, click Options.
2. Click the Edit/Find tab.
3. Under Default find/replace behavior, select one of the following:
   • Fast Search: searches the current field and matches the whole field.
   • General Search: searches all fields and matches any part of the field.
   • Start of Field Search: searches the current field and matches the beginning characters of the field.

Sorting

Sorting records in form view or datasheet view

1. Start MS Access, and then open the database that you are working with.
2. Open the table or the form whose data you want to view.
3. Click the field that you want to use for sorting records. To sort records in a subform, click the field that you want to sort. To sort records in a sub-datasheet, display the sub-datasheet by clicking the expand indicator, and then click the field that you want to sort.
4. On the Records menu, point to Sort, and then click Sort Ascending or Sort Descending.

Note: In a form, you can sort on only one field at a time.

Sorting with a sub-datasheet

In datasheet view, when you sort records, MS Access sorts all the sub-datasheets at that level. In a datasheet or sub-datasheet, you can select two or more adjacent columns at the same time and then sort them. Access sorts records starting with the leftmost selected column. When you save the form or datasheet, Access saves the sort order.

Sorting records on a report

1. Start MS Access, and then open the database that you are working with.
2. Open the report in Design View.
3. On the View menu, click Sorting and Grouping to display the Sorting and Grouping dialogue box.
4. In the first row of the Field/Expression column, select a field name or type an expression.

Note: When you fill in the Field/Expression column, MS Access sets the sort order to Ascending.

You can sort up to 10 fields or expressions in a report. To sort your report on more than one field, add another field or expression to the Field/Expression column. The field or expression in the first row is the first sorting level. The second row is the second sorting level, and so on.

Modifying data

To add or edit data in a datasheet (table or query) or a form:

1. Open a table or a query in Datasheet View, or a form in Form View.
2. Do one of the following:
   • In MS Office Access 2003 or earlier versions, to add a new record, point to Go on the Edit menu, and then click New Record. Type the data, and then press TAB to go to the next field. At the end of the record, press TAB to go to the next record.
   • In MS Office Access 2007, to add a new record, click the Home tab, and then click New in the Records group.
   • To edit data within a field, click on the field that you want to edit, and then type the data.
   • To replace the entire value, move the mouse pointer to the leftmost part of the field until the pointer changes into the plus pointer, and then click. Type the data.

Note: To correct a typing mistake, press BACKSPACE. To cancel your changes in both the current field and the entire record, press ESC.

Evaluation

1. What is a field type (data type) and why is it used?
2. Name the data type that accepts only Yes/No or True/False.
3. Which keyboard command is used to save data in MS Access?
4. State the difference between Fast Search and General Search.
5. How many fields or expressions can you sort in a report?

Assignment

1. State the operations that can be performed in a database.
2. List five (5) data types.
3. List the steps in modifying data.