Transcript PowerPoint Presentation - Austin Community College

Relational Databases
Database Driven Applications
•Retrieving Data
•Changing Data
•Analysing Data
•What is a DBMS
•An application that holds the data
•manages the data
•allows us to access the data
•Access, Oracle, Sybase, MySQL
•Connect from VB to DBMS (Access)
•Using ADO/ODBC/OLEDB
•Can use ADO to connect to any DBMS that as an OLEDB interface
ODBC
ODBC (Open Database Connectivity) is a standard Application
Programming Interface (API) for accessing information from
different database systems and different storage formats.
The purpose of ODBC is to enable you to access a diverse
selection of databases and data formats without having to learn
the features and peculiarities of each.
There is an ODBC driver for each popular database, which
creates a database-independent environment.
OLE DB
OLEDB is Microsoft’s technology that allows applications to
access data from multiple data providers. Any data source can be
an OLE DB provider as long as the proper library routines allow
low level access to the data.
OLE DB is a standardized interface that allows the developer to
use one set of programming tools to refer to data from any source.
ADO
ActiveX Data Objects (ADO) is Microsoft’s latest database object
model.
The goal of ADO is to allow VB developers to use a standard set
of objects to refer to any OLE DB source.
Database Definitions
Database -A container (usually a file or set of files) used to store
organized data.
Tables – A structured list of data of a specific type. Every table has a
unique name.
Fields –A field is a column in a table. A field contains a particular piece
of information.
All tables are made up of one or more fields.
Fields have an associated data type.
Records –Data in a table is stored in rows. The rows are called records.
Database Definitions
Primary Key – A column or set or columns whose values uniquely
identify every row in a table.
To be a primary key a set of column or column must meet the following
conditions:
•No two rows can have the same primary key value
•Every row must have a value in the Primary Key column (no Null
values).
Customer Table
cust_id
1000000001
1000000002
1000000003
1000000004
1000000005
cust_name
Village Toys
Kids Place
Fun4All
Fun4All
The Toy Store
cust_contact
John Smith
Michelle Green
Jim Jones
Denise L. Stephens
Kim Howard
cust_city
Detroit
Columbus
Muncie
Phoenix
Chicago
cust_state
MI
OH
IN
AZ
IL
cust_zip
44444
43333
42222
88888
54545
Customer Table
cust_id
1000000001
1000000002
1000000003
1000000004
1000000005
cust_name
Village Toys
Kids Place
Fun4All
Fun4All
The Toy Store
Record
Field
cust_contact
John Smith
Michelle Green
Jim Jones
Denise L. Stephens
Kim Howard
cust_city
Detroit
Columbus
Muncie
Phoenix
Chicago
cust_state
MI
OH
IN
AZ
IL
cust_zip
44444
43333
42222
88888
54545
Relationships
vend_id
DLL01
DLL01
DLL01
BRS01
BRS01
BRS01
DLL01
FNG01
FNG01
prod_name
Fish bean bag toy
Bird bean bag toy
Rabbit bean bag toy
8 inch teddy bear
12 inch teddy bear
18 inch teddy bear
Raggedy Ann
King doll
Queen doll
vend_id
BRE02
BRS01
DLL01
FNG01
FRB01
JTS01
vend_name
Bear Emporium
Bears R Us
Doll House Inc.
Fun and Games
Furball Inc.
Jouets et ours
prod_price
$3.49
$3.49
$3.49
$5.99
$8.99
$11.99
$4.99
$3.49
$9.49
vend_address
500 Park Street
123 Main Street
555 High Street
42 Galaxy Road
1000 5th Avenue
1 Rue Amusement
vend_city
Anytown
Bear Town
Dollsville
London
New York
Paris
Relationships
PK
FK
Primary Key – A column or set or columns whose values uniquely
identify every row in a table.
Foreign Key – A column in a table whose value must be listed in a
primary key in another table or be null.
Schema
Schema – Information about relationships and tables in a database.
What is SQL?
SQL is a language designed specifically for communicating with
databases.
Advantages of SQL
•SQL is not a proprietary language. So multiple databases support
SQL.
•Standard SQL is governed by the ANSI standards committee and I
s called ANSI SQL, all major DBMS support ANSI SQL even if
they have their own extensions.
•SQL is very powerful and also fairly simple.
•You will be able to perform very complex and sophisticated
database operations by only knowing a few commands.
Relational Algebra
Select – takes a horizontal subset of a table. Select retrieves certain
rows that meet our user defined criteria.
Project – takes a vertical subset of the table. Project retrieves only
certain columns from a table)
Join— Join allows us to pull together data from more than one table.
The join forms a new table that contains the columns of both
the tables.
Rows are the concatenation of row from first table and row
from second table.
Join
vend_id
DLL01
DLL01
DLL01
BRS01
BRS01
BRS01
DLL01
FNG01
FNG01
prod_name
Fish bean bag toy
Bird bean bag toy
Rabbit bean bag toy
8 inch teddy bear
12 inch teddy bear
18 inch teddy bear
Raggedy Ann
King doll
Queen doll
vend_id
BRE02
BRS01
DLL01
FNG01
FRB01
JTS01
vend_name
Bear Emporium
Bears R Us
Doll House Inc.
Fun and Games
Furball Inc.
Jouets et ours
prod_price
$3.49
$3.49
$3.49
$5.99
$8.99
$11.99
$4.99
$3.49
$9.49
vend_address
500 Park Street
123 Main Street
555 High Street
42 Galaxy Road
1000 5th Avenue
1 Rue Amusement
Join
vend_id
DLL01
DLL01
DLL01
BRS01
BRS01
BRS01
DLL01
FNG01
FNG01
prod_name
Fish bean bag toy
Bird bean bag toy
Rabbit bean bag toy
8 inch teddy bear
12 inch teddy bear
18 inch teddy bear
Raggedy Ann
King doll
Queen doll
prod_price
vend_name
$3.49 Doll House Inc.
$3.49 Doll House Inc.
$3.49 Doll House Inc.
$5.99 Bears R Us
$8.99 Bears R Us
$11.99 Bears R Us
$4.99 Doll House Inc.
$3.49 Fun and Games
$9.49 Fun and Games
vend_address
555 High Street
555 High Street
555 High Street
123 Main Street
123 Main Street
123 Main Street
555 High Street
42 Galaxy Road
42 Galaxy Road
Rows in the joined table are the concatenation of row from first table
and row from second table.
Inner Join – only rows that have matching values in both table are
included.
Outer Join
vend_id
DLL01
DLL01
DLL01
BRS01
BRS01
BRS01
DLL01
FNG01
FNG01
FRB01
JTS01
prod_name
Fish bean bag toy
Bird bean bag toy
Rabbit bean bag toy
8 inch teddy bear
12 inch teddy bear
18 inch teddy bear
Raggedy Ann
King doll
Queen doll
prod_price
vend_name
$3.49 Doll House Inc.
$3.49 Doll House Inc.
$3.49 Doll House Inc.
$5.99 Bears R Us
$8.99 Bears R Us
$11.99 Bears R Us
$4.99 Doll House Inc.
$3.49 Fun and Games
$9.49 Fun and Games
Furball Inc.
Jouets et ours
Outer Join – all values from both tables included.
vend_address
555 High Street
555 High Street
555 High Street
123 Main Street
123 Main Street
123 Main Street
555 High Street
42 Galaxy Road
42 Galaxy Road
1000 5th Avenue
1 Rue Amusement