Firebird as an Alternative to Oracle/MS SQL Server/Interbase/Access

Features of Firebird

What is firebird:

The software has two main components: the database server, which lives on the same host machine as the databases, and the

application interface, commonly referred to as “the client library”. The client runs in the client pc or in the middleware

for accessing through internet browsers.

The server's executable is less than 1.5 Mb and a full server installation, including all tools and documentation, takes

Versions:

1.5 most stable and tested

firebird 2: Mid of year 2006

Vulcan: A parallel project used by SAS to move many of its business applications from Oracle to Firebird. Purpose: redesigning the threading architecture of the database engine

Is Firebird “Enterprise Capable”?

stability, scalability, availability, capacity, interoperability and autonomy.

Stable enough, supports strongly ACID properties

Good enough but not extremely scalable

Firebird's upscaling is merely a question of adapting the environment. The same engine comfortably handles anything from

being embedded in a stand-alone client application, through to a classical two-tier client/server LAN of around 750

potential users, to incorporation in a multi-tier solution for thousands of potential clients. Database growth is

effectively limited only by the disk storage available and can be split across multiple hard disks.

Through smart replication and good connection management in the access layers, the workload of a busy system can be

distributed across multiple servers. For example, a well-resourced central server can handle the interactive demands of

LAN, intranet or extranet (or all together) while a replicated server takes care of long-running jobs that need to isolate

a snapshot of data for lengthy periods.

Among its users, Firebird has a reputation for being bomb-proof.

It uses optimistic locking at record level, drastically reducing the wait-time overheads in comparison to others where

read-write transactions lock entire sets, even tables, pre-emptively. No tuning is ever required to facilitate handling

varying workloads through the day or week. A database does not have to be shut down for backups. It can be replicated or

shadowed for almost instantaneous cutover in the event of disk failure. It is robust and recovers immediately from power

failure, without loss of database integrity.

Supports online backup.

Firebird is a popular choice for enterprises needing continuity of service around the clock. Command-line tools are

distributed with the software for all administrative activities, allowing regular housekeeping to be automated as

scheduled or on-demand jobs. A Services API is also available to wrap admin tasks into a program or service application.

Firebird Databases as the Back-end to Enterprise Software Systems

Comparison: FireBird, MySQL, PostGreSQL

PosTGRESQL Provides better query responsetime than firebird and MySql. Firebird stands somewhere in the middle. A little longer response time than PostGreSql.
http://benchw.sourceforge.net/benchw_results_open3.html

Several PostGreSQL versions are compared at:
http://benchw.sourceforge.net/benchw_results_postgres_history.html

Several MySQL versions are compared at:
http://benchw.sourceforge.net/benchw_results_mysql_indexes.html

PostGreSQL Supports 400+GB DB. One source: As for each connection it forks new thread, it is bit slower than MySql: Another source: postgresql is faster than mysql. Seems the truth is: for single connection postgresql can be faster but for multiple connections mysql is faster. just an information, not sure yet.

New PostGre Features

Supports roles, in/out parameters/two phase commit/table partitioning shared row locking(firebird record level locking), 64 bit shared memory hence supports two terabytes of ram

Important Notes

Wikipedia link for postgressql-http://en.wikipedia.org/wiki/PostgreSQL

PostGreSQL Limitations and solutions

http://searchopensource.techtarget.com/originalContent/0,289142,sid39_gci1172668,00.html

PostGreSQL Windows Installer http://pgfoundry.org/projects/pginstaller/

Important Links for Mysql, PostGreSQL, Firebird, EnterpriseDB, Ingres

Firebird and postgresql: http://www.linuxjournal.com/node/7010
Important comment: easy to migrate from oracle to firebird than to postgre or mysql. From interbase to firebird will be better as they are similar.

http://forums.devshed.com/firebird-sql-development-61/mysql--vs--firebird-sql-62269.html : Firebird is far better choice than mysql
As for Performance, MySQL will outperform Firebird on almost all tests on local machine......
On a Network Server a properly designed Firebird Server will outperform MySQL on any Test and the network-Traffic generated by Mysql will be many times higher than Firebird So you decide the one you need according to your needs .....

This link also compares fb with potgre and favors fb as the author's background is borland

Why Postgress is better than firebird

Firebird is not as good as PostgresQL (Score:0) By Anonymous Reader on 2004.12.03 2:21 (#102703) And here is why:

1. Firebird has no temp table support.

2. Firebird uses several SQL modes i.e DSQL which only works client side and PSQL which only works in procs. All sql works in Postgres functions. Try creating a table or a user in a Firebird proc...ooops can't be done.

3. Firebird databases grow out of control and have to be backed up restored on a regular basis.

4. Firebird has a concept of a OAT (oldest active transaction counter) if this counter gets stuck all transactions get held up and your DB size goes through the roof and performance goes to the basement.

5. Firebird has virtually no built in functions and flakey UDFs must be used. Postgres on the other hand has every function you can think of.

6. Postgres has many rich procedural languages including perl,TCL,Java and C# in the works.

I could go on.... Version 8 of Postgres which will be released soon adds point in time recovery, Try except error handling in functions, table spaces and more.

I converted all my apps from Firebird 1.5 to Postgresql 7.4 and I would never use Firebird again on Unix or Windows

Why PostGress better than FireBird: This comparison may not be accurate though.

Speed: Firebird vs. PostGreSql

Please read. According to this article postgresql faster than firebird. But I also got information/tests where firebird is faster than postgresql.

http://archives.postgresql.org/pgsql-advocacy/2003-06/msg00280.php

However, Speed may not be the only factor to select a database product. We can improve raw speed in many alternate ways, hardware solutions, ram, faster storage, faster CPU. To compare speed issue, efficiency issue, we need to take a look how the query optimizations are implemented in databases. How, the joins, inner joins, outer joins are implemented. How multiple joins are implemented. Also, indexing is a factor that also affects performance issue. We can try creating same database and using a queries that are simple to complex and run on both databases and see, how well they perform.

Postgresql supports SMP (multiple processor system) and provides better efficiency in multiprocessor systems. Firebird, is not still matured in SMP environment.

Firebird supports 32 bit environment, still not stable/reliable enough for 64 bit env. It supports max ram of 2 gb and hence 150 - 450 concurrent users. Postgresql supports 2 TB of ram in a single server. For both of them, we can implement multiple servers with master/slave relation for better performances.

Tools to create ER diagrams from a database dynamically

Microsoft Visio, and dbVisualizer provide features to create ER diagrams from existing database tables.

Why needed?
ER diagram is very useful to understand the total
database structure that helps a new programmer to work on the existing databases more efficiently.

Visio:
While creating Database diagrams, a menu item named database is displayed. Reverse Engineering option under Database menu helps to create ER diagram from 

existing database tables. Before that, target database drivers for visio need to be installed. If the database is in db2/mysql/postgresql, then  

db2/mysql/postgresql drivers for visio should be installed. From Database/options/drivers, the driver needs to be configured. Afterwards, Reverse Engineering 

option may be used to create the ER diagram. There is no suitable driver for postgresql databses for Visio. ODBC drivers may be used for the purpose (the ER 

diagram may not be accurate from ODBC driver for postgresql). 

dbVisualizer:
In dbVisualizer, when the TABLEs group/option is selected in the left pane in the right/middle pane there will be a references option. When the references 

tab is selected, an ER diagram is displayed using all tables. The ER diagram can be displayed as Hierarchical, circular, orthogonal, or organic. There are 

options for the purpose.

When a single table name is selected, the table and the associated tables are displyed in an ER diagram.

In the rightest column, selected tables option allows to create ER diagrams only for the selected tables. When 'selected tables' is clicked a list is 

displayed in a dialogbox. Tables can be selected from this list (use control key). Build graph  option creates and displays the ER diagram.

Normalization (1NF to 5th NF)

DBMS System Online Course

Please check the resources at:
http://www.cs.sfu.ca/CC/354/zaiane/material/notes/contents.html

Just like an undergraduate course in Database Management Systems. Along with other topic, ER-Diagram and representation of ER-Diagrams into tables are provided. Some of the complex forms of ER diagrams are discussed.

Practical Database Design Resources

• http://www.dbpd.com/vault/9804date.htm
• http://cs.senecac.on.ca/~dbs201/pages/Normalization_Practice.htm
• http://cisnet.baruch.cuny.edu/holowczak/classes/3400/norm_ex.html
• http://thecommunityengine.com/bit320/syllabus/
• http://www.ibm.com/developerworks/web/library/wa-dbdsgn2.html
• http://www.phpbuilder.com/columns/barry20000731.php3
• http://www.devx.com/ibm/Article/20859
• http://www-128.ibm.com/developerworks/web/library/wa-dbdsgn1.html
• http://www.devx.com/ibm/Article/20702

Normalization in Relational DBMS Systems

First Normal Form:


All table columns should have distinct meaning

In another word: All attributes of an entity/table must be unique

Solution: 

Grow the table row wise[in the direction of rows] not the column wise
Group the columns with same meaning into one
To handle multiple values for the same attribute of an entity create multiple rows [not multiple columns]

To keep track of paycheques, you may think the table should be as follows:

Name, provider company, date 1, pay cheque 1, date2 , pay cheque 2, date 3, paycheque 3

See date1, date2, and date 3 have the same meaning
Pay cheque 1, Pay cheque 2, and Pay cheque 3 have the same meaning
These are the repeating attributes

Solution:
create table with:

number name, company, date,   paycheque
100    1,    xyz,      12/13,  1000.00
100    1,    abc,      12/13,  1000.00
100    1,    xyz,      12/31,  1000.00

or 

create a child entity with Date and amount. And a master entity with number, Name and Company
Entity one: number, name, company
Entity two: number, date, amount


2nd Normal form:
----------------

Rule: 

All attributes can be identified from the primary key. Primary key is directly related to all other attributes.
All attributes are fully dependent on the primary key.

Line Item Table/Entity:
--------------------
number [line item] [pk]
order number[pk]
vendor name
vendor town
product code
product amount

Here, vendor name and vendor town may not be fully dependent on the whole primary key. They are dependent on the order number.
So we can decompose it into another table.

Order entity
Number [pk]
Vendor Name
Vendor Town

Lineitem entity

number [pk]
order_number [pk]
product code
product amount


3rd normal form
-----------------
Rule: Non key attributes are fully dependent on the primary key but not on any other key or attribute.
In Order entity table, vendor town is not fully dependent on number[pk], but it does depend on the vendor name. So we can decompose order entity table into two

Vendor Entity
Name [pk]
Town

Order Entity
Number
vendor-name

Boyce/Codd and Fourth Normal Form

Boyce/Codd Normal Form
----------------------
It is just an extension to the third normal form. Third normal form ensures that non key attributes does not depend on any non key attribute but fully depend on the key[primary] attributes.

Boyce codd normal form ensures that non key attributes are fully dependent on the total set of the primary key. Non-key attributes should not depend [only] on a subset of the key[primary] attributes.

Fourth Normal Form:
-------------------
It tries to decompose an entity/table into multiple entities/tables when there are multiple independent[not dependent on each other] multivalued attributes in an entity/table.

For example: Consider a table with emp_number, skill name, objective of the employee. Employees may have many skills and also many objectives. Hence, skill_name, and objective are multivalued and also they do not depend on each other. This table will cause many redundant data.Better is to use two tables like:

Emp_skill
Emp_num, Skill Name

Emp_objective
Emp_num, Objective

Logical Data Modeling: Logical Database Design Steps: RDBMS

Logical Data Modeling

1. Identify major entities
2. Determine relationships between entities
3. Determine primary and alternate keys
4. Determine foreign keys
5. Determine key business rules
6. Add remaining attributes
7. Validate user views through normalization
8. Determine domains
9. Determine triggering operations
10. Combine user views
11. Integrate with existing data models
12. Analyze for stability and growth

1. Identify tables
2. Identify columns
3. Adapt data structure to product environment
4. Design for business rules about entities
5. Design for business rules about relationships
6. Design for additional business rules about attributes
7. Tune for scan efficiency
8. Define clustering sequences
9. Define hash keys
10. Add indexes
11. Add duplicate data
12. Redefine columns
13. Redefine tables

1. Provide for access through views
2. Establish security
3. Cope with very large databases
4. Access and accommodate change
5. Anticipate relational technology evolution

Query Optimization in PostGreSQL

"optimization effort is caused by the support of a variety of join methods (e.g., nested loop, hash join, merge join in PostgreSQL) to process individual joins and a diversity of indexes (e.g., R-tree, B-tree, hash in PostgreSQL) as access paths for relations. "

"The current PostgreSQL optimizer implementation performs a near-exhaustive search over the space of alternative strategies. This query optimization technique is inadequate to support database application domains that involve the need for extensive queries, such as artificial intelligence. "

Query Optimization in Firebird

What is Data Warehousing?

Data Warehouse is a storage of an organization's historical data. Usually, data warehouse is built to provide a decision support system to the management. Data warehouse can be mined (using data mining) and queried to collect overall and summarized business information that significantly helps in decision making. It can provide trends over many years.

Operational databases are optimized for day to day operation access. Here, the relationships are normalized and further divided into more relations/tables to make accessing faster that also reduces data traffic among the systems. But data warehouse does the reverse, where relationships/tables are de-normalized as much as possible to provide the required information to the management. As datawarehouse provides summarized information de-normalization helps, otherwise the data would be required to collect from many tables and the queries may be running over data for 10 years (millions/billions of records). Usually, data warehouse is created separately from the operational databases.

Microsoft SQL Server and Oracle both have supports and tools to create Data warehouse Systems. There are also free tools for the purpose.

Random Information on BI

• Real internet money/business lies in B2B not in B2C.
• Decision Support System (DSS): Full spectrum of systems that allow/help management to take decisions, such as, reporting, OLAP, data mining.
• Java provides support for web enabled DSS
• JDM is Java's Data mining/BI API.
• BI Applications: Balanced Score-card, Activity Based Costing.
• Data warehouse elements
  • Operational Environment: Maintains day to day activities. Raw Data
  • Independent data mart: A small data ware house specific to one topic/area/subject
  • Extraction: Extraction of data from operational systems
  • Extraction Store: Data storage after extraction
  • Transformation and cleansing: Before putting data into the central/main warehouse data needs to be cleaned up
  • Extraction log: Create a log in the transformation and cleansing process
  • External source: Data originated outside the organization is also incorporated into the data warehouse.
  • Data administrator: The person who ensures the quality of data
  • Central Repository: Stores all data and meta data
  • Meta data: Data about data. Example: the extraction log.
  • Dependent Data Mart: Relies on the data warehouse for the sources of its data
• Distributed Data Warehouse: Warehouse data is physically distributed across a company's information infrastructure.
• Data mining types: classification, and estimation.
• Classification: categorize customers, Estimation: predict or estimate some numerical value
• Decision trees: modeling technique helps in deciding. Different alternatives are drawn as paths where nodes represent the selected alternative. edges/paths may carry some probability values as selection/influencing criteria.
• Neural networks: Try to mimic the work nature of human brain to take a decision.
• General modeling: Well-suited for categorizing.
• Balanced Scorecard application: views the health and well-being of the organization by more than its financial data. We need to define: Vision, Strategy, Strategic themes, Strategic Objectives, Key performance indicators.
• Activity based costing application:
• Three types of organizations: Engineer driven: They know how to make a product. Than sales it through sales force. Sales Driven: They know how to sale a particular product, then make the product and sell it. Customer driven: They Know what customers want, then make the products and sale them. Customer driven organizations are the most successful.

How to implement BI/Warehouse

• Crime Detection using BI
• Credit Card fraud detection using BI
• A presentation on Oracle Data Mining
• Oracle by Example - Business Intelligence
• Oracle Data Mining Components
• Business Intelligence with Microsoft SQL Server Reporting Services - Part 1
• SQL Server 2005 Analysis Services Tutorial
• SQL Server 2005 Data Mining
• BI for MySQL
• MySQL in Data Warehousing & Business Intelligence
• Business Intelligence in Mysql

Steps in Data Warehouse Design and Implementation

• Most BI projects usually fail. It is not due to the errors in construction steps but due to the inappropriate design steps and methodologies.
• Waterfall method is not appropriate for BI. A method like developmental spiral may be more appropriate. Ref: [W. A. Giovinazzo ]
• Steps in developmental spiral: Definition, Analysis, Design, Development, Implementation/Deployment, Maintenance, Evaluation
• Developmental spiral method is effective for BI only when object oriented approach is taken
• In developmental spiral, object modeling is used to represent objects in the related business areas. In object modeling, objects are represented as rectangles, the rectangles can be treated as classes. Classes/objects can have sub-classes/sub-objects. Objects have attributes. Objects also have some special properties like: accelerators, stops.
• Definition - first step of developmental spiral: Define a short description what you want to build - like the problem statement in a research proposal. Example: I want to build a data mart that will provide data to support the analysis of customer demographics such as age, gender, and income for all product lines
• Analysis Phase: In this step, analyze the requirements of the BI system, the nature of the BI system, the expectations from the BI system. Interviewing different parties will help a lot:
• Interview the sponsor to get a detail understanding of the mission. The questions will include: Data-related(which objects are related to the business needs?), user-related: who are the users, system evaluation: what parameters will make the project a success?
• Interviewing management: To understand management's expectation of the data warehouse. Questions may include: User profile related (what types of systems you use/will use to access BI?), System vision: what do you see as the objective of the data ware house, System Evaluation: what parameters will make the project a success?
• Interviewing Users : Interviewing users who will use the system
• Interviewing information technology staffs: To know the current state of the organization's information systems
• Design phase: output - object model, implementation model
• Start with the analysis phase information. The design phase is complete when all the objects related to the mission are well defined. An object diagram needs to be drawn showing all objects, relationships, attributes.
• Another chart listing all the attributes and their data types will be useful. Objects will have relations like super classes or sub-classes.
• Cardinality relationships: exactly one, one to many, zero or one, zero to many
• aggregation may be required in some cases. Aggregation: an object is composed of other objects.
• Implementation Model: Data warehouse databases are multi dimensional databases. Objects are represented in more than two dimensions. Common practice is: three dimensional databases. Such as an object/table represented with three dimensions (called cube) like: product, dealer, and time. RDBMS's are two dimensional.
• Data cubes: have six possible different views [permutation of three dimensions].
• Why not always multidimensional databases: because of space requirements.
• Star schema: Provides a multidimensional flavor in two dimensional relational databases.
• Star schema uses a concept called fact tables to bind dimensions to create a multidimensional space.
• Denormalization of the tables are utilized in star schema to create multi dimension.
• Dimension table: Think about a three dimensional cube. Each wall represents a dimension table. Or think it as a mathematical combination of the dimensions.
• All objects represented in the dimensions can slowly change over time - slowly changing dimensions. There are many approaches to address this issue.
• Snowflakes: normalizing dimensions - not always a great thing
• Implementation considerations: Parallel processing, Bitmapped Indexing, Star Query Optimization, Summation Tables

MySql Administration SQL Commands

Optimize Cursors

 Avoid using SQL Server cursors whenever possible
 Always close SQL Server cursors when result sets are not needed
 Deallocate SQL Server cursors when the data structures comprising the cursors are not needed
 Reduce the number of records to process in the cursor
 Only use the required columns in the cursors 
 Use READ ONLY cursors, whenever possible, instead of updatable cursors.
 Avoid using insensitive, static and keyset cursors, whenever possible.
 Use FAST_FORWARD cursors, whenever possible.
 Use FORWARD_ONLY cursors, if you need updatable cursors 

Cursors are server side database objects that are used by 
applications to apply operations on the database table data 
on a row-by-row basis. The operations may vary from one row 
to another row dynamically based on the requirements 
(business logic) and also multiple operations can be 
performed on the same row. Typical SQL commands apply the 
same updates/changes to all the selected rows at once where 
cursors apply updates/changes one by one row. 

Cursors must be declared in the database before they can be 
used/called from the applications [front end]. Afterwards, 
you can open cursors to fetch data using them, you can fetch
row by row and make multiple operations on the currently 
active row in the cursor. You should close the cursors and 
deallocate them after you are done with the cursors.


This is the Transact-SQL Extended Syntax:

DECLARE cursor_name CURSOR
[LOCAL | GLOBAL]
[FORWARD_ONLY | SCROLL]
[STATIC | KEYSET | DYNAMIC | FAST_FORWARD]
[READ_ONLY | SCROLL_LOCKS | OPTIMISTIC]
[TYPE_WARNING]
FOR select_statement
[FOR UPDATE [OF column_name [,...n]]]

An example cursor:

DECLARE AuthorsCursor CURSOR FOR
SELECT authors.id, au_lname, au_fname
FROM authors
ORDER BY authors.id

Another Example

DECLARE @AuthorID char(11)
	
DECLARE Cursor1 CURSOR READ_ONLY
FOR
SELECT au_id
FROM authors

OPEN Cursor1

FETCH NEXT FROM Cursor1
INTO @AuthorID

WHILE @@FETCH_STATUS = 0
BEGIN

	PRINT @AuthorID

	FETCH NEXT FROM Cursor1
	INTO @AuthorID

END

CLOSE Cursor1
DEALLOCATE Cursor1


where

cursor_name: The name of the server side cursor

LOCAL: The cursor will be available only to the batch, stored procedure, or trigger in which the cursor was created 

GLOBAL: The cursor is global to the connection 

FORWARD_ONLY: The cursor can only fetch data sequentially 

STATIC: The cursor will use a temporary copy of the data instead of base tables 

KEYSET: Specifies keysets so that the membership and order of rows in the cursor are fixed when the cursor is opened 

DYNAMIC: The cursor reflects all data changes made to the base tables as you scroll around the cursor 

FAST_FORWARD: The cursor is FORWARD_ONLY and READ_ONLY  

READ ONLY: The cursor [data] cannot be updated

SCROLL_LOCKS: The fetched data will be locked 

OPTIMISTIC: The cursor does not lock rows as they are read into the cursor 

select_statement: The standard select statement, cannot contain COMPUTE, COMPUTE BY, FOR BROWSE, and INTO keywords

UPDATE [OF column_name [,...n]]: Specifies which columns can be updated

SQL Server Hints
----------------
What are hints?
Simply to influence query execution plans to retrieve data 
faster.

syscacheobjects
----------------
SQL server after compiling a query creates the execution 
plan and keeps it on syscacheobjects object.
You can query the object to see the existing query plans as 
follows:

select * from sys.syscacheobjects
where cacheobjtype='compiled plan'


Hints
-----

When you specify hints in your queries the query execution 
plans are biased toward your hints rather than using default
 strategies.

Types of Hints
--------------
Join Hints: Affects the joins [join types]
Query Hints: Affects overall execution plan
Table: Affects to table access 


Join Hints
----------
Loops: Uses nesting for join. Each row of the inner table is
 checked for condition satisfaction with the corresponding 
outer row.
Hash: One table is treated as a hash table and the other is 
scanned one row at a time and the hash function is used to 
find equalities

Merge: Each table is sorted first. Then they are compared 
row by row [corresponding]

Remote: In this case, at least one table is remote. The join
 is done at the right table side.


Example:

select *
-----
----
---
option (merge join)


or

select * 
from x
inner merge join y on...


Query Hints:
------------
Specified at the end of the query with keyword 'Option'


select * from x
------------
------------
option (recompile, fast 80)
Hash Group or Order groups:  Aggregations (group by, compute, distinct) are done by hashing or ordering
Concat Union, Hash Union, Merge Union: All unions will be done with the specified union type
Loop Join, Hash Join, Merge Join: Joins will be done with the specified join types
FAST number_rows:
Force Order:
MaxDop:
Optimize For:
Recompile:
Keep Plan:
Expand Views:



Table Hints:
------------
Affects how the table will be locked or which index will be 
used for the query.
NoEXPAND
INDEX
fastfirstrow
nowait
rowlockpaglock
tablock
nolock
holdlock
ignore_triggers


Plan Guides
-----------
Sometimes queries are originated from a third part application and you have no control to change the query.
You can use plan guides to influence such queries. For example: Using plan guides you can specify the nature of the
 select, update, insert queries.
You can use built in stored procedures to create or drop 
plan guides.

sp_create_plan_guide
sp_control_plan_guide