Designing a Database and Relational Schemacamron 2
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.
© 2009 Laureate Higher Education Group, Inc. All rights reserved. 1
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.
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
IdTag Table Identification Number Style
123456789 Rhinestone Collar 234567890 Reflective Collar 345678901 LED Tag 456789012 Collar Charm 567890123 Ankle Bracelet
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
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
PetFood Table Name Price
© 2009 Laureate Higher Education Group, Inc. All rights reserved. 2
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.
© 2009 Laureate Higher Education Group, Inc. All rights reserved. 3
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.
© 2009 Laureate Higher Education Group, Inc. All rights reserved. 4
Dave: Which lucky pet has the rhinestone collar?
Dave: The relationship between Pet and Toy is more difficult.
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.
© 2009 Laureate Higher Education Group, Inc. All rights reserved. 5