Database Management System II: How to Create Database – SS2 Data Processing Lesson Note (First Term)
Lesson note at a glance
- Class
- SS2
- Subject
- Data Processing
- Term
- First Term
- Reading time
- 7 min
Creating a database means defining its structure, choosing a field type for each field, and then entering the records.
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.
- Search for, sort and modify records in MS Access.
What are the key terms used in creating a database?
A database is an organised collection of related data stored so that it can be searched, updated and reported on.
- DBMS: database management system, the software used to create and manage a database, e.g. MS Access.
- Table: rows and columns that hold data about one kind of item, e.g. a Student table.
- Field: one column in a table, holding one kind of detail, e.g. Surname.
- Record: one row in a table, holding all the details about one item, e.g. one student.
- Primary key: a field whose value is unique for every record, e.g. admission number.
How do you create a database?
There are three basic steps in creating a database with any DBMS: define the structure, specify the field types, and enter the data.
1. Define the database structure
The database structure states how the data will be organised.
- Type of organisation: the form of database to use, e.g. relational.
- RDBMS: in a relational database, the relational DBMS that will be used.
- Table structure: the layout of each table.
- Rows and columns: the number of records (rows) and fields (columns).
- Key: the field that identifies each record uniquely.
- Relationships: how the tables are linked to one another.
2. Specify the field type
A field type, also called a data type, sets the kind of input a field will accept.
- Purpose: it stops wrong input from being stored, which protects database integrity.
- Names: the name of a data type varies with the DBMS, but the meaning is the same.
What are the common data types?
| Data type | What it accepts | Example |
|---|---|---|
| Alphanumeric/Text | Both numbers and text | ASP2548 |
| Numeric (real) | Decimal numbers | 8.15, 9.1 |
| Numeric (integer) | Whole numbers | 125, 80 |
| Date | Data in date format | 11-04-2009 |
| Boolean (Yes/No) | Only Yes/No or True/False | Yes |
| Memo | Long pieces of text, such as notes and long descriptions | A student’s medical notes |
| Currency | Money values | Fees paid |
| AutoNumber | Numbers inserted automatically for each new record, used to create a primary key | 1, 2, 3 |
| Hyperlink | Links to web pages or files | A web address |
| Attachment | Attached files and images | A passport photograph |
| OLE Object | An OLE object such as a Word document, spreadsheet or PowerPoint presentation | A Word document |
3. Enter the records
Records are stored by typing the data beneath the field names after the data types have been set.
How do you input and save data in MS Access?
In MS Access, data is entered in a table opened in Datasheet View.
- Double click the Student table in the left-hand pane of the MS Access window.
- Type the data beneath the field names.
- Click the next cell to continue entering data.
- Save the table.
How do you save data in MS Access?
Data must be saved regularly to keep the database updated.
- Save option: select Save on the MS Access window.
- Keyboard: press CTRL + S.
- Office button: click the Office button and choose Save.
What are the basic operations on a database?
There are four basic operations on a database: searching, sorting, modifying data and generating a report.
- Searching: finding a particular record or value.
- Sorting: arranging records in order, such as A to Z.
- Modifying: adding new records and editing existing data.
- Generating a report: presenting selected data in a printable layout.
How do you set the search behaviour in MS Access?
Searching in MS Access depends on the default find/replace behaviour you choose.
- On the Tools menu, click Options.
- Click the Edit/Find tab.
- Under Default find/replace behavior, select Fast Search, General Search or Start of Field Search.
- 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.
| Search type | Fields searched | Match |
|---|---|---|
| Fast Search | Current field | Whole field |
| General Search | All fields | Any part of the field |
| Start of Field Search | Current field | Beginning characters |
How do you sort records in MS Access?
Sorting arranges records in ascending or descending order of the values in one or more fields.
- Ascending: A to Z, smallest to largest, earliest to latest.
- Descending: Z to A, largest to smallest, latest to earliest.
How do you sort records in form view or datasheet view?
- Start MS Access and open the database.
- Open the table or form whose data you want to view.
- Click the field you want to use for sorting.
- On the Records menu, point to Sort, then click Sort Ascending or Sort Descending.
- Subform: click the field you want to sort in the subform.
- Sub-datasheet: click the expand indicator to display it, then click the field to sort.
- Note: in a form, you can sort on only one field at a time.
How does sorting work with a sub-datasheet?
- Same level: in datasheet view, Access sorts all the sub-datasheets at that level.
- Several columns: select two or more adjacent columns, then sort them together.
- Order of sorting: Access starts with the leftmost selected column.
- Saving: when you save the form or datasheet, Access saves the sort order.
How do you sort records on a report?
- Start MS Access and open the database.
- Open the report in Design View.
- On the View menu, click Sorting and Grouping to display its dialogue box.
- In the first row of the Field/Expression column, select a field name or type an expression.
- Default order: Access sets the sort order to Ascending when you fill in the Field/Expression column.
- Limit: you can sort up to 10 fields or expressions in a report.
- Sorting levels: the first row is the first sorting level, the second row is the second level, and so on.
- More than one field: add another field or expression to the next row.
How do you modify data in MS Access?
Modifying data means adding new records or editing existing data in a datasheet (table or query) or a form.
- Open a table or query in Datasheet View, or a form in Form View.
- Add a new record or edit the existing data, as shown below.
- New record (Access 2003 or earlier): on the Edit menu, point to Go, then click New Record. Type the data and press TAB to move to the next field.
- New record (Access 2007): click the Home tab, then click New in the Records group.
- Next record: at the end of a record, press TAB to go to the next record.
- Edit part of a value: click the field and type the data.
- Replace the whole value: move the pointer to the leftmost part of the field until it changes to the plus pointer, click, then type.
- Correct a typing mistake: press BACKSPACE.
- Cancel changes: press ESC to cancel changes in the current field and the entire record.
Evaluation
- What is a field type (data type) and why is it used?
- Name the data type that accepts only Yes/No or True/False.
- Which keyboard command is used to save data in MS Access?
- State the difference between Fast Search and General Search.
- How many fields or expressions can you sort in a report?
Assignment
Design a Student table with five fields, such as Admission Number, Surname, Date of Birth, Fees Paid and Boarder (Yes/No). Choose a suitable data type for each field. Then list the steps for modifying a record in the table and for sorting the table by Surname.
This term's Data Processing topics
SS2 First Term · 8 topics
- Database Management System I
- Database Management System II: How to Create Database
- Data Model
- Data Model II: Types of Data Modelling
- Data Modeling
- Data Modeling II: Standard Data Model & Examples
- Normalization
- Entity-Relationship Models

