Database Introduction

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 (新增資料表 – 設計檢視)

Ø Input the following data

STUDFILE ABS

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.

Class work (Continue)

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

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.