One of the biggest issues beginning SQL writers have is being able to write queries that use more than one table. In this series of articles we are going to show you how to write a query that combines, or joins, data from more than one table. Once you have gone through the examples you will understand how to write the basic commands to make this happen and why data is separated in the first place.
This first article introduces the concept of joining tables. The focus is going to be more on the type of joins, not necessarily their syntax. The later articles focus on the various types of joins. Through the narrative and examples you’ll become very comfortable with each one.
In my prior articles you learned about the need to normalize to make it easier to maintain the data. Though this makes it easier to maintain and update the data, it makes it very inconvenient to view and report information. Typically the information you need to see has to be cross referenced across several tables for you to see the full picture. [click to continue…]
This is the fourth in a series of posts teaching normalization.
The third post focused on the second normal form, its definition, and examples to hammer it home.
Once a table is in second normal form, we are guaranteed that every column is dependent on the primary key, or as I like to say, the table serves a single purpose. But what about relationships among the columns? Could there be dependencies between columns that could cause an inconsistency?
A table containing both columns for an employee’s age and birth date is spelling trouble, there lurks an opportunity for a data inconsistency!
How are these addressed? By the third normal form. [click to continue…]
I recently had the opportunity to ask Brian Larson some questions regarding Business Intelligence. For those new to the field, Business Intelligence, or BI for short, is an rapidly expanding field within the database realm.
BI brings with it a different way of thinking! We all struggled to understand normalization and create efficient transactional data models. BI turns this thinking on its head. With BI the emphasis is placed on fast reporting. This mean pre-calculating values and denormalizing data.
BI tools can be as simple as using add-ins in Excel to as complex as stand-along database products.
I got a lot out of hearing what Brian had to say, and you share the feeling. So with out further delay, enjoy the interview.
[click to continue…]
This is the third in a series of posts teaching normalization.
The second post focused on the first normal form, its definition, and examples to hammer it home.
Now it is time to take a look at the second normal form. I like to think the reason we place tables in 2nd normal form is to narrow them to a single purpose. Doing so bring’s clarity to the database design, makes it easier for us to describe and use a table, and tends to eliminate modification anomalies.
This stems from the primary key identifying the main topic at hand, such as identifying buildings, employees, or classes, and the columns, serving to add meaning through descriptive attributes.
An EmployeeID isn’t much on its own, but add a name, height, hair color and age, and now you’re starting to describe a real person.
So what is the definition of 2nd normal form?
[click to continue…]
This is the second in a series of posts teaching normalization.
The first post introduced database normalization, its importance, and the types of issues it solves.
In this article we’ll explore the first normal form. For the examples, we’ll use the Sales Staff Information shown below as a starting point. As we pointed out in the last post’s modification anomalies section, there are several issues to keeping the information in this form. By normalizing the data you see we’ll eliminate duplicate data as well as modification anomalies.
1NF – First Normal Form Definition
The first steps to making a proper SQL table is to ensure the information is in first normal form. Once a table is in first normal form it is easier to search, filter, and sort the information. The rules to satisfy 1st normal form are:
- That the data is in a database table. The table stores information in rows and columns where one or more columns, called the primary key, uniquely identify each row.
- Each column contains atomic values, and there are not repeating groups of columns.
Tables in first normal form cannot contain sub columns. That is, if you are listing several cities, you cannot list them in one column and separate them with a semi-colon. When a value is atomic, the values cannot be further subdivided. For example, the value “Chicago” is atomic; whereas “Chicago; Los Angeles; New York” is not. Related to this requirement is the concept that a table should not contain repeating groups of columns such as Customer1Name, Customer2Name, and Customer3Name. [click to continue…]