Transcript Database

Data-mining & Data
• As we used Excel that has capability to analyze data to find
important information, the data-mining helps us to extract
information from data. However, not all data can be used
for that data-mining.
• We need to understand data can be classified into three
parts including database, data-warehouse, and data-mart.
• For example, typical retail shop has database that keep
track of sales, inventories, and customers. Sales data is
used to keep records of sales that customers made such as
product, quantity, price, date and so on. Inventory data
contains the records of products that this retail shop is
currently keeps. It may be name of product, price, maker,
and units.
Data-mining and Data
• Database such as sales data can be used for data-mining to
find the information such as seasonal sales trends.
However, by combining multiple data such as customer
data, sales data, and inventory data, more useful
information can be obtained with data-mining. To
combining data, it necessary to determine the relationship
between each data. For instance, particular sales in the
sales data is made by a customer in the customer data. This
relationship must be clearly defined. The combining
multiple data by this kind of relationship is called datawarehouse.
Data-mining and Data
• Not all data in the data-warehouse is necessary for datamining to analyze. In many case, we need to extract
necessary data from data-warehouse for analysis. This
extracted data is called data-mart. For instance, only
particular customer group such as young adult is needed to
analyze their sales pattern.
• Data, data-warehouse, and data-mart is used by many
organizations to find important information to make better
decisions.
• To construct data, data-warehouse, and data-mart, these
organizations use database management system such as
Oracle, SQL server, and Access.
Database Management System
•
•
Database is a collection of integrated data items that includes a descriptions of
the data therein.
Database structure means a relational view of database that is a collection of
tables in which rows of the table represent records and the columns of the table
represent fields or attributes of a record.
Schema
• Schema is a description of complete structure of the
database, including names and descriptions of all data
items, sets of data, and collections of the data item sets.
• Names assigned in the schema are used to reference
individual data items and collections of data items within
the database.
Database Structure
•
Relational view or structure is a collection of tables in which the rows of each
table represent database records, and the columns of each table refer to the
fields or attributes of a record.
Record
Order Table
OrderItem Table
Field
Basic Structure of Database
Field
Order Table
Record
OrderItem Table
Primary Key
Foreign Key
Primary Key
Important Terms
•
•
•
Table
– A logical structure used to group sets of related information
Record
– A set of attributes describing each person, place, or item in the database
Field
–
•
Primary Key
–
•
Fields in common between tables. It makes possible to link two tables.
Index
–
•
Each record in a relational database must be unique. This is accomplished by assigning a
unique ID or number to each record, which is called primary key.
Foreign key
–
•
An attribute of record
Access to the database record can be faster through the use of index assigned to fields
Queries
–
Language used to communicate with database. In VB, English-like language names Structured
Query Language (SQL) is used to retrieve data from a database.
Two Types of Database
•
•
Local database
– It generally used for single-user application.
– Some of commercially available database includes, Microsoft Access, Microsoft FoxPro, dBase, Paradox, and
ASCII.
– Visual Basic can access it through Visual Basic’s database Jet engine using Data Access Object.
Remote Database
– By definition, remote databases don’t reside on the user’s PC. It requires an ODBC (Open Database
Connectivity) driver for an application to access it. It can be accessed by multiple applications spontaneously.
–
Some of commercially available database include Oracle, Sybase, and Microsoft SQL Server.
– Visual Basic can access it through Remote Data Object and ODBC.
Building MS Access Database
• By using Microsoft Access
• By using Visual Data Manager
Building MS Access Database by using Microsoft Access
•
•
•
•
Step 1:
Step 2:
Step 3:
Step 4:
Make Database File
Create Tables
Define Relationships
Enter data
Building MS Access Database by using Microsoft Access
Step 1: Make Database File
Type a name for new Access Database
Building MS Access Database by using Microsoft Access
•Step 2: Create Tables
Select Tables in the Objects,
and double click “Create table
in Design View” menu
Design View
Building MS Access Database by using Microsoft Access
Design View
Add Columns
for the table
Set properties
for the column
Building MS Access Database by using Microsoft Access
•
This is sample of designing publisher table
Clicking this button in the tool bar
makes the current column as
primary key of the table
Auto Number data type makes the
column having automatic numeric value
without user entry. The first record in the
table will get value of 1, and subsequent
records get incremented value. Hence,
every record will have unique value.
Building MS Access Database by using Microsoft Access
You can change the properties of
the column. Here, the field size for
the Name column is changed to
100
Building MS Access Database by using Microsoft Access
Click Save
button to
save table
You need to save table after designing
Building MS Access Database by using Microsoft Access
After designing the table, you can start enter records in
the table in Datasheet View. To open Datasheet View
click Datasheet button on the Toolbar
Datasheet View
Notice Auto Number
column where you do not
need to type value, Access
automatically enter value
for new record.
Start enter records
Building MS Access Database by using Microsoft Access
•
Create the second table for Book Title and create relationship with the Publisher table
Each book title is published by specific publisher, so the
Book Title table has Publ ID column that holds the key
value of Publisher table. To make this relationship,
choose Lookup Wizard in the data type.
Lookup Wizard
Click Next
Building MS Access Database by using Microsoft Access
Select Lookup Table
Select Lookup Column
Choose sort order for Look up column
Lookup Wizard create relationships for two tables
Building MS Access Database by using Microsoft Access
When entering records, you do not need
to enter value. Instead, you can choose
one from dropdown combo box.
Accessing Database Tables with Relationship from VB
•
Using Connection, Data Adapter, and Dataset objects
– To access data from tables with relationship is the same as accessing data from a single table except you must
include tables in the Query Builder when creating Data Adopter.