Commercial RDBMSs:Office Access and Oracle

Pertemuan 13

Matakuliah : M0564 /Pengantar Sistem Basis Data Tahun : 2008

Bina Nusantara

Pada akhir pertemuan ini, diharapkan mahasiswa

akan mampu :• Mahasiswa dapat menerapkan perintah-perintah

sql pada commercial RDBMS. (C3)

Learning Outcomes

3

Bina Nusantara

AcknowledgmentsThese slides have been adapted from Thomas Connolly and Carolyn Begg

Bina Nusantara

• Microsoft Office Access• Oracle

Outline Materi

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

ObjectsUser 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 DefinitonFive 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 ReferentialIntegrity Definition

• Relationship can be created in Microsoft Access using – SQL CREATE TABLE– Relationship Window

• Two things to note about setting referential integrity constraints1. One-to-Many (1 : *) and One-to-One (1 : 1)2. There are only two referential integrity

action for update and delete– NO ACTION– CASCADE

Bina Nusantara

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:

– Report Header– Page Header– Detail– Page Footer– Report Footer

Bina Nusantara

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 DefinitionThere 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