M&M is a computer manufacturer, which requires a database system for its parts inventory. The inventory comprises all the mechanical and electronic parts M&M use to make their computers.
Each part has a unique part number, description, and serial number. The quantity on hand, reorder levels and re-order quantity for each part all need to be recorded in the system. In addition, electronic parts need their power rating and voltage recorded and mechanical parts their physical dimensions.
When more parts are required, authorised M&M employees raise purchase orders with their suppliers. All the items on the purchase order are recorded on the system, i.e. the employeeâs name, purchase order number and date, supplier's name and address, parts ordered with their quantity and unit price. The date the parts are required may also be recorded along with the delivery charge (i.e. postage & packaging) and the delivery date (i.e. despatch or shipping date).
Note that the same part may be available from different suppliers and the unit price of the part depends on the supplier it comes from and the date it was ordered. Full contact details and payment terms on the suppliers are kept on the system. If a supplier stops supplying parts to M&M, its details are no longer required on the live system, but the details are archived at the end of every year. The details are then kept for one year and then deleted permanently after that.
For simplicity, you may assume that M&M use the same part numbers as their suppliers, and a part number is unique among all suppliers.
The parts may have other parts as components and/or they may themselves be components of other parts. For instance, a motherboard part M2002 has various different component microchips on it: C8080, C8090 etc; and a particular chip, say the C8080, may itself be component of many different circuit boards made by the company: M2001, M2002 etc. Note that a component can have a maximum of three levels of subcomponents.
M&M management has decided that rather than removing suppliers who supply no parts from the DB on an annual basis, this should be done immediately after the supplier stops supplying parts, on a weekly basis. All archived data is to be kept.
End of further requirements specification
Remember this project is to be done in Ms Access.