Case Problems

Download as a Word document: Case Problems Ch05 (87.0K)

CP-1: Spinners Record Store

Spinners Record Store is just starting to store the information on albums in stock in an Access database. After using it for a short while, there have been a number of changes identified that you have been assigned to complete. Currently, the database consists of three tables, Artist, CDArtist and CDs. Open the file AC05CP01 and make the following changes:

First, open the Artist table in design view and:
- Add a description to the LastName field that reads, "Artist last name or name of band."
- Set required property of LastName to Yes.
- Close Artist design view wind, save the changes, and read and answer yes to the dialog.
Open the CDArtist table and change the ArtistID field to Number, field size: long integer, required: Yes. Close the design window, save changes and after reading the message, answer yes to each dialog box that opens.
Next open the Relationship window, show the artist table and establish a relationship with these characteristics: Enforce Referential Integrity, Cascade Updates.
Edit the relationship between CDs and CDArtist to enforce referential integrity and close the edit relationship dialog box.
Print the relationships (Choose: File, Print). Close the relationships window and save changes.

After completing the steps, close the database and exit Access.

CP-2: R and B Home and Garden

In this project, you will design and create table objects in an existing database. You will need to determine which tables are necessary and assign fields to those tables. Open file AC05CP02 and review the design and contents of the existing tables. Table 1 contains the fields that will need to be allocated to tables. You may wish to review the first module of chapter five before proceeding.

R and B Home and Garden sells, and services appliances and other fixtures to enhance the home and yard. They stock appliances, house wares, and a limited supply of sporting goods. They sell to both the final consumer and act as a distributor to other stores. They have three warehouses for storing inventory. The database being developed is for the distributor side of the business. The database should store information on customers, sales reps, orders with details, and what is in inventory and where it is stored. Each customer is serviced by one sales rep, who in turn services many customers. Most customers are repeat customers. An order may have many items to be shipped. The customers can order any number of items. They may order one, or they might order several different items on a given order. Each item will appear only once on an order, but a customer may order several units of each item. (See Sample Invoice in Figure 5-4)

*Table 1*
Field Name	Data Type	Field Size	Description/Constraints
CustomerNum	Text	5	Primary Key
FirstName	Text	25	Customer’s First Name
LastName	Test	25	Customer’s Last Name
Address	Text	50	Customer street address
City	Text	30	Customer’s City
State	Text	2	Customer’s State
ZipCode	Text	5	Customer’s zip code
Phone	Text	10	Customer’s Phone
CreditLimit	Currency		Customer’s Credit limit, must be less than $5,000, Default is $3,000.
SalesRepID	Text	3	Sales Rep ID number
FirstName	Text	25	Sales Rep First Name
LastName	Text	25	Sales Rep Last Name
Address	Text	50	Sales Rep street address
City	Text	30	Sales Rep City
State	Text	2	Sales Rep State
ZipCode	Text	5	Sales Rep zip code
Phone	Text	10	Sales Rep Phone
TotalCommission	Currency		Total Commission Earned, YTD.
CommissionRate	Number	Percent	Commission Rate, must be less than 15%
PartNumber	Text	8
PartDescription	Text	50
ItemClassification	Text	2	2 character classification
WareHouseID	Text	1	Warehouse ID, 1, 2, or 3
UnitsOnHand	Number	Integer
UnitPrice	Currency		Price per unit

The Orders and OrderLine tables are already created in the database. You need to perform the following steps to complete the database design:

Identify tables necessary to store the data. (do this and the next two steps on paper, before changing the database)
Assign correct fields from Table 1 above to each table.
Put foreign keys in proper tables to create the relationships identified in the narrative above.
Create tables in the file AC05CP02.
Create the relationships between the tables as identified above.
Print the relationships after they’ve been created.

Save the changes to the relationship window, and close it. Then close the database and exit Access.

Case Problems Data Files Ch05 (44.0K)

2002 McGraw-Hill Higher Education
Any use is subject to the Terms of Use and Privacy Policy.
McGraw-Hill Higher Education is one of the many fine businesses of The McGraw-Hill Companies.