SQL + Vark

In honor of Google's acquisition of Aardvark, thought I'd share a SQL-related Vark from a while back.

Here's how I spent part of my morning in an empty house.

http://vark.com/t/83c38c

Question

Dec 05, 2009

Sent to Aardvark via IM

Friend 1

22 / M / Omaha, NE

In database design, are relational schemas created with ER diagrams usually in 3NF? Is there a case where ERD schemas wouldn't be in 3NF?

Answer 1

+ 11 minutes

Aardvark found someone who knows about database

William A. (<--- that's me)

I am a database architect. For a relational system, yes, diagrams are created with at least third normal form. It is a part of the design process to consider business rules, usage and business entities... strictly designing a system to a normalization spec is not realistic. When designing a data warehouse however, converting higher-normalization relational data into a less normalized form may lead to better performance in a system that will be primarily for reads, not writes.

Friend 1

Is there a something about the ERD approach to designing relational schemas that makes it generally create schemas that satisfy third normal form, or is it just something you keep in mind during the design process?

William A.

So, I wasn't aware ERD was an "approach". Typically a CERD is followed by a PERD. That is, a Conceptual ERD, where relationships are specified as M-M, and objects are in business terms that a client would be able to easily recognize and comprehend. A Physical ERD is not easily consumable by a client or business user because it translates the CERD into database tables and foreign key relationships. The PERD contains join tables that break many to many relationships into a pair of 1-M relationships, for example. So building an ERD is to me separate from proper Normalization methodology.

Friend 1

Yeah, ERD schema design and 3NF normalization are usually not thought of simultaneously, which is why I'm having a hard time finding any resources that relate the two. Much harder is figuring out how well ERD creates schemas which satisfy 3NF, whether that was the intention of the design or not.

William A.

To me, ERD is not a design process or architecture methodology. To me, it is a document. Yes, ERD can display a schema that has been created in 3NF well, though typically only a PERD would be capable to displaying proper normalization. I'd suggest papers or books by CJ Date or EF Codd

Friend 1

I'll look into it, thanks for your help.

William A.

good luck

He'll need it.

Aardvark is a really neat social tool that is sort of like the oft-advertised KGB, except the answers come from other users who have signed up for knowledge in general categories. I signed up for Aardvark after hearing a story about it on NPR, and had a delightful conversation about Xbox360's and the weather with a fellow named Mert in Turkey. As you can see, the above conversation was brought to me because I signed up as knowledgeable in the area of database.

I've also Vark'd (is that a word?) about Conan O'Brien's TV ratings and martial arts schools for my kid. But since this is a SQL blog, I figured I'd share this one.

UPDATE: Sadly, Google killed Aardvark in September 2011.  Oh well.