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

• a set of named columns that correspond to attributes
• an arbitrary set of unnamed rows that correspond to records

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,

• try to uniquely identify a book by its title and you are likely to get some duplicates, so you add in the author, so you then add in the edition and/or publication date - even then this might not be enough - but it will very likely uniquely identify a book
  • how does this approach compare to using an ISBN number?
• try to uniquely identify a person by their complete name and you will not be very successful, so you add their address - but then maybe you have a child named after their father so adding in the phone number doesn't really help, so you add in their date of birth
  • how does this approach compare to using a social security number?

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.

territories

physicians

• So there is a one-to-many relationship between territories and sales_reps.  The foreign key that coordinates this is fk_id_territory.

 

• Then there is a one-to-many relationship between sales_reps and physicians.  The foreign key that coordinates this is fk_id_rep.

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.

• Domain Constraints - a domain is the set of values that may be assigned to an attribute.
  • all of the entries in a column of a table must be taken from the same domain.
  • a domain definition usually consist of the following
    • domain name
    • meaning
    • data type
    • size or length allowed
    • allowable values or allowable range
  • some examples
    • phone_number - 10 digit number of the form xxx-xxx-xxxx
    • state - 2 capital letter abbreviations
      • should actually list possibilities
        • AK
        • AL
    • price - currency with two digits
• Entity Integrity - ensures that every table has a primary key and that the values are all valid and non-null
• Referential Integrity - each foreign key value in a related table must match a primary key entry in another related table or the foreign key value must be null.
  • will usually not be null unless the relationship is optional
• Action Assertions - will be discussed in much greater depth in later chapters
  • for example - an action assertion - a person may purchase a playoff ticket only if they are a season ticket holder