Assignment

Chapter 9: Data Design

Part 1

Background

The IT team at Game Technology is working on database design and codes. Your job is to develop a recommendation for keeping track of individual games and their authors. You will be expected to create an ERD and table designs. Here is what you know so far:

• Every game has a product ID, name, version number, category code, and category description.
• Every author has an author ID, name, mailing address, and e-mail address.
• An individual author can develop one or more games.
• A game also can be developed by several authors. In this case, each author receives a specific percentage of the royalty that the game earns.

This information must be stored in the database. Your plan is to develop an overall design, list all the individual data items, and then include them as fields in the tables you create. Your design must be in the form of an ERD that shows all the entities, including any associative entities, and their relationships. Also, all table designs must be in third normal form (3NF).

The IT team also discussed data codes. You read somewhere that six-letter codes can have over 300 million unique combinations, but you don't know the exact number. Your friend, a math major, said that you could figure out the answer on your own, and she gave you a hint by saying it was 26 to the sixth power.

Practice Tasks

Task 1. Draw an ERD showing all entities and their relationships, and create table designs that include all necessary fields.

Task 2. Calculate the exact number of unique combinations that are possible using a six-letter code.

Challenge Tasks

Your recommended design ran into a snag. The team leader said it wasn't bad, but it did not go far enough. The specific comment was that it was not in third normal form.

Also, the team finally decided on a code for the customer ID field, which is a primary key.

The code will be a combination code, with four letters followed by four digits. The letters would be the first four letters of the customer's last name, which might make it easier for customers to remember.

Task 1. Review the sample ERD and table designs, and convert them to third normal form (3NF).

Task 2. Calculate the exact number of possible combinations with a code composed of four letters followed by four numbers. Does this seem like a good choice? Why or why not? What would you suggest?

Part 2

Background

The IT team at Game Technology is working on database design and codes. Your job is to develop a recommendation for keeping track of individual games and their authors. You will be expected to create an ERD and table designs. Here is what you know so far:

• Every game has a product ID, name, version number, category code, and category description.
• Every author has an author ID, name, mailing address, and e-mail address.
• An individual author can develop one or more games.
• A game also can be developed by several authors. In this case, each author receives a specific percentage of the royalty that the game earns.

This information must be stored in the database. Your plan is to develop an overall design, list all the individual data items, and then include them as fields in the tables you create. Your design must be in the form of an ERD that shows all the entities, including any associative entities, and their relationships. Also, all table designs must be in third normal form (3NF).

The IT team also discussed data codes. You read somewhere that six-letter codes can have over 300 million unique combinations, but you don't know the exact number. Your friend, a math major, said that you could figure out the answer on your own, and she gave you a hint by saying it was 26 to the sixth power.

Practice Tasks and Answers

Task 1. Draw an ERD showing all entities and their relationships, and create table designs that include all necessary fields

The ERD shown above joins the AUTHOR and GAME tables with an associative entity, called AUTHORS and GAMES. The crow-foot notation is explained in Chapter 9. As shown, one and only one author can create many games, or none. On the other hand, one and only one game can be created by at least one, or many authors.

Microsoft Access was used to create a database with the tables, fields, and relationships shown below.

Task 2. Calculate the exact number of unique combinations that are possible using a six-letter code.

A one-letter code has 26 possible values. A two-letter code has 26 x 26, or 676 possible values. A three-letter code has 26 x 26 x 26, or 17,576 values – and so on. As the following table shows, a six-letter code would have 308,915,776 possible values.