Database Management

Here are my notes on how to manage data in computers within a large enterprise.

Sound: Electronic piano riff

DBForums

Take the Brainbench certification test on RDBMS Concepts & On-Line Analytical Processing (OLAP)

Topics this page:

Data Entry using Excel

SQL Programming

Data Warehousing

Site Map
About this site

Basic Concepts

A database contains one or more tables.
Each table is used to store rows and columns of information.
A column (field) is a single item of information kept for each record in the table.
A row of columns (also called a record) contains the information for a single entry.
A primary key is a field, or set of fields, that is used to uniquely identify a specific entry record in a table.
A foreign key is used to create a link between the table it is in and another table. The relationship between a primary and foreign key is that a foreign key in one table 'points' to a primary key in another table to create a link or relationship between a record from each table.
Referential Integrity describes the validity of the relationship between Parents and child data.
A variable is a container to store values which can be changed.
Variables must be declared before being referenced (no forward referencing).
If you don't know already what tables and fields you are going to use, GET AWAY FROM THAT COMPUTER. Go back to the drawing board and think it through. —Pamela Grimm
SQL statements can be embedded into C, COBOL, FORTRAN, and other programming source between exec SQL execute and end-exec for processing by the pre-compiler .
References to host variables from within a SQL block begin with a colon (:).
Database engines such as "SQL Server" do the queries, additions, deletions, etc that are performed on the data.
SQL = Structured Query Language The first implementation of SQL was on IBM's DB2 mainframe databases.
A data warehouse collects historical subject-oriented data for non-volatile time-series analysis using analytical engines such as Excel 2000. Such databases are kept separate from operational data stores to avoid degrading the performance of on-line transactions. This separation also allows for integration of data from various sources.
A trigger is a procedure that is stored in the database and executed automatically (implictly) before or after insert, update, or delete commands is issued against the associated table. Triggers are used to transparently create events logs which can be used to generate statistics or to review security enforcement. Trigger conditions must be a SQL condition and not a PL/SQL condition.
Scalar datatypes have no internal components.
composite datatypes have internal components which can be manipulated.

Get this print framed for your wall!

Database Diagramming with Visual Studio 6.0 and SQL Server 7.0 covers the purpose of the database diagram, how to create a database diagram using the Visual Studio 6.0 database diagram tool and how to version your schema in Visual SourceSafe.
Take the Brainbench certification test on Data Modeling Concepts, which measures knowledge of the most important aspects of data modeling. Designed for both administrators and developers, this test covers: Attributes & Keys, Entities, Implementation, Normalization, Relationships, and Uses & Benefits.

Database Software

According to Gartner Group:

Product Market Share %

2004 2003 2001

Oracle . 33.7 32.4 33.8

IBM's DB2 32.7 33.6 30.1

Microsoft's SQL Server
Provides an integrated security environment, where SQL Server uses Windows NT's SAM (Security Accounts Manager) database for validation of SQL Server logins.
SQL Server 2000 connections run over TCP/IP port 1433 TCP but SQL Enterprise Manager is run over port 1434 UDP.
20.0 18.7 14.9

Sybase 2.3 2.5 3.2

Informix — Low cost. 1.4 1.9 3.0

Others, such as

mySQL

MaxDB (until 3/04 SAP DB) open source db created by SAP to work with its ERM software.

innoDB (purchased 11/05 by Oracle)

Postgres (pgSQL)

SQLite is a small C library that implements a self-contained, embeddable, zero-configuration SQL database engine public-domained by D. Richard Hipp. It's available as a single ANSI-C source-code file that you can easily drop into another project.
An encrypted version is available for US$2500.

quickdb 2.3 by Matricsoft is a light database engine which resides in memory and which permits you to have a very versatile database engine for C++ programs. Quickdb does not rely on any other package, can be delivered in a dll and is compatible with XML.

Cloudscape

Firebird

InstantDB

Mckoi

Pointbase

Progress (proprietary 4G database)

Information Builders' Focus (proprietary 4G database)

NCR Teradata

HSQL, an in-core database (good for demo systems)

9.5 10.5 15.0

Mark Frawley notes in his four page article "Generating Realistic and Non-Random Sample Data" that is often harder than you might think to generate sample data, He explores the planning required for a successful data-mining demonstration and presents guidelines for realistic sample data generation.

ASPNeat and VBConverter or ASP Maker/ ASP.NET Maker explores SQL databases and creates ASP applications to perform updates.
In many shops, developers make use of such software. However, I think that DBAs should do the generation because inevitably the output needs to be tweaked.
In fact, DBAs should generate the ASP without fail every time they make a schema change, such as to create triggers using the INFORMATION_SCHEMA views provided by SQL Server.
Its TABLE view lists all the tables in a particular database.
Its KEY_COLUMN_USAGE table lists the primary keys in a particular database.

Microsoft Access

Consider using Leszynski's naming conventions and approach in his Expert Solutions book on-line —a timeless must read classic for Access developers.
Only the “Developer Edition” of Microsoft Office allows you to attach a menu bar or toolbar to a form.
Jet 3.5
Two places where you must use macros: 1) If you want some action to start on opening the database, you can only do that in the Autoexec macro. 2) VBA can't do is trapping keystrokes.
For an opening splash screen, put a BMP picture file into the same directory as the database and name it the same. So if you have Whisky.MDB call the picture file Whisky.BMP. Now when you double click on the whisky.MDB file from Explorer (or from a shortcut) the picture file is displayed. Admittedly it's pretty fast, but it's a quick cheat method that might be useful. Just remember, if you have a machine in excess of 200MHz, you may not see it at all! You might have to restart Access to get it to take. If you open Access, then whisky1, the bmp won't show. To bypass the start up screen, hold down the Shift key whilst opening a database.
third party ActiveX Treeview replacement GreenTree, Protoview and Sheridan.
Access 97 is not truly ActiveX compliant?
Access 97 is limited to 255 fields.
Microsoft's Upsizing wizard in Microsoft's Access Developer forum area.
Hidden Tables. To hide a table in the database windows so that other users can't select it, preface the table's name with 'Usys'. For example, if your table's name is employee, change it to UsysEmployee. This makes your table into a system object which access does not display. To see this table later, choose view, options, show system objects.
To find all the records that are in one table but do not have matches in another table, see MSKB article number Q95326.
Duplicate keys

Programming ASP to use Access databases via ODBC & OLE-DB

Word Jenny's Microsoft Access World Wide Developer Network has great tips and discussion forums and a developer's registry listing people such as Peter De Baets of Santa Monica.
Dev Avish's Access Web, an Access Web Ring member.
Dave Liske's MS-Access VBA FAQ at ZDU
FMS has a suite which addresses resolution issues with forms.
news:comp.databases.ms-access
Cary Prague used to have 25 Cool Control Tricks for Microsoft Access

mySQL

mySQL AB -- the world's most popular open source database.
mailist

Other Databases

A-XOrion for CGI-based database from World Wide Internet Database Consulting.

The connectionstring property is a string that can contain all the settings necessary to make a connection. The parameters passed in the string are driver-dependent. For example, ODBC drivers allow the string to contain driver, provider, default database, server, username, and password.

At design time, you can create a connection by setting the connectionstring property to a valid connection string, then set the RecordSource property to a statement appropriate to the database manager. You can also set the connectionstring property to the name of a file that defines a connection; the file is generated by a Data Link dialog box, which appears when you click connectionstring on the Properties window and then click either Build or Select.

If you wish to use create a connection string, select Use connectionstring and then click Build, and use the Data Link Properties dialog box to create a connection string. After creating the connection string, click OK. The connectionstring property will be filled with a string like:

driver={SQL Server};server=bigsmile;uid=sa;pwd=pwd;database=pubs

Is Your Data Normal?

Rules for the normalization of data:

First Normal Form (FNF)
A column cannot contain multiple segments (such as product1, product2, etc.).
For example, the names of people should be broken out into separate last name and first name fields.

Second Normal Form (SNF)
Every non-key column must depend on the entire key, and not just a part of the key. P> or exmple, in a table with a composite key containing the Customer ID and Part Number, ll the columns in that table must apply only to a particular customer and part number and no other.

Third Normal Form (TNF)
All non-key columns must not depend on other non-key columns. In other words, non-key fields in a table must relate only to the primary key of the table and not to another data field.
This forces data to relate exclusively to the entity that the table describes.
For example, in a table with a street address field, the zip code must not depend on the city. Also, an order table should not have a price field because prices apply to products, not orders.
This rule is often violated for the sake of convenience.

Fourth (Boyce Codd) Normal Form
Only related data entities be included in a single table.
In other words, a table should store only data for a single type of entity.
An example is to have books data in a separate table from the publisher information. This ensures that publisher information does not disappear even when all books are available for that publisher.

Fifth Normal Form
A table divided into multiple tables can be JOINed back together. In other words, each sub-table should contain a key common to other sub-tables.
For example, since MS-SQL7 only supports up to 64,000 members per file, a large file may need to be divided by city. Large cities may need several areas.

Normalization improves query performance, but increases the complexity of the database schema -- making it is more difficult to manage the data and to construct queries.

Your rating of this page:
Low High

Your comments on this topic, please:

Publish this comment publicly

Your first name:

Your family name:

Your location (city, country):

Your Email address:

Email me updates

Top of Page

Thank you!