Designing a Database and Relational Schema

(a) Find the names of pets owned by anyone whose first name is John.
Note: Use the SELECT command.

(b) As there is a many-to-many relationship (between Pet and Toy) that cannot be expressed in a Relational database, an additional table (PetToy) was introduced that links the two tables. Thus, the final design is:

Priscilla is interested in providing additional services to her customers, as well as having a better control on her toys and food suppliers. For additional services, she wants to add to the database the names of veterinary doctors in the neighborhood. Each customer could choose the vet he or she wants to work with, and that vet will treat all the customers’ pets.

Priscilla also likes to add supplier data to her database. Each PetFood item is supplied by just one FoodSupplier (but a supplier can supply many food items). A Toy can be supplied by multiple ToySuppliers. A ToySupplier can supply many Toys.Extend the design to include Veterinarians, PetSuppliers, and ToySuppliers.If you have the MS PowerPoint application, you can use the

Pets MS PowerPoint Presentation

and add your constructs there.

PetToy
PetOwner
Pet
PetFood
Toy
IdTag
1
0, 1
0-n, *
1
n
1
1
1
n
n

Media Audio Document

Topic: Designing a Database

We come across a lot of raw data every day—the number of phone calls made to a company’s help desk, the Google
searches made in a day, the transactions made at your local bank. Data is a collection of facts. To make the facts
meaningful, you need to process the raw data.

Databases are designed to enable efficient management of data. Databases enable many simultaneous users to
locate and process desired data into relevant information. This animation discusses how to design an effective
database.

Priscilla owns Priscilla’s Pampered Pets, a pet store. She uses index cards to keep information about pets and their
owners. Here are examples of those index cards.

Toys: Ruff n Tuff Dog Chews, Li’l Nipper Catnip Mouse, Toys: Li’l Nipper Catnip Mouse
Jumbo Bunny Ball IdTag: 234567890 Reflective Collar

IdTag: 123456789 Rhinestone Collar

General: Priscilla realizes her store has expanded rapidly, the number of pets in the store is increasing, and the stack
of completed index cards is filling her office. She needs help, professional help! So, she calls Dave’s Database
Designers to design and build a database for her pet store.

General: Dave comes right over to talk to Priscilla about her data storage problem. He says they can identify what she
expects from a database system, and what data needs to be stored. After gathering this information, Dave is sure he
will be able to design an effective database and build the database for Priscilla’s pet store.

Priscilla: Dave, look at these!

General: Priscilla shows Dave the stacks of index cards in her office.

Dave: I am impressed with your organizational and juggling skills in managing so many cards. Talking to you and
looking at these cards have helped me create a data model for you. The data model shows the business entities and
their relationships. A business entity represents an element in the problem domain, in this case the pet store, for which
information needs to be stored in the database.

General: Dave quickly identifies two important entities, Pet and PetOwner. He sketches a diagram that shows similar
information as Priscilla’s index cards. Here is the diagram that Dave created.

Dave: This is a Unified Modeling Language (UML) class diagram. Each box is called a class in UML terminology. Each
box represents one of the important elements in your business that we need to store information about in the
database.

Priscilla: This design is similar to the design of my index cards!

General: Priscilla and Dave now discuss how the Pet and PetOwner classes are related to each other.

Priscilla: People come to my store and choose the pets they would like to take home. Once they buy the pets, the
food, and any other items the pet will need, they become pet owners. I enter their information on index cards.

Dave: Let me update the data model to include the relationship between a pet and a pet owner. The diagram will now
looks like this:

Dave: The line between the two classes represents the relationship between them. Another important piece of

information about the system is the number of entities that can participate at each end of the relationship.

Priscilla: Some pet owners have many pets, but each pet is owned by only one pet owner.

Dave: Can you have a pet owner who has no pets?

Priscilla: Well, I make a pet owner card only when someone buys a pet, but pets do get old and eventually die. So, I

guess it would be ok to have a pet owner who doesn’t have a pet right now. I would still like to keep their information

because they may buy another pet from my store.

General: Dave adds this information to the data model:

This diagram now shows that each pet is owned by exactly one pet owner. It also shows that a pet owner can own an
unlimited number of pets or have no pets at all. This is called a onetomany relationship, and is the most common kind
in relational databases.

Dave: Priscilla, this is starting to look pretty good, but there is some duplicated data about pet foods in the Pet class.
Did you notice that Howlin’ Hounds Kibble and Purrfectly Fine Feline Food appear several times in the Favorite Food
entry on several pet cards?

Well, I think that we can simplify things by introducing a new class to represent PetFood. That way we can describe
each kind of pet food in just one place.

Priscilla: Is that difficult?

Dave: No, it is actually pretty easy. Let’s add a new class box for PetFood that looks like this.

Dave: Now I have a few more questions about the data you need.

Dave: I noticed you listed toys for many of the pets. Are these toys that this pet likes to play with?

Priscilla: Yes, that is the idea. The owner often buys several toys for their pets. I like to keep track of which pet likes

which toys.

Dave: Ok. So each pet can have several toys right?

Priscilla: Yes, most pets have more than one toy.

Dave: And several pets may like the same kind of toy. For example, several dogs may like the Ruff n Tuff Dog Chews.

Priscilla: That’s exactly right. In fact the dog chews are a big hit with most of the dogs.

Dave: Sounds good. I will add that to the diagram, but before I do, I have one more question.

Priscilla: Sure, what is it?

Dave: I notice you have an IdTag entry on many of the pet cards. What is that?

Priscilla: Oh, it’s an identification tag for the pet. There are several kinds of tags, but each tag has a number that

identifies that particular pet.

Dave: So the number is unique? That is, an id tag number will only appear on one tag for one particular pet?

Priscilla: Right. The number is registered with a national pet id registry, so if the pet is lost, whoever finds it can locate

the owner by contacting the national registry and giving them the number on the id tag.

Dave: Ok, I think I understand your data pretty well now. I think we need Pet, PetOwner, Toy, and IdTag classes to

start with. Here is the diagram showing those classes with their data. And the lines between the class boxes represent
the relationships between the classes.

Priscilla: Hey, I understand this pretty well! Each pet can have only one id tag, but a bunch of toys. And any kind of
toy could be played with by a bunch of pets.

Dave: I think we have a pretty good design started here!

Priscilla: Thanks, Dave. The data looks much more organized than my stack of index cards!

General: Priscilla smiles as she starts imagining what she can do with all the space in her office after the cards are

gone!

Media Audio Document

Topic: Relational Schema

The goal of this animation is to show the process of transforming a data model into a relational schema.

General: Dave, from Dave’s Database Designers, is talking to Priscilla, who owns Priscilla’s Pampered Pets, a pet
store. They are discussing Priscilla’s data needs. She uses index cards but realizes her office will soon be dealing with
a huge number of index cards unless she quickly looks for a solution. Dave has been building a data model of the
different business entities that Priscilla manages for her pet store.

General: Priscilla tells Dave about the information on her index cards. Dave makes notes. He thinks that he has
enough information to build a UML data model of Priscilla’s system. The data model is displayed on the screen.

Dave: Let me explain what the diagram means.

Dave: This diagram represents the classes, such as IdTag, PetOwner, Pet, PetFood, and Toy that are important to the
management of your pet store and the relationships among those classes. I am going to use this diagram to create a
database that will store all the data for your pet store.

Priscilla: This diagram seems to accurately represent the information on my index cards.

General: Dave starts designing database tables from this data model. The collection of database tables and their
structure is known as a database schema.

Dave: Each class typically becomes one database table. Therefore, there will be five tables in the database, each
named for a class in the data model diagram.

Dave: Each table should contain a column to store one attribute of the corresponding class. For example, the Toy
table will have two columns, one for the description and one for the price of the toy. The five tables are displayed on
the screen.

Table 1

PetOwner Table
First Name Last Name Address
Jesse James 66 Wild West Way
Joe Smith 42 Memory Lane
Harvey Jones 1342 Daffodil Ave
Dilbert James 1492 Columbus Street
Dorothy Kansas Prairie

Table 2

IdTag Table
Identification Number Style

123456789 Rhinestone Collar
234567890 Reflective Collar
345678901 LED Tag
456789012 Collar Charm
567890123 Ankle Bracelet

Table 3

Pet Table
Name Age Species
Phydeaux 3 Dog
Felix 5 Cat
Polly 10 Parrot
Iggie 2 Iguana
Bugs 3 Rabbit
Toto 23 Dog
Cheshire 12 Cat

Table 4

Toy Table
Description Price
Ruff n Tuff Dog Chews 9.95
Li’l Nipper Catnip Mouse 5.45
Dunk Em Basketball Hoop 15
Jumbo Bunny Ball 7.5

Table 5

PetFood Table
Name Price

Howlin’ Hounds Kibble 19.99
Purrrrfectly Fine Feline Food 9.99
Polly’s Sunflower Seeds 12.95
RealLive Crickets 9.95
Crisply Lettuce Leaves 6.79

Dave: This is a good start because each table has a column for each attribute in the data model. A good relational
database table must also contain a primary key, which is a column or a combination of columns that uniquely identifies
a given row in that table.

Dave: Let me add a new column to each table. The new column stores the primary key value for each row in that
table. The IdTag table, which is Table 2, already has a natural primary key column, the IdentificationNumber column,
which stores a value guaranteed to be unique. The look of the remaining four tables will now change. The Pet,
PetOwner, PetFood, and Toy tables do not have a column (or combination of columns) that can serve as a primary
key, so I added a new column to each of these tables. The new primary key column in these tables will store a unique
value for each row in the table. This “nonnatural” primary key is known as a surrogate primary key.

PetOwner Table
First Name Last Name Address Owner Id
Jesse James 66 Wild West Way 1
Joe Smith 42 Memory Lane 2
Harvey Jones 1342 Daffodil Ave 3
Dilbert James 1492 Columbus Street 4
Dorothy Kansas Prairie 5

Pet Table
Name Age Species PetId
Phydeaux 3 Dog 1
Felix 5 Cat 2
Polly 10 Parrot 3
Iggie 2 Iguana 4
Bugs 3 Rabbit 5
Toto 23 Dog 6
Cheshire 12 Cat 7

Toy Table
Description Price ToyId
Ruff n Tuff Dog Chews 9.95 1
Li’l Nipper Catnip Mouse 5.45 2
Dunk Em Basketball Hoop 15 3
Jumbo Bunny Ball 7.5 4

PetFood Table
Name Price FoodId
Howlin’ Hounds Kibble 19.99 1
Purrrrfectly Fine Feline Food 9.99 2
Polly’s Sunflower Seeds 12.95 3
RealLive Crickets 9.95 4
Crisply Lettuce Leaves 6.79 5

Dave: The database must also represent the relationships among the classes. There are four relationships in the data
model. Each is slightly different because of the number of classes that participate at each end of the relationship.

Dave: The relationship between PetOwner and Pet is a onetomany relationship. Each pet has exactly one owner, but
a pet owner can own many pets. To represent this relationship in the database, I have introduced another type of key
called a foreign key. This is a column in one table that stores the primary key value of a row in another table,
establishing a relationship between a row in the first table with a row in the second table.

Priscilla: I see

General: Dave adds a foreign key column to the Pet table, which stores the primary key value of the pet owner who
owns the pet. The Pet table now looks like the one on the screen.

Pet Table
Name Age Species PetId OwnerId
Phydeaux 3 Dog 1 4
Felix 5 Cat 2 1
Polly 10 Parrot 3 2
Iggie 2 Iguana 4 3
Bugs 3 Rabbit 5 2
Toto 23 Dog 6 5
Cheshire 12 Cat 7 5

Dave: Notice that two Pet rows store the PetOwner primary key value of 5. Who owns Toto and Cheshire? You can
find out by referring to the PetOwner table. Find the row that contains a primary key value of 5. The relationship
between Pet and PetFood is a manytoone relationship. Each Pet has one favorite food, but several pets could have
the same pet food as their favorite food. I’ll manage this relationship by adding a foreign key column in the Pet table.
This field will store the primary key value of that Pet’s favorite PetFood. The Pet table now looks like this.

Pet Table
Name Age Species PetId Owner FavoriteFood
Phydeaux 3 Dog 1 4 1
Felix 5 Cat 2 1 2
Polly 10 Parrot 3 2 3
Iggie 2 Iguana 4 3 4
Bugs 3 Rabbit 5 2 5
Toto 23 Dog 6 5 1
Cheshire 12 Cat 7 5 2

Dave: The relationship between Pet and IdTag is a onetoone relationship. Each identification tag is related to only
one pet, and each pet has only one identification tag.

General: Dave represents this relationship in the database by adding a foreign key column to the IdTag table. This
column stores the primary key value of a row in the Pet table. The IdTag table now looks like this.

IdTag Table
IdentificationNumber Style PetId

123456789 Rhinestone Collar 1
234567890 Reflective Collar 2
345678901 LED Tag 7
456789012 Collar Charm 6
567890123 Ankle Bracelet 3

Dave: By looking at the primary key values stored in the PetId foreign key column in the IdTag table, you can
determine the identification tag of each pet.

Dave: Which lucky pet has the rhinestone collar?

Dave: The relationship between Pet and Toy is more difficult.

Priscilla: Really?

Dave: Yes. This is a manytomany relationship. Each pet can play with many toys and many pets can play with each
toy. To manage this type of relationship, I have to introduce a new table that stores the primary key value of a pet and
a primary key value of a toy. This new table converts the manytomany relationship into two onetomany
relationships. The new table looks like this:

PetToy Table
Pet Toy
1 1
2 2
3 3
5 4
6 1
7 2
1 4
1 2

Dave: Each row in this table pairs a pet with a toy.

Dave: Can you now determine which pets play with which toys?

Priscilla: Yes, I think so!

General: At this point, Dave is satisfied that he has created a database schema that describes the five classes and

their relationships. He does anticipate that while more tables will be added for more classes, and some changes may

be needed to these tables in the future, this schema provides a solid foundation for Priscilla’s database.

Media Audio Document

Topic: Internals of Indexes

Since a library contains so many books, we need to have some help in finding the particular book we are interested in.
Most libraries are far too large for us to walk around hoping we will see our book as we walk by! Fortunately, the library
helps us by providing the library catalog to assist in locating a particular book. The library catalog is indexed by title,
topic, and author to make it possible for us to find the location of a book by a particular author or title, or simply browse
books related to a topic of interest.

The index in a database system is similar to the index in a library catalog. Conceptually, a database index contains a
key value and a collection of pointers that store the location of the data identified by the key.

Why would you want to create an index in a database? One of the main purposes is to improve the performance of a
database query. An index can quickly return the rows that match a query; without an index, every row in a table must
be examined to see if it matches the query condition. For large tables, an index can represent a significant increase in
performance.

Dave from Dave’s Database Designers has designed a database for Priscilla, the owner of Priscilla’s Pampered Pets
pet store. One of the tables from that database is shown below.

Pet Table
Name Age Species PetId OwnerId
Phydeaux 3 Dog 1 4
Felix 5 Cat 2 1
Polly 10 Parrot 3 2
Iggie 2 Iguana 4 3
Bugs 3 Rabbit 5 2
Toto 23 Dog 6 5
Cheshire 12 Cat 7 5

Creating an index on a column used as a search key, in comparison operations in a conditional expression, or to order
the rows in a table is a common practice in relational databases. An index would be columns often used in the
WHERE, ORDER BY, and GROUP BY clauses of a SELECT statement.

Dave decides that since Priscilla often needs to locate all the pets who belong to a particular pet owner, he creates an
index on the Owner column for the Pet table by using the following command:

CREATE INDEX OWNERINDEX ON PET(OWNER);

Now, when the following structured query language (SQL) command is used to perform a query, the OwnerIndex will
be used to locate Dilbert’s pets. The operation will be quicker than before the index was created.

SELECT * FROM PET WHERE OWNER = ‘DILBERT’;

Sometimes, it is helpful to create an index using multiple columns in a table. This is known as a composite index. From
his conversations with Priscilla, Dave concludes it will be a good idea to create an index for the Pet table based on the
Owner, Species, and Name columns. So he executes this command:

CREATE INDEX PETINDEX ON PET(OWNER, SPECIES, NAME);

Composite database indexes should be used rarely, because they probably require a lot of memory and processing
power to maintain. The one situation in which a composite index can be helpful is when the combination of the
columns always contains a unique set of values. For example, if each pet owner names each pet of a particular
species with a different name (that is, every dog has a different name), then the PetIindex can be specified to be
unique. Dave decides to make the composite index unique. He uses the following command to do so:

CREATE UNIQUE INDEX PETINDEX ON PET(OWNER, SPECIES, NAME);

The database will now guarantee that every row in it will have a unique combination of the Owner, Species, and Name
column values. This guarantee of uniqueness is another use of database indexes.

You have now seen how an index can improve the performance of a database query. You have also seen how to
create a database index. Most indexes are based on only one column in a table. However, you can also create a
composite index—an index that uses multiple columns from a table.

Turn in your highest-quality paper
Get a qualified writer to help you with

“ Designing a Database and Relational Schema ”

Get high-quality paper

NEW! AI matching with writer

Still stressed with your coursework?

Get quality coursework help from an expert!

Order now