Session Seven Additional Considerations

Topics

• Review of  Worksheet Five
• Indexes
• Views
• Denormalize the Database Design
• Plan Physical Storage Usage
• Database Build Overview
• Forward Engineering
• Review for the Final

Indexes

Indexes:

• Can significantly improve data access time.
• Provide quick access to rows of data and avoid full table scans
• Facilitate table joins
• Ensure uniqueness of a value if defined as unique
• Are used automatically when referenced in the WHERE clause of a SQL statement if the column is not modified


A concatenated index is an index created on a group of columns in a single table.

Map a composite key to a concatenated index.

Use indexes to implement keys and to support application access requirements.
 

Build Indexes for
• Primary keys (unique indexes)
• Foreign keys (generally non-unique indexes)


Consider indexing

• Alternate keys (unique indexes)
• Any critical non-key columns used in WHERE clauses
• Any search keys


Indexes add storage and update overhead.

• An Introduction to Database Indexes
• Additional information on Indexes
• SQL Performance Tuning using Indexes

Back to the top

---

VIEWS

Predefined windows into the database.

Establish database views to meet application access requirements .


Views can be used for:

• restricting access.
• providing referential integrity.
• presenting tables to users in any form.
• pre-packaging complex queries.
• producing rapid prototypes.
• pre-joined base tables in SQL*Forms.
• checking data input.

Views can:

• Restrict what the user, designer, or tool sees.
• Present normalized data in a denormalized form.

Use views with caution.

Access through a view is slower

View may cause query optimization to be slower.

Audit Checks
 

• Additional Information on Views
• Oracle and Views

Back to the top

---

DENORMALIZE THE DATABASE DESIGN

Start in 3 NF

Beware of Denormalization! Be VERY reluctant to denormalize.

Denormalization can cause data inconsistency problems.

Denormalization may be needed for transactions with performance requirements such as:

• high throughput.
• high frequency.
• quick response time.

Consider all other options prior to denormalization, especially adding or changing the index structure.

Combining tables is the most common form of denormalization.

Individual codes tables may be combined into a reference table.

• This will allow for validating and decoding coded values for an entire application system.
• Establish a companion CODE_TYPE table for validating code description lengths.

A vector is: A one-dimensional array with a fixed number of values A repeating group of definite size. Represent vector data as either a set of rows or a set of columns. Choose the design based upon the functional access requirements.


 

Column-Wise Table Design


• SQL group functions act on columns, e.g., SUM, AVG.
• Changes in the vector length can be easily accommodated.

Row-Wise Table Design
 

On the input form, all data values can appear on a single line.

All values can be inserted with a single INSERT statement.

The storage space requirement is lower.


Output reports showing all values horizontally are easy to produce.




Reconsider storing derived data in light of the functional access requirements and the capabilities of the software development tools.

Back to the top

---

PLAN PHYSICAL STORAGE USAGE

Work with the Database Administrator to plan the physical placement of the database tables and indexes.

Back to the top

---

DATABASE BUILD OVERVIEW

In Database Build, create physical relational database tables to implement the database design.
The next step is to create DDL (Data Definition Language) that is need for the desired database system.  This code or script file is used to create the database schema.

SQL> CREATE TABLE DEPARTMENT

2  (DEPTNO  NUMBER(2) NOT NULL PRIMARY KEY,
3  DNAME    CHAR(20)  NOT NULL,
4  LOC      CHAR(15)  NOT NULL );
SQL> CREATE TABLE EMPLOYEE
    2  (EMPNO   NUMBER(5) NOT NULL PRIMARY KEY,
    3  FNAME    CHAR(15)  NOT NULL,
    4  LNAME    CHAR(15)  NOT NULL,
    5  JOB      CHAR(9),
    6  HIREDATE DATE      NOT NULL,
    7  SAL      NUMBER(7,2),
    8  COMM     NUMBER(7,2),
    9  MGR      CHAR(4)   REFERENCES EMPLOYEE(EMPNO),
    10 DEPTNO   NUMBER(2) NOT NULL REFERENCES DEPARTMENT(DEPTNO) );

Back to the top

---

DATABASE MANIPULATION LANGUAGE

Once the structure for the database has been created then DML is used to load, update and delete data.

---