Category Archives: SQL

A suggestive picture for the concept of database grading.

Database Grading, Part 2: Database Models

picture by Greg via Flickr

This article is the second in a series of two blogs that present our grading scale for database quality. We assign separate grades for the quality of a database design (previous blog) and the underlying model (this blog). The design grade measures the quality of the database syntax. The model grade measures the quality of the semantic concepts underlying the database. Applications can have different design and model grades.

Continue reading Database Grading, Part 2: Database Models

A suggestive picture for the concept of database grading.

Database Grading, Part 1: Database Designs

picture by Greg via Flickr

While working on database projects, we often find ourselves doing reverse engineering. Reverse engineering is the inverse to normal development. Developers start with an application and work backwards to understand the software and infer its intent. Reverse engineering can apply to a variety of artifacts, such as hardware, programming code, and databases. Our focus here is on relational databases.

There are many reasons for database reverse engineering. One reason is to assess software quality. For example, you may want to assess the quality of a vendor product or an internal legacy application. Information systems revolve about a database, so you can use database quality as an indicator of software quality.

This article is the first in a series of two blogs that present our grading scale for database quality. We assign separate grades for the quality of a database design (this blog) and the underlying model (next blog). The design grade measures the quality of the database syntax. The model grade measures the quality of the semantic concepts underlying the database. Applications can have different design and model grades.

Continue reading Database Grading, Part 1: Database Designs

Database Design Errors

Miscellaneous Database Design Errors

picture by strange little woman on stream via Flickr

This is the third and final blog in a series about database design errors. The two previous blogs addressed primary key and foreign key errors as well as confusion with many-to-many relationships. Now let’s discuss several other design errors. Our coverage is clearly not complete, but these are common mistakes that are found in practice.

Continue reading Miscellaneous Database Design Errors

Database Design Errors

More Database Design Errors – Many-to-Many Relationships

picture by strange little woman on stream via Flickr

A many-to-many relationship is an intersection of two entities. A person may own stock in multiple companies. A company can have multiple stockholders. The combination of a person and a company stock has an attribute of the number of shares owned. A many-to-many relationship depends for its existence on the underlying entities.

Relational databases and SQL have robust support for many-to-many relationships. Referential integrity keeps relationships consistent with their underlying entities and SQL can efficiently combine them in queries. In contrast, programming languages lack support for keeping references consistent with referents and providing easy traversal. As a result programmers have to use workarounds to handle many-to-many relationships in their code. Sometimes they make the bad decision to contaminate a database with their programming representation.

Continue reading More Database Design Errors – Many-to-Many Relationships

Traversal

Traversal of Data Models

picture by Onilad via Flickr

Developers routinely use data models for defining database structure. This is beneficial, but it uses only part of data modeling’s power. Data models not only capture data structure, but they also express the potential for computation. Traversals of models can provide blueprints for resolving use cases, phrasing SQL queries, and assessing quality.

Continue reading Traversal of Data Models

cover for Agile Data Warehouse Design

Agile Data Warehouse Design Video Course

My new video course — Agile Data Warehouse Design — is now on the market.

A data warehouse is a database dedicated to decision support and business analysis. The inputs to a data warehouse are data from the day-to-day operational systems. A data warehouse integrates the input data and restructures it so that it is amenable to data mining.

With an agile approach developers build a data warehouse rapidly to get it into the hands of business users so they can give feedback. Where possible, SQL code substitutes for programming and ETL scripts.

The course is organized about a threaded case study for the retail industry.