Tommy and Tom (TT) Inc. sells hundreds of products and uses over 10,000 components in its products. The company knows precisely what parts are needed for each product. They maintain a list of suppliers who supply one or more of the components. Each supplier is evaluated on price/unit, quality, and reliability for each part it supplies (implies, a supplier can supply more than one part but the supplier is evaluated for each part separately). Each supplier is then ranked (that is, if there are 5 suppliers then each supplier will be ranked Number 1 to 5. One implies highest preference). The new system that the management is considering will require suppliers to update their lead-time, and minimum and maximum order quantity. For each part there is a re-order level. If the number of units falls below that re-order level then a new purchase order is released to a supplier based on the rank. The system maintains when an order was placed, expected delivery date, and actual delivery date. On receipt, parts are evaluated again for quality. A batch can be rejected if the quality is bad. If the parts supplied is certified "OK" then a supplier is a paid immediately through electronic money transfer.
TASK:
- Identify various entities and the relationships and draw the E-R diagram. Convert the E-R diagram into tables (relational model).
- What if an order can be supplied in small quantities at various times (that is, a large order can be split into many small orders). How does your E-R diagram and table structure change?