MIS 150 Inc. the largest privately owned department store located in the SAC metropolitan area with more than 5 million populations. It has owned and operated by MIS 150 family over 100 years. The company currently has 4 stores and a central warehouse. The original store is located in the downtown area and other 3 stores are located in the regional shopping malls. The company has strong customer loyalty and a reputation for providing quality products and services.

Nancy Tsai, the chief executive officer of MIS 150 Inc., believes that the reason of doing so well is due to its hard working employees and its traditional business approach to treat and respect every customer as the most important individual to her and store employees. However, the company has been very slow to adopt the modern information technology.

Nancy has just purchased ORACLE database management system. Now, Nancy is looking for utilizing this database management system to improve the companys functions, operations and profits. After several discussions with the companys executive board, Nancy wants to replace all the cash registers with point-of-sale terminals and automatically collect the data for inventory control system, customer accounts receivable system, vendor accounts payable system, human resources system, and payroll system.

Despite the fact that the company has had a computer for the past fifty years, all its data-collection operations still involve written documents as manual input to computer. Only the data processing activities are carried out on the computer via file-based systems. The salesperson is paid a base salary plus the commission. The commission is calculated as 10 percent of the total sales amount (not including the sales tax) from all the sales slips for each salesperson in a month. Currently, a salespersons performance is a general worksheet containing the monthly commission paid to each salesperson. This performance becomes very important during the evaluation of promotion and rehiring.

The companys current inventory system is also a manual one. An inventory card for each item is kept in a master inventory book at the central warehouse. This inventory card carries the item name, inventory number and number of units on hand in the central warehouse. When an inventory clerk ships item out of the warehouse to the stores, the number of units on hand is reduced on the card. If the inventory manager wants to know the actual number of units the company has of a particular item, the inventory card must be checked for the number of units in the warehouse, and then each store must be called to find the number of units in that particular store. The minimum level of inventory for each item is set by the inventory manager and the department managers.

The inventory level of each item carried by each department is checked weekly against the reorder information carried in a master inventory book. When the inventory for an item falls below the reorder point, the inventory card for the item is tagged, and the buyer for that department is notified. If the item is to be reordered, the buyer sends a purchase order to the supplier and a copy to the Accounts Payable Department. At the end of each quarter, marketing manager also uses the master inventory book to determine the items to be offered for end of season sales.

When shipments of items arrive at the central warehouse, the inventory clerk checks each order against the enclosed invoice and send copies of the invoice to both the department buyer and the Accounts Payable Department. The inventory clerk will update the item count if the item received already has an inventory card. If the item is new, a new inventory card is created for it, and the department manager of each store is notified that the item is in the warehouse.

The system runs fairly smoothly with several problems that cause the company money. Most of them have occurred because the company does not have a good inventory control system to report exactly the location and quantity of a given item. Sales have been lost because items are unavailable or are not available at one store but at others. Unpopular items for quick sales can only be identified by looking and checking the store display.

For the accounts payable system, Nancy want to pay the vendor at the latest date without incurring any financial penalty. Nancy likes to create a new accounts receivable system by issuing company credit card for its customers. This allows the company to avoid the high percentage service fee imposed by the banks that offer the credit to customers using Master Charge credit system or Visa credit system. The data collected by this system can also be used by the marketing manager and product buyer to conduct marketing and product research for sales promotion and population items.

Nancy has hired your team as the manager of information technology department. Your responsibility is to design a database using the point of sale terminal to collect the information about vendor payment, sales, revenue, inventory levels and location, credit card transactions, cash transaction, and sales staff commissions with payroll and personal information. Nancy expects that your database design is flexible to handle not only the large volume transactional processing reports for the operation management, but also the ad hoc reports for the top executives.

To Be Turn In

Part 1. MIS 150 Corp. database designed according to the organization view in conceptual level of ANSI-SPARC Architecture

1. Attributes for each relation with primary key, candidate key, and foreign key.