Transcript Database

Overview of
Databases
1
Content



Databases
Example: Access
Structure Query language (SQL)
2
What is a Database?



Database is an organized collection of
related data, typically stored on disk, and
accessible by possibly many concurrent
users.
Have programming capability to access
and manipulate data
Three traditional types of database:




hierarchical
relational (most prevalent type)
network
New: object-oriented
3
Database

Example databases:





Oracle
Sybase/Microsoft SQL
IBM’s DB2, IMS and SQL/DS
dBase
Access
4
Database

Relational database:


It is a tabular database in which the data is
defined so that it can reorganized and accessed
in a number of different ways.
Tables are used to make it easy to maintain
and data
5
Database

Advantage of relational databases:




easy to create
easy to access
easy to extend
Possible disadvantage

Limited set of content (compared to object
oriented databases).
6
Database

Relational database structure:



set of tables containing data fitted into predefined
categories;
each table (relation) contains one or more data
categories in columns.
each row contains a unique instance of data for
the categories defined by column.
7
Programming



Database systems also have some
programming capability
Often using SQL (see later)
Allows for accessing and manipulating data
to form a report or to answer a query.
8
Access





Relational database
Integrates data from spreadsheet and other
database
User friendly
Easy to use (help wizard)
Limited in scale of application (small
applications best)
9
Features of Access





Database Wizard
Simple Query Wizard
Filter by selection
Integration with Form, filter by form
Report output
10
Relationships in Access


Multiple tables in one database
Matching key fields between table


A key is usually a field with the same name in
both tables.
Such key is primary key for one table, foreign key
in the other table.
11
Access : Database
Note: tables, queries, forms and reports.
12
Access: Table
Table is where the data is kept
13
Access: key
Need to have a key to link data across tables
14
Access: Macros and Modules


A macro is a set of one or more actions that each
perform a particular operation, such as opening a
form or printing a report.
Macros can help to automate common tasks. For
example, a macro can run to prints a report when
a user clicks a command button.
Module

A module is a collection of Visual Basic for
Applications declarations and procedures that
are stored together as a unit.
15
Access:
Report
Predefined report can be
generated on demand
from data.
16
Database Programming



Access can use VBA or SQL for
programming.
VBA not a standard.
SQL (see later) is used by most database
systems.
17
SQL



SQL: Structured Query Language
It is used to query from and update database.
Systems using SQL:




Oracle
Sybase
Microsoft SQL server
Access
18
SQL

Standard SQL commands:





Select
Insert
Update
Delete
Create
19
SQL Example
20
SQL Example

To find out the ‘Category name’ when
‘Category ID’ equals to 1
SELECT Category name FROM Categories WHERE Category ID=1
Note: ‘Category name’ and ‘Category ID’ are column/field,
‘Categories’ is the table
21