70-451 – Designing a Database Solution – Designing a Database Model

Requirement

Design a database model. This objective may include but is not limited to: normalization, entities, entity relationships

My Take

This seems like a pretty cut and dry measure of your ability to design rational databases using the standard Entity – Attribute – Relationship Model (or Entity – Relationship Diagram ERD). I’m somewhat curious to see how it is that they would measure this skill on a multiple choice test.

I feel like at this point I have a decent grasp on this topic, as I have a few years of practical experience, as well as recently having read through a very thorough delve into the topic (Data Modeling Essentials, Third Edition – G. Simsion). I realize that I can’t spout off the differences between each of the normal forms without second guessing myself; but I believe that I am able to come up with third normal form or higher designs by instinct. See the Idiom: "Every non-key attribute must provide a fact about the key, the whole key, and nothing but the key … so help me Codd!".

Normalization

Microsoft has it’s page on Normalization which is at a bit higher level than that normally talked about to describe normalization (for instance, the wikipedia page on Database Normalization would appear to be much more informative). In particular, the Microsoft page mentions that normalization is the core of an optimized relational database and that good logical database design can lay the foundation for optimal database and application performance. Conversely, poor logical database design can hinder performance of the whole system.

Benefits:

  • Faster Sorting and Index Creation
  • A larger number of clustered indexes
  • Narrower and More Compact Indexes
  • Fewer indexes per table.
  • Fewer null values and less opportunity for inconsistency.

Achieving the well designed database

  • A table should have an identifier
  • A table should store only data for a single type of entity
  • A table should avoid nullable columns
  • A table should not have repeating values or columns.

Warning: Normalization can lead to complexity in joins required to retrieve data, which can in turn lead to sub-optimal performance.

Entities

An entity in an ERD (or ERM, or RM) is something capable of independent existence, whether it be tangible or intangible. It is the "noun" when you are describing things. More than that, entities are really going to be the foundation of your model.

Oddly enough, looking around I did not find any particular page on msdn B.O.L. dealing with Entities.

Entity Relationships

The entity relationships are definitions of how entities interact with and / or are related to other entities. These are generally represented as the "verbs" when you are describing things.

Examples

clip_image001

As it appears I am getting really bad at producing a description of what entities and entity relationships are without examples, I will provide an overly simplistic view instead. Given the above picture, we can see that we have three entities, or "nouns". Owner, which provides a list of names for those people or things which will potentially own something; Blog, which contains a set of entries related to individual blogs – namely a name and a description of the blog; Posts, which contain the content of the individual entries, or posts of the blog.

We can also see how the entities are related to each other by the relationships, or "verbs", between them. An owner owns a blog, a blog is owned by an owner. A blog contains a post, a post is contained by a blog. We could further delve into what it means to have relationships with zero, one or more potential related items on either end of the ERD, but I think that is probably out of the scope of notes which I am crafting for myself to study for this exam.

As for normalization in this example, we could have included the owner and blog information along with every post.  However, we would be storing redundant data, and if this information changed, it would need to be updated in many places.

Advertisements
This entry was posted in SQL and tagged , , . Bookmark the permalink.

2 Responses to 70-451 – Designing a Database Solution – Designing a Database Model

  1. Eric says:

    Testing Comments

  2. Pingback: MCITP 70-451 Links Page | Destination: Change

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s