Hour 6

Defining Entities and Relationships

What You’ll Learn in This Hour:

• ▶   Gain a deeper understanding of entities and relationships

• ▶   Understand how to model referential integrity

• ▶   Define parent and child relationships using primary and foreign keys

• ▶   Walk through defining entities and relationships with the BIRDS database

• ▶   Walk through the process of adding entities to the BIRDS database through hands-on exercises for modeling your own entities and relationships that will be integrated with the BIRDS database

During this hour, you learn about one of the most important phases of database design: defining entities and relationships. The entities and relationships that you define in this book are based on the groups of data and lists of fields that you defined in Hour 5, “Understanding the Basics of Relational (SQL) Database Design.” In this hour, you walk through some of the design and modeling for the sample BIRDS database, think about how you might add components for bird rescues to the existing BIRDS database, and begin to model the data you defined in Hour 5. This is a crucial part of database design, and you must understand it so that you can fully take advantage of the SQL language. As you define entities and relationships in this hour, you’ll gain a better understanding of the cornerstone of the relational database, referential integrity. As always, any hands-on exercises in this hour are immediately applicable to the real world.

Creating a Data Model Based on Your Data

Before you start modeling entities and relationships, let’s dive a little deeper into data relationships and referential integrity. In the following sections, you look at the different types of relationships in a relational database and walk through examples based on the sample BIRDS database.

Data

As you know by now, data is at the heart of every organization and every individual. The sample database for this book is the BIRDS database. Output from the existing tables has been provided to show the actual data that currently exists in this database. Not only do you look deeper into the data itself and see how the different entities of the BIRDS database are related to one another, but you also get examples in this hour of how you might model data that can be added to the existing database. After looking at some examples of data about bird rescues that could be integrated into the BIRDS database, you move on to model data about photographers that can be integrated into the same database.

Note

Defining Data

In Hour 8, “Defining Data Structures,” you learn how to define data structures using SQL data types. The most common data types are character, numeric, date, and time. These data types are initially defined using the SQL command CREATE TABLE.

Relationships

A relational database is all about relationships between data sets within a database. During the database design phase, you identify entities, or groups of data, and specify the relationships between those entities. For example, some entities are parent entities; some are child entities. These entities have relationships with one another and eventually define the database that will be created. Conceptually, you conceive the database and then use SQL to physically create the database and manage the data within that database. Different types of relationships are covered later in this hour.

Any relationship between entities is a two-way relationship and can be interpreted from either entity’s side. Consider the following example between birds and the food items they eat (see Figure 6.1).

The two-way relationship between birds and the food items they eat. Birds eat food and food is eaten by birds. The text Bird and Food are given in a box. The term eat is given above the line to its left adjacent to the text Bird and the phrase is eaten by is given below the line to its right adjacent to the text Food.

FIGURE 6.1
Reading a relationship between entities

• ▶   Birds eat food.

• ▶   Food is eaten by birds.

Other examples from the BIRDS database include the following:

• ▶   Birds migrate to various locations, and each migration location has birds that migrate there.

• ▶   Birds have nicknames, and nicknames are given to birds.

• ▶   Birds have photographs taken of them, and photographs are taken of birds.

• ▶   Birds build nests, and nests are built by birds.

Referential Integrity

Referential integrity is a term used to describe parent and child relationships between entities within a database. Some entities that eventually become tables are parent entities; others are children. Children entities refer to parent entities: They depend on the data that already exists within parent entities. Of course, entities do not contain data; they are conceptual. But when you create the physical tables in the database, any data inserted into the database must comply with any rules that you have set forth within the database, according to the rules of the relational database implementation you are using and the constraints you have defined (such as primary key and foreign key constraints). The relational database is all about referential integrity.

Take a minute to study the parent and child relationship modeled in Figure 6.2.

A parent and child relationship. The parent entity is about birds and the child entity is about nicknames. Birds are given three nicknames represented by three boxes one behind the other. Each nickname is associated with only a single bird. The text Birds and Nicknames are given in a box. The phrase are given is given above the line to its left adjacent to the text Bird and the phrase is are given to is given below the line to its right adjacent to the text Nicknames.

FIGURE 6.2
Parent and child relationship

All of the following information can be derived from the simple ERD shown in Figure 6.2.

• ▶   Birds are given one or more nicknames.

• ▶   A nickname may be given to a specific bird.

• ▶   The parent entity is about birds.

• ▶   The child entity is about nicknames.

• ▶   A bird may be given many nicknames.

• ▶   In this specific example, each nickname is associated with only a single bird.

• ▶   Because BIRDS is the parent entity, the NICKNAMES entity has dependencies on BIRDS.

• ▶   This means that you cannot have a nickname that is not associated with a specific bird.

• ▶   This also means that you cannot remove a bird if nicknames are associated with that specific bird.

• ▶   To add nicknames to the database that will eventually be created from this design, a corresponding entry for a bird must first exist.

• ▶   To remove a bird from the database that will eventually be created from this design, any corresponding nicknames that relate to that specific bird must first be deleted.

Note

Using SQL to Employ Referential Integrity

In Hour 9, “Creating and Managing Database Objects,” you learn how to use referential integrity in a physical database by using SQL to create tables as well as constraints for primary and foreign keys. For example, the SQL commands CREATE TABLE and ALTER TABLE provide a simple mechanism for defining and managing database constraints to enforce referential integrity.

Figure 6.3 reveals more detail, showing sample data that might exist in the BIRDS table derived from this database design. The following section explains this in more detail, but you should easily see that BIRD_ID is a primary key in the BIRDS table, which uniquely identifies every row of data, or specific type of bird, in the BIRDS table.

A BIRDS table. The two column heads are labeled as BIRD_ID and Bird_Name. The row entries for the two columns are as follows: Row 1. 1, Great Blue Heron; Row 2. 2, Mallard; Row 3. 3; Common Loon; Row 4. 4; Bald Eagle. BIRD_ID is a primary key.

FIGURE 6.3
Visual of data in the BIRDS table

Figure 6.4 expands on the previous example, showing more sample data that might exist in tables derived from this database design that are related to one another. The following sections also explain this in more detail, but you can see both a parent table and a child table. The child table contains the nicknames and relates back to the BIRDS table through a common column called BIRD_ID. Using a basic manual process, you can determine the nicknames of each bird in this example. Again, you are working with these basic concepts so that you can better understand how SQL works to get useful data from the database.

A parent table and a child table. The parent table IDs are Bird_Id and Bird_name. The row entries under IDs Bird_Id and Bird_name are as follows: 1, Great Blue Heron; 2, Mallard; 3, Common Loon; 4, Bald Eagle. The parent table Bird_Id is the primary key. The child table IDs are Bird_Id and Nickname. The row entries under IDs Bird_Id and Bird_name are as follows: 1, Big Cranky; 1, Blue Crane; 2, Green Head. 2, Green Cap. The child table Bird_Id is the Foreign key. The nicknames under the child table relates back to the BIRDS table through a common column called BIRD_ID. Birds may have one or more nicknames. The bird Great Blue Heron has two nicknames Big Cranky and Blue Crane.

FIGURE 6.4
Data in parent and child relationships

Defining Relationships

In this section, you examine the basic relationships that can exist within a relational database. The four most common relationships in a relational database follow:

• ▶   One-to-one relationship

• ▶   One-to-many relationship

• ▶   Many-to-many relationship

• ▶   Recursive relationship

When drawing an ERD, the symbols in Figure 6.5 are commonly used to depict entities and their relationships.

Common Entity Relationship diagram symbols to depict entities and relationships. The Entity (Table) is represented by a rectangle. The One-to-one Relationship is represented by two rectangles connected by a line in the middle. A note to the left of the diagram reads as, One-to-one Relationship - A table has a row of data that is related to only one other row of data in another table through a common key on a field (column). The One-to-many Relationship is represented by two rectangles connected by a line in the middle with one end of the line branching out into three lines. A note to the left of the diagram reads as, One-to-many Relationship - A table has a row of data that may be related to one or more rows of data in another table through a common key on a field (column). The Many-to-many Relationship is represented by two rectangles connected by a line in the middle with both ends of the line branching out into three lines. A note to the left of the diagram reads as, Many-to-many Relationship - A table has multiple rows of data that may be related to multiple other rows of data in another table through a common key on a field (column). The Recursive Relationship is represented by a rectangle with a curved arrow running from its base to the side. A note to the left of the diagram reads as, Recursive Relationship - A table has a row of data that may be related to another row of data in the same table through a common key on a field (column).

FIGURE 6.5
Common ERD symbols to depict entities and relationships

One-to-One

In a one-to-one relationship, one record in a table is related to only one record in another table. For example, each employee typically has only one pay record. In the bird example, each bird might have only one nickname.

Figure 6.6 shows a one-to-one relationship. In this example, every bird is assumed to have only one nickname; conversely, each nickname is associated with only one type of bird.

FIGURE 6.6
A one-to-one relationship in the BIRDS database

One-to-Many

In a one-to-many relationship, one record in a table can be related to one or more records in another table. For instance, each employee might have multiple dependents, or each bird might eat many different types of food.

Figure 6.7 shows two relationships that might exist in the BIRDS database. The first example shows a relationship between BIRDS and NICKNAMES. Unlike in the previous example, each bird can have multiple nicknames. However, each nickname can be related to only a specific type of bird. The second example shows a relationship between RESCUES and STAFF. Each bird rescue can have multiple staff members that work within that organization. This example also indicates that many staff members might work for a single bird rescue, but they work for only one bird rescue.

A one-to-many relationship in the BIRDS database. There are two relationship examples in the BIRDS database. The first example shows the relationship between BIRDS and NICKNAMES. Each bird can have multiple nicknames. The second example shows a relationship between RESCUES and STAFF. Multiple staff members might work for a single bird rescue.

FIGURE 6.7
A one-to-many relationship in the BIRDS database

Many-to-Many

In a many-to-many relationship, many records in a table can be related to many records in another table. For instance, in the case of birds and the food they eat, a bird might eat many different types of food and each type of food might be eaten by many different types of birds.

Figure 6.8 shows two many-to-many relationships that could exist in the BIRDS database. For the first example, suppose that staff members can work or volunteer at multiple bird rescues. Remember that staff members can be anybody—individuals, photographers, full-time staff, part-time staff, and so on. Thus, that scenario is conceivable. This is an example of a many-to-many relationship within a database.

A many-to-many relationships in the BIRDS database. There are two many-to-many relationship examples in the BIRDS database. The first example shows that staff members can work or volunteer at multiple bird rescues. The second example illustrates birds and the food they eat. A bird might eat many different types of food and each specific type of food might be eaten by many different types of birds.

FIGURE 6.8
Examples of many-to-many relationships in the BIRDS database

The second example in this figure illustrates birds and the food they eat. A bird might eat many different types of food, such as worms, berries, and fish. Conversely, each specific type of food might be eaten by many different types of birds. These are two simple examples of a many-to-many relationship. In Hour 7, “Normalizing Your Database,” you see that, in a relational database, many-to-many relationships are typically not a best practice for design. (However, they are a stepping stone to a strong relational database design; you learn later how many-to-many relationships can be resolved to eliminate redundant data in the database, thereby preserving the utmost integrity of data within the database.)

Recursive

In a recursive relationship, an attribute within one entity is related to an attribute within the same entity. Basically, an entity serves as both a parent entity and a child entity in a relationship. An attribute, or an eventual column in a table, depends on a column in the same table. You can place rules on attributes or columns within a table that other columns in a table must adhere to. (Later in this book, you use a self join to compare data to other data within the same table.) For example, each employee in an employees table might be managed by another employee in the same table.

Figure 6.9 shows two common examples of how a recursive relationship might look in a relational database. Remember that a recursive relationship involves data in a table that is related to data in the same table. In the first example in this figure, the recursive relationship involves staff members. For example, staff members might be managed by other staff members or, depending on which way you look at the relationship, staff members might manage other staff members. In the second example in this figure, photographers might be mentored by other photographers, or photographers might mentor other photographers that exist in the same entity.

Examples of two recursive relationships. The first example shows the recursive relationship involving staff members. staff members might be managed by other staff members or staff members might manage other staff members The second example shows photographers might be mentored by other photographers or photographers might mentor other photographers existing in the same entity.

FIGURE 6.9
Examples of recursive relationships

Employing Referential Integrity

Next we look at an example of employee referential integrity. You have already seen referential integrity defined as it pertains to parent and child relationships in a relational database. Now you look at the BIRDS database and some objects that you might add for bird rescues. In the exercises at the end of this hour, you can add objects of your own for photographers and use referential integrity within those objects.

Referential integrity is defined in a relational database using the following:

• ▶   Primary keys

• ▶   Foreign keys

Note

Referential Integrity During Logical Database Design

At this point, the use of referential integrity concepts is still part of the logical process. Sometimes referential integrity is employed using primary key and foreign key constraints in the logical design process; other times, referential integrity is not fully employed until the physical database is created. These constraints are designated in a logical data model but are ultimately defined by constraints using SQL commands when you create the physical database.

Identifying Primary Keys

A primary key is simply a constraint placed on a column in a table that identifies each data value in that column, or field, as unique. A primary key designates a parent value in a parent/child relationship. A primary key is a required attribute within an entity.

Figure 6.4 shows the BIRD_ID entry in the BIRDS table as the primary key. This primary key ensures that every entry or row of data in the BIRDS table is a unique entry. A primary key not only identifies uniqueness, but also assumes that it is a parent record that can have child records in another table which are dependent.

In looking at the BIRDS database as a whole (at least, to this point), the primary keys are as follows:

• ▶   BIRD_ID in the BIRDS table

• ▶   FOOD_ID in the FOOD table

• ▶   MIGRATION_ID in the MIGRATION table

• ▶   NEST_ID in the NESTS table

• ▶   PHOTO_ID in the PHOTOS table

• ▶   LOCATION_ID in the LOCATIONS table

In the BIRDS database, the composite primary keys are as follows:

• ▶   BIRD_ID and FOOD_ID (combined) in the BIRDS_FOOD table

• ▶   BIRD_ID and MIGRATION_ID (combined) in the BIRDS_MIGRATION table

• ▶   BIRD_ID and NEST_ID (combined) in the BIRDS_NESTS tables

• ▶   BIRD_ID and NICKNAME (combined) in the NICKNAMES table

Note

Composite Primary Keys

A composite primary key is a primary key in a table that consists of more than one column. The combination of these columns must be unique for every row of data in the table. Future hours explain this concept in more detail.

Identifying Foreign Keys

A foreign key is a constraint placed on a column in a table—or, in this hour, an attribute within an entity—that depends on a data value in another entity. For example, a foreign key references a primary key in another entity. The foreign key designates the child record in a parent/child relationship. A foreign key can also be defined as a primary key and can be either a required attribute in an entity or optional.

Referring to Figure 6.4, you can see two tables there: the BIRDS table and the NICKNAMES table. BIRD_ID in the BIRDS table is a primary key, or a parent record. In the NICKNAMES table, BIRD_ID is a foreign key, or child record, that refers back to, or is dependent upon, the BIRD_ID in the BIRDS table.

In looking at the BIRDS database as a whole, the foreign keys are as follows:

• ▶   BIRD_ID in the BIRDS_FOOD table not only is a part of a composite primary key; it also is a foreign key representing a child record that references the BIRD_ID in the BIRDS table, which is a primary key that represents a parent record.

• ▶   BIRD_ID in the BIRDS_MIGRATION table is a foreign key representing a child record that references BIRD_ID in the BIRDS table, which is a primary key that represents a parent record.

• ▶   BIRD_ID in the BIRDS_NESTS table is a foreign key representing a child record that references BIRD_ID in the BIRDS table, which is a primary key that represents a parent record.

• ▶   BIRD_ID in the NICKNAMES table is a foreign key representing a child record that references BIRD_ID in the BIRDS table, which is a primary key that represents a parent record.

• ▶   BIRD_ID in the PHOTOS table is a foreign key representing a child record that references BIRD_ID in the BIRDS table, which is a primary key that represents a parent record.

• ▶   FOOD_ID in the BIRDS_FOOD table is a foreign key representing a child record that references FOOD_ID in the FOOD table, which is a primary key that represents a parent record.

• ▶   MIGRATION_ID in the BIRDS_MIGRATION table is a foreign key representing a child record that references MIGRATION_ID in the MIGRATION table, which is a primary key that represents a parent record.

• ▶   NEST_ID in the BIRDS_NESTS table is a foreign key representing a child record that references NEST_ID in the NESTS table, which is a primary key that represents a parent record.

• ▶   PHOTO_LOCATION_ID in the PHOTOS table is a foreign key representing a child record that references LOCATION_ID in the LOCATIONS table, which is a primary key that represents a parent record.

Creating an Entity Relationship

In this section, you create two basic entity relationship diagrams. Figure 6.10 shows the first one, an entity called RESCUES that represents the entire data set related to the bird rescues that will eventually be integrated into the BIRDS database. In this example, you can see that bird rescue information is primarily related to the BIRDS table in the BIRDS database. This does not mean that rescue information will not be related to any other information, such as locations and photographers. This figure is only a starting point to illustrate the basic relationship that might be visualized at this point.

Basic Entity Relationship diagram showing how bird rescues might be integrated into the BIRDS database. The basic entities in the BIRDS database are: PHOTOS, LOCATIONS, FOOD, BIRDS, NICKNAMES, NESTS, and MIGRATION. The data fields PHOTOS, BIRDS and MIGRATION are given vertically. LOCATIONS is given to the right of PHOTOS. NICKNAMES is given to the right of BIRDS and FOOD is given to the left of BIRDS. NESTS branches out from BIRDS to the left of MIGRATION. An entity called RESCUES branching out from BIRDS data field represents the entire data set related to the bird rescues and will be integrated into the existing BIRDS database.

FIGURE 6.10
Basic ERD showing how bird rescues might be integrated into the BIRDS database

In Figure 6.11, you can see that the data set for bird rescues has been expanded upon. It is ultimately linked to the BIRDS database through the BIRDS table. Take a few minutes to study this figure and understand the relationships. Consider the data of the bird rescues and, beyond that, think about how the new data might be integrated and used with the existing BIRDS database.

Basic Entity Relationship diagram showing the high-level entities and relationship for bird rescue data. The basic entities in the BIRDS database are: PHOTOS, LOCATIONS, FOOD, BIRDS, NICKNAMES, NESTS, and MIGRATION. The data fields PHOTOS, BIRDS and MIGRATION are given vertically. LOCATIONS is given to the right of PHOTOS. NICKNAMES is given to the right of BIRDS and FOOD is given to the left of BIRDS. NESTS branches out from BIRDS to the left of MIGRATION. An entity called RESCUES branching out from BIRDS data field represents the entire data set related to the bird rescues and will be integrated into the existing BIRDS database. RESCUES table is the most centralized table within the new data set. The RESCUES table is also the main table that relates back to the BIRDS database through the BIRDS table. Staff members within Rescues consists of sponsors, online sites, events, publications, staff, and facilities.

FIGURE 6.11
Basic ERD showing the high-level entities and relationship for bird rescue data

Let’s look at the basic relationships that have been modeled in the ERD in Figure 6.11. The new data is about bird rescues, so the RESCUES table is probably the most centralized table within that new data set. The RESCUES table is also the main table that relates back to the BIRDS database through the BIRDS table. As stated earlier, this does not mean that other entities will not be related to other entities within the BIRDS database. For example, sponsors might have favorite birds that influence the rescues that they support. Various facilities might be most appropriate for different types of birds, such as larger raptors that need more space and eat different types of foods. Staff members within rescues could consist of sponsors, photographers, or paid personnel. Keep in mind, then, that the basic relationships modeled here could differ from what you came up with.

• ▶   The RESCUES entity is the parent entity.

• ▶   A rescue might house many different types of birds, and many different types of birds might be housed within each rescue.

• ▶   A rescue might have multiple facilities.

• ▶   Each rescue might have multiple staff members, and each staff member might also be a staff member at another rescue.

• ▶   A rescue might have multiple publications.

• ▶   Each rescue might host multiple events.

• ▶   Each rescue might have multiple sites, such as websites and various social media.

• ▶   Each rescue might have multiple sponsors or donors, and a sponsor might donate to many different rescues.

Summary

In this hour, you looked at entities and their relationships with one another. Data is initially modeled as entities. These entities eventually become tables using SQL commands as they are implemented into a physical database design. Attributes are implemented as columns in tables into a physical database design, and relationships are essentially defined as constraints using SQL in a physical database. Relationships are defined using SQL by constraints. The primary relationships in a relational database are based on referential integrity and are referred to as primary keys and foreign keys. Primary keys are parent values in a relational database. Foreign keys are child values in a relational database that are dependent upon primary keys, or parent values. The relationship between the primary key and the foreign key is the main concept behind a relational database.

You also walked through the thought process of modeling a basic database, and you analyzed some basic information so you could model your own entities that will eventually evolve into physical database objects that you can easily integrate into the sample BIRDS database. As you progress through the next few hours, you will see how SQL helps you both understand and implement these design concepts, ultimately leading to a well-designed database that unlocks the robustness of the standard SQL language. You can then use SQL to obtain critical information from a database that can help you and your organization succeed in today’s competitive environment.

Q&A

Q. Can a primary key have multiple foreign keys associated with it?

A. Yes, a primary key can have multiple foreign keys that reference its data value.

Q. Do other constraints protect the integrity of data?

A. Yes, you can apply various other constraints with SQL to protect your data in addition to primary and foreign keys. Some of these constraints are covered in future hours, and other options might be available with your specific implementation of SQL.

Workshop

The following workshop consists of a series of quiz questions and practical exercises. The quiz questions are designed to test your overall understanding of the current material. The practical exercises give you the opportunity to apply the concepts discussed during the current hour, as well as build on the knowledge you acquired in previous hours of study. Be sure to complete the quiz questions and exercises before continuing to the next hour. Refer to Appendix C, “Answers to Quizzes and Exercises,” for answers.

Quiz

1. 1. What are the four basic types of relationships between entities in a relational database?

2. 2. In which relationship does an attribute relate to another attribute in the same table?

3. 3. What constraints, or keys, are used to enforce referential integrity in a relational database?

4. 4. If a primary key represents a parent record, what represents a child record in a relational database?

Exercises

1. 1. Suppose that you came up with a group of data and lists of fields similar to the following for the information on photographers that will be integrated into the BIRDS database. This baseline data is provided for your convenience so that you can build on it during the next few hours. However, feel free to use any list that you already have derived, or you can combine your list of data with this example. Keep in mind that the solutions that you come up with might vary from the example solutions provided. You will also find throughout this book that although many of your solutions might vary from the book solutions, they still yield the same results, similar results, or even better results, based on how you interpret the data. Also keep in mind that the following data is merely a subset of the data that can be derived from the description provided in Hour 5. Figure 6.12 hints at where you might find a recursive relationship. Complete the other relationships as you see fit.

   

   The Basic Entity Relationship diagram for photographers is shown. The information on photographers is integrated into the BIRDS database. The possible relationships shown in this diagram are: many-to-many relationship between Photographers, Cameras, and Lenses; one-to-one relationship between Photographers and Styles; one-to-one relationship between Photographers and Birds; and one-to-one relationship Birds and Photos. The photographers might be protégés of other photographers or photographers might mentor other photographers existing in the same entity illustrating a recursive relationship.

   FIGURE 6.12
   Example basic ERD for photographers

   Sample limited lists of fields for photographer data might include the following:

   Click here to view code image

   PHOTOGRAPHERS
   
     Photographer_Id
     Photographer_Name
     Photographer_Contact_Info
     Education
     Website
     Mentor
   
   
   STYLES
   
     Style_Id
     Style
   
   
   CAMERAS
   
     Camera_Id
     Camera_Make
     Camera_Model
     Sensor_Type
     Megapixels
     Frames_Per_Second
     ISO_Range
     Cost
   
   
   LENSES
   
     Lens_Id
     Lens_Make
     Lens_Type
     Aperature_Range
     Cost

2. 2. List some possible relationships for the information provided for photographers that will be integrated into the BIRDS database.

3. 3. List the attributes that you anticipate will comprise the primary keys for the entities that you have defined for the photographers.

4. 4. List the attributes that you anticipate will comprise the foreign keys for the entities that you have defined for the photographers.

5. 5. Draw a basic ERD depicting the entities and the relationships between those entities that you envision at this point for the photographer data.

6. 6. Using words, describe the two-way relationships between your entities. The types of relationships should already be represented on your ERD from question 5 using the symbols introduced during this hour.

7. 7. Refer to Figure 6.4 to answer the remaining questions in this exercise. Some of these questions might seem too simple, but these are the same types of questions you will be asking of the database using SQL commands. Remember that a major goal of this book is to get you to think the way that SQL does.

   1. What are the nicknames of the Great Blue Heron?

   2. What are the nicknames of the Mallard?

   3. Which birds have the word green in their nickname?

   4. Which birds have a nickname that starts with the letter B?

   5. Which birds do not have a nickname listed in the example?

   6. How many unique birds are listed in this example?

   7. What is the average number of nicknames per bird in this example?

   8. Which birds do not have a nickname listed in the example?

   9. Can any birds be deleted from the BIRDS table without first having to delete nicknames from the NICKNAMES table?

   10. Which birds have child records in the NICKNAMES table?

   11. Does any duplicate data exist in either table besides the BIRD_ID itself?