Apply Your Knowedge4

Western Wear Outfitters

Situation:
Western Wear is a mail-order firm that offers an extensive selection of casual clothing for men and women. Western Wear plans to launch a new Web site, and the company wants to develop a new set of product codes. Currently, 650 different products exist,
with the possibility of adding more in the future. Many products come in various sizes, styles, and colors. The marketing manager asked you to develop an individualized product code that can identify a specific item and its characteristics. Your initial reaction is that it can be done, but the code might be fairly complex. Back in your office, you give the matter some thought.

1. Design a code scheme that will meet the marketing manager’s stated requirements.

2. Write a brief memo to the marketing manager suggesting at least one alternative to the code she proposed, and state your reasons.

3. Suggest a code scheme that will identify each Western Wear customer.
I would suggest that ach customer would hav it's own ID number ang that has W on starting number to identify that they are a customer of Western Wear.

*See table below*

4. Suggest a code scheme that will identify each specific order.
i would suggest that each product has distinct code and the number of quantity. 

*See table below*

Apply Your Knowledge3

Mayville Public Library

Situation:

Mayville is a rural village with a population of 900. Until now, Mayville was served by a bookmobile from a larger town. The Mayville Village Council has authorized funds for a small public library, and you have volunteered to set up an information system for the library. Assume that the library will have multiple copies of certain books.

1. Draw an ERD for the Mayville library system.

2. Indicate cardinality.

3. Identify all fields you plan to include in the tables.

4. Create 3NF table designs.

Apply Your Knowledge2

PUPPY PALACE

Situation:

Puppy Palace works with TV and movie producers who need dogs that can perform

special tricks, such as headstands, somersaults, ladder climbs, and various dog-andpony

tricks. Puppy Palace has about 16 dogs and a list of 50 tricks from which to

choose. Each dog can perform one or more tricks, and many tricks can be performed

by more than one dog. When a dog learns a new trick, the trainer assigns a skill level.

Some customers insist on using dogs that score a 10, which is the highest skill level.

As an IT consultant, you have been asked to suggest 3NF table designs. You are fairly

certain that a M:N relationship exists between dogs and tricks.

_______________________________________________________________

1. Draw an ERD for the Puppy Palace information system.

2. Indicate cardinality.

3. Identify all fields you plan to include in the dogs and tricks tables. For example,

in the dogs table, you might want breed, size, age, name, and so on. In the tricks

table, you might want the trick name and description. You will need to assign a

primary key in each table. Hint: Before you begin, review some database design

samples in this chapter. You might spot a similar situation that requires an associative

entity that you can use as a pattern. In addition, remember that numeric

values work well in primary key fields.

4. Create 3NF table designs.

Apply Your Knowledge 1

Pick and Shovel Construction Company

SITUATION:

Pick and Shovel Construction Company is a multi state building contractor specializing in medium-priced town homes. C. T. Scott, the owner, is in your office for the third time today to see how the new relational database project is coming along. Unfortunately, someone mentioned to C. T. that the delay had something to do with achieving normalization.

“Why is all this normalization stuff so important?” he asks. “The old system worked

OK most of the time, and now you are telling me that we need all these special rules.

Why is this necessary?”

1. How should you respond to C. T.? Write him a brief memo with your views.

Normalization has to be eliminated to eliminate redundant data (making sure that all data is stored in only one place),  to enforce data consistency, to ensure data dependencies to be logical (meaning that data is stored in the correct table), and to isolate data (so that the changes made to a field or table does not affect others). 

 2. Assume that the Pick and Shovel’s main entities are its customers, employees, projects, and equipment. A customer can hire the company for more than one project, and employees sometimes work on more than one project at a time. Equipment, however, is assigned only to one project. Draw an ERD showing those entities.

3. Add cardinality notation to your ERD.

4. Create 3NF table designs.

(INF TABLE)

* we already satisfy the 1NF and 2NF table* (See picture above)

3NF Table

*Proj. Code and Equipment were eliminated because they are dependent on proj. Name and not on our primary which is the C.ID*