When Sharon finishes, her Tutor entity looks like this:
Tutor PK TutorKey
TutorLastName TutorFirstName TutorPhone TutorEmail TutorHireDate
TutorCourse FIGURE 4-8 Tutor Entity
Version 1
Tutor PK TutorKey
TutorLastName TutorFirstName TutorPhone TutorEmail TutorHireDate TutorCourse1 TutorCourse2
TutorCourse3 FIGURE 4-9 Tutor Entity
Revision 1
Tutor PK TutorKey
TutorLastName TutorFirstName TutorPhone TutorEmail TutorHireDate TutorStatus
Course PK CourseKey
CourseName CourseDescription
FIGURE 4-10 Tutor and Course Entities
Sharon looks at the entity for a moment. Something about it bothers her. Then it hits her. What if a tutor tutors for more than one course? She could modify the entity to look like this:
But as she looks at it, she knows it is wrong. What if a tutor does only tutor for one class? That means two of the attributes would always be null. What if a tutor tutored for four classes? There would be no place to put the fourth one. And, she real-izes, if someone wanted to find out what tutors tutored a specific class, he or she would have to always query three separate columns to be sure. Also, if her memory serves her, the entity violates the first normal form. (Normal forms and normalization will be covered in detail in Chapter 5 .)
Sharon revises the Tutor entity one more time. She realizes that course doesn’t
belong to Tutor. It is an entity in itself, with its own attributes and its own key. She
cre-ates another entity called Course .
Sharon looks at the two entities, trying to determine what kind of relationship ex-ists between the two. It puzzles her for a moment and then she realizes it is a many-to-many relationship. One tutor can tutor for many-to-many courses, and each course can have many tutors. She smiles as she remembers her instructor in class going over just this situation again and again. “Whenever you have a many-to-many relationship, you must always make a linking table.”
Things You Should Know
Relationships
There are three kinds of relationships that can exist between entities:
• one to one
• one to many
• many to many One to One
In databases, a one-to-one relationship is rare, but it can be useful. A one-to-one relationship speci-fies that for each row in the primary entity, there can be one and no more than one related record in the secondary entity. In a one-to-one relationship, the primary key of the first entity is often the pri-mary key of the second entity.
In crow’s feet notation, one-to-one relationships can be represented in two ways:
FIGURE 4-11 Zero or one FIGURE 4-12 Exactly One
One use for this kind of relationship is to express a class/subclass relationship. Say a database is keeping a list of different resources. The resources can be in any of several different media, and the attributes to describe each media are significantly different. If you put all the attributes in one Resource entity, each entry will have several nulls for the attributes it doesn’t need. To solve this, you can break the Resource entity into several one-to–one relationships.
Resource PK ResourceKey
ResourceTitle ResourceType
Video PK,FK1 ResourceKey
VideoFormat VideoDateReleased VideoLength
Book PK,FK1 ResourceKey
BookPublisher BookYear BookCity BookISBN
Magazine PK,FK1 ResourceKey
MagazineName MagazineIssue MagazineVolume MagazinePage FIGURE 4-13 One-to-One
Relationships
The entities mentioned earlier don’t include all the relevant attributes, just enough to show the relationship. Notice that each entity has the same primary key. That means each resource will appear
once in the resource table and exactly once in one of the resource type tables. To get a clearer sense of this relationship, look at the following tables based on this design.
FIGURE 4-14 Resource Table
ResourceKey ResourceTitle ResourceType
235091 Database Programming with ADO Book
244088 PhotoShop Basics Video
200211 Data Binding with LINQ Magazine 202883 Relational Algebra Book
One-to-one relationships are sometimes also used as part of a security structure. A single entity may be broken into two entities. One will contain publicly viewable content and the sec-ond private content. For example, an employee’s information might be broken into two entities.
The first one contains nonsensitive content such as the employee’s name, department, business phone number, and position title. The second table contains sensitive material such as the employee’s social security number, home address, home phone, and salary information. There is a one-to-one relationship between the tables. Each employee has exactly one related record in the private table.
FIGURE 4-15 Book Table ResourceKey BookPublisher BookYear BookCity BookISBN
235091 Westland Press 2005 San Francisco 123-77-6576-X 202883 PL University Press 1998 Seattle 234-11-2345-0
FIGURE 4-16 Magazine Table ResourceKey MagazineName MagazineIssue MagazineVolume MagazinePage
200211 Visual Studio Magazine March 2008 3 76
Employee PK EmployeeKey
EmployeeDept
EmployeeBusinessPhone EmployeTitle
EmployeePrivate PK,FK1 EmployeeKey
EmployeeSocialSecurity EmployeeAddress
EmployeeSalary FIGURE 4-17 One to One for Security
It should be noted, this is not necessarily the best way to deal with security issues. There are many ways to allow the public aspects of the Employee entity to be accessed while protecting the private information. Creating a view or using a stored procedure (see Chapter 7) to control which columns a user can access is generally a better strategy.
One to Many
Most of the entities in any relational database will have a many relationship. A one-to-many relationship means that for each record in the primary entity there can be one-to-many associated records in the secondary or child entity. There are two crow’s feet symbols for one-to-many relationships:
FIGURE 4-18 Zero or More FIGURE 4-19 At Least One or More
For an example of a one-to-many relationship, consider the relationship between a department in a business and its employees. Each department can contain zero or more employees. Each employee belongs to one department.
You need to enter the department information only once in the Department table. You use the primary key to link the table to a child table by repeating it in that table as a foreign key. The foreign key can repeat as often as needed in the child table.
Caution
It is important that you do not create a “cross relationship.”
There is a temptation to think that because a department contains employees, the Department entity should contain a foreign key for employee.
Doing this will create an impossible situation. In effect, a department will only be able to contain a single employee. The second employee will create a conflict with the DepartmentKey, which cannot repeat. This is a fairly common error among novice designers.
It often isn’t discovered until the attempt to enter data into the tables fails.
Employee PK EmployeeKey
EmployeeLastName EmployeeFirstName FK1 DepartmentKey Department
PK DepartmentKey
DepartmentName DepartmentPhone DepartmentRoom FIGURE 4-20 One to Many
Many to Many
Many–to-many relationships are common, and they are legitimate relationships in logical terms, but no database can implement them. A many-to-many relationship means that each record in the pri-mary entity can have many related records in a second entity and each record in the second entity can have many related records in the primary entity.
The symbol for a many-to-many relationship has a crow’s foot on both sides of the relation-ship, as shown in the following figure:
FIGURE 4-22 Employee Table EmployeeKey EmployeeLastName EmployeeFirstName DepartmentKey
FB2001D Collins Richard IT
BN2004N Faulkner Leonore IT
NC2004M Brown Carol ACC
LL2006O Anderson Thomas IT
FIGURE 4-21 Department Table DepartmentKey DepartmentName DepartmentPhone DepartmentRoom
ACC Accounting (206)555-1234 SB201
IT Information Technology (206)555-2468 NB100
Employee PK EmployeeKey
EmployeeLastName EmployeeFirstName FK1 DepartmentKey Department
PK DepartmentKey DepartmentName DepartmentPhone DepartmentRoom FK1 EmployeeKey
FIGURE 4-23 Cross-Relationship Error
FIGURE 4-24 Many to Many
Visio doesn’t contain a symbol for this relationship.
For an example, consider the relationship between Subscribers and an entity designed to store a list of various magazines. Each customer can subscribe to many magazines, and each magazine can be subscribed to by many customers. That creates a many-to-many relationship.
Subscriber PK SubscriberKey
SubscriberLastName SubscriberFirstName SubscriberAddress SubscriberCity SubscriberState SubscriberPostalCode Magazine
PK MagazineKey MagazineName MagazinePrice
FIGURE 4-25 Many-to-Many Relationship
In any RDBMS, a many-to-many relationship must be resolved into two one-to-many relation-ships. This is done by creating a linking entity. In this case, the Magazine and the Subscriber entities are linked by a Subscription entity. A subscriber subscribes to one or more magazines. A magazine can be subscribed to by zero-to-many subscribers. Often, as in this case, creating the linking entity reveals a forgotten or undiscovered entity, Subscription is more than a linking entity. It is a legitimate entity with attributes of its own.
Magazine PK MagazineKey
MagazineName MagazinePrice
Subscriber PK SubscriberKey
SubscriberLastName SubscriberFirstName SubscriberAddress SubscriberCity SubscriberState SubscriberPostalCode Subscription
PK SubscriptionKey SubscriptionStartDate FK1 MagazineKey FK2 SubscriberKey
FIGURE 4-26 Linking Table
The following tables show how these entities would be translated into a database. These are, of course, much simplified. A real database would contain many more columns of essential informa-tion such as the subscripinforma-tion length, the magazine type (e.g., weekly and quarterly), the magazine publisher information, and so on. Also for the subscribers, it would be necessary to distinguish between the billing address and the shipping address, since they are not necessarily the same.
FIGURE 4-27 Magazine Table MagazineKey MagazineName MagazinePrice
TM2K1 Time 35.50
NW2K1 Newsweek 36.40
Sometimes, however, the linking entity only serves to resolve the many-to-many relationship.
Consider the relationship between authors and books. Each book can have several authors, and each author can author several books. This relationship can be resolved with a linking table as in the fol-lowing figure:
FIGURE 4-30 Linking Table Two FIGURE 4-28 Subscriber Table
SubscriberKey Subscriber LastName
FIGURE 4-29 Subscription Table
SubscriptionKey MagazineKey SubscriberKey SubscriptionStartDate
1004 TM2K1 4333 1/15/2009
1005 NW2K1 4333 1/15/2009
1006 NW2K1 4231 2/1/2009
1007 TM2K1 5344 2/15/2009
It is not uncommon for a linking entity to have a composite key made up of the foreign keys from the two tables whose relationship it resolves. Another note: You may have noticed in the earlier diagrams that most relationships are represented by dashed lines. The preceding relationships and the one-to-one relationships are represented as solid lines. Visio distinguishes between identifying and nonidentifying relationships. An identifying relationship is one where the foreign key in the child table is also a part of the primary key of that child table. For instance, AuthorKey is both a foreign key and part of the composite primary key in the entity BookAuthor. A nonidentifying relationship is one in which the foreign key is not a part of the primary key of the child table.
Following are some examples of how these entities would be translated into tables in a data-base. Notice how the Head First Object Oriented Analysis and Design book has three authors.
COMPOSITE KEY
A key that consists of more than one column.
LINKING ENTITY
An entity which resolves a many-to-many relationship into two one-to-many relationships.
SURROGATE KEY
A random or arbitrary key often generated by just incrementing numbers.
FIGURE 4-31 Book Table
BookKeyISBN BookTitle BookPublisher BookPublisherYear 0-07-222513-0 Java 2 Beginners Guide Oracle Press 2002
FIGURE 4-32 Author Table AuthorKey AuthorLastName AuthorFirstName
HSCHLT Schildt Herbert
SMITHN Mithen Steven
BMCLAU McLaughlin Brett
GPOLLIC Pollice Gary
DWEST West David
FIGURE 4-33 Linking Table BookAuthor
BookKeyISBN AuthorKey 0-07-222513-0 HSCHLT 0674019999-1 SMITHN 0-596-00867-8 BMCLAU 0-596-00867-8 GPOLLIC 0-596-00867-8 DWEST