INTRODUCTION TO
DATABASE
With Microsoft Office Access 2007
Agenda
Getting to Know Database
Basic of Microsoft Office Access 2007
Basic Operations of Database
Structured
Query Language (SQL)
CRUD Operations
Getting to Know Database
Definition of Database
Organized mechanism to store, manage and
retrieve information
Efficient
Robust
Stable
Arranged in tabular fashion
Apparent relationship between information
Themost important aspect
Won’t be taught in this class :-(
A Really Simple Database
Consists of a table, multiple fields and many columns
Organization of Database
Tables
Fields (Columns)
Has many types
Primary Key (Optional)
Records (Rows, Entries)
When to Use Database? (1/3)
Tasks Appropriateness
Transaction records Appropriate
Logging Appropriate
Shopping List Inappropriate
Blog/Forum Appropriate
Music Playlist Inappropriate
Media Library Appropriate
When to Use Database? (2/3)
Appropriate Inappropriate
Transaction records Shopping list
Logging Music playlist
Blog/Forum
Media Library
When to Use Database? (3/3)
Managing mass amount of information
Sharing Information between many users
Manipulating complicatedly related information
Need security
Desire organization
Under the Hood
Implementations in which we
Infrastructure of Database are going to learn
Structured Query
Interface Language
Database Management Microsoft Office
System (DBMS) Access 2007
Computer Resources
Access Database
File (*.accdb)
Basic of Access 2007
Microsoft Office Access 2007
Don’t panic! You’ll soon be familiar with it.
Field Types (1/2)
Type Contains
Text String of maximum length
at 255 characters
Memo String
Number Number in multiple format
(Integer, Double, etc.)
Date/Time Date and time
Currency Currency
Field Types (2/2)
Type Contains
AutoNumber Auto generated number
(Increment, Randomized)
Yes/No Boolean (True/False)
OLE Object A certain type of file (not
mentioned)
Hyperlink URL
Attachment File
Basic Operations of Database
Structured Query Language (SQL)
Defines methods to manipulate database
Attempt to request something from Database is
called Query
Each formed SQL statement refer as SQL Query
Resembles natural language
Has many standards
However, the basic part is still the same
CRUD
Create new tables and records
Retrieve records from tables
Update tables’ definition and record’s data
Delete existing tables and records
CRUD : Create
INSERT INTO <table_name> (<field_list>)
VALUES (<value_list>);
AutoNumber field must not be included
Examples
INSERT
INTO students (nisit_id, name, surname)
VALUES (51052744, “Pongsakorn”, “U-chupala”);
CRUD : Create - Example
INSERT INTO students(nisit_id, name, surname)
VALUES (51052744, “Pongsakorn”, “U-chupala”);
nisit_id name surname
51051234 Steve Jobs
51052345 John Warnock
CRUD : Create - Example
INSERT INTO students(nisit_id, name, surname)
VALUES (51052744, “Pongsakorn”, “U-chupala”);
nisit_id name surname
51051234 Steve Jobs
51052345 John Warnock
51052744 Pongsakorn U-chupala
CRUD : Create - Practice
Insert a record with every field specified
CRUD : Retrieve
SELECT <select_list> FROM <table_name>
[ WHERE <search_condition> ]
[ ORDER BY <order_expression> [ ASC | DESC ] ];
Select which fields to retrieve
Examples
SELECT field_1, field_2 FROM table_name …
SELECT * FROM table_name …
CRUD : Retrieve
SELECT <select_list> FROM <table_name>
[ WHERE <search_condition> ]
[ ORDER BY <order_expression> [ ASC | DESC ] ];
Available operators: =, <, >, <=, >=, <>
Modifiers: AND, OR, NOT, ()
Examples
… WHERE student_id=1 …
… WHEHE (<cond1>) AND (<cond2>) …
CRUD : Retrieve
SELECT <select_list> FROM <table_name>
[ WHERE <search_condition> ]
[ ORDER BY <order_expression> [ ASC | DESC ] ];
Sort results by order expression ascending
(default) or descending
Expression can be chained together
Examples
… ORDER BY date DESC …
… ORDER BY name ASC, surname ASC …
CRUD : Retrieve - Example
SELECT name, height FROM students
WHERE height>160
ORDER BY height DESC;
nisit_id name surname height
51051234 Steve Jobs 160
51052345 John Warnock 165
51052744 Pongsakorn U-chupala 170
CRUD : Retrieve - Example
SELECT name, height FROM students
WHERE height>160
ORDER BY height DESC;
nisit_id name surname height
51051234 Steve Jobs 160
51052345 John Warnock 165
51052744 Pongsakorn U-chupala 170
name height
Pongsakorn 170
John 165
CRUD : Retrieve - Practice
Select every record, sort by
STU_ID, ascending
Select name, surname and height of everyone
shorter than 170
Select everyone heavier than 70, sort by
height, descending
CRUD : Update
UPDATE <table_name> SET <field_value_list>
[ WHERE <search_condition> ];
Update every record that match the search
condition
We usually use primary key for this
Examples
UPDATE students SET
name=“Knight”, surname=“Baron” WHERE
nisit_id=1;
CRUD : Update - Example
UPDATE students SET name=“Knight”, surname=“Baron”
WHERE nisit_id=51052744;
nisit_id name surname
51051234 Steve Jobs
51052345 John Warnock
51052744 Pongsakorn U-chupala
CRUD : Update - Example
UPDATE students SET name=“Knight”, surname=“Baron”
WHERE nisit_id=51052744;
nisit_id name surname
51051234 Steve Jobs
51052345 John Warnock
51052744 Knight Baron
CRUD : Update - Practice
Update the record that you’ve added earlier
with different data
CRUD : Delete
DELETE FROM <table_name>
WHERE <search_condition> ;
Delete every record that match the search
condition
Examples
DELETE FROM students WHERE id=1
DELETE FROM students WHERE
(name=“Knight”) AND (surname=“Baron”);
CRUD : Delete - Example
DELETE FROM students
WHERE (nisit_id=51052345) OR (nisit_id=51052744);
nisit_id name surname
51051234 Steve Jobs
51052345 John Warnock
51052744 Pongsakorn U-chupala
CRUD : Delete - Example
DELETE FROM students
WHERE (nisit_id=51052345) OR (nisit_id=51052744);
nisit_id name surname
51051234 Steve Jobs
51052345 John Warnock
51052744 Pongsakorn U-chupala
CRUD : Delete - Example
DELETE FROM students
WHERE (nisit_id=51052345) OR (nisit_id=51052744);
nisit_id name surname
51051234 Steve Jobs
CRUD : Delete - Practice
Delete the record you’ve modified earlier
Conclusion
Review
Getting to know Database
Definition
Organization
Practicing with Access 2007
Database operations
SQLSyntax
CRUD Operations
Any Questions?
Please do not hesitate to ask
Thank You!
Author: @KnightBaron
Blog: http://aosekai.net/
Email:
[email protected]
