Saratoga National Corporation (SNC) is a wholesaler of silicon

							Fall 2002                                                                                   Tsai

                    MIS 150 – Database Management Systems for Business
                                      Group Project


Saratoga Chip National Corporation (SCNC) is a wholesaler of silicon integrated circuits (ICs),
the kind that are commonly found in all modern electronic equipment, especially computers. An
integrated circuit contains a silicon chip, enclosed in a package. The chip is a square slice of
silicon, metallic in appearance, about the size of the nail of a small finger. One surface of such a
chip has been heavily processed, by fine chemical methods, to generate an interconnected
complex of many thousands of miniaturized transistors.

 Chips can be made for a few dollars each, or even less, despite their complexity. They are
manufactured in large batches of a hundred or more circular wafers of processed silicon. Each
wafer (like a silicon “cookie”) about three inches in diameter and containing many chips.
Following chemical formation of the circuit chips on the circular wafers, each wafer is sliced to
give the individual chips.

The final stage of manufacturing is the wiring (or wire-bounding) of the chip into a plastic, metal,
or ceramic package. A package can be quite big compared with the size of enclosed silicon chip.
For example, a package about two inches by a half inch is not uncommon. A packaged circuit
will have an array of interconnection pins along two sides to enable the circuit to be inserted into
an electronic circuit board.

It is the packaged integrated circuits that SCNC deals with. SCNC never touches the
manufacturing end of IC production. That requires hundreds of millions of dollars in capital for
just a minimal production setup. SCNC buys ICs in large quantities from manufacturers,
warehouses them, and sells them, usually in smaller batches, to manufacturers of computers and
other electronic equipment.

When electronics manufacturers cannot get supply by a manufacturer they turn to company like
SCNC. SCNC buys only the highest quality product with long-term viability at low prices at
times when nobody wanted it, and sold it again at high prices when the market was clamoring for
product. The firm is generally well regarded in manufacturer circles and located in Los Gatos,
CA. Most of the firm’s building space is warehouse space for storage of the integrated circuits.

SCNC is owned by Nancy. She only has two employees, Thomas who looks after the warehouse
and Gwen who looks after the office. At the front of the building is Gwen’s office located near
the main entrance, so that Gwen also doubles as receptionist. Next to Gwen’s office is a small
conference room, used for sales presentations, and next to that is Nancy’s modestly furnished
office. There is a loading/unloading ramp for the warehouse, and Thomas has a small office just
adjacent to the ramp. The integrated circuits are usually in cardboard boxes, of varying sizes, on
rows of shelves in the warehouse storage area. Everything is on one floor.

Business at SCNC is very simple in principle. Nancy buys IC’s at one price and sells them at
another price. However, it requires special skill to know what and when to buy. An amateur
might buy high quality ICs at low prices only to discover later they had become obsolete and un-
saleable. Nancy rarely makes such mistake, although she expects it to happen once in a while.
To avoid making a fatal mistake, however, Nancy never puts a lot of her capital into any one
purchase. Thus if a deal does go sour, only a small fraction of her working capital is lost.


                                                  1
Nancy spends most of her day on the telephone, talking to marketing managers at the IC
manufacturers, purchasing manager at electronic equipment manufacturers, and her other
important contacts throughout the industry. This keeps her on top of trends in the business.

Before placing an order to buy ICs, Nancy will have carried out extensive negotiations with the
marketing people at the IC manufacturer. When details of price, quantity, quality, reliability,
deliver, and price have been worked out, Nancy hands the process of issuing a purchase
requisition over to Gwen. The supplier of the parts looks after delivery. On arrival the parts are
given a careful visual inspection by Tom. If there is a problem, Tom reports to Gwen, who
notifies the supplier of refusal to accept the goods.

Similarly, before an equipment manufacturer places an order with Nancy for parts, extensive
negotiations take place on the telephone. The purchase requisition, which usually arrives by mail,
is handled by Gwen. Nancy does not pay for shipment. She does not want the responsibility for
the parts once they leave SCMC. The purchaser is required to provide and pay for shipment.
This also helps Nancy maintain quality. Furthermore, it simplifies her business.

Both purchases and sales are on credit. The unpaid bills of suppliers are called accounts payable.
The unpaid bills of customers are called accounts receivable. Nancy manages her cash very
carefully, and never pays a bill before she has to. Funds are kept in an account at the Bank of
Sacramento. It should be clear that SCMC, despite its small size, is in a very strong financial
position.

Although Nancy’s business is clearly successful and quite profitable, its management is a hectic
affair for Nancy. A particularly difficult problem is not accepting orders for which there are no
parts in stock. Suppose that there were 1,000 ICs of type 3333 in stock from two distinct
invoices. Suppose now that a customer phoned asking for 700 of these parts. Nancy would
immediately check with Gwen about how many 3333’s were in stock. If 700 out of the 1,000 in
stock were from the same supplier, Nancy would tell the customer to go ahead and submit the
order. Otherwise the supplier would have to order part (say 300) made by one supplier and the
rest (400) from the other supplier, all of which has to be explained to the customer. Gwen would
keep a note of this expected order in a manual Orders Expected file.

Suppose now, that a few days later a second customer asks for 600 of type 3333 ICs. The first
customer has not yet submitted an order for the 700 parts, so that there are still 1,000 of the
3333’s in stock. As usual, Nancy asks Gwen about how many parts are in stock. Gwen checks
the inventory account and obtains the number 1,000, but also checks the Orders Expected file,
and informs Nancy that SCMC is also expecting an order for 700 from the first customer. Thus
there are not enough 3333 parts in stock, and the order has to be refused.

Nancy then starts phoning a number of suppliers to see what kind of price she will have to pay for
a new supply of 3333 devices. If she judges the price too high, she will simply not order. When
Nancy does ask Gwen to issue a purchase order for parts, she makes a note of this in a Parts On
Order file.

All this is very time consuming, and Gwen is more than busy, issuing purchase orders, invoices,
packing slips, retrieving information for Nancy, and keeping the books. Although Nancy likes to
keep thing simple and has come to the conclusion that a computer system for routine chores
would be a great help. She visualizes a video display screen in front of her when customer calls,
so that she can get the complete picture for any individual type of part—how many are in stock,


                                                 2
what they cost, how many are committed to orders not yet received, how many are on order, and
so on. Nancy figures that the computer system might just allow her to accept orders from
customers for parts that are not in stock but are on order. With only a manual system Nancy feels
that accepting such order is far too risky now.

An additional benefit of a computer system would be to provide instant up-to-date displays of
sales trends in any type of IC, as well as trends in prices paid and prices obtained. This would
help Nancy make better purchasing decisions and better pricing decisions on sales. The sales
trends versus quantity in stock would also give her a better control on when and what to reorder.

Nancy has hired your team from MIS150 Consulting, Inc., of Sacramento to design a basic
database system to handle the routine accounting and operational data for her firm. The proposed
data base and associated manipulation system should relieve Gwen from an increasingly
intolerable burden and, at the same time, provide Nancy with management information for
making tactical decision.

The assignment requirements:

1.      Entity relationship model

        a. Entity relationship diagram (only one to many relationship)

2.      Relational model

        a.   Corrected entity relationship diagram (only one to many relationship)
        b.   Normalized relations (relation name, attributes, primary key, and foreign key(s))
        c.   Constraints (entity, referential, domain, and enterprise)
        d.   Underline the primary key and double underline the foreign key(s) in each relation

3.      SQL (Oracle 8i)
        a. Corrected entity relationship diagram (only one to many relationship)
        b. Corrected and Normalized relations (relation name, attributes, primary key, and
           foreign key(s))
        c. Constraints (entity, referential, domain, and enterprise)
        d. Underline the primary key and double underline the foreign key(s) in each relation
        e. SQL statements and reports (both hard copy and soft copy)
        f. SQL Database (state the username and password)




                                                3