Some Discussion of HPM
PP187 - 210
Some Definitions. I want to take some time to do what will be some review about relationships. I am going to largely skip over the more abstract concepts of HPM that are mostly unique to their book and focus on topics that are more universal in their approach.
HPM defines a relation (not a relationship) as a named two-dimensional table of data consisting of
Everyone else I have ever read just calls these tables.
A primary key is an attribute or combination of attributes that uniquely identifies each row in a relation or table. Notice that in this definition a primary key does not need to be a single attribute, but can be obtained by combining a number of attributes.
A composite key is a primary key that consists of more than one attribute. Some examples of composite keys,
As you can see, composite keys can get quite complicated quite easily and this is why we have things like ISBN and social security numbers. After thinking about composite keys you likely realize why my definition in an earlier webpage excludes them.
A foreign key is an attribute in a table that serves as a primary key in another table (we are already excluding composite keys).
Even HPM discusses the importance of not having multi-valued attributes in databases. Though, my experience is while they are something to be avoided, they can sometimes be useful to save time and effort. My experience is this is particularly true if you never need to isolate single values of a multi-valued attribute or parse them as we've discussed in class.
For example, you can have a first_name, middle_name and last_name field in a table. These may not always be enough if you want to know someone's nickname, title or whether they are a junior or something like it. You might have these separate name fields, but also have a multi-valued field for something like credit_card_name to identify the name a person has on their credit card.
As another example, you might create a separate table that accumulates someone's interests linked back to who they are associated with by using a foreign key. But depending on how you are going to use these it may well be much easier to just have a multi-valued field called interests.
A More Developed Example. Now I want to present a fairly simple example motivating the use of primary keys and foreign keys in a database that has a few tables.
Consider a sales management database for a large pharmaceutical. In our example, we will focus on territories, the sales reps assigned to each territory and the doctors assigned to each sales rep. This will result in the following sorts of tables.
Obviously, you do not need to put the fk_ in a foreign key. Many developers choose to name the foreign key the same as the primary key it refers to, as does HPM.
Now we need to move onto another topic.
Integrity Constraints. Remember, we are always trying to make sure our business rules are followed when we develop our databases. Ensuring these rules are taken into account helps improve the integrity of the data.
HPM defines four major types of integrity.