Transcript Introduction to Database

Introduction to Database
ISYS 263
File Concepts
• File consists of a group of records. Each
record contains a group of fields.
– Key field, grouping field, calculated field
• Example: Student file
–
–
–
–
SID
S1
S3
S5
Sname
Peter
Paul
Mary
Major
CIS
ACCT
CIS
Sex
M
M
F
GPA
3.0
2.7
3.2
Various Types of File
• Database file
– Created and managed by a DBMS
• Spreadsheet file
– Created and managed by a spreadsheet software
• XML file
– Extensible Markup Language
• Comma-Delimited file – A text file that can be processed by
any programming languages, DBMS and spreadsheet.
– "s5","peter",3.5
– "s1","paul",3
– "s7","mary",2
• Comma-Delimited file demo: stdata.txt
– Access –External Data/Text File
– Excel – Data/Get External Data/From Text
XML: Data with the Meaning of Data (UserDefined Tags)
<?xml version="1.0" ?>
<Books>
<Book>
<ISBN>1-34567-04-01</ISBN>
<Authors>
<AuthorName>John Smith</AuthorName>
<AuthorName>Peter Chen</AuthorName>
<AuthorName>David Chao</AuthorName>
</Authors>
<Price> $45.00</Price>
<Description>This is a grerat book</Description>
</Book>
<Book>
<ISBN>1-34567-04-02</ISBN>
<Authors>
<AuthorName>Adam Smith</AuthorName>
</Authors>
<Price> $25.00</Price>
<Description>This is a second great book</Description>
</Book>
</Books>
Database Concepts
• A database is a collection of related files.
– Support business operations
– Provide information
• A database management system is a
software that enables users to create and
maintain a database.
Relational Database
• Data is logically structured within relations.
• Each relation is a table (file) with named
columns (attributes, fields) and rows
(records).
• Example of relational database systems:
–
–
–
–
MS Access, MS SQL Server
Oracle, MySQL
IBM DB2
Sysbase, Informix, etc.
Major Functions of Database
Management
• Creating a database
– Analysis: Entity-Relationship Diagram
– Design: Design file structure
– Implementation
• Accessing a database
• Updating a database
Introduction to MS Access
Creating a New Database
• MS Office button/New
– Blank database
– New database name and location
Access Objects
• Tables
– Create a new table: Create/Table Design
– Open a table:
• Double click the table name
– Home/View:
• Datasheet view
• Design view
• Queries
– Create a new query: Create/Query Design
• Others: Forms, Reports, Pages
Creating a Table
• Create/Table Design:
– Field name
– Field data type
• Create table by using table templates
– Create/Table Templates/select template
• Create a primary key
– Never let Access to create a primary key for you.
• Enter data
Example: Create an Employee
Table
• Database name: HRdatabase
• Table name: Employee
• Table fields:
–
–
–
–
–
EmpID: Text, 10 characters
EmpName: Text, 30 characters
Sex: Text, 1 character
HireDate: DateTime field
Salary: Currency field
Create a new Query
• Create/Query design/Show table
– Select fields
– Add criteria
• Sorting
• Total and subtotal
– Query design tools/Total
– A Total row is added to the design view
– Select function from the total row’s dropdown list
Creating a New Form
• Using Form Wizard
– Create/Form
– Create/More Forms/Form Wizard
Database design objectives
• Eliminate data duplication.
– Problems with duplication:
• Inconsistent data
• double entry
• waste space
• Link related records in related tables.
• Example:
– StudentTable: SID, Sname, Address, Major,
– StudentAccountTable: SID, Sname, Address, Balance
Database Design Example
• Example:
– Employee Table: EmpID, EmpName, Sex,
HireDate,Salary
– Department Table: DeptID, DepName, DeptPhone
• Assumption an employee works for one department
and a department has many employees,
– 1. how to link an employee record to the department
record the employee works for?
– 2. how to link a department record to all its employees?
• Note: A table’s key field plays an important role in
linking related records.
MS Word’s Mail Merge with Data from a
Query
• Mailings/Start Mail Merge/Step by Step Mail Merge
Wizard
• Step 1: Select document type – letter
• Step 2: Select starting document – Use current
document
• Step 3: Select recipients – From database/browse
• Step 4: Write your letter –
• Address book, greeting line, More item
• Step 5: Preview your letters
• Step 6: Complete the merge