2.4 Using a Database Management System
Introduction
A database
is a set of logically related files (Table). A database management system is application software that allows
users to create, retrieve and modify databases. Example: Visual Foxpro, Access,
(SQL server, Oracle, MySQL etc)
Basic features of Database management software package
Database – Table (File) – Record – Field
A database
refers to a set of logically related (相關) files (Tables)
organized in such a way data access is improved and data redundancy (重複、多餘) is minimized.
Each column of the table is called a field while each row of the table is
called a record. All the tables of a
database are linked by a common field called key field. (Unique and
simple)
Using Microsoft Microsoft Access (or Visual FoxPro)
Create a Table and set the relation between
two tables
Define Table Structure – Field name, field
type and field width
Class work: handling absent of students using Access
(database.mdb)
Step 1: Create two tables with the
following data:
Ø
Load Microsoft Access
Ø
Open a new database開空白資料庫 and save it as “database.mdb”
Ø
Create two tables with the
following structure (新增資料表 – 設計檢視)
|
STUDFILE
|
ABS
|
|
Field Name
|
Type
|
Width
|
Field Name
|
Text
|
Width
|
|
StudID
|
Char (text – 文字)
|
6
|
StudID
|
Char
|
6
|
|
Name
|
Char
|
30
|
DateOfAbs
|
Date
|
|
|
Sex
|
Char
|
1
|
|
|
|
Ø
Input the following data
STUDFILE ABS
|
Studid
|
Name
|
Sex
|
|
Studid
|
DateOfAbs
|
|
103456
|
BEN
|
M
|
|
103252
|
2013/9/9
|
|
103041
|
WINNIE
|
F
|
|
103195
|
2013/9/10
|
|
103044
|
CHRISTY
|
F
|
|
103044
|
2013/9/6
|
|
103221
|
DENNIS
|
M
|
|
103456
|
2013/9/16
|
|
103463
|
THOMAS
|
M
|
|
103456
|
2013/9/4
|
|
103252
|
RONDIE
|
M
|
|
103252
|
2013/9/15
|
|
103257
|
ALEX
|
M
|
|
103044
|
2013/9/15
|
|
103425
|
ANDRIAN
|
M
|
|
103252
|
2013/9/4
|
|
103195
|
KITTY
|
F
|
|
|
|
|
103357
|
JENNY
|
F
|
|
|
|
|
103434
|
BARRY
|
M
|
|
|
|
|
963461
|
EDDIE
|
M
|
|
|
|
Step 2: Related the two files (工具 Tools – 資料庫關聯圖 Relationships) with the student ID. The two tables are related by the STUDID
Searching A records
You can search a particular record in a
table. You can also sort or filter the table
Query (查詢)
Based on the contents of a table, a query
enables users to view a table in different ways. You can Filter and Sort records
during query. It can extract data from different tables.
|
Classwork
(Continue) – Query Wizard
Step 3: Create a query to display the name and sex of the students
Ø
Create a query (新增查詢)
Ø
Select query Wizard (簡單查詢精靈)
Ø
Select table (studfile) and
fields (name and sex)
Ø
Apply sort and filter
In SQL: Select Name, Sex from STUDFILE
Step 4: Create a query to display the
ABS.studid, ABS.DateOfAbs, STUDFILE.name
(It use the contents of both tables)
Ø
Create a query (新增查詢)
Ø
Select query Wizard (簡單查詢精靈)
Ø
Select table and fields
(ABS.studid, ABS.DateOfAbs, STUDFILE.name)
Ø
Apply sort and filter
In SQL: Select STUDFILE.studid, DateOfAbs, Name from STUDFILE, ABS where STUDFILE.studid = ABS.studid
|
Output Records – Report (報表) and Label (表單)
DBMS can print out a hard copy of the table
in different formats. The two most common formats are report and label.
Step 5: Modify
the structure of ABS, add a field “ApplyLeave”
“Y”
stand for leave application is submitted
Field
Name: ApplyLeave
Field
Type: Char (text)
Field
Width: 1
Modify
the data as follows
|
studid
|
DateOfAbs
|
ApplyLeave
|
|
103252
|
2013/9/9
|
Y
|
|
103195
|
2013/9/10
|
Y
|
|
103044
|
2013/9/6
|
N
|
|
103456
|
2013/9/16
|
Y
|
|
103456
|
2013/9/4
|
N
|
|
103252
|
2013/9/15
|
Y
|
|
103044
|
2013/9/15
|
N
|
|
103252
|
2013/9/4
|
Y
|
Step 6: Create
Form (表單) to show
those absentee’s name and date of absent
Ø
Create Form (表單 – 新增)
Ø
Select Form Wizard (表單精靈)
Ø
Select suitable fields from
different table
Step 7: Create Report Form to show students
absentee list
Ø
Create Reports by using Wizard
Ø Select suitable fields from
tables, it must include ApplyLeave for Grouping, DateOfAbs for Sorting
Ø
Grouping by ApplyLeave
Ø
Sorting by DateOfabs
Other Features
You can also using Macros (巨集) and Modules (模組) in DBMS. It involves programs
that can handle the database more effectively.