5Y/5Z I.T.
SECTION 6 – DATABASE MANAGEMENT
A database is an organized collection of data stored in a set of related tables, from which data
can be easily retrieved and accessed.
A database consists of tables, queries, forms and reports.
Tables
A field (or attribute) is a single piece of data. Every field must be assigned a data type.
Data Type Description
Short Text Holds any piece of data that contains letters,
symbols and digits
Number Holds only numerical data
Autonumber The database automatically assigns a value to
each record that the user cannot change
Date/Time Holds a date (day/month/year). There are
different date formats to choose from
Currency Holds monetary values
Yes/No A checkbox is created where a checked box
means Yes and an unchecked box means No
Calculated A calculation must be inserted using other
fields within the table. Once this is set, the
database automatically calculates a value to
be placed in this field
Each field has field properties.
A record consists of related fields.
A table (entity) consists of multiple records. An entity is a place, person or thing that we can
store data on.
Field > Record > Table
Every table must have a primary key selected.
A primary key uniquely identifies a record. One field is selected as the primary key. This means
that no two fields will have the same value.
A table can also contain a foreign key. This is a field that appears in a table where it is not
necessarily needed. However, it is a primary key in another table.
Foreign keys allow two tables to be joined.
There are other types of keys:
Candidate key: a key that could have been considered as the primary key
Shereen Ali
5Y/5Z I.T.
Composite key: A primary key that consists of two or more fields. Sometimes in a table, one field
cannot be selected as the primary key as it may not contain unique values. A combination of two
or more fields may be unique. Hence, the table will have a composite key.
Relationships
Tables can be joined together so the database can link the fields of the different tables to create
other database objects.
The linking of tables is referred to as ‘creating a relationship’. Each relationship name tells how
the two tables are related
Example: Two tables, Teacher and Student, are created. We would say Teacher teaches Student.
Therefore the relationship between the two tables is teaches.
There are three types of relationships:
1. One-to-One (1:1) – one person has one academic record (that academic record does not
belong to anyone else)
2. One-to-Many (1:M) – one teacher teaches many students
3. Many-to-Many (M:M) – many students learn many subjects
Queries
There are two major types of queries:
1. Select query – this type of query extracts data from tables based on fields selected and
criteria specified.
2. Action query – this type of query makes changes to the database when executed. The
different types are:
• Update – updates fields in a table based on a calculation
• Append – adds records to an existing table
• Delete – deletes a record from a table
• Make Table – selects fields and records from one table and saves it as a new table.
We will create select queries without criteria, select queries using one or multiple tables with
criteria, select queries with user prompts, select queries with calculations, update queries with
calculations, make table queries, delete queries.
Forms
Forms are used to make data entry easier. You can enter data one record at a time. This is helpful
to users who may not be tech-savvy. A form is an interface that allows for easy interaction
between users and the database, without corrupting or deleting the data saved in the tables.
Shereen Ali
5Y/5Z I.T.
Subforms can be created within a form. This allows for data in two tables to be updated by using
one form.
Reports
Reports allow a user to display specific fields from a table or query in a particular order.
Functions within a report:
- Group data by a specific field: example, instead of listing names of students randomly,
they can be grouped by the class they belong to.
- Sort data on a specific field: data can be sorted in ascending/alphabetical or
descending/reverse alphabetical order.
- Generate statistical data such as totals for a specified field, number of records, average
values of a specified field
Shereen Ali
