Designing a Database and Relational Schema

part1.pdf

Home >Information Systems homework help > Designing a Database and Relational Schema

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!