Transcript COMP08MAINe

8.1 Database Concept

Revolution of Information Management

In the past, we often used the following methods to manage
information storage and retrieval.
A librarian looking up the index card of
a book in a filing cabinet
An office clerk storing customer
information on index cards
Historical methods of storing and retrieving information
8.1 Database Concept

Revolution of Information Management



The searching or sorting process of information stored in a
manual filing system such as a filing cabinet could be very time
consuming.
Most companies realize that information is one of their most
valuable assets.
Instant access to required information can take much less time
to complete business transactions.

e.g. a staff member of a fashion company can check the system
and find out instantly whether the requested item is in stock.
8.1 Database Concept

Revolution of Information Management
Switching from a manual filing system to an electronic one can save time and space
on information management
8.1 Database Concept

Databases



Many activities in our daily lives involve the use of databases.
Database is a collection of data related to a particular topic or
purpose.
It is well organized so that information can be stored and
retrieved efficiently.
8.1 Database Concept

Databases

Typical applications of databases:








Personal phone book
Video or CD catalogue
Property list of an estate agent
Employee records of a company
Animal records of a vet clinic
Stock control of a shop
Club membership of a club house
Student records of a school
8.1 Database Concept

Databases

Typical applications of databases:








Personal phone book
Video or CD catalogue
Property list of an estate agent
Employee records of a company
Animal records of a vet clinic
Stock control of a shop
Club membership of a club house
Student records of a school
8.2 Using the Database Management
System (DBMS)

Database Management System (DBMS)

Data hierarchy can be divided into




Field
Record
Table
Database
8.2 Using the Database Management
System (DBMS)

Designing a Database

A good database design is the keystone to creating a database
that can perform effectively, accurately and efficiently.

Make a list of items you want to record.

Each item will become a field of a table in the database.


Each field should be given a unique name that is short but
descriptive.
Additional information is the size and data type of each field.
8.2 Using the Database Management
System (DBMS)

Designing a Database
Field
Identity card number of
an employee
Mobile phone number
Date of birth
Salary
Field name
id_no
Data type
Text
Field length
7
tel_m
dob
salary
Text
Date
Numeric
8
N/A
Integer
Some examples of fields in a typical employee database
8.2 Using the Database Management
System (DBMS)

Designing a Database

The data type of each field helps us enter the data.



e.g. designating the data type of the ‘date of birth’ field as date
prevents us from entering anything other than a valid date.
Rules for data entry can be set so that the DBMS will compare
data entered against a predefined value.
This process is called data validation which is an important
feature of DBMSs.
8.2 Using the Database Management
System (DBMS)

Key Field




The data in a key field is unique to a specific record.
The key field should not be anything with a chance of
duplication such as names and birthdays.
If no field in your database meets this requirement, you can ask
the DBMS to assign an identity number to each record.
Examples of key fields:





Driver’s licence number
Student identification number
Bank account number
Product code
Order number
8.2 Using the Database Management
System (DBMS)

Key Field

‘member_id’ field is assigned as the key field of a member table.
member_id
first_name
last_name
address
tel_no
e-mail
join_date
2195
Peter
Leung
5D, Long Building,
Delight Street,
Hong Kong
51151100
[email protected]
6/10/2001
2304
Albert
Po
12F, Short
Mansion, Happy
Street, Kowloon
52818688
[email protected]
12/5/2003
2416
Albert
Fung
38D, King
Building, Lung
Street, Kowloon
58580068
[email protected]
26/1/2004
A sample member table
8.2 Using the Database Management
System (DBMS)

Input Mask


Help prevent users from inputting invalid data.
Make data entry easier by controlling the values that users can
input.

e.g. an input mask can make sure that phone numbers always have
the correct number of digits.
8.2 Using the Database Management
System (DBMS)

Input Mask


Help prevent users from inputting invalid data.
Make data entry easier by controlling the values that users can
input.

e.g. an input mask can make sure that phone numbers always have
the correct number of digits.
8.3 Microsoft Access

Microsoft Access

A very robust and powerful DBMS

Easy to set up a database

An Access database contains various database objects and is
stored in a single file.
8.3 Microsoft Access

Microsoft Access
Template categories
Office button
Blank database
button
Getting Started Screen of Microsoft Access
Recently used databases
8.3 Microsoft Access

Database Objects in Microsoft Access

Six types of database objects in Microsoft Access
Database
object
Table
Forms
Reports
Queries
Description
Tables are used to store the data. You can create as many
tables as you need to store different types of data. Data in a
table is arranged in rows and columns.
Forms are attractive or user-friendly interfaces that can be
used to manipulate and display the data in a table.
Reports are used to present data in a professional and easy-toread layout and they often contain summarized information.
Queries are used to perform an action on a table. For example,
they can be used to return records that meet the specified
criteria from one or more tables.
Database objects in Microsoft Access
8.3 Microsoft Access

Database Objects in Microsoft Access
Database
object
Macros
Module
Description
Macros are simple programs used to automate certain tasks.
Modules are programs that contain data management logic
defined by users.
Database objects in Microsoft Access

Macros and modules are beyond the scope of this book and will
not be discussed.
8.3 Microsoft Access

Understanding Tables in Access


Every database needs at least one table to store data.
If you want to store several lists of related information, you may
need to create more than one table.

e.g. an event table and a student table are needed to keep track of
the students that take part in different events during Sports Day.
8.3 Microsoft Access

Understanding Tables in Access

Two ways to create or edit the structure of a table in Microsoft
Access:


Datasheet View
Design View
Datasheet View of a table
8.3 Microsoft Access

Understanding Tables in Access
Design View of a table
8.3 Microsoft Access

Understanding Tables in Access

The Datasheet View




Display the table organized in rows and columns.
Work like a spreadsheet and you can input data directly into the
table.
During the data input, it will assign a data type to each field.
It can only guess what you need and most often you need to
rename each field and re-assign its data type again.
8.3 Microsoft Access

Understanding Tables in Access

Design View



Display the underlying structure of the table.
You can inspect and modify the data type and description of each
field.
The preferred way of creating or modifying the structure of a table.
8.3 Microsoft Access

Understanding Tables in Access
Data type
Text
Memo
Number
Currency
AutoNumber
Yes/No
OLE object
Hyperlink
Description
Store text of length up to 255 characters.
Store long text of length up to 65,536 characters.
Store any numeric entry used for numerical calculations
Store currencies.
Store a unique number automatically assigned by Access
when a new record is created.
Store yes/no, true/false or on/off values.
Store object linking and embedding (OLE) objects such as
Excel spreadsheets, graphics, pictures, sounds, etc.
Store hyperlinks.
Data types supported by Microsoft Access
8.3 Microsoft Access

Data Sorting and Filtering in Access

Sorting


Rearrange all the records in ascending or descending order
according to a specified field.
Filtering

Extract a subset of the records in the table according to specified
criteria.

e.g. you can apply a filter on the ‘members’ table to obtain a list of
members who live in a certain district.
8.3 Microsoft Access

Data Sorting and Filtering in Access

Sorting


Rearrange all the records in ascending or descending order
according to a specified field.
Filtering

Extract a subset of the records in the table according to specified
criteria.

e.g. you can apply a filter on the ‘members’ table to obtain a list of
members who live in a certain district.
8.4 Forms

Forms



A graphical user interface used to manipulate and display the
data.
A well-designed form has the fields displayed in a clearer and
more attractive format than the Datasheet View.
Provide more efficient interface
8.4 Forms

Forms
The same table displayed in the Datasheet View (upper)
and by a form (lower)
8.4 Forms

Forms
The same table displayed in the Datasheet View (upper)
and by a form (lower)
8.5 Reports

Reports

For printing data in a professional and easy-to-read layout

Can display data from tables or queries

Often used to present summarized information

Report Wizard helps produce professional reports easily
8.5 Reports

Reports

For printing data in a professional and easy-to-read layout

Can display data from tables or queries

Often used to present summarized information

Report Wizard helps produce professional reports easily
8.6 Queries

Queries

Used to retrieve data from one or more tables that meets
specified criteria.

Perform calculations and reorder the data on request.

Results are presented in a datasheet.
8.6 Queries

Queries


Must be written in a format that Access can interpret, i.e.
Structured Query Language (SQL).
The syntax of SQL statements are shown below.
SELECT field name(s) FROM table name(s) WHERE criteria;
SELECT field name(s) FROM table name(s) WHERE criteria ORDER BY
field name(s);
8.6 Queries

Queries
Operator
=
<
<=
>
>=
<>
Between X and Y
Is Null
And
Or
Not
Description
Equal to
Less than
Less than or equal to
Greater than
Greater than or equal to
Not equal to
Within a range (replace X & Y with values)
Null values
True only if both conditions exist
True if either condition exists
True if the single instance is not true
Operators used to specify criteria in SQL statements
8.6 Queries

Queries

Three ways to create a query:



Query Wizard – a quick way to build a simple query
Design View – a graphical tool that can be used to create query
SQL View – a text-input interface for writing SQL statement
8.6 Queries

Queries

Three ways to create a query:



Query Wizard – a quick way to build a simple query
Design View – a graphical tool that can be used to create query
SQL View – a text-input interface for writing SQL statement