Office Supplies Inc. (OSI) sales office supplies to institutional buyers. It\'s

Question

Office Supplies Inc. (OSI) sales office supplies to institutional buyers. It's sales representatives use the attached form to take orders. Customer account numbers on the form are unique. Customers have only one billing address. Each customer also has a designated contact person who is responsible for bill payment. The billing address and the contact person information are filled out when a new customer account is created or when there is a change of address or contact person of an existing customer. OSI offers free delivery service to its customers. Usually all deliveries are made within two business days of the order date. If a customer decides to use OSI’s delivery service then the delivery address is filled in the order form. Sometimes a customer may not use the delivery service if he/she needs the supplies immediately. In such a case the customer is responsible for picking up the order from OSI’s warehouse. All items in an order are delivered to the same address. OSI owns several trucks for delivering customer orders. Trucks have a vehicle number, license plate number, license expiration date, and inspection expiration date. The same driver is always assigned to the same truck.

OSI maintains information on items it sales. Each item type is identified by an item code and has a unit price. OSI also keeps a record of the number of units of each item type available for sale. OSI employs several employess. Each employee has a social security number, name, address, and phone number. Drivers are a type of employee. In addition to employee information, each driver also has a driver’s license number and a license expiration date. Sales reps are also a type of employee. Sales reps have a salary and commission. All other employees have only a salary.

OSI wants to create an information system to manage its operational data. The OSI management has hired you as consultants for this project.

Requirements

Write business rules to describe the relationships among these entities. Each pair of business rules will have the following format:

Each A is {sometimes | always} related to {one | one or more} B's.

Each B is {sometimes | always} related to {one | one or more} A's.

Explanation / Answer

Entities:

1. Attached Form

2. Customer A/c No

3. Billing Address

4. Contact Person

5. Bill Payment

6. Address change

7. Employee

8. Sales Representative

9. Driver

10. Item

11. Free Delivery

12. Truck

Following are the rules to be maintened to keep the information organised as per each entity

Each Attach Form is always related to one A/c number

Each A/c number is always related to one Attach Form

Each Attach Form is always related to one Billing Address

Each Billing Address is always related to one Attach Form

Each Attach Form is always related to one Contact person

Each Contact person is always related to one Attach Form

Each Customer A/c number is always related to one Billing Address

Each Billing Address is always related to one Customer A/c number

Each Customer A/c number is always related to one Contact Person

Each Contact Person is always related to one Customer A/c number

Each Customer A/c number is sometimes related to one or more Billing payment

Each Bill payment is always related to one Customer A/c number

Each Customer A/c number is sometimes related to one or more Address change

Each Address change is always related to one Customer A/c number

Each Customer A/c number is always related to one or more items

Each Item is sometimes related to one Customer A/c number

Each Customer A/c number is sometimes related to one or more free delivery

Each Free delivery is always related to one Customer A/c number

Each Billing Address is always related to one contact person

Each contact person is always related to one Billing Address

Each Billing Address is sometimes related to one or more sales representatives

Each Sales representative is sometimes related to one or more Billing Address

Each Billing Address is sometimes related to one or more Items

Each Item is sometimes related to one Billing Address

Each Billing Address is sometimes related to one or more Free delivery

Each free delivery is always related to one Billing Address

Each Contact Person is sometimes related to one or more Employee

Each Employee is sometimes related to one or more Contact Persons

Each Contact Person is sometimes related to one or more Sales Representatives

Each Sales Representative is sometimes related to one or more Contact Persons

Each Contact Person is sometimes related to one or more Items

Each Item is sometimes related to one Contact Persons

Each Contact Person is sometimes related to one or more Free Delivery

Each Free delivery is always related to one Contact Persons

Each Employee is sometimes related to one Sales representatives

Each Sales Representative is always related to one employee

Each Employee is sometimes related to one Drivers

Each Driver is always related to one employee

Each Employee is sometimes related to one truck

Each truck is always related to one employee

Each Sales Representative is sometimes related to one or more Items

Each Item is sometime related to one Sales Representative

Each Sales Representative is sometimes related to one or more free delivery

Each free delivery is sometime related to one Sales Representative

Each driver is sometimes related to one or more Items

Each Item is sometimes related to one driver

Each driver is sometimes related to one or more free delivery

Each free delivery is sometimes related to one driver

Each driver is always related to one truck

Each truck is always related to one driver

Each Item is sometimes related to one free delivery

Each free delivery is always related to one or more Items

Each Item is sometimes related to one truck

Each truck is sometimes related to one or more Items

Each free delivery is always related to one truck

Each truck is sometimes related to one or more free delivery

Each Sales Representative is

Here are some additional rules for independent entities:

Each truck is always related to one vehicle number

Each truck is always related to one license plate

Each truck is always related to one license expiry date

Each truck is always related to one inspection expiry date

Each driver is always related to one license number

Each driver is always related to one license expiry date

Each driver is always related to one salary

Each Item is always related to one item code

Each Item is always related to one unit price

Each Item is always related to one inumber count available

Each Sales representative is always related to one comission

Each Employee is always related to one social security number

Each Employee is always related to one name

Each Employee is always related to one address

Each Employee is always related to one phone number

Each Employee is always related to one salary

Related Questions

Navigate

• Browse (All)
• Browse O
• Subjects

Integrity-first tutoring: explanations and feedback only — we do not complete graded work. Learn more.