Chapter 2 Databases Fundamentals
Session 1.1 Creating a Database
The Access Window Visual Overview
- Shutter Bar/Open/Close Button: allows you to close and open the Navigation Pane; you might want to close
the pane so that you have more room on the screen to view the object’s contents.
- Navigation Pane: the area that list all the objects (tables, reports, and so on) in the database, and it is the
main control center for opening and working with database objects
- Datasheet View: shows the table’s contents as a datasheet. The status bar indicates the current view, in this
case, Datasheet view.
- Add & Delete group: contains options for adding different types of fields, including Text and Currency, to a
- Click to Add column: provides another way for you to add fields to the table
- Access Windows: the program window that appears when you create a new database or open an existing
database. You use the window buttons to minimize, maximize, and close the Access windows.
- Datasheet: it displays the table’s contents in rows and columns, similar to a table that you create in a Word
document or an Excel spreadsheet. Each row will be a separate record in the table, and each column will
contain the field values for one filed in the table.
Access assigns the default name “Table 1” to the first new table you create. By default, Access creates the ID
column as the primary key field for all new tables. The Microsoft Access Help button Opens the Help window, where you
can find information about Access commands and features as well as instruction for using them. The Ribbon provides the
main Access commands organized by task into tabs and groups.
Introduction to Database Concepts
Organizing, storing, maintaining, retrieving, and sorting this type of data are critical activities that enable a
business to find and use information effectively. Before storing data on a computer, however, you must organize the data.
You first step in organizing data is to identify the individual fields. A field is a single characteristics or attribute of a person,
place, object, event or idea. Next, you group related fields together into tables. A table is a collection of fields that
describes a person, place, objects, event or idea. The specific value, or content, of a filed is called the Field Value (record).
(Figure 1-1 Txt Pg 224)
Databases and Relationships
A collection of related table is called a database, or a relational database. You connect the records in the
separate table through a common field that appear in both tables. A Primary Key is a field, or a collection of fields, whose
values uniquely identify each record in a table. No two records can contain the same value for the primary key field. When
you include the primary key form one table as a field in a second table to form a relationship between the two tables, it is
called a Foreign Key( does not necessarily contain unique value).
Relational Database Management System (RDMBS)
A database management system (DBMS) is a software program that lets you create databases and then
manipulate data in them. Most of today’s database management systems, including Access, are called relational database management systems. In a relational database management system, data is organized as a collection of tables.
As stated earlier, a relationship between two tables in a relational DBMS is formed through a common field. A relational
DBMS controls the storage of database on disk and facilitates the creation, manipulation, and reporting of data.
Relational DBMS provides following functions( Figure 1-3 Txt pg 226)
1. Create database structures containing fields, tables, and table relationship
2. Easily add new records, change field values in existing records, and delete records
3. Contains a built-in query language, which let you obtain immediate answers to the question you ask about you
4. Contains a buit0in report generator, which let you produce professional-looking, formatted reports from you data,
5. Protects databases through security, control and recover facilities.
A company can benefit from a relational DBNS because it allows users working in different groups to share the same
date. More than one user can enter data into a database, more than one user can retrieve and analyse data that other
users entered. Finally, unlike other software programs, such as spreadsheet programs, a DBMS can handle massive
amounts of data and can be used to create relationships among multiple tables, and has up to 225 people using the table
at the same time.
Creating a Database
When you start Access, the first screen that appears is Backstage View(Figure 1-4 Txt pg 227), which is the
starting place for you work in Access. Backstage view provides options for you to get information about the current
database, create a new database, if open an existing database. To create a new database that does not contain any data
or objects, you use the Blank database option. If the database you need to create contains objects that match those found
in common database, you can use a template. A template is a predesigned database that includes professionally
designed tables, reports and other database objects that can make it quick and essay for you to create a database.
When you create a new database, make sure you have created your copy of the Access Starting Data Files. In
the File Name Box on the right side of the screen select the default name (database 1) provided by Access and then type
the name you wanted. If you don't type the filename extension, Access adds it automatically, and then save as Microsoft
Access 2007 Databases( b/c Access 2007 and Access 2010 have the same file extension)
Creating a Table in Datasheet View
Table contain all the data in a database and are fundamental objects for you work in Access. There are different
ways to create a table in ACCESS, including entering the files ad records for a table directly in Datasheet View.
Renaming the Default Primary Key Field
Access provides the ID field as the default primary key for a new table you create in Datasheet view. (Change it:
Rename Field, AutoNumber data type assigned to the default primary key field (ID)) A primary key is a field, or a
collection of fields, whose values uniquely identify each record in a table.
Changing the Data Type of the Default Key Field
Each field in an Access table must be assigned a data type. The data type determines what field values you can
enter for the field. The AutoNumber data type automatically inserts a unique number in this field for every record.
Therefore, it can serve as the primary key for any table you create. Text data type, which allows field values containing
letters, digits, and other characters, and which is appropriate for identifying numbers, such as contract numbers, that are
never used in calculation. Note the Unique check box in the Field Validation group is checked because the field assumed
the each value in the primary key must be unique. No two records in the primary key will be allowed to have the same
value. (Field-Column, Record-Row)
(Select a column: a column is selected when you click a field value, in which case the background color of the column
heading changes (the default color is orange) and the insertion point appears in the field value. You can also click the column heading to select a column, in which case the background color of both the column heading and the field value
changes (the default colors are gray and blue, respectively)
Datasheet view shows a table’s content in rows (record) and columns (field). Each column is headed by a field
name inside a field selector, and each row has a record selector to its left. Clicking a field sector or a record selector
(row/Column heading) selects that entire column or row (respectively), which you then can manipulate. A field selector is
also called a column selector, and a record selector is also called a row selector.
- The pencil symbol indicates that the record is being edited.
- The star symbol identifies the second row as the next row available for a new record.
- You can also press the Enter key instead of the Tab key to move from one field to another, and to the next
- Current Record box displays the number of the current record as well as the total number of records in the
Saving a Table
The records you enter are immediately stored in the database as soon as you enter them; however, the table’s
design-the field names and characteristics of the fields themselves, plus any layout change to the datasheet-are not
saved until you save the table. The new added record does not display in primary key order until you close and reopen the
table, or switch between values.
Access sorted and displayed the records in order by the value in the Primary key.
Opening a Table
The table in a database are listed in the Navigation Pane. You open a table, or any Access Object (query, form,
report), by double-clicking the object name in the Navigation pane.
Session 1.2 the Create Tab Options
Visual Overview summary: The forms group contains options for creating a form. Form is a database object you use to
enter, edit, and view research in a database. The Queries group contains options for creating a query, which is a question
you ask about the data stored in a database. In response to a query, Access displays the specific records and fields that
answer your question. The Query Wizard button opens a dialog box with different types of wizards that guide you through
the steps to create a query. One of these, the Simple Query Wizard, allows you to select records and fields quickly to
display in the query results. The Form tool quickly creates a form containing all the fields in the table (or query) on which
you’re basing the form. The Form Wizard guides you through the process of creating form. The Report tool places all the
fields from a selected table (or query) on a report, making it the quickest way to create a report. The Report wizard guides
you through the process of creating a report. The reports group contains options for creating a report, which is a formatted
printout (or screen display) of the contents of one or more tables (or queries) in a database. The Create tab provides
options for creating various database objects, including table, forms and reports. The options appear on the tab grouped
by object table.
Copying Records from another Access Database
There are many other ways to enter records in a table, including copying and pasting records from a table into the
same database or into a different database. To use this method, however, the table must have the same structure – that
is, the tables must contain the same fields, with the same design, and characteristics, in the same order. (Click the
datasheet selector to select all the records in the table, Detail Procedure on Txt pg 246)
Navigating a Datasheet
To view fields or records not currently visible on the screen, you can use the horizontal and vertical scroll bars
shown in pg 246 to navigate the data. The navigation buttons provide another way to move vertically through the records. The Current Record box appears between the two sets of navigation buttons and displays the number of the current
record as well as the total number of records in the table.(Navigation Buttons on Pg 247) You can make a field the current
field by clicking anywhere within the column for that field; you can make a record the current record by clicking anywhere
within the record.
Creating a Simple Query
A Query is question you ask about the data stored in a database. When you create a query, you tell Access which
fields you need and what criteria Access should use to select the records that will answer your question. Then Access
displays only the information you want, so you don’t have to navigate through the entire database for the information.
You need to select fields from the Available Field box to include them in the query (Selected Fields)- (You can
also double-click a field to move it from the Available Field box to the Selected Field box). More detail steps are on pg 248.
Each simple Query Wizard dialog box contains buttons on the bottom that allow you to move to the previous dialog box
(Back button), move to the next dialog box(Next button), or cancel the creation process(Cancel button) You can also finish
creating the object(Finish button) and accept the wizard’s defaults for the remaining options.
The query results are not stored in the database; however, the query design is stored as part of the database with
the name you specified. You can re-create the query results at any time by opening the query again.
Creating a Simple Form
You use a form to enter, edit, and view records in a database. Although you can perform this same function with
tables and queries, forms can present data in many customized and useful ways. (Form created by the Form tool-Figure
1-22 pg 251)
The form displays one record at a time in the contract table (usually shows the field value for the first record),
providing the layout view and allow you to focus on the values for one record. Access displays the field values for the first
record in the table and selects the first field value by placing a border around the value. As indicated in the status bar, the
form is displayed in layout view. In layout view, you can make design changes to the form while it is displaying data, so
that you can see the effects of the changes you make immediately. If you want to move the data, instead of using the
arrows buttons, you have to use the Next record, Last record, Previous record, First record to move it. (Detail pg 251).
Tips: Saving Database Objects
In general, it is best to save a database object-query, form, or report-only if you anticipate using the object
frequently or if it is time consuming to create because all objects use storage space on your disk.
Creating a Simple Report
A Report is formatted printout (or screen display) of the contents of one or more tables or queries. (Ex. The
Report tool creates a simple report showing every field in the Contract table and places it on a tab name “Contract”
(Format on Pg 253 Important Figure 1-23)
The report shows each field in a column, with the field value for each record in a row, similar to a datasheet.
However, the report offers a more visually appealing format for the data, with the column headings in a different color,
borders around each field value, a graphic of a report at the top left, and the current day, date, and time at the top right.
Also notice the dotted horizontal and vertical lines on the top and right, respectively, these lines mark the edge of the page
and show where text will print on the page.
Printing a Report
You can also use the Print dialog box to print over database objects, such as table, report and query datasheets.
Most often, table and query datasheet are used for viewing and entering data, reports are used for printing the data in a
Viewing Objects in the Navigation Pane Objects displayed in the Navigation Pane(figure- pg258)
All Access objects, which lists all the database objects in the pane. Each object type (Tables, Queries, Forms and
Reports) appears in its own group. Each database object has a unique icon to its left to indicate the type of object. This
makes it easy for you to identify the objects and choose which one you want to open work with. The arrow on the All
Access Objects bar displays a menu with options for various ways to group and display objects in the Navigation Pane.
The Search box enables you to enter text for Access to find. Note that Access searches for objects only in the categories
and group currently displayed in the Navigation pane.
Managing a Database
By managing your databases, you can ensure that they operate in the most efficient way, that the data they
contain is secure, and that you can work with the data effectively. Some of the activities involved in database
management include compacting and repairing a database and backing up and restoring a database.
Compacting and Repairing a Database
Whenever you open an Access database and work in it, the size of the database increases. Further, when you
delete records or when you delete or replace database objects-such as queries, forms, and reports-the space that had
been occupied on the disk by the deleted or replaced records or object does not automatically become available for other
records or objects. To make the space available, you must compact the database. Compacting a database rearranges the
data and objects in a database to decrease its file size, thereby making more space available on your disk and letting you
open and close the data base more quickly.
When you compact a database, Access repairs the database at the same time. In many cases, Access detects
that a database is damaged when you try to open it and gives you the options to compact and repair it at that time. (For
example, the data in your database might become damaged, or corrupted, if you exit the Access program suddenly by
turning off your computer. If you think your database might be damaged because it is behaving unpredictably, you can
use the Compact & Repair Database option to open it. Access also allows you to set an option to compact and repair a
database file automatically every time you choose it. By default, the compact on Close option is turned off. ( Although
nothing visible happens on the screen, Access compacts the database, making it smaller, and repairs it at the same time.
Backing Up and Restoring a Database
Backing a database is the process of making a copy of the database file to protect your database against loss or
damage. The Back Up Database command enables you to back up your database file from within the Access program,
while you are working on your database. Access provides a default filename for the backup copy that consists of the same
filename as the database you are backing up and an underscore character, plus the current date. This file naming system
makes it easy for you to keep track of your database backups and when they were created. To restore a backup database
file, you simply copy the backup form the drive on which it is stored to your hard drive…
Tutorial 2: Building a Database and Defining Table Relationships
Session 2.1 Table Window in Design View
Design View allows you to define or modify a table structure or the properties of the fields in a table. You can use
the Description property to enter an optional description for a field to explain its purpose or usage. A field’s Description
property can be up to 225 characters long, and its value appears on the status bar when you view the table datasheet.
The bottom portion of the Table window in Design view is called the Field Properties pane. Here, you select values for all
other field properties, most of which are optional. The purpose or characteristics of the current property (Field Name, in
this case) appear in this section of the field properties pane. You can display more complete Help information about the
current property by pressing the F1 key. The default name for a new table you create in Design view is Table1. This name
appears on the tab for the new table. The top portion of the Table window in Design view is called the Table Design grid.
Here, you enter values for the Field Name, Data Type, and Description field properties. In the Field Name column, you
enter the name for each new field in the table. When you first open a new Table window in Design view, Field Name is the
current property. In the Data Type column, you select the appropriate data type for each new field in the table. The data
type determines what field values you can enter for a field and what other properties the field will have. The default data
type for a new field is Text. After you assign a data type to a field, the general tab displays additional field properties for that data type. Initially, most field properties are assigned default values. When defining the fields in a table, you can
move from the Table Design grid to the Field Properties pane by pressing the F6 key.
Guidelines for Designing Database
When you design a database, you should follow these guidelines:
1. Identify all the fields needed to produce the require information
2. Organize each piece of data into its smallest useful part (For example, Oren could store each customer’s
complete name in one field called Customer Name instead of using two fields called FirstName and LastName)
3. Group related fields into tables.(For example, Oren grouped the fields related to contracts into the Contract table)
4. Determine each table’s primary key (the primary key with two or more fields is called composite key. For example,
a school grade table would use a combination of student number and course code to serve as the primary key)
5. Include a common field in related tables (You use the common filed to connect one table logically with another
table. For example, Oren’s Contract and Customer tables include the CustomerID as a common. Recall when you
include the primary key from one table as a field in a second table to form a relationship, the field is called a
6. Avoid data redundancy (when you store the same data in more than one place, data redundancy occurs. With the
exception of common fields to connect table, you should avoid data redundancy because it wastes storage space
and can cause inconsistencies)
7. Determine the properties of each field.
- You need to identify the properties or characteristics, of each field so that the DBMS knows how to store,
display, and process the field values. These properties include the field’s name, maximum number of
characters or digits, description, valid values, and other field characteristics.
Guidelines for Setting Filed Properties
When you select or enter a value for a property, you set the property. Access has rules for naming fields and
objects, assigning data types, and setting other field properties.
1. Naming Fields and Objects
You must name each field table, and other object in an Access database. Access then stores their items
in the database, using the names you supply. A field name must be unique within a table, but it can be
used again in another table-（foreign key）
2. Assigning Field Data Types
- Each field must have a data type, which is either assigned automatically, by Access or specifically by the
table designer. The data type determines what field values you can enter for the field and what other
properties the field will have(common data type: figure 2-4 pg 269)
Ex. AutoNumber, Yes/No, Hyperlink
3. Setting Field Size
The Field Size Property defines a field value’s maximum storage size for Text, Number, and AutoNumber
fields only. The other data types have no Field Size property because their storage size is either a fixed,
predetermined amount or is determined automatically by the field value itself.
4. Setting the Caption Property for Fields
The Caption property for a field specified how the field name is displayed in database objects, including
table and query datasheets, forms and reports. If you don’t set the Caption property, Access displays the field
name as the column heading or label for field.
Insight: Setting the Caption Property V.S Naming Fields
Although Access allows you to include spaces in field name, this practice is not recommended because
the spaces causes problems when you try to perform more complex tasks with the data in your database.
Setting the Caption property allows you to follow best field naming practices, such as not including spaces in
field names, while still providing users with more readable field names in datasheet, forms and reports.
Changing the Format of a Field in Datasheet view
The formatting group on the Fields tab in Datasheet view allows you to modify some formatting for
certain field types. When you format a field, you change the way data is display, but not the actual values
stored in the table.(field values appear unchanged, but the format is different)
In Access, values for Text fields are left-aligned, and values for Number, Date/Time, and currency fields
are right-alighted Creating a Table in Design View
Creating a table in Design view involves entering the field names and defining the properties for the
fields, specifying a primary key for the table, and then saving the table structure. (Different with creating a
Table in Datasheet view)-Figure 2-9 Txt Pg 273
Steps for creating a table in Design View (create tab – Table Design button)
1. Defining Fields:
When you first create a table in Design View, the insertion point is located in the first row’s Field Name
box, ready for you to begin defining the first field in the table. You e