Transcript Title Goes Here - Binus Repository

Matakuliah
Tahun
: M0564 /Pengantar Sistem Basis Data
: 2008
Commercial RDBMSs:
Office Access and Oracle
Pertemuan 13
Learning Outcomes
Pada akhir pertemuan ini, diharapkan mahasiswa
akan mampu :
• Mahasiswa dapat menerapkan perintah-perintah sql
pada commercial RDBMS. (C3)
3
Bina Nusantara
Acknowledgments
These slides have been adapted
from Thomas Connolly and
Carolyn Begg
Bina Nusantara
Outline Materi
• Microsoft Office Access
• Oracle
5
Bina Nusantara
Microsoft Office Access 2003
•
•
•
•
•
•
•
•
•
Objects
Microsoft Access Architecture
Table Definition
Relationships and Referential Integrity Definition
General Constraint Definition
Form
Reports
Macros
Object Dependencies
Bina Nusantara
Objects
User interacts and develops a database using:
• Table
– The base tables that make up the database.
– Organized into columns (called fields) and rows (called
records)
• Queries
– Allow the user to view, change and analyze data.
– Can stored and used as the source of records for forms,
reports and data access pages.
• Forms
– Can be used for a variety of purposes such as to create a
data entry form to enter data into a table.
Bina Nusantara
Objects (Con’t…)
• Reports
– Allow data in the database to be presented in an effective
way in a customized printed format.
• Pages
– A (data access) page is a special type of web page designed
for viewing and working with data from internet or an
intranet.
• Macros
– A Set of one or more actions each of which performs a
particular operation.
• Modules
– A collection of VBA declarations and procedures that are
stored together as a unit.
Bina Nusantara
Multi-User Support
•
•
•
•
File-Server Solutions
Client-Server Solutions
Database replication solutions
Web-based database solutions.
Bina Nusantara
Table Definiton
Five ways to create a blank (empty) table:
• Use the database wizard to create in one operation
all the tables, form and reports that are required for
the entire database.
• Use the table wizard to choose the fields for the
table from a variety of predefined tables.
• Enter data directly into a blank table (called a
datasheet).
• Use design view to specify all table details from
scratch
• Use the CREATE TABLE statement in SQL View.
Bina Nusantara
Relationships and Referential
Integrity Definition
• Relationship can be created in Microsoft
Access using
– SQL CREATE TABLE
– Relationship Window
• Two things to note about setting referential
integrity constraints
1. One-to-Many (1 : *) and One-to-One (1 : 1)
2. There are only two referential integrity action for
update and delete
Bina Nusantara
– NO ACTION
– CASCADE
General Constraint Definition
• To Create general constraints in
Microsoft Access using
– Validation rules for fields
– Validation rules for records
– Validation for forms using Visual Basic for
Applications (VBA)
Bina Nusantara
Microsoft Access - Form
• Allow a user to view and edit the data stored
in the underlying base tables, presenting the
data in an organized and customized manner.
• Contructed as a collection of individual
design elements called controls or control
objects.
• Divided into
– Form Header
– Detail
– Form Footer
Bina Nusantara
Microsoft Access - Report
• Special type of continuous form designed
specifically for printing, rather than for displaying in
a window.
• Allows the user to
–
–
–
–
Sort Records
Group Records
Calculate summary information
Control the overall layout and appearance of the report
• Divided into:
–
–
–
–
–
Bina Nusantara
Report Header
Page Header
Detail
Page Footer
Report Footer
Oracle9i
•
•
•
•
•
•
Objects
Oracle Architecture
Table Definition
General Constraint Definition
PL/SQL
Subprograms, Stored Procedures, Function and
Packages
• Triggers
• Oracle Internet Developer Suite
• Other Oracle Functionality
Bina Nusantara
Object
• Tables
– Table is organized into columns and rows.
– One or more tables are stored within a tablespace
– Oracle supports temporary tables that exist only for the
duration of a transaction or session
• Objects
– Object types provide a way to extend Oracle’s relational
data type system.
• Clusters
– Cluster is a set of tables physically stored together as one
table that shares common columns.
• Indexes
– Index is a structure that provides accelerated access to the
rows of a table based on the values in one or more columns.
Bina Nusantara
Object (Con’t…)
• Views
– View is a virtual table that does not necessarily
exist in the database but can be produced upon
request by a particular user at the time of request.
• Synonyms
– Alternative names for objects in the database
• Sequences
– Generate a unique sequence of number in cache.
• Stored Functions
– Set of SQL or PL/SQL statements used together to
execute a particular function and stored in the
database
Bina Nusantara
Object (Con’t…)
• Stored Procedures
– Procedure and functions are identical except that
functions always return a value (procedure do
not).
• Packages
– Collection of procedure, functions, variables and
SQL statements that are grouped together and
stored as a single program unit in the database.
• Triggers
– Trigger are code stored in the database and
invoked by events that occur in the database.
Bina Nusantara
Oracle Architecture
Bina Nusantara
General Constraint Definition
There are several ways to create general
constraints:
• SQL and the CHECK and CONSTRAINT
clauses of the CREATE and ALTER TABLE
statements
• Stored procedures and functions
• Triggers
• Methods
Bina Nusantara
PL/SQL
• PL/SQL is
– Oracle’s procedural extension to SQL.
– Similar to modern programming languages.
– Block-structured language.
• PL/SQL Block has up to three parts:
– Optional declaration part
– Mandatory executable part
– Optional exception part
Bina Nusantara
Subprograms and Packages
• Subprograms are named PL/SQL blocks that can
take parameters and be invoked
– (Stored) Procedure
• Will always return a single value to the caller
– Functions
• Only one return value is needed
•
Packages is a collection of procedures, function,
variables and SQL statements that are grouped
together and stored as a single program unit.
– Have two parts
• Specification
• Body
Bina Nusantara
Terima kasih
Bina Nusantara